How to Execute a SQL (.sql) script from within VB?

Hello,

I am trying to figure out the best way to make VB6 execute an auto-generated .SQL file from SQL Server 2000.

SQL Server 2000 has created a script (about 1000 lines long), creating views and SPs, and I need to execute said script from a VB client application. Similar to the effect of pasting it into the Query Analyser, only automated.

Anyone have a suggestion (besides reformatting evey like and hard-coding it?)

Thanks a ton!read it into a string, and execute it as normal.|||You can read the SQL into a string and give it to a SqlCommand class for ExecuteNonQuery.

One tip: the "GO" batch delimiter is not T-SQL, it is a signal to Query Analyzer. Many script generation tools will place the "GO" delimiter into script files to tell QA where to split up the commands. You'll need to parse the script and execute individual batches as you encounter GO batch delimiters.sql

How to execute a "batch file" using SQL Server 2005?

Hi all,

As the topic describes, I'd like to run a batch file using sql server.
How do i do it?

Thanks in advanceOne method is with a SQL Server agent CmdExec job step. Another is via
xp_cmdshell. If you choose to use xp_cmdshell, be sure you fully understand
the security implications and don't grant direct execute permissions on the
proc.

--
Happy Holidays

Dan Guzman
SQL Server MVP

"coosa" <coosa76@.gmail.com> wrote in message
news:1135346052.206276.14220@.o13g2000cwo.googlegro ups.com...
> Hi all,
> As the topic describes, I'd like to run a batch file using sql server.
> How do i do it?
> Thanks in advance

How to enter this command line sql statement...??

:confused: I am trying to use a program called ODBCVIEW to query a database from the command line, and write it to text file. The instructions look easy, But I am illiterate to translating command line syntax.
I am not sure if I use the ">" , "[", etc... or if I leave them out. Can some one show me exactly how the finished code is supposed to look (in laymans)...

The program I am using to query the DB is called ODBCVIEW. Here is a link to the page with the syntax(located at the bottom) - http://www.slik.co.nz/HTML_help/odbc_view.htm

...and I have also pasted it below:

-COPIED FROM WEBSITE:
ODBCView also supports a non-interactive command line mode to execute a query and save the results to a text file. The syntax is as follows:

ODBCView.exe <DSN=DataSourceName;UID=User;PWD=Password> [<SQLScript.sql>] [<OutputFile.htm|csv|txt>]

Where:

DataSource
The desired datasource name.

UID
An optional user name to logon to the database

PWD
An optional user password

SQLScript
The query to execute.

