Grid display: creating

Showing posts with label creating. Show all posts

Monday, March 26, 2012

Generic report and dataset

Hello,
I am new to SRS. So this is a new-be question.
When creating a new report in visual studio. When creating a new dataset.
Do you always need to create a query string to retrieve the fields that can
be used on the form?
Is there some SQL picker query builder. Where you can build a query by
picking and choosing from all tables?
To help people out?
thanksOn Nov 2, 4:52 pm, "greg" <g...@.nospam.com> wrote:
> Hello,
> I am new to SRS. So this is a new-be question.
> When creating a new report in visual studio. When creating a new dataset.
> Do you always need to create a query string to retrieve the fields that can
> be used on the form?
> Is there some SQL picker query builder. Where you can build a query by
> picking and choosing from all tables?
> To help people out?
> thanks
In the Report Wizard, there is a Generic Query Builder; however, I'm
not sure if that will meet your needs completely. Also, there is a
technology called Linq that is being used in Visual Studio 2008 that
allows this type of functionality; however, I'm not sure if it will be
available in SSRS/Reporting Services 2008. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant

Generic report and dataset

generating XML from SQL

Hi all,

Can anybody advice me how can i go about creating an XML from SQL. The need arised when our jobs which create Xmls from the select statement using vbscript hang in the executing state because the file gets a sharing voilation so we have to change the generated filename on the vbscript code frquently so i thought about can i generate it directly from SQL......

Any ideas ?

this solution would be very helpful for me

Thanks in ADVANCE

Jacx

This may help you

XML support in SQL Server 2005 (just in case you're not familiar with what is available):
http://msdn2.microsoft.com/en-us/library/ms345117.aspx#sql2k5xml_topic3

Constructing XML using SQL:
http://msdn2.microsoft.com/en-us/ms178107.aspx

Wednesday, March 21, 2012

Generating scripts and creating backups

Hi All,
When to opt for generating scripts and exporting data and when to create a
backup and restore the backup?
kd
If you are looking to maintain a standby server or a backup server, then
BACKUP/RESTORE is the safest way, as backup makes exact copies of the
database.
If you want to send your data to a non-SQL Server environment or database,
then you might want to export data out into text files. Also, even between 2
SQL Servers, if you just want to copy some tables (and not the entire
database), then you could go with DTS or BCP to export the data.
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"kd" <kd@.discussions.microsoft.com> wrote in message
news:4DC56605-A663-424F-98A4-147F1CE189AE@.microsoft.com...
> Hi All,
> When to opt for generating scripts and exporting data and when to create a
> backup and restore the backup?
> kd
|||you might want to geneerate scripts if your database is incorporated as part
of an automated build process and coupled to some software product.
Greg Jackson
PDX, Oregon

generating script with SMO

I am taking my table names and colums from xml file and then generating the script and executing it.I have problem creating the script for incerting table with multiple colums.Maybe the problem is that I am using a for loop to get all column names from a array string.Has anyone idea why it is not working? When I try insert of a table without loop it is wirking!Here is the code :
Server server = new Server(); // Create table in my personal database Database db = server.Databases["new"]; // Make Sure Constraint is Scripted ScriptingOptions so = new ScriptingOptions(); so.IncludeHeaders = true; so.SchemaQualify = true; // Create In-Memory Table Table newTable = new Table(db, Names[0].ToString()); for (int i = 1; i < Names.Length; i++) { Column newColumn = new Column(newTable,Names Idea ); newTable.Columns.Add(newColumn); } foreach (string s in newTable.Script(so)) /// Error generating the script { myTableScript = myTableScript + s; } db.ExecuteNonQuery(myTableScript); //disconnect form Database connection.Close();

Could you please metion the full error you are getting.

Thanks,
Kuntal

|||I fixed the problem.I haven't set the column format and that's why the script was not correctly generated.The loop is functioning now ok. Here is the edit line :Column newColumn = new Column(newTable, attr.Name, DataType.NVarChar(50));Now I can easily take the table and column names from the xml file and directly put them without using array strings.Thanks for yor time :) Have a nice day !

Generating Reports Error, dependent on local groups?

