I need to load a database with new data, from an existing parallelsystem, but the database schema has changed and I did not make a script to do the changes. Sure wish I had. So, now I would like to copy the data from the existing parallel system into the new SQL database that has the correct schema. I have built the new database from the existing changed database. Now I would like to know if there is an easy way to clear the data out of all the tables, then copy all the data from the old schema into the new schema's tables.
Thanks!
Vic(1) to create a blank DB from an existing DB
generate a full script from the existing DB and run it. it is that easy
(2) to populate data
use DTS. just a few clicks and u r done. all matching fields will be copied automatically. u can even map fields manually for non-matching fields if u feel like.
(3) reporting differences
there r tools that can compare 2 DB and generate a report of differences|||Thank you for your reply. Because I am quite new to SQL Server, I really don't know where to go to do those "few clicks." Could you please give me a little bit more direction. I'm using SQL Server 2005.
Thanks,|||sorry, i assumed that u r on SQL 2K. i do not have much knowldge about sql 2005. all that i can say is it is having options to generate scripts for sure and it does not support DTS. others might help u with details.|||Why not just drop this new database and atttach\detach or backup\restore the version you want to move? After the move you want the schema and data to be identical in both databases right? Do it in one go rather than schema then data.|||In SQL Server Management Studio:
Management >> Legacy >> Data Transformation Services.
They're fairl self explanatory...
Add connections for both datasources and then add an Transform Data Task (black arrow/cog icon). Right click your task and go to properties.
You can write a simple SQL SELECT statement in the first tab, then select it's destination in the 2nd and then map the columns in the 3rd.
Hope that's of some help. Feel free to ask away!|||I just went back and re-read my original post and I see where I need to clarify a few points.
I'm working in SQL Server 2005. Both databases are in 2005. Older SQL Servers have not been involved.
Database one (SAC) was developed, then the need for a separate, but the same database was identified (we are opening another office in another state) So, we now have added a new database, VGS.
Sort of major modification have been made to SAC, and VGS has been left alone, except data has been being added now for 6 to 8 weeks. I should have made scripts of all the modifications to SAC, but did not. I now know better! :o
So, the challenge before me is to make a copy of SAC but with VGS data in it. I know what I want, but sure don't know my way around SQL Server enough to do it without some help.
Thanks in advance for helping.|||to generate scripts for all objects, you might try this free app I wrote, using SMO. The source is available so you can tweak it if it's not exactly what you are looking for:
http://www.elsasoft.org/tools.htm
it will also script all the data out (optionally) using bcp.exe.
I am guessing you are not using source control. you should be. scriptdb.exe will help you with that because it generates a separate file for each object, which you can then check in to your favorite source control system.
Showing posts with label script. Show all posts
Showing posts with label script. Show all posts
Tuesday, March 27, 2012
cleansing data
I have a table with 1.5 million rows. Each field has a text qualifier of " ". Could you please tell me if and how I can write a script to remove the quotes from each field?
Thank you. ;)Using which database engine? The string operations aren't very standard between engines, so which engine makes a considerable difference.
-PatP|||Using MS SQL Server 2000|||Hi mary10k, try this
update tablename set filedname=replace(filedname,""" ""","")
Madhivanan
Thank you. ;)Using which database engine? The string operations aren't very standard between engines, so which engine makes a considerable difference.
-PatP|||Using MS SQL Server 2000|||Hi mary10k, try this
update tablename set filedname=replace(filedname,""" ""","")
Madhivanan
Sunday, March 11, 2012
Choose database dinamically
Hello,
Can you tell me how can i choose one database dinamically
to execute one script
ex:
use @.database_name
go
It's possible?
Best regardsAs you probably know, USE does not accept a variable. There might be ways to accomplish what you
want to do, but you don't post what your scenario is...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:206de01c45933$c3ebc830$a501280a@.phx.gbl...
> Hello,
> Can you tell me how can i choose one database dinamically
> to execute one script
> ex:
> use @.database_name
> go
> It's possible?
> Best regards|||not really. Not without Dynamic SQL concatenated at run time and executed
via sp_ExecuteSQL
Greg Jackson
PDX, Oregon|||Hi,
Use the belo sample.
DECLARE @.DBNAME VARCHAR(30)
DECLARE @.SQL NVARCHAR(4000)
SET @.DBNAME = 'TEST'
SET @.SQL = 'USE ' + @.DBNAME + ' SELECT * FROM sysobjects'
EXEC SP_EXECUTESQL @.SQL
--
Thanks
Hari
MCDBA
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:206de01c45933$c3ebc830$a501280a@.phx.gbl...
> Hello,
> Can you tell me how can i choose one database dinamically
> to execute one script
> ex:
> use @.database_name
> go
> It's possible?
> Best regards
Can you tell me how can i choose one database dinamically
to execute one script
ex:
use @.database_name
go
It's possible?
Best regardsAs you probably know, USE does not accept a variable. There might be ways to accomplish what you
want to do, but you don't post what your scenario is...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:206de01c45933$c3ebc830$a501280a@.phx.gbl...
> Hello,
> Can you tell me how can i choose one database dinamically
> to execute one script
> ex:
> use @.database_name
> go
> It's possible?
> Best regards|||not really. Not without Dynamic SQL concatenated at run time and executed
via sp_ExecuteSQL
Greg Jackson
PDX, Oregon|||Hi,
Use the belo sample.
DECLARE @.DBNAME VARCHAR(30)
DECLARE @.SQL NVARCHAR(4000)
SET @.DBNAME = 'TEST'
SET @.SQL = 'USE ' + @.DBNAME + ' SELECT * FROM sysobjects'
EXEC SP_EXECUTESQL @.SQL
--
Thanks
Hari
MCDBA
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:206de01c45933$c3ebc830$a501280a@.phx.gbl...
> Hello,
> Can you tell me how can i choose one database dinamically
> to execute one script
> ex:
> use @.database_name
> go
> It's possible?
> Best regards
Wednesday, March 7, 2012
Checksum computation help
Please execute the script below to understand the problem -
--
create table test(id int, col1 int,col2 varchar(5),col3 datetime)
create table test2(id int, col1 int,col2 varchar(5),col3 datetime)
--id & col1 make up the PK.
insert test values(4,4,'d','02/06/2004')
insert test values(4,4,'e','02/06/2004')
insert test2 values(4,4,'d','02/06/2004')
insert test2 values(4,4,'e','02/06/2004')
select *
from test
select *
from test2
--The rows are identical.
--Script A
select t.*
from test t
join test2 t2 on t2.id=t.id
where CHECKSUM(t.col2,t.col3)<>CHECKSUM(t2.col2,t2.col3)
--The purpose of the above script is to check for any updates in the two tables. It returns two rows. But as you can see both these rows were present in the table before. So I modify the script to -
--SCRIPT B
select t.*
from test t
join test2 t2 on t2.col2=t.col2
where CHECKSUM(t.col3)<>CHECKSUM(t2.col3)
-- In this case no row is returned.This is exactly what I need. The problem - Now execute the script below.
TRUNCATE TABLE TEST
TRUNCATE TABLE TEST2
insert test values(4,4,'d','02/06/2004')
insert test values(4,4,'d','02/01/2004')
insert test2 values(4,4,'d','02/06/2004')
insert test2 values(4,4,'d','02/01/2004')
--Now when I execute script B two rows are returned which is not what I want. Since the rows are identical no row should be returned. So depending on what column changes (col2 or col3), I have to alter the script. I seek advise on the method to calculate checksum. Again the PK is ID and Col1 only.
Thanks
drop table test
drop table test2
go
--Script B is not correct because you have no keys in tables and, of course, it returns rows - col3s are different. There is relation many to many.|||And did you look up CHECKSUM() in BOL?
I know you're trying to accomplish something...but you got me lost..
It's in the same manner as your previous threads...
Can you give us a "big picture" view of what you're trying to accomplish?
I don't mean to offend, but you need to understan what primary keys are for...sounds like your data model is not fitting in quite right with what you're trying to accomplish...|||I think this would give you an idea of the data. Yesterday when I did the processing I had this view of the table -
ID...County...Univ...Dept.....Status
1...A......XYZ...Accounting...Processed - Good
1...A......ABC...Accounting...Processed - Bad
1...A......XYZ...Marketing...Processed - Good
1...B......PQR...HR............Processed - Good
1...C......XXX...HR............Processed - Bad
I have an index on the Status field coz I can see all Bad records on top.
Today I have in my source system -
ID...County...Univ...Dept
1...A......ABC...Accounting
1...A......XYZ...Accounting
1...A......XYZ...Marketing
1...B......PQR...HR
1...C......XXX...HR
2...C......YYY...Training
I want to process only those records that are new/updated since yesterday's version. I get the above records in a separate table and assign a Status to them as 'Not Processed'. I then compare the two tables. And so because of the problem stated before, I end up processing a record that I have processed the previous day.
So how do I go about this problem? Is there a need for another column in here.
--
create table test(id int, col1 int,col2 varchar(5),col3 datetime)
create table test2(id int, col1 int,col2 varchar(5),col3 datetime)
--id & col1 make up the PK.
insert test values(4,4,'d','02/06/2004')
insert test values(4,4,'e','02/06/2004')
insert test2 values(4,4,'d','02/06/2004')
insert test2 values(4,4,'e','02/06/2004')
select *
from test
select *
from test2
--The rows are identical.
--Script A
select t.*
from test t
join test2 t2 on t2.id=t.id
where CHECKSUM(t.col2,t.col3)<>CHECKSUM(t2.col2,t2.col3)
--The purpose of the above script is to check for any updates in the two tables. It returns two rows. But as you can see both these rows were present in the table before. So I modify the script to -
--SCRIPT B
select t.*
from test t
join test2 t2 on t2.col2=t.col2
where CHECKSUM(t.col3)<>CHECKSUM(t2.col3)
-- In this case no row is returned.This is exactly what I need. The problem - Now execute the script below.
TRUNCATE TABLE TEST
TRUNCATE TABLE TEST2
insert test values(4,4,'d','02/06/2004')
insert test values(4,4,'d','02/01/2004')
insert test2 values(4,4,'d','02/06/2004')
insert test2 values(4,4,'d','02/01/2004')
--Now when I execute script B two rows are returned which is not what I want. Since the rows are identical no row should be returned. So depending on what column changes (col2 or col3), I have to alter the script. I seek advise on the method to calculate checksum. Again the PK is ID and Col1 only.
Thanks
drop table test
drop table test2
go
--Script B is not correct because you have no keys in tables and, of course, it returns rows - col3s are different. There is relation many to many.|||And did you look up CHECKSUM() in BOL?
I know you're trying to accomplish something...but you got me lost..
It's in the same manner as your previous threads...
Can you give us a "big picture" view of what you're trying to accomplish?
I don't mean to offend, but you need to understan what primary keys are for...sounds like your data model is not fitting in quite right with what you're trying to accomplish...|||I think this would give you an idea of the data. Yesterday when I did the processing I had this view of the table -
ID...County...Univ...Dept.....Status
1...A......XYZ...Accounting...Processed - Good
1...A......ABC...Accounting...Processed - Bad
1...A......XYZ...Marketing...Processed - Good
1...B......PQR...HR............Processed - Good
1...C......XXX...HR............Processed - Bad
I have an index on the Status field coz I can see all Bad records on top.
Today I have in my source system -
ID...County...Univ...Dept
1...A......ABC...Accounting
1...A......XYZ...Accounting
1...A......XYZ...Marketing
1...B......PQR...HR
1...C......XXX...HR
2...C......YYY...Training
I want to process only those records that are new/updated since yesterday's version. I get the above records in a separate table and assign a Status to them as 'Not Processed'. I then compare the two tables. And so because of the problem stated before, I end up processing a record that I have processed the previous day.
So how do I go about this problem? Is there a need for another column in here.
CheckPoints and On Completion
I have a package that has 4 Script Tasks that are placed sequentially.
I have Task1--> Task2-->Task3-->Task4
The arrows between them are OnCompletion Arrows as opposed to the Standard OnSuccess arrows.Even if Task2 failed, it would still execute 3 and 4
the catch is that i want it such that when i run the first time and task 2 fails, then all the tasks except task2 should run which is fine, but when i rerun it. I want it such that it realises that task 2 had failed earlier, so it runs just task2.... if both 2 and 4 had failed then it should just run 2 and 4
i tired to implement it with check points, but the problemn is that if it fails at task2 it stops at task2 and does not continue to execute tasks 3 &4... when u rerun it starts at 2 but like i said i would like 3 & 4 to have completed the previous run...
Any suggestions would be helpful
Thanks for any help in advance..
smathew
So do you really need the condition to start the next task? It seems that if you would just like to rerun the tasks that failed you don't REALLY have any constraints on when the tasks should start... Or is this wrong?
If that is correct you can probably just get rid of the constraints and have four unconnected tasks which would (should?) rerun the correct tasks via the checkpoints...
|||well the scripts call child packages, where the child packages are the same, but they call with different variables, so if they are called in parallel and they execute parallel, then since they load the same set of tables, it could end up in a blocking situation..
smathew
|||Just an idea, try saving the status of relevant tasks (2, 4) in variable(s) and save those variable(s) in the config file or a flat file at the start read the prevously saved status from that file and based on the value of the variable branch as required.
|||Hi,
Interesting problem. There's a possible solution here: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=464381&SiteID=1
Its a pretty funky little method - very clever indeed.
-Jamie
|||That is just about the exact solution I came up with after looking at this for the past hour or two...
No fun GUI point and click, but probably the best you will do with this type of situation.
Saturday, February 25, 2012
checkpoint command and backup log
This is maybe a dumb question but I couldn't find a definitive answer
on BOL.
on BOL.
Looking at my backup script. If I issue a CHECKPOINT, does this truly
force all transaction log entries to the data file? Therefore, making
it unnecessary to BACKUP log (just BACKUP database is needed).
LouisLouis (louisducnguyen@.gmail.com) writes:
> This is maybe a dumb question but I couldn't find a definitive answer
> on BOL.
> Looking at my backup script. If I issue a CHECKPOINT, does this truly
> force all transaction log entries to the data file? Therefore, making
> it unnecessary to BACKUP log (just BACKUP database is needed).
The answer to your actual question may be yes, but the answer to the
implicit question about BACKUP log to be unnecessary is no.
Either you are running your database in simple recovery mode, in which
case you don't have to backup the log anyway.
Or you are running your database in full or hulk-logged mode, in which
case you must backup the log, or else the log till continue to grow. And
if you want up-to-the-point recovery, you want the transaction log.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
Checking to see if SQL Server on machine is up and running
Hi All,
i have a doos script that downloads from an ftp site and extracts data from
zip files before running a DTS Package.
my question is; is there a way to check to see if SQL is running and if not
start it?
any help woudl be appreciated
Simon Whale
Hi,
Take a look into this script. I have not tested this.
http://www.softtreetech.com/24x7/archive/35.htm
Thanks
Hari
SQL Server MVP
"simon whale" <hell@.nospam.com> wrote in message
news:%23M8$s1J5GHA.1012@.TK2MSFTNGP05.phx.gbl...
> Hi All,
> i have a doos script that downloads from an ftp site and extracts data
> from zip files before running a DTS Package.
> my question is; is there a way to check to see if SQL is running and if
> not start it?
> any help woudl be appreciated
>
> Simon Whale
>
i have a doos script that downloads from an ftp site and extracts data from
zip files before running a DTS Package.
my question is; is there a way to check to see if SQL is running and if not
start it?
any help woudl be appreciated
Simon Whale
Hi,
Take a look into this script. I have not tested this.
http://www.softtreetech.com/24x7/archive/35.htm
Thanks
Hari
SQL Server MVP
"simon whale" <hell@.nospam.com> wrote in message
news:%23M8$s1J5GHA.1012@.TK2MSFTNGP05.phx.gbl...
> Hi All,
> i have a doos script that downloads from an ftp site and extracts data
> from zip files before running a DTS Package.
> my question is; is there a way to check to see if SQL is running and if
> not start it?
> any help woudl be appreciated
>
> Simon Whale
>
Checking to see if SQL Server on machine is up and running
Hi All,
i have a doos script that downloads from an ftp site and extracts data from
zip files before running a DTS Package.
my question is; is there a way to check to see if SQL is running and if not
start it?
any help woudl be appreciated
Simon WhaleHi,
Take a look into this script. I have not tested this.
http://www.softtreetech.com/24x7/archive/35.htm
Thanks
Hari
SQL Server MVP
"simon whale" <hell@.nospam.com> wrote in message
news:%23M8$s1J5GHA.1012@.TK2MSFTNGP05.phx.gbl...
> Hi All,
> i have a doos script that downloads from an ftp site and extracts data
> from zip files before running a DTS Package.
> my question is; is there a way to check to see if SQL is running and if
> not start it?
> any help woudl be appreciated
>
> Simon Whale
>
i have a doos script that downloads from an ftp site and extracts data from
zip files before running a DTS Package.
my question is; is there a way to check to see if SQL is running and if not
start it?
any help woudl be appreciated
Simon WhaleHi,
Take a look into this script. I have not tested this.
http://www.softtreetech.com/24x7/archive/35.htm
Thanks
Hari
SQL Server MVP
"simon whale" <hell@.nospam.com> wrote in message
news:%23M8$s1J5GHA.1012@.TK2MSFTNGP05.phx.gbl...
> Hi All,
> i have a doos script that downloads from an ftp site and extracts data
> from zip files before running a DTS Package.
> my question is; is there a way to check to see if SQL is running and if
> not start it?
> any help woudl be appreciated
>
> Simon Whale
>
Friday, February 24, 2012
Checking to see if SQL Server on machine is up and running
Hi All,
i have a doos script that downloads from an ftp site and extracts data from
zip files before running a DTS Package.
my question is; is there a way to check to see if SQL is running and if not
start it?
any help woudl be appreciated
Simon WhaleHi,
Take a look into this script. I have not tested this.
http://www.softtreetech.com/24x7/archive/35.htm
Thanks
Hari
SQL Server MVP
"simon whale" <hell@.nospam.com> wrote in message
news:%23M8$s1J5GHA.1012@.TK2MSFTNGP05.phx.gbl...
> Hi All,
> i have a doos script that downloads from an ftp site and extracts data
> from zip files before running a DTS Package.
> my question is; is there a way to check to see if SQL is running and if
> not start it?
> any help woudl be appreciated
>
> Simon Whale
>
i have a doos script that downloads from an ftp site and extracts data from
zip files before running a DTS Package.
my question is; is there a way to check to see if SQL is running and if not
start it?
any help woudl be appreciated
Simon WhaleHi,
Take a look into this script. I have not tested this.
http://www.softtreetech.com/24x7/archive/35.htm
Thanks
Hari
SQL Server MVP
"simon whale" <hell@.nospam.com> wrote in message
news:%23M8$s1J5GHA.1012@.TK2MSFTNGP05.phx.gbl...
> Hi All,
> i have a doos script that downloads from an ftp site and extracts data
> from zip files before running a DTS Package.
> my question is; is there a way to check to see if SQL is running and if
> not start it?
> any help woudl be appreciated
>
> Simon Whale
>
Checking the state of a variable as first step of package?
Am I correct in thinking that I need to place a dummy script component which does nothing in order to route logic depending on the value of a variable?
ie. I want to output to a file if a Parent variable is a certain value, or i want to load a table if its a different value.
Unfortunately, yes.
Sunday, February 19, 2012
Checking Foreign Keys in script
I have the following in an update script.
ALTER TABLE [dbo].[SoftwareRentalMaster] ADD
CONSTRAINT [FK_SoftwareRentalMaster_ProductLookup] FOREIGN KEY
(
[ProductCode]
) REFERENCES [dbo].[ProductLookup] (
[ProductCode]
)
GO
I would like to check if the FOREIGN KEY exists and delete or use an if
statement to prevent the following error messages
Server: Msg 2714, Level 16, State 4, Line 2
There is already an object named 'FK_SoftwareRentalMaster_ProductLookup' in
the database.
Server: Msg 1750, Level 16, State 1, Line 2
Could not create constraint. See previous errors.
How can i do this
Regards
Jeff
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.691 / Virus Database: 452 - Release Date: 26/05/2004
Hi,
Use the below sample of script. Replace the table name, constraint name and
column names based on ur requirement.
if exists(select constraint_name from
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS where constraint_name ='fk_i')
Begin
select 'Foreign Key exists'
select 'dropping the Foreign Key constraint'
alter table x_2 drop constraint fk_i
end
select 'Creating the Foreign key constraint back to table'
Alter table x_2 add constraint fK_i foreign key (i) references x_1(i)
Thanks
Hari
MCDBA
"Jeff Williams" <jeff.williams@.hardsoft.com.au> wrote in message
news:uKPPPvFREHA.3728@.TK2MSFTNGP10.phx.gbl...
> I have the following in an update script.
> ALTER TABLE [dbo].[SoftwareRentalMaster] ADD
> CONSTRAINT [FK_SoftwareRentalMaster_ProductLookup] FOREIGN KEY
> (
> [ProductCode]
> ) REFERENCES [dbo].[ProductLookup] (
> [ProductCode]
> )
> GO
> I would like to check if the FOREIGN KEY exists and delete or use an if
> statement to prevent the following error messages
> Server: Msg 2714, Level 16, State 4, Line 2
> There is already an object named 'FK_SoftwareRentalMaster_ProductLookup'
in
> the database.
> Server: Msg 1750, Level 16, State 1, Line 2
> Could not create constraint. See previous errors.
> How can i do this
> Regards
> Jeff
>
> --
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.691 / Virus Database: 452 - Release Date: 26/05/2004
>
ALTER TABLE [dbo].[SoftwareRentalMaster] ADD
CONSTRAINT [FK_SoftwareRentalMaster_ProductLookup] FOREIGN KEY
(
[ProductCode]
) REFERENCES [dbo].[ProductLookup] (
[ProductCode]
)
GO
I would like to check if the FOREIGN KEY exists and delete or use an if
statement to prevent the following error messages
Server: Msg 2714, Level 16, State 4, Line 2
There is already an object named 'FK_SoftwareRentalMaster_ProductLookup' in
the database.
Server: Msg 1750, Level 16, State 1, Line 2
Could not create constraint. See previous errors.
How can i do this
Regards
Jeff
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.691 / Virus Database: 452 - Release Date: 26/05/2004
Hi,
Use the below sample of script. Replace the table name, constraint name and
column names based on ur requirement.
if exists(select constraint_name from
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS where constraint_name ='fk_i')
Begin
select 'Foreign Key exists'
select 'dropping the Foreign Key constraint'
alter table x_2 drop constraint fk_i
end
select 'Creating the Foreign key constraint back to table'
Alter table x_2 add constraint fK_i foreign key (i) references x_1(i)
Thanks
Hari
MCDBA
"Jeff Williams" <jeff.williams@.hardsoft.com.au> wrote in message
news:uKPPPvFREHA.3728@.TK2MSFTNGP10.phx.gbl...
> I have the following in an update script.
> ALTER TABLE [dbo].[SoftwareRentalMaster] ADD
> CONSTRAINT [FK_SoftwareRentalMaster_ProductLookup] FOREIGN KEY
> (
> [ProductCode]
> ) REFERENCES [dbo].[ProductLookup] (
> [ProductCode]
> )
> GO
> I would like to check if the FOREIGN KEY exists and delete or use an if
> statement to prevent the following error messages
> Server: Msg 2714, Level 16, State 4, Line 2
> There is already an object named 'FK_SoftwareRentalMaster_ProductLookup'
in
> the database.
> Server: Msg 1750, Level 16, State 1, Line 2
> Could not create constraint. See previous errors.
> How can i do this
> Regards
> Jeff
>
> --
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.691 / Virus Database: 452 - Release Date: 26/05/2004
>
Checking Foreign Keys in script
I have the following in an update script.
ALTER TABLE [dbo].[SoftwareRentalMaster] ADD
CONSTRAINT [FK_SoftwareRentalMaster_ProductLookup] FOREIGN KEY
(
[ProductCode]
) REFERENCES [dbo].[ProductLookup] (
[ProductCode]
)
GO
I would like to check if the FOREIGN KEY exists and delete or use an if
statement to prevent the following error messages
Server: Msg 2714, Level 16, State 4, Line 2
There is already an object named 'FK_SoftwareRentalMaster_ProductLookup' in
the database.
Server: Msg 1750, Level 16, State 1, Line 2
Could not create constraint. See previous errors.
How can i do this
Regards
Jeff
--
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.691 / Virus Database: 452 - Release Date: 26/05/2004Hi,
Use the below sample of script. Replace the table name, constraint name and
column names based on ur requirement.
if exists(select constraint_name from
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS where constraint_name ='fk_i')
Begin
select 'Foreign Key exists'
select 'dropping the Foreign Key constraint'
alter table x_2 drop constraint fk_i
end
select 'Creating the Foreign key constraint back to table'
Alter table x_2 add constraint fK_i foreign key (i) references x_1(i)
Thanks
Hari
MCDBA
"Jeff Williams" <jeff.williams@.hardsoft.com.au> wrote in message
news:uKPPPvFREHA.3728@.TK2MSFTNGP10.phx.gbl...
> I have the following in an update script.
> ALTER TABLE [dbo].[SoftwareRentalMaster] ADD
> CONSTRAINT [FK_SoftwareRentalMaster_ProductLookup] FOREIGN KEY
> (
> [ProductCode]
> ) REFERENCES [dbo].[ProductLookup] (
> [ProductCode]
> )
> GO
> I would like to check if the FOREIGN KEY exists and delete or use an if
> statement to prevent the following error messages
> Server: Msg 2714, Level 16, State 4, Line 2
> There is already an object named 'FK_SoftwareRentalMaster_ProductLookup'
in
> the database.
> Server: Msg 1750, Level 16, State 1, Line 2
> Could not create constraint. See previous errors.
> How can i do this
> Regards
> Jeff
>
> --
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.691 / Virus Database: 452 - Release Date: 26/05/2004
>
ALTER TABLE [dbo].[SoftwareRentalMaster] ADD
CONSTRAINT [FK_SoftwareRentalMaster_ProductLookup] FOREIGN KEY
(
[ProductCode]
) REFERENCES [dbo].[ProductLookup] (
[ProductCode]
)
GO
I would like to check if the FOREIGN KEY exists and delete or use an if
statement to prevent the following error messages
Server: Msg 2714, Level 16, State 4, Line 2
There is already an object named 'FK_SoftwareRentalMaster_ProductLookup' in
the database.
Server: Msg 1750, Level 16, State 1, Line 2
Could not create constraint. See previous errors.
How can i do this
Regards
Jeff
--
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.691 / Virus Database: 452 - Release Date: 26/05/2004Hi,
Use the below sample of script. Replace the table name, constraint name and
column names based on ur requirement.
if exists(select constraint_name from
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS where constraint_name ='fk_i')
Begin
select 'Foreign Key exists'
select 'dropping the Foreign Key constraint'
alter table x_2 drop constraint fk_i
end
select 'Creating the Foreign key constraint back to table'
Alter table x_2 add constraint fK_i foreign key (i) references x_1(i)
Thanks
Hari
MCDBA
"Jeff Williams" <jeff.williams@.hardsoft.com.au> wrote in message
news:uKPPPvFREHA.3728@.TK2MSFTNGP10.phx.gbl...
> I have the following in an update script.
> ALTER TABLE [dbo].[SoftwareRentalMaster] ADD
> CONSTRAINT [FK_SoftwareRentalMaster_ProductLookup] FOREIGN KEY
> (
> [ProductCode]
> ) REFERENCES [dbo].[ProductLookup] (
> [ProductCode]
> )
> GO
> I would like to check if the FOREIGN KEY exists and delete or use an if
> statement to prevent the following error messages
> Server: Msg 2714, Level 16, State 4, Line 2
> There is already an object named 'FK_SoftwareRentalMaster_ProductLookup'
in
> the database.
> Server: Msg 1750, Level 16, State 1, Line 2
> Could not create constraint. See previous errors.
> How can i do this
> Regards
> Jeff
>
> --
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.691 / Virus Database: 452 - Release Date: 26/05/2004
>
Checking Foreign Keys in script
I have the following in an update script.
ALTER TABLE [dbo].[SoftwareRentalMaster] ADD
CONSTRAINT & #91;FK_SoftwareRentalMaster_ProductLooku
p] FOREIGN KEY
(
[ProductCode]
) REFERENCES [dbo].[ProductLookup] (
[ProductCode]
)
GO
I would like to check if the FOREIGN KEY exists and delete or use an if
statement to prevent the following error messages
Server: Msg 2714, Level 16, State 4, Line 2
There is already an object named 'FK_SoftwareRentalMaster_ProductLookup' in
the database.
Server: Msg 1750, Level 16, State 1, Line 2
Could not create constraint. See previous errors.
How can i do this
Regards
Jeff
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.691 / Virus Database: 452 - Release Date: 26/05/2004Hi,
Use the below sample of script. Replace the table name, constraint name and
column names based on ur requirement.
if exists(select constraint_name from
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS where constraint_name ='fk_i')
Begin
select 'Foreign Key exists'
select 'dropping the Foreign Key constraint'
alter table x_2 drop constraint fk_i
end
select 'Creating the Foreign key constraint back to table'
Alter table x_2 add constraint fK_i foreign key (i) references x_1(i)
Thanks
Hari
MCDBA
"Jeff Williams" <jeff.williams@.hardsoft.com.au> wrote in message
news:uKPPPvFREHA.3728@.TK2MSFTNGP10.phx.gbl...
> I have the following in an update script.
> ALTER TABLE [dbo].[SoftwareRentalMaster] ADD
> CONSTRAINT & #91;FK_SoftwareRentalMaster_ProductLooku
p] FOREIGN KEY
> (
> [ProductCode]
> ) REFERENCES [dbo].[ProductLookup] (
> [ProductCode]
> )
> GO
> I would like to check if the FOREIGN KEY exists and delete or use an if
> statement to prevent the following error messages
> Server: Msg 2714, Level 16, State 4, Line 2
> There is already an object named 'FK_SoftwareRentalMaster_ProductLookup'[
/vbcol]
in[vbcol=seagreen]
> the database.
> Server: Msg 1750, Level 16, State 1, Line 2
> Could not create constraint. See previous errors.
> How can i do this
> Regards
> Jeff
>
> --
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.691 / Virus Database: 452 - Release Date: 26/05/2004
>
ALTER TABLE [dbo].[SoftwareRentalMaster] ADD
CONSTRAINT & #91;FK_SoftwareRentalMaster_ProductLooku
p] FOREIGN KEY
(
[ProductCode]
) REFERENCES [dbo].[ProductLookup] (
[ProductCode]
)
GO
I would like to check if the FOREIGN KEY exists and delete or use an if
statement to prevent the following error messages
Server: Msg 2714, Level 16, State 4, Line 2
There is already an object named 'FK_SoftwareRentalMaster_ProductLookup' in
the database.
Server: Msg 1750, Level 16, State 1, Line 2
Could not create constraint. See previous errors.
How can i do this
Regards
Jeff
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.691 / Virus Database: 452 - Release Date: 26/05/2004Hi,
Use the below sample of script. Replace the table name, constraint name and
column names based on ur requirement.
if exists(select constraint_name from
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS where constraint_name ='fk_i')
Begin
select 'Foreign Key exists'
select 'dropping the Foreign Key constraint'
alter table x_2 drop constraint fk_i
end
select 'Creating the Foreign key constraint back to table'
Alter table x_2 add constraint fK_i foreign key (i) references x_1(i)
Thanks
Hari
MCDBA
"Jeff Williams" <jeff.williams@.hardsoft.com.au> wrote in message
news:uKPPPvFREHA.3728@.TK2MSFTNGP10.phx.gbl...
> I have the following in an update script.
> ALTER TABLE [dbo].[SoftwareRentalMaster] ADD
> CONSTRAINT & #91;FK_SoftwareRentalMaster_ProductLooku
p] FOREIGN KEY
> (
> [ProductCode]
> ) REFERENCES [dbo].[ProductLookup] (
> [ProductCode]
> )
> GO
> I would like to check if the FOREIGN KEY exists and delete or use an if
> statement to prevent the following error messages
> Server: Msg 2714, Level 16, State 4, Line 2
> There is already an object named 'FK_SoftwareRentalMaster_ProductLookup'[
/vbcol]
in[vbcol=seagreen]
> the database.
> Server: Msg 1750, Level 16, State 1, Line 2
> Could not create constraint. See previous errors.
> How can i do this
> Regards
> Jeff
>
> --
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.691 / Virus Database: 452 - Release Date: 26/05/2004
>
Thursday, February 16, 2012
Checking for replication when applying schema changes
All
Is there some simple way to check if a database is being replicated that I
can use in a script? I have 2 copies of a production database, one under
replication and one not, and I would like to have any schema change scripts
check for replication to see which action to take. E.g.
<script>
If <database is replicating>
sp_addreplcolumn ...
Else
Alter Table...
</script>
TIA
Ron Lounsbury
sp_dboption 'pubs','published'
GO
sp_dboption 'pubs','merge publish'
GO
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Ron L" <ronl@.bogus.Address.com> wrote in message
news:uY5tItmUGHA.4764@.TK2MSFTNGP11.phx.gbl...
> All
> Is there some simple way to check if a database is being replicated that I
> can use in a script? I have 2 copies of a production database, one under
> replication and one not, and I would like to have any schema change
> scripts check for replication to see which action to take. E.g.
> <script>
> If <database is replicating>
> sp_addreplcolumn ...
> Else
> Alter Table...
> </script>
> TIA
> Ron Lounsbury
>
|||Hilary
Thanks for the reply. Unfortunately, when I run that against a database
that is participating in replication and one that is not, both give me a
result for the "CurrentSetting" of OFF. This occurs for both "Merge
Publish" and "Publish". Is there something I am missing? I am running SQL
Server 2000, SP 3a (with some hotfixes).
Thanks,
Ron Lounsbury
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:edTAbLnUGHA.4740@.TK2MSFTNGP14.phx.gbl...
> sp_dboption 'pubs','published'
> GO
> sp_dboption 'pubs','merge publish'
> GO
>
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Ron L" <ronl@.bogus.Address.com> wrote in message
> news:uY5tItmUGHA.4764@.TK2MSFTNGP11.phx.gbl...
>
|||If it returns off it means these databases are not enabled for replication.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Ron L" <ronl@.bogus.Address.com> wrote in message
news:ODmoO2pUGHA.4452@.TK2MSFTNGP12.phx.gbl...
> Hilary
> Thanks for the reply. Unfortunately, when I run that against a database
> that is participating in replication and one that is not, both give me a
> result for the "CurrentSetting" of OFF. This occurs for both "Merge
> Publish" and "Publish". Is there something I am missing? I am running
> SQL Server 2000, SP 3a (with some hotfixes).
> Thanks,
> Ron Lounsbury
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:edTAbLnUGHA.4740@.TK2MSFTNGP14.phx.gbl...
>
|||Hilary
When I run this script in Query Analyzer:
USE NITSS2kDev
exec sp_dboption 'pubs','merge publish'
GO
use NITSS2kdeployed
exec sp_dboption 'pubs','merge publish'
GO
I get these results:
OptionName CurrentSetting
-- --
merge publish off
OptionName CurrentSetting
-- --
merge publish off
NITSS2kDev is actively replicating with 2 other databases, and one of the
replication jobs ran 3 minutes ago. NITSS2kDeployed is not replicating.
The script was run on the Publishing server.
Thanks,
Ron L
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:eKM%23DAqUGHA.1444@.TK2MSFTNGP11.phx.gbl...
> If it returns off it means these databases are not enabled for
> replication.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Ron L" <ronl@.bogus.Address.com> wrote in message
> news:ODmoO2pUGHA.4452@.TK2MSFTNGP12.phx.gbl...
>
|||Oh Man, My Stupid. I don't know where my brain was yesterday. When I put
the right database name in the call it works fine.
Thanks,
Ron L
"Ron L" <ronl@.bogus.Address.com> wrote in message
news:OtXBmGqUGHA.5148@.TK2MSFTNGP12.phx.gbl...
> Hilary
> When I run this script in Query Analyzer:
> USE NITSS2kDev
> exec sp_dboption 'pubs','merge publish'
> GO
> use NITSS2kdeployed
> exec sp_dboption 'pubs','merge publish'
> GO
> I get these results:
> OptionName CurrentSetting
> -- --
> merge publish off
> OptionName CurrentSetting
> -- --
> merge publish off
> NITSS2kDev is actively replicating with 2 other databases, and one of the
> replication jobs ran 3 minutes ago. NITSS2kDeployed is not replicating.
> The script was run on the Publishing server.
> Thanks,
> Ron L
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:eKM%23DAqUGHA.1444@.TK2MSFTNGP11.phx.gbl...
>
Is there some simple way to check if a database is being replicated that I
can use in a script? I have 2 copies of a production database, one under
replication and one not, and I would like to have any schema change scripts
check for replication to see which action to take. E.g.
<script>
If <database is replicating>
sp_addreplcolumn ...
Else
Alter Table...
</script>
TIA
Ron Lounsbury
sp_dboption 'pubs','published'
GO
sp_dboption 'pubs','merge publish'
GO
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Ron L" <ronl@.bogus.Address.com> wrote in message
news:uY5tItmUGHA.4764@.TK2MSFTNGP11.phx.gbl...
> All
> Is there some simple way to check if a database is being replicated that I
> can use in a script? I have 2 copies of a production database, one under
> replication and one not, and I would like to have any schema change
> scripts check for replication to see which action to take. E.g.
> <script>
> If <database is replicating>
> sp_addreplcolumn ...
> Else
> Alter Table...
> </script>
> TIA
> Ron Lounsbury
>
|||Hilary
Thanks for the reply. Unfortunately, when I run that against a database
that is participating in replication and one that is not, both give me a
result for the "CurrentSetting" of OFF. This occurs for both "Merge
Publish" and "Publish". Is there something I am missing? I am running SQL
Server 2000, SP 3a (with some hotfixes).
Thanks,
Ron Lounsbury
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:edTAbLnUGHA.4740@.TK2MSFTNGP14.phx.gbl...
> sp_dboption 'pubs','published'
> GO
> sp_dboption 'pubs','merge publish'
> GO
>
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Ron L" <ronl@.bogus.Address.com> wrote in message
> news:uY5tItmUGHA.4764@.TK2MSFTNGP11.phx.gbl...
>
|||If it returns off it means these databases are not enabled for replication.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Ron L" <ronl@.bogus.Address.com> wrote in message
news:ODmoO2pUGHA.4452@.TK2MSFTNGP12.phx.gbl...
> Hilary
> Thanks for the reply. Unfortunately, when I run that against a database
> that is participating in replication and one that is not, both give me a
> result for the "CurrentSetting" of OFF. This occurs for both "Merge
> Publish" and "Publish". Is there something I am missing? I am running
> SQL Server 2000, SP 3a (with some hotfixes).
> Thanks,
> Ron Lounsbury
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:edTAbLnUGHA.4740@.TK2MSFTNGP14.phx.gbl...
>
|||Hilary
When I run this script in Query Analyzer:
USE NITSS2kDev
exec sp_dboption 'pubs','merge publish'
GO
use NITSS2kdeployed
exec sp_dboption 'pubs','merge publish'
GO
I get these results:
OptionName CurrentSetting
-- --
merge publish off
OptionName CurrentSetting
-- --
merge publish off
NITSS2kDev is actively replicating with 2 other databases, and one of the
replication jobs ran 3 minutes ago. NITSS2kDeployed is not replicating.
The script was run on the Publishing server.
Thanks,
Ron L
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:eKM%23DAqUGHA.1444@.TK2MSFTNGP11.phx.gbl...
> If it returns off it means these databases are not enabled for
> replication.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Ron L" <ronl@.bogus.Address.com> wrote in message
> news:ODmoO2pUGHA.4452@.TK2MSFTNGP12.phx.gbl...
>
|||Oh Man, My Stupid. I don't know where my brain was yesterday. When I put
the right database name in the call it works fine.
Thanks,
Ron L
"Ron L" <ronl@.bogus.Address.com> wrote in message
news:OtXBmGqUGHA.5148@.TK2MSFTNGP12.phx.gbl...
> Hilary
> When I run this script in Query Analyzer:
> USE NITSS2kDev
> exec sp_dboption 'pubs','merge publish'
> GO
> use NITSS2kdeployed
> exec sp_dboption 'pubs','merge publish'
> GO
> I get these results:
> OptionName CurrentSetting
> -- --
> merge publish off
> OptionName CurrentSetting
> -- --
> merge publish off
> NITSS2kDev is actively replicating with 2 other databases, and one of the
> replication jobs ran 3 minutes ago. NITSS2kDeployed is not replicating.
> The script was run on the Publishing server.
> Thanks,
> Ron L
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:eKM%23DAqUGHA.1444@.TK2MSFTNGP11.phx.gbl...
>
Checking for free disk space and getting mail when it falls below a certain limit
Hello
I have a script which checks the disk space and when it falls a certain size , it mails the dba mail box.
I would like to know how I can change it , as a percentage calculation.
For example when the free space is less than 20% of the total space on the drive I should be receiving a mail.
The script I have is :
declare @.MB_Free int
create table #FreeSpace(
Drive char(1),
MB_Free int)
insert into #FreeSpace exec xp_fixeddrives
-- Free Space on F drive Less than Threshold
if @.MB_Free < 4096
exec master.dbo.xp_sendmail
@.recipients ='dvaddi@.domain.edu',
@.subject ='SERVER X - Fresh Space Issue on D Drive',
@.message = 'Free space on D Drive
has dropped below 2 gig'
drop table #freespace
Thanks
Hi Vaddi -
Why not use the Alerts feature in Performance Monitor? The Logical Disk Performance Object has a Counter for % Free Space and you can select which drive letter you'd like to monitor. Once the limit is reached, you can have it email you using a WSH script.
HTH...
checking for free disk space and getting mail , when falls below a certain limit
Hello
I have a script which checks the disk space and when it falls a certain size , it mails the dba mail box.
I would like to know how I can change it , as a percentage calculation.
For example when the free space is less than 20% of the total space on the drive I should be receiving a mail.
The script I have is :
declare @.MB_Free int
create table #FreeSpace(
Drive char(1),
MB_Free int)
insert into #FreeSpace exec xp_fixeddrives
-- Free Space on F drive Less than Threshold
if @.MB_Free < 4096
exec master.dbo.xp_sendmail
@.recipients ='dvaddi@.domain.edu',
@.subject ='SERVER X - Fresh Space Issue on D Drive',
@.message = 'Free space on D Drive
has dropped below 2 gig'
drop table #freespace
ThanksHere's what I use:
set nocount on
declare @.MB_Threshold int
set @.MB_Threshold = 102400
declare @.From varchar(500)
declare @.Subject varchar(500)
declare @.Message varchar(500)
create table #FreeSpace(Drive char(1), MB_Free int)
insert into #FreeSpace exec master..xp_fixeddrives
select @.Message = isnull(@.Message + ', ', 'The following drives have dropped below ' + cast(@.MB_Threshold as varchar(10)) + ' MB free space: ') + Drive
from #FreeSpace
where MB_Free < @.MB_Threshold
set @.From = @.@.ServerName
set @.Subject = 'Drive space warning!'
if len(@.Message) > 0
begin
exec master.dbo.xp_smtp_sendmail
@.SERVER = 'exchange.foobar.corp',
@.FROM = @.From,
@.TO = N'blindman@.dbforums.com',
@.SUBJECT = @.Subject,
@.MESSAGE = @.Message
end
drop table #FreeSpace
go
I have a script which checks the disk space and when it falls a certain size , it mails the dba mail box.
I would like to know how I can change it , as a percentage calculation.
For example when the free space is less than 20% of the total space on the drive I should be receiving a mail.
The script I have is :
declare @.MB_Free int
create table #FreeSpace(
Drive char(1),
MB_Free int)
insert into #FreeSpace exec xp_fixeddrives
-- Free Space on F drive Less than Threshold
if @.MB_Free < 4096
exec master.dbo.xp_sendmail
@.recipients ='dvaddi@.domain.edu',
@.subject ='SERVER X - Fresh Space Issue on D Drive',
@.message = 'Free space on D Drive
has dropped below 2 gig'
drop table #freespace
ThanksHere's what I use:
set nocount on
declare @.MB_Threshold int
set @.MB_Threshold = 102400
declare @.From varchar(500)
declare @.Subject varchar(500)
declare @.Message varchar(500)
create table #FreeSpace(Drive char(1), MB_Free int)
insert into #FreeSpace exec master..xp_fixeddrives
select @.Message = isnull(@.Message + ', ', 'The following drives have dropped below ' + cast(@.MB_Threshold as varchar(10)) + ' MB free space: ') + Drive
from #FreeSpace
where MB_Free < @.MB_Threshold
set @.From = @.@.ServerName
set @.Subject = 'Drive space warning!'
if len(@.Message) > 0
begin
exec master.dbo.xp_smtp_sendmail
@.SERVER = 'exchange.foobar.corp',
@.FROM = @.From,
@.TO = N'blindman@.dbforums.com',
@.SUBJECT = @.Subject,
@.MESSAGE = @.Message
end
drop table #FreeSpace
go
Checking for Existence of File fails during validation
OK. Here's my situation. I check for the existence of a dummy .txt file using a script. I send an e-mail if it does not exist and exit package. The .txt file only exists if another .xls file is present which I import. However, during the validation phase of the package, the package fails because the .xls file does not exist. Is there a way to bypass the validation step? The only solution I came up with is to have a two-step job. The first runs the file check step and sends the e-mail. The second attemps to run the package and fails. Not a very graceful exit.
In your package, make the first task a script task that checks to see if the file exists. If it does, set a package variable to indicate that it does exist. Connect that script task to the data flow task with a Success constraint. Double-click the constraint and add an expression that evaluates to True if the variable indicates that the file exists. On your data flow task, set the DelayValidation property to True.
Let me know if you need additional details.
|||Thanks for the quick response. I actually didn't use a variable, just some control flow controls to send an e-mail if the first file doesn't exist; I'll try to integrate your suggestion on setting the variables in the future. The trick for me was the delay validation flag on the data flow. Thanks again.
Checking for existence of a table that is already full-text indexe
Full-text indexing was manually set up to work on the development server.
Now, I need to write a SQL script to programmatically set up Full-text
indexing on the staging server and then on the production server.
The SQL script must be rerunnable. It should handle the scenario where
full-text indexing already exists or not, per table, as necessary.
The SQL script works on the first pass successfully (because the staging
server did not have full-text indexing).
EXEC sp_fulltext_table 'tablename', 'create', 'Catalog', 'PK_tablename'
GO
The SQL script fails on the second pass on the staging server. Because it
tries to create the index that already exists.
So, I added a statement to drop the index before creating the index.
EXEC sp_fulltext_table 'tablename', 'drop'
GO
EXEC sp_fulltext_table 'tablename', 'create', 'Catalog', 'PK_tablename'
GO
That works because it drops an existing index, then creates the index.
However, the drop statement will fail if an index doesn't exist.
So then, how do I check for the existence of a table index before dropping it?
IF EXISTS ("statement to check for existence of a table index")
BEGIN
EXEC sp_fulltext_table 'tablename', 'drop'
GO
END
EXEC sp_fulltext_table 'tablename', 'create', 'Catalog', 'PK_tablename'
GO
I looked at sp_help_fulltext_tables, but it doesn't return TRUE/FALSE that I
could use in the if statement.
EXEC sp_help_fulltext_tables 'Catalog', 'tablename'
GO
Any suggestions?
MGBloomfield,
Yes, there are some good T-SQL code examples can be found & modified for
your purposes in the procedures in KB article: 240867 (Q240867) "INF: How to
Move, Copy, and Backup Full-Text Catalog Folders and Files" at:
http://support.microsoft.com/default...b;EN-US;240867
You might also find this code useful as well:
-- To Create/Remove the Existing Full-Text Table Index, Catalog
-- If Full-Text Index exists, DROP that Index,
-- If Full-Text Index does not exist, CREATE that Index.
use pubs
go
IF OBJECTPROPERTY ( object_id('pub_info'),'TableHasActiveFulltextIndex ') = 1
BEGIN
print 'Table pub_info is Full-Text Enabled, dropping Full-Text Index &
Catalog...'
EXEC sp_fulltext_table 'pub_info', 'drop'
EXEC sp_fulltext_catalog 'PubInfo', 'drop'
END
ELSE IF OBJECTPROPERTY (
object_id('pub_info'),'TableHasActiveFulltextIndex ') = 0
BEGIN
print 'Table pub_info is NOT Full-Text Enabled, creating FT Catalog,
Index & Activating...'
EXEC sp_fulltext_catalog 'PubInfo', 'create'
EXEC sp_fulltext_table 'pub_info', 'create', 'PubInfo', 'UPKCL_pubinfo'
EXEC sp_fulltext_column 'pub_info', 'pub_id', 'add'
EXEC sp_fulltext_column 'pub_info', 'pr_info', 'add'
EXEC sp_fulltext_table 'pub_info', 'activate'
END
Regards,
John
"MGBloomfield" <MGBloomfield@.discussions.microsoft.com> wrote in message
news:F80D1509-E8EF-4B4F-9011-BA44DF7EF91C@.microsoft.com...
> Full-text indexing was manually set up to work on the development server.
> Now, I need to write a SQL script to programmatically set up Full-text
> indexing on the staging server and then on the production server.
> The SQL script must be rerunnable. It should handle the scenario where
> full-text indexing already exists or not, per table, as necessary.
> The SQL script works on the first pass successfully (because the staging
> server did not have full-text indexing).
> EXEC sp_fulltext_table 'tablename', 'create', 'Catalog', 'PK_tablename'
> GO
> The SQL script fails on the second pass on the staging server. Because it
> tries to create the index that already exists.
> So, I added a statement to drop the index before creating the index.
> EXEC sp_fulltext_table 'tablename', 'drop'
> GO
> EXEC sp_fulltext_table 'tablename', 'create', 'Catalog', 'PK_tablename'
> GO
> That works because it drops an existing index, then creates the index.
> However, the drop statement will fail if an index doesn't exist.
> So then, how do I check for the existence of a table index before dropping
it?
> IF EXISTS ("statement to check for existence of a table index")
> BEGIN
> EXEC sp_fulltext_table 'tablename', 'drop'
> GO
> END
> EXEC sp_fulltext_table 'tablename', 'create', 'Catalog', 'PK_tablename'
> GO
> I looked at sp_help_fulltext_tables, but it doesn't return TRUE/FALSE that
I
> could use in the if statement.
> EXEC sp_help_fulltext_tables 'Catalog', 'tablename'
> GO
> Any suggestions?
>
Now, I need to write a SQL script to programmatically set up Full-text
indexing on the staging server and then on the production server.
The SQL script must be rerunnable. It should handle the scenario where
full-text indexing already exists or not, per table, as necessary.
The SQL script works on the first pass successfully (because the staging
server did not have full-text indexing).
EXEC sp_fulltext_table 'tablename', 'create', 'Catalog', 'PK_tablename'
GO
The SQL script fails on the second pass on the staging server. Because it
tries to create the index that already exists.
So, I added a statement to drop the index before creating the index.
EXEC sp_fulltext_table 'tablename', 'drop'
GO
EXEC sp_fulltext_table 'tablename', 'create', 'Catalog', 'PK_tablename'
GO
That works because it drops an existing index, then creates the index.
However, the drop statement will fail if an index doesn't exist.
So then, how do I check for the existence of a table index before dropping it?
IF EXISTS ("statement to check for existence of a table index")
BEGIN
EXEC sp_fulltext_table 'tablename', 'drop'
GO
END
EXEC sp_fulltext_table 'tablename', 'create', 'Catalog', 'PK_tablename'
GO
I looked at sp_help_fulltext_tables, but it doesn't return TRUE/FALSE that I
could use in the if statement.
EXEC sp_help_fulltext_tables 'Catalog', 'tablename'
GO
Any suggestions?
MGBloomfield,
Yes, there are some good T-SQL code examples can be found & modified for
your purposes in the procedures in KB article: 240867 (Q240867) "INF: How to
Move, Copy, and Backup Full-Text Catalog Folders and Files" at:
http://support.microsoft.com/default...b;EN-US;240867
You might also find this code useful as well:
-- To Create/Remove the Existing Full-Text Table Index, Catalog
-- If Full-Text Index exists, DROP that Index,
-- If Full-Text Index does not exist, CREATE that Index.
use pubs
go
IF OBJECTPROPERTY ( object_id('pub_info'),'TableHasActiveFulltextIndex ') = 1
BEGIN
print 'Table pub_info is Full-Text Enabled, dropping Full-Text Index &
Catalog...'
EXEC sp_fulltext_table 'pub_info', 'drop'
EXEC sp_fulltext_catalog 'PubInfo', 'drop'
END
ELSE IF OBJECTPROPERTY (
object_id('pub_info'),'TableHasActiveFulltextIndex ') = 0
BEGIN
print 'Table pub_info is NOT Full-Text Enabled, creating FT Catalog,
Index & Activating...'
EXEC sp_fulltext_catalog 'PubInfo', 'create'
EXEC sp_fulltext_table 'pub_info', 'create', 'PubInfo', 'UPKCL_pubinfo'
EXEC sp_fulltext_column 'pub_info', 'pub_id', 'add'
EXEC sp_fulltext_column 'pub_info', 'pr_info', 'add'
EXEC sp_fulltext_table 'pub_info', 'activate'
END
Regards,
John
"MGBloomfield" <MGBloomfield@.discussions.microsoft.com> wrote in message
news:F80D1509-E8EF-4B4F-9011-BA44DF7EF91C@.microsoft.com...
> Full-text indexing was manually set up to work on the development server.
> Now, I need to write a SQL script to programmatically set up Full-text
> indexing on the staging server and then on the production server.
> The SQL script must be rerunnable. It should handle the scenario where
> full-text indexing already exists or not, per table, as necessary.
> The SQL script works on the first pass successfully (because the staging
> server did not have full-text indexing).
> EXEC sp_fulltext_table 'tablename', 'create', 'Catalog', 'PK_tablename'
> GO
> The SQL script fails on the second pass on the staging server. Because it
> tries to create the index that already exists.
> So, I added a statement to drop the index before creating the index.
> EXEC sp_fulltext_table 'tablename', 'drop'
> GO
> EXEC sp_fulltext_table 'tablename', 'create', 'Catalog', 'PK_tablename'
> GO
> That works because it drops an existing index, then creates the index.
> However, the drop statement will fail if an index doesn't exist.
> So then, how do I check for the existence of a table index before dropping
it?
> IF EXISTS ("statement to check for existence of a table index")
> BEGIN
> EXEC sp_fulltext_table 'tablename', 'drop'
> GO
> END
> EXEC sp_fulltext_table 'tablename', 'create', 'Catalog', 'PK_tablename'
> GO
> I looked at sp_help_fulltext_tables, but it doesn't return TRUE/FALSE that
I
> could use in the if statement.
> EXEC sp_help_fulltext_tables 'Catalog', 'tablename'
> GO
> Any suggestions?
>
Tuesday, February 14, 2012
checking agents
I have around 25 sql servers(sql server 2000) all on windows server 2003. i would like to know if anyone has a script that will poll all the servers and check to make sure the agent is running.
thanks for your helpI have around 25 sql servers(sql server 2000) all on windows server 2003. i would like to know if anyone has a script that will poll all the servers and check to make sure the agent is running.
thanks for your help
These can help you
http://www.databasejournal.com/features/mssql/article.php/3491201
http://www.sqlservercentral.com/articles/Administering/longrunningjobs/1897/|||Try these:
exec master..xp_servicecontrol querystate, MSSQLSERVER
exec master..xp_servicecontrol querystate, SQLServerAgent
thanks for your helpI have around 25 sql servers(sql server 2000) all on windows server 2003. i would like to know if anyone has a script that will poll all the servers and check to make sure the agent is running.
thanks for your help
These can help you
http://www.databasejournal.com/features/mssql/article.php/3491201
http://www.sqlservercentral.com/articles/Administering/longrunningjobs/1897/|||Try these:
exec master..xp_servicecontrol querystate, MSSQLSERVER
exec master..xp_servicecontrol querystate, SQLServerAgent
Checking / Opinion ?
I am taking these steps in a test environment;
Publishing database
1. Changed identity columns = Not for Replication (Hilary's script)
2. Scripted all Triggers & changed to "Not for Replication" (Is there a method to do this with a script?)
3. Added Primary Keys where not present as follows;
Alter Table x
add pk_col int identity(1,1) NOT FOR REPLICATION
Alter Table x
add constraint x_tbl_repl
primary key(pk_col)
4. Backed up Publishing Database
5. Restored Publishing Database with a new name = Subscribing Database
6. Setup Transactional continuous replication from Publishing Database to Subscribing Database
The reason I did everything in the publishing database is so that it's all set in the Subscribing database.....Make sense?
I am going to have a production oltp database that replicates all transactions to my reporting database. I also want to think of my reporting database as sort of a fall back position. If anything goes wrong in the oltp publishing database, everything resides in the subscribing db.
It is my understanding that I wanted the Identity columns in the subscribing database to just be written to, not incremented, therefore, the necessity of the Not for Replication.
The same goes for triggers in the subscribing database, I don't want them firing when replication occurs, therefore, the Not for Replication.
I don't have any inserts as follows;
Insert into Table A (Table A may have a newly added pk_col )
Select * from Table B (Table B may have a newly added pk_col )
therefore, I should not have any problems with my stored procedures.
Do I have it all covered? Am I on the right track? I really want to get this implemented, and correctly, to get this monkey off my back.
Any comments will certainly be appreciated!
This is not the standard way of deploying subscribers, but it does work. I just tried it.
The problem is when you go to do a resync. When a no-sync subscription needs to be re-initialized you must drop and recreate your subscription. You have the potential of having data consistency problems with your approach.
Also, it sounds like you might be thinking you are implementing bi-directional transactional replication with your method. You aren't. Its not clear from your post if you are or not. But the "fall back position" could be interpreted as this.
If you fail over to your Subscriber you will have to manually resync your Publisher with your Subscriber when it comes back on line.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
|||I am only doing Transactional Replication in one direction. What I want is
to have a production OLTP database that replicates to a reporting database.
The reason I set everything up in the test production database is so that
these databases mirror one another from the start, and hoping I would not
miss anything.
If the schema needs to change in the production database, I intend to make
the same change in the subscriber. We have change management, where the
scripts are sent to me and I execute them over production. I am hoping that
it will just be a matter of running the scripts to create the objects
(views, sp's) in both databases to keep them in sync object wise.
I don't understand what you mean that I may face problems when doing a
resync, what am I doing wrong, or what is wrong with my approach to setting
up a reporting database, how would you approach setting up a reporting
database that is replicated to from the production database?
Am I out in left field making this project more difficult than it needs to
be?
For fall back I was thinking that since all transactions are being
replicated from production, if something went wrong in the production db, we
could just point the users to the subscribing database.
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:B8A63257-FE63-4673-AD55-9494C453404B@.microsoft.com...
> This is not the standard way of deploying subscribers, but it does work. I
just tried it.
> The problem is when you go to do a resync. When a no-sync subscription
needs to be re-initialized you must drop and recreate your subscription. You
have the potential of having data consistency problems with your approach.
> Also, it sounds like you might be thinking you are implementing
bi-directional transactional replication with your method. You aren't. Its
not clear from your post if you are or not. But the "fall back position"
could be interpreted as this.
> If you fail over to your Subscriber you will have to manually resync your
Publisher with your Subscriber when it comes back on line.
>
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
Publishing database
1. Changed identity columns = Not for Replication (Hilary's script)
2. Scripted all Triggers & changed to "Not for Replication" (Is there a method to do this with a script?)
3. Added Primary Keys where not present as follows;
Alter Table x
add pk_col int identity(1,1) NOT FOR REPLICATION
Alter Table x
add constraint x_tbl_repl
primary key(pk_col)
4. Backed up Publishing Database
5. Restored Publishing Database with a new name = Subscribing Database
6. Setup Transactional continuous replication from Publishing Database to Subscribing Database
The reason I did everything in the publishing database is so that it's all set in the Subscribing database.....Make sense?
I am going to have a production oltp database that replicates all transactions to my reporting database. I also want to think of my reporting database as sort of a fall back position. If anything goes wrong in the oltp publishing database, everything resides in the subscribing db.
It is my understanding that I wanted the Identity columns in the subscribing database to just be written to, not incremented, therefore, the necessity of the Not for Replication.
The same goes for triggers in the subscribing database, I don't want them firing when replication occurs, therefore, the Not for Replication.
I don't have any inserts as follows;
Insert into Table A (Table A may have a newly added pk_col )
Select * from Table B (Table B may have a newly added pk_col )
therefore, I should not have any problems with my stored procedures.
Do I have it all covered? Am I on the right track? I really want to get this implemented, and correctly, to get this monkey off my back.
Any comments will certainly be appreciated!
This is not the standard way of deploying subscribers, but it does work. I just tried it.
The problem is when you go to do a resync. When a no-sync subscription needs to be re-initialized you must drop and recreate your subscription. You have the potential of having data consistency problems with your approach.
Also, it sounds like you might be thinking you are implementing bi-directional transactional replication with your method. You aren't. Its not clear from your post if you are or not. But the "fall back position" could be interpreted as this.
If you fail over to your Subscriber you will have to manually resync your Publisher with your Subscriber when it comes back on line.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
|||I am only doing Transactional Replication in one direction. What I want is
to have a production OLTP database that replicates to a reporting database.
The reason I set everything up in the test production database is so that
these databases mirror one another from the start, and hoping I would not
miss anything.
If the schema needs to change in the production database, I intend to make
the same change in the subscriber. We have change management, where the
scripts are sent to me and I execute them over production. I am hoping that
it will just be a matter of running the scripts to create the objects
(views, sp's) in both databases to keep them in sync object wise.
I don't understand what you mean that I may face problems when doing a
resync, what am I doing wrong, or what is wrong with my approach to setting
up a reporting database, how would you approach setting up a reporting
database that is replicated to from the production database?
Am I out in left field making this project more difficult than it needs to
be?
For fall back I was thinking that since all transactions are being
replicated from production, if something went wrong in the production db, we
could just point the users to the subscribing database.
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:B8A63257-FE63-4673-AD55-9494C453404B@.microsoft.com...
> This is not the standard way of deploying subscribers, but it does work. I
just tried it.
> The problem is when you go to do a resync. When a no-sync subscription
needs to be re-initialized you must drop and recreate your subscription. You
have the potential of having data consistency problems with your approach.
> Also, it sounds like you might be thinking you are implementing
bi-directional transactional replication with your method. You aren't. Its
not clear from your post if you are or not. But the "fall back position"
could be interpreted as this.
> If you fail over to your Subscriber you will have to manually resync your
Publisher with your Subscriber when it comes back on line.
>
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
Subscribe to:
Posts (Atom)