scripting data

Hi,

I need to take data from a SQL Serer 2005 database, and load into a
remote 2000 database. I've already been able to script and create the
database objects (MS SQL Server 2005 has a nifty option which allows
you to scripting for SQL Server 2000 compliance). Now i just need to
get the data in.

Is there a tool or utility out there that i can use to generate insert
statements for all the tables in database?

Thanks much for any advice regarding this.On Feb 13, 6:07 am, "farseer" <fars...@.optonline.netwrote:

Quote:

Originally Posted by

Hi,
>
I need to take data from a SQL Serer 2005 database, and load into a
remote 2000 database. I've already been able to script and create the
database objects (MS SQL Server 2005 has a nifty option which allows
you to scripting for SQL Server 2000 compliance). Now i just need to
get the data in.
>
Is there a tool or utility out there that i can use to generate insert
statements for all the tables in database?
>
Thanks much for any advice regarding this.

Hi,

Yes, there is and it's called DB Ghost (www.dbghost.com). It has a
Data and Schema Scripter (which does what you need) and a host of
other tools that form a complete change management process for SQL
Server.and can all be called from the command line for full process
automation :)

Kind regards,

Malcolm|||farseer (farseer@.optonline.net) writes:

Quote:

Originally Posted by

Is there a tool or utility out there that i can use to generate insert
statements for all the tables in database?

SELECT 'bcp yourdb..' + name + ' out ' + name + '.bcp -n -T'
FROM sys.objects
WHERE type = 'U'

Copy result into a BAT file and run.

The to load:

SELECT 'bcp yourtargetdb..' + name + ' in ' + name + '.bcp -n -T ' +
'-S REMOTSER' +
CASE WHEN EXISTS (SELECT *
FROM sys.columns c
WHERE o.object_id = c.object_id
AND c.is_identity = 1)
THEN ' -E '
ELSE ''
END
FROM sys.objects o
WHERE type = 'U'

I assume that you are not using any data types that are not available
in SQL 2005.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspxsql

scripting data

Hi,
I need to take data from a SQL Serer 2005 database, and load into a
remote 2000 database. I've already been able to script and create the
database objects (MS SQL Server 2005 has a nifty option which allows
you to scripting for SQL Server 2000 compliance). Now i just need to
get the data in.
Is there a tool or utility out there that i can use to generate insert
statements for all the tables in database?
Thanks much for any advice regarding this.You could uses SSIS to transfer the data, however if you want to generate
insert scripts then take a look at sp_generate_inserts
http://vyaskn.tripod.com/code.htm#inserts
--
HTH,
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
"farseer" <farseer@.optonline.net> wrote in message
news:1171346776.733940.21960@.a75g2000cwd.googlegroups.com...
> Hi,
> I need to take data from a SQL Serer 2005 database, and load into a
> remote 2000 database. I've already been able to script and create the
> database objects (MS SQL Server 2005 has a nifty option which allows
> you to scripting for SQL Server 2000 compliance). Now i just need to
> get the data in.
> Is there a tool or utility out there that i can use to generate insert
> statements for all the tables in database?
> Thanks much for any advice regarding this.
>|||I've listed some tools here: http://www.karaszi.com/SQLServer/info_generate_script.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"farseer" <farseer@.optonline.net> wrote in message
news:1171346776.733940.21960@.a75g2000cwd.googlegroups.com...
> Hi,
> I need to take data from a SQL Serer 2005 database, and load into a
> remote 2000 database. I've already been able to script and create the
> database objects (MS SQL Server 2005 has a nifty option which allows
> you to scripting for SQL Server 2000 compliance). Now i just need to
> get the data in.
> Is there a tool or utility out there that i can use to generate insert
> statements for all the tables in database?
> Thanks much for any advice regarding this.
>|||Hi
You are able to create a linked server between both and share the data
"farseer" <farseer@.optonline.net> wrote in message
news:1171346776.733940.21960@.a75g2000cwd.googlegroups.com...
> Hi,
> I need to take data from a SQL Serer 2005 database, and load into a
> remote 2000 database. I've already been able to script and create the
> database objects (MS SQL Server 2005 has a nifty option which allows
> you to scripting for SQL Server 2000 compliance). Now i just need to
> get the data in.
> Is there a tool or utility out there that i can use to generate insert
> statements for all the tables in database?
> Thanks much for any advice regarding this.
>|||Try www.sqlscripter.com to script your data.
Its free.
"farseer" wrote:
> Hi,
> I need to take data from a SQL Serer 2005 database, and load into a
> remote 2000 database. I've already been able to script and create the
> database objects (MS SQL Server 2005 has a nifty option which allows
> you to scripting for SQL Server 2000 compliance). Now i just need to
> get the data in.
> Is there a tool or utility out there that i can use to generate insert
> statements for all the tables in database?
> Thanks much for any advice regarding this.
>

scripting data

Hi,
I need to take data from a SQL Serer 2005 database, and load into a
remote 2000 database. I've already been able to script and create the
database objects (MS SQL Server 2005 has a nifty option which allows
you to scripting for SQL Server 2000 compliance). Now i just need to
get the data in.
Is there a tool or utility out there that i can use to generate insert
statements for all the tables in database?
Thanks much for any advice regarding this.
You could uses SSIS to transfer the data, however if you want to generate
insert scripts then take a look at sp_generate_inserts
http://vyaskn.tripod.com/code.htm#inserts
HTH,
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
"farseer" <farseer@.optonline.net> wrote in message
news:1171346776.733940.21960@.a75g2000cwd.googlegro ups.com...
> Hi,
> I need to take data from a SQL Serer 2005 database, and load into a
> remote 2000 database. I've already been able to script and create the
> database objects (MS SQL Server 2005 has a nifty option which allows
> you to scripting for SQL Server 2000 compliance). Now i just need to
> get the data in.
> Is there a tool or utility out there that i can use to generate insert
> statements for all the tables in database?
> Thanks much for any advice regarding this.
>
|||Hi
You are able to create a linked server between both and share the data
"farseer" <farseer@.optonline.net> wrote in message
news:1171346776.733940.21960@.a75g2000cwd.googlegro ups.com...
> Hi,
> I need to take data from a SQL Serer 2005 database, and load into a
> remote 2000 database. I've already been able to script and create the
> database objects (MS SQL Server 2005 has a nifty option which allows
> you to scripting for SQL Server 2000 compliance). Now i just need to
> get the data in.
> Is there a tool or utility out there that i can use to generate insert
> statements for all the tables in database?
> Thanks much for any advice regarding this.
>
|||Try www.sqlscripter.com to script your data.
Its free.
"farseer" wrote:

> Hi,
> I need to take data from a SQL Serer 2005 database, and load into a
> remote 2000 database. I've already been able to script and create the
> database objects (MS SQL Server 2005 has a nifty option which allows
> you to scripting for SQL Server 2000 compliance). Now i just need to
> get the data in.
> Is there a tool or utility out there that i can use to generate insert
> statements for all the tables in database?
> Thanks much for any advice regarding this.
>

Scripting creation of server alias

Does anyone know if there's a way to script creation of a server alias,
as you can do using the Client Network Utility?
allancady@.yahoo.com wrote:
> Does anyone know if there's a way to script creation of a server
> alias, as you can do using the Client Network Utility?
Do you mean from a T-SQL script? The utility just writes to the
registration database. Sniff the regdb while you perform adding a new
alias and see where it goes. Then you can use any programming tool or
just a .reg file to add the new data to everyones PC. From a domain this
is easy.
You can sniff the regdb using free tools from www.sysinternals.com
David Gugick
Imceda Software
www.imceda.com

Scripting creation of server alias

Does anyone know if there's a way to script creation of a server alias,
as you can do using the Client Network Utility?allancady@.yahoo.com wrote:
> Does anyone know if there's a way to script creation of a server
> alias, as you can do using the Client Network Utility?
Do you mean from a T-SQL script? The utility just writes to the
registration database. Sniff the regdb while you perform adding a new
alias and see where it goes. Then you can use any programming tool or
just a .reg file to add the new data to everyones PC. From a domain this
is easy.
You can sniff the regdb using free tools from www.sysinternals.com
David Gugick
Imceda Software
www.imceda.com

Scripting Compound Primary Keys

Hi all

I have a requirment to dynamically script a whole database schema. Scripting tables, constraints defaults etc. This all works well until I come up against compound primary key constraints then using Information_schema or system tables I only seem to get to the first column making up the constraint. Anyone have a snippet of script which can list the primary key and all its columns for a given table?

Currently using SQL 2000 and shortly moving to SQL 2005.

Thanks

The following query may give idea...

CREATEProc TableCreationText

(

@.TableName Nvarchar(2000)

)

as

Create Table #Text

(

Source NVarchar(4000)

)

Insert Into #Text(Source) Values ('Create Table ' + @.TableName + '(');

Insert Into #Text(Source)

Select

'[' + C.Name + '] ' +Ty.name + Case When C.Scale Is NULL Then '(' + Cast(C.Length as Varchar) + ') ' Else '' End +

Case When C.IsNullable =0 And C.Colstat & 1 <> 1 Then ' NULL ' Else ' NOT NULL ' End

