Dynamic Quote Problem

I'm trying to insert a quarter and year variable in CODE (BAD) section below
in a dynamic sql statement from Northwind. I'm trying to make CODE (BAD)
look like CODE (GOOD) section. I'm having trouble getting the correct quotes
around the @.YearName and @.QtrName1 part in CODE (BAD) section.
Can someone copy/paste CODE (BAD) section into Northwind and fix quotes?
CODE (BAD)***********************************
**
declare @.SQL varchar(4000), @.SQL1 varchar(4000), @.SQL2 varchar(4000)
declare @.QtrName1 int, @.QtrName2 int, @.YearName int
set @.QtrName1 = '1'
set @.QtrName2 = '2'
set @.YearName = '1997'
SET @.SQL1 = 'SELECT ' + '''Qtr''' + ''' + CAST(@.YearName AS VARCHAR(55)) +
''' + '''-''' + ''' + CAST(@.QtrName1 AS VARCHAR(55)) + ''' + ' AS Quarter,
'
SET @.SQL1 = @.SQL1 + 'COUNT(Orders.ShipName) AS ctShipName,
COUNT(Orders.ShipCity) AS ctShipCity
FROM Categories INNER JOIN
Products ON Categories.CategoryID =
Products.CategoryID INNER JOIN
Orders INNER JOIN
[Order Details] ON Orders.OrderID = [Order
Details].OrderID ON Products.ProductID = [Order Details].ProductID'
SET @.SQL2 = 'SELECT ' + '''Qtr''' + ''' + CAST(@.YearName AS VARCHAR(55)) +
''' + '''-''' + ''' + CAST(@.QtrName2 AS VARCHAR(55)) + ''' + ' AS Quarter,
'
SET @.SQL2 = @.SQL2 + 'COUNT(Orders.ShipName) AS ctShipName,
COUNT(Orders.ShipCity) AS ctShipCity
FROM Categories INNER JOIN
Products ON Categories.CategoryID =
Products.CategoryID INNER JOIN
Orders INNER JOIN
[Order Details] ON Orders.OrderID = [Order
Details].OrderID ON Products.ProductID = [Order Details].ProductID'
SET @.SQL = @.SQL1 + ' UNION ALL ' + @.SQL2
EXEC(@.SQL)
CODE (GOOD)**********************************
***
SELECT 'Qtr 1997-1' AS Qtr, COUNT(Orders.ShipName) AS ctShipName,
COUNT(Orders.ShipCity) AS ctShipCity
FROM Categories INNER JOIN
Products ON Categories.CategoryID =
Products.CategoryID INNER JOIN
Orders INNER JOIN
[Order Details] ON Orders.OrderID = [Order
Details].OrderID ON Products.ProductID = [Order Details].ProductID
UNION ALL
SELECT 'Qtr 1997-2' AS Qtr, COUNT(Orders.ShipName) AS ctShipName,
COUNT(Orders.ShipCity) AS ctShipCity
FROM Categories INNER JOIN
Products ON Categories.CategoryID =
Products.CategoryID INNER JOIN
Orders INNER JOIN
[Order Details] ON Orders.OrderID = [Order
Details].OrderID ON Products.ProductID = [Order Details].ProductIDScott wrote:

> I'm trying to insert a quarter and year variable in CODE (BAD) section bel
ow
> in a dynamic sql statement from Northwind. I'm trying to make CODE (BAD)
> look like CODE (GOOD) section. I'm having trouble getting the correct quot
es
> around the @.YearName and @.QtrName1 part in CODE (BAD) section.
> Can someone copy/paste CODE (BAD) section into Northwind and fix quotes?
> CODE (BAD)***********************************
**
> declare @.SQL varchar(4000), @.SQL1 varchar(4000), @.SQL2 varchar(4000)
> declare @.QtrName1 int, @.QtrName2 int, @.YearName int
> set @.QtrName1 = '1'
> set @.QtrName2 = '2'
> set @.YearName = '1997'
> SET @.SQL1 = 'SELECT ' + '''Qtr''' + ''' + CAST(@.YearName AS VARCHAR(55)) +
> ''' + '''-''' + ''' + CAST(@.QtrName1 AS VARCHAR(55)) + ''' + ' AS Quarter
,
> '
> SET @.SQL1 = @.SQL1 + 'COUNT(Orders.ShipName) AS ctShipName,
> COUNT(Orders.ShipCity) AS ctShipCity
> FROM Categories INNER JOIN
> Products ON Categories.CategoryID =
> Products.CategoryID INNER JOIN
> Orders INNER JOIN
> [Order Details] ON Orders.OrderID = [Order
> Details].OrderID ON Products.ProductID = [Order Details].ProductID'
> SET @.SQL2 = 'SELECT ' + '''Qtr''' + ''' + CAST(@.YearName AS VARCHAR(55)) +
> ''' + '''-''' + ''' + CAST(@.QtrName2 AS VARCHAR(55)) + ''' + ' AS Quarter
,
> '
> SET @.SQL2 = @.SQL2 + 'COUNT(Orders.ShipName) AS ctShipName,
> COUNT(Orders.ShipCity) AS ctShipCity
> FROM Categories INNER JOIN
> Products ON Categories.CategoryID =
> Products.CategoryID INNER JOIN
> Orders INNER JOIN
> [Order Details] ON Orders.OrderID = [Order
> Details].OrderID ON Products.ProductID = [Order Details].ProductID'
> SET @.SQL = @.SQL1 + ' UNION ALL ' + @.SQL2
> EXEC(@.SQL)
>
>
> CODE (GOOD)**********************************
***
> SELECT 'Qtr 1997-1' AS Qtr, COUNT(Orders.ShipName) AS ctShipName,
> COUNT(Orders.ShipCity) AS ctShipCity
> FROM Categories INNER JOIN
> Products ON Categories.CategoryID =
> Products.CategoryID INNER JOIN
> Orders INNER JOIN
> [Order Details] ON Orders.OrderID = [Order
> Details].OrderID ON Products.ProductID = [Order Details].ProductID
> UNION ALL
> SELECT 'Qtr 1997-2' AS Qtr, COUNT(Orders.ShipName) AS ctShipName,
> COUNT(Orders.ShipCity) AS ctShipCity
> FROM Categories INNER JOIN
> Products ON Categories.CategoryID =
> Products.CategoryID INNER JOIN
> Orders INNER JOIN
> [Order Details] ON Orders.OrderID = [Order
> Details].OrderID ON Products.ProductID = [Order Details].ProductID
You don't need dynamic SQL for any of that, so why do it? Extraneous
quotes etc removed from the following. If you quote it up again it
should work. It looks like you've left out WHERE clauses for the
quarters though.
SELECT 'Qtr' + CAST(@.yearname AS VARCHAR(55)) + '-'
+ CAST(@.qtrname1 AS VARCHAR(55)) AS Quarter,
COUNT(Orders.ShipName) AS ctShipName,
COUNT(Orders.ShipCity) AS ctShipCity
FROM Categories
INNER JOIN Products
ON Categories.CategoryID = Products.CategoryID
INNER JOIN [Order Details]
ON Products.ProductID = [Order Details].ProductID
INNER JOIN Orders
ON Orders.OrderID = [Order Details].OrderID
UNION ALL
SELECT 'Qtr' + CAST(@.yearname AS VARCHAR(55)) + '-'
+ CAST(@.qtrname2 AS VARCHAR(55)) AS Quarter,
COUNT(Orders.ShipName) AS ctShipName,
COUNT(Orders.ShipCity) AS ctShipCity
FROM Categories
INNER JOIN Products
ON Categories.CategoryID = Products.CategoryID
INNER JOIN [Order Details]
ON Products.ProductID = [Order Details].ProductID
INNER JOIN Orders
ON Orders.OrderID = [Order Details].OrderID ;
David Portas
SQL Server MVP
--|||Scott (sbailey@.mileslumber.com) writes:
> I'm trying to insert a quarter and year variable in CODE (BAD) section
> below in a dynamic sql statement from Northwind. I'm trying to make CODE
> (BAD) look like CODE (GOOD) section. I'm having trouble getting the
> correct quotes around the @.YearName and @.QtrName1 part in CODE (BAD)
> section.
Nah, getting order into nested quotes is something I leave as an exercise
to the poor student. :-)
But some hints:
1) The function quotename() can sometimes be handy.
2) If you use SET QUOTED_IDENTIFIER OFF, you can also use " as quote
delimiter. You cannot use this, if there indexed views involved, or
you need to use indexes on computed columns. But it does make
composition of SQL strings easier.
3) There is actually no obligation to do this in T-SQL. After all, building
SQL strings is about string manipulation, and that is not a strong
point of T-SQL. Doing in client code may be better.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||David Portas (REMOVE_BEFORE_REPLYING_dportas@.acm.org) writes:
> You don't need dynamic SQL for any of that, so why do it? Extraneous
> quotes etc removed from the following. If you quote it up again it
> should work. It looks like you've left out WHERE clauses for the
> quarters though.
David, has it never occurred to you that what you see may only be a piece
of the actual problem? Since I wrote a crosstab query for Scott earlier
this wend, I strongly suspect that his query will include a dynamic
number of UNION ALL things.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||it was a simple example without the where. thanks for the extra info.
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns972A4E50E881Yazorman@.127.0.0.1...
> Scott (sbailey@.mileslumber.com) writes:
> Nah, getting order into nested quotes is something I leave as an exercise
> to the poor student. :-)
> But some hints:
> 1) The function quotename() can sometimes be handy.
> 2) If you use SET QUOTED_IDENTIFIER OFF, you can also use " as quote
> delimiter. You cannot use this, if there indexed views involved, or
> you need to use indexes on computed columns. But it does make
> composition of SQL strings easier.
> 3) There is actually no obligation to do this in T-SQL. After all,
> building
> SQL strings is about string manipulation, and that is not a strong
> point of T-SQL. Doing in client code may be better.
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||Scott,
A simple solution to this (which I give at the risk of
encouraging too much dynamic SQL, which can be
downright dangerous) is to use REPLACE, based on
a model query. It's quite easy to set up, and it avoids
most headaches.
declare @.SQL varchar(4000)
set @.SQL = '
SELECT
''Qtr $$@.YearName$$-$$@.QtrName1$$'' AS Qtr,
COUNT(Orders.ShipName) AS ctShipName,
COUNT(Orders.ShipCity) AS ctShipCity
FROM Categories INNER JOIN
Products ON Categories.CategoryID =
Products.CategoryID INNER JOIN
Orders INNER JOIN
[Order Details] ON Orders.OrderID = [Order
Details].OrderID ON Products.ProductID = [Order Details].ProductID
UNION ALL
SELECT
''Qtr $$@.YearName$$-$$@.QtrName2$$'' AS Qtr,
COUNT(Orders.ShipName) AS ctShipName,
COUNT(Orders.ShipCity) AS ctShipCity
FROM Categories INNER JOIN
Products ON Categories.CategoryID =
Products.CategoryID INNER JOIN
Orders INNER JOIN
[Order Details] ON Orders.OrderID = [Order Details].OrderID
ON Products.ProductID = [Order Details].ProductID
'
set @.SQL = REPLACE(@.SQL,'$$@.YearName$$',@.YearName)
set @.SQL = REPLACE(@.SQL,'$$@.QtrName1$$',@.QtrName1)
set @.SQL = REPLACE(@.SQL,'$$@.QtrName2$$',@.QtrName2)
The only quotes you have to double in this case are the
few in the model query. If any of the substituted parameters
can contain quotes, you will have to do additional replacements
like
set @.param = replace(@.param,char(39),char(39)+char(39
))
Even other complications, like an unknown number of UNION ALL
clauses, are not so hard to handle this way.
Steve Kass
Drew University
Scott wrote:

>I'm trying to insert a quarter and year variable in CODE (BAD) section belo
w
>in a dynamic sql statement from Northwind. I'm trying to make CODE (BAD)
>look like CODE (GOOD) section. I'm having trouble getting the correct quote
s
>around the @.YearName and @.QtrName1 part in CODE (BAD) section.
>Can someone copy/paste CODE (BAD) section into Northwind and fix quotes?
>CODE (BAD)***********************************
**
>declare @.SQL varchar(4000), @.SQL1 varchar(4000), @.SQL2 varchar(4000)
>declare @.QtrName1 int, @.QtrName2 int, @.YearName int
>set @.QtrName1 = '1'
>set @.QtrName2 = '2'
>set @.YearName = '1997'
>SET @.SQL1 = 'SELECT ' + '''Qtr''' + ''' + CAST(@.YearName AS VARCHAR(55)) +
>''' + '''-''' + ''' + CAST(@.QtrName1 AS VARCHAR(55)) + ''' + ' AS Quarter,
>'
>SET @.SQL1 = @.SQL1 + 'COUNT(Orders.ShipName) AS ctShipName,
>COUNT(Orders.ShipCity) AS ctShipCity
> FROM Categories INNER JOIN
> Products ON Categories.CategoryID =
>Products.CategoryID INNER JOIN
> Orders INNER JOIN
> [Order Details] ON Orders.OrderID = [Order
>Details].OrderID ON Products.ProductID = [Order Details].ProductID'
>SET @.SQL2 = 'SELECT ' + '''Qtr''' + ''' + CAST(@.YearName AS VARCHAR(55)) +
>''' + '''-''' + ''' + CAST(@.QtrName2 AS VARCHAR(55)) + ''' + ' AS Quarter,
>'
>SET @.SQL2 = @.SQL2 + 'COUNT(Orders.ShipName) AS ctShipName,
>COUNT(Orders.ShipCity) AS ctShipCity
> FROM Categories INNER JOIN
> Products ON Categories.CategoryID =
>Products.CategoryID INNER JOIN
> Orders INNER JOIN
> [Order Details] ON Orders.OrderID = [Order
>Details].OrderID ON Products.ProductID = [Order Details].ProductID'
> SET @.SQL = @.SQL1 + ' UNION ALL ' + @.SQL2
> EXEC(@.SQL)
>
>
>CODE (GOOD)**********************************
***
>SELECT 'Qtr 1997-1' AS Qtr, COUNT(Orders.ShipName) AS ctShipName,
>COUNT(Orders.ShipCity) AS ctShipCity
>FROM Categories INNER JOIN
> Products ON Categories.CategoryID =
>Products.CategoryID INNER JOIN
> Orders INNER JOIN
> [Order Details] ON Orders.OrderID = [Order
>Details].OrderID ON Products.ProductID = [Order Details].ProductID
>UNION ALL
>SELECT 'Qtr 1997-2' AS Qtr, COUNT(Orders.ShipName) AS ctShipName,
>COUNT(Orders.ShipCity) AS ctShipCity
>FROM Categories INNER JOIN
> Products ON Categories.CategoryID =
>Products.CategoryID INNER JOIN
> Orders INNER JOIN
> [Order Details] ON Orders.OrderID = [Order
>Details].OrderID ON Products.ProductID = [Order Details].ProductID
>
>|||i realize now that i don't need dynamic sql for this satement, but for
learning reasons, what would be the correct syntax for the year and qtr
concatenation using dynamic sql be?
sql quotes are tricky.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1134340924.362906.149210@.g14g2000cwa.googlegroups.com...
> Scott wrote:
>
>
> You don't need dynamic SQL for any of that, so why do it? Extraneous
> quotes etc removed from the following. If you quote it up again it
> should work. It looks like you've left out WHERE clauses for the
> quarters though.
> SELECT 'Qtr' + CAST(@.yearname AS VARCHAR(55)) + '-'
> + CAST(@.qtrname1 AS VARCHAR(55)) AS Quarter,
> COUNT(Orders.ShipName) AS ctShipName,
> COUNT(Orders.ShipCity) AS ctShipCity
> FROM Categories
> INNER JOIN Products
> ON Categories.CategoryID = Products.CategoryID
> INNER JOIN [Order Details]
> ON Products.ProductID = [Order Details].ProductID
> INNER JOIN Orders
> ON Orders.OrderID = [Order Details].OrderID
> UNION ALL
> SELECT 'Qtr' + CAST(@.yearname AS VARCHAR(55)) + '-'
> + CAST(@.qtrname2 AS VARCHAR(55)) AS Quarter,
> COUNT(Orders.ShipName) AS ctShipName,
> COUNT(Orders.ShipCity) AS ctShipCity
> FROM Categories
> INNER JOIN Products
> ON Categories.CategoryID = Products.CategoryID
> INNER JOIN [Order Details]
> ON Products.ProductID = [Order Details].ProductID
> INNER JOIN Orders
> ON Orders.OrderID = [Order Details].OrderID ;
> --
> David Portas
> SQL Server MVP
> --
>|||Erland Sommarskog wrote:
> David, has it never occurred to you that what you see may only be a piece
> of the actual problem? Since I wrote a crosstab query for Scott earlier
> this wend, I strongly suspect that his query will include a dynamic
> number of UNION ALL things.
>
It occurred to me, that's why I asked.
David Portas
SQL Server MVP
--|||Scott (sbailey@.mileslumber.com) writes:
> i realize now that i don't need dynamic sql for this satement, but for
> learning reasons, what would be the correct syntax for the year and qtr
> concatenation using dynamic sql be?
> sql quotes are tricky.
Nah, just overwhelming the more you nest. The basic rule is simple: any
nested quote needs to be doubled.
In addition to everything else, the syntax colouring in Query Analyzer is
helpful here. If an expression like CAST(@.YearName AS VARCHAR(55)) comes out
read, you are an odd number of quotes.
If it is a consolation, also experienced SQL programmers like me have
to fight a battle with all the '. It was oh so easier back in the days
I could use " as well. So that's the real story I don't post any example -
I probably get it wrong. :-)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspxsql

Dynamic queue receive sql ?

Hi There

My activation sp must be able to read of various queues.

I load a variable with the queue name that activated the sp btu i cannot get the syntax working to receive or get a conversation group of a queue name that is a variable.

I have tried:

WAITFOR

(

RECEIVE message_body, conversation_handle, message_type_name, message_sequence_number, conversation_group_id FROM @.callingQueue INTO @.msgTable WHERE conversation_group_id = @.conversationGroup

), TIMEOUT 2000;

But i get this error:

Incorrect syntax near '@.callingQueue'.

Looks like you cannot use a variable.

So i tried the following:

SELECT @.SQL = N' WAITFOR

(

RECEIVE message_body, conversation_handle, message_type_name, message_sequence_number, conversation_group_id FROM @.callingQueue INTO @.msgTable WHERE conversation_group_id = @.conversationGroup

), TIMEOUT 2000'

EXEC sp_executesql @.SQL, N'@.msgTable table output'

But i get the same error.

How do i receive of a queue using a vriable holding the queue name ?

Thanx

Hi,

Just have a look at the following code sample. This sample implements an actviated stored procedure that gets the queue name from the sys.dm_broker_activated_tasks DMV. Maybe you must modify the code a little bit to target your scenario, but the basic structure should meet your requirements.

HTH

Klaus Aschenbrenner
www.csharp.at
http://www.sqljunkies.com/weblog/klaus.aschenbrenner

CREATE PROCEDURE ProcessRequestMessages
AS
DECLARE @.ch UNIQUEIDENTIFIER
DECLARE @.messagetypename NVARCHAR(256)
DECLARE @.messagebody XML
DECLARE @.responsemessage XML
DECLARE @.queue_id INT
DECLARE @.queue_name NVARCHAR(MAX)
DECLARE @.sql NVARCHAR(MAX)
DECLARE @.param_def NVARCHAR(MAX);

-- Determining the queue for that the stored procedure was activated
SELECT @.queue_id = queue_id FROM sys.dm_broker_activated_tasks
WHERE spid = @.@.SPID

SELECT @.queue_name = [name] FROM sys.service_queues
WHERE object_id = @.queue_id

-- Creating the parameter substitution
SET @.param_def = '
@.ch UNIQUEIDENTIFIER OUTPUT,
@.messagetypename NVARCHAR(MAX) OUTPUT,
@.messagebody XML OUTPUT'

-- Creating the dynamic T-SQL statement, which does a query on the actual queue
SET @.sql = '
WAITFOR (
RECEIVE TOP(1)
@.ch = conversation_handle,
@.messagetypename = message_type_name,
@.messagebody = CAST(message_body AS XML)
FROM '
+ @.queue_name + '
), TIMEOUT 1000'

WHILE (1=1)
BEGIN
BEGIN TRANSACTION

-- Executing the dynamic T-SQL statement that contains the actual queue
EXEC sp_executesql
@.sql,
@.param_def,
@.ch = @.ch OUTPUT,
@.messagetypename = @.messagetypename OUTPUT,
@.messagebody = @.messagebody OUTPUT

IF (@.@.ROWCOUNT = 0)
BEGIN
ROLLBACK TRANSACTION
BREAK
END
END

|||

Hi Klaus

I use exactly the same technique, i can see straight away where i went wrong, Thank You

|||

Hi Klaus

Ok next problem.

This does not work:

SELECT @.SQL = N'WAITFOR

