how to insert sp_helptext output into a table?

hi Freinds,
SQL 2000
How can I insert the output of sp_helptext mytriggername into a table?
Sp_helptext out put is a table ! I need to collect the rows into a custom
table.
Thanks in advance,
PatCREATE TABLE #trigger
(
[text] VARCHAR(8000)
)
INSERT #trigger EXEC sp_helptext insContactEmailFormatNull
SELECT * FROM #trigger
DROP TABLE #trigger
Of course, you could have silly triggers that go more than 8000 characters
without a carriage return. In that case I would suggest CREATE TABLE
#PeopleToShoot(EmployeeID INT) ...
A
"Patrick" <patriarck@.gmail.com> wrote in message
news:%23OW3DK1oFHA.3380@.TK2MSFTNGP12.phx.gbl...
> hi Freinds,
> SQL 2000
> How can I insert the output of sp_helptext mytriggername into a table?
> Sp_helptext out put is a table ! I need to collect the rows into a custom
> table.
> Thanks in advance,
> Pat
>|||> ... CREATE TABLE
#PeopleToShoot(EmployeeID INT) ...
I'd suggest a permanent table for that.
ML

How to insert multiple values in a single column

I need to display output as shown below. In Col1, Col2 and Col3 I want to insert values from subqueries. But do not want to use sub-reports... is there any alternative to subqueries.

In Col1, Col2 and Col3 there can be any number of values.

Company

BankCol1Col2Col3AstroTechICICI

123

5

34

MindTreeHDFC

54

8

why don't you want to use a sub-report?

|||

It takes very long time, because i have 17 columns in the report.. its not fisible to insert 15 sub-reports

|||

Ah I understand, and agree. Not sure how else to implement this though...could you insert another table into your main table's cells?

|||

Not resolved yet... any ideas...

|||

One idea would be to build your data using a view. Capture the main data (Company, Bank, ID) and then capture each columns' data (based on the ID in first query). Select from the view.

I think your data would be more like:

ID Company Bank ManagerNames BranchOffices TopSalesNames

1 CompanyA BankOne Jerry, Ted, Lisa Omaha, Chicago Fred, Mary

2 CompanyB BankTwo Paul Lincoln, Springfield, Florence William, John

Not sure if this meets your needs. You may be able to format the multi-value rows once in the report. Hope this helps.

|||

thanks... I will try this

How to insert a space after each Manager Starts.

hi,
guys
i have query which given below output given below

manager personlevel person name
20851 Howard Wilson1
20852Howard Wilson2
20853Howard Wilson3
20854Howard Wilson4
20855Howard Wilson5
60861Andrew Saxon
60862Andrew Saxon
60863Ian Thompson
60864Ian Thompson
60865Phil Dargan

what i want is after a manager ends i want a null to be inserted for
each of there columns
so that i can distinguish that when a new manager starts

so thatt output looks like this

manager personlevel person name
20851 Howard Wilson1
20852Howard Wilson2
20853Howard Wilson3
20854Howard Wilson4
20855Howard Wilson5
null null null
60861Andrew Saxon
60862Andrew Saxon
60863Ian Thompson
60864Ian Thompson
60865Phil Dargan

Brlliant minds any solution for this..
i know can i loop through the records and do it
and check for a new manager
but i want a better solution ..
give me your ideads folks..

Regards,
Navin MahindrooHi

You don't post the DDL or the current query so it is hard to know what your
SQL is.

Assuming something like:

SELECT Manager, Personlevel, PersonName from Mgmt

You could try (untested)

SELECT Manager, Personlevel, PersonName from
( SELECT Manager as Id, Manager, Personlevel, PersonName from Mgmt
UNION
SELECT DISTINCT Manager, NULL, NULL, NULL from Mgmt
ORDER BY Id ASC, Manager DESC ) M

John

"Navin" <navinsm2@.rediffmail.com> wrote in message
news:5dc7f532.0306300051.7b6d1f67@.posting.google.c om...
> hi,
> guys
> i have query which given below output given below
> manager personlevel person name
> 2085 1 Howard Wilson1
> 2085 2 Howard Wilson2
> 2085 3 Howard Wilson3
> 2085 4 Howard Wilson4
> 2085 5 Howard Wilson5
> 6086 1 Andrew Saxon
> 6086 2 Andrew Saxon
> 6086 3 Ian Thompson
> 6086 4 Ian Thompson
> 6086 5 Phil Dargan
> what i want is after a manager ends i want a null to be inserted for
> each of there columns
> so that i can distinguish that when a new manager starts
> so thatt output looks like this
> manager personlevel person name
> 2085 1 Howard Wilson1
> 2085 2 Howard Wilson2
> 2085 3 Howard Wilson3
> 2085 4 Howard Wilson4
> 2085 5 Howard Wilson5
> null null null
> 6086 1 Andrew Saxon
> 6086 2 Andrew Saxon
> 6086 3 Ian Thompson
> 6086 4 Ian Thompson
> 6086 5 Phil Dargan
> Brlliant minds any solution for this..
> i know can i loop through the records and do it
> and check for a new manager
> but i want a better solution ..
> give me your ideads folks..
> Regards,
> Navin Mahindroo|||Hi Navin M,

Same other way round.

SELECT 'N' 'GRP_SEP',manager, personlevel ,[person name] FROM
TableName
UNION ALL
SELECT DISTINCT 'Y',manager,NULL,NULL FROM TableName
ORDER BY manager,GRP_SEP ASC

Group seperator is added to explicitly know that row with 'Y' is group
seperator and avoid null conflit if personlevel and name both are
null.

Also note that Manager field has appropriate index on it.

hope this helps you.

Thanks Amit.

