Basic problems with replication

Hi
I've just installed Sql Server 7 and wanted to see how replication works.
With DTS i copied Northwind database so now i have two db Northwind and
Northwind_copy on my server.
Using wizards (default values) I've tried snapshot replication from
Northwind (publication) to Northwind_copy (pull subscription, I used sa
login without password ? is it correct ? ).
It seems easy but doesn't work ;(
Snapshot agent session details:
7) The process could not bulk copy out of table
'[dbo].[syncobj_0x3044324641344544]'
6) Bulk copied snapshot data for article 'Categories' (0 rows)
5) Bulk copying snapshot data for article 'Categories'
4) Locking published tables while generating the snapshot
3) Generating Schema script for article 'Categories'
2) Connecting to Publisher 'MyServer'
1) Initializing
In Pull Substraction window:
Could not get license information correctly. The step failed
Thanks
urga
Hi,
For Snapshot replication , no need to copy the objects to Northwind_copy
database. Automatically snapshot replication will copy the selected
articles to destination database.
Licensing:-
- check the licensing in control panel and see how many license you have for
sql 7
- check this registry key as well for previlages..
hklm\system\currentcontrolset\services\licenseinfo \mssql7.0
-- Apply SP4 for SQL 7 snd check.
Thanks
Hari
MCDBA
"urga" <urga@.go2.pl> wrote in message
news:cbtu2h$r0f$1@.nemesis.news.tpi.pl...
> Hi
> I've just installed Sql Server 7 and wanted to see how replication works.
> With DTS i copied Northwind database so now i have two db Northwind and
> Northwind_copy on my server.
> Using wizards (default values) I've tried snapshot replication from
> Northwind (publication) to Northwind_copy (pull subscription, I used sa
> login without password ? is it correct ? ).
> It seems easy but doesn't work ;(
> Snapshot agent session details:
> 7) The process could not bulk copy out of table
> '[dbo].[syncobj_0x3044324641344544]'
> 6) Bulk copied snapshot data for article 'Categories' (0 rows)
> 5) Bulk copying snapshot data for article 'Categories'
> 4) Locking published tables while generating the snapshot
> 3) Generating Schema script for article 'Categories'
> 2) Connecting to Publisher 'MyServer'
> 1) Initializing
> In Pull Substraction window:
> Could not get license information correctly. The step failed
> Thanks
> urga
>
>
sql

Basic DTS...

Hi all,

am not very experienced in using DTS and really need your help. I have a dts package that i have scheduled to run every day. Here's what i want the package to do:

1. Check whether a value for a certain column in a certain row of a table in my database is 0 or 1. If it is 1, then
2. Run the dts task (which i have created and is working)

In other words, when the package is started, i want to execute a stored procedure or sql task or whatever, and if that returns 1 then i want to continue, if it returns 0 i want to finish the package without running the dts task. I'm sure there's a simple way to do this, but i could use your help...!

Thanks,
Elisabet--call ur dts package from a sp, something like,
if(@.somecondition)
exec master..xp_cmd_shell 'DTSRun /S MySQLServerName /E /N "Package Name"'
else
break

Basic DTS service problems, i guess...

I try to access a remote SP.
Server1 has a working DTS-service running
Server2 cannot start the DTS due to the following error:
"An error 1060 - (the specified service does not exist as an installes service) occured while performing this service operation on the MSDTC service".

Ok, I got it, the service isn't running (since I get this mess. when I'm tryin to start the service).
Can't find any service in the opsys services. Tried to reinstall the MSSQL but the MSDTC was/is allready installed.

Please help!
By the way, I'm running MSSQL 2000.

thanksIs the dtc service started ?|||Originally posted by rnealejr
Is the dtc service started ?

Nope, and that's the problem. I get the errormessage when I try to start the service in enterprise manager.
I can't find any corresponding service in the opsys services. It seems like the service isn't installed...but it is??!!

Bad Page error

I am getting an error in my DTS logs about a bad page. Here is the exact
message.
Step Error Description:I/O error (bad page ID) detected during read at
offset 0x0000022a040000 in file 'E:\SQLData\ALS_Stage_Data.MDF'.
We are getting this error on 2 different servers. The process has been
running like a champ for years and now we are getting this message. All that
is running when it errors out is an update statement that is joining 2
tables. I have read some about tempdb running into these issues and it
suggests that there may be hardware issues. That is not the case as we have
looked into that and we have many other processes that run on these servers.
I have also read that service pack 4 needs to be installed. Well we moved
our process to another server with exactly the same configuration and it ran
fine on there. Had anybody else ran into this? Any suggestions? All help
is appreciated.
Thanks
have you run DBCC CHECKDB to see if there are any errors?
Jack Vamvas
__________________________________________________ ________________
Receive free SQL tips - register at www.ciquery.com/sqlserver.htm
SQL Server Performance Audit - check www.ciquery.com/sqlserver_audit.htm
New article by Jack Vamvas - SQL and Markov Chains -
www.ciquery.com/articles/art_04.asp
"Andy" <Andy@.discussions.microsoft.com> wrote in message
news:AA513080-F72F-4386-93CA-93CEF0673F76@.microsoft.com...
> I am getting an error in my DTS logs about a bad page. Here is the exact
> message.
> Step Error Description:I/O error (bad page ID) detected during read at
> offset 0x0000022a040000 in file 'E:\SQLData\ALS_Stage_Data.MDF'.
> We are getting this error on 2 different servers. The process has been
> running like a champ for years and now we are getting this message. All
that
> is running when it errors out is an update statement that is joining 2
> tables. I have read some about tempdb running into these issues and it
> suggests that there may be hardware issues. That is not the case as we
have
> looked into that and we have many other processes that run on these
servers.
> I have also read that service pack 4 needs to be installed. Well we moved
> our process to another server with exactly the same configuration and it
ran
> fine on there. Had anybody else ran into this? Any suggestions? All
help
> is appreciated.
> Thanks
|||Yes, we ran that and no errors were returned. We also ran it with Allow data
loss and no errors were returned. Like I mentioned below, this is happening
on 2 servers. It is the same process, but 1 is the dev server and 1 is prod.
"Jack Vamvas" wrote:

> have you run DBCC CHECKDB to see if there are any errors?
> --
> Jack Vamvas
> __________________________________________________ ________________
> Receive free SQL tips - register at www.ciquery.com/sqlserver.htm
> SQL Server Performance Audit - check www.ciquery.com/sqlserver_audit.htm
> New article by Jack Vamvas - SQL and Markov Chains -
> www.ciquery.com/articles/art_04.asp
> "Andy" <Andy@.discussions.microsoft.com> wrote in message
> news:AA513080-F72F-4386-93CA-93CEF0673F76@.microsoft.com...
> that
> have
> servers.
> ran
> help
>
>
|||Hi Andy,
This is usually caused by the hardware, but if it is happening on two
different hardware systems, it seems like a logical problem in the database.
You probably restored a backup of the database from server to another.
Here is the logical meaning of this error:
http://support.microsoft.com/default...b;en-us;828339
HTH
DeeJay Puar
MCDBA
(bad page ID): This message means that the pageID on the page header is not
the expected page that was read from the disk. For example, if SQL Server
2000 provides a file offset for database file 1 that is for logical page 100,
the pageID on the page header for that 8 KB page should be 1:100. If not, the
bad page ID is included in the logical I/O check failure message.
You can read more about it here:
"Andy" wrote:
[vbcol=seagreen]
> Yes, we ran that and no errors were returned. We also ran it with Allow data
> loss and no errors were returned. Like I mentioned below, this is happening
> on 2 servers. It is the same process, but 1 is the dev server and 1 is prod.
>
> "Jack Vamvas" wrote:
|||I looked into that as well, as I thought I did take a backup. The 2nd server
that it is happening on I created brand new databases before I kicked off the
process and we received the same error, at the same point in the process.
"DeeJay Puar" wrote:
[vbcol=seagreen]
> Hi Andy,
> This is usually caused by the hardware, but if it is happening on two
> different hardware systems, it seems like a logical problem in the database.
> You probably restored a backup of the database from server to another.
> Here is the logical meaning of this error:
> http://support.microsoft.com/default...b;en-us;828339
> HTH
> DeeJay Puar
> MCDBA
> (bad page ID): This message means that the pageID on the page header is not
> the expected page that was read from the disk. For example, if SQL Server
> 2000 provides a file offset for database file 1 that is for logical page 100,
> the pageID on the page header for that 8 KB page should be 1:100. If not, the
> bad page ID is included in the logical I/O check failure message.
> You can read more about it here:
>
> "Andy" wrote:
|||No too sure as to what is happening. I can not really duplicate it here.
On the server, did you take a backup from the old server and restore the
database on the new server? Or did you just create a shell and then ran your
dts package to load the data? Have you looked at the source tables in the DTS
package?
Have you looked into torn-page?
"Andy" wrote:
[vbcol=seagreen]
> I looked into that as well, as I thought I did take a backup. The 2nd server
> that it is happening on I created brand new databases before I kicked off the
> process and we received the same error, at the same point in the process.
> "DeeJay Puar" wrote:

Bad Page error

I am getting an error in my DTS logs about a bad page. Here is the exact
message.
Step Error Description:I/O error (bad page ID) detected during read at
offset 0x0000022a040000 in file 'E:\SQLData\ALS_Stage_Data.MDF'.
We are getting this error on 2 different servers. The process has been
running like a champ for years and now we are getting this message. All that
is running when it errors out is an update statement that is joining 2
tables. I have read some about tempdb running into these issues and it
suggests that there may be hardware issues. That is not the case as we have
looked into that and we have many other processes that run on these servers.
I have also read that service pack 4 needs to be installed. Well we moved
our process to another server with exactly the same configuration and it ran
fine on there. Had anybody else ran into this? Any suggestions? All help
is appreciated.
Thankshave you run DBCC CHECKDB to see if there are any errors?
--
Jack Vamvas
__________________________________________________________________
Receive free SQL tips - register at www.ciquery.com/sqlserver.htm
SQL Server Performance Audit - check www.ciquery.com/sqlserver_audit.htm
New article by Jack Vamvas - SQL and Markov Chains -
www.ciquery.com/articles/art_04.asp
"Andy" <Andy@.discussions.microsoft.com> wrote in message
news:AA513080-F72F-4386-93CA-93CEF0673F76@.microsoft.com...
> I am getting an error in my DTS logs about a bad page. Here is the exact
> message.
> Step Error Description:I/O error (bad page ID) detected during read at
> offset 0x0000022a040000 in file 'E:\SQLData\ALS_Stage_Data.MDF'.
> We are getting this error on 2 different servers. The process has been
> running like a champ for years and now we are getting this message. All
that
> is running when it errors out is an update statement that is joining 2
> tables. I have read some about tempdb running into these issues and it
> suggests that there may be hardware issues. That is not the case as we
have
> looked into that and we have many other processes that run on these
servers.
> I have also read that service pack 4 needs to be installed. Well we moved
> our process to another server with exactly the same configuration and it
ran
> fine on there. Had anybody else ran into this? Any suggestions? All
help
> is appreciated.
> Thanks|||Yes, we ran that and no errors were returned. We also ran it with Allow data
loss and no errors were returned. Like I mentioned below, this is happening
on 2 servers. It is the same process, but 1 is the dev server and 1 is prod.
"Jack Vamvas" wrote:
> have you run DBCC CHECKDB to see if there are any errors?
> --
> Jack Vamvas
> __________________________________________________________________
> Receive free SQL tips - register at www.ciquery.com/sqlserver.htm
> SQL Server Performance Audit - check www.ciquery.com/sqlserver_audit.htm
> New article by Jack Vamvas - SQL and Markov Chains -
> www.ciquery.com/articles/art_04.asp
> "Andy" <Andy@.discussions.microsoft.com> wrote in message
> news:AA513080-F72F-4386-93CA-93CEF0673F76@.microsoft.com...
> > I am getting an error in my DTS logs about a bad page. Here is the exact
> > message.
> >
> > Step Error Description:I/O error (bad page ID) detected during read at
> > offset 0x0000022a040000 in file 'E:\SQLData\ALS_Stage_Data.MDF'.
> >
> > We are getting this error on 2 different servers. The process has been
> > running like a champ for years and now we are getting this message. All
> that
> > is running when it errors out is an update statement that is joining 2
> > tables. I have read some about tempdb running into these issues and it
> > suggests that there may be hardware issues. That is not the case as we
> have
> > looked into that and we have many other processes that run on these
> servers.
> > I have also read that service pack 4 needs to be installed. Well we moved
> > our process to another server with exactly the same configuration and it
> ran
> > fine on there. Had anybody else ran into this? Any suggestions? All
> help
> > is appreciated.
> >
> > Thanks
>
>|||Hi Andy,
This is usually caused by the hardware, but if it is happening on two
different hardware systems, it seems like a logical problem in the database.
You probably restored a backup of the database from server to another.
Here is the logical meaning of this error:
http://support.microsoft.com/default.aspx?scid=kb;en-us;828339
HTH
DeeJay Puar
MCDBA
(bad page ID): This message means that the pageID on the page header is not
the expected page that was read from the disk. For example, if SQL Server
2000 provides a file offset for database file 1 that is for logical page 100,
the pageID on the page header for that 8 KB page should be 1:100. If not, the
bad page ID is included in the logical I/O check failure message.
You can read more about it here:
"Andy" wrote:
> Yes, we ran that and no errors were returned. We also ran it with Allow data
> loss and no errors were returned. Like I mentioned below, this is happening
> on 2 servers. It is the same process, but 1 is the dev server and 1 is prod.
>
> "Jack Vamvas" wrote:
> > have you run DBCC CHECKDB to see if there are any errors?
> >
> > --
> > Jack Vamvas
> > __________________________________________________________________
> > Receive free SQL tips - register at www.ciquery.com/sqlserver.htm
> > SQL Server Performance Audit - check www.ciquery.com/sqlserver_audit.htm
> > New article by Jack Vamvas - SQL and Markov Chains -
> > www.ciquery.com/articles/art_04.asp
> > "Andy" <Andy@.discussions.microsoft.com> wrote in message
> > news:AA513080-F72F-4386-93CA-93CEF0673F76@.microsoft.com...
> > > I am getting an error in my DTS logs about a bad page. Here is the exact
> > > message.
> > >
> > > Step Error Description:I/O error (bad page ID) detected during read at
> > > offset 0x0000022a040000 in file 'E:\SQLData\ALS_Stage_Data.MDF'.
> > >
> > > We are getting this error on 2 different servers. The process has been
> > > running like a champ for years and now we are getting this message. All
> > that
> > > is running when it errors out is an update statement that is joining 2
> > > tables. I have read some about tempdb running into these issues and it
> > > suggests that there may be hardware issues. That is not the case as we
> > have
> > > looked into that and we have many other processes that run on these
> > servers.
> > > I have also read that service pack 4 needs to be installed. Well we moved
> > > our process to another server with exactly the same configuration and it
> > ran
> > > fine on there. Had anybody else ran into this? Any suggestions? All
> > help
> > > is appreciated.
> > >
> > > Thanks
> >
> >
> >|||I looked into that as well, as I thought I did take a backup. The 2nd server
that it is happening on I created brand new databases before I kicked off the
process and we received the same error, at the same point in the process.
"DeeJay Puar" wrote:
> Hi Andy,
> This is usually caused by the hardware, but if it is happening on two
> different hardware systems, it seems like a logical problem in the database.
> You probably restored a backup of the database from server to another.
> Here is the logical meaning of this error:
> http://support.microsoft.com/default.aspx?scid=kb;en-us;828339
> HTH
> DeeJay Puar
> MCDBA
> (bad page ID): This message means that the pageID on the page header is not
> the expected page that was read from the disk. For example, if SQL Server
> 2000 provides a file offset for database file 1 that is for logical page 100,
> the pageID on the page header for that 8 KB page should be 1:100. If not, the
> bad page ID is included in the logical I/O check failure message.
> You can read more about it here:
>
> "Andy" wrote:
> > Yes, we ran that and no errors were returned. We also ran it with Allow data
> > loss and no errors were returned. Like I mentioned below, this is happening
> > on 2 servers. It is the same process, but 1 is the dev server and 1 is prod.
> >
> >
> > "Jack Vamvas" wrote:
> >
> > > have you run DBCC CHECKDB to see if there are any errors?
> > >
> > > --
> > > Jack Vamvas
> > > __________________________________________________________________
> > > Receive free SQL tips - register at www.ciquery.com/sqlserver.htm
> > > SQL Server Performance Audit - check www.ciquery.com/sqlserver_audit.htm
> > > New article by Jack Vamvas - SQL and Markov Chains -
> > > www.ciquery.com/articles/art_04.asp
> > > "Andy" <Andy@.discussions.microsoft.com> wrote in message
> > > news:AA513080-F72F-4386-93CA-93CEF0673F76@.microsoft.com...
> > > > I am getting an error in my DTS logs about a bad page. Here is the exact
> > > > message.
> > > >
> > > > Step Error Description:I/O error (bad page ID) detected during read at
> > > > offset 0x0000022a040000 in file 'E:\SQLData\ALS_Stage_Data.MDF'.
> > > >
> > > > We are getting this error on 2 different servers. The process has been
> > > > running like a champ for years and now we are getting this message. All
> > > that
> > > > is running when it errors out is an update statement that is joining 2
> > > > tables. I have read some about tempdb running into these issues and it
> > > > suggests that there may be hardware issues. That is not the case as we
> > > have
> > > > looked into that and we have many other processes that run on these
> > > servers.
> > > > I have also read that service pack 4 needs to be installed. Well we moved
> > > > our process to another server with exactly the same configuration and it
> > > ran
> > > > fine on there. Had anybody else ran into this? Any suggestions? All
> > > help
> > > > is appreciated.
> > > >
> > > > Thanks
> > >
> > >
> > >|||No too sure as to what is happening. I can not really duplicate it here.
On the server, did you take a backup from the old server and restore the
database on the new server? Or did you just create a shell and then ran your
dts package to load the data? Have you looked at the source tables in the DTS
package?
Have you looked into torn-page?
"Andy" wrote:
> I looked into that as well, as I thought I did take a backup. The 2nd server
> that it is happening on I created brand new databases before I kicked off the
> process and we received the same error, at the same point in the process.
> "DeeJay Puar" wrote:
> > Hi Andy,
> >
> > This is usually caused by the hardware, but if it is happening on two
> > different hardware systems, it seems like a logical problem in the database.
> > You probably restored a backup of the database from server to another.
> >
> > Here is the logical meaning of this error:
> >
> > http://support.microsoft.com/default.aspx?scid=kb;en-us;828339
> >
> > HTH
> >
> > DeeJay Puar
> > MCDBA
> >
> > (bad page ID): This message means that the pageID on the page header is not
> > the expected page that was read from the disk. For example, if SQL Server
> > 2000 provides a file offset for database file 1 that is for logical page 100,
> > the pageID on the page header for that 8 KB page should be 1:100. If not, the
> > bad page ID is included in the logical I/O check failure message.
> >
> > You can read more about it here:
> >
> >
> >
> > "Andy" wrote:
> >
> > > Yes, we ran that and no errors were returned. We also ran it with Allow data
> > > loss and no errors were returned. Like I mentioned below, this is happening
> > > on 2 servers. It is the same process, but 1 is the dev server and 1 is prod.
> > >
> > >
> > > "Jack Vamvas" wrote:
> > >
> > > > have you run DBCC CHECKDB to see if there are any errors?
> > > >
> > > > --
> > > > Jack Vamvas
> > > > __________________________________________________________________
> > > > Receive free SQL tips - register at www.ciquery.com/sqlserver.htm
> > > > SQL Server Performance Audit - check www.ciquery.com/sqlserver_audit.htm
> > > > New article by Jack Vamvas - SQL and Markov Chains -
> > > > www.ciquery.com/articles/art_04.asp
> > > > "Andy" <Andy@.discussions.microsoft.com> wrote in message
> > > > news:AA513080-F72F-4386-93CA-93CEF0673F76@.microsoft.com...
> > > > > I am getting an error in my DTS logs about a bad page. Here is the exact
> > > > > message.
> > > > >
> > > > > Step Error Description:I/O error (bad page ID) detected during read at
> > > > > offset 0x0000022a040000 in file 'E:\SQLData\ALS_Stage_Data.MDF'.
> > > > >
> > > > > We are getting this error on 2 different servers. The process has been
> > > > > running like a champ for years and now we are getting this message. All
> > > > that
> > > > > is running when it errors out is an update statement that is joining 2
> > > > > tables. I have read some about tempdb running into these issues and it
> > > > > suggests that there may be hardware issues. That is not the case as we
> > > > have
> > > > > looked into that and we have many other processes that run on these
> > > > servers.
> > > > > I have also read that service pack 4 needs to be installed. Well we moved
> > > > > our process to another server with exactly the same configuration and it
> > > > ran
> > > > > fine on there. Had anybody else ran into this? Any suggestions? All
> > > > help
> > > > > is appreciated.
> > > > >
> > > > > Thanks
> > > >
> > > >
> > > >

