How to rename a file in SSIS

I want to rename a file that is a variable to a fixed file name in a specific folder in SQL Server System Integration Services using the File System Task Editor.

Example: File1.txt to Users.txt or File2 to Users.txt. The source file is a variable the destination file is not. Here is an example of the File System Task Editor

HOW DO I SPECIFY A FIXED OR VARIABLE FOLDER NAME WITH A SPECIFIC FILE NAME???

File System Task Editor

Properties Value

Destination Connection
IsDestinationPathVariable True
DestinationVariable User::ArchivePath
OverWriteDestination True

General
Name Rename a file
Description File System Task

Operation
Operation Rename file

Source Connection
IsSourcePathVariable True
SourceVariable User::ImportFilePath

Thanks

There is more than one way to do it, the last link is run by DTS/SSIS experts you will find most of what you need. Hope this helps.

http://blogs.conchango.com/jamiethomson/archive/2005/09/14/2149.aspx

http://msdn2.microsoft.com/en-us/library/ms140185.aspx

http://www.sqlis.com

How to reduce timeouts in SQL Server?

Hi,
We have designed a system which processes millions of transactions a
year. There are a couple of tables in the system which are heavily
used. These 2 tables gets about 30,000 rows inserted into them
everyday at an average. The inserts are done through stored procedures
after perfomring some complex logic. There are also a few scheduled
jobs that run every night which accesses these 2 tables. In addition,
there are a few reports that run in the web system which acces these
table for ad-hoc reporting. In the last few months, the number of
transactions have increased quite a bit and system started giving us
problems such as a lot of timeouts and a few deadlockks everyday.
I am thinking that if I can somehow replicate these tables every 6
hours or so and then use the new tables for all reporting purposes, my
timeouts might decrease. Could you suggest on how I could go about
replicating these 2 tables in an efficient way? and whether this would
help my situation?
Any other suggestions on how I could improve my system would be
appreciated.
Thanks,
Karanouch....
tough question without being able to see your database.
here are some thoughts...
1. Make sure indexes are being optimized\maintained on a regular basis (Read
up on showContig and on dbcc IndexDefrag)
2. review your critical transactions and make sure that you are not causing
excessive blocking and deadlocks (This is really hard, you'll have to read
up on troubleshooting deadlocks)
3. yes your adhoc reporting should be moved out to another database. You can
use DTS for this, replication or log shipping. We are using log shipping to
satisfy this need.
you're likely going to need to spend some serious hours pinpointing the
problem here. My guess is that option #1 above will buy you some serious
performance gains.
Cheers,
Greg Jackson
PDX, Oregon|||Articles for reference:
http://www.microsoft.com/technet/community/chats/trans/sql/sql1023.mspx
http://vyaskn.tripod.com/watch_your_timeouts.htm
"Karan" wrote:
> Hi,
> We have designed a system which processes millions of transactions a
> year. There are a couple of tables in the system which are heavily
> used. These 2 tables gets about 30,000 rows inserted into them
> everyday at an average. The inserts are done through stored procedures
> after perfomring some complex logic. There are also a few scheduled
> jobs that run every night which accesses these 2 tables. In addition,
> there are a few reports that run in the web system which acces these
> table for ad-hoc reporting. In the last few months, the number of
> transactions have increased quite a bit and system started giving us
> problems such as a lot of timeouts and a few deadlockks everyday.
> I am thinking that if I can somehow replicate these tables every 6
> hours or so and then use the new tables for all reporting purposes, my
> timeouts might decrease. Could you suggest on how I could go about
> replicating these 2 tables in an efficient way? and whether this would
> help my situation?
> Any other suggestions on how I could improve my system would be
> appreciated.
> Thanks,
> Karan
>|||I performed the DBCC SHOWCONTIG on the tables affected and the stats
look ok to me. I am pasting the stats below..
DBCC SHOWCONTIG scanning 'claims' table...
Table: 'table_name' (322100188); index ID: 1, database ID: 7
TABLE level scan performed.
- Pages Scanned........................: 202265
- Extents Scanned.......................: 25349
- Extent Switches.......................: 27598
- Avg. Pages per Extent..................: 8.0
- Scan Density [Best Count:Actual Count]......: 91.61% [25284:27599]
- Logical Scan Fragmentation ..............: 0.64%
- Extent Scan Fragmentation ...............: 10.38%
- Avg. Bytes Free per Page................: 672.6
- Avg. Page Density (full)................: 91.69%
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
Please let me know if you see anything I could improve with this
stats. All other indexes on these table have similar stats as well.
Thanks for your help.
Karan|||looks good to me.
Red Flag would be scan density LESS THAN 80%....You look fine in that
regard.
Greg Jackson
PDX, Oregonsql

How to reduce timeouts in SQL Server?

Hi,
We have designed a system which processes millions of transactions a
year. There are a couple of tables in the system which are heavily
used. These 2 tables gets about 30,000 rows inserted into them
everyday at an average. The inserts are done through stored procedures
after perfomring some complex logic. There are also a few scheduled
jobs that run every night which accesses these 2 tables. In addition,
there are a few reports that run in the web system which acces these
table for ad-hoc reporting. In the last few months, the number of
transactions have increased quite a bit and system started giving us
problems such as a lot of timeouts and a few deadlockks everyday.
I am thinking that if I can somehow replicate these tables every 6
hours or so and then use the new tables for all reporting purposes, my
timeouts might decrease. Could you suggest on how I could go about
replicating these 2 tables in an efficient way? and whether this would
help my situation?
Any other suggestions on how I could improve my system would be
appreciated.
Thanks,
Karan
ouch....
tough question without being able to see your database.
here are some thoughts...
1. Make sure indexes are being optimized\maintained on a regular basis (Read
up on showContig and on dbcc IndexDefrag)
2. review your critical transactions and make sure that you are not causing
excessive blocking and deadlocks (This is really hard, you'll have to read
up on troubleshooting deadlocks)
3. yes your adhoc reporting should be moved out to another database. You can
use DTS for this, replication or log shipping. We are using log shipping to
satisfy this need.
you're likely going to need to spend some serious hours pinpointing the
problem here. My guess is that option #1 above will buy you some serious
performance gains.
Cheers,
Greg Jackson
PDX, Oregon
|||Articles for reference:
http://www.microsoft.com/technet/com...l/sql1023.mspx
http://vyaskn.tripod.com/watch_your_timeouts.htm
"Karan" wrote:

