generating sql scripts of stored procedures

Can someone tell me why I get this behavior?
Whenever I build a new stored procedure into my database I specifically
set the ansi nulls and quoted identifier on like so
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE xxxx AS
It compiles fine, but when I go back to Enterprise Mgr and right click
on the stored procedure and "generate script" it puts
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
before stored proc, and
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
at the end of the script. All my settings in sql server and database
say QUOTED_IDENTIFIER is ON.. SO why does it keep adding
QUOTED_IDENTIFIER OFF to the end of my scripts? I stopped my service to
restarted it.. What could be causing this> restarted it.. What could be causing this
IMO, design miss in EM...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"David" <daveygf@.yahoo.com> wrote in message
news:1108059473.053294.211600@.z14g2000cwz.googlegroups.com...
> Can someone tell me why I get this behavior?
> Whenever I build a new stored procedure into my database I specifically
> set the ansi nulls and quoted identifier on like so
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_NULLS ON
> GO
> CREATE PROCEDURE xxxx AS
> It compiles fine, but when I go back to Enterprise Mgr and right click
> on the stored procedure and "generate script" it puts
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_NULLS ON
> GO
> before stored proc, and
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
> at the end of the script. All my settings in sql server and database
> say QUOTED_IDENTIFIER is ON.. SO why does it keep adding
> QUOTED_IDENTIFIER OFF to the end of my scripts? I stopped my service to
> restarted it.. What could be causing this
>

Generating SQL Script

Hello there
I have huge database on sql server. The database probide tables, views,
store procedures and functions
Some of the views or the store procedures are depend on the functions, store
presedures are depend on the views.
When i update the version I generate SQL Script and run it on my client
The script first of all destroy objects on the client and establish the new
schema. The sql script firs create tables, views, store procedures and at
finaly functions.
Because some of the views or the store procedures are using the functions
they can't be created.
What i need to do to create first the functions?
and if on the future i will create functions that using functions. Is there
a "smart" script that first of all create the objects without any
dependencies and after that create wnat under them?
any help would be usefulI have the same problem with some scripts I generate. Here's the solutions
I came up with:
1) manually re-arrange the script so that objects that need to be created
first are created first, or
2) generate multiple scripts (generate one that just creates functions, one
that just creates views, etc.)
I prefer the second method myself, since it's less work, especially for very
large scripts.
Thx
"Roy Goldhammer" <roygoldh@.hotmail.com> wrote in message
news:%23blV03ZHFHA.400@.TK2MSFTNGP14.phx.gbl...
> Hello there
> I have huge database on sql server. The database probide tables, views,
> store procedures and functions
> Some of the views or the store procedures are depend on the functions,
> store
> presedures are depend on the views.
> When i update the version I generate SQL Script and run it on my client
> The script first of all destroy objects on the client and establish the
> new
> schema. The sql script firs create tables, views, store procedures and at
> finaly functions.
> Because some of the views or the store procedures are using the functions
> they can't be created.
> What i need to do to create first the functions?
> and if on the future i will create functions that using functions. Is
> there
> a "smart" script that first of all create the objects without any
> dependencies and after that create wnat under them?
> any help would be useful
>

Generating scripts, tables, views, procedures, roles...

Hi...

I'm trying to generate scripts in SQL Server Management Studio 2005.

When I choose 'Script all objects' I get an error when I try to execute it. When I generate the scripts in single files, only tables in one file, only views in one file etc. etc., the execution is succeded.

1) Why do I get an error when I try to execute the script containing tables, views, procedures in one file....?

2) I get an error in the view-file where one column is 'invalid' but I can see it in the view. A generated script should execute succesfully when it is generated one second ago on the same database and so on... Right?

Thanks....

Which exact error do you get ?

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

sorry, I cant reproduce the errors because my workbuddy has the day off...

But I remember it was one of the views with 2 columns in it, which caused the error: 'Invalid item on view...'