navinsm2@.rediffmail.com (Navin) wrote in message news:<5dc7f532.0306300051.7b6d1f67@.posting.google.com>...
> hi,
> guys
> i have query which given below output given below
> manager personlevel person name
> 20851 Howard Wilson1
> 20852Howard Wilson2
> 20853Howard Wilson3
> 20854Howard Wilson4
> 20855Howard Wilson5
> 60861Andrew Saxon
> 60862Andrew Saxon
> 60863Ian Thompson
> 60864Ian Thompson
> 60865Phil Dargan
> what i want is after a manager ends i want a null to be inserted for
> each of there columns
> so that i can distinguish that when a new manager starts
> so thatt output looks like this
> manager personlevel person name
> 20851 Howard Wilson1
> 20852Howard Wilson2
> 20853Howard Wilson3
> 20854Howard Wilson4
> 20855Howard Wilson5
> null null null
> 60861Andrew Saxon
> 60862Andrew Saxon
> 60863Ian Thompson
> 60864Ian Thompson
> 60865Phil Dargan
> Brlliant minds any solution for this..
> i know can i loop through the records and do it
> and check for a new manager
> but i want a better solution ..
> give me your ideads folks..
> Regards,
> Navin Mahindroo|||Hi

Got around to testing it... you can't use the order by in the derived
table!

SELECT Manager, Personlevel, PersonName from
( SELECT Manager as Id, Manager, Personlevel, PersonName from Mgmt
UNION
SELECT DISTINCT Manager, NULL, NULL, NULL from Mgmt
) M
ORDER BY id, Manager Desc

John

"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:3f0009d7$0$18490$ed9e5944@.reading.news.pipex. net...
> Hi
> You don't post the DDL or the current query so it is hard to know what
your
> SQL is.
> Assuming something like:
> SELECT Manager, Personlevel, PersonName from Mgmt
> You could try (untested)
> SELECT Manager, Personlevel, PersonName from
> ( SELECT Manager as Id, Manager, Personlevel, PersonName from Mgmt
> UNION
> SELECT DISTINCT Manager, NULL, NULL, NULL from Mgmt
> ORDER BY Id ASC, Manager DESC ) M
> John
> "Navin" <navinsm2@.rediffmail.com> wrote in message
> news:5dc7f532.0306300051.7b6d1f67@.posting.google.c om...
> > hi,
> > guys
> > i have query which given below output given below
> > manager personlevel person name
> > 2085 1 Howard Wilson1
> > 2085 2 Howard Wilson2
> > 2085 3 Howard Wilson3
> > 2085 4 Howard Wilson4
> > 2085 5 Howard Wilson5
> > 6086 1 Andrew Saxon
> > 6086 2 Andrew Saxon
> > 6086 3 Ian Thompson
> > 6086 4 Ian Thompson
> > 6086 5 Phil Dargan
> > what i want is after a manager ends i want a null to be inserted for
> > each of there columns
> > so that i can distinguish that when a new manager starts
> > so thatt output looks like this
> > manager personlevel person name
> > 2085 1 Howard Wilson1
> > 2085 2 Howard Wilson2
> > 2085 3 Howard Wilson3
> > 2085 4 Howard Wilson4
> > 2085 5 Howard Wilson5
> > null null null
> > 6086 1 Andrew Saxon
> > 6086 2 Andrew Saxon
> > 6086 3 Ian Thompson
> > 6086 4 Ian Thompson
> > 6086 5 Phil Dargan
> > Brlliant minds any solution for this..
> > i know can i loop through the records and do it
> > and check for a new manager
> > but i want a better solution ..
> > give me your ideads folks..
> > Regards,
> > Navin Mahindroo

How to input and output simultaneously in SQL 2005 Express?

I want to process data of an old VS6 project in VisualStudio2005.
First part is done. With SQL Management Studio I created the database file 'dbTest4' and attached it to SQL 2005 Express server (.\SQLEXPRESS).
Then I recoded the VS6_C++ project feeding its data into the database file using the ConnectionString:
"Provider='sqloledb';Data Source='.';Initial Catalog='dbTest4';Integrated Security='SSPI')"
Until here its working fine.

Assecond part I want to catch these data with VisualStudio2005 for processing there.
In VisualStudio 2005 I created a Windows Application as new project added the database file 'dbTest4' as data source, waited two minutes till VisualStudio2005 had created the DataGridTools bound to the tabbles of my database file and dropped a grid tool on to the surface of the new Form.
Great, within 5 minutes I had created a working database solution without writing a single line of code and it worked well. Starting the new build exe as standlolone it showed the data of my database file in a nice grid view.
But it worked as standolone only. When I tried to combine both parts one part was blockedalways producing error messages as ' ..error 32 (The process cannot access the file because it is being used by another process.) while attempting to open the file '...\dbTest4'.

Does anybody know
how to get this input/output combination running
or is ist impossible to reach my target with SQL 2005 Express?

After reading through Roger Wolters November 2005 article SQL Server 2005 Express Edition User Instances and some hours of testing I came to the conclusion:
When using Visual Studio not exclusively but simultaneously with other clients as SQLcmd, SQL Managent Studio or what else, as shown in my screenshot 325 you should consider to
set 'User Instance=False'
if you are struck by connection problems.
For details you are welcome to visit my sreenshot folder. By clicking the numbered screenshots, enlarging them and using your browsers back button you can follow a detailed installation and see why, where and how to set 'User Instance=False'.
Martin

How to include the date as part of the log file name...