(RECEIVE message_body, conversation_handle, message_type_name, message_sequence_number, conversation_group_id FROM ' + @.callingQueue + ' INTO @.msgTable WHERE conversation_group_id = '

+ CAST(@.conversationGroup AS char) + '), TIMEOUT 2000'

EXEC sp_executesql @.SQL, N'@.msgTable TABLE output', @.msgTable out

The reason for this is becuase a table variable cannot be an output.

Now i have to read into a tabel variable because i am expecting many messages. Originally i kept looping to get the next message but i kept getting the first message of the queue , i then saw that it was a good practice to read all message into a table variable and then process them.

So i am a bit stuck i have to use sp_executesql to have a dynamic queue, and i need a table variable becuase i want to process multipel message, but i cannot use a table variable with sp_executesql.?

Please help

|||

Hi Klaus

Just so you can get a better idea, this is what i am trying to do. Not sure how to make it work without a table variable. As mentioned i used to simply do a receive top 1, i would then process the message commit and do another read of the queue but i would keep getting the first message. This was resolved using a table variable which is now a problem for sp_executesql.

BEGIN

SELECT @.SQL = N'WAITFOR

(RECEIVE message_body, conversation_handle, message_type_name, message_sequence_number, conversation_group_id FROM ' + @.callingQueue + ' INTO @.msgTable WHERE conversation_group_id = '

+ CAST(@.conversationGroup AS char) + '), TIMEOUT 2000'

EXEC sp_executesql @.SQL, N'@.msgTable TABLE output', @.msgTable out

IF @.@.ROWCOUNT = 0

BEGIN

ROLLBACK TRAN

BREAK

END

DECLARE message_cursor CURSOR FOR

SELECT message_body , conversation_handle , message_type_name, message_seq_number, conversation_group_id from @.msgTable order by message_seq_number asc

OPEN message_cursor

FETCH NEXT from message_cursor INTO @.msgBody, @.ConvHandle, @.msgType, @.message_seq_number, @.conversationGroup

WHILE @.@.FETCH_STATUS = 0

BEGIN

IF @.msgType = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog'

FETCH NEXT from message_cursor INTO @.msgBody, @.ConvHandle, @.msgType, @.message_seq_number, @.conversationGroup

END

END CONVERSATION @.ConvHandle;

CLOSE message_cursor

DEALLOCATE message_cursor

return

END

|||Hi Kluas please note i dont actually do an end conversation in the if, i have cut the logic out, i just wanted you to get an idea of the recieve and how i use the table variable. Thanx|||

Hi Dietz!

Yes, that's a restriction with the sp_executesql statement: you can't use table variables.
Another option would be to use a temp table instead a table variable - would this make sense to you?

Thanks

Klaus Aschenbrenner
www.csharp.at
www.sqljunkies.com/weblog/klaus.aschenbrenner

|||

Dietz wrote:

SELECT @.SQL = N'WAITFOR

SELECT @.SQL = N'WAITFOR

(RECEIVE message_body, conversation_handle, message_type_name, message_sequence_number, conversation_group_id FROM ' + @.callingQueue + ' INTO @.msgTable WHERE conversation_group_id = '

+ CAST(@.conversationGroup AS char) + '), TIMEOUT 2000'

EXEC sp_executesql @.SQL, N'@.msgTable TABLE output', @.msgTable out

Use QUOTENAME around the @.callingQueue. Use INSERT ... EXEC instead of INTO @.msgTable:

SELECT @.SQL = N'WAITFOR (RECEIVE message_body, conversation_handle, message_type_name, message_sequence_number, conversation_group_id FROM ' + QUOTENAME(@.callingQueue)

+ ' WHERE conversation_group_id = '

+ CAST(@.conversationGroup AS char) + '), TIMEOUT 2000'

INSERT INTO @.msgTable EXEC (@.SQL);

Dietz wrote:

DECLARE message_cursor CURSOR FOR

SELECT message_body , conversation_handle , message_type_name, message_seq_number, conversation_group_id from @.msgTable order by message_seq_number asc

The correct order by is conversation_handle, message_seq_number, to process one conversation at a time:

DECLARE message_cursor CURSOR FOR

SELECT message_body , conversation_handle , message_type_name, message_seq_number, conversation_group_id from @.msgTable order by conversation_handle, message_seq_number asc

HTH,

~ Remus

|||

Hi Remus

That looks great, thank you very much, i will try it today.

There is only ever 1 conversation handle, the converation continues for years so i dont think it is required in the order by. Also is it necessary to order by message_seq_number to ensure messages are processed in the right order? Is this not "built into" service broker, i added it just in case.

|||

The RECEIVE returns the messages in order, but the SELECT ... FROM @.table does not offer any quarantee unless an explicit ORDER BY is provided.

HTH,
~ Remus

|||Yes of course, sorry stupid question. Dont know why i thought you where reffering to the recieve :)

Dynamic query, local cursor variable and global cursors

Hi all.

I am stuck in a bit of a conundrum for quite a while now, and I hope someone here will help me figure this one out.

So, first things first: let me explain what I need to do. I am

designing a web application that will allow users to consult info

available in a SQL2000 database. The user will enter the search

criterea, and hopefully the web page will show matching results.

The problem is the results shown aren't available per se in the DB, I

need to process the data a bit. I decided to do so on the SQL Server

side, though the use of cursors. So, when a user defines his search

criteria, I run a stored procedure that begins by building a dynamic

sql query and creating a cursor for it. I used a global cursor in order

to do so. It looked something like this:

SET @.sqlQuery = ... (build the dinamic sql query)

SET @.cursorQuery = 'DECLARE myCursor CURSOR GLOBAL FAST_FORWARD FOR ' + @.sqlQuery

EXEC @.cursorQuery

OPEN myCursor

FETCH NEXT FROM myCursor INTO ...

CLOSE myCursor

DEALLOCATE myCursor

This works fine, if there's only one instance of the

stored procedure running at a time. Should another user connect to the

site and run a search while someone's at it, it'll fail due to the

atempt to create a cursor with the same name.

My first thought was to make the cursor name unique, which led me to:

...

SET @.cursorName = 'myCursor' + @.uniqueUserID

SET @.cursorQuery = 'DECLARE '+ @.cursorName + 'CURSOR FAST_FORWARD FOR ' + @.sqlQuery

EXEC @.cursorQuery

...

The problem with this is that I can't do a FETCH NEXT FROM @.cursorName since

@.cursorName is a char variable holding the cursor name, and not a

cursor variable. So to enforce this unique name method the only option

I have is to keep creating dynamic sql queries and exucting them. And

this makes the sp a bitch to develop and maintain, and I'm guessing it

doesn't make it very performant.

So I moved on to my second idea: local cursor variables. The problem with

this is that if I create a local cursor variable by executing a dynamic

query, I can't extract it from the EXEC (or sp_executesql) context, as

it offers no output variable.

I guess my concrete questions are:

Is it possible to execute a dynamic sql query and extract a (cursor) variable from it?Is it possible to populate a local cursor variable with a global cursor, by providing the global cursor's name?Can I create a local cursor variable for a dynamic sql query? How?

Anybody sees another way arround this?Thanks in advance,

Carlos

First off, let me just say that I kind of hate myself for the answer I am going to give you, because almost certainly the processing you are trying to do with a cursor in SQL could be done easier/better/faster/etcer outside of T-SQL, and you would be far happier with the final result.

On the other hand, I am pretty sure there is a way to do this, using sp_executesql by passing a parameter of type cursor to the proc:

declare @.query nvarchar(max), @.number int, @.mainCursor cursor

set @.query = ' set @.cursor = cursor for select 1 as number
open @.cursor'

exec sp_executesql @.query,N'@.cursor cursor output',@.mainCursor output

fetch next from @.maincursor into @.number
select @.number

Good luck with this, but seriously consider doing this outside of T-SQL :)

|||Hey Louis.

I can only say I was amazed to see that your piece of code worked,

since I had tried about the same thing a while ago and it didn't work.

So I did a little digging to see what the difference was between your

implementation and mine. And then I discovered something odd: if you

put the OPEN @.cursor after the sp_executesql command, instead of in it, you get an error saying that your variable has no cursor allocated to it. Go figure.

Well, I guess this is part of why you're telling me to give up T-SQL.

Believe me, I'm no masochist. I know this would be much easier if I did

it on the webserver's side, where I have a beautiful JVM eager to do

the job. But I have to disagree when you say it would be faster.

The trouble is that I have to go through a great amount of data to

display but a few lines of result to the user. The overhead involved in

transfering all this data to another system is just too great (it's a

web application, so the time scale is very short: a few seconds will be

enough to hamper it). So I guess I'll have to live with it, right?

Anyways, many thanks for your help. Problem solved. Moving on.

Carlos

Dynamic query to store record count in variable??

Hello everyone,
I am attempting to write a stored procedure to retrieve the record count but
have failed so far. Here's what I have tried...
SET @.value = 'SELECT COUNT(*) FROM ' + @.tablea
EXECUTE sp_executesql @.sql
...but this just ends up displaying the count to the screen, so I did...
SET @.value = 'DECLARE @.tablea_count NVARCHAR(128) SELECT
@.tablea_count=COUNT(*) FROM ' + @.tablea
EXECUTE sp_executesql @.sql, @.tablea_count OUTPUT
but this doesnt seem to work either.
Help?
--
ChrisHave a look here:
http://www.sommarskog.se/dynamic_sql.html
http://www.support.microsoft.com/?id=262499
Andrew J. Kelly SQL MVP
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:292BA190-F8FD-4696-A6EA-8EE930095F5A@.microsoft.com...
> Hello everyone,
> I am attempting to write a stored procedure to retrieve the record count
> but
> have failed so far. Here's what I have tried...
> SET @.value = 'SELECT COUNT(*) FROM ' + @.tablea
> EXECUTE sp_executesql @.sql
> ...but this just ends up displaying the count to the screen, so I did...
> SET @.value = 'DECLARE @.tablea_count NVARCHAR(128) SELECT
> @.tablea_count=COUNT(*) FROM ' + @.tablea
> EXECUTE sp_executesql @.sql, @.tablea_count OUTPUT
> but this doesnt seem to work either.
> Help?
> --
> Chris

Dynamic query to store record count in variable??

Hello everyone,
I am attempting to write a stored procedure to retrieve the record count but
have failed so far. Here's what I have tried...
SET @.value = 'SELECT COUNT(*) FROM ' + @.tablea
EXECUTE sp_executesql @.sql
...but this just ends up displaying the count to the screen, so I did...
SET @.value = 'DECLARE @.tablea_count NVARCHAR(128) SELECT
@.tablea_count=COUNT(*) FROM ' + @.tablea
EXECUTE sp_executesql @.sql, @.tablea_count OUTPUT
but this doesnt seem to work either.
Help?
Chris
Have a look here:
http://www.sommarskog.se/dynamic_sql.html
http://www.support.microsoft.com/?id=262499
Andrew J. Kelly SQL MVP
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:292BA190-F8FD-4696-A6EA-8EE930095F5A@.microsoft.com...
> Hello everyone,
> I am attempting to write a stored procedure to retrieve the record count
> but
> have failed so far. Here's what I have tried...
> SET @.value = 'SELECT COUNT(*) FROM ' + @.tablea
> EXECUTE sp_executesql @.sql
> ...but this just ends up displaying the count to the screen, so I did...
> SET @.value = 'DECLARE @.tablea_count NVARCHAR(128) SELECT
> @.tablea_count=COUNT(*) FROM ' + @.tablea
> EXECUTE sp_executesql @.sql, @.tablea_count OUTPUT
> but this doesnt seem to work either.
> Help?
> --
> Chris
sql

Dynamic query to store record count in variable??

Hello everyone,
I am attempting to write a stored procedure to retrieve the record count but
have failed so far. Here's what I have tried...
SET @.value = 'SELECT COUNT(*) FROM ' + @.tablea
EXECUTE sp_executesql @.sql
...but this just ends up displaying the count to the screen, so I did...
SET @.value = 'DECLARE @.tablea_count NVARCHAR(128) SELECT
@.tablea_count=COUNT(*) FROM ' + @.tablea
EXECUTE sp_executesql @.sql, @.tablea_count OUTPUT
but this doesnt seem to work either.
Help?
--
ChrisHave a look here:
http://www.sommarskog.se/dynamic_sql.html
http://www.support.microsoft.com/?id=262499
--
Andrew J. Kelly SQL MVP
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:292BA190-F8FD-4696-A6EA-8EE930095F5A@.microsoft.com...
> Hello everyone,
> I am attempting to write a stored procedure to retrieve the record count
> but
> have failed so far. Here's what I have tried...
> SET @.value = 'SELECT COUNT(*) FROM ' + @.tablea
> EXECUTE sp_executesql @.sql
> ...but this just ends up displaying the count to the screen, so I did...
> SET @.value = 'DECLARE @.tablea_count NVARCHAR(128) SELECT
> @.tablea_count=COUNT(*) FROM ' + @.tablea
> EXECUTE sp_executesql @.sql, @.tablea_count OUTPUT
> but this doesnt seem to work either.
> Help?
> --
> Chris

Dynamic Query in View

Can I use variable name in the view? because the result set is depend on
different database not the same database, so I am wondering if I can use
this as a dynanice query in the view then my problem would be solved.
Thanks in advance
Views cannot have parameters, but table-valued user-defined functions can.
They don't necessarily have the same performance as views, but this sounds
like what you want. You can do subqueries on TVF too, unlike resultsets that
are returned from stored procedures.
Bob Beauchemin
http://www.SQLskills.com/blogs/bobb
"Rogers" <naissani@.hotmail.com> wrote in message
news:uF1y3UlLGHA.3100@.tk2msftngp13.phx.gbl...
> Can I use variable name in the view? because the result set is depend on
> different database not the same database, so I am wondering if I can use
> this as a dynanice query in the view then my problem would be solved.
> Thanks in advance
>

Dynamic Properties Task in DTS 2000, need to convert it to SSIS

I have a Dynamic propeties task in dts 2000 that process/executes a global variable.

The global variable basically executes a bat file.

How do i set this up in ssis. The migration failed to properly convert this task.

Please help.

Thank you.

To execute a .bat file you would use the Execute Process task.|||

I don't think I can use the process task because I have a global variable which gets set in a previous task. It is via this global variable that a bat file is called that copies a file from one location to another. The issue is that how do I execute the global variable. In dts sql 2000, the dynamice properties task is used. but in ssis that does not work. the process task does not allow you to execute/process aglobal variable....

hope I was able to explain better. Any help is appreciated.

Thanks

|||You'll have to use expressions on the Execute Process Task. You'll likely have to create two variables based off of your global variable first, though. That is, the execute process takes two arguments at a minimum, the executable (cmd.exe perhaps) and its arguments (your bat file). Then, just pass in the two new variables into the appropriate expressions (Executable & Arguments)|||

Thanks Phil,

I understand what you said, but not quite sure how to do it...

my global varaible is called gv_commandline

the value is '\\......\..\.... .bat \\..............\\......... .txt \\............\\.............\\.... .txt

(batch file) (1st arg) (2nd arg)

basically the batch file will copy a file from the source (ie the 1st arg) to the destination (ie 2nd arg)

For instance, how do I set the two new variables with values from my global variable.

How do I set the expressions

Thanks in advance.

Jinita

|||

Jain wrote:

Thanks Phil,

I understand what you said, but not quite sure how to do it...

my global varaible is called gv_commandline

the value is '\\......\..\.... .bat \\..............\\......... .txt \\............\\.............\\.... .txt

(batch file) (1st arg) (2nd arg)

basically the batch file will copy a file from the source (ie the 1st arg) to the destination (ie 2nd arg)

Thanks in advance.

Jinita

Actually, just try putting your global variable in the expression for Argument. Right click on the Execute Process Task and select properties. Find the expression parameter and click on the "...". Find "Arguments" and in that box, just drag your global variable to it. Then, click out of that and double click on the execute process task to configure it. For the executable, type in: c:\windows\system32\cmd.exe|||

Thanks Phil,

That was awesome. Looks like it worked. the task seems to work, I will test the complete package run just to make sure everything works fine.

Great. Thank you so much.

Have a great weekend :)

