Can I generate Table Script in a Stored Procedure?

I have to load around 40 tables and I want to write one Stored Procedure that I pass a table name into. Data is coming from 3 different sources and to fill each of the 40 tables. I want to create a temp table that I can load first to make sure it succeeds first before I truncate and insert into the production table.

Is there a system procedure or something that will allow me to create a temp table from a table that alresdy exist in my database?

You can use SELECT INTO to create a new table based on a query like:

SELECT t.col1, t.col2, t.col3

INTO #t

FROM your_table AS t

Above query will create table with same structure as your_table without the defaults, constraints, indexes etc. Identity property on columns will be transferred though. You may also want to look at SSIS to automate your bulk loading process. You can also take a look at the link below for more pointers on how to improve the bulk load process:

http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/incbulkload.mspx

Note that with above query, you can create permanent table too. If you create a temporary table then you can only use BULK INSERT statement since the temporary table is connection scoped. And I am not sure if SSIS can perform SELECT...INTO and bulk load from the client side within context of single connection (You will have to ask in the SSIS forum).

|||

Good to know you can create Temps like this.

I might be able to work with this let me know what you think.

Problem:

I’m getting my data from an AS400 using

Insert Into @.TableName

Select *

from Opendatasource(“Connection Info”).AS400ServerName.AS400Library.@.TableName

The table name and column structure in the AS400 are the same as in my database but the column type is different i.e. A lot of the AS400 columns are Char when I need them to be Decimal. I need to create the temp table with the same structure as my table because the majority of the time my process fails is because the AS400 has Char data when it should be Decimal

My Solution:

Select Top 1 *

Into #t

From @.TableName

Truncate Table #t

--Insert temp data from sites

Insert Into #t

Select *

from Opendatasource(“Connection Info”).AS400ServerName.California.@.TableName

Insert Into #t

Select *

from Opendatasource(“Connection Info”).AS400ServerName.Nevada.@.TableName

Insert Into #t

Select *

from Opendatasource(“Connection Info”).AS400ServerName.Arizona.@.TableName

Truncate Table @.TableName

--Insert data to Production

Insert Into @.TableName

Select *

From #t

Can I generate a SQL CREATE script for a table that exists in the db automatical

I'm trying to figure out what the easiest/best way to transfer just one table from one sql server to another. Any help would be appreciated. Thanks!you can use the query analyzer, select database --> user tables --> select correct table --> right click --> script object to new window as -->> create. The query analyzer will generate a script that will generate your table.|||Thanks for the quick reply!
However, I can't seem to find the "user tables-->select correct table" option in the query analyzer?
Thanks again!|||At the left side is the object browser. If you cannot see, push the F8 button. You see at the top your server and below the databases in that server. From that point on it's like the Windows Explorer. When you see the user tables you can select one and right click on it for the scripting.|||Is this a SQL 2000 thing? I'm on SQL 7.0|||don't know, never used sql 7. Something else you can try: in the enterprise manager, select the table you want to script. Right click on it, select all tasks and then generate script. A popup will appear and if you want you can set some preferences, but just pushing OK is sufficient. Hopefully this is in sql 7|||You can access this through enterprise manager by right-clicking on the table and selecting All Tasks, Generate SQL Script.

Note that this does not copy the actual data. To do that you will need to use DTS.

blindman

Can I force printing?

I have a script or SP that takes a very long time to perform
multiple tasks, and after each one there is a PRINT statement
that shows the time and what was just accomplished, to help me
monitor what's happening and estimate how long it will take.
In a script, I can put a GO after each PRINT to cause the output
to appear immediately, but that's not possible inside an SP.
Instead, it seems the output goes to a buffer, and the buffer
is only output when a PRINT causes the buffer to become full.
Sometimes there is a long delay before the buffer fills. Is
there a way other than GO to cause immediate printing? (I
guess I could just use longer messages, and fill up the
buffer with every one, but that's not very elegant.)

Thanks,
Jim G
Countrywide Home LoansYou can use RAISERROR...WITH NOWAIT to cause messages to avoid buffering.
Messages will be displayed immediately when using a tool like Query
Analyzer:

RAISERROR ('my message', 0, 1) WITH NOWAIT

--
Hope this helps.

Dan Guzman
SQL Server MVP

<jim_geissman@.countrywide.com> wrote in message
news:1125531618.828313.53510@.z14g2000cwz.googlegro ups.com...
>I have a script or SP that takes a very long time to perform
> multiple tasks, and after each one there is a PRINT statement
> that shows the time and what was just accomplished, to help me
> monitor what's happening and estimate how long it will take.
> In a script, I can put a GO after each PRINT to cause the output
> to appear immediately, but that's not possible inside an SP.
> Instead, it seems the output goes to a buffer, and the buffer
> is only output when a PRINT causes the buffer to become full.
> Sometimes there is a long delay before the buffer fills. Is
> there a way other than GO to cause immediate printing? (I
> guess I could just use longer messages, and fill up the
> buffer with every one, but that's not very elegant.)
> Thanks,
> Jim G
> Countrywide Home Loans|||Thanks, Dan.

Jim Geissman

can I export a database from query analyzer?

Is there a command or script I can run from query analyzer to export a
database?
Its a long story but I can only get to the database through query analyzer
and there is one database I need to pull out to another machine.
Thanks,
You can do a couple of things:
1) Backup the database and then restore it on the other server. Refer to SQL Server Books Online for syntax and examples
2) If you have a copy of the database files (data + logs) then you can use sp_attach_db to attach the database files on another server.
3) If you don't have the database files but can take the database offline for sometime then you can take the database offline (one method will be to use sp_dboption) then
copy the files to the new server. Now you can take db online. connect to the new server using QA and use sp_attach_db
HTH,
Best Regards,
Uttam Parui
Microsoft Corporation
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.
Microsoft highly recommends that users with Internet access update their Microsoft software to better protect against viruses and security vulnerabilities. The easiest way
to do this is to visit the following websites: http://www.microsoft.com/protect
http://www.microsoft.com/security/guidance/default.mspx
|||Great I do have the database files and log. I will just attach those in
another instance.
Thanks for your help.
"Uttam Parui[MS]" wrote:

> You can do a couple of things:
> 1) Backup the database and then restore it on the other server. Refer to SQL Server Books Online for syntax and examples
> 2) If you have a copy of the database files (data + logs) then you can use sp_attach_db to attach the database files on another server.
> 3) If you don't have the database files but can take the database offline for sometime then you can take the database offline (one method will be to use sp_dboption) then
> copy the files to the new server. Now you can take db online. connect to the new server using QA and use sp_attach_db
> HTH,
> Best Regards,
> Uttam Parui
> Microsoft Corporation
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Are you secure? For information about the Strategic Technology Protection Program and to order your FREE Security Tool Kit, please visit
> http://www.microsoft.com/security.
> Microsoft highly recommends that users with Internet access update their Microsoft software to better protect against viruses and security vulnerabilities. The easiest way
> to do this is to visit the following websites: http://www.microsoft.com/protect
> http://www.microsoft.com/security/guidance/default.mspx
>
>
sql

Can I change a field/column width in a script ?

Can I change a field/column width in a script ? For example, I have a table
where field COL1 is a varchar(200). The table already has data in it. I
would like to increase the width of field COL1 to be varchar(300). Can I do
this in a script ? Thank you.Assuming no keys or constraints reference the column:
ALTER TABLE tablename
ALTER COLUMN Col1 VARCHAR(300)
You won't lose any data if you do this (but if you went the other way, you
could).
http://www.aspfaq.com/
(Reverse address to reply.)
"Fie Fie Niles" <fniles@.wincitesystems.com> wrote in message
news:##m9c8BdEHA.1644@.tk2msftngp13.phx.gbl...
> Can I change a field/column width in a script ? For example, I have a
table
> where field COL1 is a varchar(200). The table already has data in it. I
> would like to increase the width of field COL1 to be varchar(300). Can I
do
> this in a script ? Thank you.
>|||Sure. You can use alter table...alter column e.g.
alter table YourTable
alter column COL1 varchar(300)
-Sue
On Tue, 27 Jul 2004 16:17:50 -0500, "Fie Fie Niles"
<fniles@.wincitesystems.com> wrote:

>Can I change a field/column width in a script ? For example, I have a table
>where field COL1 is a varchar(200). The table already has data in it. I
>would like to increase the width of field COL1 to be varchar(300). Can I do
>this in a script ? Thank you.
>|||Thank you.
What did you mean by "but if you went the other way, you could lose data" ?
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:ewU7fACdEHA.3596@.tk2msftngp13.phx.gbl...
> Assuming no keys or constraints reference the column:
> ALTER TABLE tablename
> ALTER COLUMN Col1 VARCHAR(300)
> You won't lose any data if you do this (but if you went the other way, you
> could).
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Fie Fie Niles" <fniles@.wincitesystems.com> wrote in message
> news:##m9c8BdEHA.1644@.tk2msftngp13.phx.gbl...
> table
> do
>|||Well, if you have a varchar(300), and you change it to varchar(200), you
will lose some data in any column that had more than 200 characters...
http://www.aspfaq.com/
(Reverse address to reply.)
"Fie Fie Niles" <fniles@.wincitesystems.com> wrote in message
news:ekHjBXCdEHA.3020@.TK2MSFTNGP11.phx.gbl...
> Thank you.
> What did you mean by "but if you went the other way, you could lose data"
> ?|||Hi ,
I feel that Alter Table command will "FAIL" if we have a column with
varchar(300) and if few columns contains more than 200 characters
already in place, and if you change it to varchar(200).
In this case to alter the column to Varchar(200) we may need to update the
column to have less than = 200 characters
update table
set column = substring(column,1,200)
Remember that the above command will truncate the records which are holding
more than 200 charecters
After that you can alter the table to varchar(200)
alter table xx_tab alter column columnname varchar(200)
Thanks
Hari
MCDBA
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OyklB9DdEHA.3588@.TK2MSFTNGP11.phx.gbl...
> Well, if you have a varchar(300), and you change it to varchar(200), you
> will lose some data in any column that had more than 200 characters...
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Fie Fie Niles" <fniles@.wincitesystems.com> wrote in message
> news:ekHjBXCdEHA.3020@.TK2MSFTNGP11.phx.gbl...
data"[vbcol=seagreen]
>|||As Sue says Hari, it does work, it simply truncates the data longer than the
new column width
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:deidg0lj8hba2t076cm1upe5l2fg4l4ukk@.
4ax.com...
> Sure. You can use alter table...alter column e.g.
> alter table YourTable
> alter column COL1 varchar(300)
> -Sue
> On Tue, 27 Jul 2004 16:17:50 -0500, "Fie Fie Niles"
> <fniles@.wincitesystems.com> wrote:
>
table[vbcol=seagreen]
do[vbcol=seagreen]
>|||> In this case to alter the column to Varchar(200) we may need to update the
> column to have less than = 200 characters
Right, which means you lose data (or have to put it somewhere else).
A|||Thank you very much, all.
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OyklB9DdEHA.3588@.TK2MSFTNGP11.phx.gbl...
> Well, if you have a varchar(300), and you change it to varchar(200), you
> will lose some data in any column that had more than 200 characters...
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Fie Fie Niles" <fniles@.wincitesystems.com> wrote in message
> news:ekHjBXCdEHA.3020@.TK2MSFTNGP11.phx.gbl...
data"[vbcol=seagreen]
>sql