OutputFile
The path to the output file. The files extension determines the format. Use htm or html to HTML, csv for CSV.If you are only trying to query a database and write the results to a file, better try WinSQL (http://www.download.com/3001-10255_4-10213451.html). ;)|||:D Just what I was looking for!

I'm installing it now - Does it have a 'command-line' mode?

How to encrypt database schema(.mdf) of file

Hi,
I have developed an application in which SQL Server 2005 is used as backend.
Once i deploy the application on client machine, others will be able to
access the database schema. How to protect my database file (database schema
)
from others viewing it ?
Thanks in advance,
Gautham.You can't stop them from seeing it if they have sa rights. The only way to
protect yourself legally is with proper copywrites and such.
Andrew J. Kelly SQL MVP
"Gautham" <bv_gautham@.yahoo.com> wrote in message
news:43132353-FC67-4DCB-A9F5-B84E377CF1CE@.microsoft.com...
> Hi,
> I have developed an application in which SQL Server 2005 is used as
> backend.
> Once i deploy the application on client machine, others will be able to
> access the database schema. How to protect my database file (database
> schema)
> from others viewing it ?
> Thanks in advance,
> Gautham.|||Hi,
please read following article by Mr. Vyas its a very good article for sql
server security best practice
http://vyaskn.tripod.com/sql_server...t_practices.htm
HTH
Regards
--
Andy Davis
Activecrypt Team
---SQL Server Encryption Software
http://www.activecrypt.com
"Gautham" wrote:

> Hi,
> I have developed an application in which SQL Server 2005 is used as backen
d.
> Once i deploy the application on client machine, others will be able to
> access the database schema. How to protect my database file (database sche
ma)
> from others viewing it ?
> Thanks in advance,
> Gautham.

How to Encrypt a Backup File

Hello, I would like to password-protect or encrypt the .bak file for 3
selected databases on a MS SQL 2000 server. The DBs are all set for Full
recovery mode and the nighly backups run OK now using a DB Maintenance Plan.
How can I encrypt only the backup files without encrypting either the DB or
the server? Thanks, PanchoPancho,
You can use the PASSWORD argument of the BACKUP statment. For encryption of
the backup file, you can use one of many available file encryption
utilities.
HTH
Jerry
PS - Litespeed will encrypt and compress your backups if needed.
"Pancho" <Pancho@.discussions.microsoft.com> wrote in message
news:D6078784-DF5B-4B79-9C76-A26723EB0930@.microsoft.com...
> Hello, I would like to password-protect or encrypt the .bak file for 3
> selected databases on a MS SQL 2000 server. The DBs are all set for Full
> recovery mode and the nighly backups run OK now using a DB Maintenance
> Plan.
> How can I encrypt only the backup files without encrypting either the DB
> or
> the server? Thanks, Pancho|||Note that the password features on backups are now obsolete. According to
BOL (SQL Server 2005) the "protection is weak" and intended to "prevent an
incorrect restore". "It does not prevent the reading of the backup data".
Ben Nevarez, MCDBA, OCP
Database Administrator
"Jerry Spivey" wrote:

> Pancho,
> You can use the PASSWORD argument of the BACKUP statment. For encryption
of
> the backup file, you can use one of many available file encryption
> utilities.
> HTH
> Jerry
> PS - Litespeed will encrypt and compress your backups if needed.
> "Pancho" <Pancho@.discussions.microsoft.com> wrote in message
> news:D6078784-DF5B-4B79-9C76-A26723EB0930@.microsoft.com...
>
>|||place backup to encrypted folder
--
Aleksandar Grbic
MCDBA
"Pancho" wrote:

> Hello, I would like to password-protect or encrypt the .bak file for 3
> selected databases on a MS SQL 2000 server. The DBs are all set for Full
> recovery mode and the nighly backups run OK now using a DB Maintenance Pla
n.
> How can I encrypt only the backup files without encrypting either the DB o
r
> the server? Thanks, Pancho|||Thank you Jerry, Ben and Aleksandar. These were all helpful ideas and I
posted a helpful rating to each.
Best Regards, Pancho
"Aleksandar Grbic" wrote:
[vbcol=seagreen]
> place backup to encrypted folder
> --
> Aleksandar Grbic
> MCDBA
>
> "Pancho" wrote:
>

How to Encrypt a Backup File

Hello, I would like to password-protect or encrypt the .bak file for 3
selected databases on a MS SQL 2000 server. The DBs are all set for Full
recovery mode and the nighly backups run OK now using a DB Maintenance Plan.
How can I encrypt only the backup files without encrypting either the DB or
the server? Thanks, PanchoPancho,
You can use the PASSWORD argument of the BACKUP statment. For encryption of
the backup file, you can use one of many available file encryption
utilities.
HTH
Jerry
PS - Litespeed will encrypt and compress your backups if needed.
"Pancho" <Pancho@.discussions.microsoft.com> wrote in message
news:D6078784-DF5B-4B79-9C76-A26723EB0930@.microsoft.com...
> Hello, I would like to password-protect or encrypt the .bak file for 3
> selected databases on a MS SQL 2000 server. The DBs are all set for Full
> recovery mode and the nighly backups run OK now using a DB Maintenance
> Plan.
> How can I encrypt only the backup files without encrypting either the DB
> or
> the server? Thanks, Pancho|||Note that the password features on backups are now obsolete. According to
BOL (SQL Server 2005) the "protection is weak" and intended to "prevent an
incorrect restore". "It does not prevent the reading of the backup data".
Ben Nevarez, MCDBA, OCP
Database Administrator
"Jerry Spivey" wrote:
> Pancho,
> You can use the PASSWORD argument of the BACKUP statment. For encryption of
> the backup file, you can use one of many available file encryption
> utilities.
> HTH
> Jerry
> PS - Litespeed will encrypt and compress your backups if needed.
> "Pancho" <Pancho@.discussions.microsoft.com> wrote in message
> news:D6078784-DF5B-4B79-9C76-A26723EB0930@.microsoft.com...
> > Hello, I would like to password-protect or encrypt the .bak file for 3
> > selected databases on a MS SQL 2000 server. The DBs are all set for Full
> > recovery mode and the nighly backups run OK now using a DB Maintenance
> > Plan.
> > How can I encrypt only the backup files without encrypting either the DB
> > or
> > the server? Thanks, Pancho
>
>|||place backup to encrypted folder
--
Aleksandar Grbic
MCDBA
"Pancho" wrote:
> Hello, I would like to password-protect or encrypt the .bak file for 3
> selected databases on a MS SQL 2000 server. The DBs are all set for Full
> recovery mode and the nighly backups run OK now using a DB Maintenance Plan.
> How can I encrypt only the backup files without encrypting either the DB or
> the server? Thanks, Pancho|||Thank you Jerry, Ben and Aleksandar. These were all helpful ideas and I
posted a helpful rating to each.
Best Regards, Pancho
"Aleksandar Grbic" wrote:
> place backup to encrypted folder
> --
> Aleksandar Grbic
> MCDBA
>
> "Pancho" wrote:
> > Hello, I would like to password-protect or encrypt the .bak file for 3
> > selected databases on a MS SQL 2000 server. The DBs are all set for Full
> > recovery mode and the nighly backups run OK now using a DB Maintenance Plan.
> > How can I encrypt only the backup files without encrypting either the DB or
> > the server? Thanks, Pancho

How to empty log file in SQL 2005?

I have been trying to shrink or emptly a log file in one of my Databases but
to no evail. When I try to shrink it only shrinks it by a little bit and
grows to the original size within seconds.
Is there a way to completely empty a log file. It about 80 GB and something
is now right...
Thank you.
Peace in Christ
Marco Napoli
http://www.ourlovingmother.orgMarco Napoli wrote:
> I have been trying to shrink or emptly a log file in one of my Databases b
ut
> to no evail. When I try to shrink it only shrinks it by a little bit and
> grows to the original size within seconds.
> Is there a way to completely empty a log file. It about 80 GB and somethin
g
> is now right...
>
> Thank you.
>
When was the last time you backed up the transaction log? Sounds like
never, which means there are transactions piling up in there. Until
those are flushed out, you can't shrink it.|||can u change it to simple mode?
"Marco Napoli" <marco@.avantitecnospam.com> glsD:uZewtPilGHA.5108@.TK2MSFTNGP03.phx
.gbl...
>I have been trying to shrink or emptly a log file in one of my Databases
>but to no evail. When I try to shrink it only shrinks it by a little bit
>and grows to the original size within seconds.
> Is there a way to completely empty a log file. It about 80 GB and
> something is now right...
>
> Thank you.
> --
> Peace in Christ
> Marco Napoli
> http://www.ourlovingmother.org
>|||How would I flush them?
Thanks.
Marco
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:OA34pVilGHA.2136@.TK2MSFTNGP04.phx.gbl...
> Marco Napoli wrote:
> When was the last time you backed up the transaction log? Sounds like
> never, which means there are transactions piling up in there. Until those
> are flushed out, you can't shrink it.|||Marco Napoli wrote:
> How would I flush them?
>
If you don't need the ability to do point-in-time recovery, put the
database in Simple recovery mode and the log will flush itself at the
next checkpoint. With an 80GB log file, this might take a few minutes.
I'm guessing you aren't concerned with point-in-time recovery, since
you're not backing up the transaction log. If I'm wrong, then you need
to do some reading about backups and the transaction log, because you're
not taking care of your database right now.
To solve your immediate problem, run BACKUP LOG WITH TRUNCATE_ONLY
against your database.|||As of right now the point-in-time recovery is not necessary but I am working
my way to that. The maintenance plan makes a full back at certain time.
Thank you for the help I will try it.
Marco
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:uQRyYrilGHA.4772@.TK2MSFTNGP04.phx.gbl...
> Marco Napoli wrote:
> If you don't need the ability to do point-in-time recovery, put the
> database in Simple recovery mode and the log will flush itself at the next
> checkpoint. With an 80GB log file, this might take a few minutes.
> I'm guessing you aren't concerned with point-in-time recovery, since
> you're not backing up the transaction log. If I'm wrong, then you need to
> do some reading about backups and the transaction log, because you're not
> taking care of your database right now.
> To solve your immediate problem, run BACKUP LOG WITH TRUNCATE_ONLY against
> your database.|||Marco Napoli wrote:
> As of right now the point-in-time recovery is not necessary but I am worki
ng
> my way to that. The maintenance plan makes a full back at certain time.
>
Do yourself a favor and stop using maintenance plans. Learn what the
various options in a maintenance plan do, understand why they are used,
and write your own routines. Had you done that with backups, instead of
clicking buttons in a wizard, you would have understood the transaction
log and not be staring at an 80GB log file now. Wizards have their
place, but database administration is not one of them.|||Thank you so much that solved my problem.
Marco
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:uQRyYrilGHA.4772@.TK2MSFTNGP04.phx.gbl...
> Marco Napoli wrote:
> If you don't need the ability to do point-in-time recovery, put the
> database in Simple recovery mode and the log will flush itself at the next
> checkpoint. With an 80GB log file, this might take a few minutes.
> I'm guessing you aren't concerned with point-in-time recovery, since
> you're not backing up the transaction log. If I'm wrong, then you need to
> do some reading about backups and the transaction log, because you're not
> taking care of your database right now.
> To solve your immediate problem, run BACKUP LOG WITH TRUNCATE_ONLY against
> your database.|||Thank you for the tip.
Marco
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:%23wbTOEjlGHA.3816@.TK2MSFTNGP02.phx.gbl...
> Marco Napoli wrote:
> Do yourself a favor and stop using maintenance plans. Learn what the
> various options in a maintenance plan do, understand why they are used,
> and write your own routines. Had you done that with backups, instead of
> clicking buttons in a wizard, you would have understood the transaction
> log and not be staring at an 80GB log file now. Wizards have their place,
> but database administration is not one of them.
>

How to eliminate the NULL field values

I am importing an Access .mdb file into MS SQL server, and empty fields where the default value is "", change into NULL. This is a problem when I re-export a result set and have to apply a procedure to clean these values. Is there a way to eliminate this? . . . . and what have I missed?Check 'allow nulls' and 'default value' settings in the access tables.|||and to solve for the export problem...

Use a query to export the table, and use
Select ISNULL(yourfield,"") AS FIELD1...sql

How to editing XML file through script tack in SSIS (Integration Service) 2005

Please help me with editing a XML file through script tack in SSIS (Integration Service) 2005

Thank you

Here are some articles to start with:

http://www.microsoft.com/technet/prodtechnol/sql/2005/intro2is.mspx
http://sqljunkies.com/WebLog/knight_reign/default.aspx

How to editing XML file through script tack in SSIS (Integration Service) 2005

Please help me with editing a XML file through script tack in SSIS (Integration Service) 2005

Thank you

Here are some articles to start with:

http://www.microsoft.com/technet/prodtechnol/sql/2005/intro2is.mspx
http://sqljunkies.com/WebLog/knight_reign/default.aspx

How to edit a asp.net2.0/C# project in VS2005, Plz HELP

Hi,

How can i run or edit my ASP.NET 2.0/C# project in VS2005. I have source code files (including database) but it doesn't have .sln file. Please guide me how can i edit or run this in VS2005

Hi,

if you are missing the sln file, you should create a new web project and import the existing files to your solution.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Thanks for the help.

which files are necessary, while sending ASP.NET 2.0/C# project (for further development) to the developer, like .sln files, database.

please write the name of other files. and how to open that project in VS2005.

Thanks

vkkv

|||Every file except *user files as they store user specific information.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

How to dynamically map the coloumn to a flat file destination?

Hi All,

I am struck at one point. I am trying to this operation and not able to go further.

1. I have got the dataset to a variable in the control flow.

2. I am looping through the dataset based on a coloumn.

3. Now inside my For each loop i have a dataflow task.

4. In the data flow task i am trying to build a dynamic query using the OLEDB Source and i have selected SQL Command from variable. And the variable build the Query as select * from @.othervariable.

Now my question is

Can i send the data of each of the Query resultset to an out put text file using Flat File Connection? If yes pls guide me how? I have tried to create a flat file connection but i am failing how to map the data comming from step 4 dynamically for every query, since every query gives you a different resultset with different coloumns.

Thanks in advance..

Regards,

Dev.

Metadata cannot change. Will it change with your dynamic query?|||

hi phil,

yes every query produces a different result. pls. let me know the best way to handle this kind of scenario. I have total 30 different result sets and need to generate 30 different flat files based on the different out out.

Thanx in advance..

Dev

|||

Dev,

If the datasets has the same structure (column names, data types); then it is possible to do it using SSIS.

I have 2 posts in my blog that explains how to loop through a a data set and how write into different Excel files. You could modified to write into different flat files if you need.

http://rafael-salas.blogspot.com/2006/12/import-header-line-tables-into-dynamic_22.html

|||

Rafeal,

Unfortunately the datasets are not same. they are different in each case. I am not bale to figure out a best solution for this. Pls let me know if u have any ideas onthis...

thanx in advance..

dev

|||

dev15`4534345677 wrote:

Rafeal,

Unfortunately the datasets are not same. they are different in each case. I am not bale to figure out a best solution for this. Pls let me know if u have any ideas onthis...

thanx in advance..

dev

You're going to have to setup a data flow for each format.|||

I completly agree with your approch as that was my last option. Do u think this is the only one i have.....

Can't i do any thing dynamically as u can imagine doing 30 different data flow for 30 times !!!!

Thanx in advance..

Regards,

Dev

|||

dev15`4534345677 wrote:

I completly agree with your approch as that was my last option. Do u think this is the only one i have.....

Can't i do any thing dynamically as u can imagine doing 30 different data flow for 30 times !!!!

Thanx in advance..

Regards,

Dev

No, that is your option. SSIS needs to know the metadata up front. Sorry. How would you map the columns in the destination anyway, if you want it to be dynamic?

You might be able to write a program that programatically builds a data flow for you, but then you aren't really using SSIS at that point. |||

Phil,

what would be your approach if there is a scenario like this for you.....

Regards,

Dev

|||

dev15`4534345677 wrote:

Phil,

what would be your approach if there is a scenario like this for you.....

Regards,

Dev

Well, the only other option that comes to mind is to read everything in as one big string, and then use a conditional split to send the data to 30 different connection managers. (That's an overly-simplified explanation, but I hope you get the idea...)

The connection managers are going to have to be setup for each format. I'm not sure there's a way around that.|||

Hi All,

I would appreciate If any body has a different solution for this pls. post it here...

regds,

dev

|||

dev15`4534345677 wrote:

Hi All,

I would appreciate If any body has a different solution for this pls. post it here...

regds,

dev

Also please search this forum. You are not the first to desire this and you'll not be the last.

The current version is very strict on metadata rules. Perhaps SSIS isn't the best option? That is, maybe you should write views and then use another SQL utility to output the results to a file. BCP, perhaps.|||Building package programatically may be another option...more complex perhaps. BOL it a godd point ot start|||

I apologize in advance if I misinterpreted something but wouldn't a script task be an option? Theoretically you could replace your data flow task with a script task and pass in the variable containing the data flow then within the script task you could loop through the dataset columns and rows and output it to a flat file of your chosing. Again, I apologize if a script task is not an option but I do not see it being overly difficult to implement what you are asking using one. Have you looked into this option? Are you familar with VB.NET?

|||

ADMariner wrote:

I apologize in advance if I misinterpreted something but wouldn't a script task be an option? Theoretically you could replace your data flow task with a script task and pass in the variable containing the data flow then within the script task you could loop through the dataset columns and rows and output it to a flat file of your chosing. Again, I apologize if a script task is not an option but I do not see it being overly difficult to implement what you are asking using one. Have you looked into this option? Are you familar with VB.NET?

I don't think it's an option as you still need to know the outputs in advance to setup the script task's outputs.

How to Dynamically Generate FileName using SSIS

Hi

I have generated a ssis package that creates a text file based on a query retrieving records from the table.

Now i want the file name to be appended with the date and time of package execution. So the filename will be something like ; Filename_MMDDYYYY.txt.

How can i do this with the help of SSIS?

Anyone having an idea would be of great help.

Regards,

Salman Shehbaz.

Have a look at expressions.|||

for me the following link did the magic;

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

how to dynamically change source text file in sql server dts

Hi ,

I have a table (source file) where all sourse file(its all text file) name and location stored. i have a dts packeage, through this package all the sourse file will upload. So I need to dynamically change the source file name in dts packages.

How i can do it?

Can any body plz help me out!

Thnaks

SAndipan

I just did it, by using dynamic property.

thanks

how to dump or append data to a text file

i am doing some insert or update on my db, and i want to dump the errors, if any, to a text file.

If the text file deosnt exist, then i want to create it. Then append data to it, in smthg like:

insert into mytable values (f1,f2,f3)

IF @.@.ERROR <> 0

begin

-- check if text file exist

-- if no create it and write 'an error occured etc...'

-- else append to it another line 'an error occured etc...

end

thanks for helping!!

Hi Terry,

If you're using 2000, then there really is no way to achieve what you are asking except to shell the command out via xp_cmdshell - which will do everything you asked with a little "creativity".

If using 2005, then your best bet would be to wrap the required functionality in a CLR stored proc. Again, BOL has some great info on this.

Cheers,
Rob

How to Dump Multiple Stored Procedures to Multiple Files?

I would like to dump each of the stored procedures in one database to a
separate file.
Programmatically would be great (e.g., start with SELECT * FROM
sysobjects WHERE xtype='P'), so that I can have greater flexibility.
My last resort is to use some tool already written by somebody else
that achieves this same goal, especially if I could get the source code
on how to do it myself.
Any ideas?do you mean the source code?
right click somewhere in the procedures window in Enterprise Manager
all tasks-->generate sql script, select the procs you want to
script,click on the options tab and select one file per object
Denis the SQL Menace
http://sqlservercode.blogspot.com/
samtil...@.gmail.com wrote:
> I would like to dump each of the stored procedures in one database to a
> separate file.
> Programmatically would be great (e.g., start with SELECT * FROM
> sysobjects WHERE xtype='P'), so that I can have greater flexibility.
> My last resort is to use some tool already written by somebody else
> that achieves this same goal, especially if I could get the source code
> on how to do it myself.
> Any ideas?|||What if he doesn't have EM? He didn't say what version he was on. I only
have 2k5 with SMS on my dev box. This version seems to have resorted to
dumping procs out as strings submitted to sp_executesql for some ungodly
reason. This has frustrated me to no end!!
"SQL" <denis.gobo@.gmail.com> wrote in message
news:1148392984.143435.54080@.j33g2000cwa.googlegroups.com...
> do you mean the source code?
> right click somewhere in the procedures window in Enterprise Manager
> all tasks-->generate sql script, select the procs you want to
> script,click on the options tab and select one file per object
>
> Denis the SQL Menace
> http://sqlservercode.blogspot.com/
>
>
> samtil...@.gmail.com wrote:
>|||For SQL Server 2005 check out Bill graziano's scripting tool
http://weblogs.sqlteam.com/billg/ar...11/22/8414.aspx
and
http://weblogs.sqlteam.com/billg/ar...12/24/8613.aspx
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||Your best bet is to use one of the API's which has such functionality built-
in: DMO (2000) or SMO
(2005). I have some info here http://www.karaszi.com/SQLServer/in...ip
t.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<samtilden@.gmail.com> wrote in message news:1148392340.216845.94900@.j55g2000cwa.googlegroups
.com...
>I would like to dump each of the stored procedures in one database to a
> separate file.
> Programmatically would be great (e.g., start with SELECT * FROM
> sysobjects WHERE xtype='P'), so that I can have greater flexibility.
> My last resort is to use some tool already written by somebody else
> that achieves this same goal, especially if I could get the source code
> on how to do it myself.
> Any ideas?
>|||Oh, yes!! Thanks...that fits the bill perfectly!
"SQL" <denis.gobo@.gmail.com> wrote in message
news:1148395150.393418.229440@.j33g2000cwa.googlegroups.com...
> For SQL Server 2005 check out Bill graziano's scripting tool
> http://weblogs.sqlteam.com/billg/ar...11/22/8414.aspx
> and
> http://weblogs.sqlteam.com/billg/ar...12/24/8613.aspx
>
> Denis the SQL Menace
> http://sqlservercode.blogspot.com/
>|||On 23 May 2006 06:52:20 -0700, samtilden@.gmail.com wrote:
in <1148392340.216845.94900@.j55g2000cwa.googlegroups.com>

>I would like to dump each of the stored procedures in one database to a
>separate file.
>Programmatically would be great (e.g., start with SELECT * FROM
>sysobjects WHERE xtype='P'), so that I can have greater flexibility.
>My last resort is to use some tool already written by somebody else
>that achieves this same goal, especially if I could get the source code
>on how to do it myself.
>Any ideas?
Here's a little VB6 code that does what you want. Just include the
microsoft SQLDMO Object Library. Functions, Triggers, and Stored
Procedures are extracted to one file and Views to another. Obviously
you'll need to modify the server and database names.
Option Explicit
Private Sub Main()
Dim oServer As SQLDMO.SQLServer2: Set oServer = New SQLDMO.SQLServer2
oServer.LoginSecure = True
oServer.Connect "STEFS-GEORGIA\HORSESHOWTIME"
Dim oDB As SQLDMO.Database2: Set oDB = oServer.Databases("ShowTime")
Dim sProcs() As String: ReDim sProcs(0 To oDB.StoredProcedures.Count - 1)
Dim oSP As SQLDMO.StoredProcedure
Dim lngN As Long: lngN = 2
For Each oSP In oDB.StoredProcedures
sProcs(lngN) = Trim$(oSP.Text)
lngN = lngN + 1
Next
Set oSP = Nothing
Dim oTable As SQLDMO.Table2
For Each oTable In oDB.Tables
Dim oTrigger As SQLDMO.Trigger2
For Each oTrigger In oTable.Triggers
Dim sTriggers As String: sTriggers = sTriggers & Trim$(oTrigger.Text) & "GO"
& vbCrLf & vbCrLf
Next
Next
Set oTrigger = Nothing
Set oTable = Nothing
Dim oUDF As SQLDMO.UserDefinedFunction
For Each oUDF In oDB.UserDefinedFunctions
Dim sFunctions As String: sFunctions = sFunctions & Trim$(oUDF.Text) & "GO"
& vbCrLf & vbCrLf
Next
Dim sViews() As String: ReDim sViews(0 To oDB.Views.Count - 1): lngN = 0
Dim oView As SQLDMO.View
For Each oView In oDB.Views
If (Not oView.Name Like "sys*") Then
sViews(lngN) = Trim$(oView.Text)
lngN = lngN + 1
End If
Next
Set oUDF = Nothing: Set oDB = Nothing
oServer.DisConnect: Set oServer = Nothing
Dim intFileNumber As Integer: intFileNumber = FreeFile
Open "D:\My Documents\ShowTime\SQL\sp.txt" For Output Lock Write As #intFile
Number
Print #intFileNumber, Replace$(Replace$(Trim$(sFunctions) & Trim$(Join(sProc
s, vbCrLf)) & vbCrLf & Trim$(sTriggers), vbCrLf & vbCrLf & "GO", vbCrLf & "G
O"), "GO" & vbCrLf & vbCrLf, "GO" & vbCrLf)
Close #intFileNumber
intFileNumber = FreeFile
Open "D:\My Documents\ShowTime\SQL\view.txt" For Output Lock Write As #intFi
leNumber
Print #intFileNumber, Replace$(Replace$(Trim$(Join(sViews, vbCrLf & "GO" & v
bCrLf)), vbCrLf & vbCrLf & "GO", vbCrLf & "GO"), "GO" & vbCrLf & vbCrLf, "GO
" & vbCrLf)
Close #intFileNumber
End Sub
This posting is provided "AS IS" with no warranties and no guarantees either
express or implied.
Stefan Berglund|||I thank all of you for your offerings to help me.
However, there must be some way to get the source code of stored
procedures directly from the database itself and not go through any
third party software or tool. After all, those third party tools do
it. But how?
For example, we can get table structures, column definitions, foreign
keys and indexes directly from the system tables. We should, I
believe, be able to get the stored procedures also from tables like
sysobjects, sysproperties, syscomments or some system table, right?|||You can get the source code in SQL 2005 by using OBJECT_DEFINITION like
this
SELECT SPECIFIC_NAME,OBJECT_DEFINITION( OBJECT_ID(SPECIFIC_NAME))
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE ='PROCEDURE'
or you can query sys.sql_modules for the definition
In 2000 you can use sp_helptext
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||Sure:
2000:
Source code in syscomments
Also available though sp_helptext
Several rows for one object in syscomments if > 4000 characters, I don't kno
w how well sp_help
handles this in current incarnation.
The ROUTINE_DEFINTION column in the INFORMATION_SCHEMA.ROUTINES view. Only r
eturns first 4000
characters.
2005:
Above applies, and also:
Column named definition in the sys.sql_modules (probably others as well) cat
alog view.
OBJECT_DEFINITION() function
Above two returns nvarchar(max), so not problem with length of source code.
Be careful if you have renamed the procedure, the source code does not refle
ct that rename. I don't
know how clever the scripting stuff are (DMO and SMO) regarding renamed proc
edures - make sure you
test it.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<samtilden@.gmail.com> wrote in message news:1148472967.521632.244810@.j73g2000cwa.googlegroup
s.com...
>I thank all of you for your offerings to help me.
> However, there must be some way to get the source code of stored
> procedures directly from the database itself and not go through any
> third party software or tool. After all, those third party tools do
> it. But how?
> For example, we can get table structures, column definitions, foreign
> keys and indexes directly from the system tables. We should, I
> believe, be able to get the stored procedures also from tables like
> sysobjects, sysproperties, syscomments or some system table, right?
>