Hi,
I finished creating my reports and have had an issue with generating the
reports on http://localhost/reports. When I login to
http://localhost/reports with a user that is part of the Local Administrators
Group the reports run perfectly correct. But when I login with a user that
is not part of the Local Administrators Group I get the Reporting Services
Error:
An error has occurred during report processing. (rsProcessingAborted) Get
Online Help
Cannot create a connection to data source 'OVSDPROD'.
(rsErrorOpeningConnection) Get Online Help
ERROR [08004] [Oracle][ODBC][Ora]Error while trying to retrieve text for
error ORA-12154 ERROR [IM006] [Microsoft][ODBC Driver Manager] Driver's
SQLSetConnectAttr failed
The user that I am logged in with has the same exact item-level and
system-level roles as the BUILTIN\Administrators and can navigate throughout
the site to get to the reports but when its time to actually generate the
reports I get the error above.
MikeThis is a problem of the user connecting to Oracle, not to Report Server. The
error is coming form ODBC.
Test this by reconfiguring the data source and storing credentials on the
report server so that the credentials you use are the same as those being
used by the other person.
HTH
Charles Kangai, MCT, MCDBA
"mtam79" wrote:
> Hi,
> I finished creating my reports and have had an issue with generating the
> reports on http://localhost/reports. When I login to
> http://localhost/reports with a user that is part of the Local Administrators
> Group the reports run perfectly correct. But when I login with a user that
> is not part of the Local Administrators Group I get the Reporting Services
> Error:
> An error has occurred during report processing. (rsProcessingAborted) Get
> Online Help
> Cannot create a connection to data source 'OVSDPROD'.
> (rsErrorOpeningConnection) Get Online Help
> ERROR [08004] [Oracle][ODBC][Ora]Error while trying to retrieve text for
> error ORA-12154 ERROR [IM006] [Microsoft][ODBC Driver Manager] Driver's
> SQLSetConnectAttr failed
> The user that I am logged in with has the same exact item-level and
> system-level roles as the BUILTIN\Administrators and can navigate throughout
> the site to get to the reports but when its time to actually generate the
> reports I get the error above.
> Mike|||The data source is configured with "Credentials stored securely in the report
server"...
Even though the error states that this is an ODBC issue, it confuses me
since I am able to generate reports with users that are part of the Local
Administrator Group, but anyone outside of that group can not Generate
Reports. If everyone was having problems generating reports then I would
definitely see how it is an ODBC issue.
Any other suggestions?
Thanks,
Mike
"Charles Kangai" wrote:
> This is a problem of the user connecting to Oracle, not to Report Server. The
> error is coming form ODBC.
> Test this by reconfiguring the data source and storing credentials on the
> report server so that the credentials you use are the same as those being
> used by the other person.
> HTH
> Charles Kangai, MCT, MCDBA
> "mtam79" wrote:
> > Hi,
> >
> > I finished creating my reports and have had an issue with generating the
> > reports on http://localhost/reports. When I login to
> > http://localhost/reports with a user that is part of the Local Administrators
> > Group the reports run perfectly correct. But when I login with a user that
> > is not part of the Local Administrators Group I get the Reporting Services
> > Error:
> >
> > An error has occurred during report processing. (rsProcessingAborted) Get
> > Online Help
> > Cannot create a connection to data source 'OVSDPROD'.
> > (rsErrorOpeningConnection) Get Online Help
> > ERROR [08004] [Oracle][ODBC][Ora]Error while trying to retrieve text for
> > error ORA-12154 ERROR [IM006] [Microsoft][ODBC Driver Manager] Driver's
> > SQLSetConnectAttr failed
> >
> > The user that I am logged in with has the same exact item-level and
> > system-level roles as the BUILTIN\Administrators and can navigate throughout
> > the site to get to the reports but when its time to actually generate the
> > reports I get the error above.
> >
> > Mike|||I figured out my problem had to do with the security permissions that were
assigned to the folder where my tnsnames.ora file was located. I needed to
set permissions for Everyone to be able to read that folder so they can
access the file and therefore run the reports.
"mtam79" wrote:
> The data source is configured with "Credentials stored securely in the report
> server"...
> Even though the error states that this is an ODBC issue, it confuses me
> since I am able to generate reports with users that are part of the Local
> Administrator Group, but anyone outside of that group can not Generate
> Reports. If everyone was having problems generating reports then I would
> definitely see how it is an ODBC issue.
> Any other suggestions?
> Thanks,
> Mike
> "Charles Kangai" wrote:
> > This is a problem of the user connecting to Oracle, not to Report Server. The
> > error is coming form ODBC.
> >
> > Test this by reconfiguring the data source and storing credentials on the
> > report server so that the credentials you use are the same as those being
> > used by the other person.
> >
> > HTH
> >
> > Charles Kangai, MCT, MCDBA
> >
> > "mtam79" wrote:
> >
> > > Hi,
> > >
> > > I finished creating my reports and have had an issue with generating the
> > > reports on http://localhost/reports. When I login to
> > > http://localhost/reports with a user that is part of the Local Administrators
> > > Group the reports run perfectly correct. But when I login with a user that
> > > is not part of the Local Administrators Group I get the Reporting Services
> > > Error:
> > >
> > > An error has occurred during report processing. (rsProcessingAborted) Get
> > > Online Help
> > > Cannot create a connection to data source 'OVSDPROD'.
> > > (rsErrorOpeningConnection) Get Online Help
> > > ERROR [08004] [Oracle][ODBC][Ora]Error while trying to retrieve text for
> > > error ORA-12154 ERROR [IM006] [Microsoft][ODBC Driver Manager] Driver's
> > > SQLSetConnectAttr failed
> > >
> > > The user that I am logged in with has the same exact item-level and
> > > system-level roles as the BUILTIN\Administrators and can navigate throughout
> > > the site to get to the reports but when its time to actually generate the
> > > reports I get the error above.
> > >
> > > Mike|||Please also check this KB article, because you may run into the same
security permission issues when trying to setup subscriptions on these
reports: http://support.microsoft.com/default.aspx?scid=kb;en-us;870668
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"mtam79" <mtam79@.discussions.microsoft.com> wrote in message
news:E1E6AF79-39B8-4E1B-AFAA-69A00E5C4B19@.microsoft.com...
> I figured out my problem had to do with the security permissions that were
> assigned to the folder where my tnsnames.ora file was located. I needed
to
> set permissions for Everyone to be able to read that folder so they can
> access the file and therefore run the reports.
>
> "mtam79" wrote:
> > The data source is configured with "Credentials stored securely in the
report
> > server"...
> >
> > Even though the error states that this is an ODBC issue, it confuses me
> > since I am able to generate reports with users that are part of the
Local
> > Administrator Group, but anyone outside of that group can not Generate
> > Reports. If everyone was having problems generating reports then I
would
> > definitely see how it is an ODBC issue.
> >
> > Any other suggestions?
> >
> > Thanks,
> > Mike
> >
> > "Charles Kangai" wrote:
> >
> > > This is a problem of the user connecting to Oracle, not to Report
Server. The
> > > error is coming form ODBC.
> > >
> > > Test this by reconfiguring the data source and storing credentials on
the
> > > report server so that the credentials you use are the same as those
being
> > > used by the other person.
> > >
> > > HTH
> > >
> > > Charles Kangai, MCT, MCDBA
> > >
> > > "mtam79" wrote:
> > >
> > > > Hi,
> > > >
> > > > I finished creating my reports and have had an issue with generating
the
> > > > reports on http://localhost/reports. When I login to
> > > > http://localhost/reports with a user that is part of the Local
Administrators
> > > > Group the reports run perfectly correct. But when I login with a
user that
> > > > is not part of the Local Administrators Group I get the Reporting
Services
> > > > Error:
> > > >
> > > > An error has occurred during report processing.
(rsProcessingAborted) Get
> > > > Online Help
> > > > Cannot create a connection to data source 'OVSDPROD'.
> > > > (rsErrorOpeningConnection) Get Online Help
> > > > ERROR [08004] [Oracle][ODBC][Ora]Error while trying to retrieve text
for
> > > > error ORA-12154 ERROR [IM006] [Microsoft][ODBC Driver Manager]
Driver's
> > > > SQLSetConnectAttr failed
> > > >
> > > > The user that I am logged in with has the same exact item-level and
> > > > system-level roles as the BUILTIN\Administrators and can navigate
throughout
> > > > the site to get to the reports but when its time to actually
generate the
> > > > reports I get the error above.
> > > >
> > > > Mike

