Now the script is generating fine, but what i find that there is no statment like
"if not exists(...) "
for the Stored Procedure( i had applied the /r parameter), for the Create Tables it is there.
Why there is no statment IF NOT EXISTS(...) for Stored Procedure?
I need this Line, how can i do this??
Regards,
Thanks.
Gurpreet S. Gill
Hi All, I need to get the database structure through a procedure. Something like this: CREATE procedure get_db_structure as DECLARE exec master..xp_cmdshell 'scptxfr.exe /S [server_name] /d [database_name] /PSA /F c:est_one_str.sql' GO I'm getting an error message. Something like this: 'scptxfr.exe' is not recognized as an internal or external command, operable program or batch file. The scptxfr is in program files/mssql/upgrade folder. I also tried with the exacpt path of the .exe file. Still I'm getting the error.
When I tried in the dos prompt (scptxfr.exe /S [server_name] /d [database_name] /PSA /F c:est_one_str.sql) the command is working fine. I think something wrong with the xp_cmdshell. I assume xp_cmdshell is mainly use to execute dos commands. Does this mean something wrong with the path? Any help is greatly appreciated. sj
I need to generate a script of my entire database, and place the resulting file on a network drive. And I need to run it as a job. I found an old post on this forum that had information on scptxfr.exe. I am a beginner, so I was glad to have found something so simple. But I have a small problem...
I created a SQL Server Agent job with a step that calls the scptxfr executable. But, the job fails because it doesn't recognize the network drive ("The system cannot find the path specified"). It works fine when I run the job and store the resulting script on my c drive. And, when I run scptxfr from the DOS prompt, it works fine when I use the network drive (using the same exact command line that I use in the sql job). It just doesn't work when I run it through a sql job. I've also tried using the full name (\servernameshare) - it didn't work.
Hi All, I am using scptxfr.exe to get the structure of a dababase. Problem is that its creating GRANT statements which I don't want. Also its selecting sysusers object. I have checked the parameters. My parameters look something like this: dos promptmssql7upgradescptxfr.exe /S(servername) -d (databasename) /P sa /f (filename) -r
How can I run this utility without creating grant statements? Any thoughts will help! Thank you in advance!
I am a very new programmer hired to do stored procedures. I want to drop stored procedures with like names..Can I do that with single command something like
Drop Proc where proc like 'XY%'
Other alternative is write a stored procedure which is a group of stored procedures.. selected from sys.objects. Please help me ..
I have to run a Big Sproc for make a lot of updates and insert. because trigger it take to many time. I can drop the trigger before the procedure and recreate it after, but I wondered whether there existed of other solution?
Can I deactive the trigger? I'm affraid too got two copie of code for the trigger that why I dont really like the Drop-Create solution...
I have SQL SERVER 2005 with SP2 on windows XP professional.
When I execute DROP PROCEDURE manually,it works. However when I execute inside a loop made using static cursors, it generates following error "Could not find server 'drop procedure dbo' in sysservers. Execute sp_addlinkedserver to add the server to sysservers."
OR it gives this error "Could not find stored procedure"
We have a secuiry review and they have recommended dropping several xp_ stored procedures. I have tried the drop procedure with no luck, Error 3701, or right click delete in Man Studio, same error. I have granted the user alter permission to the master database and when I try to delete get Error 4606. I try to grant CONTROL permission of the stored proc to the user and get another 4606 error.
Do I just have to control the permissions of these procedures tightly?
Hi,I found this SQL in the news group to drop indexs in a table. I need ascript that will drop all indexes in all user tables of a givendatabase:DECLARE @indexName NVARCHAR(128)DECLARE @dropIndexSql NVARCHAR(4000)DECLARE tableIndexes CURSOR FORSELECT name FROM sysindexesWHERE id = OBJECT_ID(N'F_BI_Registration_Tracking_Summary')AND indid 0AND indid < 255AND INDEXPROPERTY(id, name, 'IsStatistics') = 0OPEN tableIndexesFETCH NEXT FROM tableIndexes INTO @indexNameWHILE @@fetch_status = 0BEGINSET @dropIndexSql = N' DROP INDEXF_BI_Registration_Tracking_Summary.' + @indexNameEXEC sp_executesql @dropIndexSqlFETCH NEXT FROM tableIndexes INTO @indexNameENDCLOSE tableIndexesDEALLOCATE tableIndexesTIARob
Here i have small problem in transactions.I don't know how it is happaning. Up to my knowldge if you start a transaction in side the transaction if you have DML statements Those statements only will be effected by rollback or commit but in MS SQL SERVER 7.0 and 6.5 It is rolling back all the commands including DDL witch it shouldn't please let me know on that If any one can help this please tell me ...........Please............ For Example begin transaction t1 create table t1 drop table t2
then execute bellow statements select * from t1 this query gives you table with out data
select * from t2 you will recieve an error that there is no object
but if you rollback T1 willn't be there in the database
droped table t2 will come back please explain how it can happand.....................
Hi folks, Wonder if I'm onto something....everything I've read says that to drop foreign key constraints, you have to drop/recreate the table.
I've been able to dynamically create the ALTER TABLE statements for all FK constraints in a database. The way I did was a little convoluted (Access 97 linked tables and pass-through queries, cut/paste from the debug window to SQL7 QA), but it does work. More importantly, it saves the hassle of dropping/recreating tables....Someone with more expertise than I in using nested cursors could probably convert it to pure T-SQL pretty easily.
Just curious...opinions? Be glad to post the code here if that's appropriate.
I am trying to drop some tables, but keep getting the message: Msg 3702, Level 16, State 1 Cannot drop the table 'TABLE' because it is currently in use. But it is NOT IN USE. I tried to do an sp_who, and I'm the only one. Please help. Thank you.
I am trying to delete a database that is labeled suspect. Is there a way to force a drop of a database? It is shows it is marked inaccessible when trying to delete. Thanks, Steve
I am managing 120 gb SQL7 database. The 120 GB is in a RAID5 (Hardware RAID). At begining, I think I should create a user defined filegroup besides Primary filegroup, as BOL suggested.
After I read some discussions here, It appears I cannot gain any performance by adding second filegroups. Now I want to drop the filegroup I created. The Question is How?. If I do alter database, move all files from userdefined filegroup, does this mean my userdefined filegroup has gone?
I am trying to drop the database through the application designed using vb.net. Through login form i am logging into the database from the application. There is an option for the user to take the backup and restore the database using the application. I am able to take the backup of the database. To restore the database, i am dropping the existing database. I am unable to drop the database as an error message is being displayed saying that "Cannot drop the database, as it is currently in use". I tried to drop the database even by changing the database names in the connection string. But i am unable to drop the database. If any one finds the solution for this please help me. Its very urgent.
'Unfortunately', there is still a SQL6.5 sp4 server that I need to handle. I need to drop a coulmn xyz from an important table tblABC. But neither Enterprise manager could do it nor 'alter table tblABC drop column xyz' could run.
Without the hastle of makin a tblABCcopy and copy all data from tblABC except column xyz and re-do all fks; is there another way to drop the column?
I have a database with many PK and FK constraints. Is there any script I can use to drop all indexes and rebuild them taking care of PK and dependencies? I am unable to drop them by tablename order. This is on MSSQLServer-2000 SP3. Any help is appreciated.
does anyone know how to drop the last part of an ip address? I've been searching various sites for string functions and havn't come up with anything that works. What I want is something that will "find the third occurance of a "." and then return everything to the left of it"
I've tried various types of InStr and even found a SUBSTRING_INDEX function, but apparently it's only for mysql.
i'm using sql server and linking tables thru access
Hello, I have hit the wall here...can't make sense of this one.
I have a script that creates a PRIMARY KEY constraint called PK_tblDRG CODE:
ALTER TABLE [dbo].[tblDRG] ALTER COLUMN [record_id] Uniqueidentifier NOT NULL Go ALTER TABLE [dbo].[tblDRG] WITH NOCHECK ADD PK_tblDRG PRIMARY KEY CLUSTERED ( [record_id] ) WITH FILLFACTOR = 90 ON [PRIMARY]
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[PK__tblDRG]') and OBJECTPROPERTY(id, N'IsPrimaryKey') = 1) ALTER TABLE [dbo].[tblDRG] DROP CONSTRAINT PK__tblDRG
is there anyone know how to drop subscription from the publisher database without a connection to the subscriber(because the subscriber server is formated)
I'm want to be able to choose(including multiple selections) names from a drop down list that are fetched from the database(from table "Employees") and add them to table "Biotechnology".