Bad Page error

I am getting an error in my DTS logs about a bad page. Here is the exact
message.
Step Error Description:I/O error (bad page ID) detected during read at
offset 0x0000022a040000 in file 'E:\SQLData\ALS_Stage_Data.MDF'.
We are getting this error on 2 different servers. The process has been
running like a champ for years and now we are getting this message. All tha
t
is running when it errors out is an update statement that is joining 2
tables. I have read some about tempdb running into these issues and it
suggests that there may be hardware issues. That is not the case as we have
looked into that and we have many other processes that run on these servers.
I have also read that service pack 4 needs to be installed. Well we moved
our process to another server with exactly the same configuration and it ran
fine on there. Had anybody else ran into this? Any suggestions? All help
is appreciated.
Thankshave you run DBCC CHECKDB to see if there are any errors?
Jack Vamvas
________________________________________
__________________________
Receive free SQL tips - register at www.ciquery.com/sqlserver.htm
SQL Server Performance Audit - check www.ciquery.com/sqlserver_audit.htm
New article by Jack Vamvas - SQL and Markov Chains -
www.ciquery.com/articles/art_04.asp
"Andy" <Andy@.discussions.microsoft.com> wrote in message
news:AA513080-F72F-4386-93CA-93CEF0673F76@.microsoft.com...
> I am getting an error in my DTS logs about a bad page. Here is the exact
> message.
> Step Error Description:I/O error (bad page ID) detected during read at
> offset 0x0000022a040000 in file 'E:\SQLData\ALS_Stage_Data.MDF'.
> We are getting this error on 2 different servers. The process has been
> running like a champ for years and now we are getting this message. All
that
> is running when it errors out is an update statement that is joining 2
> tables. I have read some about tempdb running into these issues and it
> suggests that there may be hardware issues. That is not the case as we
have
> looked into that and we have many other processes that run on these
servers.
> I have also read that service pack 4 needs to be installed. Well we moved
> our process to another server with exactly the same configuration and it
ran
> fine on there. Had anybody else ran into this? Any suggestions? All
help
> is appreciated.
> Thanks|||Yes, we ran that and no errors were returned. We also ran it with Allow dat
a
loss and no errors were returned. Like I mentioned below, this is happening
on 2 servers. It is the same process, but 1 is the dev server and 1 is prod
.
"Jack Vamvas" wrote:

> have you run DBCC CHECKDB to see if there are any errors?
> --
> Jack Vamvas
> ________________________________________
__________________________
> Receive free SQL tips - register at www.ciquery.com/sqlserver.htm
> SQL Server Performance Audit - check www.ciquery.com/sqlserver_audit.htm
> New article by Jack Vamvas - SQL and Markov Chains -
> www.ciquery.com/articles/art_04.asp
> "Andy" <Andy@.discussions.microsoft.com> wrote in message
> news:AA513080-F72F-4386-93CA-93CEF0673F76@.microsoft.com...
> that
> have
> servers.
> ran
> help
>
>|||Hi Andy,
This is usually caused by the hardware, but if it is happening on two
different hardware systems, it seems like a logical problem in the database.
You probably restored a backup of the database from server to another.
Here is the logical meaning of this error:
http://support.microsoft.com/defaul...kb;en-us;828339
HTH
DeeJay Puar
MCDBA
(bad page ID): This message means that the pageID on the page header is not
the expected page that was read from the disk. For example, if SQL Server
2000 provides a file offset for database file 1 that is for logical page 100
,
the pageID on the page header for that 8 KB page should be 1:100. If not, th
e
bad page ID is included in the logical I/O check failure message.
You can read more about it here:
"Andy" wrote:
[vbcol=seagreen]
> Yes, we ran that and no errors were returned. We also ran it with Allow d
ata
> loss and no errors were returned. Like I mentioned below, this is happeni
ng
> on 2 servers. It is the same process, but 1 is the dev server and 1 is pr
od.
>
> "Jack Vamvas" wrote:
>|||I looked into that as well, as I thought I did take a backup. The 2nd serve
r
that it is happening on I created brand new databases before I kicked off th
e
process and we received the same error, at the same point in the process.
"DeeJay Puar" wrote:
[vbcol=seagreen]
> Hi Andy,
> This is usually caused by the hardware, but if it is happening on two
> different hardware systems, it seems like a logical problem in the databas
e.
> You probably restored a backup of the database from server to another.
> Here is the logical meaning of this error:
> http://support.microsoft.com/defaul...kb;en-us;828339
> HTH
> DeeJay Puar
> MCDBA
> (bad page ID): This message means that the pageID on the page header is no
t
> the expected page that was read from the disk. For example, if SQL Server
> 2000 provides a file offset for database file 1 that is for logical page 1
00,
> the pageID on the page header for that 8 KB page should be 1:100. If not,
the
> bad page ID is included in the logical I/O check failure message.
> You can read more about it here:
>
> "Andy" wrote:
>|||No too sure as to what is happening. I can not really duplicate it here.
On the server, did you take a backup from the old server and restore the
database on the new server? Or did you just create a shell and then ran your
dts package to load the data? Have you looked at the source tables in the DT
S
package?
Have you looked into torn-page?
"Andy" wrote:
[vbcol=seagreen]
> I looked into that as well, as I thought I did take a backup. The 2nd ser
ver
> that it is happening on I created brand new databases before I kicked off
the
> process and we received the same error, at the same point in the process.
> "DeeJay Puar" wrote:
>