How to Dump Multiple Stored Procedures to Multiple Files?

I would like to dump each of the stored procedures in one database to a
separate file.
Programmatically would be great (e.g., start with SELECT * FROM
sysobjects WHERE xtype='P'), so that I can have greater flexibility.
My last resort is to use some tool already written by somebody else
that achieves this same goal, especially if I could get the source code
on how to do it myself.
Any ideas?do you mean the source code?
right click somewhere in the procedures window in Enterprise Manager
all tasks-->generate sql script, select the procs you want to
script,click on the options tab and select one file per object
Denis the SQL Menace
http://sqlservercode.blogspot.com/
samtil...@.gmail.com wrote:
> I would like to dump each of the stored procedures in one database to a
> separate file.
> Programmatically would be great (e.g., start with SELECT * FROM
> sysobjects WHERE xtype='P'), so that I can have greater flexibility.
> My last resort is to use some tool already written by somebody else
> that achieves this same goal, especially if I could get the source code
> on how to do it myself.
> Any ideas?|||Your best bet is to use one of the API's which has such functionality built-
in: DMO (2000) or SMO
(2005). I have some info here http://www.karaszi.com/SQLServer/in...ip
t.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<samtilden@.gmail.com> wrote in message news:1148392340.216845.94900@.j55g2000cwa.googlegroup
s.com...
>I would like to dump each of the stored procedures in one database to a
> separate file.
> Programmatically would be great (e.g., start with SELECT * FROM
> sysobjects WHERE xtype='P'), so that I can have greater flexibility.
> My last resort is to use some tool already written by somebody else
> that achieves this same goal, especially if I could get the source code
> on how to do it myself.
> Any ideas?
>|||On 23 May 2006 06:52:20 -0700, samtilden@.gmail.com wrote:
in <1148392340.216845.94900@.j55g2000cwa.googlegroups.com>

