[SQL Server] Efficiently Reuse Gaps in an Identity Column

Efficiently Reuse Gaps in an Identity Column

Weekly SQL Server newsletter with articles, forum posts, and blog posts via email. Subscribers receive our white paper with performance tips for developers.

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

Read more

sqlteam.com

[SQL 2012] view with schema binding?

---

• view with schema binding?
• SSIS 2012 Project Deployment version control for multiple environments
• Can't find options to retain partitions in SSAS Tabular Deployment?
• Extract string from in between

view with schema binding? Posted: 01 Aug 2013 08:18 PM PDT I have come across a meaning of schema binding in views which i am unable to understandthe following is the explanation:" Binds the view to the schema of the underlying table or tables."my question is1) what is this underlying table? "When SCHEMABINDING is specified, the base table or tables cannot be modified in a way that would affect the view definition."my question is2) Which base table the explanation is talking about that cannot be modified?" The view definition itself must first be modified or dropped to remove dependencies on the table that is to be modified." my question is3) can you explain the above statement?When you use SCHEMABINDING, the select_statement must include the two-part names (schema.object) of tables, views, or user-defined functions that are referenced. All referenced objects must be in the same database. "Views or tables that participate in a view created with the SCHEMABINDING clause cannot be dropped unless that view is dropped or changed so that it no longer has schema binding. Otherwise, the Database Engine raises an error."my question is4) is this right that we cannot drop table that has been used in views with schema binding? Also, executing ALTER TABLE statements on tables that participate in views that have schema binding fail when these statements affect the view definition."my question is 5) i have tried altering the view with schema binding it altered successfully but the explanation says otherwise?

SSIS 2012 Project Deployment version control for multiple environments Posted: 01 Aug 2013 01:48 PM PDT Question:How do we manage the different versions of the SSIS project codebase for the different environments in the IS Server Catalog?Question Example: SSIS project v1.4 is deployed to the IS Server Catalog and an Agent job executes that deployed package against Environment PROD. How can I deploy SSIS project v2.0 to the same IS Server so i can execute the project against TEST without overwriting v1.4?Context:The Project Deployment model in SSIS 2012 has been documented with many articles on the web. They often describe the use of Environments in the IS Server Catalog to configure the project and package parameters for executing a deployed SSIS package in different environments, typically DEV, TEST, PROD.This means the [i]same[/i] deployed project packages can execute against a selected Environment. This is great and I understand this practice. My question is [i]not[/i] related to how to configure the Environments, parameters, and executing the package against the selected Environment.Typically an application would have potentially different versions (codebases) of the same application in DEV, TEST, PROD depending on the position in the application delivery cycle. eg. Dev 3.0, Test 2.0, Prod 1.4

Can't find options to retain partitions in SSAS Tabular Deployment? Posted: 04 Mar 2013 11:05 AM PST So I have created a new Tabular cube on 2012, deployed it for a while. And created a few new partitions via SSMS in the server to cover all the data. Now I have made some changes in the project, planning to deploy it to the server, but can NOT find an option to ignore existing partitions (that are not defined in the project itself) I have tried it on a test db, and no matter what i do... the project seems to deploy its definition, and overwrite what's on the server. meaning all the partitions that i created after deploy are WIPED! I have billions of rows of data, so reprocessing all the missing partitions are really not preferable... and managing the partitions in project but not in ssms is also not preferable as we typicall use scripts to add / manage partitions after it goes livewe have found some blog posts about changing .deploymentoptions file... but we don't know what value we should change to (not in BOL).. we changed it to "RetainPartitions" as a test.. but that doesn't workanyone has been through this? thanks

Extract string from in between Posted: 01 Aug 2013 09:04 AM PDT Example:Column Data Type: varchar(max)Table Name: insColumnName: inscolData in "inscol"For Example:Row #1:<?xml version="1.0"?><!DOCTYPE ins PUBLIC "ins" "hsdbdj"><instructions><format-template>${ND} </format-template><node id="node-0" variable-name="item"><node-text>Ref To</node-text></node><node id="node-1" variable-name="ND"><node-text>ABC</node-text></node></instructions>Output needed: ABCRow #2:<?xml version="1.0"?><!DOCTYPE ins PUBLIC "ins" "hsdbdj"><instructions><format-template>${ND} </format-template><node id="node-1" variable-name="ND"><node-text>XYZ</node-text></node></instructions>Output needed:XYZNote: In row it contains char(13) and char(10).