And the sequense in the script is wrong. For example I can see that the script are trying to create a view before the table is created?

|||

You may find that the Database Publishing Wizard offers you more functionality.

Database Publishing Wizard
http://www.microsoft.com/downloads/details.aspx?familyid=56E5B1C5-BF17-42E0-A410-371A838E570A&displaylang=en

|||Thomas, did you find a solution to your problem?

I have the same problem and Database Publishing Wizard does not work any better.

The CREATE VIEW is placed before the dependant tables in the script.

This ONLY happens when I'm trying to script a SQL Server 2005 Express db.

I run the script wizard in SQL Server Management Studio 2005.

SQL Server 2005 Express: 9.0.3042
SQL Server 2005: 9.0.1399

Thanks
Jonas
|||

Did you use the switch "create dependent objects" ?

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Yes I did.

I've now updated to sp2 (9.0.3042) on the SQL Server 2005 too - no difference. Except that the default value of "create dependant objects" in the script wizard is now false.

I can not include any image of the properties selected, but all are default exept the dependant switch which is true.

The final report start with:

Generate Script Progress

- Determining objects in database '....MDF' that will be scripted. (Success)

- dbo.AllDataView (Success)

- dbo.FindNewRawData (Success)

Which also shows that a View is created first of all objects.

Thanks

Jonas

Generating scripts, tables, views, procedures, roles...

Hi...

I'm trying to generate scripts in SQL Server Management Studio 2005.

When I choose 'Script all objects' I get an error when I try to execute it. When I generate the scripts in single files, only tables in one file, only views in one file etc. etc., the execution is succeded.

1) Why do I get an error when I try to execute the script containing tables, views, procedures in one file....?

2) I get an error in the view-file where one column is 'invalid' but I can see it in the view. A generated script should execute succesfully when it is generated one second ago on the same database and so on... Right?

Thanks....

Which exact error do you get ?

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

sorry, I cant reproduce the errors because my workbuddy has the day off...

But I remember it was one of the views with 2 columns in it, which caused the error: 'Invalid item on view...'

And the sequense in the script is wrong. For example I can see that the script are trying to create a view before the table is created?

|||

You may find that the Database Publishing Wizard offers you more functionality.

Database Publishing Wizard
http://www.microsoft.com/downloads/details.aspx?familyid=56E5B1C5-BF17-42E0-A410-371A838E570A&displaylang=en

|||Thomas, did you find a solution to your problem?

I have the same problem and Database Publishing Wizard does not work any better.

The CREATE VIEW is placed before the dependant tables in the script.

This ONLY happens when I'm trying to script a SQL Server 2005 Express db.

I run the script wizard in SQL Server Management Studio 2005.

SQL Server 2005 Express: 9.0.3042
SQL Server 2005: 9.0.1399

Thanks
Jonas
|||

Did you use the switch "create dependent objects" ?

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Yes I did.

I've now updated to sp2 (9.0.3042) on the SQL Server 2005 too - no difference. Except that the default value of "create dependant objects" in the script wizard is now false.

I can not include any image of the properties selected, but all are default exept the dependant switch which is true.

The final report start with:

Generate Script Progress

- Determining objects in database '....MDF' that will be scripted. (Success)

- dbo.AllDataView (Success)

- dbo.FindNewRawData (Success)

Which also shows that a View is created first of all objects.

Thanks

Jonas

Generating scripts, tables, views, procedures, roles...

Hi...

I'm trying to generate scripts in SQL Server Management Studio 2005.

When I choose 'Script all objects' I get an error when I try to execute it. When I generate the scripts in single files, only tables in one file, only views in one file etc. etc., the execution is succeded.

1) Why do I get an error when I try to execute the script containing tables, views, procedures in one file....?

2) I get an error in the view-file where one column is 'invalid' but I can see it in the view. A generated script should execute succesfully when it is generated one second ago on the same database and so on... Right?

Thanks....

