Archive

Archive for the ‘SQL Server Questions’ Category

XML to Table – MSDN TSQL forum

February 28, 2013 Leave a comment

–>Question:

I’m using sql server 2000 and here is my target table:

CREATE TABLE #SampleTable
(
    Number varchar(100),
    StartNum int
)

I want to parse the XML and insert into the above table:

<activateNumber>
         <!--You may enter ANY elements at this point-->
         <number>1234</number>
         <StaartNumbers>
            <StartNum>234</StartNum>
         </StaartNumbers>
</activateNumber>

 

–> My Answer:

Check the code below:

CREATE TABLE #SampleTable
(
    Number varchar(100),
    StartNum int
)

DECLARE @xml XML = '<activateNumber>
         <!--You may enter ANY elements at this point-->
         <number>1234</number>
         <StaartNumbers>
            <StartNum>234</StartNum>
         </StaartNumbers>
</activateNumber>'

DECLARE @iDoc int
EXEC sp_xml_preparedocument @iDoc output, @xml

INSERT INTO #SampleTable
SELECT 
	number,
	StartNum
FROM OPENXML(@iDoc,'/activateNumber/StaartNumbers',2)
WITH (number INT '../number[1]', 
		StartNum INT 'StartNum[1]')

EXEC sp_xml_removedocument @iDoc

 

Ref Link.

Categories: SQL Server Questions, XML Tags: ,

Are User Defined Functions (UDF) pre-compiled – MSDN TSQL forum

June 15, 2011 Leave a comment

–> Question:

I have seen several posts saying that a UDF is not pre-compiled like a stored procedure.

But this MSDN Page it says both are.

Could any one let me know which one is correct and what is meant by this?
 

–> My Answer:

UDF are not pre-compiled, UDFs similar to SPs are only compiled on their first execution. They are just parsed and created in SQL catalog.

Try to execute the DMV query before the SQL query where the function TsqlSplit() is called. It won’t give to you any cache’d record. But after you execute the SQL query and then execute the DMV query then you will see a cache’d record for Function execution.

Check this:

dbcc freeproccache
GO

SET ANSI_NULLS ON 
GO 
SET QUOTED_IDENTIFIER ON 
GO 

CREATE FUNCTION [dbo].[TsqlSplit] (@List As varchar(8000)) 
RETURNS @Items table ( 
  Item varchar(8000) Not Null) 
AS 
  BEGIN 
      DECLARE @Item As varchar(8000), 
              @Pos  As int 

      WHILE DATALENGTH(@List) > 0 
        BEGIN 
            SET @Pos=CHARINDEX(',', @List) 

            IF @Pos = 0 
              SET @Pos=DATALENGTH(@List) + 1 

            SET @Item = LTRIM(RTRIM(LEFT(@List, @Pos - 1))) 

            IF @Item <> '' 
              INSERT INTO @Items 
              SELECT @Item 

            SET @List=SUBSTRING(@List, @Pos + DATALENGTH(','), 8000) 
        END 

      RETURN 
  END 
GO 

SELECT objtype, 
       cacheobjtype, 
       usecounts, 
       text 
FROM   sys.dm_exec_cached_plans AS ECP 
       CROSS APPLY sys.dm_exec_sql_text(ECP.plan_handle) AS EST 
WHERE  EST.text LIKE '%TsqlSplit%' 

GO 

SELECT * 
FROM   [TsqlSplit] ('1,2,3') 
GO 

SELECT objtype, 
       cacheobjtype, 
       usecounts, 
       text 
FROM   sys.dm_exec_cached_plans AS ECP 
       CROSS APPLY sys.dm_exec_sql_text(ECP.plan_handle) AS EST 
WHERE  EST.text LIKE '%TsqlSplit%'

 

Ref Link.

Categories: SQL Server Questions Tags: , ,

Select multiple number of OrderIDs and send the criteria to a Stored Procedure parameter – MSDN TSQL forum

April 21, 2011 Leave a comment

–> Question:

I’m trying to select any number of orders and call a stored procedure only one time. So my question is in order to perform a select statement in a stored procedure, is it possible to have n number of parameters, and if it is possible what is the best way to call a stored procedure with any number of orderIds? Or is it better to send in a nvarchar 30000 with xml and parse the xml for each order id and stick it into my stored procedure that way? I’m looking for a better way to perform this operation that is reliable.