You are subscribed to email updates from SQLServerCentral / SQL Server 2012 / SQL 2012 - General To stop receiving these emails, you may unsubscribe now.	Email delivery powered by Google
Google Inc., 20 West Kinzie, Chicago IL USA 60610

[SQL Server] Using REPLACE in an UPDATE statement

Using REPLACE in an UPDATE statement

Weekly SQL Server newsletter with articles, forum posts, and blog posts via email. Subscribers receive our white paper with performance tips for developers.

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

Read more

sqlteam.com

[SQL Server] Handling SQL Server Errors

Handling SQL Server Errors

Weekly SQL Server newsletter with articles, forum posts, and blog posts via email. Subscribers receive our white paper with performance tips for developers.

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

Read more

sqlteam.com

[SQL Server] Using Dynamic SQL in Stored Procedures

Using Dynamic SQL in Stored Procedures

Weekly SQL Server newsletter with articles, forum posts, and blog posts via email. Subscribers receive our white paper with performance tips for developers.

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

Read more

sqlteam.com

[T-SQL] Insert a Total Row Into a Dynamic Table For Reporting

---

• Insert a Total Row Into a Dynamic Table For Reporting
• Linked server restirction
• IDENTITY_INSERT
• How To Use IS NOT NULL in a Case Statement
• need query help
• [HELP] FOR XML
• Find potential locks caused by joined queries
• Grouping by columns to create single record
• String Manipulation
• update records based on condition
• Executing an SP in a new SPID?
• CTE

Insert a Total Row Into a Dynamic Table For Reporting Posted: 02 Aug 2013 12:58 AM PDT I am creating a table based on a dynamic PIVOT. This table is used for reporting, i'm emailing out the results using DB send mail. I need to add a total row to this table BEFORE I email. Here is the code that creates the table.-- Construct the full T-SQL statement-- and execute dynamicallySET @sql = N'SELECT Customer, ' + @selectcols +' into DailySalesTotalsFROM (SELECT Customer, sorderdate, isnull(qty,0) as QuantityFROM dbo.dailysales) AS DPIVOT(sum(quantity) FOR sorderdate IN(' + @cols + N')) AS P order by 2 desc;';EXEC sp_executesql @sql; I've thought about then doing a Insert into DailySalesTotalsSelect 'Total', 0,0,0,0,0,0,0but, then how to update the zeros to the column totals without knowing the column headers? Is there a way to loop and do that? Or reference ordinal position? Would love some help and suggestions! Thanks!

Linked server restirction Posted: 02 Aug 2013 12:51 AM PDT Hi friends,Is there a way to restrict linked server access for a particular login in sql server 2008r2.Thanks in advance.

IDENTITY_INSERT Posted: 01 Aug 2013 10:51 PM PDT Hi, when I try this query it gives me an error [i]An explicit value for the identity column in table 'DC1..DMAuditHistory' can only be specified when a column list is used and IDENTITY_INSERT is ON.[/i]DECLARE @projectId intSELECT @projectId = ProjectId FROm Project with(nolocK) WHERE ProjectNo = '61N24643'SET IDENTITY_INSERT DC1..DMAuditHistory ONINSERT INTO DC1..DMAuditHistory SELECT * FROM DMAuditHistory with(nolock) WHERE ProjectId = @projectId SET IDENTITY_INSERT DMAuditHistory OFF

How To Use IS NOT NULL in a Case Statement Posted: 01 Aug 2013 05:38 PM PDT Hi Guys, I'd like to know a correct way of using the query below in a case statement:and it.bitIsAsset = Case it.bitIsAsset When 1 Then it.intItemId IS NOT NULL Else 'All' End --it ia as Alias for my tableThis is used in the WHERE Clause, If bitIsAsset = 1 Then the intItemId mustn't be NULL Else 'All' but my query doesn't work.any help would be appreciated.Thanks Teee