+ Case When C.Colstat & 1 = 1 Then ' Identity(' +Cast(ident_seed(T.name) as varchar) + ',' + Cast(ident_incr(T.name) as Varchar) + ') 'Else '' End

+ Isnull(' Constraint ' + ChkCon.Name + ' Check ' + comments.Text ,'')

+ Isnull(' Default ' + defcomments.text ,'') + ','

From

Sysobjects T

Join Syscolumns C on T.id = C.Id

Join systypes Ty On C.xtype = Ty.xType And Ty.Name <> 'SysName'

Left Outer Join sysobjects ChkCon On ChkCon.parent_obj = T.Id

And ChkCon.xtype= 'C' And ChkCon.Info = C.Colorder

Left Outer Join syscomments comments ON Comments.id = ChkCon.id And Comments.colid =1

Left Outer Join sysobjects def On def.parent_obj = T.Id

And def.xtype= 'D' And def.Info = C.Colorder

Left Outer Join syscomments defcomments ON defcomments.id = def.id

Where

T.Type='U'

And T.Name=@.TableName

Order By

T.Name,

C.Colorder

Insert Into #Text(Source)

Select

'Constraint [' + ind.name + '] ' + case when xtype='PK' Then ' Primary Key ' Else ' Unique ' End+ Case when ind.status & 16=16 Then ' clustered ' Else ' nonclustered' End+'(' +dbo.GetAllIndexedColumns(@.TableName, 2)+ '),'

From

sysindexes ind Join sysobjects tbl On tbl.parent_obj = object_id(@.TableName)

and ind.name = object_name(tbl.id)

and xtype in ('PK', 'UQ')

Insert Into #Text(Source)

select

'Constraint [' + tbl.name + '] FOREIGN KEY ([' + col_name(fk.fkeyid, fk.fkey) + ']) REFERENCES [' +

object_name(fk.rkeyid) + ']([' + col_name(fk.rkeyid, fk.rkey) + ']),'

from

sysforeignkeys fk Join sysobjects tbl On tbl.parent_obj = object_id(@.TableName)

and fk.constid = tbl.id

and xtype in ('F')

Declare @.Source as Nvarchar(4000);

Select @.Source = Source From #Text;

Update #Text

Set

Source = Substring(Source,1,Len(Source)-1)

Where

Source = @.Source;

Insert Into #Text(Source) values (')');

Select Source From #Text

|||Hi,

Could you also provide script for dbo.GetAllIndexedColumns this function.

Zafar|||

Thanks for that

dbo.GetAllIndexedCollumns is this a hellper stored proc ? The script fails at this point when I run it on my system.

|||

oops.. I missed that.. here it is..

Create Function dbo.GetAllIndexedColumns(@.ObjectName nvarchar(1000), @.indexid int)

Returns NVarchar(4000)

as

Begin

Declare @.IndexedCols as NVarchar(4000);

Declare @.I as Int;

Select @.I = 1;

Select @.IndexedCols = index_col(@.ObjectName, @.indexid, @.I) + '' + Case When indexkey_property(object_id(@.ObjectName),@.indexid, @.i, 'isdescending') = 1 Then ' Desc ' Else '' End

Select @.I = @.I +1;

While index_col(@.ObjectName, @.indexid, @.I) is not null

Begin

Select @.IndexedCols = @.IndexedCols + ',' + index_col(@.ObjectName, @.indexid, @.I) + '' + Case When indexkey_property(object_id(@.ObjectName),@.indexid, @.i, 'isdescending') = 1 Then ' Desc ' Else '' End

Select @.I = @.I +1;

End

return @.IndexedCols;

End

|||

For some reason the script only reports the second of the columns involved in the compound key. I figure that the clues are in there I just need to keep plugging away at it...

Thanks

|||

Yes.. I didn't tested properly in SQL Server 2005..

here the updated query..

Code Snippet

Declare @.tablename as varchar(100)

Set @.tablename = 'YourTablename'

Select

'Constraint [' + ind.name + '] ' + case when xtype='PK' Then ' Primary Key ' Else ' Unique ' End + Case when ind.status & 16=16 Then ' clustered ' Else ' nonclustered' End + '(' + dbo.GetAllIndexedColumns(@.TableName, ind.indid) + '),'

From

sysindexes ind Join sysobjects tbl On tbl.parent_obj = object_id(@.TableName)

and ind.name = object_name(tbl.id)

and xtype in ('PK', 'UQ')

Scripting component.

Hi Guys,

I need to generate a file using script component. I'm taking some data from an oledb source and then i would need to modify the fields, generate new fields and then generate the flat file.

I don't have any problems with this but the file i need to replicate would need to have a head record and a trail record which doesn't need to be duplicated.

Now when i generate the file and put anything in "Public Overrides Sub Input0_ProcessInputRow" it will work but it will generate the header record again and again as well.

Any quick help would be really appreciated.

TA

Gemma

Gemma,

Any reason for using the script component to create the file? Can you just use the script to populate the pipeline and use a flat file destination for the file? If so, I recently described how you would go about creating a flat file with header and trailing records.. http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2168163&SiteID=1

|||

Hi Eric,

I'm just combinging the fields, padding the fields with zero and doing some cleaning of the data as well.

Is there any other way? I've checked your post but I don't know how to add something from the database in the header.

Yes you can enter any expressions but i need the value from the table.

Can you help me do that?

TA
Gemma

|||

Add a boolean flag at the class level and default it to True, Then in the ProcessInput check the flag. If it is True, write the header line and change the flag to false.

There's an example of that approach in the script I posted here - http://agilebi.com/cs/blogs/jwelch/archive/2007/09/14/dynamically-pivoting-rows-to-columns.aspx. The example is not what you were asking about, but the script illustrates the technique.

Scripting changes to the data source view

I need to script out creation of some new cubes and dimension to an SSAS server while leaving the rest of the OLAP database unchanged. Handling the cubes and dimensions themselves seems pretty straight forward, using the scripting tools in SQL Management Studio. However, I'm uncertain about scripting the changes to the data source view (for the fact and dimension tables for the new cubes and dimensions).

In SQL Management Studio, only the entire DSV is scriptable - I don't see a way to script out changes to just some of the tables. I could, in theory, manually edit out the parts of the DSV that aren't related to my new entities, but it's not clear from the XMLA Alter command documentation whether that would work. But my reading, the command would replace the entire Major Object, which is the entire DSV in this case. Am I reading that right?

The only other option I can think of is to programmatically add the new entities to the DSV through explicit code - doable, but a royal pain, and I'd rather avoid it if possible.

Am I understanding things correctly? Is there some other option I'm not aware of?

You are right, the XMLA alter command would replace the entire DSV, so if you only scripted out changed objects that is all you would end up with.

You could programmatically add new entities to the DSV through code, but if you trace the resulting interaction with SSAS you will find that it is still doing an XMLA Alter and sending throught the definition for the entire DSV. But atleast if you programmatically add a table to the DSV, AMO will handle the scripting of the rest of the DSV for you.

|||Thanks for the confirmation, Darren. Fortunately, I came up with a slightly simpler way to do it than manually creating all of the new DSV entities in code. I'm loading up the full cube deployment script, plucking out the new DSV items, and copying them into the existing DSV. In the end, it's not too painful. Works well for new cubes and dimensions as well.
|||Glad you have found a solution. I am just curious as to what you mean by "copying them into the existing DSV" are you doing an XML or text "diff" between your new DSV and the existing one?|||By that I meant using AMO to get the DSV on the existing database instance. Basically I'm getting the existing DSV schema (using AMO), parsing out the new DSV schema from the XML/A deployment script for the modified database, and merging the new elements into the existing DSV.

I haven't gotten as fancy as an automated diff process (yet). Currently I'm just manually keeping track of what the new items are. Hopefully I'll be automate that more soon.
|||

I thought you must have been doing something like this. It does seem like a lot of manual work. I'm not sure if you really need to go to all that effort. If you basically pass SSAS an alter script with the entire DSV from the deployment script it *should* be able to figure things out. (assuming that your development project is just an updated version of the project that was originally deployed to your server)

Would I be correct in assuming that the situation you are facing is that you have made mulitple changes to your solution, but you only want to deploy some of them - hence the need to manually script things out? I have been working with a friend on a project to build an add-in to BIDS (www.codeplex.com/bidshelper) to enhance BIDS and one feature we were thinking about was the option to deploy just a single object from a project. I have a sub set of this working where I can deploy just the Calculation Script from a cube. If you are interested I don't think it would be too hard for us to add some basic "deploy single object" functionality that would handle other object types.

|||>assuming that your development project is just an updated version of
>the project that was originally deployed to your server

And that's the rub - it's not. We are an ISV that includes a set of SSAS cubes in our product. We provide a UI for customers to extend the cubes with custom measures and dimensions. When we ship new versions of our software, we need to be able to deploy our changes (new cubes, dimensions, attributes, etc) without overwriting the customer's customizations.

The BidsHelper project looks pretty cool - I'll check it out. Ultimately, it doesn't sound like something we'd be able to take advantage of to solve our problem - but it may come in handy during development.

|||

Aah, now I see where you are coming from.

I am not really aware of any good way of handling this "out of the box". It sounds like you will either need to write custom AMO code to attach to the client database and modify them. Or you would need to store the customisations that customers apply so that they could be re-applied after an upgrade.

