Recently I was working on some SQL scripts using TSQL in SQL Server Management Studio and from force of habit I was using the "GO" statement between each of my SQL statements to seperate them.
I then started re-factoring the SQL, creating variables where I had hardcoded values etc. and placed those variable declarations at the top of the script.
After re-factoring, I executed the scipt and got an error message saying that the variable @varName was not defined?
I checked for any typos...none. I then started breaking down the script to see if I could isolate the issue. When I removed the "GO" statements, the script executed without error.
It turns out that "GO" is not a TSQL statement, but rather a command that is recognised by the sqlcmd and osql utilities and SQL Server Management Studio. These utilities interpret the "GO" statement as a signal that it should send the current batch of TSQL statements to an instance of SQL Server. Therefore any variables declared before the last "GO" and the next will NOT be available in the current batch of statements.
Just for fun I then copied my TSQL into a <cfquery> and when I executed this, found that the same error was thrown.
One to watch out for if you're not familiar with the true definition of the "GO" statement.
Full details of the "GO" statement can be found here: http://msdn.microsoft.com/en-US/library/ms188037(v=SQL.90).aspx
When using SQL Diff, should the tables you need to script be related via referential integrity, ApexSQL Diff has the ability to figure out the relationships in the tables, in most cases, scripting them so that referential integrity problems don't exist in the genreated script. Version 2008.11 has a more accurate and optimised parser that may help but if you ever find that you cannot get a script to run when updating data in a table with constraints you can check the following.
After initially moving a database to it's demo environment - I found that I had to manually sync any diagram changes manually as ApexSQL didn't handle these.
I found this article on the Microsoft KB which worked perfectly, allowing me to transfer the diagrams from dev:
How to Move a Database Diagram in SQL Server 7.0 and 2000
- Expand the Databases folder.
- Right-click the database that contains the diagram that you want to copy, click All Tasks, and then click Export Data
- In the Data Transformation Services Import/Export Wizard, click Next.
- On the Choose a Data Source page, click Next.
- On the Choose a Destination page, select the server and database to which you want to transfer the database diagram. Click Next.
- On the Specify Table Copy or Query page, click to select the Use a Query to specify the database to transfer check box, and then click Next.
- On the Type SQL Statement page, type the following line, and then click Next:
select * from dtproperties
- On the Select Source Table and Views page, click the Results destination, and then change it to dtproperties. Click Next.
- On the Save, Schedule, and Replicate Package page, click to select the Run Immediately check box, and then click Next.
- On the Completing the DTS Import/Export Wizard page, click Finish.
- Click OK, and then click Done to exit the wizard.
If you've ever setup a table and want to change the owner details you can run the below sample script in your SQL IDE e.g. Query Analyzer \ Aqua DataStudio to change database owner:
sp_changeobjectowner '[DOMAIN\user.name].databasename' , 'dbo'
All, When working on a Stored Procedure using a Linked Server, the following error was occurring when trying to capture the results of another stored proc within the same procedure i.e.
-- CREATE A TEMP TABLE TO STORE THE RESULTS FROM THE REMOTE STORED PROC CREATE TABLE #tmpTableName( columnName1 INT ,columnName2 NVARCHAR(10) ,columnName3 FLOAT ,columnName4 FLOAT ,columnName5 NVARCHAR(256) ,columnName6 INT ) INSERT #tmpTableName EXEC LINKED_SERVER.DATABASE_NAME.OWNER.sspStoredProcName @argument1, @argument2 We were able to call the remote stored procedure own it's own fine -
EXEC LINKED_SERVER.DATABASE_NAME.OWNER.sspStoredProcName @argument1, @argument2 but when trying to capture the results the below error occurred.
[OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ] OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d00a]. Msg 7391, Level 16, State 1, Line 10 The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.