> Hi,
> We have designed a system which processes millions of transactions a
> year. There are a couple of tables in the system which are heavily
> used. These 2 tables gets about 30,000 rows inserted into them
> everyday at an average. The inserts are done through stored procedures
> after perfomring some complex logic. There are also a few scheduled
> jobs that run every night which accesses these 2 tables. In addition,
> there are a few reports that run in the web system which acces these
> table for ad-hoc reporting. In the last few months, the number of
> transactions have increased quite a bit and system started giving us
> problems such as a lot of timeouts and a few deadlockks everyday.
> I am thinking that if I can somehow replicate these tables every 6
> hours or so and then use the new tables for all reporting purposes, my
> timeouts might decrease. Could you suggest on how I could go about
> replicating these 2 tables in an efficient way? and whether this would
> help my situation?
> Any other suggestions on how I could improve my system would be
> appreciated.
> Thanks,
> Karan
>
|||I performed the DBCC SHOWCONTIG on the tables affected and the stats
look ok to me. I am pasting the stats below..
DBCC SHOWCONTIG scanning 'claims' table...
Table: 'table_name' (322100188); index ID: 1, database ID: 7
TABLE level scan performed.
- Pages Scanned........................: 202265
- Extents Scanned.......................: 25349
- Extent Switches.......................: 27598
- Avg. Pages per Extent..................: 8.0
- Scan Density [Best Count:Actual Count]......: 91.61% [25284:27599]
- Logical Scan Fragmentation ..............: 0.64%
- Extent Scan Fragmentation ...............: 10.38%
- Avg. Bytes Free per Page................: 672.6
- Avg. Page Density (full)................: 91.69%
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
Please let me know if you see anything I could improve with this
stats. All other indexes on these table have similar stats as well.
Thanks for your help.
Karan
|||looks good to me.
Red Flag would be scan density LESS THAN 80%....You look fine in that
regard.
Greg Jackson
PDX, Oregon

How to reduce timeouts in SQL Server?

Hi,
We have designed a system which processes millions of transactions a
year. There are a couple of tables in the system which are heavily
used. These 2 tables gets about 30,000 rows inserted into them
everyday at an average. The inserts are done through stored procedures
after perfomring some complex logic. There are also a few scheduled
jobs that run every night which accesses these 2 tables. In addition,
there are a few reports that run in the web system which acces these
table for ad-hoc reporting. In the last few months, the number of
transactions have increased quite a bit and system started giving us
problems such as a lot of timeouts and a few deadlockks everyday.
I am thinking that if I can somehow replicate these tables every 6
hours or so and then use the new tables for all reporting purposes, my
timeouts might decrease. Could you suggest on how I could go about
replicating these 2 tables in an efficient way? and whether this would
help my situation?
Any other suggestions on how I could improve my system would be
appreciated.
Thanks,
Karanouch....
tough question without being able to see your database.
here are some thoughts...
1. Make sure indexes are being optimized\maintained on a regular basis (Read
up on showContig and on dbcc IndexDefrag)
2. review your critical transactions and make sure that you are not causing
excessive blocking and deadlocks (This is really hard, you'll have to read
up on troubleshooting deadlocks)
3. yes your adhoc reporting should be moved out to another database. You can
use DTS for this, replication or log shipping. We are using log shipping to
satisfy this need.
you're likely going to need to spend some serious hours pinpointing the
problem here. My guess is that option #1 above will buy you some serious
performance gains.
Cheers,
Greg Jackson
PDX, Oregon|||Articles for reference:
http://www.microsoft.com/technet/co...ql/sql1023.mspx
http://vyaskn.tripod.com/watch_your_timeouts.htm
"Karan" wrote:

> Hi,
> We have designed a system which processes millions of transactions a
> year. There are a couple of tables in the system which are heavily
> used. These 2 tables gets about 30,000 rows inserted into them
> everyday at an average. The inserts are done through stored procedures
> after perfomring some complex logic. There are also a few scheduled
> jobs that run every night which accesses these 2 tables. In addition,
> there are a few reports that run in the web system which acces these
> table for ad-hoc reporting. In the last few months, the number of
> transactions have increased quite a bit and system started giving us
> problems such as a lot of timeouts and a few deadlockks everyday.
> I am thinking that if I can somehow replicate these tables every 6
> hours or so and then use the new tables for all reporting purposes, my
> timeouts might decrease. Could you suggest on how I could go about
> replicating these 2 tables in an efficient way? and whether this would
> help my situation?
> Any other suggestions on how I could improve my system would be
> appreciated.
> Thanks,
> Karan
>|||I performed the DBCC SHOWCONTIG on the tables affected and the stats
look ok to me. I am pasting the stats below..
DBCC SHOWCONTIG scanning 'claims' table...
Table: 'table_name' (322100188); index ID: 1, database ID: 7
TABLE level scan performed.
- Pages Scanned........................: 202265
- Extents Scanned.......................: 25349
- Extent Switches.......................: 27598
- Avg. Pages per Extent..................: 8.0
- Scan Density [Best Count:Actual Count]......: 91.61% [25284:27599
]
- Logical Scan Fragmentation ..............: 0.64%
- Extent Scan Fragmentation ...............: 10.38%
- Avg. Bytes Free per Page................: 672.6
- Avg. Page Density (full)................: 91.69%
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
Please let me know if you see anything I could improve with this
stats. All other indexes on these table have similar stats as well.
Thanks for your help.
Karan|||looks good to me.
Red Flag would be scan density LESS THAN 80%....You look fine in that
regard.
Greg Jackson
PDX, Oregon