Jinita

|||

Hi Phil,

Apologise for reopening this issue, but I am having problems with the task. I have done what you suggested before, but when I try to execute that process task, it comes up with the cmd.exe window waiting for the command or argument. Shouldn't it take the argument and execute the whole thing. Please help.

Thank you.

|||

I am still running into the same problem. Why does the command prompt come up. I am expecting it to execute the process task since i have already provided it with an executable and arguments.

Please advice.

Thanks in advance.

Dynamic Properties Task in DTS 2000, need to convert it to SSIS

I have a Dynamic propeties task in dts 2000 that process/executes a global variable.

The global variable basically executes a bat file.

How do i set this up in ssis. The migration failed to properly convert this task.

Please help.

Thank you.

To execute a .bat file you would use the Execute Process task.|||

I don't think I can use the process task because I have a global variable which gets set in a previous task. It is via this global variable that a bat file is called that copies a file from one location to another. The issue is that how do I execute the global variable. In dts sql 2000, the dynamice properties task is used. but in ssis that does not work. the process task does not allow you to execute/process aglobal variable....

hope I was able to explain better. Any help is appreciated.

Thanks

|||You'll have to use expressions on the Execute Process Task. You'll likely have to create two variables based off of your global variable first, though. That is, the execute process takes two arguments at a minimum, the executable (cmd.exe perhaps) and its arguments (your bat file). Then, just pass in the two new variables into the appropriate expressions (Executable & Arguments)|||

Thanks Phil,

I understand what you said, but not quite sure how to do it...

my global varaible is called gv_commandline

the value is '\\......\..\.... .bat \\..............\\......... .txt \\............\\.............\\.... .txt

(batch file) (1st arg) (2nd arg)

basically the batch file will copy a file from the source (ie the 1st arg) to the destination (ie 2nd arg)

For instance, how do I set the two new variables with values from my global variable.

How do I set the expressions

Thanks in advance.

Jinita

|||

Jain wrote:

Thanks Phil,

I understand what you said, but not quite sure how to do it...

my global varaible is called gv_commandline

the value is '\\......\..\.... .bat \\..............\\......... .txt \\............\\.............\\.... .txt

(batch file) (1st arg) (2nd arg)

basically the batch file will copy a file from the source (ie the 1st arg) to the destination (ie 2nd arg)

Thanks in advance.

Jinita

Actually, just try putting your global variable in the expression for Argument. Right click on the Execute Process Task and select properties. Find the expression parameter and click on the "...". Find "Arguments" and in that box, just drag your global variable to it. Then, click out of that and double click on the execute process task to configure it. For the executable, type in: c:\windows\system32\cmd.exe|||

Thanks Phil,

That was awesome. Looks like it worked. the task seems to work, I will test the complete package run just to make sure everything works fine.

Great. Thank you so much.

Have a great weekend :)

Jinita

|||

Hi Phil,

Apologise for reopening this issue, but I am having problems with the task. I have done what you suggested before, but when I try to execute that process task, it comes up with the cmd.exe window waiting for the command or argument. Shouldn't it take the argument and execute the whole thing. Please help.

Thank you.

|||

I am still running into the same problem. Why does the command prompt come up. I am expecting it to execute the process task since i have already provided it with an executable and arguments.

Please advice.

Thanks in advance.

Dynamic Properties Task in DTS 2000, need to convert it to SSIS

I have a Dynamic propeties task in dts 2000 that process/executes a global variable.

The global variable basically executes a bat file.

How do i set this up in ssis. The migration failed to properly convert this task.

Please help.

Thank you.

To execute a .bat file you would use the Execute Process task.|||

I don't think I can use the process task because I have a global variable which gets set in a previous task. It is via this global variable that a bat file is called that copies a file from one location to another. The issue is that how do I execute the global variable. In dts sql 2000, the dynamice properties task is used. but in ssis that does not work. the process task does not allow you to execute/process aglobal variable....

hope I was able to explain better. Any help is appreciated.

Thanks

|||You'll have to use expressions on the Execute Process Task. You'll likely have to create two variables based off of your global variable first, though. That is, the execute process takes two arguments at a minimum, the executable (cmd.exe perhaps) and its arguments (your bat file). Then, just pass in the two new variables into the appropriate expressions (Executable & Arguments)|||

Thanks Phil,

I understand what you said, but not quite sure how to do it...

my global varaible is called gv_commandline

the value is '\\......\..\.... .bat \\..............\\......... .txt \\............\\.............\\.... .txt

(batch file) (1st arg) (2nd arg)

basically the batch file will copy a file from the source (ie the 1st arg) to the destination (ie 2nd arg)

For instance, how do I set the two new variables with values from my global variable.

How do I set the expressions

Thanks in advance.

Jinita

|||

Jain wrote:

Thanks Phil,

I understand what you said, but not quite sure how to do it...

my global varaible is called gv_commandline

the value is '\\......\..\.... .bat \\..............\\......... .txt \\............\\.............\\.... .txt

(batch file) (1st arg) (2nd arg)

basically the batch file will copy a file from the source (ie the 1st arg) to the destination (ie 2nd arg)

Thanks in advance.

Jinita

Actually, just try putting your global variable in the expression for Argument. Right click on the Execute Process Task and select properties. Find the expression parameter and click on the "...". Find "Arguments" and in that box, just drag your global variable to it. Then, click out of that and double click on the execute process task to configure it. For the executable, type in: c:\windows\system32\cmd.exe|||

Thanks Phil,

That was awesome. Looks like it worked. the task seems to work, I will test the complete package run just to make sure everything works fine.

Great. Thank you so much.

Have a great weekend :)

Jinita

|||

Hi Phil,

Apologise for reopening this issue, but I am having problems with the task. I have done what you suggested before, but when I try to execute that process task, it comes up with the cmd.exe window waiting for the command or argument. Shouldn't it take the argument and execute the whole thing. Please help.

Thank you.

|||

I am still running into the same problem. Why does the command prompt come up. I am expecting it to execute the process task since i have already provided it with an executable and arguments.

Please advice.

Thanks in advance.

sql

Dynamic Mapping For Source/Destination

Hello,

What I'm trying to accomplish is to have a variable names "SourceTable" and "DestinationTable". So for each SourceTable, the DestinationTable will have the same columns. All I need is to auto-map these columns between source and destination via code?