Can I change a field/column width in a script ?

Can I change a field/column width in a script ? For example, I have a table
where field COL1 is a varchar(200). The table already has data in it. I
would like to increase the width of field COL1 to be varchar(300). Can I do
this in a script ? Thank you.
Assuming no keys or constraints reference the column:
ALTER TABLE tablename
ALTER COLUMN Col1 VARCHAR(300)
You won't lose any data if you do this (but if you went the other way, you
could).
http://www.aspfaq.com/
(Reverse address to reply.)
"Fie Fie Niles" <fniles@.wincitesystems.com> wrote in message
news:##m9c8BdEHA.1644@.tk2msftngp13.phx.gbl...
> Can I change a field/column width in a script ? For example, I have a
table
> where field COL1 is a varchar(200). The table already has data in it. I
> would like to increase the width of field COL1 to be varchar(300). Can I
do
> this in a script ? Thank you.
>
|||Sure. You can use alter table...alter column e.g.
alter table YourTable
alter column COL1 varchar(300)
-Sue
On Tue, 27 Jul 2004 16:17:50 -0500, "Fie Fie Niles"
<fniles@.wincitesystems.com> wrote:

>Can I change a field/column width in a script ? For example, I have a table
>where field COL1 is a varchar(200). The table already has data in it. I
>would like to increase the width of field COL1 to be varchar(300). Can I do
>this in a script ? Thank you.
>
|||Thank you.
What did you mean by "but if you went the other way, you could lose data" ?
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:ewU7fACdEHA.3596@.tk2msftngp13.phx.gbl...
> Assuming no keys or constraints reference the column:
> ALTER TABLE tablename
> ALTER COLUMN Col1 VARCHAR(300)
> You won't lose any data if you do this (but if you went the other way, you
> could).
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Fie Fie Niles" <fniles@.wincitesystems.com> wrote in message
> news:##m9c8BdEHA.1644@.tk2msftngp13.phx.gbl...
> table
> do
>
|||If you alter the column to a smaller size, you could lose
data.
-Sue
On Tue, 27 Jul 2004 17:05:22 -0500, "Fie Fie Niles"
<fniles@.wincitesystems.com> wrote:

>Thank you.
>What did you mean by "but if you went the other way, you could lose data" ?
>
>"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
>news:ewU7fACdEHA.3596@.tk2msftngp13.phx.gbl...
>
|||Hi ,
I feel that Alter Table command will "FAIL" if we have a column with
varchar(300) and if few columns contains more than 200 characters
already in place, and if you change it to varchar(200).
In this case to alter the column to Varchar(200) we may need to update the
column to have less than = 200 characters
update table
set column = substring(column,1,200)
Remember that the above command will truncate the records which are holding
more than 200 charecters
After that you can alter the table to varchar(200)
alter table xx_tab alter column columnname varchar(200)
Thanks
Hari
MCDBA
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OyklB9DdEHA.3588@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
> Well, if you have a varchar(300), and you change it to varchar(200), you
> will lose some data in any column that had more than 200 characters...
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Fie Fie Niles" <fniles@.wincitesystems.com> wrote in message
> news:ekHjBXCdEHA.3020@.TK2MSFTNGP11.phx.gbl...
data"
>
|||As Sue says Hari, it does work, it simply truncates the data longer than the
new column width
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:deidg0lj8hba2t076cm1upe5l2fg4l4ukk@.4ax.com... [vbcol=seagreen]
> Sure. You can use alter table...alter column e.g.
> alter table YourTable
> alter column COL1 varchar(300)
> -Sue
> On Tue, 27 Jul 2004 16:17:50 -0500, "Fie Fie Niles"
> <fniles@.wincitesystems.com> wrote:
table[vbcol=seagreen]
do
>
|||> In this case to alter the column to Varchar(200) we may need to update the
> column to have less than = 200 characters
Right, which means you lose data (or have to put it somewhere else).
A
|||Thank you very much, all.
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OyklB9DdEHA.3588@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
> Well, if you have a varchar(300), and you change it to varchar(200), you
> will lose some data in any column that had more than 200 characters...
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Fie Fie Niles" <fniles@.wincitesystems.com> wrote in message
> news:ekHjBXCdEHA.3020@.TK2MSFTNGP11.phx.gbl...
data"
>