Which exact error do you get ?

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

sorry, I cant reproduce the errors because my workbuddy has the day off...

But I remember it was one of the views with 2 columns in it, which caused the error: 'Invalid item on view...'

And the sequense in the script is wrong. For example I can see that the script are trying to create a view before the table is created?

|||

You may find that the Database Publishing Wizard offers you more functionality.

Database Publishing Wizard
http://www.microsoft.com/downloads/details.aspx?familyid=56E5B1C5-BF17-42E0-A410-371A838E570A&displaylang=en

|||Thomas, did you find a solution to your problem?

I have the same problem and Database Publishing Wizard does not work any better.

The CREATE VIEW is placed before the dependant tables in the script.

This ONLY happens when I'm trying to script a SQL Server 2005 Express db.

I run the script wizard in SQL Server Management Studio 2005.

SQL Server 2005 Express: 9.0.3042
SQL Server 2005: 9.0.1399

Thanks
Jonas
|||

Did you use the switch "create dependent objects" ?

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Yes I did.

I've now updated to sp2 (9.0.3042) on the SQL Server 2005 too - no difference. Except that the default value of "create dependant objects" in the script wizard is now false.

I can not include any image of the properties selected, but all are default exept the dependant switch which is true.

The final report start with:

Generate Script Progress

- Determining objects in database '....MDF' that will be scripted. (Success)

- dbo.AllDataView (Success)

- dbo.FindNewRawData (Success)

Which also shows that a View is created first of all objects.

Thanks

Jonas

Generating script per object

Hi,
I want to generate Script for 4 Stored Procedures at a time in SQL SERVER 2005 as 4 seperate files.

In SQL SERVER 2000 there is a option
-Script file per object, using which we can generate script only once for all the 4 sps as 4 seperate files
Is there any such option in 2005?

Please suggest.

Thanks in advance

Ram

They added this feature to the Scripting Wizard in SP2.|||

Jason Callas wrote:

They added this feature to the Scripting Wizard in SP2.

To elaborate a little bit more... if you use the Generate Script Wizard in SQL Server 2005 SP2, you can choose to create one file per object.

Steps:

1) Right-click on a database -> Tasks -> Generate Scripts...

2) On the "Output Option" screen

a) Choose "Script to file"

b) Choose "File per object"

Paul A. Mestemaker II

Program Manager

Microsoft SQL Server Manageability

http://blogs.msdn.com/sqlrem/

sql

Generating Script in sql2005

Previosly, in SqlServer2000, when i was putting database changes (stored procedures) to the production server, I used to generate script for all stored procedures on the source server and run it it on the production server. It had in itself all the "If Exist--drop" rows, so it was posible to copy the new ones and change the existing stored procedures .

Now I'm using SqlServer2005 Express and when i want to generate sript, that "If Exist--drop" is missing so i have trouble changing all stored procedures in few steps. My question is: How can i get the script to contain those "If Exist--drop" automaticaly?

thanks

Yes. Management Studio uses the following logic,

When you create, "Script Behavior” = “Generate Create Statement Only”

If you say “Include If NOT Exists” option as TRUE then it Generate the following template