Is this possible?

Thanks,

awiora

You can do it, but not in the same package. Packages can't modify themselves while they are running. You can, however, use a package (with some code in it) to generate and call another package. Take a look at this post to see an example.

http://blogs.conchango.com/jamiethomson/archive/2007/03/28/SSIS_3A00_-Building-Packages-Programatically.aspx

|||

Exactly what I was looking for.

Thank you... Much Appreciated.

Dynamic IDENTITY seed

Hi all,
Does anyone know if it is possible to set identity seed using a variable, an
d if not what is the alternative? I would not like to use Dynamic SQL. I can
not get any of the following to work.
For example,
---
Declare @.MySeed int
Select @.MySeed = Max(SomeField) From SomeTable
Declare @.MyVariableTbl Table
(
UniqueID int Identity(@.MySeed, 1) Primary Key Clustered Not Null
)
---
Error Msg:
Server: Msg 170, Level 15, State 1, Line 6
Line 6: Incorrect syntax near '@.MySeed'.
OR,
---
Declare @.MySeed int
Select @.MySeed = Max(SomeField) From SomeTable
Declare @.MyVariableTbl Table
(
UniqueID int Identity(1, 1) Primary Key Clustered Not Null
)
DBCC CHECKIDENT (@.MyTableVariable, RESEED, @.NewSeed)
---
Error Msg:
Server: Msg 2501, Level 16, State 2, Line 13
Could not find a table or object named '@.MyVariableTbl'. Check sysobjects.
DBCC execution completed. If DBCC printed error messages, contact your syste
m administrator.
TIA
Goran DjuranovicGoran Djuranovic wrote:
> Hi all,
> Does anyone know if it is possible to set identity seed using a variable, and if n
ot what is the alternative? I would not like to use Dynamic SQL. I cannot get any of
the following to work.
What's your problem with using dynamic DDL?
Kind regards
robert|||Because I would have to write the whole SP as a dynamic SQL? I am using a bu
nch of table variables, XML pointers and some other stuff. It is just too co
mplex to be all in Dynamic SQL.
I found one way to do it by using temp table:
---
Create Table #MyTempTbl
(
UniqueID int Identity(1, 1) Primary Key Clustered Not Null
)
Declare @.MySeed int
Select @.MySeed = Max(SomeField) From SomeTable
DBCC CHECKIDENT ('#MyTempTbl', RESEED, @.NewSeed)
---
BUT, I would really like to use table variable instead.
Thanks for your response.
Goran Djuranovic
"Robert Klemme" <bob.news@.gmx.net> wrote in message news:%23qep8$1TGHA.6048@.TK2MSFTNGP11.ph
x.gbl...
> Goran Djuranovic wrote:
>
> What's your problem with using dynamic DDL?
>
> Kind regards
>
> robert|||What you're asking for is not possible. You cannot reseed the ident for @.tb
and dynamic @.tb creation won't help either because the @.tb is bound to that
execution context.
--
-oj
"Goran Djuranovic" <goran.djuranovic@.newsgroups.nospam> wrote in message new
s:uko3bv2TGHA.5972@.TK2MSFTNGP10.phx.gbl...
Because I would have to write the whole SP as a dynamic SQL? I am using a bu
nch of table variables, XML pointers and some other stuff. It is just too co
mplex to be all in Dynamic SQL.
I found one way to do it by using temp table:
---
Create Table #MyTempTbl
(
UniqueID int Identity(1, 1) Primary Key Clustered Not Null
)
Declare @.MySeed int
Select @.MySeed = Max(SomeField) From SomeTable
DBCC CHECKIDENT ('#MyTempTbl', RESEED, @.NewSeed)
---
BUT, I would really like to use table variable instead.
Thanks for your response.
Goran Djuranovic
"Robert Klemme" <bob.news@.gmx.net> wrote in message news:%23qep8$1TGHA.6048@.TK2MSFTNGP11.ph
x.gbl...
> Goran Djuranovic wrote:
>
> What's your problem with using dynamic DDL?
>
> Kind regards
>
> robert|||"Goran Djuranovic" <goran.djuranovic@.newsgroups.nospam> wrote in message
news:%237OGE21TGHA.4952@.TK2MSFTNGP09.phx.gbl...
Hi all,
Does anyone know if it is possible to set identity seed using a variable,
and if not what is the alternative? I would not like to use Dynamic SQL. I
cannot get any of the following to work.
For example,
---
Declare @.MySeed int
Select @.MySeed = Max(SomeField) From SomeTable
Declare @.MyVariableTbl Table
(
UniqueID int Identity(@.MySeed, 1) Primary Key Clustered Not Null
)
---
Error Msg:
Server: Msg 170, Level 15, State 1, Line 6
Line 6: Incorrect syntax near '@.MySeed'.
OR,
---
Declare @.MySeed int
Select @.MySeed = Max(SomeField) From SomeTable
Declare @.MyVariableTbl Table
(
UniqueID int Identity(1, 1) Primary Key Clustered Not Null
)
DBCC CHECKIDENT (@.MyTableVariable, RESEED, @.NewSeed)
---
Error Msg:
Server: Msg 2501, Level 16, State 2, Line 13
Could not find a table or object named '@.MyVariableTbl'. Check sysobjects.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
TIA
Goran Djuranovic
If your table was a permanent or temporary one rather than a table variable
then the following would have the same effect (almost). Although the seed
isn't changed directly the next value inserted will take on the value of
MAX(somecol)+increment. AFAIK you can't do this with a table variable. If
this doesn't help then maybe you could explain a bit more about what you are
trying to achieve.
SET IDENTITY_INSERT your_table ON ;
BEGIN TRAN ;
INSERT INTO your_table (uniqueid)
SELECT MAX(somecol)
FROM other_table ;
ROLLBACK TRAN ;
SET IDENTITY_INSERT your_table OFF ;
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||[Reposted, as posts from outside msnews.microsoft.com does not seem to make
it in.]
Goran Djuranovic (goran.djuranovic@.newsgroups.nospam) writes:
> Does anyone know if it is possible to set identity seed using a variable,
> and if not what is the alternative?
To suggest alternatives it would be very helpful to know what you are
really wahy you are trying to achieve. Setting the seed dynamically
sounds like a very odd request, so there is a good chance that the
solution to your real problem is entirely different.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.seBooks Online for SQL
Server 2005
athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000
athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||Hi Erland,
First, thanks for your response. Here is the situation so far :-) :
- I insert 5 records into a table (Player) with identity column (PlayerID) a
nd name, so for example, the identities for those records get created as 3,4
,5,6,7.
- I also have a mapping table (PlayerMapping) with 3 fields (ExternalTeamID,
ExternalPlayerID, PlayerID), all INTs, no identity.
Now, what I need to do is the following:
- After the first insert in Player, I also need to insert those identities (
3,4,5,6,7) into a PlayerMapping's PlayerID column. Don't worry about Externa
lTeamID and ExternalPlayerID values.
So in a nutshell, if records a successfully inserted into Player table, thei
r keys need to be mapped into PlayerMapping table.
Here is the code excerpt from the sproc:
****************************************
******************************
/** Assign last identity value from PlayerTest table. Used for reseeding #Pl
ayerMappingInsertTbl table. **/
If (Select Count(1) From Player) > 1
Begin
Set @.LastIdentityInt = Ident_Current('Player') + 1
End
Else
Begin
Set @.LastIdentityInt = Ident_Current('Player')
End
/** Insert Players that need to be inserted and are valid. **/
Insert Into Player (LeagueID, Name)
Select
LeagueID,
Name
From @.PlayerTbl PlayerTbl
Where IsValid = '1' And OperationToDo = 'Insert'
Order By RowID
/** Insert Players into mapping table. **/
Create Table #PlayerMappingInsertTbl
(
ExternalTeamID varchar (20) Not Null,
ExternalPlayerID varchar (20) Not Null,
PlayerID int Identity(0, 1) Primary Key Clustered Not Null
)
DBCC CheckIdent ('#PlayerMappingInsertTbl', ReSeed, @.LastIdentityInt)
Insert Into #PlayerMappingInsertTbl(ExternalTeamID, ExternalPlayerID)
Select
ExternalTeamID,
ExternalPlayerID
From @.PlayerTbl PlayerTbl
Where IsValid = '1' And OperationToDo = 'Insert'
Order By RowID
Insert Into PlayerMapping
Select
ExternalTeamID,
ExternalPlayerID,
PlayerID
From #PlayerMappingInsertTbl
Drop Table #PlayerMappingInsertTbl
****************************************
*********************************
Thanks again
Goran Djuranovic
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message news:Xns9792E42A55D38Yazorman@.1
27.0.0.1...
> [Reposted, as posts from outside msnews.microsoft.com does not seem to mak
e
> it in.]
>
>
>
> Goran Djuranovic (goran.djuranovic@.newsgroups.nospam) writes:
>
> To suggest alternatives it would be very helpful to know what you are
> really wahy you are trying to achieve. Setting the seed dynamically
> sounds like a very odd request, so there is a good chance that the
> solution to your real problem is entirely different.
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.seBooks Online for SQ
L
> Server 2005
> athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.ms
px
> Books Online for SQL Server 2000
> athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||Please include DDL with future posts so that we don't have to guess your
table structures, keys and so forth.
Logically it looks like you don't need the temp table or the dynamic
IDENTITY value to do this. I'm guessing of course because I haven't seen
your table structures (did I mention how important it it to post DDL? :-).
Try:
/** Insert Players that need to be inserted and are valid. **/
INSERT INTO Player (leagueid, name)
SELECT DISTINCT leagueid, name
FROM @.PlayerTbl
WHERE IsValid = '1'
AND operationtodo = 'Insert' ;
INSERT INTO PlayerMapping (externalteamid, externalplayerid, playerid)
SELECT T.externalteamid, T.externalplayerid, P.playerid
FROM @.PlayerTbl T
JOIN Player AS P
ON T.name = P.name
AND T.leagueid = P.leagueid
WHERE isvalid = '1'
AND operationtodo = 'Insert' ;
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx|||Hi David,
In terms of DDL, I was just testing your brains. :-))))))) And, you did fine
:-)))))))
The code you sent below is exactly what I need. I totally forgot about JOIN
to LeagueID (mostly because I didn't have it in my @.PlayerTbl, but I was
able to add it after your suggestion). Your suggestion is as clean and
elegant as Einstein's E=m*c2. :-)))))
Seriously, thank you very much for your suggestion.
Goran Djuranovic
P.S. Some SQL problems can be solved even without DDL. :-)))))
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:eCdYt3bUGHA.4740@.TK2MSFTNGP14.phx.gbl...
> Please include DDL with future posts so that we don't have to guess your
> table structures, keys and so forth.
> Logically it looks like you don't need the temp table or the dynamic
> IDENTITY value to do this. I'm guessing of course because I haven't seen
> your table structures (did I mention how important it it to post DDL? :-).
> Try:
> /** Insert Players that need to be inserted and are valid. **/
> INSERT INTO Player (leagueid, name)
> SELECT DISTINCT leagueid, name
> FROM @.PlayerTbl
> WHERE IsValid = '1'
> AND operationtodo = 'Insert' ;
> INSERT INTO PlayerMapping (externalteamid, externalplayerid, playerid)
> SELECT T.externalteamid, T.externalplayerid, P.playerid
> FROM @.PlayerTbl T
> JOIN Player AS P
> ON T.name = P.name
> AND T.leagueid = P.leagueid
> WHERE isvalid = '1'
> AND operationtodo = 'Insert' ;
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||Goran Djuranovic wrote:
> Hi David,
> In terms of DDL, I was just testing your brains. :-))))))) And, you did fi
ne
> :-)))))))
> The code you sent below is exactly what I need. I totally forgot about JOI
N
> to LeagueID (mostly because I didn't have it in my @.PlayerTbl, but I was
> able to add it after your suggestion). Your suggestion is as clean and
> elegant as Einstein's E=m*c2. :-)))))
> Seriously, thank you very much for your suggestion.
> Goran Djuranovic
> P.S. Some SQL problems can be solved even without DDL. :-)))))
>
One thing I should have mentioned is that my solution may fail if
(leagueid, name) isn't unique in the Players table. Make sure you
declare a UNIQUE key on those two columns. That's an example of why
accurate DDL is important - if I'd known the keys I wouldn't have had
to make an assumption which may not be valid. (Although for reasons of
good design that key is pretty much a given anyway based on your source
code).
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Dynamic FLOWR XQuery into an xml variable

