DataSet to SQL script?

Hi,Does anyone have a way to generate a SQL 2000 database generation script from a DataSet? I'm looking to load an XML file into a DataSet and use it to create the db design.Matt.

You can load elements/attributes into fields of database tables, which contains information used to create a database. Then you can query out the information, and use it in dynamic sql statement. For example if we want to create such a database using DataFileName, LogFileName, DatabaseName, Size, FileGrowth frommyTable:

DECLARE @.DFN sysname,@.LFN sysname, @.DBN sysname
DECLARE @.Size varchar(4),@.FG varchar(4)
SELECT @.DFN=DataFileName, @.LFN=LogFileName, @.DBN=DatabaseName,
@.Size=Size, @.FG=FileGrowth
FROMmyTable

--select @.DFN='c:\saledat.mdf',@.LFN='c:\salelog.ldf',@.DBN='Sales',
--@.Size='10MB',@.FG='5MB'
EXEC('CREATE DATABASE'+@.DBN+'
ON
( NAME = Sales_dat,
FILENAME ='''+@.DFN+''',
SIZE ='+@.Size+',
MAXSIZE = 50,
FILEGROWTH ='+@.FG+' )
LOG ON
( NAME = Sales_log,
FILENAME ='''+@.LFN+''',
SIZE ='+@.Size+',
MAXSIZE = 25,
FILEGROWTH ='+@.FG+' )')

Note: the aboving create database statement recieves @.Size and @.FG as varchar datatype.

|||Thanks, but I was under the impression that the BulkInsert3 method (SQLXMLBULKLOADLib.SQLXMLBulkLoad3Class.Execute() found in xblkld3.dll) would take an xml schema and xml file and actually create the tables, within a specified database, if they were not already present.Do you know if this is actually possible?Matt.|||Actually, to clarify, I would like an automated version of the article mentioned previously (http://msdn.microsoft.com/msdnmag/issues/03/05/MetaDataServices/) in order to generate a db (well, the tables) script.This comes from seeing Visual Studio open an XML file, click on XML->Create Schema and View Designer. This shows what I would derive a db table design from.However, currently I am loading the XML into a DataSet and generating the xml schema from that. I may have to simply generate the database tables from looking at the DataSet and then use the bulk insert library.Obviously, my intention here is to do as much of the work with automation as possible (as any good/lazy coder would) but it just looks like some crowbarring is needed.Unless anyone knows how to generate a databases tables from the XML/XML schema or DataSet directly?Matt.

dataset object dispose after first use

I have an Execute SQL Task that returns a dataset to variable DfltValData. A dataflow follows that with a script component that access that dataset (read only variable) (see code below) and everything is fine. Now, after that, there's another dataflow with a script component, with the same code as below, trying to access DfltValData. Here is where the problem is, the DfltValData object does not contains any row. Whats happening and how to solve this?

Thanks!

Dim olead As New Data.OleDb.OleDbDataAdapter
Dim dt As New Data.DataTable
Dim row As System.Data.DataRow
olead.Fill(dt, Me.Variables.DfltValData)
For Each row In dt.Rows
.

.

.// read value from row

.HAHAHAHA!! Not to laugh at your problem, but search a bit and you'll find a VERY recent discussion on this very topic.|||http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1406350&SiteID=1|||

Use a MULTICAST if you need to work with same rows again.

There is a code work-around (shown in the forum post Phil referenced), but it's ugly.

|||I don't understand how multicast would work if we have multiple dataflow.|||

Sorry, I didn't read the original post closely enough.

If the basic problem is that you want to reuse the data in another data flow, you could use a multicast in your first data flow, and output the data to a raw file. Then use a raw file source to pull it into your second data flow.

|||

jwelch wrote:

Sorry, I didn't read the original post closely enough.

If the basic problem is that you want to reuse the data in another data flow, you could use a multicast in your first data flow, and output the data to a raw file. Then use a raw file source to pull it into your second data flow.

Exactly. Or simply use a multicast to populate 2 variables in the first place with exactly the same data.

-Jamie

DataRecordTypeToBufferType not declared

When I try to use the DataRecordTypeToBufferType and ConvertBufferDataTypeToFitManaged helper functions in my SIS script it tells me they are not defined. According to the doc they are in the Microsoft.SqlServer.Dts.Pipeline namespace, which I have included and the reference is there. It has been a few years since I have worked with visual studio so I am a little rusty, can anyone tell me what I might be missing?

Thanks!
Harry

Harry,

Do you have both the reference and the using or Imports statement?

Are you trying to invoke a static/Shared method from an instance variable? C# should give you a good error for this, and I think VB allows it.

-Doug
|||Harry,

These are protected methods in the namespace. I don't know why they are documented since they can not be used externally. Can you file a doc bug on this. It won't make RTM but hopefully we can get it into a refresh.

Thanks,
Matt

DataRecordTypeToBufferType not declared

When I try to use the DataRecordTypeToBufferType and ConvertBufferDataTypeToFitManaged helper functions in my SIS script it tells me they are not defined. According to the doc they are in the Microsoft.SqlServer.Dts.Pipeline namespace, which I have included and the reference is there. It has been a few years since I have worked with visual studio so I am a little rusty, can anyone tell me what I might be missing?

Thanks!
Harry

Harry,

Do you have both the reference and the using or Imports statement?

Are you trying to invoke a static/Shared method from an instance variable? C# should give you a good error for this, and I think VB allows it.

-Doug
|||Harry,

These are protected methods in the namespace. I don't know why they are documented since they can not be used externally. Can you file a doc bug on this. It won't make RTM but hopefully we can get it into a refresh.

Thanks,
Matt

'DATABASEPROPERTYEX' is not a recognized function name

Hello,
i create one script that give me the output with the
Databases that have the full recovery model. In the middle
of the script i needed to use this
function 'DATABASEPROPERTYEX' but when i run the script on
Servers that haves SQL 7.0, sql generate the following
error.
Server: Msg 195, Level 15, State 10, Line 13
'DATABASEPROPERTYEX' is not a recognized function name.
I know that this function doesn't exists on SQL 7.0,
instead of this one SQL 7.0 has the 'DATABASEPROPERTY'
function but i cant get what i want.
Help... please.
Best Regards
You could make use of sp_dboption in SQL Server 7.0. See SQL Server Books
Online for more information.
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:55d901c42cfd$f15257b0$a101280a@.phx.gbl...
Hello,
i create one script that give me the output with the
Databases that have the full recovery model. In the middle
of the script i needed to use this
function 'DATABASEPROPERTYEX' but when i run the script on
Servers that haves SQL 7.0, sql generate the following
error.
Server: Msg 195, Level 15, State 10, Line 13
'DATABASEPROPERTYEX' is not a recognized function name.
I know that this function doesn't exists on SQL 7.0,
instead of this one SQL 7.0 has the 'DATABASEPROPERTY'
function but i cant get what i want.
Help... please.
Best Regards

'DATABASEPROPERTYEX' is not a recognized function name

Hello,
i create one script that give me the output with the
Databases that have the full recovery model. In the middle
of the script i needed to use this
function 'DATABASEPROPERTYEX' but when i run the script on
Servers that haves SQL 7.0, sql generate the following
error.
Server: Msg 195, Level 15, State 10, Line 13
'DATABASEPROPERTYEX' is not a recognized function name.
I know that this function doesn't exists on SQL 7.0,
instead of this one SQL 7.0 has the 'DATABASEPROPERTY'
function but i cant get what i want.
Help... please.
Best RegardsYou could make use of sp_dboption in SQL Server 7.0. See SQL Server Books
Online for more information.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:55d901c42cfd$f15257b0$a101280a@.phx.gbl...
Hello,
i create one script that give me the output with the
Databases that have the full recovery model. In the middle
of the script i needed to use this
function 'DATABASEPROPERTYEX' but when i run the script on
Servers that haves SQL 7.0, sql generate the following
error.
Server: Msg 195, Level 15, State 10, Line 13
'DATABASEPROPERTYEX' is not a recognized function name.
I know that this function doesn't exists on SQL 7.0,
instead of this one SQL 7.0 has the 'DATABASEPROPERTY'
function but i cant get what i want.
Help... please.
Best Regards

'DATABASEPROPERTYEX' is not a recognized function name

Hello,
i create one script that give me the output with the
Databases that have the full recovery model. In the middle
of the script i needed to use this
function 'DATABASEPROPERTYEX' but when i run the script on
Servers that haves SQL 7.0, sql generate the following
error.
Server: Msg 195, Level 15, State 10, Line 13
'DATABASEPROPERTYEX' is not a recognized function name.
I know that this function doesn't exists on SQL 7.0,
instead of this one SQL 7.0 has the 'DATABASEPROPERTY'
function but i cant get what i want.
Help... please.
Best RegardsYou could make use of sp_dboption in SQL Server 7.0. See SQL Server Books
Online for more information.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:55d901c42cfd$f15257b0$a101280a@.phx.gbl...
Hello,
i create one script that give me the output with the
Databases that have the full recovery model. In the middle
of the script i needed to use this
function 'DATABASEPROPERTYEX' but when i run the script on
Servers that haves SQL 7.0, sql generate the following
error.
Server: Msg 195, Level 15, State 10, Line 13
'DATABASEPROPERTYEX' is not a recognized function name.
I know that this function doesn't exists on SQL 7.0,
instead of this one SQL 7.0 has the 'DATABASEPROPERTY'
function but i cant get what i want.
Help... please.
Best Regards

Database with dash job run as dbo

I have written a script that needs to run as dbo and is triggered by a 9002 alert and it runs fine on about 500 databases, however I can not get it to run on databases that have a dash in their name (Nor if they have a space).

I am running 8.00.534 SQL Server 2000 SP2 November 30th, 2001 Standard Edition.

To reproduce the problem:

#1 Create a database named "test-dash"
#2 Create a job "testjob" with owner sa
#3 in enterpise manager under job steps click "new"
#4 General tab:
Step Name: testing
Type: TSQL
Database: test-dash
Comand: select 'hello world'
#5 Advance tab:
Run as user: dbo
#6 click ok and click ok

It returns:
Error 170: Line 3: Incorrect syntax near '-', the job was not saved,

Apparently it is dying on line 148 of msdb.dbo.sp_verify_jobstep
EXECUTE(N'DECLARE @.ret INT
SELECT @.ret = COUNT(*)
FROM ' + @.database_name_temp + N'.dbo.sysusers
WHERE (name = N''' + @.database_user_name_temp + N''')
HAVING (COUNT(*) > 0)')

Which should generate:
SELECT COUNT(*)
FROM [test-dash].dbo.sysusers
WHERE (name = N'dbo')
HAVING (COUNT(*) > 0)

But is missing the brackets

Has anyone seen a microsoft patch to fix this?I was able to replicate your problem. I haven't seen a patch for this but I am curious why you set the Run As = dbo when the scripts is running under the sa login?|||Like Paul, I did the test and came down with the same error. However as Paul stated, if the job is owned by sa why set RunAs to dbo. When you remove that option you can save the job.|||Thank you for the replies.

The directions were a "simplified" example to try to find the problem.

I was writing a script to automate the install of the maintenance jobs on 500+ databases.

I am trying to move away from "Mixed Mode" authentication, so actually I was planning to have the jobs owned by the same user that SQL Server is running as which may not have the needed privileges. Can a job be owned by and run as SA when you are using NT Authentication?

The user that a certain application is running as owns many of the databases. I was hoping that running as dbo would simplify the install.

I know I can look at the owner of each database and set that as the owner of the job, but the job might break when the owner of a database is changed.

The script does currently work if I have the jobs all owned and run by my personal account, but I did not think that was a good solution. Occasionally servers get set to deny access to all domain administrators, so I was hoping the scripts would still continue to run when that happens.

Bob S.|||You are headed in the right direction, and yes a job can be owned by and run as SA.

If the NT account used by your SQL Server Agent is alias to SA your life will be much easier. I personnaly have worked for only one company that had a problem with this and we resolved it by switching to a server side authenticated id.|||I got the error on line no 248 (rather than 148) when doing the same in T-SQL, see below.
WHy does it say that "dbo" doesn't exist, when I know it does?

use msdb

EXEC sp_add_job @.job_name = 'testingtsql',
@.owner_login_name = 'sa'

EXEC sp_add_jobstep @.job_name = 'testingtsql',
@.step_name = 'selecthello',
@.subsystem = 'TSQL',
@.command = 'select hello-world',
@.database_name = 'test-dash',
@.database_user_name = 'dbo'

Server: Msg 14261, Level 16, State 1, Procedure sp_verify_job, Line 67
The specified @.name ('testingtsql') already exists.
Server: Msg 170, Level 15, State 1, Line 3
Line 3: Incorrect syntax near '-'.
Server: Msg 14262, Level 16, State 1, Procedure sp_verify_jobstep, Line 248
The specified @.database_user_name ('dbo') does not exist.|||Okay, I bet you USD100.00 that you DON'T have a user named 'dbo', but do have a role called db_owner commenly refered to as dbo.

In your job deffinition you do not need to specify @.database_user_name when the job owner is sa, by default the job will run as dbo.|||You're probably right.

Anyway, this bug about the dash-name occurs regardless of user, I guess?

Database Variable

Hi,

I have a sql script that references a database explicitly, but the name of that database changes. Rather then have to find anywhere I reference that database and change it, i would like to just set it in one spot.

For example.

SELECT * FROM [V1_Database].[a_table]

I then decide to upgrade from V1 to V2.

Instead of having to find the above code in all of my stored procs and manually change it, I would like to do the following.

SET dbName = 'V2_Database' (Database wide variable)

SELECT * FROM [dbName].[a_table]

But I don't want to have to make all of the places that reference a specific database into string executions. ( strSQL = 'SELECT * FROM [' + dbName + ']' )

I hope i havent made this too confusing, but any help is appreciated.

Thanks,

Brian

You could put the connectionstring in web.config file and use it across your application. That way you would only need to change at one place.