IF NOT EXISTS (SELECT * FROM ..

BEGIN

CREATE ..

END

When you create, "Script Behavior” = “Generate Drop Statement Only”

If you say “Include If NOT Exists” option as TRUE then it Generate the following template

IF EXISTS (SELECT * FROM ..

BEGIN

DROP ..

END

Missing in Console:

When you create, "Script Behavior” = “Generate Drop Statements Followed By Create Statement”

If you say “Include If NOT Exists” option as TRUE then it Generate the following template

IF EXISTS (SELECT * FROM ..

BEGIN

DROP ..

END

CREATE ..

Since the above option is missing (may be bug), first generate the drop script & append with Create generation Script with/without If Not Exist.

|||thanks|||

(This is a related question, so I'll borrow the thread)

Is there a way to save the scripting options for the script wizard or customize the default options?

For various reasons (as this thread points out) I use script generaiton options changed from the defualts as:

Include Descriptive Headers = FALSE
Include If NOT EXISTS = True
Script Drop = True
Script USE DATABASE = FALSE
Script Indexes = True

Can I change my Script Generation Wizard defaults so I don't have to select these options each time?

|||

michaelplevy wrote:

Is there a way to save the scripting options for the script wizard or customize the default options?

With SQL Server 2005 Service Pack 2, we've introduced the Tools -> Options... -> Scripting dialog so that you can change the behavior of how Management Studio generates scripts. These settings will be the default settings for the Generate Script Wizard.

Does this help?

Paul A. Mestemaker II

Program Manager

Microsoft SQL Server

http://blogs.msdn.com/sqlrem/

|||Paul, thanks for the tip about changing the script options.|||Hello,

Not all of the options available during scripting appear to be configurable from Tools > Options.
I'm running 9.00.3042.00. Is there a hotfix that remedies this? Or is the selective availability by design? I'm mainly interested in the "Script Drop" option.

Vivek

Generating Script in sql2005

Previosly, in SqlServer2000, when i was putting database changes (stored procedures) to the production server, I used to generate script for all stored procedures on the source server and run it it on the production server. It had in itself all the "If Exist--drop" rows, so it was posible to copy the new ones and change the existing stored procedures .

Now I'm using SqlServer2005 Express and when i want to generate sript, that "If Exist--drop" is missing so i have trouble changing all stored procedures in few steps. My question is: How can i get the script to contain those "If Exist--drop" automaticaly?

thanks

Yes. Management Studio uses the following logic,

When you create, "Script Behavior” = “Generate Create Statement Only”

If you say “Include If NOT Exists” option as TRUE then it Generate the following template

IF NOT EXISTS (SELECT * FROM ..

BEGIN

CREATE ..

END

When you create, "Script Behavior” = “Generate Drop Statement Only”

If you say “Include If NOT Exists” option as TRUE then it Generate the following template

IF EXISTS (SELECT * FROM ..

BEGIN

DROP ..

END

Missing in Console:

When you create, "Script Behavior” = “Generate Drop Statements Followed By Create Statement”

If you say “Include If NOT Exists” option as TRUE then it Generate the following template

IF EXISTS (SELECT * FROM ..

BEGIN

DROP ..

END

CREATE ..

Since the above option is missing (may be bug), first generate the drop script & append with Create generation Script with/without If Not Exist.

|||thanks|||

(This is a related question, so I'll borrow the thread)

Is there a way to save the scripting options for the script wizard or customize the default options?

For various reasons (as this thread points out) I use script generaiton options changed from the defualts as:

Include Descriptive Headers = FALSE
Include If NOT EXISTS = True
Script Drop = True
Script USE DATABASE = FALSE
Script Indexes = True

Can I change my Script Generation Wizard defaults so I don't have to select these options each time?

|||

michaelplevy wrote:

Is there a way to save the scripting options for the script wizard or customize the default options?

With SQL Server 2005 Service Pack 2, we've introduced the Tools -> Options... -> Scripting dialog so that you can change the behavior of how Management Studio generates scripts. These settings will be the default settings for the Generate Script Wizard.

Does this help?

Paul A. Mestemaker II

Program Manager

Microsoft SQL Server

http://blogs.msdn.com/sqlrem/

|||Paul, thanks for the tip about changing the script options.|||Hello,

Not all of the options available during scripting appear to be configurable from Tools > Options.
I'm running 9.00.3042.00. Is there a hotfix that remedies this? Or is the selective availability by design? I'm mainly interested in the "Script Drop" option.

Vivek

Generating script for existing sql jobs

we have a database and we are planning to deploy the database on another Box. I have database tables,stored procedures,views,user defined functions,triggers and some jobs on this database. So instead of restoring a back of this database we are planning to create the whole environment by using the sql scrtipts. So I know how to generate the sql script for all the objects(using generate sql script at database level) but how can I get the script for my Sql Jobs. How can I copy all the sql jobs onto the the new Box.

Thanks.In Enterprise Manager, if you right click on the job and select "all tasks" you will be able to generate a script for that job.

If you have many jobs to move, you could do a restore of the msdb database on your new server since the SQL Server Agent jobs are maintained in the msdb database.

Generating Multi Level nodes in Stored Procedures

Hi all,

What I am trying to do is generate a stored procedure that is desired
to output XML in this type of format

<Parent Device>
<Device>
<Device ID>1</DeviceID>
<ChildRegister>
<ChildRegisterID>22</ChildRegisterID>
</ChildRegister>
</Device>
<Device>
<Device ID>2</DeviceID>
<ChildRegister>
<ChildRegisterID>23</ChildRegisterID>
</ChildRegister>
</Device>
</Parent Device
The area of concern is the child register, the XML being generated
disregards the Device the ChildRegister belongs to and always places it
as elements of the last device.

<Parent Device>
<Device>
<Device ID>1</DeviceID>
</Device>
<Device>
<Device ID>2</DeviceID>
<ChildRegister>
<ChildRegisterID>23</ChildRegisterID>
</ChildRegister>
<ChildRegister>
<ChildRegisterID>22</ChildRegisterID>
</ChildRegister>
</Device>
</Parent Device
I am trying to produce XML like the first one I described and have yet
to discover a way of associating the ChildRegister with the parent
Device in XML. I am not sure if it is a limitation of SQL Server, or if
my implementation is incorrect. If anyone could post hints or
solutions, I would greatly appreciate it.
A shortened version of the stored procedure is below

Cheers :)
Alvin

SELECT
1AS TAG
,NULL AS PARENT
,NULL AS [Device!2!DeviceID!element]
,NULL AS [ChildRegister!3!RegisterID!element]

FROM udetails INNER JOIN
Detail ON udetails.ID = Detail.ID
WHERE (uDetails.JobID = @.ID)

UNION ALL

SELECT
2 AS TAG
,1 AS PARENT
,TempTable.DeviceIDAS [Device!2!DeviceID!element]
,NULL AS [ChildRegister!3!RegisterID!element]

>From #Temp as TempTable INNER JOIN
device ON TempTable.DeviceID = device.DeviceID

UNION ALL

SELECT
3 AS TAG
,2 AS PARENT
,NULL AS [Device!2!DeviceID!element]
,RegisterID AS [ChildRegister!3!RegisterID!element]

FROM #Temp t INNER JOIN
register ON t.DeviceID =
register.DeviceID

FOR XML EXPLICIT(teohster@.gmail.com) writes:
> I am trying to produce XML like the first one I described and have yet
> to discover a way of associating the ChildRegister with the parent
> Device in XML. I am not sure if it is a limitation of SQL Server, or if
> my implementation is incorrect. If anyone could post hints or
> solutions, I would greatly appreciate it.
> A shortened version of the stored procedure is below

For all problems like this, it is a good idea to post:

o CREATE TABLE statements of the tables inolved.
o INSERT statements with sample data.
o The desired output given the sample data.

You posted the last, but not the first two.

This permits people to post a tested solution to your query. In this
case, an aggrevating factor is that I am not extremely versed in XML,
so I would have to play around with the query.

It may be more effective to ask the real pros in
microsoft.public.sqlserver.xml though.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Generating GRANT EXECUTE Scripts

Hi All
I have a set of users and roles in a database permissions for various stored
procedures. I want to script these permissions but the default scripting
options don't give me what I want. Is there any way to script permissions
without having to write:
GRANT EXECUTE ON sproc TO user/role
for each stored proc for each user myself.
DaleUse sp_helptext to check out how MS coded the sp_helplogins, sp_helpsrvrole,
sp_helpsrvrolemember, sp_helpuser, sp_helprole, sp_helprolemember, and
sp_helprotect.
I'm sure you will want some variant combination of all of these.
Best of luck.
Sincerely,
Anthony Thomas
"Dale" <Dale@.discussions.microsoft.com> wrote in message
news:CFF9A6DA-98C4-4FCD-AC63-4BB67B522299@.microsoft.com...
Hi All
I have a set of users and roles in a database permissions for various stored
procedures. I want to script these permissions but the default scripting
options don't give me what I want. Is there any way to script permissions
without having to write:
GRANT EXECUTE ON sproc TO user/role
for each stored proc for each user myself.
Dale

Generate SQL Script for Stored Procedures

I want to script some stored procedures before I drop
them. When I right click on the master database, and
select generate sql script, I see 2 objects for the master
database, the helpbox procedures.
If I right click on the stored procedure itself, the
generate sql script is greyed out.
would appreciate help on this. Thanks.Hi,
Master database contains only the system stored procedures and those will
not be shown in "Generate SQL Script". Please try with your user database
and try generating the script.
Thanks
Hari
MCDBA
"Barb" <anonymous@.discussions.microsoft.com> wrote in message
news:12f2e01c41203$270271d0$a401280a@.phx
.gbl...
> I want to script some stored procedures before I drop
> them. When I right click on the master database, and
> select generate sql script, I see 2 objects for the master
> database, the helpbox procedures.
> If I right click on the stored procedure itself, the
> generate sql script is greyed out.
> would appreciate help on this. Thanks.|||As Hari pointed out, system stored procedures won't show up
in the Generate SQL Script dialog from Enterprise Manager.
If you are trying to script out system stored procedures,
you can script these individually using the object browser
in Query Analyzer. If you right click on the stored
procedure in the object browser, you get scripting options.
You can script the stored procedure to a file by selecting
Script Object to File as Create.
-Sue
On Wed, 24 Mar 2004 16:50:22 -0800, "Barb"
<anonymous@.discussions.microsoft.com> wrote:

>I want to script some stored procedures before I drop
>them. When I right click on the master database, and
>select generate sql script, I see 2 objects for the master
>database, the helpbox procedures.
>If I right click on the stored procedure itself, the
>generate sql script is greyed out.
>would appreciate help on this. Thanks.|||Thank You.

>--Original Message--
>As Hari pointed out, system stored procedures won't show
up
>in the Generate SQL Script dialog from Enterprise Manager.
>If you are trying to script out system stored procedures,
>you can script these individually using the object browser
>in Query Analyzer. If you right click on the stored
>procedure in the object browser, you get scripting
options.
>You can script the stored procedure to a file by selecting
>Script Object to File as Create.
>-Sue
>On Wed, 24 Mar 2004 16:50:22 -0800, "Barb"
><anonymous@.discussions.microsoft.com> wrote:
>
master
>.
>|||Thank You.

>--Original Message--
>Hi,
>Master database contains only the system stored
procedures and those will
>not be shown in "Generate SQL Script". Please try with
your user database
>and try generating the script.
>Thanks
>Hari
>MCDBA
>"Barb" <anonymous@.discussions.microsoft.com> wrote in
message
> news:12f2e01c41203$270271d0$a401280a@.phx
.gbl...
master
>
>.
>

Generate scripts only for stored procedures and views?

Hello, I have two environments, testing and developing, sometimes on developing environment I make chagnes to views or stored procedures, the first time I copied the whole database, but when the tester began to test the second cycle I made the same and he losed the changes, the tables wont change, but the views and sps may change, so I need an easy way to change those objects without having to copy all the database?

I tried but the generate scrips task but I didnt see an option to script only views and procedures.

What version of SQL Server are you using? I'm using SQL Server 2005 Express, and when I go through the script wizard to script objects, I see the "Choose Object Types you want to script." just fine.

In Object Explorer, right-click on your database -> Tasks -> Generate Scripts...

|||I am using sql 2005 developer edition and I dont see that option anywhere.|||what option don't you see where and at what point?|||Did you complete the wizard? I remember that option near the end of the wizard.|||You were right, it was in the last step. I really would think to put it in the first step. Its just my opinion but thanks.

Generate Scripts for existing records in a table

I can use Generate Scripts tool to get scripts for tables, procedures etc. But now I have records in tables which I want all "Insert into ..." queries from sqlserver. My co-worker said I have to manually type all these lines for my data.sql. Do I have to? I guess there must be a way...

Thanks,

LiliTry using DTS (Data Transformation services) which will allow you to recreate and / or rename the table and also lets you populate with original data.

The fastest way to recreate tables as you can recreate your entire database if you want to.

:p|||DTS is awsome :) but I need to deliver data.sql (of course with all other *.sql files) to the client so that they can install and drop database any time they mess it up :P|||It sounds like you want to give the client some base data. Could you not just BCP the data out into files and then create a command file that would truncate the tables and then BCP the data back in?

Or do you need to create the INSERT statements?
Example:INSERT myTbl (col1,col2,col3) VALUES ('xxx','aaa',123)|||Yes! base data. I need to insert statements. I just typed up some, but I'm wondering whether I can use a tool to make life easier :)

and guru, could you read my post about "Create My Own Log (for functionality) "? Thank you!

Lili|||Following sql statement will help you to generate INSERT STATEMENT for your base table

Table Name : BaseTable
fields
BName varchar(20)
dt smalldatetime
val int

select 'Insert into BaseTable VALUES (' + '''' + bname + '''' + ','
+ '''' + convert(varchar(10),dt,101) + '''' + ','
+ '''' + convert(varchar(10),val) + '''' + ')'
from basetable

handle NULL part by using ISNULL function

All the best|||Wow! Good idea. Silly me :p Thanks,

Lili|||You know sometimes I get carried away. I wrote a stored procedure that will generate all the INSERT statements for a given table. I've attached the proc, it worked for me on 2000.

Example on pubs
exec usp_CreateInsert discounts

OUTPUT:
INSERT discounts ( discounttype, stor_id, lowqty, highqty, discount)
VALUES ('Initial Customer',NULL,NULL,NULL,10.50)
INSERT discounts ( discounttype, stor_id, lowqty, highqty, discount)
VALUES ('Volume Discount',NULL,100,1000,6.70)
INSERT discounts ( discounttype, stor_id, lowqty, highqty, discount)
VALUES ('Customer Discount','8042',NULL,NULL,5.00)|||//@.@. where is the stored procedure?|||It must have stoppped me because the extension of the file was '.sql', so I had to change it to '.txt'|||wow, you are real sql guru //admire

thank you for your help and sharing :)

Have a nice weekend!

Lili

Generate scripts doesn''t take into account dependencies

I'm having a problem with the generate scripts procedure in Sql Server Express. My database has stored procedures which use views. When I generate the database create script it puts the stored procedures before the views, so that the create script fails when I try to execute it, because the stored procedure can't be compiled until the view is defined. Is there a fix for this or a way around the problem?

Jon Webb

I found a workaround / fix: 1) Make the objects I was having trouble with schema bound; 2) Check "generate dependent objects" in the generate scripts wizard.

Generate scripts doesn''t take into account dependencies

I'm having a problem with the generate scripts procedure in Sql Server Express. My database has stored procedures which use views. When I generate the database create script it puts the stored procedures before the views, so that the create script fails when I try to execute it, because the stored procedure can't be compiled until the view is defined. Is there a fix for this or a way around the problem?

Jon Webb

I found a workaround / fix: 1) Make the objects I was having trouble with schema bound; 2) Check "generate dependent objects" in the generate scripts wizard.