Hello.
I'm trying to do something I haven't seen anywhere, and have been
trying everything but no joy. Basically, my XML is in a variable, and
I'm querying that variable to filter it further. The problem is that
these filters are dynamic. I want to keep the results as an xml
variable because I want to work with them further after this query (I
will do an xpath query so I can use position() and return a page). I'd
really like to stay away from using a tmp table for this.
See the code inline for a few of the things I've tried. I appreciate
any help on this...
Thanks very much in advance for anyone taking the time to help.
Relevant Code with sample xml filling the var
This should be ready to run (and break on the dynamic attempts)
Move the close comments (*/) around to try one method at a time
---
DECLARE @.ItemXML xml
SET @.ItemXML = '
<itemdata>
<item>
<rank>558</rank>
<itemid>11111111</itemid>
<catid>1</catid>
<pid>11</pid>
<ctid>1</ctid>
<link>http://www.foo.com</link>
<thumburl>http://foo.com/thumb.jpg</thumburl>
<title>Foo title 1</title>
<published>2006-04-05T15:52:17</published>
<provider>foo provider #1</provider>
</item>
<item>
<rank>558</rank>
<itemid>22222222</itemid>
<catid>22</catid>
<pid>50</pid>
<ctid>2</ctid>
<link>http://www.foo.com</link>
<thumburl>http://foo.com/thumb.jpg</thumburl>
<title>Foo title 2</title>
<published>2006-04-05T15:52:17</published>
<provider>foo provider #2</provider>
</item>
<item>
<rank>558</rank>
<itemid>333333333</itemid>
<catid>33</catid>
<pid>50</pid>
<ctid>3</ctid>
<link>http://www.foo.com</link>
<thumburl>http://foo.com/thumb.jpg</thumburl>
<title>Foo title 3</title>
<published>2006-04-05T15:52:17</published>
<provider>foo provider #3</provider>
</item>
</itemdata>'
DECLARE @.CatID int, @.ProviderID int, @.CTID int
-- JUST SET 1 VAR FOR NOW, BUT THIS IS BUILT TO ALLOW MULTIPLE FILTERS
SET @.CTID = 2
DECLARE @.ItemStmt varchar(500), @.XQuery varchar(300), @.WhereStmt
varchar(200)
SET @.WhereStmt = 'where 1 = 1'
IF @.CatID IS NOT NULL
SET @.WhereStmt = @.WhereStmt + ' and $i/catid[1] =
'+CONVERT(varchar(4),@.CatID)
IF @.ProviderID IS NOT NULL
SET @.WhereStmt = @.WhereStmt + ' and $i/pid[1] =
'+CONVERT(varchar(4),@.ProviderID)
IF @.CTID IS NOT NULL
SET @.WhereStmt = @.WhereStmt + ' and $i/ctid[1] =
'+CONVERT(varchar(4),@.CTID)
/*
Base Query with no dynamic vars... this works
*/
SET @.ItemXML = @.ItemXML.query('<itemdata>{
for $i in itemdata[1]/item
where 1 = 1 and $i/ctid[1] = 2
return $i
}</itemdata>
')
SELECT 'Attempt #1: Works',@.ItemXML
/*
Attempt #2... sort of like mrorke at
http://blogs.msdn.com/mrorke/archiv.../24/484237.aspx
EXEC the entire SET @.Var statement.
This results in "Must declare the scalar variable "@.ItemXML"."
-- move close comment here:
SET @.XQuery = '<itemdata>{
for $i in itemdata[1]/item
'+@.WhereStmt+'
return $i
}
</itemdata>'
SET @.XQuery = 'SET @.ItemXML = @.ItemXML.query('''+@.XQuery+''')'
EXEC(@.XQuery)
SELECT 'Attempt #2: Breaks', @.ItemXML
*/
/*
Attempt #3... Try a sql:variable inline
Result: XQuery [query()]: Syntax error near 'sql', expected 'where',
'(stable) order by' or 'return'.
-- move close comment here:
SET @.ItemXML = @.ItemXML.query('<itemdata>{
for $i in itemdata[1]/item
sql:variable("@.WhereStmt")
return $i
}
</itemdata>')
SELECT 'Attempt #3: Breaks', @.ItemXML
*/Rather than using EXEC(@.XQuery), try using this
EXEC sp_executeSQL @.XQuery,N'@.ItemXML XML OUTPUT',@.ItemXML OUTPUT
You'll have to declare @.XQuery as nvarchar instead of varchar|||Markc. Thank you very much for your response. That did exactly what I
needed it to do.
One problem I'm seeing now is in my next step where I apply an xpath to
the @.ItemXML just to do the pagination. It seems that xpath step then
accounts for 85% of my query cost. I'm trying another approach just to
be sure I've covered all my bases and can get the most performant
solution, and I'm running into trouble because I'm still learning the
xml datatype & some other SQL 2005 features. Anyway, I though I'd try a
query against the xml variable, while also joining to my lookup tables
and creating a rownumber for pagination. I've tried several different
syntax with this, but it keeps breaking with "Invalid object name 'R'",
so if anyone can see what I'm doing wrong, again, I'd greatly
appreciate it.
SELECT ROW_NUMBER() OVER (ORDER BY rank DESC) AS RowNumber,
i.value('rank','int'),
i.value('itemid','int'),
i.value('catid','int'),
dbo.c.vch_categorytype AS cat,
i.value('pid','int'),
dbo.p.vch_providername_public AS provider,
i.value('ctid','int'),
i.value('link','varchar(300)'),
i.value('thumburl','varchar(300)'),
i.value('title','varchar(500)'),
i.value('published','datetime')
FROM R cross apply @.ItemXML.nodes('itemdata') R(i)
JOIN Categories_LU c ON R.i.value('catid','int') = c.i_category_id
JOIN Providers_LU p ON R.i.value('pid','int') = p.i_provider_id
WHERE RowNumber BETWEEN 1 AND 20 -- assume page 1, items 1-20
AND R.i.value('ctid','int') = 1 -- this would eventually be a dynamic
where clause|||I think
FROM R cross apply @.ItemXML.nodes('itemdata') R(i)
should be
FROM @.ItemXML.nodes('itemdata') R(i)
However, I believe there are other problems here as well. Suggest you
post the DDL for the two tables and some sample data.
Regards
Mark|||Sorry... I know @.ItemXML.nodes('itemdata') won't return the nodes... I
was thrashing around trying different things. Here are some other ways
I've tried to get this...
FROM R cross apply @.ItemXML.nodes('itemdata/item') R(i)
FROM FooTblNm cross apply @.ItemXML.nodes('itemdata/item') R(i)
... also have mixed up R.i.value and i.value... Nothing made a
difference. Always came back with the table ("R", "FooTblNm") being an
invalid object. Sure, I know answer #1 is I need to read a book so I
don't have to guess on syntax. :-)
Thanks again,
STA|||Here's a complete query with tmp table data...
Again, thanks for your help.
----
--
DECLARE @.ItemXML xml
SET @.ItemXML = '
<itemdata>
<item>
<rank>558</rank>
<itemid>11111111</itemid>
<catid>1</catid>
<pid>11</pid>
<ctid>1</ctid>
<link>http://www.foo.com</link>
<thumburl>http://foo.com/thumb.jpg</thumburl>
<title>Foo title 1</title>
<published>2006-04-05T15:52:17</published>
</item>
<item>
<rank>558</rank>
<itemid>22222222</itemid>
<catid>22</catid>
<pid>50</pid>
<ctid>2</ctid>
<link>http://www.foo.com</link>
<thumburl>http://foo.com/thumb.jpg</thumburl>
<title>Foo title 2</title>
<published>2006-04-05T15:52:17</published>
</item>
<item>
<rank>558</rank>
<itemid>333333333</itemid>
<catid>33</catid>
<pid>50</pid>
<ctid>3</ctid>
<link>http://www.foo.com</link>
<thumburl>http://foo.com/thumb.jpg</thumburl>
<title>Foo title 3</title>
<published>2006-04-05T15:52:17</published>
</item>
</itemdata>'
-- CHECK TO SEE IF #tmps ARE THERE ALREADY...
-- THE PROBABLY DIDN'T GET DROPPED BECAUSE THE QUERY BROKE
IF object_id('tempdb..#tmpCats') IS NULL
BEGIN
CREATE TABLE #tmpCats (i_category_id int, vch_category
varchar(100))
INSERT #tmpCats VALUES (1,'Category 1')
INSERT #tmpCats VALUES (2,'Category 2')
INSERT #tmpCats VALUES (3,'Category 3')
END
IF object_id('tempdb..#tmpProviders') IS NULL
BEGIN
CREATE TABLE #tmpProviders (i_provider_id int, vch_provider
varchar(100))
INSERT #tmpProviders VALUES (1,'Provider 1')
INSERT #tmpProviders VALUES (2,'Provider 2')
INSERT #tmpProviders VALUES (3,'Provider 3')
END
SELECT ROW_NUMBER() OVER (ORDER BY rank DESC) AS RowNumber,
i.value('rank','int'),
i.value('itemid','int'),
i.value('catid','int'),
c.vch_category AS cat,
i.value('pid','int'),
p.vch_provider AS provider,
i.value('ctid','int'),
i.value('link','varchar(300)'),
i.value('thumburl','varchar(300)'),
i.value('title','varchar(500)'),
i.value('published','datetime')
FROM R cross apply @.ItemXML.nodes('itemdata/item') R(i)
JOIN #tmpCats c ON R.i.value('catid','int') = c.i_category_id
JOIN #tmpProviders p ON R.i.value('pid','int') = p.i_provider_id
WHERE RowNumber BETWEEN 1 AND 20 -- assume page 1, items 1-20
AND R.i.value('ctid','int') = 1 -- eventually a dynamic where clause
DROP TABLE #tmpCats
DROP TABLE #tmpProviders|||This should help get you started
;
WITH
XMLNodes(RowNumber,rank,itemid,catid,cat
,pid,provider,ctid,link,thumburl,tit
le,published)
AS(
SELECT ROW_NUMBER() OVER (ORDER BY i.value('rank[1]','int') DESC) AS
RowNumber,
i.value('rank[1]','int'),
i.value('itemid[1]','int'),
i.value('catid[1]','int'),
c.vch_category,
i.value('pid[1]','int'),
p.vch_provider,
i.value('ctid[1]','int'),
i.value('link[1]','varchar(300)'),
i.value('thumburl[1]','varchar(300)'),
i.value('title[1]','varchar(500)'),
i.value('published[1]','datetime')
FROM @.ItemXML.nodes('/itemdata/item') R(i)
JOIN #tmpCats c ON R.i.value('catid[1]','int') = c.i_category_id
JOIN #tmpProviders p ON R.i.value('pid[1]','int') = p.i_provider_id
WHERE R.i.value('ctid[1]','int') = 1
)
SELECT RowNumber,
rank,
itemid,
catid,
cat,
pid,
ctid,
link,
thumburl,
title,
published
FROM XMLNodes
WHERE RowNumber BETWEEN 1 AND 20|||mark,
You absolutely rock! Thank you so much for your help on this.
STA