example:

select * 
from tblOrders 
where OrderId = 1 or OrderId = 2 or OrderId = 1000 etc...

 

–> Answer:

Yes, not only XML, but there are various ways to pass multiple values to a Stored Proc via parameters, like:
1. Passing CSV string
2. Passing an XML or JSON string
3. Using temp-table
4. Using TVPs
… check here for all these options, link.
 

You can go with by creating a temp table:

CREATE TABLE #temptblOrders (OrderID int)

… store all OrderIDs into this.

Use this temp table inside your proc as:

SELECT * 
FROM tblOrders 
WHERE OrderId in (
	SELECT OrderID 
	FROM #temptblOrders
)

 

Ref link.

Categories: SQL Server Questions Tags: ,

Error while Querying Excel file – The Microsoft Access database engine could not find the object ‘Sheet1$’ – MSDN TSQL Forum

April 14, 2011 Leave a comment

–> Question:

I have 2 servers, on server A the query:

SELECT * --INTO #productlist
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
    'Excel 8.0;HDR=YES;Database=C:\Book1.xls',
    'SELECT * FROM [Sheet1$]');

Runs perfectly, but on server B I get following error:

OLE DB provider “Microsoft.ACE.OLEDB.12.0” for linked server “(null)” returned message “The Microsoft Access database engine could not find the object ‘Sheet1$’. Make sure the object exists and that you spell its name and the path name correctly. If ‘Sheet1$’ is not a local object, check your network connection or contact the server administrator.”.

Msg 7350, Level 16, State 2, Line 1
Cannot get the column information from OLE DB provider “Microsoft.ACE.OLEDB.12.0” for linked server “(null)”.

If I go to Proviver -> Microsoft.ACE.OLEDB.12.0 -> Properties:
On the server that works: on the white square bellow i got: Linked servers using this provider: XML
On the server with error i got nothing.
 

–> Answer:

If you are using same query/connection then make sure the excel files are on both the servers at C: drive.

Are both the servers same, i.e. 32 bit or 64? check: select @@version

And same for excel, it seems you have Excel 2007.

Check this blog post for more info.
 

Ref link.

Categories: SQL Server Questions Tags: ,

Autocommit Transactions with SQL Server – MSDN TSQL forum

April 7, 2011 Leave a comment

–> Question:

I remember for the previous version, “autocommit” is defaulted to be on. How about SQL Server 2008 Management Studio? Where can we change its default?
 

–> My Answer:

Go to SSMS, Menu, Tools, Options, Query Execution, SQL Server, ANSI.

Here check the option on SET IMPLICIT_TRANSACTIONS
 

–> Answer by Tom Cooper:

Autocommit defaults to be on for SQL Server 2008. As per MSBoL it says:

“Autocommit mode is the default transaction management mode of the SQL Server Database Engine. Every Transact-SQL statement is committed or rolled back when it completes. If a statement completes successfully, it is committed; if it encounters any error, it is rolled back. A connection to an instance of the Database Engine operates in autocommit mode whenever this default mode has not been overridden by either explicit or implicit transactions. Autocommit mode is also the default mode for ADO, OLE DB, ODBC, and DB-Library.

A connection to an instance of the Database Engine operates in autocommit mode until a BEGIN TRANSACTION statement starts an explicit transaction, or implicit transaction mode is set on. When the explicit transaction is committed or rolled back, or when implicit transaction mode is turned off, the connection returns to autocommit mode.”

There is a user option named IMPLICIT_TRANSACTIONS which will set implicit transactions ON when the connection is made by a user that has that option. But it only works if you are using DBLIB which is an old deprecated connection method that has many restrictions and should only be used for legacy applications. That user option is ignored if you are using ODBC or OLEDB connections.
 

–> Answer by Steven Wang:

If you SET IMPLICIT_TRANSACTIONS on then you need manually to rollback and commit.

by default the IMPLICIT_TRANSACTIONS is set to off and commit is automatically done.
 

Ref link.

Categories: SQL Server Questions Tags: , ,