How to re-directory of Master and model dbs

Hi All,
Anyone know how to change the directory of system dbs -- master and model dbs? Thanks.I mean changing the directory of system db data files.|||http://support.microsoft.com/default.aspx?scid=kb;en-us;224071

Tim S|||Check these links:

http://www.databasejournal.com/features/mssql/article.php/1458131
http://www.sqlteam.com/item.asp?ItemID=10060
http://support.microsoft.com/default.aspx?scid=kb;EN-US;224071|||Hi Guys,

I tried to use the infor you guys providing for me to moved the msdb, was ok. but as moving Master db, I changed the startup Parameters using new location, then tried to restart the SQL server which was failed, got error message:"Could not start the MSSqlserver service on local computer. Error 1067: The process terminated unexpectedly".

Any suggestiongs??

Thanks.|||Did you move MDF and LDF files to the location that you specified in startup parameters?|||Yes, I moved both of them.|||Did you move anything else like MODEL?|||No, I didn't move Model only Master.|||This is the steps I did:

Moving the master database
1. Change the path for the master data and log files in SQL Server Enterprise Manager.

Note You may optionally change the location of the error log here as well.
2. Right-click the SQL Server in Enterprise Manager and click Properties.
3. Click the Startup Parameters button and you will see the following entries: -dD:\MSSQL7\data\master.mdf
-eD:\MSSQL7\log\ErrorLog
-lD:\MSSQL7\data\mastlog.ldf

-d is the fully qualified path for the master database data file.

-e is the fully qualified path for the error log file.

-l is the fully qualified path for the master database log file.
4. Change these values as follows: a. Remove the current entries for the Master.mdf and Mastlog.ldf files.
b. Add new entries specifying the new location: -dE:\SQLDATA\master.mdf
-lE:\SQLDATA\mastlog.ldf



5. Stop SQL Server.
6. Copy the Master.mdf and Mastlog.ldf files to the new location (E:\Sqldata).
7. Restart SQL Server.

Now last step was failed. Failed to restart SQL server. The error is " An error 1058 -- ( The service connot be started, either because it is disabled or because it has no enabled devices asociated with it ) occurred while performing this service operation on the MSSQL server service.

Any solutions Please? Thanks.|||Hi,

I checked the service porperty--Log on button-- Hardware Profile, disabled it and enabled it , then start the service again, the previous error message just gone , but other error coming out"Could not start the MSSqlserver service on local computer. Error 1067: The process terminated unexpectedly". I checked all possibility, but still no any clues.

Help !!!! Please!!!|||Try checking the sql server logs, event viewer to have an indication of what is going wrong ...|||I checked both of them yesterday: event view was empty, and sql server error log is :

2004-10-13 14:15:30.53 spid51 Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install..

2004-10-13 14:15:30.64 spid51 Using 'xplog70.dll' version '2000.80.760' to execute extended stored procedure 'xp_msver'.

2004-10-13 14:26:20.26 spid2 SQL Server is terminating due to 'stop' request from Service Control Manager."

I tried to put the original startup parameters back at MSSQL service property window on log on button-- startup parameter.

it's still failed and nothing recorded at event view and error log too.|||I think you have a typo in the "Startup Parameters"

I Might be able to help you fix it, but need more info.

What is the exact paths to the following files?

master.mdf
ERRORLOG
mastlog.ldf

What OS are you running?

I am assuming SQL 2000 SP3, correct?

Tim S|||Thanks a lot Tim.

My OS is window 2000, and Sql server is sp3. The Mater original path is :

-dD:\Database Files\MSSQL\Data\master.mdf
-eD:\Database Files\MSSQL\Data\Log\ERRORLog
-lD:\Database Files\MSSQL\Data\mastlog.ldf

The new path I changed is:

-dD:\Database Files\Master\master.mdf
-eD:\Database Files\MSSQL\Data\Log\ERRORLog (this one was not changed)
-lD:\Log Files\Master\mastlog.ldf

Thanks.|||REGEDIT4

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\ MSSQLServer\Parameters]
"SQLArg0"="-dD:\\Database Files\\Master\\master.mdf"
"SQLArg1"="-eD:\\Database Files\\MSSQL\\Data\\Log\\ERRORLOG"
"SQLArg2"="-lD:\\Log Files\\Master\\mastlog.ldf"

Save above in a file ending with .reg
Then Click on the file This will set the default instance of SQL Server to the paths you gave for the master.

If it was just a typo should fix it.

Tim S|||Well, your error just does not make any sense, unless you misspelled the location of the files. It's that simple! No misteries! SQL Server service will start with -d and -l parameters for as long as the path specified actually contains the MDF and LDF files for master (providing nothing else got screwed up). Check the spelling and make sure you tell us the truth, otherwise, - you are making us all feel unworthy ;)|||Help!!!!!Please.|||Since you can't seem to figure out where sql server is looking for the master database, you might have to use the rebuildm utility to let sql server create a new set of system databases and then replace the newly created databases with yours by copying them over. Then you can try moving them again with the help in this thread, because it works if done right.

Be sure to stop sql sever and copy your system databases to a safe location (where they will not be overwritten !!) before running the utility. Also, read this first ... http://support.microsoft.com/default.aspx?scid=kb;en-us;273572|||Rebuild might be the last way.