Generating Report Error, Local Administrator Group

Monday, March 12, 2012

Generate XML file from stored procedure

I need to develop some crystal reports on some .NET ado datasets.
This is easy to do if I actually had data to work with. It is much
easer creating reports with you have data.

Therefore, I would like to run the stored procedure that the .NET ado
dataset is built on and generate an XML file. I could run the project
and try to figure out where the developers are making the call to the
stored procedure and insert a line to writetoxmlfile. I would rather
not have to mess with their code.

Is there a way working with SQL Server (either query analyzer or
enterprise manager, dts, or whatever) that I can generate an xml file.

I see that I can run a stored procedure and get an xml style return in
query analyzer, but I don't know how to save that as an actual file.

Thanks for the help.

Tony"CrystalDBA" <tturner6@.hotmail.com> wrote in message
news:b0fe186f.0405100539.3807ad32@.posting.google.c om...
> I need to develop some crystal reports on some .NET ado datasets.
> This is easy to do if I actually had data to work with. It is much
> easer creating reports with you have data.
> Therefore, I would like to run the stored procedure that the .NET ado
> dataset is built on and generate an XML file. I could run the project
> and try to figure out where the developers are making the call to the
> stored procedure and insert a line to writetoxmlfile. I would rather
> not have to mess with their code.
> Is there a way working with SQL Server (either query analyzer or
> enterprise manager, dts, or whatever) that I can generate an xml file.
> I see that I can run a stored procedure and get an xml style return in
> query analyzer, but I don't know how to save that as an actual file.
> Thanks for the help.
> Tony