Bad List ?

I recently saw a list of "possibly" bad practices which included the
following. Can anyone expand as to why ?
Using DTS packages because they may be scheduled.
Using Enteprise manager to make data and schema changes
Embedding sql in applications
Thanks !"Rob C" <rwc1960@.bellsouth.net> wrote in message
news:H31Qd.2002$0y6.1071@.bignews1.bellsouth.net...
>I recently saw a list of "possibly" bad practices which included the
>following. Can anyone expand as to why ?
> Using DTS packages because they may be scheduled.
> Using Enteprise manager to make data and schema changes
> Embedding sql in applications
> Thanks !
>
>
I can't speak to the DTS package issue, but using SEM to make data and
schema changes is not always in your best interest. While it will work, the
way SEM handles schema changes may not be the quickest or best way to make
those changes in your system. As an example, turn on SQL Profiler and see
what the SEM does when you rename a table.
Embedding SQL in applications is generally verboten for a variety of
reasons. The first and foremost is that you are now creating a tightly
coupled application. What happens if you have an INSERT statement embedded
in your front-end. If you now make a schema change to the table referenced
by that INSERT statement, the INSERT may now fail. (Wrong number of columns
for example.) In an n-tier system, you should generally be looking to
achieve a loosely coupled, highly cohesive system. A second issue with
embedded SQL is that you now have to fix two programs and deploy those
programs. A third item could be security. Depending on how you have things
set up, you may have fewer and less appetizing security options if you are
using embedded SQL.
Rick Sawtell
MCT, MCSD, MCDBA|||Thanks Rick,
What about embedding SQL in VBScript within a DTS package ? Does your
opinion change ? Would you prefer that the VBScript execute a stored
proedure ?
"Rick Sawtell" <quickening@.msn.com> wrote in message
news:%232QTJepEFHA.3536@.TK2MSFTNGP15.phx.gbl...
> "Rob C" <rwc1960@.bellsouth.net> wrote in message
> news:H31Qd.2002$0y6.1071@.bignews1.bellsouth.net...
>>I recently saw a list of "possibly" bad practices which included the
>>following. Can anyone expand as to why ?
>> Using DTS packages because they may be scheduled.
>> Using Enteprise manager to make data and schema changes
>> Embedding sql in applications
>> Thanks !
>>
> I can't speak to the DTS package issue, but using SEM to make data and
> schema changes is not always in your best interest. While it will work,
> the way SEM handles schema changes may not be the quickest or best way to
> make those changes in your system. As an example, turn on SQL Profiler
> and see what the SEM does when you rename a table.
> Embedding SQL in applications is generally verboten for a variety of
> reasons. The first and foremost is that you are now creating a tightly
> coupled application. What happens if you have an INSERT statement
> embedded in your front-end. If you now make a schema change to the table
> referenced by that INSERT statement, the INSERT may now fail. (Wrong
> number of columns for example.) In an n-tier system, you should generally
> be looking to achieve a loosely coupled, highly cohesive system. A second
> issue with embedded SQL is that you now have to fix two programs and
> deploy those programs. A third item could be security. Depending on how
> you have things set up, you may have fewer and less appetizing security
> options if you are using embedded SQL.
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>|||"Rob C" <rwc1960@.bellsouth.net> wrote in message
news:tW1Qd.2007$0y6.29@.bignews1.bellsouth.net...
> Thanks Rick,
> What about embedding SQL in VBScript within a DTS package ? Does your
> opinion change ? Would you prefer that the VBScript execute a stored
> proedure ?
>
It depends. ;-)
If the DTS package is a run-once type of deal, then I have no problems with
the embedded SQL.
If however, the package is something you will run often, I would rethink it
a bit and probably go with some sprocs that are called from your DTS
package.
Rick|||Thanks Rick,
Basically, the DTS packages simply create xml files from data stored in
tables, and as each file is created, an embedded sql call is made to update
the rows that were just used to create the xml file. No user intervention
is required. The packages are run once each day.
Sound OK ?
"Rick Sawtell" <quickening@.msn.com> wrote in message
news:O9x2gj3EFHA.2828@.TK2MSFTNGP09.phx.gbl...
> "Rob C" <rwc1960@.bellsouth.net> wrote in message
> news:tW1Qd.2007$0y6.29@.bignews1.bellsouth.net...
>> Thanks Rick,
>> What about embedding SQL in VBScript within a DTS package ? Does your
>> opinion change ? Would you prefer that the VBScript execute a stored
>> proedure ?
>
> It depends. ;-)
> If the DTS package is a run-once type of deal, then I have no problems
> with the embedded SQL.
> If however, the package is something you will run often, I would rethink
> it a bit and probably go with some sprocs that are called from your DTS
> package.
>
> Rick
>
>

Bad List ?

I recently saw a list of "possibly" bad practices which included the
following. Can anyone expand as to why ?
Using DTS packages because they may be scheduled.
Using Enteprise manager to make data and schema changes
Embedding sql in applications
Thanks !"Rob C" <rwc1960@.bellsouth.net> wrote in message
news:H31Qd.2002$0y6.1071@.bignews1.bellsouth.net...
>I recently saw a list of "possibly" bad practices which included the
>following. Can anyone expand as to why ?
> Using DTS packages because they may be scheduled.
> Using Enteprise manager to make data and schema changes
> Embedding sql in applications
> Thanks !
>
>
I can't speak to the DTS package issue, but using SEM to make data and
schema changes is not always in your best interest. While it will work, the
way SEM handles schema changes may not be the quickest or best way to make
those changes in your system. As an example, turn on SQL Profiler and see
what the SEM does when you rename a table.
Embedding SQL in applications is generally verboten for a variety of
reasons. The first and foremost is that you are now creating a tightly
coupled application. What happens if you have an INSERT statement embedded
in your front-end. If you now make a schema change to the table referenced
by that INSERT statement, the INSERT may now fail. (Wrong number of columns
for example.) In an n-tier system, you should generally be looking to
achieve a loosely coupled, highly cohesive system. A second issue with
embedded SQL is that you now have to fix two programs and deploy those
programs. A third item could be security. Depending on how you have things
set up, you may have fewer and less appetizing security options if you are
using embedded SQL.
Rick Sawtell
MCT, MCSD, MCDBA|||Thanks Rick,
What about embedding SQL in VBScript within a DTS package ? Does your
opinion change ? Would you prefer that the VBScript execute a stored
proedure ?
"Rick Sawtell" <quickening@.msn.com> wrote in message
news:%232QTJepEFHA.3536@.TK2MSFTNGP15.phx.gbl...
> "Rob C" <rwc1960@.bellsouth.net> wrote in message
> news:H31Qd.2002$0y6.1071@.bignews1.bellsouth.net...
> I can't speak to the DTS package issue, but using SEM to make data and
> schema changes is not always in your best interest. While it will work,
> the way SEM handles schema changes may not be the quickest or best way to
> make those changes in your system. As an example, turn on SQL Profiler
> and see what the SEM does when you rename a table.
> Embedding SQL in applications is generally verboten for a variety of
> reasons. The first and foremost is that you are now creating a tightly
> coupled application. What happens if you have an INSERT statement
> embedded in your front-end. If you now make a schema change to the table
> referenced by that INSERT statement, the INSERT may now fail. (Wrong
> number of columns for example.) In an n-tier system, you should generally
> be looking to achieve a loosely coupled, highly cohesive system. A second
> issue with embedded SQL is that you now have to fix two programs and
> deploy those programs. A third item could be security. Depending on how
> you have things set up, you may have fewer and less appetizing security
> options if you are using embedded SQL.
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>|||"Rob C" <rwc1960@.bellsouth.net> wrote in message
news:tW1Qd.2007$0y6.29@.bignews1.bellsouth.net...
> Thanks Rick,
> What about embedding SQL in VBScript within a DTS package ? Does your
> opinion change ? Would you prefer that the VBScript execute a stored
> proedure ?
>
It depends. ;-)
If the DTS package is a run-once type of deal, then I have no problems with
the embedded SQL.
If however, the package is something you will run often, I would rethink it
a bit and probably go with some sprocs that are called from your DTS
package.
Rick|||Thanks Rick,
Basically, the DTS packages simply create xml files from data stored in
tables, and as each file is created, an embedded sql call is made to update
the rows that were just used to create the xml file. No user intervention
is required. The packages are run once each day.
Sound OK ?
"Rick Sawtell" <quickening@.msn.com> wrote in message
news:O9x2gj3EFHA.2828@.TK2MSFTNGP09.phx.gbl...
> "Rob C" <rwc1960@.bellsouth.net> wrote in message
> news:tW1Qd.2007$0y6.29@.bignews1.bellsouth.net...
>
> It depends. ;-)
> If the DTS package is a run-once type of deal, then I have no problems
> with the embedded SQL.
> If however, the package is something you will run often, I would rethink
> it a bit and probably go with some sprocs that are called from your DTS
> package.
>
> Rick
>
>