Can I change a field/column width in a script ?

Can I change a field/column width in a script ? For example, I have a table
where field COL1 is a varchar(200). The table already has data in it. I
would like to increase the width of field COL1 to be varchar(300). Can I do
this in a script ? Thank you.Assuming no keys or constraints reference the column:
ALTER TABLE tablename
ALTER COLUMN Col1 VARCHAR(300)
You won't lose any data if you do this (but if you went the other way, you
could).
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Fie Fie Niles" <fniles@.wincitesystems.com> wrote in message
news:##m9c8BdEHA.1644@.tk2msftngp13.phx.gbl...
> Can I change a field/column width in a script ? For example, I have a
table
> where field COL1 is a varchar(200). The table already has data in it. I
> would like to increase the width of field COL1 to be varchar(300). Can I
do
> this in a script ? Thank you.
>|||Sure. You can use alter table...alter column e.g.
alter table YourTable
alter column COL1 varchar(300)
-Sue
On Tue, 27 Jul 2004 16:17:50 -0500, "Fie Fie Niles"
<fniles@.wincitesystems.com> wrote:
>Can I change a field/column width in a script ? For example, I have a table
>where field COL1 is a varchar(200). The table already has data in it. I
>would like to increase the width of field COL1 to be varchar(300). Can I do
>this in a script ? Thank you.
>|||Thank you.
What did you mean by "but if you went the other way, you could lose data" ?
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:ewU7fACdEHA.3596@.tk2msftngp13.phx.gbl...
> Assuming no keys or constraints reference the column:
> ALTER TABLE tablename
> ALTER COLUMN Col1 VARCHAR(300)
> You won't lose any data if you do this (but if you went the other way, you
> could).
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Fie Fie Niles" <fniles@.wincitesystems.com> wrote in message
> news:##m9c8BdEHA.1644@.tk2msftngp13.phx.gbl...
> > Can I change a field/column width in a script ? For example, I have a
> table
> > where field COL1 is a varchar(200). The table already has data in it. I
> > would like to increase the width of field COL1 to be varchar(300). Can I
> do
> > this in a script ? Thank you.
> >
> >
>|||If you alter the column to a smaller size, you could lose
data.
-Sue
On Tue, 27 Jul 2004 17:05:22 -0500, "Fie Fie Niles"
<fniles@.wincitesystems.com> wrote:
>Thank you.
>What did you mean by "but if you went the other way, you could lose data" ?
>
>"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
>news:ewU7fACdEHA.3596@.tk2msftngp13.phx.gbl...
>> Assuming no keys or constraints reference the column:
>> ALTER TABLE tablename
>> ALTER COLUMN Col1 VARCHAR(300)
>> You won't lose any data if you do this (but if you went the other way, you
>> could).
>> --
>> http://www.aspfaq.com/
>> (Reverse address to reply.)
>>
>>
>> "Fie Fie Niles" <fniles@.wincitesystems.com> wrote in message
>> news:##m9c8BdEHA.1644@.tk2msftngp13.phx.gbl...
>> > Can I change a field/column width in a script ? For example, I have a
>> table
>> > where field COL1 is a varchar(200). The table already has data in it. I
>> > would like to increase the width of field COL1 to be varchar(300). Can I
>> do
>> > this in a script ? Thank you.
>> >
>> >
>>
>|||Well, if you have a varchar(300), and you change it to varchar(200), you
will lose some data in any column that had more than 200 characters...
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Fie Fie Niles" <fniles@.wincitesystems.com> wrote in message
news:ekHjBXCdEHA.3020@.TK2MSFTNGP11.phx.gbl...
> Thank you.
> What did you mean by "but if you went the other way, you could lose data"
> ?|||Hi ,
I feel that Alter Table command will "FAIL" if we have a column with
varchar(300) and if few columns contains more than 200 characters
already in place, and if you change it to varchar(200).
In this case to alter the column to Varchar(200) we may need to update the
column to have less than = 200 characters
update table
set column = substring(column,1,200)
Remember that the above command will truncate the records which are holding
more than 200 charecters
After that you can alter the table to varchar(200)
alter table xx_tab alter column columnname varchar(200)
Thanks
Hari
MCDBA
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OyklB9DdEHA.3588@.TK2MSFTNGP11.phx.gbl...
> Well, if you have a varchar(300), and you change it to varchar(200), you
> will lose some data in any column that had more than 200 characters...
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Fie Fie Niles" <fniles@.wincitesystems.com> wrote in message
> news:ekHjBXCdEHA.3020@.TK2MSFTNGP11.phx.gbl...
> > Thank you.
> > What did you mean by "but if you went the other way, you could lose
data"
> > ?
>|||As Sue says Hari, it does work, it simply truncates the data longer than the
new column width
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:deidg0lj8hba2t076cm1upe5l2fg4l4ukk@.4ax.com...
> Sure. You can use alter table...alter column e.g.
> alter table YourTable
> alter column COL1 varchar(300)
> -Sue
> On Tue, 27 Jul 2004 16:17:50 -0500, "Fie Fie Niles"
> <fniles@.wincitesystems.com> wrote:
> >Can I change a field/column width in a script ? For example, I have a
table
> >where field COL1 is a varchar(200). The table already has data in it. I
> >would like to increase the width of field COL1 to be varchar(300). Can I
do
> >this in a script ? Thank you.
> >
>|||> In this case to alter the column to Varchar(200) we may need to update the
> column to have less than = 200 characters
Right, which means you lose data (or have to put it somewhere else).
A|||Thank you very much, all.
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OyklB9DdEHA.3588@.TK2MSFTNGP11.phx.gbl...
> Well, if you have a varchar(300), and you change it to varchar(200), you
> will lose some data in any column that had more than 200 characters...
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Fie Fie Niles" <fniles@.wincitesystems.com> wrote in message
> news:ekHjBXCdEHA.3020@.TK2MSFTNGP11.phx.gbl...
> > Thank you.
> > What did you mean by "but if you went the other way, you could lose
data"
> > ?
>