I'm not entirely sure what you're looking for, but you can save the results
from Query Analyzer to a file, or use DTS or osql.exe to write the results
to file automatically. You would probably need to process the file further.
Alternatively, you may be looking for the FOR XML clause of the SELECT
statement - there are examples of using this in Books Online.

If this isn't helpful, perhaps you can give some more specific details of
what you need to achieve.

Simon|||I need an xml file.

When I execute a stored procedure with parameters, I need to output an
actual xml file.

Thanks,

Tony|||"CrystalDBA" <tturner6@.hotmail.com> wrote in message
news:b0fe186f.0405110638.41b97942@.posting.google.c om...
> I need an xml file.
> When I execute a stored procedure with parameters, I need to output an
> actual xml file.
> Thanks,
> Tony

From your previous post, you seem to have a procedure which returns XML,
presumably using the FOR XML clause. In that case, you have to use some
sort of client program to get the results into a file. For example, you
could use osql.exe:

osql.exe -S Server -d Database -E -Q "exec myProc" -o outputfile.txt

Or you can use DTS, or write your own client script using ADO COM objects.
But I believe that even with FOR XML, you get a fragment, not a well-formed
XML document, so you would still need to do some more work with the file to
get a real XML document. If you're using the ADO Command object, for
example, you need to use the Command object's "XML Root" property to add a
root which is then wrapped round the results.

But XML is a big topic, and there are other ways to get XML out of SQL
Server. You might want to post in microsoft.public.sqlserver.xml for more
information.

Simon

Friday, March 9, 2012

Generate SQL scripts creating a separate file per object

Hello all,

What is an easy way of scripting all the database tables and sprocs automatically to a separate file each one? I can't find this option in SQL Server Management Studio.

Or is any other way of uploading all objects to a Visual Source Safe project that does not require to copy and paste each script into a VSS sql script?

Thanks!

David

Using SQL Server 2005, right click the database, choose Tasks-> Generate Scripts. You should see a new window open with an info screen. Click Next. Now you should be at the 'Select Database' screen. Select the database you want to generate scripts for, then check the "Script all objects in the selected database" checkbox. Click Next.

Now make sure your script options are correct. If you use the default properties for SQL Server setup, then default should be fine for this as well. Click Next after making any necessary changes.

Now you are at the "Output Option" screen. Select the "Script to file" radio button, and under "Files to generate", select the "File per object" option. Make sure you set your save location and encoding, if necessary.

At this point, you can either click Next to view a summary of what you just selected, or you can click Finish to start scripting. (There is also a "Finish" button on the summary screen.)

|||

That "File per Object" option does not appear in my SQL Server Manager 2005.

I think I have installed the latest service pack.

|||

neutrino:
That "File per Object" option does not appear in my SQL Server Manager 2005.
I think I have installed the latest service pack.

I'm not sure if it has to do with the service pack. I have two computers that should both be updated to the latest SP. However, I was able to reproduce this problem with a machine running XP Pro, and the SQL Server Management Studio version was 9.00.1399.x.

The version this works on is 9.00.3042.x, and it is running on Server 2003.

Both computers only have the Development version installed, so maybe it has to do with being on a server OS?

Friday, February 24, 2012

Generate Column List