Hello to all,
I'm using SQL Server agent to perform some schedule jobs.
I have it setup so that it will write to an result output
file (Jobs Steps -> Advanced -> Output file:
The problem is that I would like to name this output file
such as 'result%date%.log' where %date% is the current
date so that I can get a seperate log file each day.
Does anyone know how to do this ? %date% does not work.
Thanks in advance.
KinKin:
The way I have it up is that I do archiving of these output log files every
day as a separate job. In the archiving process I copy the files and add
date to the filename as follows:
declare @.cmd sysname
declare @.var sysname
set @.var = convert(varchar(11),getdate(),112)
--add date to file and archive
set @.cmd = 'copy e:\Logs\DBMaintenance\*.* e:\Logs\Archive_Logs\*' + @.var +
'.txt'
exec master..xp_cmdshell @.cmd
"Kin" <ninka_2000@.yahoo.com> wrote in message
news:017501c34fa8$212552a0$a301280a@.phx.gbl...
> Hello to all,
> I'm using SQL Server agent to perform some schedule jobs.
> I have it setup so that it will write to an result output
> file (Jobs Steps -> Advanced -> Output file:
> The problem is that I would like to name this output file
> such as 'result%date%.log' where %date% is the current
> date so that I can get a seperate log file each day.
> Does anyone know how to do this ? %date% does not work.
> Thanks in advance.
> Kin|||I'm not sure that there is a direct way to do that in SQL Agent. You could
always build you're own backup log command and execute that directly. Also,
you could simply let the job name it what it wants, and then add a step that
uses xp_cmdshell to rename the file.
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Kin" <ninka_2000@.yahoo.com> wrote in message
news:017501c34fa8$212552a0$a301280a@.phx.gbl...
> Hello to all,
> I'm using SQL Server agent to perform some schedule jobs.
> I have it setup so that it will write to an result output
> file (Jobs Steps -> Advanced -> Output file:
> The problem is that I would like to name this output file
> such as 'result%date%.log' where %date% is the current
> date so that I can get a seperate log file each day.
> Does anyone know how to do this ? %date% does not work.
> Thanks in advance.
> Kin|||>--Original Message--
>Hello to all,
>I'm using SQL Server agent to perform some schedule
jobs.
>I have it setup so that it will write to an result output
>file (Jobs Steps -> Advanced -> Output file:
>The problem is that I would like to name this output file
>such as 'result%date%.log' where %date% is the current
>date so that I can get a seperate log file each day.
>Does anyone know how to do this ? %date% does not work.
>Thanks in advance.
>Kin
>.
>

How to improve query

Following is a query based on Northwind. I need to output the highest
order value for each date.
The following works. However I want to see if there is any way to
remove the nested query and do some joins.
select a.orderid, a.orderdate, sum(b.quantity * b.unitprice) as total
from orders a, [order details] b
where a.orderid = b.orderid
group by a.orderid, a.orderdate
having sum(b.quantity * b.unitprice) = (select max(total1)
from (select sum(quantity*unitprice) as total1, orders.orderdate as
date, orders.orderid
from [order details], orders
where [order details].orderid = orders.orderid
group by orders.orderdate, orders.orderid
) as C
where c.date = a.orderdate)
order by a.orderdate asc
Thanks,Here is another way (but I'm not sure if it's better, though; in fact,
the execution plan indicates that it's worse):
select x.orderid, x.orderdate, x.total
from (
select o1.orderid, o1.orderdate,
sum(d1.quantity * d1.unitprice) as total
from orders o1
inner join [order details] d1 on o1.orderid = d1.orderid
group by o1.orderid, o1.orderdate
) x inner join (
select orderdate, max(total) as max_total
from (
select o2.orderid, o2.orderdate,
sum(d2.quantity * d2.unitprice) as total
from orders o2
inner join [order details] d2 on o2.orderid = d2.orderid
group by o2.orderid, o2.orderdate
) y
group by orderdate
) z on x.orderdate=z.orderdate and x.total=z.max_total
order by x.orderdate
We can use a view (or a CTE in SQL Server 2005), instead of the x and y
derived tables, but this will not improve the performance (just the
readability).
Razvan

How to import a XSD and XML file into a SQL table

I have generated the output of a SQL query against Northwind in the
form of the XSD and the XML file for a query using the following query:
For generating the XML output:
SELECT * FROM Customers
FOR XML AUTO
Similarly, I use the following query to generate a XSD output of the
above query from the Northwind database:
SELECT * FROM Customers
WHERE 1 = 0
FOR XML AUTO, ELEMENTS, XMLDATA
Now I have the dump of the customers table in a XSD and XML format I
want to import the data in the resulting table into another database
using DTS or whatever to interpre the .XSD and the .XML files.
Please share the code to import a arbitrary .XML and .XSD file into a
SQL table and the meta data SQL must determine dynamically to create a
table in the target database.
Thanks
KarenDownload SQLXML 3.0
(http://www.microsoft.com/downloads/...&DisplayLang=en)
and read the documentation on the XML Bulk Load component. It's a COM
component you can call from a script (such as a .vbs) that will import data
from an XML file into a SQL Server database based on the mappings defined in
a schema.
Cheers,
Graeme
--
Graeme Malcolm
Principal Technologist
Content Master
- a member of CM Group Ltd.
www.contentmaster.com
<karenmiddleol@.yahoo.com> wrote in message
news:1116579889.377764.305650@.g44g2000cwa.googlegroups.com...
I have generated the output of a SQL query against Northwind in the
form of the XSD and the XML file for a query using the following query:
For generating the XML output:
SELECT * FROM Customers
FOR XML AUTO
Similarly, I use the following query to generate a XSD output of the
above query from the Northwind database:
SELECT * FROM Customers
WHERE 1 = 0
FOR XML AUTO, ELEMENTS, XMLDATA
Now I have the dump of the customers table in a XSD and XML format I
want to import the data in the resulting table into another database
using DTS or whatever to interpre the .XSD and the .XML files.
Please share the code to import a arbitrary .XML and .XSD file into a
SQL table and the meta data SQL must determine dynamically to create a
table in the target database.
Thanks
Karen|||http://msdn.microsoft.com/library/d... />
sqlxml.asp
Download the sample code, project for this article as well as reading it...
One of the sample projects shows the nearly exaclty the code you inquired
about...|||Just a clarification: XMLDATA does not generate an XSD but an XDR file. You
have to use a mid-tier tool that converts the XDR into XSD if you really
need an XSD (or use SQL Server 2005 where you can specify XMLSCHEMA to get
an XSD).
Best regards
Michael
<karenmiddleol@.yahoo.com> wrote in message
news:1116579889.377764.305650@.g44g2000cwa.googlegroups.com...
>I have generated the output of a SQL query against Northwind in the
> form of the XSD and the XML file for a query using the following query:
> For generating the XML output:
> SELECT * FROM Customers
> FOR XML AUTO
> Similarly, I use the following query to generate a XSD output of the
> above query from the Northwind database:
> SELECT * FROM Customers
> WHERE 1 = 0
> FOR XML AUTO, ELEMENTS, XMLDATA
>
> Now I have the dump of the customers table in a XSD and XML format I
> want to import the data in the resulting table into another database
> using DTS or whatever to interpre the .XSD and the .XML files.
> Please share the code to import a arbitrary .XML and .XSD file into a
> SQL table and the meta data SQL must determine dynamically to create a
> table in the target database.
> Thanks
> Karen
>|||Anyone knows how to dump the result of the XML AUTO query to an XML
file?
I have a query and used bcp to dump it and i named it with an XML
extension. If the result dump is very short like 3 records only. It
is displaying OK when I try to opien it in IE Browser. But when the
result of my BCP dump is big.. it is producing an error. The thing is,
If I executr my query directly over the Browser (using HTTP with my
database tied up to a Virtual Directory in IIS), It is displaying all
the right results.
Can anyone help me on this? I just need to dump the XML result into a
file so it can be archived and viewed.
Thanks,
Jeff
pongaski
---
Posted via http://www.mcse.ms
---
View this thread: http://www.mcse.ms/message1626243.html|||Programmatically, you should use ADO or ADO.Net to write the FOR XML result
into the file stream. Alternatively, you can use the HTTP access through the
virtual directory and use view source, save as to save it...
Best regards
Michael
"pongaski" <pongaski.1pudhz@.mail.mcse.ms> wrote in message
news:pongaski.1pudhz@.mail.mcse.ms...
> Anyone knows how to dump the result of the XML AUTO query to an XML
> file?
> I have a query and used bcp to dump it and i named it with an XML
> extension. If the result dump is very short like 3 records only. It
> is displaying OK when I try to opien it in IE Browser. But when the
> result of my BCP dump is big.. it is producing an error. The thing is,
> If I executr my query directly over the Browser (using HTTP with my
> database tied up to a Virtual Directory in IIS), It is displaying all
> the right results.
> Can anyone help me on this? I just need to dump the XML result into a
> file so it can be archived and viewed.
> Thanks,
> Jeff
>
> --
> pongaski
> ---
> Posted via http://www.mcse.ms
> ---
> View this thread: http://www.mcse.ms/message1626243.html
>|||"Michael Rys [MSFT]" wrote:

> Programmatically, you should use ADO or ADO.Net to write the FOR XML resul
t
> into the file stream. Alternatively, you can use the HTTP access through t
he
> virtual directory and use view source, save as to save it...
> Best regards
> Michael
It is not true. Lower I write stored proc wich write result "FOR XML" query
to txt file.
CREATE PROCEDURE usp_SQLtoXMLfile (
@.sqlXmlQuerryInput NVARCHAR(4000), --Query text with FOR XML
@.FileNameInput varchar(255), --File name with full path
@.headInput nvarchar(4000), --Prefix expression in result file
@.sufixInput nvarchar(4000) --postfix expression in result file
) AS
DECLARE @.FileName varchar(255) --\
DECLARE @.Text1 nvarchar(4000) --|
DECLARE @.FS int --|
DECLARE @.OLEResult int -- \
DECLARE @.FileID int -- /The bloc variables for work with files
DECLARE @.hr int --|
DECLARE @.source varchar(30) --|
DECLARE @.desc varchar (200) --/
DECLARE @.text NVARCHAR(4000) --bufer only
declare @.cur_edit cursor
declare @._kfvalue sql_variant --Return value
DECLARE @.sqlXmlQuerry NVARCHAR(4000) --Variable for query
BEGIN
----
--
EXECUTE @.OLEResult = sp_OACreate 'Scripting.FileSystemObject', @.FS
OUTPUT --create object for work with file system
IF @.OLEResult <> 0 --chek errors
BEGIN
PRINT 'Scripting.FileSystemObject'
GOTO Error_Handler
END
SET @.FileName=@.FileNameInput
execute @.OLEResult = sp_OAMethod @.FS,'CreateTextFile',@.FileID OUTPUT,
@.FileName --Create text file
IF @.OLEResult <> 0
BEGIN
PRINT 'CreateTextFile'
GOTO Error_Handler
END
----
--
IF LEN(@.headInput)>0 --Check prefix empty
BEGIN
execute @.OLEResult = sp_OAMethod @.FileID, 'WriteLine', NULL, @.headInput
--Write to text file prefix
IF @.OLEResult <> 0
BEGIN
PRINT 'WriteLine'
GOTO Error_Handler
END
END
----
--
SET @.sqlXmlQuerry=@.sqlXmlQuerryInput --Query text
SET @.sqlXmlQuerry=' SELECT * FROM OPENROWSET(''MSDASQL'',''DRIVER={SQL
Server};SERVER='+@.@.SERVERNAME+''','''+@.s
qlXmlQuerry+''') ' --Adapt query for
cursor
SET @.sqlXmlQuerry=' set @.cur_edit = CURSOR STATIC FOR '+@.sqlXmlQuerry+' open
@.cur_edit ' --Add to query string declaration and opening cursor
--PRINT @.sqlXmlQuerry --for debug
exec sp_executesql @.sqlXmlQuerry, N'@.cur_edit cursor output, @._kfvalue
sql_variant', @.cur_edit output, @._kfvalue --Execute the string
FETCH NEXT FROM @.cur_edit INTO @.text --Return from cursor
WHILE (@.@.fetch_status = 0)
BEGIN
----
--
execute @.OLEResult = sp_OAMethod @.FileID, 'Write', NULL, @.text --Add to
text file value from @.text
IF @.OLEResult <> 0
BEGIN
PRINT 'WriteLine'
GOTO Error_Handler
END
FETCH NEXT FROM @.cur_edit INTO @.text
END
CLOSE @.cur_edit
DEALLOCATE @.cur_edit
----
--
IF LEN(@.sufixInput)>0 --If variable with postfix expression not empty
BEGIN
execute @.OLEResult = sp_OAMethod @.FileID, 'Write', NULL, @.sufixInput
--Write postfix expression to text file
IF @.OLEResult <> 0
BEGIN
PRINT 'WriteLine'
GOTO Error_Handler
END
END
----
--goto Done
Error_Handler:
PRINT '*** ERROR ***'
EXEC @.hr = sp_OAGetErrorInfo null, @.source OUT, @.desc OUT
SELECT hr = CONVERT (binary(4), @.hr), source = @.source, description = @.desc
Done:
EXECUTE @.OLEResult = sp_OADestroy @.FileID
EXECUTE @.OLEResult = sp_OADestroy @.FS
END
GO
PS Sorry for my english...|||You can use sp_OA stored procs, but it is not something that I recommend
(note that I say "should" and not "it is the only way" :-)).
Thanks for the posting though and no worries about the English. TSQL crosses
language-boundaries :-)
Michael
"Serbat_Ivan" <SerbatIvan@.discussions.microsoft.com> wrote in message
news:C525C398-29B0-4FF1-A494-29D6B61CD66E@.microsoft.com...
> "Michael Rys [MSFT]" wrote:
>
> It is not true. Lower I write stored proc wich write result "FOR XML"
> query
> to txt file.
> CREATE PROCEDURE usp_SQLtoXMLfile (
> @.sqlXmlQuerryInput NVARCHAR(4000), --Query text with FOR XML
> @.FileNameInput varchar(255), --File name with full path
> @.headInput nvarchar(4000), --Prefix expression in result file
> @.sufixInput nvarchar(4000) --postfix expression in result file
> ) AS
> DECLARE @.FileName varchar(255) --\
> DECLARE @.Text1 nvarchar(4000) --|
> DECLARE @.FS int --|
> DECLARE @.OLEResult int -- \
> DECLARE @.FileID int -- /The bloc variables for work with files
> DECLARE @.hr int --|
> DECLARE @.source varchar(30) --|
> DECLARE @.desc varchar (200) --/
> DECLARE @.text NVARCHAR(4000) --bufer only
> declare @.cur_edit cursor
> declare @._kfvalue sql_variant --Return value
> DECLARE @.sqlXmlQuerry NVARCHAR(4000) --Variable for query
> BEGIN
> ----
--
> EXECUTE @.OLEResult = sp_OACreate 'Scripting.FileSystemObject', @.FS
> OUTPUT --create object for work with file system
> IF @.OLEResult <> 0 --chek errors
> BEGIN
> PRINT 'Scripting.FileSystemObject'
> GOTO Error_Handler
> END
> SET @.FileName=@.FileNameInput
> execute @.OLEResult = sp_OAMethod @.FS,'CreateTextFile',@.FileID OUTPUT,
> @.FileName --Create text file
> IF @.OLEResult <> 0
> BEGIN
> PRINT 'CreateTextFile'
> GOTO Error_Handler
> END
> ----
--
> IF LEN(@.headInput)>0 --Check prefix empty
> BEGIN
> execute @.OLEResult = sp_OAMethod @.FileID, 'WriteLine', NULL, @.headInput
> --Write to text file prefix
> IF @.OLEResult <> 0
> BEGIN
> PRINT 'WriteLine'
> GOTO Error_Handler
> END
> END
> ----
--
> SET @.sqlXmlQuerry=@.sqlXmlQuerryInput --Query text
> SET @.sqlXmlQuerry=' SELECT * FROM OPENROWSET(''MSDASQL'',''DRIVER={SQL
> Server};SERVER='+@.@.SERVERNAME+''','''+@.s
qlXmlQuerry+''') ' --Adapt query
> for
> cursor
> SET @.sqlXmlQuerry=' set @.cur_edit = CURSOR STATIC FOR '+@.sqlXmlQuerry+'
> open
> @.cur_edit ' --Add to query string declaration and opening cursor
> --PRINT @.sqlXmlQuerry --for debug
> exec sp_executesql @.sqlXmlQuerry, N'@.cur_edit cursor output, @._kfvalue
> sql_variant', @.cur_edit output, @._kfvalue --Execute the string
> FETCH NEXT FROM @.cur_edit INTO @.text --Return from cursor
> WHILE (@.@.fetch_status = 0)
> BEGIN
> ----
--
> execute @.OLEResult = sp_OAMethod @.FileID, 'Write', NULL, @.text --Add
> to
> text file value from @.text
> IF @.OLEResult <> 0
> BEGIN
> PRINT 'WriteLine'
> GOTO Error_Handler
> END
> FETCH NEXT FROM @.cur_edit INTO @.text
> END
> CLOSE @.cur_edit
> DEALLOCATE @.cur_edit
> ----
--
> IF LEN(@.sufixInput)>0 --If variable with postfix expression not empty
> BEGIN
> execute @.OLEResult = sp_OAMethod @.FileID, 'Write', NULL, @.sufixInput
> --Write postfix expression to text file
> IF @.OLEResult <> 0
> BEGIN
> PRINT 'WriteLine'
> GOTO Error_Handler
> END
> END
> ----
--goto
> Done
> Error_Handler:
> PRINT '*** ERROR ***'
> EXEC @.hr = sp_OAGetErrorInfo null, @.source OUT, @.desc OUT
> SELECT hr = CONVERT (binary(4), @.hr), source = @.source, description =
> @.desc
> Done:
> EXECUTE @.OLEResult = sp_OADestroy @.FileID
> EXECUTE @.OLEResult = sp_OADestroy @.FS
> END
> GO
>
> PS Sorry for my english...
>|||"Michael Rys [MSFT]" wrote:
> You can use sp_OA stored procs, but it is not something that I recommend
> (note that I say "should" and not "it is the only way" :-)).