need query help Posted: 01 Aug 2013 10:15 AM PDT Given the following table, create table highdollardx (dx char(10),ssn char(9), year int, paid money)and some sample rows like thisinsert highdollardx values ([034.0],[148569256],[2011], 115.3)insert highdollardx values ([075],[148569256],[2010], 113.58)insert highdollardx values ([078.10],[148569256],[2010], 75.71)insert highdollardx values ([110.1],[148569256],[2012], 500.76)insert highdollardx values ([173.21],[147400434],[2012], 0)insert highdollardx values ([173.21],[147400434],[2013], 1264.76)insert highdollardx values ([211.2],[148569256],[2010], 73.82)insert highdollardx values ([216.4],[148569256],[2012], 85.6)insert highdollardx values ([221.2],[148569256],[2010], 75)insert highdollardx values ([236.5],[147400434],[2010], 8.75)insert highdollardx values ([236.5],[147400434],[2011], 9.7)insert highdollardx values ([250.00],[148569256],[2012], 223.89)insert highdollardx values ([272.0],[148569256],[2011], 188.83)insert highdollardx values ([272.0],[148569256],[2012], 567.97)insert highdollardx values ([272.0],[148569256],[2013], 248)insert highdollardx values ([287.5],[148569256],[2011], 395)insert highdollardx values ([305.00],[148569256],[2011], 1142)insert highdollardx values ([309.0],[147400434],[2010], 520)insert highdollardx values ([309.0],[147400434],[2011], 300)insert highdollardx values ([338.18],[147400434],[2010], 267.75)insert highdollardx values ([346.10],[147400434],[2011], 61.94)insert highdollardx values ([346.10],[147400434],[2012], 46.44)insert highdollardx values ([346.90],[147400434],[2010], 141.01)insert highdollardx values ([346.90],[147400434],[2011], 129.58)insert highdollardx values ([346.90],[147400434],[2012], 76.22)insert highdollardx values ([366.14],[147400434],[2010], 81.68)insert highdollardx values ([366.16],[147400434],[2010], 46.27)insert highdollardx values ([366.16],[147400434],[2012], 46.27)insert highdollardx values ([368.8],[147400434],[2012], 0)insert highdollardx values ([368.8],[148569256],[2012], 123.82)insert highdollardx values ([372.14],[148569256],[2010], 82)insert highdollardx values ([380.23],[148569256],[2011], 481.97)insert highdollardx values ([380.23],[148569256],[2012], 93.8)insert highdollardx values ([380.4],[147400434],[2012], 0)insert highdollardx values ([401.1],[147400434],[2010], 12.13)insert highdollardx values ([401.1],[147400434],[2011], 45.19)insert highdollardx values ([401.1],[147400434],[2013], 21.69)insert highdollardx values ([401.1],[148569256],[2010], 64.46)insert highdollardx values ([401.1],[148569256],[2011], 653.15)insert highdollardx values ([401.1],[148569256],[2012], 86.45)insert highdollardx values ([401.9],[148569256],[2010], 165.85)insert highdollardx values ([401.9],[148569256],[2011], 64.46)insert highdollardx values ([461.1],[148569256],[2011], 99)insert highdollardx values ([461.9],[148569256],[2010], 82)insert highdollardx values ([461.9],[148569256],[2013], 159.51)insert highdollardx values ([462],[148569256],[2012], 121.05)insert highdollardx values ([465.9],[147400434],[2011], 46.04)insert highdollardx values ([473.2],[148569256],[2012], 823.1)insert highdollardx values ([477.9],[147400434],[2010], 178.57)insert highdollardx values ([477.9],[147400434],[2011], 398.31)insert highdollardx values ([477.9],[147400434],[2012], 911.96)insert highdollardx values ([493.90],[147400434],[2010], 244.34)insert highdollardx values ([520.6],[148569256],[2012], 91.33)insert highdollardx values ([595.0],[148569256],[2011], 86.45)insert highdollardx values ([595.9],[148569256],[2011], 84.45)insert highdollardx values ([600.00],[147400434],[2010], 62.25)insert highdollardx values ([600.01],[147400434],[2010], 9.31)insert highdollardx values ([600.10],[147400434],[2010], 139.05)insert highdollardx values ([600.10],[147400434],[2011], 45.19)insert highdollardx values ([600.10],[147400434],[2012], 37.7)insert highdollardx values ([600.10],[147400434],[2013], 23.03)insert highdollardx values ([611.72],[147400434],[2010], 80.92)insert highdollardx values ([611.72],[147400434],[2011], 217.47)insert highdollardx values ([627.4],[148569256],[2010], 77.72)insert highdollardx values ([695.89],[148569256],[2010], 30.6)insert highdollardx values ([696.1],[147400434],[2010], 45.19)insert highdollardx values ([696.1],[147400434],[2011], 26.18)insert highdollardx values ([696.1],[147400434],[2012], 53.13)insert highdollardx values ([698.8],[148569256],[2011], 30.6)insert highdollardx values ([702.0],[147400434],[2013], 174.68)insert highdollardx values ([702.11],[148569256],[2010], 45)insert highdollardx values ([709.2],[147400434],[2010], 152.36)insert highdollardx values ([709.2],[147400434],[2011], 43.65)insert highdollardx values ([709.2],[147400434],[2013], 135.93)insert highdollardx values ([709.8],[147400434],[2010], 100.52)insert highdollardx values ([715.15],[147400434],[2010], 70837.7)insert highdollardx values ([715.15],[147400434],[2011], 4783.06)insert highdollardx values ([715.16],[148569256],[2012], 169.45)insert highdollardx values ([715.94],[147400434],[2011], 213.86)insert highdollardx values ([715.95],[147400434],[2010], 85.96)insert highdollardx values ([715.96],[147400434],[2013], 0)insert highdollardx values ([717.2],[147400434],[2013], 94.75)insert highdollardx values ([717.9],[147400434],[2012], 74.61)insert highdollardx values ([719.45],[147400434],[2010], 289.81)insert highdollardx values ([719.45],[147400434],[2011], 171.69)insert highdollardx values ([719.45],[148569256],[2011], 792.49)insert highdollardx values ([719.46],[147400434],[2012], 86.43)insert highdollardx values ([719.46],[147400434],[2013], 244.72)insert highdollardx values ([721.3],[148569256],[2010], 149.02)insert highdollardx values ([721.3],[148569256],[2013], 61.45)insert highdollardx values ([722.10],[148569256],[2011], 10497.2)insert highdollardx values ([722.52],[148569256],[2010], 243.98)insert highdollardx values ([722.52],[148569256],[2011], 351.6)insert highdollardx values ([722.83],[148569256],[2012], 249.9)insert highdollardx values ([723.2],[147400434],[2012], 180)insert highdollardx values ([723.3],[147400434],[2010], 365.66)insert highdollardx values ([723.3],[147400434],[2011], 103.08)insert highdollardx values ([723.3],[147400434],[2012], 117)insert highdollardx values ([724.02],[148569256],[2010], 1653.34)insert highdollardx values ([724.02],[148569256],[2011], 17914.5)what Select Statement returns the dx with the highest paid amount for each ssn and year?iow, the result should be eight rows, four for each ssn and year, with the highest paid amount and the dx responsible for it.thanks a ton...going bald with it.