Thanks
Hari
MCDBA
"Keith" <@..> wrote in message news:uuNjPFQWEHA.3664@.TK2MSFTNGP12.phx.gbl...
> Is it possible to generate a list of all column names in a table seperated
> by a comma?
> I want to do this because I am creating some triggers on a large number of
> tables, with a large number of fields in each, and rather than type out my
> list of fields I wanted to generate a list so I could just copy/past into
my
> triggers.
> Thanks
>
|||Using the object browser window in SQL Query Analyzer may help you. Try it:
- Start Query Analyzer, connect to your database server.
- Click from the Menus: Tools | Object Browser | Show/Hide (or just hit F8).
This brings up the Object Browser pane.
- Navigate database object tree in Object Browser pane to table of interest.
- Right-click on that table and pick Script Object to Clipboard as Select |
Insert | Update | Delete as appropriate.
- Click in your query window and hit Control/V to paste the SQL statement.
You get a complete SQL statement with placeholders for things you will need
to supply.
A friend pointed this out to me a while back. Looks like there may be other
tricks available via the templates tab but I've not explored the
possibilities.
"Keith" <@..> wrote in message news:uuNjPFQWEHA.3664@.TK2MSFTNGP12.phx.gbl...
> Is it possible to generate a list of all column names in a table seperated
> by a comma?
> I want to do this because I am creating some triggers on a large number of
> tables, with a large number of fields in each, and rather than type out my
> list of fields I wanted to generate a list so I could just copy/past into
my
> triggers.
> Thanks
>

Generate Column List

Thanks
Hari
MCDBA
"Keith" <@..> wrote in message news:uuNjPFQWEHA.3664@.TK2MSFTNGP12.phx.gbl...
> Is it possible to generate a list of all column names in a table seperated
> by a comma?
> I want to do this because I am creating some triggers on a large number of
> tables, with a large number of fields in each, and rather than type out my
> list of fields I wanted to generate a list so I could just copy/past into
my
> triggers.
> Thanks
>|||Using the object browser window in SQL Query Analyzer may help you. Try it:
- Start Query Analyzer, connect to your database server.
- Click from the Menus: Tools | Object Browser | Show/Hide (or just hit F8).
This brings up the Object Browser pane.
- Navigate database object tree in Object Browser pane to table of interest.
- Right-click on that table and pick Script Object to Clipboard as Select |
Insert | Update | Delete as appropriate.
- Click in your query window and hit Control/V to paste the SQL statement.
You get a complete SQL statement with placeholders for things you will need
to supply.
A friend pointed this out to me a while back. Looks like there may be other
tricks available via the templates tab but I've not explored the
possibilities.
"Keith" <@..> wrote in message news:uuNjPFQWEHA.3664@.TK2MSFTNGP12.phx.gbl...
> Is it possible to generate a list of all column names in a table seperated
> by a comma?
> I want to do this because I am creating some triggers on a large number of
> tables, with a large number of fields in each, and rather than type out my
> list of fields I wanted to generate a list so I could just copy/past into
my
> triggers.
> Thanks
>

Generate Column List