>I would like to dump each of the stored procedures in one database to a
>separate file.
>Programmatically would be great (e.g., start with SELECT * FROM
>sysobjects WHERE xtype='P'), so that I can have greater flexibility.
>My last resort is to use some tool already written by somebody else
>that achieves this same goal, especially if I could get the source code
>on how to do it myself.
>Any ideas?
Here's a little VB6 code that does what you want. Just include the
microsoft SQLDMO Object Library. Functions, Triggers, and Stored
Procedures are extracted to one file and Views to another. Obviously
you'll need to modify the server and database names.
Option Explicit
Private Sub Main()
Dim oServer As SQLDMO.SQLServer2: Set oServer = New SQLDMO.SQLServer2
oServer.LoginSecure = True
oServer.Connect "STEFS-GEORGIA\HORSESHOWTIME"
Dim oDB As SQLDMO.Database2: Set oDB = oServer.Databases("ShowTime")
Dim sProcs() As String: ReDim sProcs(0 To oDB.StoredProcedures.Count - 1)
Dim oSP As SQLDMO.StoredProcedure
Dim lngN As Long: lngN = 2
For Each oSP In oDB.StoredProcedures
sProcs(lngN) = Trim$(oSP.Text)
lngN = lngN + 1
Next
Set oSP = Nothing
Dim oTable As SQLDMO.Table2
For Each oTable In oDB.Tables
Dim oTrigger As SQLDMO.Trigger2
For Each oTrigger In oTable.Triggers
Dim sTriggers As String: sTriggers = sTriggers & Trim$(oTrigger.Text) & "GO"
& vbCrLf & vbCrLf
Next
Next
Set oTrigger = Nothing
Set oTable = Nothing
Dim oUDF As SQLDMO.UserDefinedFunction
For Each oUDF In oDB.UserDefinedFunctions
Dim sFunctions As String: sFunctions = sFunctions & Trim$(oUDF.Text) & "GO"
& vbCrLf & vbCrLf
Next
Dim sViews() As String: ReDim sViews(0 To oDB.Views.Count - 1): lngN = 0
Dim oView As SQLDMO.View
For Each oView In oDB.Views
If (Not oView.Name Like "sys*") Then
sViews(lngN) = Trim$(oView.Text)
lngN = lngN + 1
End If
Next
Set oUDF = Nothing: Set oDB = Nothing
oServer.DisConnect: Set oServer = Nothing
Dim intFileNumber As Integer: intFileNumber = FreeFile
Open "D:\My Documents\ShowTime\SQL\sp.txt" For Output Lock Write As #intFile
Number
Print #intFileNumber, Replace$(Replace$(Trim$(sFunctions) & Trim$(Join(sProc
s, vbCrLf)) & vbCrLf & Trim$(sTriggers), vbCrLf & vbCrLf & "GO", vbCrLf & "G
O"), "GO" & vbCrLf & vbCrLf, "GO" & vbCrLf)
Close #intFileNumber
intFileNumber = FreeFile
Open "D:\My Documents\ShowTime\SQL\view.txt" For Output Lock Write As #intFi
leNumber
Print #intFileNumber, Replace$(Replace$(Trim$(Join(sViews, vbCrLf & "GO" & v
bCrLf)), vbCrLf & vbCrLf & "GO", vbCrLf & "GO"), "GO" & vbCrLf & vbCrLf, "GO
" & vbCrLf)
Close #intFileNumber
End Sub
This posting is provided "AS IS" with no warranties and no guarantees either
express or implied.
Stefan Berglund|||I thank all of you for your offerings to help me.
However, there must be some way to get the source code of stored
procedures directly from the database itself and not go through any
third party software or tool. After all, those third party tools do
it. But how?
For example, we can get table structures, column definitions, foreign
keys and indexes directly from the system tables. We should, I
believe, be able to get the stored procedures also from tables like
sysobjects, sysproperties, syscomments or some system table, right?|||You can get the source code in SQL 2005 by using OBJECT_DEFINITION like
this
SELECT SPECIFIC_NAME,OBJECT_DEFINITION( OBJECT_ID(SPECIFIC_NAME))
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE ='PROCEDURE'
or you can query sys.sql_modules for the definition
In 2000 you can use sp_helptext
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||Sure:
2000:
Source code in syscomments
Also available though sp_helptext
Several rows for one object in syscomments if > 4000 characters, I don't kno
w how well sp_help
handles this in current incarnation.
The ROUTINE_DEFINTION column in the INFORMATION_SCHEMA.ROUTINES view. Only r
eturns first 4000
characters.
2005:
Above applies, and also:
Column named definition in the sys.sql_modules (probably others as well) cat
alog view.
OBJECT_DEFINITION() function
Above two returns nvarchar(max), so not problem with length of source code.
Be careful if you have renamed the procedure, the source code does not refle
ct that rename. I don't
know how clever the scripting stuff are (DMO and SMO) regarding renamed proc
edures - make sure you
test it.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<samtilden@.gmail.com> wrote in message news:1148472967.521632.244810@.j73g2000cwa.googlegrou
ps.com...
>I thank all of you for your offerings to help me.
> However, there must be some way to get the source code of stored
> procedures directly from the database itself and not go through any
> third party software or tool. After all, those third party tools do
> it. But how?
> For example, we can get table structures, column definitions, foreign
> keys and indexes directly from the system tables. We should, I
> believe, be able to get the stored procedures also from tables like
> sysobjects, sysproperties, syscomments or some system table, right?
>