[HELP] FOR XML Posted: 21 Jul 2013 09:16 PM PDT Hi All,I need use SQL (for xml) to write in this customize format of XML as below:<track_n_trace tranid="1234" ordernum="ABC123"> <current_location>MY</current_location> <status code="OK" reason_code="FINE"> <remark>KUU1234</remark> </status> <consignment_number>A5555</consignment_number> <total_shipped overall_qty="0" box_qty="0"/></track_n_trace>

Find potential locks caused by joined queries Posted: 01 Aug 2013 02:46 AM PDT I have an application that is slowing down under a fairly heavy load and I believe some of my relational logic are creating locks that is creating a queue. Is there a way to use SSMS to analyze a query to determine what type of lock the server will attempt to place on the resources?

Grouping by columns to create single record Posted: 01 Aug 2013 04:05 AM PDT I am in a time crunch, many things on my plate today, and I just can't seem to get this problem, thought I would toss it out to the forum, and someone would easily see the answer.My Table looks like this:AttribID FormatID-------- ---------12 3415 3419 3425 3412 4915 4927 49I want to basically query the table like this: Give me FormatID when AttribID=12 AND AttribID=15 AND NOT AttribID = 27, and I want to get back one answer:34I know it is a grouping issue, but I am not getting the exact syntax, and am running out of time. Can anyone see the obvious solution that I am missing :angry:ThanksBrian