Generate custom primary key fields

Hi all,

I've recently dabbled into the world of Stored procedures and Triggers but am having some issues trying to implement some functionality.

What I want to accomplish is to either create a SP or Trigger (I'm still trying to figure out the prime differece between them) that takes values in, concatenates them, adds an increment and then saves it into the assigned column.

Say for example I have a company name, and a region
Name:'Traders Inc"
Region:KEN (Kenya).

What I want my sp to do is take the first four letters of the name, add a dash, concat the region and add an increment starting from 001 thereby giving me TRAD-KEN-001. Incase I put in another entry, it should do the same and start it at 001 as well. Only when the first two variables are the same should the number be incremented. I.e if we have another entry
Name: Tradine Jewelers",
Region: Kenya;

It should calculate and give me a value TRAD-KEN-002. This is what I want my sp to do.

I can experiment and get the concats etc to work but my confusion kicks in on how I integrate 'If' statements and how SQL actually manipulates vinputted values and where (and how) I get the value of the last increment.

Values will be passed in from my Business Logic Layer but I am totally lost after that i.e. Should I use a SP or Trigger, how do I get the values into one column, how do I integrate the IF statemnets to get the result that I am getting. The only part I understand is the Insert but thats about it.

Any help on flow, functions and commands would be wonderful with where the GO and EXECs should go in and how it will store variables temporarily.

Thanks in Advance.

I'd just like someone to set me off in the right direction.

Thanks.

|||

Here is one idea, perhaps still 'half baked', so take it as a seed, not a fully grown solution.

Column for Name
Column for Region
Column for Sequence

Column for Key Value (Concatenated fields); this column will have a UNIQUE INDEX -which will serve 'almost' as good as a Primary Key -but will allow the initial INSERT to succeed even with NULL values.

Use a Trigger (the difference between a Trigger and a Stored Procedure is that a Stored Procedure executes when called, whereas a Trigger automatically executes upon change to data in a table.)

Create an FOR INSERT Trigger. When a new row is inserted, search for the max(Sequence) WHERE MyTable.Name = inserted.Name AND MyTable.Region = Inserted.Region. Add one to that value, put it in the Sequence Column, and then concatenate the three columns together to make your key value. Store in the Key value column. (It may be necessary to use an INSTEAD OF Trigger rather than a FOR INSERT Trigger.)

I hope that this helps point you in a direction that will work for you.

|||Hi,

Assuming the table structure is like
create table table1 (AreaName varchar(100), Region varchar(100), CodeGenerated varchar(100))

You can use something like this or you can convert it into trigger.

CREATE PROCEDURE InsertData
@.AreaName VARCHAR(100),
@.Region VARCHAR(100)
AS
BEGIN
DECLARE @.CodeGenerated VARCHAR(100), @.Counter varchar(10)

SET @.CodeGenerated = substring(@.Areaname, 1, 4) + '-' + substring(@.Region,1,3) + '-'

SELECT @.Counter = ISNULL(MAX(REPLACE(CodeGenerated,@.CodeGenerated, '')),0)+1 FROM Table1
WHERE AreaName = @.AreaName AND Region = @.region

SET @.CodeGenerated = UPPER(@.CodeGenerated) + REPLICATE('0',3-len(@.Counter)) + convert(VARCHAR,@.Counter)

INSERT table1 VALUES(@.AreaName, @.Region, @.CodeGenerated)
END

-- Code to execute stored procedure
EXEC InsertData @.AreaName = 'Traders Inc', @.Region = 'KEN'

SELECT * FROM table1

--Neeraj--|||

Awesome. Thanks alot for your responses.

Neeraj, by looking at the code and my newbie databases status, you just saved me 1 or 2 days of trial and error with this one.Code works perfect. Thanks a million!

I need to get my hands on some good sql books. Any book recommendations or links which I can start off with?

|||Hi kundalani,

Good to know that your problem is solved.

As per forums guidelines you should close the topic if it is able to solve your problem :)

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=17861&SiteID=1