How to Dump Multiple Stored Procedures to Multiple Files?

I would like to dump each of the stored procedures in one database to a
separate file.
Programmatically would be great (e.g., start with SELECT * FROM
sysobjects WHERE xtype='P'), so that I can have greater flexibility.
My last resort is to use some tool already written by somebody else
that achieves this same goal, especially if I could get the source code
on how to do it myself.
Any ideas?do you mean the source code?
right click somewhere in the procedures window in Enterprise Manager
all tasks-->generate sql script, select the procs you want to
script,click on the options tab and select one file per object
Denis the SQL Menace
http://sqlservercode.blogspot.com/
samtil...@.gmail.com wrote:
> I would like to dump each of the stored procedures in one database to a
> separate file.
> Programmatically would be great (e.g., start with SELECT * FROM
> sysobjects WHERE xtype='P'), so that I can have greater flexibility.
> My last resort is to use some tool already written by somebody else
> that achieves this same goal, especially if I could get the source code
> on how to do it myself.
> Any ideas?|||What if he doesn't have EM? He didn't say what version he was on. I only
have 2k5 with SMS on my dev box. This version seems to have resorted to
dumping procs out as strings submitted to sp_executesql for some ungodly
reason. This has frustrated me to no end!!
"SQL" <denis.gobo@.gmail.com> wrote in message
news:1148392984.143435.54080@.j33g2000cwa.googlegroups.com...
> do you mean the source code?
> right click somewhere in the procedures window in Enterprise Manager
> all tasks-->generate sql script, select the procs you want to
> script,click on the options tab and select one file per object
>
> Denis the SQL Menace
> http://sqlservercode.blogspot.com/
>
>
> samtil...@.gmail.com wrote:
>> I would like to dump each of the stored procedures in one database to a
>> separate file.
>> Programmatically would be great (e.g., start with SELECT * FROM
>> sysobjects WHERE xtype='P'), so that I can have greater flexibility.
>> My last resort is to use some tool already written by somebody else
>> that achieves this same goal, especially if I could get the source code
>> on how to do it myself.
>> Any ideas?
>|||For SQL Server 2005 check out Bill graziano's scripting tool
http://weblogs.sqlteam.com/billg/archive/2005/11/22/8414.aspx
and
http://weblogs.sqlteam.com/billg/archive/2005/12/24/8613.aspx
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||Your best bet is to use one of the API's which has such functionality built-in: DMO (2000) or SMO
(2005). I have some info 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/
<samtilden@.gmail.com> wrote in message news:1148392340.216845.94900@.j55g2000cwa.googlegroups.com...
>I would like to dump each of the stored procedures in one database to a
> separate file.
> Programmatically would be great (e.g., start with SELECT * FROM
> sysobjects WHERE xtype='P'), so that I can have greater flexibility.
> My last resort is to use some tool already written by somebody else
> that achieves this same goal, especially if I could get the source code
> on how to do it myself.
> Any ideas?
>|||Oh, yes!! Thanks...that fits the bill perfectly!
"SQL" <denis.gobo@.gmail.com> wrote in message
news:1148395150.393418.229440@.j33g2000cwa.googlegroups.com...
> For SQL Server 2005 check out Bill graziano's scripting tool
> http://weblogs.sqlteam.com/billg/archive/2005/11/22/8414.aspx
> and
> http://weblogs.sqlteam.com/billg/archive/2005/12/24/8613.aspx
>
> Denis the SQL Menace
> http://sqlservercode.blogspot.com/
>|||On 23 May 2006 06:52:20 -0700, samtilden@.gmail.com wrote:
in <1148392340.216845.94900@.j55g2000cwa.googlegroups.com>
>I would like to dump each of the stored procedures in one database to a
>separate file.
>Programmatically would be great (e.g., start with SELECT * FROM
>sysobjects WHERE xtype='P'), so that I can have greater flexibility.
>My last resort is to use some tool already written by somebody else
>that achieves this same goal, especially if I could get the source code
>on how to do it myself.
>Any ideas?
Here's a little VB6 code that does what you want. Just include the
microsoft SQLDMO Object Library. Functions, Triggers, and Stored
Procedures are extracted to one file and Views to another. Obviously
you'll need to modify the server and database names.
Option Explicit
Private Sub Main()
Dim oServer As SQLDMO.SQLServer2: Set oServer = New SQLDMO.SQLServer2
oServer.LoginSecure = True
oServer.Connect "STEFS-GEORGIA\HORSESHOWTIME"
Dim oDB As SQLDMO.Database2: Set oDB = oServer.Databases("ShowTime")
Dim sProcs() As String: ReDim sProcs(0 To oDB.StoredProcedures.Count - 1)
Dim oSP As SQLDMO.StoredProcedure
Dim lngN As Long: lngN = 2
For Each oSP In oDB.StoredProcedures
sProcs(lngN) = Trim$(oSP.Text)
lngN = lngN + 1
Next
Set oSP = Nothing
Dim oTable As SQLDMO.Table2
For Each oTable In oDB.Tables
Dim oTrigger As SQLDMO.Trigger2
For Each oTrigger In oTable.Triggers
Dim sTriggers As String: sTriggers = sTriggers & Trim$(oTrigger.Text) & "GO" & vbCrLf & vbCrLf
Next
Next
Set oTrigger = Nothing
Set oTable = Nothing
Dim oUDF As SQLDMO.UserDefinedFunction
For Each oUDF In oDB.UserDefinedFunctions
Dim sFunctions As String: sFunctions = sFunctions & Trim$(oUDF.Text) & "GO" & vbCrLf & vbCrLf
Next
Dim sViews() As String: ReDim sViews(0 To oDB.Views.Count - 1): lngN = 0
Dim oView As SQLDMO.View
For Each oView In oDB.Views
If (Not oView.Name Like "sys*") Then
sViews(lngN) = Trim$(oView.Text)
lngN = lngN + 1
End If
Next
Set oUDF = Nothing: Set oDB = Nothing
oServer.DisConnect: Set oServer = Nothing
Dim intFileNumber As Integer: intFileNumber = FreeFile
Open "D:\My Documents\ShowTime\SQL\sp.txt" For Output Lock Write As #intFileNumber
Print #intFileNumber, Replace$(Replace$(Trim$(sFunctions) & Trim$(Join(sProcs, vbCrLf)) & vbCrLf & Trim$(sTriggers), vbCrLf & vbCrLf & "GO", vbCrLf & "GO"), "GO" & vbCrLf & vbCrLf, "GO" & vbCrLf)
Close #intFileNumber
intFileNumber = FreeFile
Open "D:\My Documents\ShowTime\SQL\view.txt" For Output Lock Write As #intFileNumber
Print #intFileNumber, Replace$(Replace$(Trim$(Join(sViews, vbCrLf & "GO" & vbCrLf)), vbCrLf & vbCrLf & "GO", vbCrLf & "GO"), "GO" & vbCrLf & vbCrLf, "GO" & vbCrLf)
Close #intFileNumber
End Sub
This posting is provided "AS IS" with no warranties and no guarantees either express or implied.
Stefan Berglund|||I thank all of you for your offerings to help me.
However, there must be some way to get the source code of stored
procedures directly from the database itself and not go through any
third party software or tool. After all, those third party tools do
it. But how?
For example, we can get table structures, column definitions, foreign
keys and indexes directly from the system tables. We should, I
believe, be able to get the stored procedures also from tables like
sysobjects, sysproperties, syscomments or some system table, right?|||You can get the source code in SQL 2005 by using OBJECT_DEFINITION like
this
SELECT SPECIFIC_NAME,OBJECT_DEFINITION( OBJECT_ID(SPECIFIC_NAME))
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE ='PROCEDURE'
or you can query sys.sql_modules for the definition
In 2000 you can use sp_helptext
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||Sure:
2000:
Source code in syscomments
Also available though sp_helptext
Several rows for one object in syscomments if > 4000 characters, I don't know how well sp_help
handles this in current incarnation.
The ROUTINE_DEFINTION column in the INFORMATION_SCHEMA.ROUTINES view. Only returns first 4000
characters.
2005:
Above applies, and also:
Column named definition in the sys.sql_modules (probably others as well) catalog view.
OBJECT_DEFINITION() function
Above two returns nvarchar(max), so not problem with length of source code.
Be careful if you have renamed the procedure, the source code does not reflect that rename. I don't
know how clever the scripting stuff are (DMO and SMO) regarding renamed procedures - make sure you
test it.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<samtilden@.gmail.com> wrote in message news:1148472967.521632.244810@.j73g2000cwa.googlegroups.com...
>I thank all of you for your offerings to help me.
> However, there must be some way to get the source code of stored
> procedures directly from the database itself and not go through any
> third party software or tool. After all, those third party tools do
> it. But how?
> For example, we can get table structures, column definitions, foreign
> keys and indexes directly from the system tables. We should, I
> believe, be able to get the stored procedures also from tables like
> sysobjects, sysproperties, syscomments or some system table, right?
>|||It's absolutely braindead that they left this out of the 2005 Management
Studio.
"SQL" wrote:
> For SQL Server 2005 check out Bill graziano's scripting tool
> http://weblogs.sqlteam.com/billg/archive/2005/11/22/8414.aspx
> and
> http://weblogs.sqlteam.com/billg/archive/2005/12/24/8613.aspx
>
> Denis the SQL Menace
> http://sqlservercode.blogspot.com/
>