It sounds like a reasonable feature, you could log this as a suggestion on the connect.microsoft.com site under SQL Server if you would like to see it considered for a future version, but in the medium/short term this sounds like something that will need custom code.

|||

Hi Kevin

Need your help desperately

as i read in ur message that you are updating the data source view by AMO

please let me know how to do that

I am working on the SSAS project and we need to add the measure dynamically to the cube( can be SSIS or Amo any thing, it should be automatic)

I am not getting the way to do that

please send me the detail to do that

that will be a big help

thanks

|||

There is a product sample that shows how to build a lite version of the Adventure Works database from scratch using AMO.

http://www.codeplex.com/MSFTASProdSamples/Wiki/View.aspx?title=SS2005%21Readme_AMOAdventureWorks&referringTitle=Home

If you are dynamically adding measures you would need to update the DSV, add the measures to the appropriate measure group and reprocess all your data.

Scripting changes to the data source view

I need to script out creation of some new cubes and dimension to an SSAS server while leaving the rest of the OLAP database unchanged. Handling the cubes and dimensions themselves seems pretty straight forward, using the scripting tools in SQL Management Studio. However, I'm uncertain about scripting the changes to the data source view (for the fact and dimension tables for the new cubes and dimensions).

In SQL Management Studio, only the entire DSV is scriptable - I don't see a way to script out changes to just some of the tables. I could, in theory, manually edit out the parts of the DSV that aren't related to my new entities, but it's not clear from the XMLA Alter command documentation whether that would work. But my reading, the command would replace the entire Major Object, which is the entire DSV in this case. Am I reading that right?

The only other option I can think of is to programmatically add the new entities to the DSV through explicit code - doable, but a royal pain, and I'd rather avoid it if possible.

Am I understanding things correctly? Is there some other option I'm not aware of?

You are right, the XMLA alter command would replace the entire DSV, so if you only scripted out changed objects that is all you would end up with.

You could programmatically add new entities to the DSV through code, but if you trace the resulting interaction with SSAS you will find that it is still doing an XMLA Alter and sending throught the definition for the entire DSV. But atleast if you programmatically add a table to the DSV, AMO will handle the scripting of the rest of the DSV for you.

|||Thanks for the confirmation, Darren. Fortunately, I came up with a slightly simpler way to do it than manually creating all of the new DSV entities in code. I'm loading up the full cube deployment script, plucking out the new DSV items, and copying them into the existing DSV. In the end, it's not too painful. Works well for new cubes and dimensions as well.|||Glad you have found a solution. I am just curious as to what you mean by "copying them into the existing DSV" are you doing an XML or text "diff" between your new DSV and the existing one?|||By that I meant using AMO to get the DSV on the existing database instance. Basically I'm getting the existing DSV schema (using AMO), parsing out the new DSV schema from the XML/A deployment script for the modified database, and merging the new elements into the existing DSV.

I haven't gotten as fancy as an automated diff process (yet). Currently I'm just manually keeping track of what the new items are. Hopefully I'll be automate that more soon.|||

I thought you must have been doing something like this. It does seem like a lot of manual work. I'm not sure if you really need to go to all that effort. If you basically pass SSAS an alter script with the entire DSV from the deployment script it *should* be able to figure things out. (assuming that your development project is just an updated version of the project that was originally deployed to your server)

Would I be correct in assuming that the situation you are facing is that you have made mulitple changes to your solution, but you only want to deploy some of them - hence the need to manually script things out? I have been working with a friend on a project to build an add-in to BIDS (www.codeplex.com/bidshelper) to enhance BIDS and one feature we were thinking about was the option to deploy just a single object from a project. I have a sub set of this working where I can deploy just the Calculation Script from a cube. If you are interested I don't think it would be too hard for us to add some basic "deploy single object" functionality that would handle other object types.

|||>assuming that your development project is just an updated version of
>the project that was originally deployed to your server

And that's the rub - it's not. We are an ISV that includes a set of SSAS cubes in our product. We provide a UI for customers to extend the cubes with custom measures and dimensions. When we ship new versions of our software, we need to be able to deploy our changes (new cubes, dimensions, attributes, etc) without overwriting the customer's customizations.

The BidsHelper project looks pretty cool - I'll check it out. Ultimately, it doesn't sound like something we'd be able to take advantage of to solve our problem - but it may come in handy during development.

|||

Aah, now I see where you are coming from.

I am not really aware of any good way of handling this "out of the box". It sounds like you will either need to write custom AMO code to attach to the client database and modify them. Or you would need to store the customisations that customers apply so that they could be re-applied after an upgrade.

It sounds like a reasonable feature, you could log this as a suggestion on the connect.microsoft.com site under SQL Server if you would like to see it considered for a future version, but in the medium/short term this sounds like something that will need custom code.

|||

Hi Kevin

Need your help desperately

as i read in ur message that you are updating the data source view by AMO

please let me know how to do that

I am working on the SSAS project and we need to add the measure dynamically to the cube( can be SSIS or Amo any thing, it should be automatic)

I am not getting the way to do that

please send me the detail to do that

that will be a big help

thanks

|||

There is a product sample that shows how to build a lite version of the Adventure Works database from scratch using AMO.

http://www.codeplex.com/MSFTASProdSamples/Wiki/View.aspx?title=SS2005%21Readme_AMOAdventureWorks&referringTitle=Home

If you are dynamically adding measures you would need to update the DSV, add the measures to the appropriate measure group and reprocess all your data.

Scripting changes to the data source view

I need to script out creation of some new cubes and dimension to an SSAS server while leaving the rest of the OLAP database unchanged. Handling the cubes and dimensions themselves seems pretty straight forward, using the scripting tools in SQL Management Studio. However, I'm uncertain about scripting the changes to the data source view (for the fact and dimension tables for the new cubes and dimensions).

In SQL Management Studio, only the entire DSV is scriptable - I don't see a way to script out changes to just some of the tables. I could, in theory, manually edit out the parts of the DSV that aren't related to my new entities, but it's not clear from the XMLA Alter command documentation whether that would work. But my reading, the command would replace the entire Major Object, which is the entire DSV in this case. Am I reading that right?

The only other option I can think of is to programmatically add the new entities to the DSV through explicit code - doable, but a royal pain, and I'd rather avoid it if possible.

Am I understanding things correctly? Is there some other option I'm not aware of?

You are right, the XMLA alter command would replace the entire DSV, so if you only scripted out changed objects that is all you would end up with.

You could programmatically add new entities to the DSV through code, but if you trace the resulting interaction with SSAS you will find that it is still doing an XMLA Alter and sending throught the definition for the entire DSV. But atleast if you programmatically add a table to the DSV, AMO will handle the scripting of the rest of the DSV for you.

|||Thanks for the confirmation, Darren. Fortunately, I came up with a slightly simpler way to do it than manually creating all of the new DSV entities in code. I'm loading up the full cube deployment script, plucking out the new DSV items, and copying them into the existing DSV. In the end, it's not too painful. Works well for new cubes and dimensions as well.
|||Glad you have found a solution. I am just curious as to what you mean by "copying them into the existing DSV" are you doing an XML or text "diff" between your new DSV and the existing one?|||By that I meant using AMO to get the DSV on the existing database instance. Basically I'm getting the existing DSV schema (using AMO), parsing out the new DSV schema from the XML/A deployment script for the modified database, and merging the new elements into the existing DSV.

I haven't gotten as fancy as an automated diff process (yet). Currently I'm just manually keeping track of what the new items are. Hopefully I'll be automate that more soon.
|||

I thought you must have been doing something like this. It does seem like a lot of manual work. I'm not sure if you really need to go to all that effort. If you basically pass SSAS an alter script with the entire DSV from the deployment script it *should* be able to figure things out. (assuming that your development project is just an updated version of the project that was originally deployed to your server)

Would I be correct in assuming that the situation you are facing is that you have made mulitple changes to your solution, but you only want to deploy some of them - hence the need to manually script things out? I have been working with a friend on a project to build an add-in to BIDS (www.codeplex.com/bidshelper) to enhance BIDS and one feature we were thinking about was the option to deploy just a single object from a project. I have a sub set of this working where I can deploy just the Calculation Script from a cube. If you are interested I don't think it would be too hard for us to add some basic "deploy single object" functionality that would handle other object types.

|||>assuming that your development project is just an updated version of
>the project that was originally deployed to your server

And that's the rub - it's not. We are an ISV that includes a set of SSAS cubes in our product. We provide a UI for customers to extend the cubes with custom measures and dimensions. When we ship new versions of our software, we need to be able to deploy our changes (new cubes, dimensions, attributes, etc) without overwriting the customer's customizations.

The BidsHelper project looks pretty cool - I'll check it out. Ultimately, it doesn't sound like something we'd be able to take advantage of to solve our problem - but it may come in handy during development.

|||

Aah, now I see where you are coming from.

I am not really aware of any good way of handling this "out of the box". It sounds like you will either need to write custom AMO code to attach to the client database and modify them. Or you would need to store the customisations that customers apply so that they could be re-applied after an upgrade.

It sounds like a reasonable feature, you could log this as a suggestion on the connect.microsoft.com site under SQL Server if you would like to see it considered for a future version, but in the medium/short term this sounds like something that will need custom code.