Bad List ?

I recently saw a list of "possibly" bad practices which included the
following. Can anyone expand as to why ?
Using DTS packages because they may be scheduled.
Using Enteprise manager to make data and schema changes
Embedding sql in applications
Thanks !
"Rob C" <rwc1960@.bellsouth.net> wrote in message
news:H31Qd.2002$0y6.1071@.bignews1.bellsouth.net...
>I recently saw a list of "possibly" bad practices which included the
>following. Can anyone expand as to why ?
> Using DTS packages because they may be scheduled.
> Using Enteprise manager to make data and schema changes
> Embedding sql in applications
> Thanks !
>
>
I can't speak to the DTS package issue, but using SEM to make data and
schema changes is not always in your best interest. While it will work, the
way SEM handles schema changes may not be the quickest or best way to make
those changes in your system. As an example, turn on SQL Profiler and see
what the SEM does when you rename a table.
Embedding SQL in applications is generally verboten for a variety of
reasons. The first and foremost is that you are now creating a tightly
coupled application. What happens if you have an INSERT statement embedded
in your front-end. If you now make a schema change to the table referenced
by that INSERT statement, the INSERT may now fail. (Wrong number of columns
for example.) In an n-tier system, you should generally be looking to
achieve a loosely coupled, highly cohesive system. A second issue with
embedded SQL is that you now have to fix two programs and deploy those
programs. A third item could be security. Depending on how you have things
set up, you may have fewer and less appetizing security options if you are
using embedded SQL.
Rick Sawtell
MCT, MCSD, MCDBA
|||Thanks Rick,
What about embedding SQL in VBScript within a DTS package ? Does your
opinion change ? Would you prefer that the VBScript execute a stored
proedure ?
"Rick Sawtell" <quickening@.msn.com> wrote in message
news:%232QTJepEFHA.3536@.TK2MSFTNGP15.phx.gbl...
> "Rob C" <rwc1960@.bellsouth.net> wrote in message
> news:H31Qd.2002$0y6.1071@.bignews1.bellsouth.net...
> I can't speak to the DTS package issue, but using SEM to make data and
> schema changes is not always in your best interest. While it will work,
> the way SEM handles schema changes may not be the quickest or best way to
> make those changes in your system. As an example, turn on SQL Profiler
> and see what the SEM does when you rename a table.
> Embedding SQL in applications is generally verboten for a variety of
> reasons. The first and foremost is that you are now creating a tightly
> coupled application. What happens if you have an INSERT statement
> embedded in your front-end. If you now make a schema change to the table
> referenced by that INSERT statement, the INSERT may now fail. (Wrong
> number of columns for example.) In an n-tier system, you should generally
> be looking to achieve a loosely coupled, highly cohesive system. A second
> issue with embedded SQL is that you now have to fix two programs and
> deploy those programs. A third item could be security. Depending on how
> you have things set up, you may have fewer and less appetizing security
> options if you are using embedded SQL.
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>
|||"Rob C" <rwc1960@.bellsouth.net> wrote in message
news:tW1Qd.2007$0y6.29@.bignews1.bellsouth.net...
> Thanks Rick,
> What about embedding SQL in VBScript within a DTS package ? Does your
> opinion change ? Would you prefer that the VBScript execute a stored
> proedure ?
>
It depends. ;-)
If the DTS package is a run-once type of deal, then I have no problems with
the embedded SQL.
If however, the package is something you will run often, I would rethink it
a bit and probably go with some sprocs that are called from your DTS
package.
Rick
|||Thanks Rick,
Basically, the DTS packages simply create xml files from data stored in
tables, and as each file is created, an embedded sql call is made to update
the rows that were just used to create the xml file. No user intervention
is required. The packages are run once each day.
Sound OK ?
"Rick Sawtell" <quickening@.msn.com> wrote in message
news:O9x2gj3EFHA.2828@.TK2MSFTNGP09.phx.gbl...
> "Rob C" <rwc1960@.bellsouth.net> wrote in message
> news:tW1Qd.2007$0y6.29@.bignews1.bellsouth.net...
>
> It depends. ;-)
> If the DTS package is a run-once type of deal, then I have no problems
> with the embedded SQL.
> If however, the package is something you will run often, I would rethink
> it a bit and probably go with some sprocs that are called from your DTS
> package.
>
> Rick
>
>

Bad Directory- DTS - Global Variables

