Pass XML between SPROCs possible in SQL 2000? 2005? (Cross Post - Sorry)

Note: Sorry for cross posting this question with
microsoft.public.sqlserver.programming, but I actually only intended to post
this question here...
A: I can create an XML fragment using the For XML Auto clause. No problem
so far.
B: I have a stored procedure that takes an XML document as ntext and uses
the extended sproc sp_xml_preparedocument and the OPENXML function to create
a table from it - no problem with that.
Now I want to execute my stored procedure using the XML result of part A as
the parameter for the sproc in part B. Is that possible? The only way I
know how to do it right now is to return the result of A using ADO.NET,
insert a root element (to make the XML valid), and then call the sproc
created in part B with this xml as the parameter.Hi Dave,
Welcome to MSDN newsgroup.
Regarding on this issue, I've also noticed your another duplicated thread
in the
Newsgroups: microsoft.public.sqlserver.programming
One community member Kent have posted some good suggestion there. Please
have a look and feel free to post in that thread...
Thanks,
Steven Cheng
Microsoft Online Support
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)
| From: "Dave Slinn" <CougarDave@.noemail.noemail>
| Subject: Pass XML between SPROCs possible in SQL 2000? 2005? (Cross Post
- Sorry)
| Date: Wed, 7 Dec 2005 12:36:59 -0600
| Lines: 18
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.3790.1830
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.1830
| X-RFC2646: Format=Flowed; Original
| Message-ID: <#Rkm201#FHA.3804@.TK2MSFTNGP14.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.xml
| NNTP-Posting-Host: online.gms.ca 142.165.52.112
| Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP14.phx.gbl
| Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.xml:26733
| X-Tomcat-NG: microsoft.public.sqlserver.xml
|
| Note: Sorry for cross posting this question with
| microsoft.public.sqlserver.programming, but I actually only intended to
post
| this question here...
|
| A: I can create an XML fragment using the For XML Auto clause. No problem
| so far.
|
| B: I have a stored procedure that takes an XML document as ntext and uses
| the extended sproc sp_xml_preparedocument and the OPENXML function to
create
| a table from it - no problem with that.
|
| Now I want to execute my stored procedure using the XML result of part A
as
| the parameter for the sproc in part B. Is that possible? The only way I
| know how to do it right now is to return the result of A using ADO.NET,
| insert a root element (to make the XML valid), and then call the sproc
| created in part B with this xml as the parameter.
|
|
||||In SQL Server 2005, you can utilize the XML datatype to do this very easily.
In SQL Server 2000, FOR XML goes always back to the client only, so using
ADO/OLEDB/ADO.Net in between is necessary...
Best regards
Michael
"Dave Slinn" <CougarDave@.noemail.noemail> wrote in message
news:%23Rkm201%23FHA.3804@.TK2MSFTNGP14.phx.gbl...
> Note: Sorry for cross posting this question with
> microsoft.public.sqlserver.programming, but I actually only intended to
> post this question here...
> A: I can create an XML fragment using the For XML Auto clause. No problem
> so far.
> B: I have a stored procedure that takes an XML document as ntext and uses
> the extended sproc sp_xml_preparedocument and the OPENXML function to
> create
> a table from it - no problem with that.
> Now I want to execute my stored procedure using the XML result of part A
> as
> the parameter for the sproc in part B. Is that possible? The only way I
> know how to do it right now is to return the result of A using ADO.NET,
> insert a root element (to make the XML valid), and then call the sproc
> created in part B with this xml as the parameter.
>

Pass XML between SPROCs possible in SQL 2000? 2005? (Cross Post - Sorry)