|||

Hi Kevin

Need your help desperately

as i read in ur message that you are updating the data source view by AMO

please let me know how to do that

I am working on the SSAS project and we need to add the measure dynamically to the cube( can be SSIS or Amo any thing, it should be automatic)

I am not getting the way to do that

please send me the detail to do that

that will be a big help

thanks

|||

There is a product sample that shows how to build a lite version of the Adventure Works database from scratch using AMO.

http://www.codeplex.com/MSFTASProdSamples/Wiki/View.aspx?title=SS2005%21Readme_AMOAdventureWorks&referringTitle=Home

If you are dynamically adding measures you would need to update the DSV, add the measures to the appropriate measure group and reprocess all your data.

Scripting automation....

Is there any stored procedure for scripting the database?
SQL 2K.
Thanks,
Johnhttp://www.karaszi.com/SQLServer/in...rate_script.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Smith John" <Smith_Jr@.hotmail.com> wrote in message news:OZXYRiK3FHA.548@.TK2MSFTNGP10.phx.g
bl...
> Is there any stored procedure for scripting the database?
> SQL 2K.
> Thanks,
> John
>
>
>|||There is no sp; you can use DMO (Distributed Management Objects) in some
scripting language. You can execite ActiveX script task in a scheduled job.
Here is an example of using DMO in vbscript :
Dim oSS As SQLDMO.SQLServer
Dim oDb As SQLDMO.Database
Dim oT As SQLDMO.Transfer
Dim sS As String
Sub Script()
Set oSS = New SQLDMO.SQLServer
Set oT = New SQLDMO.Transfer
oSS.LoginSecure = True
oSS.Connect
Set oDb = oSS.Databases("pubs")
oT.CopyAllTables = True
oDb.ScriptTransfer oT, SQLDMOXfrFile_SingleFile, "C:\pubs.sql"
End Sub
Dejan Sarka, SQL Server MVP
Mentor
www.SolidQualityLearning.com
"Smith John" <Smith_Jr@.hotmail.com> wrote in message
news:OZXYRiK3FHA.548@.TK2MSFTNGP10.phx.gbl...
> Is there any stored procedure for scripting the database?
> SQL 2K.
> Thanks,
> John
>
>
>|||I was searching for this article of yours a minute ago, but somehow I
couldn't find it...
Dejan Sarka
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uhNyQuK3FHA.472@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> http://www.karaszi.com/SQLServer/in...rate_script.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Smith John" <Smith_Jr@.hotmail.com> wrote in message
> news:OZXYRiK3FHA.548@.TK2MSFTNGP10.phx.gbl...

Scripting automation....

Is there any stored procedure for scripting the database?
SQL 2K.
Thanks,
John
http://www.karaszi.com/SQLServer/inf...ate_script.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Smith John" <Smith_Jr@.hotmail.com> wrote in message news:OZXYRiK3FHA.548@.TK2MSFTNGP10.phx.gbl...
> Is there any stored procedure for scripting the database?
> SQL 2K.
> Thanks,
> John
>
>
>
|||There is no sp; you can use DMO (Distributed Management Objects) in some
scripting language. You can execite ActiveX script task in a scheduled job.
Here is an example of using DMO in vbscript:
Dim oSS As SQLDMO.SQLServer
Dim oDb As SQLDMO.Database
Dim oT As SQLDMO.Transfer
Dim sS As String
Sub Script()
Set oSS = New SQLDMO.SQLServer
Set oT = New SQLDMO.Transfer
oSS.LoginSecure = True
oSS.Connect
Set oDb = oSS.Databases("pubs")
oT.CopyAllTables = True
oDb.ScriptTransfer oT, SQLDMOXfrFile_SingleFile, "C:\pubs.sql"
End Sub
Dejan Sarka, SQL Server MVP
Mentor
www.SolidQualityLearning.com
"Smith John" <Smith_Jr@.hotmail.com> wrote in message
news:OZXYRiK3FHA.548@.TK2MSFTNGP10.phx.gbl...
> Is there any stored procedure for scripting the database?
> SQL 2K.
> Thanks,
> John
>
>
>
|||I was searching for this article of yours a minute ago, but somehow I
couldn't find it...
Dejan Sarka
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uhNyQuK3FHA.472@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> http://www.karaszi.com/SQLServer/inf...ate_script.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Smith John" <Smith_Jr@.hotmail.com> wrote in message
> news:OZXYRiK3FHA.548@.TK2MSFTNGP10.phx.gbl...

Scripting automation....

Is there any stored procedure for scripting the database?
SQL 2K.
Thanks,
Johnhttp://www.karaszi.com/SQLServer/info_generate_script.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Smith John" <Smith_Jr@.hotmail.com> wrote in message news:OZXYRiK3FHA.548@.TK2MSFTNGP10.phx.gbl...
> Is there any stored procedure for scripting the database?
> SQL 2K.
> Thanks,
> John
>
>
>|||There is no sp; you can use DMO (Distributed Management Objects) in some
scripting language. You can execite ActiveX script task in a scheduled job.
Here is an example of using DMO in VBScript:
Dim oSS As SQLDMO.SQLServer
Dim oDb As SQLDMO.Database
Dim oT As SQLDMO.Transfer
Dim sS As String
Sub Script()
Set oSS = New SQLDMO.SQLServer
Set oT = New SQLDMO.Transfer
oSS.LoginSecure = True
oSS.Connect
Set oDb = oSS.Databases("pubs")
oT.CopyAllTables = True
oDb.ScriptTransfer oT, SQLDMOXfrFile_SingleFile, "C:\pubs.sql"
End Sub
--
Dejan Sarka, SQL Server MVP
Mentor
www.SolidQualityLearning.com
"Smith John" <Smith_Jr@.hotmail.com> wrote in message
news:OZXYRiK3FHA.548@.TK2MSFTNGP10.phx.gbl...
> Is there any stored procedure for scripting the database?
> SQL 2K.
> Thanks,
> John
>
>
>|||I was searching for this article of yours a minute ago, but somehow I
couldn't find it...
--
Dejan Sarka
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uhNyQuK3FHA.472@.TK2MSFTNGP15.phx.gbl...
> http://www.karaszi.com/SQLServer/info_generate_script.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Smith John" <Smith_Jr@.hotmail.com> wrote in message
> news:OZXYRiK3FHA.548@.TK2MSFTNGP10.phx.gbl...
>> Is there any stored procedure for scripting the database?
>> SQL 2K.
>> Thanks,
>> John
>>
>>

Scripting an index to a table

What is the correct syntax to add an index to a table?
Example: tableA with fieldB- create an index on fieldBCheck out CREATE INDEX (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_create_64l4.asp).

-PatP|||Originally posted by Pat Phelan
Check out CREATE INDEX (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_create_64l4.asp).

-PatP

Thank you! I found it immediatley after I submitted the post|||Does anyone know the correct procedure to setup a pull relationship from MS Access to a SQL database and vice versa?|||Refer to your post http://www.dbforums.com/t994687.html

scripting an entire database along with the data

Hi,
I would like to know how to create a script for an exsisting database such
that the script also contains statements to recreate all the data in the
database aswell.
regards,
Ahmed.Ahmed Shafi wrote:
> Hi,
> I would like to know how to create a script for an exsisting database
> such that the script also contains statements to recreate all the
> data in the database aswell.
There was a post about a free tool http://www.sqlscripter.com in
sqlserver.tools recently.
You might also want to see ApexSQL Script for this
http://www.apexsql.com/index_ai.htm
There is also really a good one in Vyas's site,
http://vyaskn.tripod.com/code/generate_inserts.txt
sincerely,
--
Sebastian K. Zaklada
Skilled Software
http://www.skilledsoftware.com
This posting is provided "AS IS" with no warranties, and confers no rights.|||You can create insert statements via any of the following tools
Largo SQL Tools -- http://www.largosqltools.com/
ObjectScripter -- http://www.rac4sql.net/
QALite -- http://www.rac4sql.net/
Lockwood Tech -- http://www.lockwoodtech.com/
You can create the database script via some of these tools. You can =
also use Enterprise Manager to script the DDL for you.
With that said, what is your goal? If you want to "save" or "move" your =
database, an easier method might be BACKUP and RESTORE. You can execute =
a Transact-SQL backup command to the database server. This command will =
backup your database (tables, data, stored procedures...) to a file =
that you can backup to tape or burn to CD. You can also use this file =
to recreate your entire database.
--=20
Keith
"Ahmed Shafi" <aflatoon99 At hotmail DOT com> wrote in message =
news:OxMW4t5BEHA.1236@.TK2MSFTNGP11.phx.gbl...
> Hi,
>=20
> I would like to know how to create a script for an exsisting database =
such
> that the script also contains statements to recreate all the data in =
the
> database aswell.
>=20
> regards,
> Ahmed.
>=20
>|||Hi Keith & Ahmed,
take a look at this script we give away
http://www.innovartis.co.uk/downloa..._statements.zip
you may want to take a look at what else we have to offer...|||>> you may want to take a look at what else we have to offer...
You are certainly good at copying others' code and claiming it as your own.
--
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"mark baekdal" <anonymous@.discussions.microsoft.com> wrote in message
news:61AD85AE-450A-4599-9503-EAB1CD7AF58B@.microsoft.com...
Hi Keith & Ahmed,
take a look at this script we give away
http://www.innovartis.co.uk/downloa..._statements.zip
you may want to take a look at what else we have to offer...|||I NEVER CLAIMED IT WAS MY OWN AND IT HAS BEEN
MODIFIED/IMPROVED!
GET BENT!!!