Can data be exported into a SQL script

Hi;

Can a table....with all of its data be exported into a SQL script
that will restor the table with all of its data?

Thanks in advance

Stevehttp://vyaskn.tripod.com/code.htm#inserts

--
David Portas
----
Please reply only to the newsgroup
--

"Steve" <stevesusenet@.yahoo.com> wrote in message
news:6f8cb8c9.0310310706.6e19c360@.posting.google.c om...
> Hi;
> Can a table....with all of its data be exported into a SQL script
> that will restor the table with all of its data?
> Thanks in advance
> Steve

Can Alter (current) Database?

I have a *.sql script that creates database tables, and I need to modify the database to enable the service broker. In addition, the actual name of the database is not known in advance - it is set per instance of the application.

I know I need to do:

ALTER DATABASE dbname SET ENABLE_BROKER

But I must avoid including the name of the database in the script. I did wonder if this would work:

DECLARE @.DB varchar(50)

SELECT @.DB = DB_NAME()

ALTER DATABASE @.DB SET ENABLE_BROKER

But I just get a syntax error. Presumably this also rules out setting the database name as a parameter to the script (SqlParameter stuff)

The only option I can think of is dynamically creating the statement, either in T-SQL or in the calling .NET environment.

Any thoughts?

Ruth

Hi,

I guess you have to create a dynamic statement to make it work, something like:

DECLARE @.DB varchar(50)

SELECT @.DB = DB_NAME()

DECLARE @.SQLString VARCHAR(200)
SET @.SQLString = ' ALTER DATABASE ' + @.DB + 'SET ENABLE_BROKER'
EXEC(@.SqlString)

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

|||

Thanks, that looks good. You can of course replace @.DB with DB_NAME() in the SET.

Ruth

|||

Dynamic SQL is the only solution I know of. Make sure you protect yourself against SQL injection problems (e.g. use QUOTENAME on the database name). Also, ALTER DATABASE requires exclusive lock on the database, see http://blogs.msdn.com/remusrusanu/archive/2006/01/30/519685.aspx

HTH,
~ Remus

|||Remus,

I assumed that the value returned from DB_NAME() would be acceptable in SQL. Probably a bad assumption, really!

So, I did have:
SET @.AlterStmt = 'ALTER DATABASE ' + DB_NAME() + ' SET ENABLE_BROKER'

but I should really have this?
SET @.AlterStmt = 'ALTER DATABASE ' + QUOTENAME(DB_NAME()) + ' SET ENABLE_BROKER'

Thinking about injection, what is the best method to use when creating SQL on the fly. I don't want to use SqlParameters for everything as they obfuscate the code significantly. Is there an System.Data.SqlClient equivalent of QUOTENAME() ?

Ruth
|||

DB_NAME() is fine, it doesn't need to be passed into QUOTENAME. When I formulated the reply, Jens' post simply wasn't there and I didn't see it and I assumed the database name comes in as an argument.

You should worry about SQL Injection if the database name comes from an external (potentialy untrusted) source like a web form text field.

There is no equivalent to QUOTENAME, I usually use the simplest String.Replace method, like this:

string quotedDbName = "[" + dbnameVariable.Replace("]","]]") + "]";

HTH,
~ Remus

Can a script prompt for a value to be inputed?