> Thanks for the posting though and no worries about the English. TSQL cross
es
> language-boundaries :-)
> Michael
You right of all points of course ;)
Why you not recommend use sp_OA stored procs? I know, it is not TSQL in pure
form, but it is one of ways to to solve the problem... Can you describe a
problems wich may appear?|||It is very easy to shoot yourself into the foot and corrupt the server if
you are not careful.
And it has many not so clear interactions (thread safety, memory etc) that
you need to be aware of.
So this is clearly black-belt territory.
Finally, I rather have us provide solutions to these problems instead of
letting us say: "Oh, but sp_OA stored procs can do it" :-).
Best regards
Michael
"Serbat_Ivan" <SerbatIvan@.discussions.microsoft.com> wrote in message
news:E3855E10-23B3-43EA-A416-B743C71E2E0E@.microsoft.com...
> "Michael Rys [MSFT]" wrote:
>
> You right of all points of course ;)
> Why you not recommend use sp_OA stored procs? I know, it is not TSQL in
> pure
> form, but it is one of ways to to solve the problem... Can you describe a
> problems wich may appear?

How to import a XSD and XML file into a SQL table

I have generated the output of a SQL query against Northwind in the
form of the XSD and the XML file for a query using the following query:
For generating the XML output:
SELECT * FROM Customers
FOR XML AUTO
Similarly, I use the following query to generate a XSD output of the
above query from the Northwind database:
SELECT * FROM Customers
WHERE 1 = 0
FOR XML AUTO, ELEMENTS, XMLDATA
Now I have the dump of the customers table in a XSD and XML format I
want to import the data in the resulting table into another database
using DTS or whatever to interpre the .XSD and the .XML files.
Please share the code to import a arbitrary .XML and .XSD file into a
SQL table and the meta data SQL must determine dynamically to create a
table in the target database.
Thanks
Karen
Download SQLXML 3.0
(http://www.microsoft.com/downloads/d...DisplayLang=en)
and read the documentation on the XML Bulk Load component. It's a COM
component you can call from a script (such as a .vbs) that will import data
from an XML file into a SQL Server database based on the mappings defined in
a schema.
Cheers,
Graeme
Graeme Malcolm
Principal Technologist
Content Master
- a member of CM Group Ltd.
www.contentmaster.com
<karenmiddleol@.yahoo.com> wrote in message
news:1116579889.377764.305650@.g44g2000cwa.googlegr oups.com...
I have generated the output of a SQL query against Northwind in the
form of the XSD and the XML file for a query using the following query:
For generating the XML output:
SELECT * FROM Customers
FOR XML AUTO
Similarly, I use the following query to generate a XSD output of the
above query from the Northwind database:
SELECT * FROM Customers
WHERE 1 = 0
FOR XML AUTO, ELEMENTS, XMLDATA
Now I have the dump of the customers table in a XSD and XML format I
want to import the data in the resulting table into another database
using DTS or whatever to interpre the .XSD and the .XML files.
Please share the code to import a arbitrary .XML and .XSD file into a
SQL table and the meta data SQL must determine dynamically to create a
table in the target database.
Thanks
Karen
|||http://msdn.microsoft.com/library/de...exchsqlxml.asp
Download the sample code, project for this article as well as reading it...
One of the sample projects shows the nearly exaclty the code you inquired
about...
|||Just a clarification: XMLDATA does not generate an XSD but an XDR file. You
have to use a mid-tier tool that converts the XDR into XSD if you really
need an XSD (or use SQL Server 2005 where you can specify XMLSCHEMA to get
an XSD).
Best regards
Michael
<karenmiddleol@.yahoo.com> wrote in message
news:1116579889.377764.305650@.g44g2000cwa.googlegr oups.com...
>I have generated the output of a SQL query against Northwind in the
> form of the XSD and the XML file for a query using the following query:
> For generating the XML output:
> SELECT * FROM Customers
> FOR XML AUTO
> Similarly, I use the following query to generate a XSD output of the
> above query from the Northwind database:
> SELECT * FROM Customers
> WHERE 1 = 0
> FOR XML AUTO, ELEMENTS, XMLDATA
>
> Now I have the dump of the customers table in a XSD and XML format I
> want to import the data in the resulting table into another database
> using DTS or whatever to interpre the .XSD and the .XML files.
> Please share the code to import a arbitrary .XML and .XSD file into a
> SQL table and the meta data SQL must determine dynamically to create a
> table in the target database.
> Thanks
> Karen
>
|||Anyone knows how to dump the result of the XML AUTO query to an XML file?
I have a query and used bcp to dump it and i named it with an XML extension. If the result dump is very short like 3 records only. It is displaying OK when I try to opien it in IE Browser. But when the result of my BCP dump is big.. it is producing an error. The thing is, If I executr my query directly over the Browser (using HTTP with my database tied up to a Virtual Directory in IIS), It is displaying all the right results.
Can anyone help me on this? I just need to dump the XML result into a file so it can be archived and viewed.
Thanks,
Jeff|||Programmatically, you should use ADO or ADO.Net to write the FOR XML result
into the file stream. Alternatively, you can use the HTTP access through the
virtual directory and use view source, save as to save it...
Best regards
Michael
"pongaski" <pongaski.1pudhz@.mail.mcse.ms> wrote in message
news:pongaski.1pudhz@.mail.mcse.ms...
> Anyone knows how to dump the result of the XML AUTO query to an XML
> file?
> I have a query and used bcp to dump it and i named it with an XML
> extension. If the result dump is very short like 3 records only. It
> is displaying OK when I try to opien it in IE Browser. But when the
> result of my BCP dump is big.. it is producing an error. The thing is,
> If I executr my query directly over the Browser (using HTTP with my
> database tied up to a Virtual Directory in IIS), It is displaying all
> the right results.
> Can anyone help me on this? I just need to dump the XML result into a
> file so it can be archived and viewed.
> Thanks,
> Jeff
>
> --
> pongaski
> Posted via http://www.mcse.ms
> View this thread: http://www.mcse.ms/message1626243.html
>
|||"Michael Rys [MSFT]" wrote:

> Programmatically, you should use ADO or ADO.Net to write the FOR XML result
> into the file stream. Alternatively, you can use the HTTP access through the
> virtual directory and use view source, save as to save it...
> Best regards
> Michael
It is not true. Lower I write stored proc wich write result "FOR XML" query
to txt file.
CREATE PROCEDURE usp_SQLtoXMLfile (
@.sqlXmlQuerryInput NVARCHAR(4000), --Query text with FOR XML
@.FileNameInput varchar(255), --File name with full path
@.headInput nvarchar(4000), --Prefix expression in result file
@.sufixInput nvarchar(4000) --postfix expression in result file
) AS
DECLARE @.FileName varchar(255)--\
DECLARE @.Text1 nvarchar(4000)--|
DECLARE @.FS int --|
DECLARE @.OLEResult int -- \
DECLARE @.FileID int -- /The bloc variables for work with files
DECLARE @.hr int--|
DECLARE @.source varchar(30)--|
DECLARE @.desc varchar (200)--/
DECLARE @.text NVARCHAR(4000)--bufer only
declare @.cur_edit cursor
declare @._kfvalue sql_variant--Return value
DECLARE @.sqlXmlQuerry NVARCHAR(4000)--Variable for query
BEGIN
EXECUTE @.OLEResult = sp_OACreate 'Scripting.FileSystemObject', @.FS
OUTPUT--create object for work with file system
IF @.OLEResult <> 0 --chek errors
BEGIN
PRINT 'Scripting.FileSystemObject'
GOTO Error_Handler
END
SET @.FileName=@.FileNameInput
execute @.OLEResult = sp_OAMethod @.FS,'CreateTextFile',@.FileID OUTPUT,
@.FileName --Create text file
IF @.OLEResult <> 0
BEGIN
PRINT 'CreateTextFile'
GOTO Error_Handler
END
-----
IF LEN(@.headInput)>0 --Check prefix empty
BEGIN
execute @.OLEResult = sp_OAMethod @.FileID, 'WriteLine', NULL, @.headInput
--Write to text file prefix
IF @.OLEResult <> 0
BEGIN
PRINT 'WriteLine'
GOTO Error_Handler
END
END
-----
SET @.sqlXmlQuerry=@.sqlXmlQuerryInput --Query text
SET @.sqlXmlQuerry=' SELECT * FROM OPENROWSET(''MSDASQL'',''DRIVER={SQL
Server};SERVER='+@.@.SERVERNAME+''','''+@.sqlXmlQuerr y+''') '--Adapt query for
cursor
SET @.sqlXmlQuerry=' set @.cur_edit = CURSOR STATIC FOR '+@.sqlXmlQuerry+' open
@.cur_edit ' --Add to query string declaration and opening cursor
--PRINT @.sqlXmlQuerry --for debug
exec sp_executesql @.sqlXmlQuerry, N'@.cur_edit cursor output, @._kfvalue
sql_variant', @.cur_edit output, @._kfvalue --Execute the string
FETCH NEXT FROM @.cur_edit INTO @.text --Return from cursor
WHILE (@.@.fetch_status = 0)
BEGIN
-----
execute @.OLEResult = sp_OAMethod @.FileID, 'Write', NULL, @.text --Add to
text file value from @.text
IF @.OLEResult <> 0
BEGIN
PRINT 'WriteLine'
GOTO Error_Handler
END
FETCH NEXT FROM @.cur_edit INTO @.text
END
CLOSE @.cur_edit
DEALLOCATE @.cur_edit
-----
IF LEN(@.sufixInput)>0 --If variable with postfix expression not empty
BEGIN
execute @.OLEResult = sp_OAMethod @.FileID, 'Write', NULL, @.sufixInput
--Write postfix expression to text file
IF @.OLEResult <> 0
BEGIN
PRINT 'WriteLine'
GOTO Error_Handler
END
END
-----goto Done
Error_Handler:
PRINT '*** ERROR ***'
EXEC @.hr = sp_OAGetErrorInfo null, @.source OUT, @.desc OUT
SELECT hr = CONVERT (binary(4), @.hr), source = @.source, description = @.desc
Done:
EXECUTE @.OLEResult = sp_OADestroy @.FileID
EXECUTE @.OLEResult = sp_OADestroy @.FS
END
GO
PS Sorry for my english...
|||You can use sp_OA stored procs, but it is not something that I recommend
(note that I say "should" and not "it is the only way" :-)).
Thanks for the posting though and no worries about the English. TSQL crosses
language-boundaries :-)
Michael
"Serbat_Ivan" <SerbatIvan@.discussions.microsoft.com> wrote in message
news:C525C398-29B0-4FF1-A494-29D6B61CD66E@.microsoft.com...
> "Michael Rys [MSFT]" wrote:
> It is not true. Lower I write stored proc wich write result "FOR XML"
> query
> to txt file.
> CREATE PROCEDURE usp_SQLtoXMLfile (
> @.sqlXmlQuerryInput NVARCHAR(4000), --Query text with FOR XML
> @.FileNameInput varchar(255), --File name with full path
> @.headInput nvarchar(4000), --Prefix expression in result file
> @.sufixInput nvarchar(4000) --postfix expression in result file
> ) AS
> DECLARE @.FileName varchar(255) --\
> DECLARE @.Text1 nvarchar(4000) --|
> DECLARE @.FS int --|
> DECLARE @.OLEResult int -- \
> DECLARE @.FileID int -- /The bloc variables for work with files
> DECLARE @.hr int --|
> DECLARE @.source varchar(30) --|
> DECLARE @.desc varchar (200) --/
> DECLARE @.text NVARCHAR(4000) --bufer only
> declare @.cur_edit cursor
> declare @._kfvalue sql_variant --Return value
> DECLARE @.sqlXmlQuerry NVARCHAR(4000) --Variable for query
> BEGIN
> ----
> EXECUTE @.OLEResult = sp_OACreate 'Scripting.FileSystemObject', @.FS
> OUTPUT --create object for work with file system
> IF @.OLEResult <> 0 --chek errors
> BEGIN
> PRINT 'Scripting.FileSystemObject'
> GOTO Error_Handler
> END
> SET @.FileName=@.FileNameInput
> execute @.OLEResult = sp_OAMethod @.FS,'CreateTextFile',@.FileID OUTPUT,
> @.FileName --Create text file
> IF @.OLEResult <> 0
> BEGIN
> PRINT 'CreateTextFile'
> GOTO Error_Handler
> END
> -----
> IF LEN(@.headInput)>0 --Check prefix empty
> BEGIN
> execute @.OLEResult = sp_OAMethod @.FileID, 'WriteLine', NULL, @.headInput
> --Write to text file prefix
> IF @.OLEResult <> 0
> BEGIN
> PRINT 'WriteLine'
> GOTO Error_Handler
> END
> END
> -----
> SET @.sqlXmlQuerry=@.sqlXmlQuerryInput --Query text
> SET @.sqlXmlQuerry=' SELECT * FROM OPENROWSET(''MSDASQL'',''DRIVER={SQL
> Server};SERVER='+@.@.SERVERNAME+''','''+@.sqlXmlQuerr y+''') ' --Adapt query
> for
> cursor
> SET @.sqlXmlQuerry=' set @.cur_edit = CURSOR STATIC FOR '+@.sqlXmlQuerry+'
> open
> @.cur_edit ' --Add to query string declaration and opening cursor
> --PRINT @.sqlXmlQuerry --for debug
> exec sp_executesql @.sqlXmlQuerry, N'@.cur_edit cursor output, @._kfvalue
> sql_variant', @.cur_edit output, @._kfvalue --Execute the string
> FETCH NEXT FROM @.cur_edit INTO @.text --Return from cursor
> WHILE (@.@.fetch_status = 0)
> BEGIN
> -----
> execute @.OLEResult = sp_OAMethod @.FileID, 'Write', NULL, @.text --Add
> to
> text file value from @.text
> IF @.OLEResult <> 0
> BEGIN
> PRINT 'WriteLine'
> GOTO Error_Handler
> END
> FETCH NEXT FROM @.cur_edit INTO @.text
> END
> CLOSE @.cur_edit
> DEALLOCATE @.cur_edit
> -----
> IF LEN(@.sufixInput)>0 --If variable with postfix expression not empty
> BEGIN
> execute @.OLEResult = sp_OAMethod @.FileID, 'Write', NULL, @.sufixInput
> --Write postfix expression to text file
> IF @.OLEResult <> 0
> BEGIN
> PRINT 'WriteLine'
> GOTO Error_Handler
> END
> END
> -----goto
> Done
> Error_Handler:
> PRINT '*** ERROR ***'
> EXEC @.hr = sp_OAGetErrorInfo null, @.source OUT, @.desc OUT
> SELECT hr = CONVERT (binary(4), @.hr), source = @.source, description =
> @.desc
> Done:
> EXECUTE @.OLEResult = sp_OADestroy @.FileID
> EXECUTE @.OLEResult = sp_OADestroy @.FS
> END
> GO
>
> PS Sorry for my english...
>
|||"Michael Rys [MSFT]" wrote:
> You can use sp_OA stored procs, but it is not something that I recommend
> (note that I say "should" and not "it is the only way" :-)).