Is it possible to generate a list of all column names in a table seperated
by a comma?
I want to do this because I am creating some triggers on a large number of
tables, with a large number of fields in each, and rather than type out my
list of fields I wanted to generate a list so I could just copy/past into my
triggers.
ThanksHi,
Simple way is:
In query Analyzer ,
Go to Tools menu -- Options -- Result Tab -- Results Output format --
Select " Comma Delimited (CSV)".
Now excute the below command in text output mode (Query menu -- click --
Results in Text):-
set rowcount 1
select * from table_name
This will give the output in comma delimited. The first row is column names.
:)
Thanks
Hari
MCDBA
"Keith" <@..> wrote in message news:uuNjPFQWEHA.3664@.TK2MSFTNGP12.phx.gbl...
> Is it possible to generate a list of all column names in a table seperated
> by a comma?
> I want to do this because I am creating some triggers on a large number of
> tables, with a large number of fields in each, and rather than type out my
> list of fields I wanted to generate a list so I could just copy/past into
my
> triggers.
> Thanks
>|||Or a SELECT TOP 0 * would do ;-)
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:ugGZPWQWEHA.3988@.tk2msftngp13.phx.gbl...
Hi,
Simple way is:
In query Analyzer ,
Go to Tools menu -- Options -- Result Tab -- Results Output format --
Select " Comma Delimited (CSV)".
Now excute the below command in text output mode (Query menu -- click --
Results in Text):-
set rowcount 1
select * from table_name
This will give the output in comma delimited. The first row is column names.
:)
Thanks
Hari
MCDBA
"Keith" <@..> wrote in message news:uuNjPFQWEHA.3664@.TK2MSFTNGP12.phx.gbl...
> Is it possible to generate a list of all column names in a table seperated
> by a comma?
> I want to do this because I am creating some triggers on a large number of
> tables, with a large number of fields in each, and rather than type out my
> list of fields I wanted to generate a list so I could just copy/past into
my
> triggers.
> Thanks
>|||Using the object browser window in SQL Query Analyzer may help you. Try it:
- Start Query Analyzer, connect to your database server.
- Click from the Menus: Tools | Object Browser | Show/Hide (or just hit F8).
This brings up the Object Browser pane.
- Navigate database object tree in Object Browser pane to table of interest.
- Right-click on that table and pick Script Object to Clipboard as Select |
Insert | Update | Delete as appropriate.
- Click in your query window and hit Control/V to paste the SQL statement.
You get a complete SQL statement with placeholders for things you will need
to supply.
A friend pointed this out to me a while back. Looks like there may be other
tricks available via the templates tab but I've not explored the
possibilities.
"Keith" <@..> wrote in message news:uuNjPFQWEHA.3664@.TK2MSFTNGP12.phx.gbl...
> Is it possible to generate a list of all column names in a table seperated
> by a comma?
> I want to do this because I am creating some triggers on a large number of
> tables, with a large number of fields in each, and rather than type out my
> list of fields I wanted to generate a list so I could just copy/past into
my
> triggers.
> Thanks
>

Generate an XML file from a stored procedure?!