Hello all,
Is there any way to get a script to prompt for a value (input box or
something else) or to use a value from a variable using SQLCMD?
I have these SELECT scripts that are essentially Crosstab/Pivot queries
where each column is a month of data and is created by a CASE statement.
When updates are done, the CASE statements for the new months need to be
created.
We want to automate this process for a client so they can have one of their
non-DBA employees do the work.
Now I have come up with some Dynamic SQL that will create the same script
and run it based on just a start date and an end date. Start date will be
fixed, the end date will change from update to update. Using the Dynamic
SQL script is alot easier--all we need to do is change one single end date,
rather than adding several CASE statements to the non-Dynamic SQL script.
What I would like to do is create a Dynamic SQL script that will prompt for
an end date to be entered. Now I have figured out how to get SQLCMD to run
a script file and save results to a file, so if I could get the Dynamic SQL
script to accept a variable from SQLCMD to set the end date, that would work
too.
Thanks for any help anyone can provide,
Conan KellyEnd users, dynamic crosstab queries...have you heard of Rac? :)
www.rac4sql.net
We did the dirty work, you take the credit.
www.beyondsql.blogspot.com
Putting logic back in application development|||There's no prompting in the SQL Server tools. But SQLCMD accepts a variable (though the -v switch
and $(varname) literal inside the script file).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Conan Kelly" <CTBarbarinNOSPAM@.msnNOSPAM.comNOSPAM> wrote in message
news:a21Ei.500477$p47.262287@.bgtnsc04-news.ops.worldnet.att.net...
> Hello all,
> Is there any way to get a script to prompt for a value (input box or something else) or to use a
> value from a variable using SQLCMD?
> I have these SELECT scripts that are essentially Crosstab/Pivot queries where each column is a
> month of data and is created by a CASE statement. When updates are done, the CASE statements for
> the new months need to be created.
> We want to automate this process for a client so they can have one of their non-DBA employees do
> the work.
> Now I have come up with some Dynamic SQL that will create the same script and run it based on just
> a start date and an end date. Start date will be fixed, the end date will change from update to
> update. Using the Dynamic SQL script is alot easier--all we need to do is change one single end
> date, rather than adding several CASE statements to the non-Dynamic SQL script.
> What I would like to do is create a Dynamic SQL script that will prompt for an end date to be
> entered. Now I have figured out how to get SQLCMD to run a script file and save results to a
> file, so if I could get the Dynamic SQL script to accept a variable from SQLCMD to set the end
> date, that would work too.
> Thanks for any help anyone can provide,
> Conan Kelly
>|||Tibor,
Thank you for your help.
That works EXACTLY the way I want it to.
Thanks again,
Conan
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23j$uliR8HHA.1168@.TK2MSFTNGP02.phx.gbl...
> There's no prompting in the SQL Server tools. But SQLCMD accepts a
> variable (though the -v switch and $(varname) literal inside the script
> file).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Conan Kelly" <CTBarbarinNOSPAM@.msnNOSPAM.comNOSPAM> wrote in message
> news:a21Ei.500477$p47.262287@.bgtnsc04-news.ops.worldnet.att.net...
>> Hello all,
>> Is there any way to get a script to prompt for a value (input box or
>> something else) or to use a value from a variable using SQLCMD?
>> I have these SELECT scripts that are essentially Crosstab/Pivot queries
>> where each column is a month of data and is created by a CASE statement.
>> When updates are done, the CASE statements for the new months need to be
>> created.
>> We want to automate this process for a client so they can have one of
>> their non-DBA employees do the work.
>> Now I have come up with some Dynamic SQL that will create the same script
>> and run it based on just a start date and an end date. Start date will
>> be fixed, the end date will change from update to update. Using the
>> Dynamic SQL script is alot easier--all we need to do is change one single
>> end date, rather than adding several CASE statements to the non-Dynamic
>> SQL script.
>> What I would like to do is create a Dynamic SQL script that will prompt
>> for an end date to be entered. Now I have figured out how to get SQLCMD
>> to run a script file and save results to a file, so if I could get the
>> Dynamic SQL script to accept a variable from SQLCMD to set the end date,
>> that would work too.
>> Thanks for any help anyone can provide,
>> Conan Kelly
>

Can a non-admin user generate a create database script?

I am trying to generate the create database script using SMO. The issue is the user account which connects to SQL server is not SA, SMO fails to generate the "Create Database" script with error User ZZZ\User1 does not have permission to run DBCC SHOWFILESTATS.

Is it possible to generate a "Create database" script using SMO when you are connecting to the SQL server using NON-ADMIN account?

The user login is a part of db_Datareader role on the database. I tried following grant permission to the concerned account at server level

Grant view any database, grant view any definition and grant view server state. Still it fails.

Any idea?

Grant dbo permissions.

Adamus

|||

Thanks Adamus for your reply.

I am sorry, I didn't understand what I need to do? Do I need to make the user as DBO?

The user account used for creating script will NOT get any permissions like DBO or SA. In SQL 2000, if the user is part of db_datareader dbrole then it can generate the "Create database" script without any problem.

|||

Adamus, just to test things, i made the user DBO of the database. We were able to generate "Create database" script after that.

But in our scenario we will not be able to make the user as DBO just to generate "Create database" script. Is there any other way to generate a "Create database" script using non-admin user.

|||Try with granting db_ddladmin privilege for that user.|||

Typically, you'd create a generic dbo account and validate in code. If the user is going to need to create databases, they should be an admin...again typically.

Adamus

|||