>--Original Message--
offer...
>You are certainly good at copying others' code and
claiming it as your own.
>--
>Vyas, MVP (SQL Server)
>http://vyaskn.tripod.com/
>
>"mark baekdal" <anonymous@.discussions.microsoft.com>
wrote in message
>news:61AD85AE-450A-4599-9503-
EAB1CD7AF58B@.microsoft.com...
>Hi Keith & Ahmed,
>take a look at this script we give away
>http://www.innovartis.co.uk/downloa...reate_insert_st
atements.zip
>you may want to take a look at what else we have to
offer...
>
>.
>|||First of all, who are you to 'give away' my code, on your website?
I did look at the code, and you did make a few modifications here and there.
But it is a minimum courtesy in software industry, to credit the original
author when using his/her code.
You wrote "There is no claim that it is your code". But hey, the code is
hosted on your website. You haven't bothered giving credit to me. Isn't that
implying that it's your code?
If you want to continue offering that code on your website, you better add
my name and URL in the header of that script. Or else, remove it from your
site.
I hate plagiarism.
--
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"mark baekdal" <anonymous@.discussions.microsoft.com> wrote in message
news:c3cc01c408f1$201cdec0$a301280a@.phx.gbl...
I NEVER CLAIMED IT WAS MY OWN AND IT HAS BEEN
MODIFIED/IMPROVED!
GET BENT!!!

>--Original Message--
offer...
>You are certainly good at copying others' code and
claiming it as your own.
>--
>Vyas, MVP (SQL Server)
>http://vyaskn.tripod.com/
>
>"mark baekdal" <anonymous@.discussions.microsoft.com>
wrote in message
>news:61AD85AE-450A-4599-9503-
EAB1CD7AF58B@.microsoft.com...
>Hi Keith & Ahmed,
>take a look at this script we give away
>http://www.innovartis.co.uk/downloa...reate_insert_st
atements.zip
>you may want to take a look at what else we have to
offer...
>
>.
>|||mark baekdal wrote:

>I NEVER CLAIMED IT WAS MY OWN AND IT HAS BEEN
>MODIFIED/IMPROVED!
>
>
Yeah, right. Read your own site:
<begin quote from your site>
The DB Ghost Process
*What you do, as a one-time process, is to:*
* Script out, using Enterprise Manager, your entire production
database's schema and static data into individual scripts. To
script your static data use this _stored procedure_ [link to
Vyas's sp] <downloads/sp_create_insert_statements.zip>. Some
companies actually market products to do this...
<end quote from your site>
I guess you find it easier to steal products to do this, remove the
copyright and authorship info and then throw a tantrum when you get
found out.
People like you suck.
Vyas's original code:
BEGIN
/ ****************************************
***********************************
********************************
Procedure: sp_generate_inserts (Build 22)
(Copyright 2002 Narayana Vyas Kondreddi. All rights reserved.)
Purpose: To generate INSERT statements from existing data.
These INSERTS can be executed to regenerate the data at some
other location.
This procedure is also useful to create a database setup, where
in you can
script your data along with your table definitions.
Written by: Narayana Vyas Kondreddi
http://vyaskn.tripod.com
Acknowledgements:
Divya Kalra -- For beta testing
Mark Charsley -- For reporting a problem with scripting
uniqueidentifier columns with NULL values
Artur Zeygman -- For helping me simplify a bit of code for
handling non-dbo owned tables
Joris Laperre -- For reporting a regression bug in handling
text/ntext columns
Tested on: SQL Server 7.0 and SQL Server 2000
Date created: January 17th 2001 21:52 GMT
Date modified: May 1st 2002 19:50 GMT
Email: vyaskn@.hotmail.com
NOTE: This procedure may not work with tables with too many columns.
Results can be unpredictable with huge text columns or SQL
Server 2000's sql_variant data types
Whenever possible, Use @.include_column_list parameter to ommit
column list in the INSERT statement, for better results
IMPORTANT: This procedure is not tested with internation data
(Extended characters or Unicode). If needed
you might want to convert the datatypes of character variables
in this procedure to their respective unicode counterparts
like nchar and nvarchar
The stolen code on your site:
BEGIN
/ ****************************************
***********************************
********************************
Procedure: sp_create_insert_statements
Purpose: To generate INSERT statements from existing data.
These INSERTS can be executed to regenerate the data at some
other location.
This procedure is also useful to create a database setup, where
in you can
script your data along with your table definitions.
NOTE: This procedure may not work with tables with too many columns.
Results can be unpredictable with huge text columns or SQL
Server 2000's sql_variant data types
IMPORTANT: Whenever possible, Use @.include_column_list parameter
to ommit column list in the INSERT statement, for better results
>GET BENT!!!
>
>
>offer...
>
>claiming it as your own.
>
>wrote in message
>
>EAB1CD7AF58B@.microsoft.com...
>
>atements.zip
>
>offer...
>|||The code has been modified extensively.
Deletes have been added.
A transaction has been created to make it atomic/durable.
Triggers have been turned off and then turned back on to
strop unwanted inserts.
Error handling has been added for completeness.
Transaction checking has been added for portability.
All of the above has added value to the script - that was
my intention. I did try to contact Vyas originally (about
two years ago) through the contact on
http://planetsourcecode.com/ where I originally found it,
however no-one got back to me. If I was trying to sell it
then I could understand your comments, but it is posted
here to help, and it is free, and it has been
modified/improved which may well help Ahmed - the whole
point of the excercise.
I will put in comments crediting Vyas for the original
work done, it has moved on though and it is not my
intention to ever profit from the work - I repeat - I
think I added value to it and it will always remain free.

>--Original Message--
>First of all, who are you to 'give away' my code, on
your website?
>I did look at the code, and you did make a few
modifications here and there.
>But it is a minimum courtesy in software industry, to
credit the original
>author when using his/her code.
>You wrote "There is no claim that it is your code". But
hey, the code is
>hosted on your website. You haven't bothered giving
credit to me. Isn't that
>implying that it's your code?
>If you want to continue offering that code on your
website, you better add
>my name and URL in the header of that script. Or else,
remove it from your
>site.
>I hate plagiarism.
>--
>Vyas, MVP (SQL Server)
>http://vyaskn.tripod.com/
>
>
>"mark baekdal" <anonymous@.discussions.microsoft.com>
wrote in message
>news:c3cc01c408f1$201cdec0$a301280a@.phx.gbl...
>I NEVER CLAIMED IT WAS MY OWN AND IT HAS BEEN
>MODIFIED/IMPROVED!
>GET BENT!!!
>
>offer...
>claiming it as your own.
>wrote in message
>EAB1CD7AF58B@.microsoft.com...
t
>atements.zip
>offer...
>
>.
>|||And another modification made
An order by clause was added to make sure the insert
statements are ordered by the primary key if it exists.
Previous to this contact - I hadn't recieved any even
though I did ask - therefore I could not ask anyone
requiring answers to questions to contact Vyas as I
couldn't get them. This is why there wasn't any
references. That has since changed and I have talked with
Vyas who seems happy with the arrangement as it now
stands.
I for one never want to have plagiarist next to my name.
And the end result is everyone has a damn fine piece of
code for free - that must be a good ending to this story!sql

Scripting ALTER TABLE

I need to create a script to disable all triggers and constraints in my
database.

It appears as though I cannot use a local variable for the table name in the
ALTER TABLE statement (e.g. ALTER TABLE @.TBL).

Is there any reason for this?

Thanks,

Kevin"Kevin Haugen" <khaugen@.pacbell.net> wrote in message
news:jx1Hd.12736$5R.1377@.newssvr21.news.prodigy.co m...
>I need to create a script to disable all triggers and constraints in my
>database.
> It appears as though I cannot use a local variable for the table name in
> the ALTER TABLE statement (e.g. ALTER TABLE @.TBL).
> Is there any reason for this?
> Thanks,
> Kevin

You can't use variables in place of table or column names, except when using
dynamic SQL, which has its own issues. Although if you're a DBA running an
admin script, then it's usually a reasonable option - there's more
discussion here:

http://www.sommarskog.se/dynamic_sql.html

Unfortunately, you don't say what your goal is, but if it's to load data
into the database, then all the usual loading tools (bcp.exe, DTS, BULK
INSERT) can ignore both constraints and triggers, so you might not need a
script anyway.

Simon|||"Simon Hayes" <sql@.hayes.ch> wrote in message
news:41ecd751$1_1@.news.bluewin.ch...
> "Kevin Haugen" <khaugen@.pacbell.net> wrote in message
> news:jx1Hd.12736$5R.1377@.newssvr21.news.prodigy.co m...
>>I need to create a script to disable all triggers and constraints in my
>>database.
>>
>> It appears as though I cannot use a local variable for the table name in
>> the ALTER TABLE statement (e.g. ALTER TABLE @.TBL).
>>
>> Is there any reason for this?
>>
>> Thanks,
>>
>> Kevin
>>
> You can't use variables in place of table or column names, except when
> using dynamic SQL, which has its own issues. Although if you're a DBA
> running an admin script, then it's usually a reasonable option - there's
> more discussion here:
> http://www.sommarskog.se/dynamic_sql.html
> Unfortunately, you don't say what your goal is, but if it's to load data
> into the database, then all the usual loading tools (bcp.exe, DTS, BULK
> INSERT) can ignore both constraints and triggers, so you might not need a
> script anyway.
> Simon

I'll look into it. I'm planning on converting existing data into a new
format. Since I have to identify each table and write a query to do the
conversion, I could easily do a copy/paste for each table to disable and
re-enable the triggers and constraints. I am hoping to shortcut some of the
work by automating that piece.

Thanks,

Kevin

Scripting a SQL database

1. With Enterprise Manager what is required to have the script create the
database, the SQL user and
set their password?
2. Now that I have created the scripts, how do I execute them on a different
SQL server and can it be done from ASP?Yes and yes.
You just use the connection.execute mysqlscriptstring
You've just got to connect to the database with an account
that has enough priviledge to run your script.
2005 Microsoft MVP C#
Robbe Morris
http://www.robbemorris.com
http://www.mastervb.net/home/ng/for...st10017013.aspx
http://www.eggheadcafe.com/articles...e_generator.asp
"Roland Hall" <nobody@.nonononono.us> wrote in message
news:%23J5RkctFFHA.3368@.TK2MSFTNGP10.phx.gbl...
> 1. With Enterprise Manager what is required to have the script create the
> database, the SQL user and
> set their password?
> 2. Now that I have created the scripts, how do I execute them on a
> different
> SQL server and can it be done from ASP?
>|||"Roland Hall" <nobody@.nonononono.us> wrote in
news:#J5RkctFFHA.3368@.TK2MSFTNGP10.phx.gbl:

> 1. With Enterprise Manager what is required to have the script create
> the database, the SQL user and
> set their password?
> 2. Now that I have created the scripts, how do I execute them on a
> different SQL server and can it be done from ASP?
>
Please clarify what you are trying to achieve.|||"JTC ^..^" wrote in message news:Xns9602F24559D9daveJTC@.217.32.252.50...
: "Roland Hall" <nobody@.nonononono.us> wrote in
: news:#J5RkctFFHA.3368@.TK2MSFTNGP10.phx.gbl:
:
: > 1. With Enterprise Manager what is required to have the script create
: > the database, the SQL user and
: > set their password?
: > 2. Now that I have created the scripts, how do I execute them on a
: > different SQL server and can it be done from ASP?
: >
: >
:
: Please clarify what you are trying to achieve.
Thanks for responding...
I have an application that uses SQL Server. Everything is portable except
the SQL part. I would like to create the database and the structure
programmatically without requiring the Admin to create it manually. My
preferred method would be to do it from ASP if possible. The other response
from Robbie, says it can be done.
I know SQL well enough to work with it in a web environment but not well
versed with SPs. Enterprise Manager appears to be able to create the
scripts for me, which will help me learn but I don't know how to utilize
them after they're created. I'm still trying to figure that out. A little
help would go a long way for me.
Roland|||"Robbe Morris [C# MVP]" <info@.turnkeytools.com> wrote in message
news:%23tAuoytFFHA.3840@.tk2msftngp13.phx.gbl...
: Yes and yes.
Thanks for responding...
I only asked one closed-end question. Can I get some pointers on getting
this done? You might want to review my response to the other responder.
Examples would be great but research material and knowing what to look for
would be fine too.
:
: You just use the connection.execute mysqlscriptstring
:
: You've just got to connect to the database with an account
: that has enough priviledge to run your script.
How are the scripts that Enterprise Manager can create from an existing
database used to recreate this structure on a different server?
Roland|||Roland Hall (nobody@.nowhere) writes:
> I have an application that uses SQL Server. Everything is portable
> except the SQL part. I would like to create the database and the
> structure programmatically without requiring the Admin to create it
> manually. My preferred method would be to do it from ASP if possible.
> The other response from Robbie, says it can be done.
> I know SQL well enough to work with it in a web environment but not well
> versed with SPs. Enterprise Manager appears to be able to create the
> scripts for me, which will help me learn but I don't know how to utilize
> them after they're created. I'm still trying to figure that out. A
> little help would go a long way for me.
To do this without Enterprise Mangager you would have to use DMO which is
a OLE interface. No, I am not going give examples, because I don't use
DMO myself. But is documented in Books Online.
Once you have the scripts, running them from ASP is no different from
running any other SQL statements. Just recall that GO is not an SQL
statement - you will have to split the code into batches yourself.
Personally, I would build this on a solution where I had the scripts to
build the database under version control, and work from these. In fact
this is how we distribute our databases, although our build tool is in
Perl and not in ASP.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Virtually everything SQL Server Enterprise Manager does can be
do via script because that is in large part what EM uses "at some
point" in the process (either directly or indirectly via DMO).
To run the generated script in ASP, you literally just
read the script file in as a string and pass it to the ADO
connection object's .Execute method. It is that simple.
You can also create a DTS (Data Transformation Package)
utilizing the Import / Export Wizard. Then, use ASP to run
the package. This, in my opinion, may be a better option than
scripts from a support and simplicity standpoint. My guess is
that you'll want to select the "copy database objects and data"
when you get to the part in the wizard where it asks you what
you want to do. At the end, you'll be given an option as
to how you want to store the package. Select the option to
store it in SQL Server. What is nice about this option is that
if you make a change to your database, you just rerun the
wizard (after deleting the current package) and you are all set.
No need to move files around or deploy them.
Using the wizard is pretty straight forward. Calling the stored
package in SQL Server isn't. Here is a VBScript (which
is really the underlying language in ASP) to run a DTS
package. It also includes other code for ftp'ing data that
you probably don't need.
http://www.eggheadcafe.com/articles/20030923.asp
I'm upgrading our servers today. So, if the link doesn't come
up, check again in a few hours.
These other SQL Server Flash Video presentations for database
management may also come in handy:
http://www.eggheadcafe.com/videodemo/
2005 Microsoft MVP C#
Robbe Morris
http://www.robbemorris.com
http://www.mastervb.net/home/ng/for...st10017013.aspx
http://www.eggheadcafe.com/articles...e_generator.asp
"Roland Hall" <nobody@.nowhere> wrote in message
news:e6iVeUzFFHA.2180@.TK2MSFTNGP10.phx.gbl...
> "Robbe Morris [C# MVP]" <info@.turnkeytools.com> wrote in message
> news:%23tAuoytFFHA.3840@.tk2msftngp13.phx.gbl...
> : Yes and yes.
> Thanks for responding...
> I only asked one closed-end question. Can I get some pointers on getting
> this done? You might want to review my response to the other responder.
> Examples would be great but research material and knowing what to look for
> would be fine too.
> :
> : You just use the connection.execute mysqlscriptstring
> :
> : You've just got to connect to the database with an account
> : that has enough priviledge to run your script.
> How are the scripts that Enterprise Manager can create from an existing
> database used to recreate this structure on a different server?
> Roland
>

Scripting a SQL database

1. With Enterprise Manager what is required to have the script create the
database, the SQL user and
set their password?
2. Now that I have created the scripts, how do I execute them on a different
SQL server and can it be done from ASP?
Yes and yes.
You just use the connection.execute mysqlscriptstring
You've just got to connect to the database with an account
that has enough priviledge to run your script.
2005 Microsoft MVP C#
Robbe Morris
http://www.robbemorris.com
http://www.mastervb.net/home/ng/foru...t10017013.aspx
http://www.eggheadcafe.com/articles/..._generator.asp
"Roland Hall" <nobody@.nonononono.us> wrote in message
news:%23J5RkctFFHA.3368@.TK2MSFTNGP10.phx.gbl...
> 1. With Enterprise Manager what is required to have the script create the
> database, the SQL user and
> set their password?
> 2. Now that I have created the scripts, how do I execute them on a
> different
> SQL server and can it be done from ASP?
>
|||"Roland Hall" <nobody@.nonononono.us> wrote in
news:#J5RkctFFHA.3368@.TK2MSFTNGP10.phx.gbl:

> 1. With Enterprise Manager what is required to have the script create
> the database, the SQL user and
> set their password?
> 2. Now that I have created the scripts, how do I execute them on a
> different SQL server and can it be done from ASP?
>
Please clarify what you are trying to achieve.
|||"JTC ^..^" wrote in message news:Xns9602F24559D9daveJTC@.217.32.252.50...
: "Roland Hall" <nobody@.nonononono.us> wrote in
: news:#J5RkctFFHA.3368@.TK2MSFTNGP10.phx.gbl:
:
: > 1. With Enterprise Manager what is required to have the script create
: > the database, the SQL user and
: > set their password?
: > 2. Now that I have created the scripts, how do I execute them on a
: > different SQL server and can it be done from ASP?
: >
: >
:
: Please clarify what you are trying to achieve.
Thanks for responding...
I have an application that uses SQL Server. Everything is portable except
the SQL part. I would like to create the database and the structure
programmatically without requiring the Admin to create it manually. My
preferred method would be to do it from ASP if possible. The other response
from Robbie, says it can be done.
I know SQL well enough to work with it in a web environment but not well
versed with SPs. Enterprise Manager appears to be able to create the
scripts for me, which will help me learn but I don't know how to utilize
them after they're created. I'm still trying to figure that out. A little
help would go a long way for me.
Roland
|||"Robbe Morris [C# MVP]" <info@.turnkeytools.com> wrote in message
news:%23tAuoytFFHA.3840@.tk2msftngp13.phx.gbl...
: Yes and yes.
Thanks for responding...
I only asked one closed-end question. Can I get some pointers on getting
this done? You might want to review my response to the other responder.
Examples would be great but research material and knowing what to look for
would be fine too.
:
: You just use the connection.execute mysqlscriptstring
:
: You've just got to connect to the database with an account
: that has enough priviledge to run your script.
How are the scripts that Enterprise Manager can create from an existing
database used to recreate this structure on a different server?
Roland
|||Roland Hall (nobody@.nowhere) writes:
> I have an application that uses SQL Server. Everything is portable
> except the SQL part. I would like to create the database and the
> structure programmatically without requiring the Admin to create it
> manually. My preferred method would be to do it from ASP if possible.
> The other response from Robbie, says it can be done.
> I know SQL well enough to work with it in a web environment but not well
> versed with SPs. Enterprise Manager appears to be able to create the
> scripts for me, which will help me learn but I don't know how to utilize
> them after they're created. I'm still trying to figure that out. A
> little help would go a long way for me.
To do this without Enterprise Mangager you would have to use DMO which is
a OLE interface. No, I am not going give examples, because I don't use
DMO myself. But is documented in Books Online.
Once you have the scripts, running them from ASP is no different from
running any other SQL statements. Just recall that GO is not an SQL
statement - you will have to split the code into batches yourself.
Personally, I would build this on a solution where I had the scripts to
build the database under version control, and work from these. In fact
this is how we distribute our databases, although our build tool is in
Perl and not in ASP.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
|||Virtually everything SQL Server Enterprise Manager does can be
do via script because that is in large part what EM uses "at some
point" in the process (either directly or indirectly via DMO).
To run the generated script in ASP, you literally just
read the script file in as a string and pass it to the ADO
connection object's .Execute method. It is that simple.
You can also create a DTS (Data Transformation Package)
utilizing the Import / Export Wizard. Then, use ASP to run
the package. This, in my opinion, may be a better option than
scripts from a support and simplicity standpoint. My guess is
that you'll want to select the "copy database objects and data"
when you get to the part in the wizard where it asks you what
you want to do. At the end, you'll be given an option as
to how you want to store the package. Select the option to
store it in SQL Server. What is nice about this option is that
if you make a change to your database, you just rerun the
wizard (after deleting the current package) and you are all set.
No need to move files around or deploy them.
Using the wizard is pretty straight forward. Calling the stored
package in SQL Server isn't. Here is a VBScript (which
is really the underlying language in ASP) to run a DTS
package. It also includes other code for ftp'ing data that
you probably don't need.
http://www.eggheadcafe.com/articles/20030923.asp
I'm upgrading our servers today. So, if the link doesn't come
up, check again in a few hours.
These other SQL Server Flash Video presentations for database
management may also come in handy:
http://www.eggheadcafe.com/videodemo/
2005 Microsoft MVP C#
Robbe Morris
http://www.robbemorris.com
http://www.mastervb.net/home/ng/foru...t10017013.aspx
http://www.eggheadcafe.com/articles/..._generator.asp
"Roland Hall" <nobody@.nowhere> wrote in message
news:e6iVeUzFFHA.2180@.TK2MSFTNGP10.phx.gbl...
> "Robbe Morris [C# MVP]" <info@.turnkeytools.com> wrote in message
> news:%23tAuoytFFHA.3840@.tk2msftngp13.phx.gbl...
> : Yes and yes.
> Thanks for responding...
> I only asked one closed-end question. Can I get some pointers on getting
> this done? You might want to review my response to the other responder.
> Examples would be great but research material and knowing what to look for
> would be fine too.
> :
> : You just use the connection.execute mysqlscriptstring
> :
> : You've just got to connect to the database with an account
> : that has enough priviledge to run your script.
> How are the scripts that Enterprise Manager can create from an existing
> database used to recreate this structure on a different server?
> Roland
>

Scripting a SQL database

1. With Enterprise Manager what is required to have the script create the
database, the SQL user and
set their password?
2. Now that I have created the scripts, how do I execute them on a different
SQL server and can it be done from ASP?Yes and yes.
You just use the connection.execute mysqlscriptstring
You've just got to connect to the database with an account
that has enough priviledge to run your script.
--
2005 Microsoft MVP C#
Robbe Morris
http://www.robbemorris.com
http://www.mastervb.net/home/ng/forumvbcode/post10017013.aspx
http://www.eggheadcafe.com/articles/adonet_source_code_generator.asp
"Roland Hall" <nobody@.nonononono.us> wrote in message
news:%23J5RkctFFHA.3368@.TK2MSFTNGP10.phx.gbl...
> 1. With Enterprise Manager what is required to have the script create the
> database, the SQL user and
> set their password?
> 2. Now that I have created the scripts, how do I execute them on a
> different
> SQL server and can it be done from ASP?
>|||"Roland Hall" <nobody@.nonononono.us> wrote in
news:#J5RkctFFHA.3368@.TK2MSFTNGP10.phx.gbl:
> 1. With Enterprise Manager what is required to have the script create
> the database, the SQL user and
> set their password?
> 2. Now that I have created the scripts, how do I execute them on a
> different SQL server and can it be done from ASP?
>
Please clarify what you are trying to achieve.|||"JTC ^..^" wrote in message news:Xns9602F24559D9daveJTC@.217.32.252.50...
: "Roland Hall" <nobody@.nonononono.us> wrote in
: news:#J5RkctFFHA.3368@.TK2MSFTNGP10.phx.gbl:
:
: > 1. With Enterprise Manager what is required to have the script create
: > the database, the SQL user and
: > set their password?
: > 2. Now that I have created the scripts, how do I execute them on a
: > different SQL server and can it be done from ASP?
: >
: >
:
: Please clarify what you are trying to achieve.
Thanks for responding...
I have an application that uses SQL Server. Everything is portable except
the SQL part. I would like to create the database and the structure
programmatically without requiring the Admin to create it manually. My
preferred method would be to do it from ASP if possible. The other response
from Robbie, says it can be done.
I know SQL well enough to work with it in a web environment but not well
versed with SPs. Enterprise Manager appears to be able to create the
scripts for me, which will help me learn but I don't know how to utilize
them after they're created. I'm still trying to figure that out. A little
help would go a long way for me.
Roland|||"Robbe Morris [C# MVP]" <info@.turnkeytools.com> wrote in message
news:%23tAuoytFFHA.3840@.tk2msftngp13.phx.gbl...
: Yes and yes.
Thanks for responding...
I only asked one closed-end question. Can I get some pointers on getting
this done? You might want to review my response to the other responder.
Examples would be great but research material and knowing what to look for
would be fine too.
:
: You just use the connection.execute mysqlscriptstring
:
: You've just got to connect to the database with an account
: that has enough priviledge to run your script.
How are the scripts that Enterprise Manager can create from an existing
database used to recreate this structure on a different server?
Roland|||Roland Hall (nobody@.nowhere) writes:
> I have an application that uses SQL Server. Everything is portable
> except the SQL part. I would like to create the database and the
> structure programmatically without requiring the Admin to create it
> manually. My preferred method would be to do it from ASP if possible.
> The other response from Robbie, says it can be done.
> I know SQL well enough to work with it in a web environment but not well
> versed with SPs. Enterprise Manager appears to be able to create the
> scripts for me, which will help me learn but I don't know how to utilize
> them after they're created. I'm still trying to figure that out. A
> little help would go a long way for me.
To do this without Enterprise Mangager you would have to use DMO which is
a OLE interface. No, I am not going give examples, because I don't use
DMO myself. But is documented in Books Online.
Once you have the scripts, running them from ASP is no different from
running any other SQL statements. Just recall that GO is not an SQL
statement - you will have to split the code into batches yourself.
Personally, I would build this on a solution where I had the scripts to
build the database under version control, and work from these. In fact
this is how we distribute our databases, although our build tool is in
Perl and not in ASP.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp|||Virtually everything SQL Server Enterprise Manager does can be
do via script because that is in large part what EM uses "at some
point" in the process (either directly or indirectly via DMO).
To run the generated script in ASP, you literally just
read the script file in as a string and pass it to the ADO
connection object's .Execute method. It is that simple.
You can also create a DTS (Data Transformation Package)
utilizing the Import / Export Wizard. Then, use ASP to run
the package. This, in my opinion, may be a better option than
scripts from a support and simplicity standpoint. My guess is
that you'll want to select the "copy database objects and data"
when you get to the part in the wizard where it asks you what
you want to do. At the end, you'll be given an option as
to how you want to store the package. Select the option to
store it in SQL Server. What is nice about this option is that
if you make a change to your database, you just rerun the
wizard (after deleting the current package) and you are all set.
No need to move files around or deploy them.
Using the wizard is pretty straight forward. Calling the stored
package in SQL Server isn't. Here is a VBScript (which
is really the underlying language in ASP) to run a DTS
package. It also includes other code for ftp'ing data that
you probably don't need.
http://www.eggheadcafe.com/articles/20030923.asp
I'm upgrading our servers today. So, if the link doesn't come
up, check again in a few hours.
These other SQL Server Flash Video presentations for database
management may also come in handy:
http://www.eggheadcafe.com/videodemo/
--
2005 Microsoft MVP C#
Robbe Morris
http://www.robbemorris.com
http://www.mastervb.net/home/ng/forumvbcode/post10017013.aspx
http://www.eggheadcafe.com/articles/adonet_source_code_generator.asp
"Roland Hall" <nobody@.nowhere> wrote in message
news:e6iVeUzFFHA.2180@.TK2MSFTNGP10.phx.gbl...
> "Robbe Morris [C# MVP]" <info@.turnkeytools.com> wrote in message
> news:%23tAuoytFFHA.3840@.tk2msftngp13.phx.gbl...
> : Yes and yes.
> Thanks for responding...
> I only asked one closed-end question. Can I get some pointers on getting
> this done? You might want to review my response to the other responder.
> Examples would be great but research material and knowing what to look for
> would be fine too.
> :
> : You just use the connection.execute mysqlscriptstring
> :
> : You've just got to connect to the database with an account
> : that has enough priviledge to run your script.
> How are the scripts that Enterprise Manager can create from an existing
> database used to recreate this structure on a different server?
> Roland
>