I just don't get it: I check the registry, all the path in parameter folder are correct, no typo error and I tried to change back to original path, it's still not working. The error message is the same.

Any sugestions!!!!!

Thanks!

How to recreate/restore the database from the data and log files

My development system hard disk crashed and I have lost the Operating
system and the SQL executables and the system cannot be salvaged.
However at the time of the crash the server was not running and the
log files and the data files of the database were on another hard
disk. I have managed to successfully recover the data and log files of
the database.
I would greatly appreciate if you could kindly show me the
procedure/process to restore the data and log files and bring the
database to life on a new SQL instance.
Please give me the command sequence to resurrect the database.
Thanks
Karen
Karen,
1) Reinstall SQL Server and place the data files in the same directory
structure as before.
2) Apply the same service pack level that you had before
3) Stop the MSSQLServer service
4) Copy your old data files into your current DIR structure
5) Start the MSSQLServer service
It should all work beautifully.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Karen Middleton wrote:
> My development system hard disk crashed and I have lost the Operating
> system and the SQL executables and the system cannot be salvaged.
> However at the time of the crash the server was not running and the
> log files and the data files of the database were on another hard
> disk. I have managed to successfully recover the data and log files of
> the database.
> I would greatly appreciate if you could kindly show me the
> procedure/process to restore the data and log files and bring the
> database to life on a new SQL instance.
> Please give me the command sequence to resurrect the database.
> Thanks
> Karen
|||Karen,
1) Reinstall SQL Server and place the data files in the same directory
2)You can use the option 'Attach Database' In Sql EnterPrise manager to attach your log Files....Without restoring the backup (Backup the system after attaching)
3)Open the enterprise manager - Click on your Sql Server group... Click the server ->Right Click on the Databases folder You can get the Attach Database
4) Browse the your Data Files (.mdf) file... and then click on verify to see everything is fine
5) Then click on OK... Then you must be able to run your system as before
Also... You can delete .ldf file (as the depending up on the transactions this might take whole lot of GB from your harddisk..) and then attach your .mdf file..
Thanks
Ramesh...

Quote:

Originally posted by Mark Allison
Karen,
1) Reinstall SQL Server and place the data files in the same directory
structure as before.
2) Apply the same service pack level that you had before
3) Stop the MSSQLServer service
4) Copy your old data files into your current DIR structure
5) Start the MSSQLServer service
It should all work beautifully.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Karen Middleton wrote:
> My development system hard disk crashed and I have lost the Operating
> system and the SQL executables and the system cannot be salvaged.
> However at the time of the crash the server was not running and the
> log files and the data files of the database were on another hard
> disk. I have managed to successfully recover the data and log files of
> the database.
> I would greatly appreciate if you could kindly show me the
> procedure/process to restore the data and log files and bring the
> database to life on a new SQL instance.
> Please give me the command sequence to resurrect the database.
> Thanks
> Karen

How to recreate/restore the database from the data and log files

My development system hard disk crashed and I have lost the Operating
system and the SQL executables and the system cannot be salvaged.
However at the time of the crash the server was not running and the
log files and the data files of the database were on another hard
disk. I have managed to successfully recover the data and log files of
the database.
I would greatly appreciate if you could kindly show me the
procedure/process to restore the data and log files and bring the
database to life on a new SQL instance.
Please give me the command sequence to resurrect the database.
Thanks
Karen
Karen,
1) Reinstall SQL Server and place the data files in the same directory
structure as before.
2) Apply the same service pack level that you had before
3) Stop the MSSQLServer service
4) Copy your old data files into your current DIR structure
5) Start the MSSQLServer service
It should all work beautifully.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Karen Middleton wrote:
> My development system hard disk crashed and I have lost the Operating
> system and the SQL executables and the system cannot be salvaged.
> However at the time of the crash the server was not running and the
> log files and the data files of the database were on another hard
> disk. I have managed to successfully recover the data and log files of
> the database.
> I would greatly appreciate if you could kindly show me the
> procedure/process to restore the data and log files and bring the
> database to life on a new SQL instance.
> Please give me the command sequence to resurrect the database.
> Thanks
> Karen

How to recreate/restore the database from the data and log files

My development system hard disk crashed and I have lost the Operating
system and the SQL executables and the system cannot be salvaged.
However at the time of the crash the server was not running and the
log files and the data files of the database were on another hard
disk. I have managed to successfully recover the data and log files of
the database.
I would greatly appreciate if you could kindly show me the
procedure/process to restore the data and log files and bring the
database to life on a new SQL instance.
Please give me the command sequence to resurrect the database.
Thanks
KarenKaren,
1) Reinstall SQL Server and place the data files in the same directory
structure as before.
2) Apply the same service pack level that you had before
3) Stop the MSSQLServer service
4) Copy your old data files into your current DIR structure
5) Start the MSSQLServer service
It should all work beautifully.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Karen Middleton wrote:
> My development system hard disk crashed and I have lost the Operating
> system and the SQL executables and the system cannot be salvaged.
> However at the time of the crash the server was not running and the
> log files and the data files of the database were on another hard
> disk. I have managed to successfully recover the data and log files of
> the database.
> I would greatly appreciate if you could kindly show me the
> procedure/process to restore the data and log files and bring the
> database to life on a new SQL instance.
> Please give me the command sequence to resurrect the database.
> Thanks
> Karen

How to recreate/restore the database from the data and log files