I am trying to set Global Variable that will open a txt file and import the
data into a sql table.
I am currently using the sample I found at sqldts.com (Looping, Importing
and Archiving).
When I set the variable for filename = dbo.cadfilepath.filename, I get a Bad
Directory error.
My filename data is stored in another table - I am looking for the best
approach to calling the filename from the table = cadfilepath and opening
the actual text file and importing the data into my project table.
ie.
Table = CADFilepath(pathname, projectnum)
Pathname ProjectNum
\\Seasc-Shared\Spln\Shared\Forecast\00000-000.txt 00000-000
\\Seasc-Shared\Spln\Shared\Forecast\00000-001.txt 00000-001
\\Seasc-Shared\Spln\Shared\Forecast\00000-002.txt 00000-002
I want to access the info in CADFIlePath and then open the first record's
text file and import this data into my Project Table, then repeat the
process until .EOF.
I know this sounds confusing and I can email you my documentation - since
this website does not allow for attachments.
Any assistances is greatly appreciated.
KarenHi
This is a stored procedure which will run a DTS and assign a variable to
file. Modify it for your needs.
I assumed you created the DTS and defined the global varaible.
CREATE proc spDTS
as
declare @.packagename varchar(255)
declare @.userpwd varchar(255) --login pwd
declare @.intsecurity bit --use non-zero to indicate integrated security
declare @.pkgPwd varchar(255) --package password
declare @.hr int
declare @.object int
set @.packagename='PackageName'
set @.userpwd=null
set @.pkgPwd=''
set @.intsecurity=0
--create a package object
EXEC @.hr = sp_OACreate 'DTS.Package', @.object OUTPUT
if @.hr <> 0
Begin
EXEC sp_displayoaerrorinfo @.object --, @.hr
RETURN
end
declare @.svr varchar(15)
declare @.login varchar(15)
select @.login = SUSER_NAME()
select @.svr = @.@.servername
declare @.flag int
select @.flag = 256
EXEC @.hr = sp_OAMethod @.object,
'LoadFromSqlServer',NULL, @.ServerName=@.svr, @.ServerUserName=@.login,
@.PackageName=@.packagename, @.Flags=@.flag, @.PackagePassword = @.pkgPwd
exec @.hr = sp_OAsetproperty
@.object,
'GlobalVariables
("FileLocation").Value', 'c:\MyFile.txt' --set location GV
EXEC @.hr = sp_OAMethod @.object, 'Execute'
IF @.hr <> 0
BEGIN
print 'Execute failed'
EXEC sp_displayoaerrorinfo @.object --, @.hr
RETURN
END
EXEC @.hr = sp_OADestroy @.object
IF @.hr <> 0
BEGIN
PRINT '*** Destroy Package failed'
EXEC sp_displayoaerrorinfo @.object, @.hr
RETURN
END
GO
"schaefer" <vkdschaefer1@.attbi.com> wrote in message
news:e%23Q8%23rW1EHA.1408@.TK2MSFTNGP10.phx.gbl...
> I am trying to set Global Variable that will open a txt file and import
the
> data into a sql table.
> I am currently using the sample I found at sqldts.com (Looping, Importing
> and Archiving).
> When I set the variable for filename = dbo.cadfilepath.filename, I get a
Bad
> Directory error.
> My filename data is stored in another table - I am looking for the best
> approach to calling the filename from the table = cadfilepath and opening
> the actual text file and importing the data into my project table.
> ie.
> Table = CADFilepath(pathname, projectnum)
> Pathname ProjectNum
> \\Seasc-Shared\Spln\Shared\Forecast\00000-000.txt 00000-000
> \\Seasc-Shared\Spln\Shared\Forecast\00000-001.txt 00000-001
> \\Seasc-Shared\Spln\Shared\Forecast\00000-002.txt 00000-002
> I want to access the info in CADFIlePath and then open the first record's
> text file and import this data into my Project Table, then repeat the
> process until .EOF.
> I know this sounds confusing and I can email you my documentation - since
> this website does not allow for attachments.
> Any assistances is greatly appreciated.
> Karen
>|||One thing you can do is to load the Global variable directly from the table.
This is done by adding a Dynamic properties task.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"schaefer" <vkdschaefer1@.attbi.com> wrote in message
news:e%23Q8%23rW1EHA.1408@.TK2MSFTNGP10.phx.gbl...
> I am trying to set Global Variable that will open a txt file and import
the
> data into a sql table.
> I am currently using the sample I found at sqldts.com (Looping, Importing
> and Archiving).
> When I set the variable for filename = dbo.cadfilepath.filename, I get a
Bad
> Directory error.
> My filename data is stored in another table - I am looking for the best
> approach to calling the filename from the table = cadfilepath and opening
> the actual text file and importing the data into my project table.
> ie.
> Table = CADFilepath(pathname, projectnum)
> Pathname ProjectNum
> \\Seasc-Shared\Spln\Shared\Forecast\00000-000.txt 00000-000
> \\Seasc-Shared\Spln\Shared\Forecast\00000-001.txt 00000-001
> \\Seasc-Shared\Spln\Shared\Forecast\00000-002.txt 00000-002
> I want to access the info in CADFIlePath and then open the first record's
> text file and import this data into my Project Table, then repeat the
> process until .EOF.
> I know this sounds confusing and I can email you my documentation - since
> this website does not allow for attachments.
> Any assistances is greatly appreciated.
> Karen
>

Bad Directory- DTS - Global Variables

I am trying to set Global Variable that will open a txt file and import the
data into a sql table.
I am currently using the sample I found at sqldts.com (Looping, Importing
and Archiving).
When I set the variable for filename = dbo.cadfilepath.filename, I get a Bad
Directory error.
My filename data is stored in another table - I am looking for the best
approach to calling the filename from the table = cadfilepath and opening
the actual text file and importing the data into my project table.
ie.
Table = CADFilepath(pathname, projectnum)
Pathname ProjectNum
\\Seasc-Shared\Spln\Shared\Forecast\00000-000.txt 00000-000
\\Seasc-Shared\Spln\Shared\Forecast\00000-001.txt 00000-001
\\Seasc-Shared\Spln\Shared\Forecast\00000-002.txt 00000-002
I want to access the info in CADFIlePath and then open the first record's
text file and import this data into my Project Table, then repeat the
process until .EOF.
I know this sounds confusing and I can email you my documentation - since
this website does not allow for attachments.
Any assistances is greatly appreciated.
KarenHi
This is a stored procedure which will run a DTS and assign a variable to
file. Modify it for your needs.
I assumed you created the DTS and defined the global varaible.
CREATE proc spDTS
as
declare @.packagename varchar(255)
declare @.userpwd varchar(255) --login pwd
declare @.intsecurity bit --use non-zero to indicate integrated security
declare @.pkgPwd varchar(255) --package password
declare @.hr int
declare @.object int
set @.packagename='PackageName'
set @.userpwd=null
set @.pkgPwd=''
set @.intsecurity=0
--create a package object
EXEC @.hr = sp_OACreate 'DTS.Package', @.object OUTPUT
if @.hr <> 0
Begin
EXEC sp_displayoaerrorinfo @.object --, @.hr
RETURN
end
declare @.svr varchar(15)
declare @.login varchar(15)
select @.login = SUSER_NAME()
select @.svr = @.@.servername
declare @.flag int
select @.flag = 256
EXEC @.hr = sp_OAMethod @.object,
'LoadFromSqlServer',NULL, @.ServerName=@.svr, @.ServerUserName=@.login,
@.PackageName=@.packagename, @.Flags=@.flag, @.PackagePassword = @.pkgPwd
exec @.hr = sp_OAsetproperty
@.object,
'GlobalVariables
("FileLocation").Value', 'c:\MyFile.txt' --set location GV
EXEC @.hr = sp_OAMethod @.object, 'Execute'
IF @.hr <> 0
BEGIN
print 'Execute failed'
EXEC sp_displayoaerrorinfo @.object --, @.hr
RETURN
END
EXEC @.hr = sp_OADestroy @.object
IF @.hr <> 0
BEGIN
PRINT '*** Destroy Package failed'
EXEC sp_displayoaerrorinfo @.object, @.hr
RETURN
END
GO
"schaefer" <vkdschaefer1@.attbi.com> wrote in message
news:e%23Q8%23rW1EHA.1408@.TK2MSFTNGP10.phx.gbl...
> I am trying to set Global Variable that will open a txt file and import
the
> data into a sql table.
> I am currently using the sample I found at sqldts.com (Looping, Importing
> and Archiving).
> When I set the variable for filename = dbo.cadfilepath.filename, I get a
Bad
> Directory error.
> My filename data is stored in another table - I am looking for the best
> approach to calling the filename from the table = cadfilepath and opening
> the actual text file and importing the data into my project table.
> ie.
> Table = CADFilepath(pathname, projectnum)
> Pathname ProjectNum
> \\Seasc-Shared\Spln\Shared\Forecast\00000-000.txt 00000-000
> \\Seasc-Shared\Spln\Shared\Forecast\00000-001.txt 00000-001
> \\Seasc-Shared\Spln\Shared\Forecast\00000-002.txt 00000-002
> I want to access the info in CADFIlePath and then open the first record's
> text file and import this data into my Project Table, then repeat the
> process until .EOF.
> I know this sounds confusing and I can email you my documentation - since
> this website does not allow for attachments.
> Any assistances is greatly appreciated.
> Karen
>|||One thing you can do is to load the Global variable directly from the table.
This is done by adding a Dynamic properties task.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"schaefer" <vkdschaefer1@.attbi.com> wrote in message
news:e%23Q8%23rW1EHA.1408@.TK2MSFTNGP10.phx.gbl...
> I am trying to set Global Variable that will open a txt file and import
the
> data into a sql table.
> I am currently using the sample I found at sqldts.com (Looping, Importing
> and Archiving).
> When I set the variable for filename = dbo.cadfilepath.filename, I get a
Bad
> Directory error.
> My filename data is stored in another table - I am looking for the best
> approach to calling the filename from the table = cadfilepath and opening
> the actual text file and importing the data into my project table.
> ie.
> Table = CADFilepath(pathname, projectnum)
> Pathname ProjectNum
> \\Seasc-Shared\Spln\Shared\Forecast\00000-000.txt 00000-000
> \\Seasc-Shared\Spln\Shared\Forecast\00000-001.txt 00000-001
> \\Seasc-Shared\Spln\Shared\Forecast\00000-002.txt 00000-002
> I want to access the info in CADFIlePath and then open the first record's
> text file and import this data into my Project Table, then repeat the
> process until .EOF.
> I know this sounds confusing and I can email you my documentation - since
> this website does not allow for attachments.
> Any assistances is greatly appreciated.
> Karen
>

Bad Directory- DTS - Global Variables