I need to develop some crystal reports on some .NET ado datasets. This is easy to do if I actually had data to work with. It is much easer creating reports with you have data.
Therefore, I would like to run the stored procedure that the .NET ado dataset is built on and generate an XML file. I could run the project and try to figure out where the developers are making the call to the stored procedure and insert a line to writet
oxmlfile. I would rather not have to mess with their code.
Is there a way working with SQL Server (either query analyzer or enterprise manager, dts, or whatever) that I can generate an xml file.
I see that I can run a stored procedure and get an xml style return in query analyzer, but I don't know how to save that as an actual file.
================================================== ====================
*** I need to generate an xml file. How do I run a stored procedure and have the results as an xml file?
================================================== ====================
Thanks for the help.
Tony
One way I can think of is
in QA, Set the "Results to file" option.
Roji. P. Thomas
SQL Server Programmer
"CrystalDBA" <tturner6@.hotmail.com> wrote in message
news:02CBE611-33FB-4ADD-A70D-6618C7448101@.microsoft.com...
> I need to develop some crystal reports on some .NET ado datasets. This
is easy to do if I actually had data to work with. It is much easer creating
reports with you have data.
> Therefore, I would like to run the stored procedure that the .NET ado
dataset is built on and generate an XML file. I could run the project and
try to figure out where the developers are making the call to the stored
procedure and insert a line to writetoxmlfile. I would rather not have to
mess with their code.
> Is there a way working with SQL Server (either query analyzer or
enterprise manager, dts, or whatever) that I can generate an xml file.
> I see that I can run a stored procedure and get an xml style return in
query analyzer, but I don't know how to save that as an actual file.
> ================================================== ====================
> *** I need to generate an xml file. How do I run a stored procedure and
have the results as an xml file?
> ================================================== ====================
> Thanks for the help.
> Tony
>
|||Assuming the stored procedure actually returns XML (i.e. it uses a FOR XML
clause), you could easily write a VB Script to do this as shown in the
following example. For this to work you need to install SQLXML 3.0 on the
client from which you'll run the script:
Const DBGUID_SQL = "{C8B522D7-5CF3-11CE-ADE5-00AA0044773D}"
Const adExecuteStream = 1024
Const adCmdStoredProc = 4
Dim conn
Set conn = CreateObject("ADODB.Connection")
conn.Provider = "SQLXMLOLEDB.3.0"
conn.ConnectionString = "DATA PROVIDER=SQLOLEDB;" & _
"SERVER=MyServer;DATABASE=MyDB;INTEGRATED SECURITY=sspi;"
conn.Open
Dim cmd
Set cmd = CreateObject("ADODB.Command")
Set cmd.ActiveConnection = conn
'Set the dialect
cmd.Dialect = DBGUID_SQL
'Specify the root tag
cmd.Properties("xml root") = "QueryResults"
'Create DOMDocument object for results.
Dim xmlDoc
Set xmlDoc= CreateObject("MSXML2.DOMDocument")
'Assign the output stream.
cmd.Properties("Output Stream") = xmlDoc
'Specify the command
cmd.CommandText = "MyStoredProc"
cmd.CommandType = adCmdStoredProc
'Execute the command returning a stream
cmd.Execute, , adExecuteStream
' Save the file
xmlDoc.Save("C:\Results.xml")
MsgBox("File saved")
Hope that helps,
Graeme
--
Graeme Malcolm
Principal Technologist
Content Master Ltd.
www.contentmaster.com
www.microsoft.com/mspress/books/6137.asp
"CrystalDBA" <tturner6@.hotmail.com> wrote in message
news:02CBE611-33FB-4ADD-A70D-6618C7448101@.microsoft.com...
> I need to develop some crystal reports on some .NET ado datasets. This
is easy to do if I actually had data to work with. It is much easer creating
reports with you have data.
> Therefore, I would like to run the stored procedure that the .NET ado
dataset is built on and generate an XML file. I could run the project and
try to figure out where the developers are making the call to the stored
procedure and insert a line to writetoxmlfile. I would rather not have to
mess with their code.
> Is there a way working with SQL Server (either query analyzer or
enterprise manager, dts, or whatever) that I can generate an xml file.
> I see that I can run a stored procedure and get an xml style return in
query analyzer, but I don't know how to save that as an actual file.
> ================================================== ====================
> *** I need to generate an xml file. How do I run a stored procedure and
have the results as an xml file?
> ================================================== ====================
> Thanks for the help.
> Tony
>
|||Thanks for your help, but I don't have SQLXML. I don't even know what that is.
I tried outputting the file by setting QA to output results to a file. It works, but the file is not a real xml file. I am using FOR XML and get the results in the text in the results pane that looks like XML. I need an actual xml file.
Tony
|||SQLXML is the name of the XML mid-tier component that comes with SQL Server
2000 as part of OLEDB. See Books Online for more information. Later versions
of the mid-tier component have been made available for free as web downloads
(see http://msdn.microsoft.com/sqlxml).
The easiest way to programmatically get your FOR XML result into a file is
to write an ADO script that retrieves the FOR XML result as a result stream
(not a recordset!). You can associate the file's stream as the result
stream, so you should get the data directly into the file.
Best regards
Michael
"CrystalDBA" <tturner6@.hotmail.com> wrote in message
news:7D7C7E80-D8EE-40F1-9064-F6CCFBE2A29B@.microsoft.com...
> Thanks for your help, but I don't have SQLXML. I don't even know what
> that is.
> I tried outputting the file by setting QA to output results to a file. It
> works, but the file is not a real xml file. I am using FOR XML and get
> the results in the text in the results pane that looks like XML. I need
> an actual xml file.
>
> Tony
|||Download SQLXML 3.0 from
http://www.microsoft.com/downloads/d...isplaylang=en,
install it on your client, and modify the script in my previous post to
access your server / database and execute your FOR XML stored procedure.
Hopefully that should do it.
Cheers,
Graeme
--
Graeme Malcolm
Principal Technologist
Content Master Ltd.
www.contentmaster.com
www.microsoft.com/mspress/books/6137.asp
"CrystalDBA" <tturner6@.hotmail.com> wrote in message
news:7D7C7E80-D8EE-40F1-9064-F6CCFBE2A29B@.microsoft.com...
> Thanks for your help, but I don't have SQLXML. I don't even know what
that is.
> I tried outputting the file by setting QA to output results to a file. It
works, but the file is not a real xml file. I am using FOR XML and get the
results in the text in the results pane that looks like XML. I need an
actual xml file.
>
> Tony

Monday, March 26, 2012

Generic report and dataset

Generic report and dataset

generating XML from SQL

Wednesday, March 21, 2012

Generating scripts and creating backups

generating script with SMO

Generating Reports Error, dependent on local groups?

Generating Report Error, Local Administrator Group

Monday, March 12, 2012

Generate XML file from stored procedure

Friday, March 9, 2012

Generate SQL scripts creating a separate file per object

Friday, February 24, 2012

Generate Column List

Generate Column List

Generate Column List

Generate an XML file from a stored procedure?!

Grid display

Blog Archive

About Me