Scripting a query to run daily.

I was just asked to script a Query that needs to run
daily and save the results as a file somewhere on the
network. The problem is that this report query needs to
be changed daily for example I need to run a report on
all scheduled apointments from the day before. My
question is this: Is there a system variable that I can
enter on the query that tells SQL to check the current
date on the system minus one so that the report is from
the day before (or Something similar). I have the query
and it runs as it should but I need to change the date
manually. If there's a solution to this problem please
let me know and how to insert it on the query.
Thanks.getDate() gets the current system date. There are some date-manipulation
functions, such as dateAdd() (or something similar). Check out BOL
"Carlos Santos" <anonymous@.discussions.microsoft.com> wrote in message
news:2b15e01c4682c$b05b9c20$a501280a@.phx
.gbl...
> I was just asked to script a Query that needs to run
> daily and save the results as a file somewhere on the
> network. The problem is that this report query needs to
> be changed daily for example I need to run a report on
> all scheduled apointments from the day before. My
> question is this: Is there a system variable that I can
> enter on the query that tells SQL to check the current
> date on the system minus one so that the report is from
> the day before (or Something similar). I have the query
> and it runs as it should but I need to change the date
> manually. If there's a solution to this problem please
> let me know and how to insert it on the query.
> Thanks.|||You need to look at DATEADD function. This function allows you to do
datemath to calculate a new date. Also you might want to look into the
CONVERT function to get the output of the DATEADD function into a format to
compare with your data. Here is a query that uses these function to get
yesterdays date in the following format mm/dd/yyyy:
select * from yourtable where yourdatecolumn =
convert(char(10),dateadd(dd,-1,getdate()),101)
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Carlos Santos" <anonymous@.discussions.microsoft.com> wrote in message
news:2b15e01c4682c$b05b9c20$a501280a@.phx
.gbl...
> I was just asked to script a Query that needs to run
> daily and save the results as a file somewhere on the
> network. The problem is that this report query needs to
> be changed daily for example I need to run a report on
> all scheduled apointments from the day before. My
> question is this: Is there a system variable that I can
> enter on the query that tells SQL to check the current
> date on the system minus one so that the report is from
> the day before (or Something similar). I have the query
> and it runs as it should but I need to change the date
> manually. If there's a solution to this problem please
> let me know and how to insert it on the query.
> Thanks.sql

Scripting a query to run daily.

I was just asked to script a Query that needs to run
daily and save the results as a file somewhere on the
network. The problem is that this report query needs to
be changed daily for example I need to run a report on
all scheduled apointments from the day before. My
question is this: Is there a system variable that I can
enter on the query that tells SQL to check the current
date on the system minus one so that the report is from
the day before (or Something similar). I have the query
and it runs as it should but I need to change the date
manually. If there's a solution to this problem please
let me know and how to insert it on the query.
Thanks.
getDate() gets the current system date. There are some date-manipulation
functions, such as dateAdd() (or something similar). Check out BOL
"Carlos Santos" <anonymous@.discussions.microsoft.com> wrote in message
news:2b15e01c4682c$b05b9c20$a501280a@.phx.gbl...
> I was just asked to script a Query that needs to run
> daily and save the results as a file somewhere on the
> network. The problem is that this report query needs to
> be changed daily for example I need to run a report on
> all scheduled apointments from the day before. My
> question is this: Is there a system variable that I can
> enter on the query that tells SQL to check the current
> date on the system minus one so that the report is from
> the day before (or Something similar). I have the query
> and it runs as it should but I need to change the date
> manually. If there's a solution to this problem please
> let me know and how to insert it on the query.
> Thanks.
|||You need to look at DATEADD function. This function allows you to do
datemath to calculate a new date. Also you might want to look into the
CONVERT function to get the output of the DATEADD function into a format to
compare with your data. Here is a query that uses these function to get
yesterdays date in the following format mm/dd/yyyy:
select * from yourtable where yourdatecolumn =
convert(char(10),dateadd(dd,-1,getdate()),101)
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Carlos Santos" <anonymous@.discussions.microsoft.com> wrote in message
news:2b15e01c4682c$b05b9c20$a501280a@.phx.gbl...
> I was just asked to script a Query that needs to run
> daily and save the results as a file somewhere on the
> network. The problem is that this report query needs to
> be changed daily for example I need to run a report on
> all scheduled apointments from the day before. My
> question is this: Is there a system variable that I can
> enter on the query that tells SQL to check the current
> date on the system minus one so that the report is from
> the day before (or Something similar). I have the query
> and it runs as it should but I need to change the date
> manually. If there's a solution to this problem please
> let me know and how to insert it on the query.
> Thanks.

Scripting a query to run daily.

I was just asked to script a Query that needs to run
daily and save the results as a file somewhere on the
network. The problem is that this report query needs to
be changed daily for example I need to run a report on
all scheduled apointments from the day before. My
question is this: Is there a system variable that I can
enter on the query that tells SQL to check the current
date on the system minus one so that the report is from
the day before (or Something similar). I have the query
and it runs as it should but I need to change the date
manually. If there's a solution to this problem please
let me know and how to insert it on the query.
Thanks.getDate() gets the current system date. There are some date-manipulation
functions, such as dateAdd() (or something similar). Check out BOL
"Carlos Santos" <anonymous@.discussions.microsoft.com> wrote in message
news:2b15e01c4682c$b05b9c20$a501280a@.phx.gbl...
> I was just asked to script a Query that needs to run
> daily and save the results as a file somewhere on the
> network. The problem is that this report query needs to
> be changed daily for example I need to run a report on
> all scheduled apointments from the day before. My
> question is this: Is there a system variable that I can
> enter on the query that tells SQL to check the current
> date on the system minus one so that the report is from
> the day before (or Something similar). I have the query
> and it runs as it should but I need to change the date
> manually. If there's a solution to this problem please
> let me know and how to insert it on the query.
> Thanks.|||You need to look at DATEADD function. This function allows you to do
datemath to calculate a new date. Also you might want to look into the
CONVERT function to get the output of the DATEADD function into a format to
compare with your data. Here is a query that uses these function to get
yesterdays date in the following format mm/dd/yyyy:
select * from yourtable where yourdatecolumn =convert(char(10),dateadd(dd,-1,getdate()),101)
--
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Carlos Santos" <anonymous@.discussions.microsoft.com> wrote in message
news:2b15e01c4682c$b05b9c20$a501280a@.phx.gbl...
> I was just asked to script a Query that needs to run
> daily and save the results as a file somewhere on the
> network. The problem is that this report query needs to
> be changed daily for example I need to run a report on
> all scheduled apointments from the day before. My
> question is this: Is there a system variable that I can
> enter on the query that tells SQL to check the current
> date on the system minus one so that the report is from
> the day before (or Something similar). I have the query
> and it runs as it should but I need to change the date
> manually. If there's a solution to this problem please
> let me know and how to insert it on the query.
> Thanks.

Scripting a maintenance plan

Simple and quick question..
Is it possible to script a maintainence plan in SQL 2000? If so how?yes. start by reading the BOL articles for DBCC and BACKUP. Use the sql you write to create jobs.

Scripting a database template

The problem I am having is that I want to replace the sql script language in
a script and call individual files containing that language from our source
control.
How can I call/run multiple .sql files against a database from a single
script file?It depends on what tool you are using to execute the script. OSQL and SQLCMD
has the :r option. The
sub-script cannot have GO in it, though.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Thomas Mick" <nospam@.nospam.com> wrote in message news:12o3bl2hfjlod55@.corp.supernews.com..
.
> The problem I am having is that I want to replace the sql script language
in a script and call
> individual files containing that language from our source control.
> How can I call/run multiple .sql files against a database from a single sc
ript file?
>