I am trying to set Global Variable that will open a txt file and import the
data into a sql table.
I am currently using the sample I found at sqldts.com (Looping, Importing
and Archiving).
When I set the variable for filename = dbo.cadfilepath.filename, I get a Bad
Directory error.
My filename data is stored in another table - I am looking for the best
approach to calling the filename from the table = cadfilepath and opening
the actual text file and importing the data into my project table.
ie.
Table = CADFilepath(pathname, projectnum)
Pathname ProjectNum
\\Seasc-Shared\Spln\Shared\Forecast\00000-000.txt 00000-000
\\Seasc-Shared\Spln\Shared\Forecast\00000-001.txt 00000-001
\\Seasc-Shared\Spln\Shared\Forecast\00000-002.txt 00000-002
I want to access the info in CADFIlePath and then open the first record's
text file and import this data into my Project Table, then repeat the
process until .EOF.
I know this sounds confusing and I can email you my documentation - since
this website does not allow for attachments.
Any assistances is greatly appreciated.
Karen
Hi
This is a stored procedure which will run a DTS and assign a variable to
file. Modify it for your needs.
I assumed you created the DTS and defined the global varaible.
CREATE proc spDTS
as
declare @.packagename varchar(255)
declare @.userpwd varchar(255) --login pwd
declare @.intsecurity bit --use non-zero to indicate integrated security
declare @.pkgPwd varchar(255) --package password
declare @.hr int
declare @.object int
set @.packagename='PackageName'
set @.userpwd=null
set @.pkgPwd=''
set @.intsecurity=0
--create a package object
EXEC @.hr = sp_OACreate 'DTS.Package', @.object OUTPUT
if @.hr <> 0
Begin
EXEC sp_displayoaerrorinfo @.object --, @.hr
RETURN
end
declare @.svr varchar(15)
declare @.login varchar(15)
select @.login = SUSER_NAME()
select @.svr = @.@.servername
declare @.flag int
select @.flag = 256
EXEC @.hr = sp_OAMethod @.object,
'LoadFromSqlServer',NULL, @.ServerName=@.svr, @.ServerUserName=@.login,
@.PackageName=@.packagename, @.Flags=@.flag, @.PackagePassword = @.pkgPwd
exec @.hr = sp_OAsetproperty
@.object,
'GlobalVariables
("FileLocation").Value', 'c:\MyFile.txt' --set location GV
EXEC @.hr = sp_OAMethod @.object, 'Execute'
IF @.hr <> 0
BEGIN
print 'Execute failed'
EXEC sp_displayoaerrorinfo @.object --, @.hr
RETURN
END
EXEC @.hr = sp_OADestroy @.object
IF @.hr <> 0
BEGIN
PRINT '*** Destroy Package failed'
EXEC sp_displayoaerrorinfo @.object, @.hr
RETURN
END
GO
"schaefer" <vkdschaefer1@.attbi.com> wrote in message
news:e%23Q8%23rW1EHA.1408@.TK2MSFTNGP10.phx.gbl...
> I am trying to set Global Variable that will open a txt file and import
the
> data into a sql table.
> I am currently using the sample I found at sqldts.com (Looping, Importing
> and Archiving).
> When I set the variable for filename = dbo.cadfilepath.filename, I get a
Bad
> Directory error.
> My filename data is stored in another table - I am looking for the best
> approach to calling the filename from the table = cadfilepath and opening
> the actual text file and importing the data into my project table.
> ie.
> Table = CADFilepath(pathname, projectnum)
> Pathname ProjectNum
> \\Seasc-Shared\Spln\Shared\Forecast\00000-000.txt 00000-000
> \\Seasc-Shared\Spln\Shared\Forecast\00000-001.txt 00000-001
> \\Seasc-Shared\Spln\Shared\Forecast\00000-002.txt 00000-002
> I want to access the info in CADFIlePath and then open the first record's
> text file and import this data into my Project Table, then repeat the
> process until .EOF.
> I know this sounds confusing and I can email you my documentation - since
> this website does not allow for attachments.
> Any assistances is greatly appreciated.
> Karen
>
|||One thing you can do is to load the Global variable directly from the table.
This is done by adding a Dynamic properties task.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"schaefer" <vkdschaefer1@.attbi.com> wrote in message
news:e%23Q8%23rW1EHA.1408@.TK2MSFTNGP10.phx.gbl...
> I am trying to set Global Variable that will open a txt file and import
the
> data into a sql table.
> I am currently using the sample I found at sqldts.com (Looping, Importing
> and Archiving).
> When I set the variable for filename = dbo.cadfilepath.filename, I get a
Bad
> Directory error.
> My filename data is stored in another table - I am looking for the best
> approach to calling the filename from the table = cadfilepath and opening
> the actual text file and importing the data into my project table.
> ie.
> Table = CADFilepath(pathname, projectnum)
> Pathname ProjectNum
> \\Seasc-Shared\Spln\Shared\Forecast\00000-000.txt 00000-000
> \\Seasc-Shared\Spln\Shared\Forecast\00000-001.txt 00000-001
> \\Seasc-Shared\Spln\Shared\Forecast\00000-002.txt 00000-002
> I want to access the info in CADFIlePath and then open the first record's
> text file and import this data into my Project Table, then repeat the
> process until .EOF.
> I know this sounds confusing and I can email you my documentation - since
> this website does not allow for attachments.
> Any assistances is greatly appreciated.
> Karen
>

backupset table info

My employer wants me to extract information for each nightly database backup
s
for each production server.
So I have created a DTS package that extracts this info into excel using the
following script:
use msdb
go
select database_name as dbname,
convert(char(20),backup_start_date,100) as start_time,
convert(char(20),backup_finish_date,100)
as end_time, backup_size from
backupset
where backup_start_date >
(SELECT MAX(backup_start_date) FROM backupset) - 1
order by database_name, backup_start_date
Which gives the following output:
dbname start_time end_time backup_size
db1 Mar 14 2005 7:00PM Mar 14 2005 7:00PM 716862464
db2 Mar 15 2005 8:00AM Mar 15 2005 8:00AM 7368704
db3 Mar 15 2005 10:00AM Mar 15 2005 10:00AM 1862656
db4 Mar 15 2005 12:00PM Mar 15 2005 12:00PM 3763200
He now wants two additional columns in the excel sheet. One with yesterday's
backup_size and 1 w ago's backup size.
Can anyone tell me how to modify this sql script to be able to retieve this
additional info from the backupset table?
Thanks,
HillaireLook at sysbackuphistory system table.
"gracie" <gracie@.discussions.microsoft.com> wrote in message
news:C0231A91-23B4-43E6-B6AD-37EDA6EC4D1A@.microsoft.com...
> My employer wants me to extract information for each nightly database
backups
> for each production server.
> So I have created a DTS package that extracts this info into excel using
the
> following script:
> use msdb
> go
> select database_name as dbname,
> convert(char(20),backup_start_date,100) as start_time,
> convert(char(20),backup_finish_date,100)
as end_time, backup_size from
> backupset
> where backup_start_date >
> (SELECT MAX(backup_start_date) FROM backupset) - 1
> order by database_name, backup_start_date
> Which gives the following output:
> dbname start_time end_time backup_size
> db1 Mar 14 2005 7:00PM Mar 14 2005 7:00PM 716862464
> DB2 Mar 15 2005 8:00AM Mar 15 2005 8:00AM 7368704
> db3 Mar 15 2005 10:00AM Mar 15 2005 10:00AM 1862656
> db4 Mar 15 2005 12:00PM Mar 15 2005 12:00PM 3763200
> He now wants two additional columns in the excel sheet. One with
yesterday's
> backup_size and 1 w ago's backup size.
> Can anyone tell me how to modify this sql script to be able to retieve
this
> additional info from the backupset table?
> Thanks,
> Hillaire
>|||Try,
select
a.database_name as dbname,
convert(char(20),a.backup_start_date,100) as start_time,
convert(char(20),a.backup_finish_date,100) as end_time,
a.backup_size,
b.backup_size
from
msdb.dbo.backupset as a
left join
msdb.dbo.backupset as b
on
a.database_name = b.database_name
and a.backup_start_date > (SELECT MAX(backup_start_date) FROM
msdb.dbo.backupset) - 1
and b.backup_start_date = dateadd(w, -1, (SELECT MAX(backup_start_date)
FROM msdb.dbo.backupset) - 1)
order by
a.database_name,
a.backup_start_date
AMB
"gracie" wrote:

> My employer wants me to extract information for each nightly database back
ups
> for each production server.
> So I have created a DTS package that extracts this info into excel using t
he
> following script:
> use msdb
> go
> select database_name as dbname,
> convert(char(20),backup_start_date,100) as start_time,
> convert(char(20),backup_finish_date,100)
as end_time, backup_size from
> backupset
> where backup_start_date >
> (SELECT MAX(backup_start_date) FROM backupset) - 1
> order by database_name, backup_start_date
> Which gives the following output:
> dbname start_time end_time backup_size
> db1 Mar 14 2005 7:00PM Mar 14 2005 7:00PM 716862464
> db2 Mar 15 2005 8:00AM Mar 15 2005 8:00AM 7368704
> db3 Mar 15 2005 10:00AM Mar 15 2005 10:00AM 1862656
> db4 Mar 15 2005 12:00PM Mar 15 2005 12:00PM 3763200
> He now wants two additional columns in the excel sheet. One with yesterday
's
> backup_size and 1 w ago's backup size.
> Can anyone tell me how to modify this sql script to be able to retieve thi
s
> additional info from the backupset table?
> Thanks,
> Hillaire
>|||What I actually need to accomplish is a comparison on the same excel sheet
showing yesterday's backup size and 1 w ago's backup size, side by side s
o
that he can look for any large decrepancies in the sizes.
"Uri Dimant" wrote:

> Look at sysbackuphistory system table.
>
>
> "gracie" <gracie@.discussions.microsoft.com> wrote in message
> news:C0231A91-23B4-43E6-B6AD-37EDA6EC4D1A@.microsoft.com...
> backups
> the
> yesterday's
> this
>
>

Backup to Unix/Linux

Hi, I have data sitting on an external MSSQL 2000 host that needs to be
backed up. Currently I'm using DTS on a Win2k desktop, but this isn't really
practical as this machine is used as a workstation.
Has anyone heard of any methods to backup to a Unix/Linux server?
Any help would be appreciated.
TIA JoIf the server can see a volume on the network then it should be possible to
write to it. If Windows can't see it then SQL Server won't either.
--
David Portas
SQL Server MVP
--|||"David Portas" wrote:
> If the server can see a volume on the network then it should be possible to
> write to it. If Windows can't see it then SQL Server won't either.
> --
> David Portas
> SQL Server MVP
> --
>
Thanks for the reply David. I can create a Samba share, but do you know of
any scripts or tools that can copy pull the data down? I only have DTS access
to my database on the hosting provider.
I realise most people have access to a Windows server, just wondering if
anyone had done/tried/heard of it.
Jo|||You can create a DTS package with one connection and a T-sql task... The
T-sql task would be a backup command ie.
backup database prod to disk = '\\myserver\mysharename\mybackup.bak' with
init
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Jo" <Jo@.discussions.microsoft.com> wrote in message
news:E1948BEF-BBF2-45A0-8695-14D766726225@.microsoft.com...
>
> "David Portas" wrote:
> > If the server can see a volume on the network then it should be possible
to
> > write to it. If Windows can't see it then SQL Server won't either.
> >
> > --
> > David Portas
> > SQL Server MVP
> > --
> >
> Thanks for the reply David. I can create a Samba share, but do you know of
> any scripts or tools that can copy pull the data down? I only have DTS
access
> to my database on the hosting provider.
> I realise most people have access to a Windows server, just wondering if
> anyone had done/tried/heard of it.
> Jo

Backup to Unix/Linux

Hi, I have data sitting on an external MSSQL 2000 host that needs to be
backed up. Currently I'm using DTS on a Win2k desktop, but this isn't really
practical as this machine is used as a workstation.
Has anyone heard of any methods to backup to a Unix/Linux server?
Any help would be appreciated.
TIA Jo
If the server can see a volume on the network then it should be possible to
write to it. If Windows can't see it then SQL Server won't either.
David Portas
SQL Server MVP
|||"David Portas" wrote:

> If the server can see a volume on the network then it should be possible to
> write to it. If Windows can't see it then SQL Server won't either.
> --
> David Portas
> SQL Server MVP
> --
>
Thanks for the reply David. I can create a Samba share, but do you know of
any scripts or tools that can copy pull the data down? I only have DTS access
to my database on the hosting provider.
I realise most people have access to a Windows server, just wondering if
anyone had done/tried/heard of it.
Jo
|||You can create a DTS package with one connection and a T-sql task... The
T-sql task would be a backup command ie.
backup database prod to disk = '\\myserver\mysharename\mybackup.bak' with
init
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Jo" <Jo@.discussions.microsoft.com> wrote in message
news:E1948BEF-BBF2-45A0-8695-14D766726225@.microsoft.com...[vbcol=seagreen]
>
> "David Portas" wrote:
to
> Thanks for the reply David. I can create a Samba share, but do you know of
> any scripts or tools that can copy pull the data down? I only have DTS
access
> to my database on the hosting provider.
> I realise most people have access to a Windows server, just wondering if
> anyone had done/tried/heard of it.
> Jo
|||"Wayne Snyder" wrote:

> You can create a DTS package with one connection and a T-sql task... The
> T-sql task would be a backup command ie.
> backup database prod to disk = '\\myserver\mysharename\mybackup.bak' with
> init
>
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
"Wayne Snyder" wrote:

> You can create a DTS package with one connection and a T-sql task... The
> T-sql task would be a backup command ie.
> backup database prod to disk = '\\myserver\mysharename\mybackup.bak' with
> init
Thanks for the reply Wayne. I'm still learning, but I'm not sure if I have
access to do this (this command would have to be run on the server?). The
database is sitting on a web host, I only have access (that I'm aware of?) by
using Enterprise Manager. I'm don't often work with MSSQL, but what I
initially thought was using a Perl or shell script that connects to the
correct port and runs some commands to initiate a backup. I was hoping that
someone had already written one too. I'm probably crazy :-P
The site I'm doing this for only has a Linux server that gets backed up
daily. Thanks for the help and patience guys :-)
Jo

Backup to Unix/Linux

Hi, I have data sitting on an external MSSQL 2000 host that needs to be
backed up. Currently I'm using DTS on a Win2k desktop, but this isn't really
practical as this machine is used as a workstation.
Has anyone heard of any methods to backup to a Unix/Linux server?
Any help would be appreciated.
TIA JoIf the server can see a volume on the network then it should be possible to
write to it. If Windows can't see it then SQL Server won't either.
David Portas
SQL Server MVP
--|||"David Portas" wrote:

> If the server can see a volume on the network then it should be possible t
o
> write to it. If Windows can't see it then SQL Server won't either.
> --
> David Portas
> SQL Server MVP
> --
>
Thanks for the reply David. I can create a Samba share, but do you know of
any scripts or tools that can copy pull the data down? I only have DTS acces
s
to my database on the hosting provider.
I realise most people have access to a Windows server, just wondering if
anyone had done/tried/heard of it.
Jo|||You can create a DTS package with one connection and a T-sql task... The
T-sql task would be a backup command ie.
backup database prod to disk = '\\myserver\mysharename\mybackup.bak' with
init
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Jo" <Jo@.discussions.microsoft.com> wrote in message
news:E1948BEF-BBF2-45A0-8695-14D766726225@.microsoft.com...
>
> "David Portas" wrote:
>
to[vbcol=seagreen]
> Thanks for the reply David. I can create a Samba share, but do you know of
> any scripts or tools that can copy pull the data down? I only have DTS
access
> to my database on the hosting provider.
> I realise most people have access to a Windows server, just wondering if
> anyone had done/tried/heard of it.
> Jo|||"Wayne Snyder" wrote:

> You can create a DTS package with one connection and a T-sql task... The
> T-sql task would be a backup command ie.
> backup database prod to disk = '\\myserver\mysharename\mybackup.bak' with
> init
>
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
"Wayne Snyder" wrote:

> You can create a DTS package with one connection and a T-sql task... The
> T-sql task would be a backup command ie.
> backup database prod to disk = '\\myserver\mysharename\mybackup.bak' with
> init
Thanks for the reply Wayne. I'm still learning, but I'm not sure if I have
access to do this (this command would have to be run on the server?). The
database is sitting on a web host, I only have access (that I'm aware of?) b
y
using Enterprise Manager. I'm don't often work with MSSQL, but what I
initially thought was using a PERL or shell script that connects to the
correct port and runs some commands to initiate a backup. I was hoping that
someone had already written one too. I'm probably crazy :-P
The site I'm doing this for only has a Linux server that gets backed up
daily. Thanks for the help and patience guys :-)
Jo