String Manipulation Posted: 01 Aug 2013 07:44 AM PDT Hello EveryoneI have a strange thing that I am trying to code for, and I am almost there, but I know there is a function.I have a string of text that I need to select, which is only the first word. The rest I do not care about. There is a blank space between the two words, there are only two words and I will only need the first word.Sample:Irvine StreetI need only the word "Irvine"I cannot, for the life of me think of the function name. I have tried substring, but that is not doing what I need.Thank you in advance for your comments, suggestions and assistanceAndrew SQLDBA

update records based on condition Posted: 01 Aug 2013 05:48 AM PDT hii have table , which can have millions of rows.Member primarycondition primaryintensity secondarycondition secondaryintensityM2345 hf 1M2345 COPD 1M2345 CAD 1M2345 dia 1M2345 Ast 1M2345 hf 2M2345 COPD 2M2345 CAD 2M2345 dia 2M2345 Ast 2what i need to do is if member has more than 1 condition, theni need to make 1 as a primary and another one secondary condition. with same primary intensitybased on this order hf COPD CAD dia Ast so the result will be Member primarycondition primaryintensity secondarycondition secondaryIntensityM2345 hf 1 M2345 COPD 1M2345 CAD 1M2345 dia 1M2345 Ast 1M2345 hf 2M2345 COPD 2M2345 CAD 2M2345 dia 2M2345 Ast 2i need to do it for all member,if member has more than 1 primary condition.any help?

Executing an SP in a new SPID? Posted: 01 Aug 2013 04:43 AM PDT Hi all,I'm coding a stored procedure which will be the control mechanism for a series of ETL subprocesses. This control procedure will run through several loops, monitoring various states, and executing these subprocess stored procedures when needed. The challenge is, I'd like to execute each of these subprocess stored procedures in their own session, so that they can run in parallel. I don't want the control procedure to wait for the completion of any of the subprocess stored procs.I think I could do this with xp_cmdshell calling SQLCMD, but that seems pretty kludgy, and I'd have to convince our admins to enable xp_cmdshell. I think I could also create, execute, and delete a SQL Agent job on the fly, but that seems even more kludgy. Is there a simpler way to execute a stored procedure in a new SPID?Thanks!

CTE Posted: 01 Aug 2013 02:49 AM PDT How do I nested common table expression?

You are subscribed to email updates from SQLServerCentral / SQL Server 2008 / T-SQL (SS2K8) To stop receiving these emails, you may unsubscribe now.	Email delivery powered by Google
Google Inc., 20 West Kinzie, Chicago IL USA 60610

[SQL Server 2008 issues] Tempdb tables two months old

---

• Tempdb tables two months old
• String esaping in TSQL
• Differential backup performance, advantage and disadvantages
• Question in SSIS package, data flow task.....
• Moving Records in Batches - 7 Tables - Several Million Records
• Moving Database That Has a Schema
• Need SQL Query
• Index Fragmentation and Page count minimum
• Dm_exec_procedure_stats rollback issues
• Using extended events to capture sort warnings and hash warnings
• Can SSIS tell a batch file to move on before the SSIS package completes?
• Right outer Join???
• Catch text before symbol
• WITH (NOLOCK), allows dirty reads.
• Backup Set List Not Populating
• Query an Acceess Table in SSMS
• Stored Procedure (or) SQL Script Output to Text File