Note: Sorry for cross posting this question with
microsoft.public.sqlserver.programming, but I actually only intended to post
this question here...
A: I can create an XML fragment using the For XML Auto clause. No problem
so far.
B: I have a stored procedure that takes an XML document as ntext and uses
the extended sproc sp_xml_preparedocument and the OPENXML function to create
a table from it - no problem with that.
Now I want to execute my stored procedure using the XML result of part A as
the parameter for the sproc in part B. Is that possible? The only way I
know how to do it right now is to return the result of A using ADO.NET,
insert a root element (to make the XML valid), and then call the sproc
created in part B with this xml as the parameter.
Hi Dave,
Welcome to MSDN newsgroup.
Regarding on this issue, I've also noticed your another duplicated thread
in the
Newsgroups: microsoft.public.sqlserver.programming
One community member Kent have posted some good suggestion there. Please
have a look and feel free to post in that thread...
Thanks,
Steven Cheng
Microsoft Online Support
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)
| From: "Dave Slinn" <CougarDave@.noemail.noemail>
| Subject: Pass XML between SPROCs possible in SQL 2000? 2005? (Cross Post
- Sorry)
| Date: Wed, 7 Dec 2005 12:36:59 -0600
| Lines: 18
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.3790.1830
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.1830
| X-RFC2646: Format=Flowed; Original
| Message-ID: <#Rkm201#FHA.3804@.TK2MSFTNGP14.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.xml
| NNTP-Posting-Host: online.gms.ca 142.165.52.112
| Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFT NGP14.phx.gbl
| Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.xml:26733
| X-Tomcat-NG: microsoft.public.sqlserver.xml
|
| Note: Sorry for cross posting this question with
| microsoft.public.sqlserver.programming, but I actually only intended to
post
| this question here...
|
| A: I can create an XML fragment using the For XML Auto clause. No problem
| so far.
|
| B: I have a stored procedure that takes an XML document as ntext and uses
| the extended sproc sp_xml_preparedocument and the OPENXML function to
create
| a table from it - no problem with that.
|
| Now I want to execute my stored procedure using the XML result of part A
as
| the parameter for the sproc in part B. Is that possible? The only way I
| know how to do it right now is to return the result of A using ADO.NET,
| insert a root element (to make the XML valid), and then call the sproc
| created in part B with this xml as the parameter.
|
|
|
|||In SQL Server 2005, you can utilize the XML datatype to do this very easily.
In SQL Server 2000, FOR XML goes always back to the client only, so using
ADO/OLEDB/ADO.Net in between is necessary...
Best regards
Michael
"Dave Slinn" <CougarDave@.noemail.noemail> wrote in message
news:%23Rkm201%23FHA.3804@.TK2MSFTNGP14.phx.gbl...
> Note: Sorry for cross posting this question with
> microsoft.public.sqlserver.programming, but I actually only intended to
> post this question here...
> A: I can create an XML fragment using the For XML Auto clause. No problem
> so far.
> B: I have a stored procedure that takes an XML document as ntext and uses
> the extended sproc sp_xml_preparedocument and the OPENXML function to
> create
> a table from it - no problem with that.
> Now I want to execute my stored procedure using the XML result of part A
> as
> the parameter for the sproc in part B. Is that possible? The only way I
> know how to do it right now is to return the result of A using ADO.NET,
> insert a root element (to make the XML valid), and then call the sproc
> created in part B with this xml as the parameter.
>

Pass a variable to SET IDENTITY_INSERT

I'm quite new to programming with SQL Server.
I'm developing an SQL statement using Query Analyzer that read a table from a MSAccess Linked server database, and put the data in to a SQL server table.

I'd like that table name, db name, etc, are values stored in some variables, so, when i want to import another table, i need to change only the value of the @.table_name variable.

I can pass the value of this variable to all the statements of my script but one. I'm able to execute something like

EXEC ('select * from' + @.table_name + '... bla bla bla')

but i'm not able to execute the SET IDENTITY_INSERT @.table_name ON/OFF.
I tried many options like print, exec, quotes, double quotes, etc, but nothing to do.

I'd really appreciate all your helping answers.

Ciao

Darioif you wish to do this you will need to build a string containing the set statement and the insert statment. then EXEC(@.string).

Remember that EXEC() runs on a seperate thread than your current connection so any settings will be lost once the EXEC finishes.

declare @.TSQL varchar(1000)
CREATE TABLE #products (id int IDENTITY PRIMARY KEY, product varchar(40))

-- Inserting values into products table.
INSERT INTO #products (product) VALUES ('screwdriver')
INSERT INTO #products (product) VALUES ('hammer')

select * From #Products

-- Inserting ids and values into products table.
set @.TSQL = 'set identity_insert #products on
INSERT INTO #products (id,product) VALUES (10,''saw'')
INSERT INTO #products (id,product) VALUES (11,''shovel'')'

exec(@.TSQL)

select * from #products

drop table #Products|||The script is the following:

-- Variable declaration
DECLARE @.nome_db nvarchar(50)
DECLARE @.nome_tabella nvarchar(50)
DECLARE @.id_tabella int
DECLARE @.str_nome_campi nvarchar(255)
DECLARE @.str_tmp_nome_campo nvarchar(255)
DECLARE @.strdata nvarchar(255)
DECLARE @.str_sql nvarchar(255)
DECLARE @.str_sql_delete nvarchar(255)
DECLARE @.str_sql_use nvarchar(255)
DECLARE @.str_sql_debug nvarchar(255)
DECLARE @.str_id_insert_on nvarchar(50)
DECLARE @.str_id_insert_off nvarchar(50)

-- Set the variables
SET @.nome_db = 'Unitec_Script_Importazione'
SET @.nome_tabella = 'ANFRAGEDETAILS2'

SET @.str_sql_delete = 'DELETE FROM [' + @.nome_db + '].[dbo].[' + @.nome_tabella + ']'
SET @.str_sql_use ='USE [' + @.nome_db + ']'
SET @.str_sql_debug = 'SELECT TOP 10 * FROM ' + @.nome_tabella

-- Stringhe per settare Identity ... maledette!
SET @.str_id_insert_on = ' SET IDENTITY_INSERT ' + quotename(@.nome_tabella) + ' ON' + char(10)
SET @.str_id_insert_off =' SET IDENTITY_INSERT ' + quotename(@.nome_tabella) + ' OFF' + char(10)
-- Trovo, all'interno della tabella di sistema sysobjects, l'ID
-- della tabella. Usando questo valore posso trovare i campi relativi
SET @.id_tabella = (SELECT [id] FROM sysobjects WHERE [name] LIKE @.nome_tabella)
--SELECT @.id_tabella -- (debug - stampa il risultato della query)

DECLARE nome_campi CURSOR
FOR SELECT [name] FROM syscolumns where id = @.id_tabella
-- Open the cursor
OPEN nome_campi
-- Prendo la prima riga del cursore e la schiaffo dentro alla variabile temporanea
FETCH NEXT FROM nome_campi INTO @.str_tmp_nome_campo

SET CONCAT_NULL_YIELDS_NULL OFF

WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.str_nome_campi = @.str_tmp_nome_campo
SET @.strdata = @.str_nome_campi + @.strdata
FETCH NEXT FROM nome_campi INTO @.str_tmp_nome_campo
-- Aggiungo la virgola se necessario
SET @.str_tmp_nome_campo = @.str_tmp_nome_campo + ', '
END

SELECT @.strdata -- Debug: stampa il contenuto di strdata

CLOSE nome_campi
DEALLOCATE nome_campi

EXECUTE (@.str_sql_use)

EXECUTE sp_executesql @.str_sql_delete

/******* THIS IS WHERE I NEED HELP*********
Instead of the name of the table (ANFRAGEDETAILS2)
i want to use a variable @.table_name
*****************************************/
SET IDENTITY_INSERT ANFRAGEDETAILS2 ON

SET @.str_sql = @.str_sql + 'INSERT INTO [dbo].[' + @.nome_tabella + '] (' + @.strdata + ') SELECT * FROM OPENQUERY(Anfragen, ''Select ' + @.strdata + ' from [' + @.nome_tabella + ']'')'

EXECUTE (@.str_sql)

SET IDENTITY_INSERT ANFRAGEDETAILS2 OFF

--
--
--|||have you tried:

SET @.str_sql = @.str_sql + 'SET IDENTITY_INSERT ANFRAGEDETAILS2 ON
INSERT INTO [dbo].[' + @.nome_tabella + '] (' + @.strdata + ') SELECT * FROM OPENQUERY(Anfragen, ''Select ' + @.strdata + ' from [' + @.nome_tabella + ']'')
SET IDENTITY_INSERT ANFRAGEDETAILS2 OFF'
EXECUTE (@.str_sql)|||Yes, 'SET IDENTITY_INSERT ANFRAGEDETAILS2 ON ... works
but 'SET IDENTITY_INSERT @.table_name ON ... Doesn't work|||AND
SET @.str_sql = @.str_sql
+ ' SET IDENTITY_INSERT [dbo].['+@.nome_tabella+'] ON '
+'INSERT INTO [dbo].[' + @.nome_tabella + '] (' + @.strdata + ') SELECT * FROM OPENQUERY(Anfragen, ''Select '' + @.strdata + '' from ['' + @.nome_tabella + '']'')'
EXECUTE (@.str_sql)
WORKS !?|||Yes, it works!!!

Many thanks

Dario