How to drop Primary key by specifying column only?

I checked column from xsd file which created from DataBase.

And before installing my application I will check any column in destination base whether have complete column or not.

And if there is any column which is not wanted column(not same as xsd structure)

I will delete it by creating sql command as follow "ALTER TABLE tableName DROP COLUMN column1, column2, ....."

and Execute it by program initialization.

So

I need delete it by run-time

However some column may be Primary Key with any reason

That's why I can't delete them by simple command

I expect to delete them by specific column instead of specific constraint name which is not sure name.

Please advise me...

I don't think you can do it by just specifying a column but you can do it this way:

To get the name of the primary key:

SELECT constraint_name
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE (TABLE_NAME = 'TestTable') AND (CONSTRAINT_TYPE = 'PRIMARY KEY')

To drop the primary key:

ALTER TABLE TestTable
DROP CONSTRAINT PK_TestTable

|||

Thanks a lot

:D

How to drop offline file/filegroups after piecemeal restore

I have a very large database (5 TB) with many file groups. Basically each
file group consists of one large table (70 - 80 GB). The backup structure is
set up to backup each filegroup after it's table is loaded (the data is
static) and then perform a differetial backup. The server crashed the other
day and I began the restore process. Unfortunately due to space limitations,
all of the file group backup files were not available. So I did a piecemeal
restore with the primary FG backup file and then all of the other available
FG backup files. The last differential file was restored with RECOVERY and
the database is now online. However there are about 30 file groups offline
now. How do I drop/remove these file/filegroups.
I can not remove the files with the alter database statement, this gets the
error:
Cannot add, remove, or modify a file in filegroup 'FG0001' because the
filegroup is offline
I am running SQL Server 2005 Enterprise edition (sp2)
Thanx in advance for any and all help,
Jeff Carrington
DBA
Comscore
According to Books Online, you should be able to use ALTER DATABASE ... REMOVE FILE to get rid of a
"defunkt" file and filegroup:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/055f9c6a-5c18-4942-98e7-ec918f0ff975.htm
Did you do the initial restore using the PARTIAL option?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Jeff Carrington" <JeffCarrington@.discussions.microsoft.com> wrote in message
news:A3407889-76CA-45E5-89BE-19765A67639B@.microsoft.com...
>I have a very large database (5 TB) with many file groups. Basically each
> file group consists of one large table (70 - 80 GB). The backup structure is
> set up to backup each filegroup after it's table is loaded (the data is
> static) and then perform a differetial backup. The server crashed the other
> day and I began the restore process. Unfortunately due to space limitations,
> all of the file group backup files were not available. So I did a piecemeal
> restore with the primary FG backup file and then all of the other available
> FG backup files. The last differential file was restored with RECOVERY and
> the database is now online. However there are about 30 file groups offline
> now. How do I drop/remove these file/filegroups.
> I can not remove the files with the alter database statement, this gets the
> error:
> Cannot add, remove, or modify a file in filegroup 'FG0001' because the
> filegroup is offline
> I am running SQL Server 2005 Enterprise edition (sp2)
> Thanx in advance for any and all help,
> --
> Jeff Carrington
> DBA
> Comscore
|||Yes I did use the PARTIAL option when executing the first restore of the
primary file group. I read the same section in BOL, but my attempt to remove
the file gets the error stating the file group is offline.
Thanx for the help...
Jeff Carrington
"Tibor Karaszi" wrote:

> According to Books Online, you should be able to use ALTER DATABASE ... REMOVE FILE to get rid of a
> "defunkt" file and filegroup:
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/055f9c6a-5c18-4942-98e7-ec918f0ff975.htm
> Did you do the initial restore using the PARTIAL option?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Jeff Carrington" <JeffCarrington@.discussions.microsoft.com> wrote in message
> news:A3407889-76CA-45E5-89BE-19765A67639B@.microsoft.com...
>
>