Let me explain the scenario in detail. I think, i was not clear, sorry about that.

THe user is trying to generate a create database (followed by create object) script from a server where he has limited access, as the source server is production server and also owned by other team. Once the script is created, the database and object will be created on different server where the user has SA permissions. After granting "any definition" permission the user can connect to the first server and now use sp_helpdb to see the database files and create script for all the objects in the database. Only while creating the script for "Create database"it fails.

|||

If the production server is owned by the other team, there is no magic or clever trickery that will enable the user to create databases on it. You have to understand that creating databases is an activity reserved for DBA's not end users. It was designed this way for many good reasons. You don't want to hear, "Oops I forgot to use a transaction!"

If the DBA creates a generic SQL Authentication account and your developers write a script that the end user can't change, this is how it is normally handled.

The end user is forced to use an application instead of a query analyzer and is confined to the functionality of the application.

I realize this isn't the answer you were hoping to get, but it is the reality.

Adamus

|||

I am NOT trying to create the database or for that matter any object on the production server which is owned by other team. I just want to script the database (including create database script) from that server.

The script will be used for creating database and objects on another server where we will have proper permission.

I can do this very well in SQL 2000, if the user is part of DB_DATAREADER db role. In SQL 2005, "GRANT VIEW ANY" permission will let you generate the script for all the objects in the database but not just the "Create database" script. My question is Can a non-admin generate the script (Just generate the script).

|||

Thanks Satya, I missed this reply.

I tried ddl_admin also, no success. SMO runs dbcc showfilestats to generate the "Create database" script and fails at that point, saying not enough permissions to execute dbcc. I was hoping to get some workaround like DBCC INPUTBUFFER can be executed by a non-admin user if that user has "VIEW SERVER STATE" permission.

I can view the database files using sp_helpdb and just cannot generate the "Create database" script. Not able to understand, why?

|||

SQL Server 2005 incorporated many security enhancements to prevent users from having access to information they shouldn't have. If the user in question could generate a script to create a database from an existing database (without the privileges to do it on that server), the user then has access to the underlying file system locations for the database files, and through clever use of SQL Injection techniques, can then compromise the security of your existing server.

I'm not suggesting your users would do this, I'm suggesting that with the permissions you desire that it's possible either in your shop or someone elses, and that's one of the things that Microsoft is attempting to prevent.

Hopefully that makes sense.

|||

Allen, I would respectfully disagree with you. The user who is a member of db_datareader role can see the same result by executing sp_helpdb (I granted grant view any). Also with that permission the user can see the exact information from different views (sys.MasterFiles, sys.Filegroups) from sys schema. SMO is only failing because it is using dbcc command while generating the "Create database" script. Why does SMO uses DBCC command to generate a "create database" script when the same information is available in different views? This is a bug and NOT a functionality.

Can a non-admin user generate "Create database" script using SMO?

I posted this thread in security also. But cross posting here with hope of getting a quick answer.

I am trying to generate the create database script using SMO. The issue is the user account which connects to SQL server is not SA, SMO fails to generate the "Create Database" script with error User ZZZ\User1 does not have permission to run DBCC SHOWFILESTATS.

Is it possible to generate a "Create database" script using SMO when you are connecting to the SQL server using NON-ADMIN account?

The user login is a part of db_Datareader role on the database. I tried following grant permission to the concerned account at server level

Grant view any database, grant view any definition and grant view server state. Still it fails.

Any idea?

One thing you can try is using the Server.ConnectionContext.ConnectionString property to specify the connection string the way we do it in applications and give the sa user id and password.

Other was is to grant dbwriter along with modify schema permission to the user.

|||Thanks Mitesh for your help. The user who connect to the server will be using it ONLY for generating the script. He is not going to create the database on the same server. Once the script is generated, the database will be created using the earlier generated script on other server, where the user has proper permission. There is no way user can be made SA, ONLY for generating the script (and that too only for CREATE DATABASE script). There is no chance of getting sa password on the first server from where user is trying to generate the script, as it is a production server.

Can a Framework 2.0 compiled DPE run from SRS 2000?