My development system hard disk crashed and I have lost the Operating
system and the SQL executables and the system cannot be salvaged.
However at the time of the crash the server was not running and the
log files and the data files of the database were on another hard
disk. I have managed to successfully recover the data and log files of
the database.
I would greatly appreciate if you could kindly show me the
procedure/process to restore the data and log files and bring the
database to life on a new SQL instance.
Please give me the command sequence to resurrect the database.
Thanks
KarenKaren,
1) Reinstall SQL Server and place the data files in the same directory
structure as before.
2) Apply the same service pack level that you had before
3) Stop the MSSQLServer service
4) Copy your old data files into your current DIR structure
5) Start the MSSQLServer service
It should all work beautifully.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Karen Middleton wrote:
> My development system hard disk crashed and I have lost the Operating
> system and the SQL executables and the system cannot be salvaged.
> However at the time of the crash the server was not running and the
> log files and the data files of the database were on another hard
> disk. I have managed to successfully recover the data and log files of
> the database.
> I would greatly appreciate if you could kindly show me the
> procedure/process to restore the data and log files and bring the
> database to life on a new SQL instance.
> Please give me the command sequence to resurrect the database.
> Thanks
> Karen

How to recreate replication system objects?

Does anyone know what script I need to run to recreate the
replication system objects in MSSQL?
I believe it's in the Install Directory after installing SQL?
Will it destroy any existing publications/subscriptions on the server?
I have 2 other publications that are running I want to keep.
Thanks
RClick Replication component/ Generate SQL Script.
"Chris" <chriswinstone@.aol.com> wrote in message
news:1125399221.478160.216810@.o13g2000cwo.googlegr oups.com...
> Does anyone know what script I need to run to recreate the
> replication system objects in MSSQL?
> I believe it's in the Install Directory after installing SQL?
> Will it destroy any existing publications/subscriptions on the server?
> I have 2 other publications that are running I want to keep.
> Thanks
>
|||I'm guessing here, but did you have a failed installation? If so, then
reapplying sp4 should do the trick.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul
No I didn't have a failed installation as such. The replication was working
fine for about 18 months then all of a sudden I started getting errors from
the merge agent after executing sp_MSfetchidentityrange saying it could not
retrieve identity range resource for the table [TABLENAME].
A post from Hilary Cotter pointed me in the direction of the
msrepl_identity_range tables, however I decided to script the publication
for backup and try removing it. So I ran sp_removedreplication (probably too
hasty on that one!) then tried to run the puplication gen script thats when
I started getting the invalid object name 'syspublications' This is where I
am at at the moment. no replication at all! I can't even remove replication
manually - documented in Q324401!
I'm really stuck! Can't go forward or back! So I thought maybe recreating
the replication system objects would help me go in at least one direction!
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:uyQXnTZrFHA.464@.TK2MSFTNGP15.phx.gbl...
> I'm guessing here, but did you have a failed installation? If so, then
> reapplying sp4 should do the trick.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Chris,
what about if you disable replication then reenable, then execute your
scripts?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul
How do you do this? In QA or EM?
Thanks
Paul Ibison wrote:
> Chris,
> what about if you disable replication then reenable, then execute your
> scripts?
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
|||Chris,
in EM - 'Tools, Replication, Disable Publishing'.
HTH,
Paul Ibison
|||Thanks Paul
Thats worked!
I now have a problem when creating my scripted publication again! See later
post.
Thanks again!
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%236JW0YjrFHA.3060@.TK2MSFTNGP09.phx.gbl...
> Chris,
> in EM - 'Tools, Replication, Disable Publishing'.
> HTH,
> Paul Ibison
>

How to recover from dropped tempdb?

I am testing sql server 2005 in different disaster situations happened under 2000. I was reading a lot about no update on system tables, so:

sp_detach_db 'tempdb'

In sql 2000 i could issue something like the following command to recover:

use master

sp_configure 'allow updates', 1

reconfigure with override

go

insert sys.sysdatabases (name, dbid, sid, mode, status, status2, crdate, reserved, category, cmptlevel, filename, version)

values ('tempdb', 2, 0x01, 0, 8, 1090520064, '2007-01-27 13:03:10.873', '1900-01-01 00:00:00.000', 0, '90', 'D:\beep\mssql\temp\tempdb.mdf', 611)

go

sp_configure 'allow updates', 0

reconfigure with override

go

As tempdb is hardcoded db id 2, I do not have a chance to recover without a master backup.

Of course, this situation can be recovered using a master backup, but it is not always available.

Thank you for your comments.

How is it that you are detaching tempdb?

1> sp_detach_db tempdb
2> go
Msg 7940, Level 16, State 1:
System databases master, model, msdb, and tempdb cannot be detached.
1>

|||

Thanks for the fast response.

Yes, it is the normal behavior. But imagine the following situation:

The end-user tries to move the system databases to another disk. He reads the information on your site (http://support.microsoft.com/kb/224071/) and starts the server with /T3608. He executes the procedure step by step, moves msdb and model, forgets to read the remaining part and happy to move tempdb the same way.

This is the point, when you are getting involved. How to proceed?

|||Well, that script won'y work with SQL Server 2005. You can not allowed to make direct updates to system tables. You can't drop or detach a system database, so the only way of having an issue with tempdb is that it either becomes damaged while SQL Server is running in which case the SQL Server will go offline and fixing the issue is a matter of restarting SQL Server or you have an issue while starting up SQL Server in which case the SQL Server won't start and you would then need to go through the process documented in BOL for recreating tempdb.|||I have asked that the article be updated to remove SQL 2005 from the section that does the moves via detach. ALTER DATABASE should be used to change the file paths in these cases and there are detailed instructions in books online.|||

Thank you for your comment.

You _can_ detach the tempdb, if you start SQL Server 2005 with the /T3608 parameter [I have tried before posting the question]. From the point tempdb detached it cannot be re-attached, nor can be re-created using the way you mention, as the row with hardcoded database id "2" does not exists in the sysdatabases table.

|||

Thank you.

For closing the issue can we say, that the only way to recover from this situation is attaching a clean master db and re-attach the databases and re-create all global settings (logins, endpoints, etc.) stored in master?

|||

If you have a backup of master, you could restore that after putting in the clean master. Master is usually small enough that it should not be too big a burden to do full backups on a regular basis.

And if you had truly followed the article, the first step is

?

Make a current backup of all databases, especially master, from their current location.

which should allow you to recover.

How to recover from dropped tempdb?

I am testing sql server 2005 in different disaster situations happened under 2000. I was reading a lot about no update on system tables, so:

sp_detach_db 'tempdb'

In sql 2000 i could issue something like the following command to recover:

use master

sp_configure 'allow updates', 1

reconfigure with override

go

insert sys.sysdatabases (name, dbid, sid, mode, status, status2, crdate, reserved, category, cmptlevel, filename, version)

values ('tempdb', 2, 0x01, 0, 8, 1090520064, '2007-01-27 13:03:10.873', '1900-01-01 00:00:00.000', 0, '90', 'D:\beep\mssql\temp\tempdb.mdf', 611)

go

sp_configure 'allow updates', 0

reconfigure with override

go

As tempdb is hardcoded db id 2, I do not have a chance to recover without a master backup.

Of course, this situation can be recovered using a master backup, but it is not always available.

Thank you for your comments.

How is it that you are detaching tempdb?

1> sp_detach_db tempdb
2> go
Msg 7940, Level 16, State 1:
System databases master, model, msdb, and tempdb cannot be detached.
1>

|||

Thanks for the fast response.

Yes, it is the normal behavior. But imagine the following situation:

The end-user tries to move the system databases to another disk. He reads the information on your site (http://support.microsoft.com/kb/224071/) and starts the server with /T3608. He executes the procedure step by step, moves msdb and model, forgets to read the remaining part and happy to move tempdb the same way.

This is the point, when you are getting involved. How to proceed?

|||Well, that script won'y work with SQL Server 2005. You can not allowed to make direct updates to system tables. You can't drop or detach a system database, so the only way of having an issue with tempdb is that it either becomes damaged while SQL Server is running in which case the SQL Server will go offline and fixing the issue is a matter of restarting SQL Server or you have an issue while starting up SQL Server in which case the SQL Server won't start and you would then need to go through the process documented in BOL for recreating tempdb.|||I have asked that the article be updated to remove SQL 2005 from the section that does the moves via detach. ALTER DATABASE should be used to change the file paths in these cases and there are detailed instructions in books online.|||

Thank you for your comment.

You _can_ detach the tempdb, if you start SQL Server 2005 with the /T3608 parameter [I have tried before posting the question]. From the point tempdb detached it cannot be re-attached, nor can be re-created using the way you mention, as the row with hardcoded database id "2" does not exists in the sysdatabases table.

|||

Thank you.

For closing the issue can we say, that the only way to recover from this situation is attaching a clean master db and re-attach the databases and re-create all global settings (logins, endpoints, etc.) stored in master?

|||

If you have a backup of master, you could restore that after putting in the clean master. Master is usually small enough that it should not be too big a burden to do full backups on a regular basis.

And if you had truly followed the article, the first step is

?

Make a current backup of all databases, especially master, from their current location.

which should allow you to recover.

How to Receive emails

I've recently migrated to sql 2005 and I'm trying to find a solution
to some new requests.
I need to setup a system that process excel spreadsheet received by
email. The sheets will contain data coming from veterinaries that -
after an integration with centrally stored data - should trigger a
report.
I can receive emails both on Lotus Notes (that I don't manage) or POP3
account.
Can't' install Exchange because of the centralized Active Directory
policy.
Anyone, who had a similar issue, can recommend me a simple and robust
solution?
The alternative is to set up a web page to upload the files, but the
IIS server is located on the intranet, so people will need to dial a
modem or use the vpn. Not straight as an email...
Many Thanks,
josephOn Oct 11, 3:37 am, curi...@.spore.it wrote:
> I've recently migrated to sql 2005 and I'm trying to find a solution
> to some new requests.
> I need to setup a system that process excel spreadsheet received by
> email. The sheets will contain data coming from veterinaries that -
> after an integration with centrally stored data - should trigger a
> report.
> I can receive emails both on Lotus Notes (that I don't manage) or POP3
> account.
> Can't' install Exchange because of the centralized Active Directory
> policy.
> Anyone, who had a similar issue, can recommend me a simple and robust
> solution?
> The alternative is to set up a web page to upload the files, but the
> IIS server is located on the intranet, so people will need to dial a
> modem or use the vpn. Not straight as an email...
> Many Thanks,
> joseph
You will most likely want to set this up via a custom ASP.NET
application. You can either have an SSIS pkg triggered through an
ASP.NET application to loop through a directory and import several
excel files into a table, etc -or- you can use bulk insert or BCP
through an ASP.NET application'/stored procedure to import the excel
spreadsheets. Then you could call Reporting Services through the web
service method (http://msdn2.microsoft.com/en-us/library/
microsoft.wssux.reportingserviceswebservice.rsexecutionservice2005.reportexecutionservice.render.aspx )
to create a report. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant

How to read windows registry thru query ?

Hi
How can I access the windows registry entries from MSSQL Server query analyzer. I am looking for some stored procedures. Are there any system stored procedures ?
ThxExtended procedure xp_regread
USE Master
EXEC xp_regread 'HKEY_LOCAL_MACHINE',
'SOFTWARE\Microsoft\MSSQLServer\Setup',
'SQLPath'

how to read an xml file on local file system

Hi,
i have a xml file c:\abcd.xml in my local filesystem.. i just want to
take the path as in parameter for a stored procedure and insert the values
into a table type variable
execute usp_xx (@.xmlpath='c:\abcd.xml')
-- inside the sp i would like to read the xml file and insert the same
into a table type variable..
Can anyone tell me the idea..
nsr
Message posted via http://www.webservertalk.comYou should read the file on the mid-tier/client and send the data as a
stored proc parameter value in SQL Server 2000. SQL Server 2005 will have a
way to read from files...
Best regards
Michael
"narasimha rao chandramouli via webservertalk.com"
<forum@.nospam.webservertalk.com> wrote in message
news:7a34860ffbc045ddaad40506b15e2a1b@.SQ
webservertalk.com...
> Hi,
> i have a xml file c:\abcd.xml in my local filesystem.. i just want to
> take the path as in parameter for a stored procedure and insert the values
> into a table type variable
> execute usp_xx (@.xmlpath='c:\abcd.xml')
> -- inside the sp i would like to read the xml file and insert the same
> into a table type variable..
> Can anyone tell me the idea..
>
> nsr
> --
> Message posted via http://www.webservertalk.com

how to read an xml file on local file system

Hi,
i have a xml file c:\abcd.xml in my local filesystem.. i just want to
take the path as in parameter for a stored procedure and insert the values
into a table type variable
execute usp_xx (@.xmlpath='c:\abcd.xml')
-- inside the sp i would like to read the xml file and insert the same
into a table type variable..
Can anyone tell me the idea..
nsr
Message posted via http://www.droptable.com
You should read the file on the mid-tier/client and send the data as a
stored proc parameter value in SQL Server 2000. SQL Server 2005 will have a
way to read from files...
Best regards
Michael
"narasimha rao chandramouli via droptable.com"
<forum@.nospam.droptable.com> wrote in message
news:7a34860ffbc045ddaad40506b15e2a1b@.droptable.co m...
> Hi,
> i have a xml file c:\abcd.xml in my local filesystem.. i just want to
> take the path as in parameter for a stored procedure and insert the values
> into a table type variable
> execute usp_xx (@.xmlpath='c:\abcd.xml')
> -- inside the sp i would like to read the xml file and insert the same
> into a table type variable..
> Can anyone tell me the idea..
>
> nsr
> --
> Message posted via http://www.droptable.com

How to query data through scheduled tasks

Hello,
We are currently using an inventory control system that is a little lacking.
It allows us to store expiration dates for our materials but lacks any way t
o
use the information. The system is built on an MSSQL 2000 database.
What I would like to do is write a program to place into the Windows
scheduled tasks to run once a day. The app needs to compare the exp. date
field in the table to the current date. If the date is within two weeks or
less of the current date I want to generate an e-mail notification or pop up
message to send to a selected group of internal users. Everyone has an
external e-mail account so I can send directly with an smtp engine.
Has anyone else tried or purchased something that could do this? If not then
what language would be recommended that is capable of doing this on a
schedule basis? If this can be done in SQL then it is simply beyond my
abilities so I would be looking for a contractor to program it.
Our network is running on Server 2000. I run Merak mail server and Goldmine
CRM/e-mail client. All local machines are windows with IE.
Thanks for your assistance.
LWhite> What I would like to do is write a program to place into the Windows
> scheduled tasks to run once a day. The app needs to compare the exp. date
> field in the table to the current date. If the date is within two weeks or
> less of the current date I want to generate an e-mail notification or pop
up
> message to send to a selected group of internal users. Everyone has an
> external e-mail account so I can send directly with an smtp engine.
Sounds like a pretty simple thing to do with SQL Server Agent (keeping the
rules and logic inside the database) and a simple mailing product like
xp_smtp_sendmail. You won't need Windows Scheduled Tasks... let us know if
you have any questions after reading http://www.aspfaq.com/2403
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.|||"LWhite" wrote:

> Hello,
> We are currently using an inventory control system that is a little lackin
g.
> It allows us to store expiration dates for our materials but lacks any way
to
> use the information. The system is built on an MSSQL 2000 database.
> What I would like to do is write a program to place into the Windows
> scheduled tasks to run once a day. The app needs to compare the exp. date
> field in the table to the current date. If the date is within two weeks or
> less of the current date I want to generate an e-mail notification or pop
up
> message to send to a selected group of internal users. Everyone has an
> external e-mail account so I can send directly with an smtp engine.
> Has anyone else tried or purchased something that could do this? If not th
en
> what language would be recommended that is capable of doing this on a
> schedule basis? If this can be done in SQL then it is simply beyond my
> abilities so I would be looking for a contractor to program it.
> Our network is running on Server 2000. I run Merak mail server and Goldmin
e
> CRM/e-mail client. All local machines are windows with IE.
> Thanks for your assistance.
> LWhite
I would use a SQL Server to schedule a job to kick off a task which executes
a sproc like the one below...assuming you can have SQL Mail installed:
create procedure spNotifyProductExpiration
as
begin
declare @.vcSQL varchar(8000)
select @.vcSQL = 'select productname,
abs(datediff(dd,expirationdate,getdate()
)) as ExpireDays from stuff where
abs(datediff(dd,expirationdate,getdate()
)) <= 15'
exec master..xp_sendmail @.recipient='manager@.yourcompany.com',
@.subject='Expirations',
@.query = @.vcSQL, @.message='These products will
be expiring soon!'
end

How to query data through scheduled tasks

Hello,
We are currently using an inventory control system that is a little lacking.
It allows us to store expiration dates for our materials but lacks any way to
use the information. The system is built on an MSSQL 2000 database.
What I would like to do is write a program to place into the Windows
scheduled tasks to run once a day. The app needs to compare the exp. date
field in the table to the current date. If the date is within two weeks or
less of the current date I want to generate an e-mail notification or pop up
message to send to a selected group of internal users. Everyone has an
external e-mail account so I can send directly with an smtp engine.
Has anyone else tried or purchased something that could do this? If not then
what language would be recommended that is capable of doing this on a
schedule basis? If this can be done in SQL then it is simply beyond my
abilities so I would be looking for a contractor to program it.
Our network is running on Server 2000. I run Merak mail server and Goldmine
CRM/e-mail client. All local machines are windows with IE.
Thanks for your assistance.
LWhite
> What I would like to do is write a program to place into the Windows
> scheduled tasks to run once a day. The app needs to compare the exp. date
> field in the table to the current date. If the date is within two weeks or
> less of the current date I want to generate an e-mail notification or pop
up
> message to send to a selected group of internal users. Everyone has an
> external e-mail account so I can send directly with an smtp engine.
Sounds like a pretty simple thing to do with SQL Server Agent (keeping the
rules and logic inside the database) and a simple mailing product like
xp_smtp_sendmail. You won't need Windows Scheduled Tasks... let us know if
you have any questions after reading http://www.aspfaq.com/2403
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
|||"LWhite" wrote:

> Hello,
> We are currently using an inventory control system that is a little lacking.
> It allows us to store expiration dates for our materials but lacks any way to
> use the information. The system is built on an MSSQL 2000 database.
> What I would like to do is write a program to place into the Windows
> scheduled tasks to run once a day. The app needs to compare the exp. date
> field in the table to the current date. If the date is within two weeks or
> less of the current date I want to generate an e-mail notification or pop up
> message to send to a selected group of internal users. Everyone has an
> external e-mail account so I can send directly with an smtp engine.
> Has anyone else tried or purchased something that could do this? If not then
> what language would be recommended that is capable of doing this on a
> schedule basis? If this can be done in SQL then it is simply beyond my
> abilities so I would be looking for a contractor to program it.
> Our network is running on Server 2000. I run Merak mail server and Goldmine
> CRM/e-mail client. All local machines are windows with IE.
> Thanks for your assistance.
> LWhite
I would use a SQL Server to schedule a job to kick off a task which executes
a sproc like the one below...assuming you can have SQL Mail installed:
create procedure spNotifyProductExpiration
as
begin
declare @.vcSQL varchar(8000)
select @.vcSQL = 'select productname,
abs(datediff(dd,expirationdate,getdate())) as ExpireDays from stuff where
abs(datediff(dd,expirationdate,getdate())) <= 15'
exec master..xp_sendmail @.recipient='manager@.yourcompany.com',
@.subject='Expirations',
@.query = @.vcSQL, @.message='These products will
be expiring soon!'
end

How to query data through scheduled tasks

Hello,
We are currently using an inventory control system that is a little lacking.
It allows us to store expiration dates for our materials but lacks any way to
use the information. The system is built on an MSSQL 2000 database.
What I would like to do is write a program to place into the Windows
scheduled tasks to run once a day. The app needs to compare the exp. date
field in the table to the current date. If the date is within two weeks or
less of the current date I want to generate an e-mail notification or pop up
message to send to a selected group of internal users. Everyone has an
external e-mail account so I can send directly with an smtp engine.
Has anyone else tried or purchased something that could do this? If not then
what language would be recommended that is capable of doing this on a
schedule basis? If this can be done in SQL then it is simply beyond my
abilities so I would be looking for a contractor to program it.
Our network is running on Server 2000. I run Merak mail server and Goldmine
CRM/e-mail client. All local machines are windows with IE.
Thanks for your assistance.
LWhite> What I would like to do is write a program to place into the Windows
> scheduled tasks to run once a day. The app needs to compare the exp. date
> field in the table to the current date. If the date is within two weeks or
> less of the current date I want to generate an e-mail notification or pop
up
> message to send to a selected group of internal users. Everyone has an
> external e-mail account so I can send directly with an smtp engine.
Sounds like a pretty simple thing to do with SQL Server Agent (keeping the
rules and logic inside the database) and a simple mailing product like
xp_smtp_sendmail. You won't need Windows Scheduled Tasks... let us know if
you have any questions after reading http://www.aspfaq.com/2403
--
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.|||"LWhite" wrote:
> Hello,
> We are currently using an inventory control system that is a little lacking.
> It allows us to store expiration dates for our materials but lacks any way to
> use the information. The system is built on an MSSQL 2000 database.
> What I would like to do is write a program to place into the Windows
> scheduled tasks to run once a day. The app needs to compare the exp. date
> field in the table to the current date. If the date is within two weeks or
> less of the current date I want to generate an e-mail notification or pop up
> message to send to a selected group of internal users. Everyone has an
> external e-mail account so I can send directly with an smtp engine.
> Has anyone else tried or purchased something that could do this? If not then
> what language would be recommended that is capable of doing this on a
> schedule basis? If this can be done in SQL then it is simply beyond my
> abilities so I would be looking for a contractor to program it.
> Our network is running on Server 2000. I run Merak mail server and Goldmine
> CRM/e-mail client. All local machines are windows with IE.
> Thanks for your assistance.
> LWhite
I would use a SQL Server to schedule a job to kick off a task which executes
a sproc like the one below...assuming you can have SQL Mail installed:
create procedure spNotifyProductExpiration
as
begin
declare @.vcSQL varchar(8000)
select @.vcSQL = 'select productname,
abs(datediff(dd,expirationdate,getdate())) as ExpireDays from stuff where
abs(datediff(dd,expirationdate,getdate())) <= 15'
exec master..xp_sendmail @.recipient='manager@.yourcompany.com',
@.subject='Expirations',
@.query = @.vcSQL, @.message='These products will
be expiring soon!'
end