MSDN TSQL forum | SQL with Manoj

Using GO statement within a Stored Procedure – MSDN TSQL forum

June 19, 2015 Leave a comment

–> Question:

I want to include GO statements to execute a large stored procedure in batches, how can I do that?

I have an SQL statement that is broken down into various sections, as it drops a temp table, then populates it, then generates another temp table based on the first one, then drops the first temp table and so on…….it enables me to execute all the code in one go.

I end each statement/block with a GO clause, to ensure it finishes running before the next peice of code.

However, when I try to incorprate this into a stored procedure, I cannot use the GO command. If I remove all the GO lines, the sp will run, but can I be confident that it’s running how it should, and that the lines of code near the end will not begin to execute before the lines above it have?

I’ve used a semicolon at the end of each statement in the sp?

–> My Answer:

First of all “GO” is not a SQL statement or command, this is just a keyword that your SSMS understands, and you can even change it to anything, check here.

Secondly “GO” is a batch separator that you use in SQL Queries, and Stored Procedure is itself a batch. If you put GO within a Stored Procedure, it will be an incomplete SP, and the rest part after GO will not be a part of the SP. More on MSDN about GO, link.

Now, why do you want to use GO within an SP?

If the SP is large, then try creating multiple SP and execute them in sequence.

–> Another Answer by Erland:

You cannot call script files from stored procedures. As I said two years ago, SQL Server is a server application – and SQLCMD is a client.

It’s a little confusing because you seem to use DML to mean DDL. (DML is Data Manipulation Language, that is INSERT, UPDATE, DELETE and also SELECT according to some, while DDL is Data Definition Language, that is CREATE/ALTER TABLE etc.)

Yes, if you want to interleave DDL and DML, you will need to make sure that the DML is not compiled until the DDL it depends on has been executed. Three solutions:

1) Separate batches. (Don’t forget to check @@trancount in the beginning of each batch.)=
2) But most commands in EXEC().
3) Have a program (in C# or whatever) to run the whole thing.

My preference is for the latter.

Ref Link and this.

Categories: SQL Server Questions Tags: batch separator, MSDN TSQL forum, Stored Procedure

Reading XML data in SQL Server – MSDN TSQL forum

May 26, 2015 Leave a comment

–> Question:

I have a table with a column with data type XML.

I don’t know the contents of the XML structure etc and I need to extract it

Please suggest.

–> My Answer:

Check these blog posts on how to query and work with XML data in SQL:

– Convert a table to XML and back to tabular format

– Query XML data in tabulat format

– Query Nested XML

– Using APPLY operator

– Read XML from a file

– XML with Namespaces

– Export XML to a file

– All XML posts

Ref Link.

Categories: SQL Server Questions, XML Tags: MSDN TSQL forum, XML SQL

DDL and DML in Single Script with GO (batch separator) – MSDN TSQL forum

April 16, 2015 Leave a comment

–> Question:

Running SQL Server 2008 R2. We currently apply schema and data changes to databases (for software upgrades) using separate scripts. Sometimes, based on the changes required this results in several scripts for a single upgrade. I’d like to (if possible) be able to encapsulate all changes, DDL and DML in a single script with transaction handling, so the whole thing rolls back if there are any issues. Problem is that with DDL changes we use GO as a batch separator, which screws up the standard error handling we implement in scripts using transactions (GO results in the script unable to find the error handler).

I’m aware of the fact that we can strip out these batch separators, but what I’m not clear on is what, if any, negative impact that would have. Are there certain commands in T-SQL (executing procedures, altering objects, etc) that are required to be executed in their own batch?

Lastly, as part of our DDL and DML changes, we perform a check to verify that the impending change actually needs to be made (so for a new column we check to see whether the column exists first). If, for example we were to make a DDL change that added a column, set a default constraint on that column and updated existing rows to have a base value for that column, could we use BEGIN…END or something similar so the column existence check would only have to be performed once for that group of changes?

–> My Answer:

You can remove GO or batch separator if you don’t have:

1. same variables declared in more than 1 batch

2. CREATE/ALTER VIEW/PROCEDURE statements, they should be first statement in the batch otherwise will fail. (otherwise it will give you following error: CREATE/ALTER VIEW/PROCEDURE’ must be the first statement in a query batch.)

3. there could be more things, just check based upon my above 2 points.

Ref Link.

Categories: SQL Server Questions Tags: batch separator, DDL, DML, MSDN TSQL forum

Table to store MS Word docs, only be accessible via SQL Queries – MSDN TSQL forum

April 15, 2015 Leave a comment

–> Question:

In Microsoft SQL Server 2012 database, I want to create a table to store Microsoft Word documents.

Documents must only be accessible via Transact-SQL queries.

How to do this?

–> My Answer:

As you are in SQL Server 2012 you can try using Filetables, a new feature introduced with this version. Check how to implement this in my blog post.

This features gives you an abstraction layer to store your files in File System but cannot directly navigate to the files. You will have to use SSMS to open the folders to view, add, or update the files.

Do check this link also on how to setup & do a Full Text Search on those Documents stored in the File System, link.

–> Another Answer by Eric:

Create a file C:\test.docx in the Server where SQL Server is hosted.

CREATE TABLE dbo.FilesTbl
(
name VARCHAR(99),
extension VARCHAR(99),
content IMAGE
)

--insert the word document into table
INSERT INTO FilesTbl 
SELECT 'test', 'docx', BULKCOLUMN 
FROM OPENROWSET (BULK N'C:\test\test.docx', SINGLE_BLOB) a;

Ref Link.

Categories: SQL Server Questions Tags: FileStream, FileTables, Full-Text Search, MSDN TSQL forum, OPENROWSET

Error Msg 7302, Cannot create an instance of OLE DB provider “Microsoft.Jet.OLEDB.4.0” for linked server – MSDN TSQL forum

April 10, 2015 Leave a comment

–> Question:

Below is the script to export data from Access 2003 using SQL server 2012 in SQL Server Management Studio:

EXEC sp_addlinkedserver 
   @server = 'SNE_SNAP2014', 
   @provider = 'Microsoft.Jet.OLEDB.4.0', 
   @srvproduct = 'OLE DB Provider for Jet',
   @datasrc = 'T:\Data Management\Project -SNAP SNE\SNE SNAP2014 T-Drive.mdb'
GO

this results into following error:

Msg 7302, Level 16, State 1, Line 2
Cannot create an instance of OLE DB provider “Microsoft.Jet.OLEDB.4.0” for linked server “SNE_SNAP2014”.

–> My Answer:

Please make sure that both Access & SQL Server 2012 should be on same 32 or 64 bit architecture.

Check this blog post for resolution and more info: https://sqlwithmanoj.com/2012/07/10/querying-excel-2010-from-sql-server-in-64-bit-environment/

Ref Link.

Categories: SQL Errors, SQL Server Questions Tags: Linked Server, MSDN TSQL forum

Newer Entries Older Entries