> Thanks for the posting though and no worries about the English. TSQL crosses
> language-boundaries :-)
> Michael
You right of all points of course ;)
Why you not recommend use sp_OA stored procs? I know, it is not TSQL in pure
form, but it is one of ways to to solve the problem... Can you describe a
problems wich may appear?
|||It is very easy to shoot yourself into the foot and corrupt the server if
you are not careful.
And it has many not so clear interactions (thread safety, memory etc) that
you need to be aware of.
So this is clearly black-belt territory.
Finally, I rather have us provide solutions to these problems instead of
letting us say: "Oh, but sp_OA stored procs can do it" :-).
Best regards
Michael
"Serbat_Ivan" <SerbatIvan@.discussions.microsoft.com> wrote in message
news:E3855E10-23B3-43EA-A416-B743C71E2E0E@.microsoft.com...
> "Michael Rys [MSFT]" wrote:
>
> You right of all points of course ;)
> Why you not recommend use sp_OA stored procs? I know, it is not TSQL in
> pure
> form, but it is one of ways to to solve the problem... Can you describe a
> problems wich may appear?

How to implement split and merge?

I need to implement following flow:

[Source] - > [Split]

- [IF TRUE] -> [DO SOMETHING] - [OUTPUT TO C]

- [IF FALSE] -> [DO SOMETHING ELSE] - [OUTPUT TO C]

The issue is to come back to the same destination after conditional split.

In other words, is there something opposite to "Multicast"?

I looked at Union and Merge - but it looks like it's not for this kind of things.

Thanks.

That sounds like a UnionAll to me...Why do you think UnionAll is not for this kind of thing? Do the two output (true output and the false output) downstreams change the column metadata in different ways?

thanks

wenyang

|||

I may not be understaning Union All well. Doesn't it require bith streams to have some data in it? To map fields from both streams?

In my scenario - only one stream has data in the buffer at the same time.

|||

There is no requirement for both inputs into a UNION ALL component to both have data. It will work perfectly well if no rows at all are passed through it - which is of course true for all components.

-Jamie

|||This worked! Thanks!