Dynamic Data Source in DTS

I want to build a DTS package to transfer data between two
SQL 2000 servers. I like to set up the data source as a
variable. In another word, I want to pass in the data
source (source SQL server 2000) as a parameter to the DTS
package so that I can reuse this package somewhere else.
Is it possible? How?
Thanks in advance,
LixinIn short, you could use global variables to specify the data source. When
you invoke the package using DTSRUN, you could specify values for these
global variables. See SQLDTS.com for examples.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
What hardware is your SQL Server running on?
http://vyaskn.tripod.com/poll.htm
"Lixin Fan" <lixin2003@.hotmail.com> wrote in message
news:1d88601c388eb$05101ce0$a601280a@.phx.gbl...
I want to build a DTS package to transfer data between two
SQL 2000 servers. I like to set up the data source as a
variable. In another word, I want to pass in the data
source (source SQL server 2000) as a parameter to the DTS
package so that I can reuse this package somewhere else.
Is it possible? How?
Thanks in advance,
Lixin|||Vyas,
Thanks a lot.
I went to SQLDTS.com. However, I didn't find the example I
really need.
What I want to kow is how to set up the server name in
source server of DTS as global viriable so that I can pass
in the server name as parameter through DTSRUN? Can you
give more instructions about how to do it in EM?
Lixin
>--Original Message--
>In short, you could use global variables to specify the
data source. When
>you invoke the package using DTSRUN, you could specify
values for these
>global variables. See SQLDTS.com for examples.
>--
>HTH,
>Vyas, MVP (SQL Server)
>http://vyaskn.tripod.com/
>What hardware is your SQL Server running on?
>http://vyaskn.tripod.com/poll.htm
>
>"Lixin Fan" <lixin2003@.hotmail.com> wrote in message
>news:1d88601c388eb$05101ce0$a601280a@.phx.gbl...
>I want to build a DTS package to transfer data between two
>SQL 2000 servers. I like to set up the data source as a
>variable. In another word, I want to pass in the data
>source (source SQL server 2000) as a parameter to the DTS
>package so that I can reuse this package somewhere else.
>Is it possible? How?
>Thanks in advance,
>Lixin
>
>.
>|||OK
Let's say I have a connection to SQL Server and I call it MySQLServer. The
Global Variable that holds the name of the server is "MyConnVar". In an
Active Script task I would do.
dim PKG
dim Conn
set PKG = DTSGlobalVariables.Parent
set Conn = PKG.Connections("MySQLServer")
Conn.DataSource = DTSGlobalVariables("MyConnVar").Value
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Lixin Fan" <lixin2003@.hotmail.com> wrote in message
news:082501c38921$6977f0a0$a401280a@.phx.gbl...
> Vyas,
> Thanks a lot.
> I went to SQLDTS.com. However, I didn't find the example I
> really need.
> What I want to kow is how to set up the server name in
> source server of DTS as global viriable so that I can pass
> in the server name as parameter through DTSRUN? Can you
> give more instructions about how to do it in EM?
> Lixin
> >--Original Message--
> >In short, you could use global variables to specify the
> data source. When
> >you invoke the package using DTSRUN, you could specify
> values for these
> >global variables. See SQLDTS.com for examples.
> >
> >--
> >HTH,
> >Vyas, MVP (SQL Server)
> >http://vyaskn.tripod.com/
> >What hardware is your SQL Server running on?
> >http://vyaskn.tripod.com/poll.htm
> >
> >
> >"Lixin Fan" <lixin2003@.hotmail.com> wrote in message
> >news:1d88601c388eb$05101ce0$a601280a@.phx.gbl...
> >I want to build a DTS package to transfer data between two
> >SQL 2000 servers. I like to set up the data source as a
> >variable. In another word, I want to pass in the data
> >source (source SQL server 2000) as a parameter to the DTS
> >package so that I can reuse this package somewhere else.
> >Is it possible? How?
> >
> >Thanks in advance,
> >Lixin
> >
> >
> >.
> >

Dynamic cursor variable

i have this code in which i define a @.TempTableCursor
but is there a way that the TargetTable will be
TargetTable1,TargetTable2,..TargetTable(i)
so when i define set the @.TempTableCursor it will have in the
defenition the TargetTable with a dynamic changing number?
[code]
Declare @.TempTableCursor cursor
Set @.TempTableCursor = Cursor Local FAST_FORWARD
For Select * From TargetTable
[/code]
thnaks in advance
peleg
On Aug 2, 3:44 pm, pelegk1 <pele...@.discussions.microsoft.com> wrote:
> i have this code in which i define a @.TempTableCursor
> but is there a way that the TargetTable will be
> TargetTable1,TargetTable2,..TargetTable(i)
> so when i define set the @.TempTableCursor it will have in the
> defenition the TargetTable with a dynamic changing number?
> [code]
> Declare @.TempTableCursor cursor
> Set @.TempTableCursor = Cursor Local FAST_FORWARD
> For Select * From TargetTable
> [/code]
> thnaks in advance
> peleg
declare @.sql nvarchar(4000)
declare @.table varchar(100)
set @.table = 't'
set @.sql = N'
set @.cur = cursor for
select
* from ' + @.table + '; open @.cur'
exec sp_executesql @.sql, N'@.cur cursor output', @.cur
output
if cursor_status('variable', '@.cur') = 1
begin
.....................
.....................
end
if cursor_status('variable', '@.cur') >= 0
close @.cur
deallocate @.cur
Regards
Amish Shah
http://shahamish.tripod.com
|||thnaks alot
"amish" wrote:

> On Aug 2, 3:44 pm, pelegk1 <pele...@.discussions.microsoft.com> wrote:
> declare @.sql nvarchar(4000)
> declare @.table varchar(100)
> set @.table = 't'
> set @.sql = N'
> set @.cur = cursor for
> select
> * from ' + @.table + '; open @.cur'
>
> exec sp_executesql @.sql, N'@.cur cursor output', @.cur
> output
>
> if cursor_status('variable', '@.cur') = 1
> begin
> .....................
> .....................
> end
>
> if cursor_status('variable', '@.cur') >= 0
> close @.cur
>
> deallocate @.cur
> Regards
> Amish Shah
> http://shahamish.tripod.com
>
|||On Aug 2, 5:38 pm, pelegk1 <pele...@.discussions.microsoft.com> wrote:
> thnaks alot
>
> "amish" wrote:
>
>
>
>
>
> - Show quoted text -
:-)

Dynamic cursor variable

i have this code in which i define a @.TempTableCursor
but is there a way that the TargetTable will be
TargetTable1,TargetTable2,..TargetTable(i)
so when i define set the @.TempTableCursor it will have in the
defenition the TargetTable with a dynamic changing number?
[code]
Declare @.TempTableCursor cursor
Set @.TempTableCursor = Cursor Local FAST_FORWARD
For Select * From TargetTable
[/code]
thnaks in advance
pelegOn Aug 2, 3:44 pm, pelegk1 <pele...@.discussions.microsoft.com> wrote:
> i have this code in which i define a @.TempTableCursor
> but is there a way that the TargetTable will be
> TargetTable1,TargetTable2,..TargetTable(i)
> so when i define set the @.TempTableCursor it will have in the
> defenition the TargetTable with a dynamic changing number?
> [code]
> Declare @.TempTableCursor cursor
> Set @.TempTableCursor = Cursor Local FAST_FORWARD
> For Select * From TargetTable
> [/code]
> thnaks in advance
> peleg
declare @.sql nvarchar(4000)
declare @.table varchar(100)
set @.table = 't'
set @.sql = N'
set @.cur = cursor for
select
* from ' + @.table + '; open @.cur'
exec sp_executesql @.sql, N'@.cur cursor output', @.cur
output
if cursor_status('variable', '@.cur') = 1
begin
.....................
....................
end
if cursor_status('variable', '@.cur') >= 0
close @.cur
deallocate @.cur
Regards
Amish Shah
http://shahamish.tripod.com|||thnaks alot
"amish" wrote:

> On Aug 2, 3:44 pm, pelegk1 <pele...@.discussions.microsoft.com> wrote:
> declare @.sql nvarchar(4000)
> declare @.table varchar(100)
> set @.table = 't'
> set @.sql = N'
> set @.cur = cursor for
> select
> * from ' + @.table + '; open @.cur'
>
> exec sp_executesql @.sql, N'@.cur cursor output', @.cur
> output
>
> if cursor_status('variable', '@.cur') = 1
> begin
> .....................
> .....................
> end
>
> if cursor_status('variable', '@.cur') >= 0
> close @.cur
>
> deallocate @.cur
> Regards
> Amish Shah
> http://shahamish.tripod.com
>|||On Aug 2, 5:38 pm, pelegk1 <pele...@.discussions.microsoft.com> wrote:
> thnaks alot
>
> "amish" wrote:
>
>
>
>
>
>
>
>
>
> - Show quoted text -
:-)

Dynamic cursor variable