Tempdb tables two months old Posted: 01 Aug 2013 02:06 AM PDT Hi AllI run the following query to get all tempdb tables:[code="sql"]SELECT *FROM tempdb.sys.sysobjectsWHERE name LIKE '#%'ORDER BY crdate[/code]I can see around 50 tables that are between one and two months old, and another 70 that are between one week and one month old.I'm guessing these are created by application components that maintain a constant connection, and are never dropped or deallocated. Is there a way I can find out what procedure, application, or even connection is creating these tables? I'd like to go into whatever is creating them and add the DROP or SET = NOTHING.Thank you

String esaping in TSQL Posted: 01 Aug 2013 06:50 AM PDT Hi guys,So after 3 days of dev I'm now collecting enough data from remote DBs to be able to knock up an OK web front end. The last part was the most annoying.I'm using openquery & exec(@Q) over my linked servers but for one script this proved very annoying. The query searches for job steps which recently failed that included a backup. It then searches the message column to find out which database was at fault. The problem is this requires a bunch of charindex/substring to get to it.In PL/SQL you can do: q'[some query]' and it will automatically escape all apostrophes between the square brackets. Does TSQL have similar? This is the kind of mess I was left with:[code]set @query = 'insert into backup_history (INSTANCE,DB_NAME,START_DATE,END_DATE,SIZE_MB,TYPE,STATUS)SELECT * from openquery(' + @server + ',''SELECT @@SERVERNAME server, SUBSTRING (sh.message,charindex(''''database '''''''''''',sh.message)+10,case when ((charindex('''''''''''''''', sh.message, charindex(''''database '''''''''''',sh.message)+10))-(charindex(''''database '''''''''''',sh.message)+10)) = -10then 0 else (charindex('''''''''''''''', sh.message, charindex(''''database '''''''''''',sh.message)+10))-(charindex(''''database '''''''''''',sh.message)+10) end) db[/code]As you may have guessed predicting the right number of apostrophes to include in each section was the main issue.

Differential backup performance, advantage and disadvantages Posted: 01 Aug 2013 05:54 PM PDT Now we are using full backup and log backup as a backup option for our sql server.We do full backup daily once and log backup every one hour. But now i am planning to use differential backup also.But i don't know much about its performance and speed and size of backup file. So i am planning to have full backup weekly once for less changing database and have differential backup everyday. I am not leaving transaction log backup as it will help in point in time recovery.Now i want to know is it differential backup will help me in anyway? What will be the size compared to log backup. Is there any disadvantage in using differential backup?

Question in SSIS package, data flow task..... Posted: 01 Aug 2013 01:03 AM PDT Hello,I am not sure how to proceed here... I will try to expline my situation clear....I need to write a SSIS package for Incremental download of data from Oracle source to SQL Server 2008 destination. With every download.. there might be combinations of Insert/Updates/Deletes.and there is a primary key column and a last_update column both in source and destination. What I want to do is.....Look at the maximum value of last_update in the destination table, then go to source and get the data which is one day before max(last_update) column..... (For ex: If max(last_update) in destination is 07/31/2013..... then I need to get all rows with last_update = 07/30/2013 from the Oracle source)...My question is: How can I get this done in SSIS package, may be it's already is there is google... but I failed to get it.....Thank you all for your replies.

Moving Records in Batches - 7 Tables - Several Million Records Posted: 01 Aug 2013 09:00 AM PDT Hello All,I know there are multiple ways to move records between tables but I'm trying to come up with a good way to go about moving millions of records from multiple tables into another set of tables. The reasoning for this move is pretty complicated but it's a necessity. Here are the basics,- 7 Tables, let's call them tables temp1 thru temp 7- Each Table has 1 million + records that need to be migrated to 7 tables within a different database, let's call those tables arc_temp1 thru arc_temp7- I would prefer to run this in batches overnight, say 1k or 10k per batch at a time- I would like the batches to go in order and then loop, i.e. move 10k from temp1, then move 10k from temp2, then move 10k from temp 3, etc. and then loop back to temp1 and start again- Although i need to move millions of records, this can be done over several nights and I would like to only run this for maybe 4 hours at a time I've done rowcount with inserts before but I don't know if this is the best way to go about it, i've also used cursors but with millions of rows I think this would be a not-so efficient way of going about it, i've never used SSIS to do a move job like this before but I could probably figure that out as well if you guys think it would be the best way.Also, you don't have to actually write any code, just a pointer in the right direction and I'll be able to figure it out.Thanks for the help

Moving Database That Has a Schema Posted: 01 Aug 2013 01:04 PM PDT Hi experts,I'm trying to move a 2008 database that has a named schema (not dbo) to another server.I restored from backup onto the new server. This database is the default for user abc. Login abc is also the owner of schema abc.On the new server the abc login this database uses works, sort of, but is unable to access the tables which are owned by the schema ie abc.Table1unless I explicitly add the schema to the Select statement like this:Select * from abc.Table 1 - this works.2 things are wrong: 1.Objects created by the abc login should automatically have the abc schema, but they don't - they have dbo2. This login cannot select data unless the schema is included in the Select statement - as Select * from abc.Table1. If I use Select * from Table1, I get "object not found".Can anyone tell me what's wrong please?I'm thinking of starting over.Should I pre-create the login BEFORE restoring or should I delete the login, then restore this db?Thanks, Jack

Need SQL Query Posted: 31 Jul 2013 11:37 PM PDT Dear All,need sql query for following scenario:Development server somebody is altering the table column or delete records,we need sql query for find the systemname and username.

Index Fragmentation and Page count minimum Posted: 01 Aug 2013 08:21 AM PDT I started writing rebuild index script.However, I came across about "SET @page_count_minimum" value .Could you please help me understand what is page_count_minimum and its value to be set etc.Thanks,

Dm_exec_procedure_stats rollback issues Posted: 01 Aug 2013 05:21 AM PDT Hi everyoneWas wonder if anyone encountered this issue querying sys.dm_exec_procedure_stats before.A user queried the sys.dm_exec_procedure_stats view and caused a system wide WRITELOG wait. We killed the query and is ended up still stuck in a Rollback for about 4 hours now.Any ideas on how to resolve this issue?

Using extended events to capture sort warnings and hash warnings Posted: 01 Aug 2013 02:48 AM PDT HiI have just downloaded the extended events gui for sql2008.Can anybody point me to any articles on how to set this up to capture the info for sort warnings and hash warnings ?

Can SSIS tell a batch file to move on before the SSIS package completes? Posted: 01 Aug 2013 03:47 AM PDT Hi,Question: Can I run a series of SSIS packages from a batch file where each package has 10 steps, and after a certain step (specified within each package) the batch file starts the next package up so that more than one package is running at once? Like, is there a task that basically says "send completion signal?" Or is there some other mechanism by which I can run a series of SSIS packages, setting a marker within each package that tells when to start the next one running asynchronously?Background:I have several SSIS packages that I run in a row. Most of them extract a bunch of data via Excel Business Warehouse add-ins, then load that data to SQL tables and massage the data. The Excel part of each package needs to run by itself as it runs on the local machine and I only want one Excel session to be open. As soon as the Excel part of each package is done (or in cases where there is no Excel then immediately), I would like the batch file to move on to the next package so that it starts while the previous one is finishing up.Here is a sample of what my batch file looks like:[code="plain"]set dtexec="C:\Program Files\Microsoft SQL Server\100\DTS\Binn\dtexec"%dtexec% /F "C:\DATA\SQL Packages\My 1st Package\My 1st Package.dtsx" /REPORTING EWCDI >>"C:\DATA\SQL Packages\My 1st Package_error.log"---------------------------------------------------set dtexec="C:\Program Files\Microsoft SQL Server\100\DTS\Binn\dtexec"%dtexec% /F "C:\DATA\SQL Packages\My 2nd Package\My 2nd Package.dtsx" /REPORTING EWCDI >>"C:\DATA\SQL Packages\My 2nd Package_error.log"---------------------------------------------------set dtexec="C:\Program Files\Microsoft SQL Server\100\DTS\Binn\dtexec"%dtexec% /F "C:\DATA\SQL Packages\My 3rd Package\My 3rd Package.dtsx" /REPORTING EWCDI >>"C:\DATA\SQL Packages\My 3rd Package_error.log"[/code]Thanks for the help!Tai

Right outer Join??? Posted: 01 Aug 2013 05:25 AM PDT I know I'm lost somewhere here but I thought this would get me all client in Rday_All_clients even if a record didn't exist in R_Day ?[code="sql"]SELECT Rday_All_clients.LName, Rday_All_clients.FName, R_Day.OID, R_Day.STARTTIME, FROM R_Day RIGHT OUTER JOIN Rday_All_clients ON R_Day.ID = Rday_All_clients.Client_IDWHERE (R_Day.OID = '140FA7F3F5924873B8A14E369A3CCCBD') AND (R_Day.CAD69 = '1')ORDER BY Rday_All_clients.LName[code="sql"][/code]

Catch text before symbol Posted: 30 Jul 2013 03:15 AM PDT Hi geniuses,consider:[code="sql"]create table #mytable( Value varchar(50))INSERT INTO #mytable (Value) SELECT 'First.Second'[/code] [b]I need to isolate what's before the symbol: '.'[/b]I was able to catch whats after the symbol:[code="sql"] SELECT Value, (substring(Value,charindex('.',Value)+1,LEN(Value))) AS 'beforedot' FROM #mytable[/code]Thanks in advance!

WITH (NOLOCK), allows dirty reads. Posted: 31 Jul 2013 09:04 PM PDT Hello,[size="1"]I am aware the the WITH (NOLOCK) hint should be avoided, that is not the question. Also I am aware that WITH (NOLOCK) can result in inconsistend data. (Any Non serializable isolation level can result in inconsistend data, so I am not concerned about that).[/size]But:[b]Does using the WITH (NOLOCK) hint in queries allow dirty reads?[/b]Thanks,Ben

Backup Set List Not Populating Posted: 01 Aug 2013 04:14 AM PDT I have posted this on Amazon's Developer Forum, but I have also seen this happen at other times.I have a Windows 2008 server with SQL Server installed (image_1) on Amazon EC2. I create an image of that machine, then I launch the newly created AMI (image_2). When I go into (image_2)'s Management Studio to perform a database restore, I noticed that all of my backup result sets are not displaying, yet they are on disk.What it does look like: NoBackupSets.jpgWhat it should look like: ListedBackupSets.jpgWhat I was hoping that would happen is I could fire up image_2, then using the Management Studio's GUI, restore a database from a point in time. This would speed up the process greatly over having to do it all individually by script.My question is, when the backup set list is not populated, is there a way to populate it or do I have to run my restore manually from script?Thank you in advance,Jim

Query an Acceess Table in SSMS Posted: 01 Aug 2013 01:16 AM PDT Heres my scenario. I have an access table called Innovative in an access database.While on the server i can query this SELECT *FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', '\\vq1\Efile Document Storage\HR\HR\Employee File Notes\Employeefilenote.accdb'; 'admin';'',innovative); so i have made that into a view called bk_Innovative. When i want to query that from SSMS loaded on my machine not the server i get this error.OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "The Microsoft Access database engine cannot open or write to the file '\\vq1\Efile Document Storage\HR\HR\Employee File Notes\Employeefilenote.accdb'. It is already opened exclusively by another user, or you need permission to view and write its data.".Msg 7303, Level 16, State 1, Procedure bk_Innovative, Line 6Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".im a network admistrator, i have access to that path, so why if i make that into a view on the server, cant i query that out on my pc. it only works to query on this server. I want to use this query in Reporting services. Any IDEAS?

Stored Procedure (or) SQL Script Output to Text File Posted: 28 Apr 2009 09:16 PM PDT Hi All, I am having a Stored Procedure Or SQL Script to be attached to Job Scheduler. When this Stored procedure executes it generates some output text. I need to store this output to text file when ever this store Procedure (or) SQL Script executed by job Scheduler.Please help me how to handle this?Thanks,Anil

You are subscribed to email updates from SQLServerCentral / SQL Server 2008 - General / SQL Server 2008 To stop receiving these emails, you may unsubscribe now.	Email delivery powered by Google
Google Inc., 20 West Kinzie, Chicago IL USA 60610