Is it possible to run a framework 2.0 compiled data processing extension from
SRS 2000? It looks like the ReportServer script mapping is to framework 1.1.
We are not ready to upgrade to SRS2005, but would like to build our DPE with
framework 2.0.
Is this possible, and if so, what issues may there be?
Thanks, DavidI have never tried this, but my guess is that you will run into some
.NET framework versioning problems. By definition, the framework will
not let a .NET 1.1 assembly make a call to an assembly compiled with a
version higher than 1.1. However, a .NET 2.0 assembly may call a 1.1
(or prior) assembly.
In other words, .NET is backwards compatible, but not "forwards
compatible." So, since the .NET portion of SRS 2000 is running on 1.1,
you probably cannot reference a 2.0 assembly.
I hope that helps. Like I said, I've never tried, but my gut reaction
is, no, it won't work.
-Jason
David Swanson wrote:
> Is it possible to run a framework 2.0 compiled data processing extension from
> SRS 2000? It looks like the ReportServer script mapping is to framework 1.1.
> We are not ready to upgrade to SRS2005, but would like to build our DPE with
> framework 2.0.
> Is this possible, and if so, what issues may there be?
> Thanks, David|||Thanks for Jason's input,
Hi David,
As for the SSRS 2005 extension components running in SSRS 2000 question,
I'm afraid this is not supported due to the limitation of .net framework's
versioning policy. Generally, .net framework only provide backward
compatibility support. Therefore, only .net framework 1.x assemblies can
not loaded and executed in .net framework 2.0 runtime(clr). On the
contrary, .net framework 1.x can still be loaded by .net framework 2.0
runtime (for backward compaitbility), however, there is still possible
issues if the used 1.x classes have any break changes in .net 2.0 classes.
BTW, as for your DP extension, have you already developed it under .net
framework 2.0? If not, I suggest you still develop it through .net
framework 1.1 so as to make it loadable in .net framework 1.x runtime(SSRS
2000).
Please feel free to post here if you have any other concerns or ideas on
this.
Regards,
Steven Cheng
Microsoft MSDN Online Support Lead
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||Hi David,
How are you doing on this issue, have you got any progress or made the
decision on this issue? If there is still anything we can help, please
feel free to post here.
Regards,
Steven Cheng
Microsoft MSDN Online Support Lead
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)

calling vbscript from SP

I am trying to call a VB script file with the following command,

xp_cmdshell 'cscript c:\file.vbs'

But its giving the following output -

Microsoft (R) Windows Script Host Version 5.1 for Windows
Copyright (C) Microsoft Corporation 1996-1999. All rights reserved.
NULL
Input Error: Can not find script file "c:\file.vbs".
NULL
----
If i changed the command to

xp_cmdshell 'cscript c:file.vbs'

it gives the folowing error -

Input Error: Can not find script file "C:\WINNT\system32\file.vbs".

Actually my main intention is to call a url from the sql server where '
i can pass some value. Plz let me know if its the right step and whts
going wrong.

Thanks & regards,

-dkoushikHow about using XML - http://www.awprofessional.com/articles/article.asp?p=102307&seqNum=9 and another possibility is to directly access the ODBC lowlevel APIs directly. I've no exp.using languages like Visual Basic. You may have some luck accessing the stored procedures
using SQL Namespace APIs & MSEQ.|||Hi satya,
Thanks for your reply. But I guess u have misunderstood my question.

My Actual intention is to call web url from the SqlServe DB.
I have done the same in oracle. I have used Java stored procedure
where I have called a web url and the java sp is get called from a
Oracle function. I am sure there is nothing called java stored proc in Sql server.

So How can I achive the same result in Sql Server.

I was just trying some thing using xp_cmdshell (a system SP)
which can call any VB script which in turn will call the web url.

Plz help if you have any idea regarding this.

Thanks & Regards,

-- dkoushik


Originally posted by Satya
How about using XML - http://www.awprofessional.com/articles/article.asp?p=102307&seqNum=9 and another possibility is to directly access the ODBC lowlevel APIs directly. I've no exp.using languages like Visual Basic. You may have some luck accessing the stored procedures
using SQL Namespace APIs & MSEQ.|||If you are using xp_cmdshell, you've got 90% of the battle won. xp_cmdshell can launch any file that the NT Login used by MS-SQL can see, using whatever tools are available on the machine that is running SQL Server.

If you can log into the machine that runs your SQL Server, using the same login credentials that SQL Server uses, then you will have almost exactly the same conditions to test with that the xp_cmdshell will run under. Find a command string you can type to get something to happend, and you can then issue the exact same command using xp_cmdshell.

-PatP|||Hi Pat,

Thanks for the reply.
What I understood from your response is ,

I have to execute the xp_cmdshell in the database server
query analyzer. I have done the same thing but nothing is happening.

But this time its not giving any error. But the result its showing as NULL.

Do have any idea whats happening.

please help.

Calling vb script from SQL job

I am trying to execute a visual basic script using a SQL job. I can schedul
e
this as a scheduled task and then use a separate SQL job to compelte the
formatting tasks, but prefer to keep all steps consolidated so it is easier
to trouble shoot.
I tried using xp_cmdshell 'job.vbs', no_output
but it gives the error that this is not a recognized internal command.
Any help would be greatly appreciated.>I am trying to execute a visual basic script using a SQL job. I can
>schedule
> this as a scheduled task and then use a separate SQL job to compelte the
> formatting tasks,
Why don't you have the VBScript task call a stored procedure?
A|||Hello Derekman:
You wrote on Mon, 26 Jun 2006 12:12:03 -0700:
D> I am trying to execute a visual basic script using a SQL job. I can
D> schedule this as a scheduled task and then use a separate SQL job to
D> compelte the formatting tasks, but prefer to keep all steps consolidated
D> so it is easier to trouble shoot.
D> I tried using xp_cmdshell 'job.vbs', no_output
D> but it gives the error that this is not a recognized internal command.
You can run only an executable; sql server won't look into the registry to
see what is the application handling file extension. In Explorer, this is
most likely specified as
WScript.exe "%1" %*
That's how it should be in sql job as well.
Vadim Rapp