i have this code in which i define a @.TempTableCursor
but is there a way that the TargetTable will be
TargetTable1,TargetTable2,..TargetTable(i)
so when i define set the @.TempTableCursor it will have in the
defenition the TargetTable with a dynamic changing number?
[code]
Declare @.TempTableCursor cursor
Set @.TempTableCursor = Cursor Local FAST_FORWARD
For Select * From TargetTable
[/code]
thnaks in advance
pelegOn Aug 2, 3:44 pm, pelegk1 <pele...@.discussions.microsoft.com> wrote:
> i have this code in which i define a @.TempTableCursor
> but is there a way that the TargetTable will be
> TargetTable1,TargetTable2,..TargetTable(i)
> so when i define set the @.TempTableCursor it will have in the
> defenition the TargetTable with a dynamic changing number?
> [code]
> Declare @.TempTableCursor cursor
> Set @.TempTableCursor = Cursor Local FAST_FORWARD
> For Select * From TargetTable
> [/code]
> thnaks in advance
> peleg
declare @.sql nvarchar(4000)
declare @.table varchar(100)
set @.table = 't'
set @.sql = N'
set @.cur = cursor for
select
* from ' + @.table + '; open @.cur'
exec sp_executesql @.sql, N'@.cur cursor output', @.cur
output
if cursor_status('variable', '@.cur') = 1
begin
.....................
.....................
end
if cursor_status('variable', '@.cur') >= 0
close @.cur
deallocate @.cur
Regards
Amish Shah
http://shahamish.tripod.com|||thnaks alot
"amish" wrote:
> On Aug 2, 3:44 pm, pelegk1 <pele...@.discussions.microsoft.com> wrote:
> > i have this code in which i define a @.TempTableCursor
> > but is there a way that the TargetTable will be
> > TargetTable1,TargetTable2,..TargetTable(i)
> > so when i define set the @.TempTableCursor it will have in the
> > defenition the TargetTable with a dynamic changing number?
> > [code]
> > Declare @.TempTableCursor cursor
> > Set @.TempTableCursor = Cursor Local FAST_FORWARD
> > For Select * From TargetTable
> > [/code]
> >
> > thnaks in advance
> > peleg
> declare @.sql nvarchar(4000)
> declare @.table varchar(100)
> set @.table = 't'
> set @.sql = N'
> set @.cur = cursor for
> select
> * from ' + @.table + '; open @.cur'
>
> exec sp_executesql @.sql, N'@.cur cursor output', @.cur
> output
>
> if cursor_status('variable', '@.cur') = 1
> begin
> .....................
> .....................
> end
>
> if cursor_status('variable', '@.cur') >= 0
> close @.cur
>
> deallocate @.cur
> Regards
> Amish Shah
> http://shahamish.tripod.com
>|||On Aug 2, 5:38 pm, pelegk1 <pele...@.discussions.microsoft.com> wrote:
> thnaks alot
>
> "amish" wrote:
> > On Aug 2, 3:44 pm, pelegk1 <pele...@.discussions.microsoft.com> wrote:
> > > i have this code in which i define a @.TempTableCursor
> > > but is there a way that the TargetTable will be
> > > TargetTable1,TargetTable2,..TargetTable(i)
> > > so when i define set the @.TempTableCursor it will have in the
> > > defenition the TargetTable with a dynamic changing number?
> > > [code]
> > > Declare @.TempTableCursor cursor
> > > Set @.TempTableCursor = Cursor Local FAST_FORWARD
> > > For Select * From TargetTable
> > > [/code]
> > > thnaks in advance
> > > peleg
> > declare @.sql nvarchar(4000)
> > declare @.table varchar(100)
> > set @.table = 't'
> > set @.sql = N'
> > set @.cur = cursor for
> > select
> > * from ' + @.table + '; open @.cur'
> > exec sp_executesql @.sql, N'@.cur cursor output', @.cur
> > output
> > if cursor_status('variable', '@.cur') = 1
> > begin
> > .....................
> > .....................
> > end
> > if cursor_status('variable', '@.cur') >= 0
> > close @.cur
> > deallocate @.cur
> > Regards
> > Amish Shah
> >http://shahamish.tripod.com- Hide quoted text -
> - Show quoted text -
:-)

Dynamic Connection Strings in SSIS

Possible or not? -->
I maybe lazy - but I want to achieve just specifiying 1 variable in SSIS package ("environment") - and all the connectionStrings should "poof" magically be adjusted to correct locations

In DTS I created a SetDTSenvironmentVariables function for all my packages - so how wouldIi achieve this in SSIS?

Function SetDTSenvironmentVariables( environment )
Folder = "MyDtsPackageFolder"
Select Case environment
case "DEV"
DTSGlobalVariables("WorkingDirectory").value = "C:\Packages" & Folder
case "STAGING"
DTSGlobalVariables("WorkingDirectory").value = "D:\Sql_working_directory\My_production\STAGING" & Folder
case "LIVE"
DTSGlobalVariables("WorkingDirectory").value = "D:\Sql_working_directory\My_production\" & Folder
End Select

'
' Set Connection Properties
'
dim oPackage, oConn
set oPackage = DTSGlobalVariables.parent
oPackage.LogFileName = DTSGlobalVariables("WorkingDirectory").value & "\Logs\Errors.txt"
For Each oConn In oPackage.connections
Select Case oConn.Name
case "My_DB"
Select Case environment
case "DEV"
oConn.datasource = "SERVER01"
oConn.Catalog = "My_Production"
case "STAGING"
oConn.datasource = "SERVER06"
oConn.Catalog = "My_Staging"
case "LIVE"
oConn.datasource = "SERVER06"
oConn.Catalog = "My_Production"
End Select
case "Schools.xls"
oConn.datasource = DTSGlobalVariables("WorkingDirectory").value & "\" & "School_Codes.xls"
case else
oConn.datasource = DTSGlobalVariables("WorkingDirectory").value & "\" & oConn.Name
End Select
Next

set oPackage = nothing
set oConn = nothing
End Function

The way I do this is to have a variable called RootFolder and all other directories are relative to that and hence can be set dynamically using an expression (on ConnectionString property of the appropriate connection manager).

RootFolder variable is set via a configuration. Its your choice as to what type of configuration you use.

I kinda talk about this a bit here:

Common folder structure
(http://blogs.conchango.com/jamiethomson/archive/2006/01/05/2559.aspx)

-Jamie

|||Awesome, dude

Thanks mate - only thing I wonder how do you get time to write all those blogs...|||

TheViewMaster wrote:

Awesome, dude

Thanks mate - only thing I wonder how do you get time to write all those blogs...

I wonder myself sometime.

I've been doing it for two years tho so there's quite a library of "stuff" up there now. I hardly ever write anything new these days.

-Jamie

dynamic connection

Hi,

In the SSIS package, I have made the filename dynamic i.e. set as a variable.

How is it possible to do the same thing for the database name in the oledb connection?

I looked at the connectionString property for the OLEDB connection. ConnectionString looks long and the databasename is in this text.

Not sure how to make this databasename inside the connectionstring dynamic?

Thanks

hi,
What is wrong with this please?
I am passing two variables to execute a ssis package.
Thanks

set @.cmd = 'dtexec /f ' + @.FullPackagePath + ' /set \Package.Variables[User::FileName].Properties[Value];"' + @.FullFilePath + '"' +
' \Package.Variables[User::ConnectionPath].Properties[Value];"' + @.ConnectionPath + '"'
print @.cmd

error is:
Option "\Package.Variables[User::ConnectionPath].Properties[Value];Data Source=server1\databasename" is not valid.

please note I just retyped the data source name here.

|||

Get the raw command line working first then port it to SQL version. You are setting two properties, so try giving each it's own /SET. Also try quoting the property ID string bit.

/FILE "P:\My Documents\Package.dtsx" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EW /SET "\Package.Variables[User::ConnectionPath].Properties[Value]";"Var Value" /SET "\Package.Variables[User::ConnectionPath].Properties[Value]";"XX XX"

I just mocked that up using DTEXecUI, as it can almost act like a command line builder tool for you.

|||

Hi,

It works if I only pass the filename variable but not with both parameters.

As you see I am using the /set option.

Can you see what is wrong with what I sent initially please?

Thanks

|||

You can just replace the entire connection string. Perhaps save the package with a default connection in a variable and then use an expression and REPLACE to change the place holder database for the real one, or just save a connnection string wout the database and add in via and expression. So I guess the trick is to manage the connection string differently, perhaps as sections, such that you can construct what you need with expressions latter. Not ideal, but no doubt driven by the nature of connection types, and connection string properties rather than the known common properties.

If using configurations you can change the InitialCatalog property, but unfortunatey this is not available as the target for a property expression.

|||

I can see you used one set option, but passed two sets against it.

Look at my example again and you will see that I have two /SET options in there, one for each property to be set.

Error -

/FILE "P:\My Documents\Package.dtsx" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EW /SET "\Package.Variables[User::ConnectionPath].Properties[Value]";"Var Value" "\Package.Variables [User::ConnectionPath].Properties[Value]";"XX XX"

Option "\Package.Variables[User::ConnectionPath].Properties[Value];XX XX" is not valid.

OK -

/FILE "P:\My Documents\Package.dtsx" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EW /SET "\Package.Variables[User::ConnectionPath].Properties[Value]";"Var Value" /SET "\Package.Variables[User::ConnectionPath].Properties[Value]";"XX XX"

|||

I am getting close to what I was after.

Basically I am passing a variable but not sure how to pass two variables. See below. Do you see what is wrong with the below query please?

It works if filename variable is used but not if the second variable is included.

set @.cmd = 'dtexec /f ' + @.FullPackagePath + ' /set \Package.Variables[User::FileName].Properties[Value];"' + @.FullFilePath + '"' +

' /set \Package.Variables[User::ConnectionPath].Properties[Value];"' + @.ConnectionPath + '"'

print @.cmd

|||

Get the same error.

This is what I have:

set @.cmd = 'dtexec /f ' + @.FullPackagePath + ' /set \Package.Variables[User::FileName].Properties[Value];"' + @.FullFilePath + '"' +

'/set \Package.Variables[User::ConnectionPath].Properties[Value];"' + @.ConnectionPath + '"'

|||Can you show us the result of "print @.cmd"?|||

dtexec /f d:\sysappl\CEM\SSIS\Imports\Trades\TradeCreds.dtsx /set \Package.Variables[User::FileName].Properties[Value];"d:\ApplData\CEM\WorkingTemp\CollateralEx.csv"

/set \Package.Variables[User::ConnectionPath].Properties[Value];"Data Source=server1\instance1, 2025;Initial Catalog=database1;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;"

|||What is this?

Data Source=server1\instance1, 2025;|||

I replaced the actual servername\instance

|||

arkiboys wrote:

I replaced the actual servername\instance

What is ", 2025"|||

port no.
This is how I connect to the database.

|||

I think I worked it out:

set @.cmd = 'dtexec /f ' + @.FullPackagePath +

' /set \Package.Variables[User::FileName].Properties[Value];"' + @.FullFilePath + '"

/set \Package.Variables[User::ConnectionPath].Properties[Value];"' + @.ConnectionPath + '"'

print @.cmd