I am running an Import from an Access database into my SQL Server 2000
database.
The problem is that when I run this process it Appends the Access details to
the SQL Server when I want to replace the SQL Server data.
Can anyone tell me how to get around this. I'm pretty new to this sort of
thing.
TIAPoppy,
You can make use of DTS (SQL Server) which will have an option to "drop existing table" or "replace
existing date". For instance if you open DTS import/export wizard. choose source and destination of
which one will be access. On the next screen select "copy tables/views from source database" . on
the next screen select the required tables to transfer and click on button of "transform" you will
get relevant options here to transfer the data. The one which will be relevent to you will be
"delete rows in destination table"
- Vishal|||Thanks
"Vishal Parkar" <_vgparkar@.yahoo.co.in> wrote in message
news:#mdDhckmDHA.2200@.TK2MSFTNGP12.phx.gbl...
> Poppy,
> You can make use of DTS (SQL Server) which will have an option to "drop
existing table" or "replace
> existing date". For instance if you open DTS import/export wizard. choose
source and destination of
> which one will be access. On the next screen select "copy tables/views
from source database" . on
> the next screen select the required tables to transfer and click on button
of "transform" you will
> get relevant options here to transfer the data. The one which will be
relevent to you will be
> "delete rows in destination table"
>
> --
> - Vishal
>
>
Showing posts with label run. Show all posts
Showing posts with label run. Show all posts
Friday, March 30, 2012
Import Performance file Run Command
I have Windows 2000 Advanced Server with SQL Server 2000
Enterprise with SP3A. I trying to set up a job to import
Performance Monitors daily into SQL Server table. How
would I set up Performance Monitor to stop at 7AM close
the present file, create a new file at 7:02AM start
collecting statistics. Then I will DTS file into a SQL
Server table.
My performance monitor job name is SAVE_STATISTICS what
run command should I use to start my SAVE_STATISTICS at 7
AM and stop it daily 7:02 AM?
This would be a very help to resolve this issue.
Thank You,
Dan
you could potentially use LOGMAN native command from a remote XP and Windows
2003 computer to Start and Stop the logs at the specified time each day - ie
a Daily scheduled task (Control Panel/ Scheduled Tasks) to do it.
And then as part of the definition of the Perfmon Log / Schedule add the
"Run this command" to have a batch file that calls DTSRUN and executes your
DTS package to import the Data.
a bit messy I admit
regards
Andy.
"Dan" <anonymous@.discussions.microsoft.com> wrote in message
news:26ad201c461ce$4a260760$a301280a@.phx.gbl...
>
> I have Windows 2000 Advanced Server with SQL Server 2000
> Enterprise with SP3A. I trying to set up a job to import
> Performance Monitors daily into SQL Server table. How
> would I set up Performance Monitor to stop at 7AM close
> the present file, create a new file at 7:02AM start
> collecting statistics. Then I will DTS file into a SQL
> Server table.
> My performance monitor job name is SAVE_STATISTICS what
> run command should I use to start my SAVE_STATISTICS at 7
> AM and stop it daily 7:02 AM?
> This would be a very help to resolve this issue.
> Thank You,
> Dan
>
|||Hi
I replied to you previous post on this subject! Perfmon has it's own method
of scheduling, through the Scheduling tab on the Properties dialog. You can
tell perfmon to stop the performance monitor and start a new log after a
given period. You can also specify that it runs a command when this happens.
This could be your DTS job. To change the name of the input file check out:
http://www.sqldts.com/default.aspx?200
John
"Dan" <anonymous@.discussions.microsoft.com> wrote in message
news:26ad201c461ce$4a260760$a301280a@.phx.gbl...
>
> I have Windows 2000 Advanced Server with SQL Server 2000
> Enterprise with SP3A. I trying to set up a job to import
> Performance Monitors daily into SQL Server table. How
> would I set up Performance Monitor to stop at 7AM close
> the present file, create a new file at 7:02AM start
> collecting statistics. Then I will DTS file into a SQL
> Server table.
> My performance monitor job name is SAVE_STATISTICS what
> run command should I use to start my SAVE_STATISTICS at 7
> AM and stop it daily 7:02 AM?
> This would be a very help to resolve this issue.
> Thank You,
> Dan
>
Enterprise with SP3A. I trying to set up a job to import
Performance Monitors daily into SQL Server table. How
would I set up Performance Monitor to stop at 7AM close
the present file, create a new file at 7:02AM start
collecting statistics. Then I will DTS file into a SQL
Server table.
My performance monitor job name is SAVE_STATISTICS what
run command should I use to start my SAVE_STATISTICS at 7
AM and stop it daily 7:02 AM?
This would be a very help to resolve this issue.
Thank You,
Dan
you could potentially use LOGMAN native command from a remote XP and Windows
2003 computer to Start and Stop the logs at the specified time each day - ie
a Daily scheduled task (Control Panel/ Scheduled Tasks) to do it.
And then as part of the definition of the Perfmon Log / Schedule add the
"Run this command" to have a batch file that calls DTSRUN and executes your
DTS package to import the Data.
a bit messy I admit
regards
Andy.
"Dan" <anonymous@.discussions.microsoft.com> wrote in message
news:26ad201c461ce$4a260760$a301280a@.phx.gbl...
>
> I have Windows 2000 Advanced Server with SQL Server 2000
> Enterprise with SP3A. I trying to set up a job to import
> Performance Monitors daily into SQL Server table. How
> would I set up Performance Monitor to stop at 7AM close
> the present file, create a new file at 7:02AM start
> collecting statistics. Then I will DTS file into a SQL
> Server table.
> My performance monitor job name is SAVE_STATISTICS what
> run command should I use to start my SAVE_STATISTICS at 7
> AM and stop it daily 7:02 AM?
> This would be a very help to resolve this issue.
> Thank You,
> Dan
>
|||Hi
I replied to you previous post on this subject! Perfmon has it's own method
of scheduling, through the Scheduling tab on the Properties dialog. You can
tell perfmon to stop the performance monitor and start a new log after a
given period. You can also specify that it runs a command when this happens.
This could be your DTS job. To change the name of the input file check out:
http://www.sqldts.com/default.aspx?200
John
"Dan" <anonymous@.discussions.microsoft.com> wrote in message
news:26ad201c461ce$4a260760$a301280a@.phx.gbl...
>
> I have Windows 2000 Advanced Server with SQL Server 2000
> Enterprise with SP3A. I trying to set up a job to import
> Performance Monitors daily into SQL Server table. How
> would I set up Performance Monitor to stop at 7AM close
> the present file, create a new file at 7:02AM start
> collecting statistics. Then I will DTS file into a SQL
> Server table.
> My performance monitor job name is SAVE_STATISTICS what
> run command should I use to start my SAVE_STATISTICS at 7
> AM and stop it daily 7:02 AM?
> This would be a very help to resolve this issue.
> Thank You,
> Dan
>
Import Performance file Run Command
I have Windows 2000 Advanced Server with SQL Server 2000
Enterprise with SP3A. I trying to set up a job to import
Performance Monitors daily into SQL Server table. How
would I set up Performance Monitor to stop at 7AM close
the present file, create a new file at 7:02AM start
collecting statistics. Then I will DTS file into a SQL
Server table.
My performance monitor job name is SAVE_STATISTICS what
run command should I use to start my SAVE_STATISTICS at 7
AM and stop it daily 7:02 AM?
This would be a very help to resolve this issue.
Thank You,
Danyou could potentially use LOGMAN native command from a remote XP and Windows
2003 computer to Start and Stop the logs at the specified time each day - ie
a Daily scheduled task (Control Panel/ Scheduled Tasks) to do it.
And then as part of the definition of the Perfmon Log / Schedule add the
"Run this command" to have a batch file that calls DTSRUN and executes your
DTS package to import the Data.
a bit messy I admit
regards
Andy.
"Dan" <anonymous@.discussions.microsoft.com> wrote in message
news:26ad201c461ce$4a260760$a301280a@.phx.gbl...
>
> I have Windows 2000 Advanced Server with SQL Server 2000
> Enterprise with SP3A. I trying to set up a job to import
> Performance Monitors daily into SQL Server table. How
> would I set up Performance Monitor to stop at 7AM close
> the present file, create a new file at 7:02AM start
> collecting statistics. Then I will DTS file into a SQL
> Server table.
> My performance monitor job name is SAVE_STATISTICS what
> run command should I use to start my SAVE_STATISTICS at 7
> AM and stop it daily 7:02 AM?
> This would be a very help to resolve this issue.
> Thank You,
> Dan
>|||Hi
I replied to you previous post on this subject! Perfmon has it's own method
of scheduling, through the Scheduling tab on the Properties dialog. You can
tell perfmon to stop the performance monitor and start a new log after a
given period. You can also specify that it runs a command when this happens.
This could be your DTS job. To change the name of the input file check out:
http://www.sqldts.com/default.aspx?200
John
"Dan" <anonymous@.discussions.microsoft.com> wrote in message
news:26ad201c461ce$4a260760$a301280a@.phx.gbl...
>
> I have Windows 2000 Advanced Server with SQL Server 2000
> Enterprise with SP3A. I trying to set up a job to import
> Performance Monitors daily into SQL Server table. How
> would I set up Performance Monitor to stop at 7AM close
> the present file, create a new file at 7:02AM start
> collecting statistics. Then I will DTS file into a SQL
> Server table.
> My performance monitor job name is SAVE_STATISTICS what
> run command should I use to start my SAVE_STATISTICS at 7
> AM and stop it daily 7:02 AM?
> This would be a very help to resolve this issue.
> Thank You,
> Dan
>
Enterprise with SP3A. I trying to set up a job to import
Performance Monitors daily into SQL Server table. How
would I set up Performance Monitor to stop at 7AM close
the present file, create a new file at 7:02AM start
collecting statistics. Then I will DTS file into a SQL
Server table.
My performance monitor job name is SAVE_STATISTICS what
run command should I use to start my SAVE_STATISTICS at 7
AM and stop it daily 7:02 AM?
This would be a very help to resolve this issue.
Thank You,
Danyou could potentially use LOGMAN native command from a remote XP and Windows
2003 computer to Start and Stop the logs at the specified time each day - ie
a Daily scheduled task (Control Panel/ Scheduled Tasks) to do it.
And then as part of the definition of the Perfmon Log / Schedule add the
"Run this command" to have a batch file that calls DTSRUN and executes your
DTS package to import the Data.
a bit messy I admit
regards
Andy.
"Dan" <anonymous@.discussions.microsoft.com> wrote in message
news:26ad201c461ce$4a260760$a301280a@.phx.gbl...
>
> I have Windows 2000 Advanced Server with SQL Server 2000
> Enterprise with SP3A. I trying to set up a job to import
> Performance Monitors daily into SQL Server table. How
> would I set up Performance Monitor to stop at 7AM close
> the present file, create a new file at 7:02AM start
> collecting statistics. Then I will DTS file into a SQL
> Server table.
> My performance monitor job name is SAVE_STATISTICS what
> run command should I use to start my SAVE_STATISTICS at 7
> AM and stop it daily 7:02 AM?
> This would be a very help to resolve this issue.
> Thank You,
> Dan
>|||Hi
I replied to you previous post on this subject! Perfmon has it's own method
of scheduling, through the Scheduling tab on the Properties dialog. You can
tell perfmon to stop the performance monitor and start a new log after a
given period. You can also specify that it runs a command when this happens.
This could be your DTS job. To change the name of the input file check out:
http://www.sqldts.com/default.aspx?200
John
"Dan" <anonymous@.discussions.microsoft.com> wrote in message
news:26ad201c461ce$4a260760$a301280a@.phx.gbl...
>
> I have Windows 2000 Advanced Server with SQL Server 2000
> Enterprise with SP3A. I trying to set up a job to import
> Performance Monitors daily into SQL Server table. How
> would I set up Performance Monitor to stop at 7AM close
> the present file, create a new file at 7:02AM start
> collecting statistics. Then I will DTS file into a SQL
> Server table.
> My performance monitor job name is SAVE_STATISTICS what
> run command should I use to start my SAVE_STATISTICS at 7
> AM and stop it daily 7:02 AM?
> This would be a very help to resolve this issue.
> Thank You,
> Dan
>
Import Performance file Run Command
I have Windows 2000 Advanced Server with SQL Server 2000
Enterprise with SP3A. I trying to set up a job to import
Performance Monitors daily into SQL Server table. How
would I set up Performance Monitor to stop at 7AM close
the present file, create a new file at 7:02AM start
collecting statistics. Then I will DTS file into a SQL
Server table.
My performance monitor job name is SAVE_STATISTICS what
run command should I use to start my SAVE_STATISTICS at 7
AM and stop it daily 7:02 AM?
This would be a very help to resolve this issue.
Thank You,
Danyou could potentially use LOGMAN native command from a remote XP and Windows
2003 computer to Start and Stop the logs at the specified time each day - ie
a Daily scheduled task (Control Panel/ Scheduled Tasks) to do it.
And then as part of the definition of the Perfmon Log / Schedule add the
"Run this command" to have a batch file that calls DTSRUN and executes your
DTS package to import the Data.
a bit messy I admit
regards
Andy.
"Dan" <anonymous@.discussions.microsoft.com> wrote in message
news:26ad201c461ce$4a260760$a301280a@.phx
.gbl...
>
> I have Windows 2000 Advanced Server with SQL Server 2000
> Enterprise with SP3A. I trying to set up a job to import
> Performance Monitors daily into SQL Server table. How
> would I set up Performance Monitor to stop at 7AM close
> the present file, create a new file at 7:02AM start
> collecting statistics. Then I will DTS file into a SQL
> Server table.
> My performance monitor job name is SAVE_STATISTICS what
> run command should I use to start my SAVE_STATISTICS at 7
> AM and stop it daily 7:02 AM?
> This would be a very help to resolve this issue.
> Thank You,
> Dan
>|||Hi
I replied to you previous post on this subject! Perfmon has it's own method
of scheduling, through the Scheduling tab on the Properties dialog. You can
tell perfmon to stop the performance monitor and start a new log after a
given period. You can also specify that it runs a command when this happens.
This could be your DTS job. To change the name of the input file check out:
http://www.sqldts.com/default.aspx?200
John
"Dan" <anonymous@.discussions.microsoft.com> wrote in message
news:26ad201c461ce$4a260760$a301280a@.phx
.gbl...
>
> I have Windows 2000 Advanced Server with SQL Server 2000
> Enterprise with SP3A. I trying to set up a job to import
> Performance Monitors daily into SQL Server table. How
> would I set up Performance Monitor to stop at 7AM close
> the present file, create a new file at 7:02AM start
> collecting statistics. Then I will DTS file into a SQL
> Server table.
> My performance monitor job name is SAVE_STATISTICS what
> run command should I use to start my SAVE_STATISTICS at 7
> AM and stop it daily 7:02 AM?
> This would be a very help to resolve this issue.
> Thank You,
> Dan
>
Enterprise with SP3A. I trying to set up a job to import
Performance Monitors daily into SQL Server table. How
would I set up Performance Monitor to stop at 7AM close
the present file, create a new file at 7:02AM start
collecting statistics. Then I will DTS file into a SQL
Server table.
My performance monitor job name is SAVE_STATISTICS what
run command should I use to start my SAVE_STATISTICS at 7
AM and stop it daily 7:02 AM?
This would be a very help to resolve this issue.
Thank You,
Danyou could potentially use LOGMAN native command from a remote XP and Windows
2003 computer to Start and Stop the logs at the specified time each day - ie
a Daily scheduled task (Control Panel/ Scheduled Tasks) to do it.
And then as part of the definition of the Perfmon Log / Schedule add the
"Run this command" to have a batch file that calls DTSRUN and executes your
DTS package to import the Data.
a bit messy I admit
regards
Andy.
"Dan" <anonymous@.discussions.microsoft.com> wrote in message
news:26ad201c461ce$4a260760$a301280a@.phx
.gbl...
>
> I have Windows 2000 Advanced Server with SQL Server 2000
> Enterprise with SP3A. I trying to set up a job to import
> Performance Monitors daily into SQL Server table. How
> would I set up Performance Monitor to stop at 7AM close
> the present file, create a new file at 7:02AM start
> collecting statistics. Then I will DTS file into a SQL
> Server table.
> My performance monitor job name is SAVE_STATISTICS what
> run command should I use to start my SAVE_STATISTICS at 7
> AM and stop it daily 7:02 AM?
> This would be a very help to resolve this issue.
> Thank You,
> Dan
>|||Hi
I replied to you previous post on this subject! Perfmon has it's own method
of scheduling, through the Scheduling tab on the Properties dialog. You can
tell perfmon to stop the performance monitor and start a new log after a
given period. You can also specify that it runs a command when this happens.
This could be your DTS job. To change the name of the input file check out:
http://www.sqldts.com/default.aspx?200
John
"Dan" <anonymous@.discussions.microsoft.com> wrote in message
news:26ad201c461ce$4a260760$a301280a@.phx
.gbl...
>
> I have Windows 2000 Advanced Server with SQL Server 2000
> Enterprise with SP3A. I trying to set up a job to import
> Performance Monitors daily into SQL Server table. How
> would I set up Performance Monitor to stop at 7AM close
> the present file, create a new file at 7:02AM start
> collecting statistics. Then I will DTS file into a SQL
> Server table.
> My performance monitor job name is SAVE_STATISTICS what
> run command should I use to start my SAVE_STATISTICS at 7
> AM and stop it daily 7:02 AM?
> This would be a very help to resolve this issue.
> Thank You,
> Dan
>
Wednesday, March 28, 2012
Import index and constain
Dear
I export the database table,SP,view,use..etc to script
file. After i run the script to another server to rebuild
the database. Only the table(index and constain) can't
import to new database but i have choice the option(index
and constain) when i export the database to script. What
is ths problem?
Many Thanks
JohnJohn
How did you export your objects?
Try using DTS , there is an optino "Transfer Objects" so you will find that
you can export indexes and keys.
"John" <anonymous@.discussions.microsoft.com> wrote in message
news:066301c3c476$0eb20a80$a501280a@.phx.gbl...
> Dear
> I export the database table,SP,view,use..etc to script
> file. After i run the script to another server to rebuild
> the database. Only the table(index and constain) can't
> import to new database but i have choice the option(index
> and constain) when i export the database to script. What
> is ths problem?
> Many Thanks
> John
I export the database table,SP,view,use..etc to script
file. After i run the script to another server to rebuild
the database. Only the table(index and constain) can't
import to new database but i have choice the option(index
and constain) when i export the database to script. What
is ths problem?
Many Thanks
JohnJohn
How did you export your objects?
Try using DTS , there is an optino "Transfer Objects" so you will find that
you can export indexes and keys.
"John" <anonymous@.discussions.microsoft.com> wrote in message
news:066301c3c476$0eb20a80$a501280a@.phx.gbl...
> Dear
> I export the database table,SP,view,use..etc to script
> file. After i run the script to another server to rebuild
> the database. Only the table(index and constain) can't
> import to new database but i have choice the option(index
> and constain) when i export the database to script. What
> is ths problem?
> Many Thanks
> John
Wednesday, March 21, 2012
Import Excel Spreadsheet
I am using TransferSpreadsheet from an ADP file to import an Excel
spreadsheet into an MSDE database. On one computer, when I run the code to
import the spreadsheet, it prompts for an SQLServer login. The same user ca
n
try it on a different computer and it will work fine. Data in the tables of
the database can be viewed and edited on the bad computer. But the
spreadsheet cannot be imported. I even tried the Get External Data->Import
menu option and it prompts for SQLServer login also.
Any ideas on what to look at on this computer to determine the problem?
Or any suggestions on a better way to import a spreadsheet into an SQLServer
database.?
Thanks!
JerryJerry..
you have to tell us what is the error that you get when you import.
What I usually do (if its only a few thousand rows to import), then I go
forr opendatasource(ODS). Check out in BOL for ODS and they give an example
of select from excel. U can then insert into the table in SQL Server.
But to investigate on why this error is occuring, you need to give us more
info on why its not getting imported.
--
"JerryWendell" wrote:
> I am using TransferSpreadsheet from an ADP file to import an Excel
> spreadsheet into an MSDE database. On one computer, when I run the code t
o
> import the spreadsheet, it prompts for an SQLServer login. The same user
can
> try it on a different computer and it will work fine. Data in the tables
of
> the database can be viewed and edited on the bad computer. But the
> spreadsheet cannot be imported. I even tried the Get External Data->Impor
t
> menu option and it prompts for SQLServer login also.
> Any ideas on what to look at on this computer to determine the problem?
> Or any suggestions on a better way to import a spreadsheet into an SQLServ
er
> database.?
> Thanks!
> Jerry
>|||Thanks for the responds.
This only happens on one particular computer. It works fine on the 8 other
computers that access the database. They are all running Access 2003 and
Windows XP.
When I try to import the spreadsheet, I am prompted to enter an SQL Server
Login. When I enter a valid login (I know it is a valid login because it
works from the other computers) after about a minute I get the following
error messages:
Connection Failed
SQLState: '01S00'
SQLServerError: 0
[Microsoft][ODBC SQLServerDriver][dbnmpntw]Invalid Connection String Attribute
SQLState: '01000'
SQLServerError: 2
[Microsoft][ODBC SQLServerDriver][dbnmpntw]ConnectionOpen
(CreateFile())
SQLState: '08001'
SQLServerError: 6
[Microsoft][ODBC SQLServerDriver][dbnmpntw]Specified SQL Server not found
Thanks for your help.
Jerry
"Omnibuzz" wrote:
> Jerry..
> you have to tell us what is the error that you get when you import.
> What I usually do (if its only a few thousand rows to import), then I go
> forr opendatasource(ODS). Check out in BOL for ODS and they give an exampl
e
> of select from excel. U can then insert into the table in SQL Server.
> But to investigate on why this error is occuring, you need to give us more
> info on why its not getting imported.
> --
>
>
> "JerryWendell" wrote:
>sql
spreadsheet into an MSDE database. On one computer, when I run the code to
import the spreadsheet, it prompts for an SQLServer login. The same user ca
n
try it on a different computer and it will work fine. Data in the tables of
the database can be viewed and edited on the bad computer. But the
spreadsheet cannot be imported. I even tried the Get External Data->Import
menu option and it prompts for SQLServer login also.
Any ideas on what to look at on this computer to determine the problem?
Or any suggestions on a better way to import a spreadsheet into an SQLServer
database.?
Thanks!
JerryJerry..
you have to tell us what is the error that you get when you import.
What I usually do (if its only a few thousand rows to import), then I go
forr opendatasource(ODS). Check out in BOL for ODS and they give an example
of select from excel. U can then insert into the table in SQL Server.
But to investigate on why this error is occuring, you need to give us more
info on why its not getting imported.
--
"JerryWendell" wrote:
> I am using TransferSpreadsheet from an ADP file to import an Excel
> spreadsheet into an MSDE database. On one computer, when I run the code t
o
> import the spreadsheet, it prompts for an SQLServer login. The same user
can
> try it on a different computer and it will work fine. Data in the tables
of
> the database can be viewed and edited on the bad computer. But the
> spreadsheet cannot be imported. I even tried the Get External Data->Impor
t
> menu option and it prompts for SQLServer login also.
> Any ideas on what to look at on this computer to determine the problem?
> Or any suggestions on a better way to import a spreadsheet into an SQLServ
er
> database.?
> Thanks!
> Jerry
>|||Thanks for the responds.
This only happens on one particular computer. It works fine on the 8 other
computers that access the database. They are all running Access 2003 and
Windows XP.
When I try to import the spreadsheet, I am prompted to enter an SQL Server
Login. When I enter a valid login (I know it is a valid login because it
works from the other computers) after about a minute I get the following
error messages:
Connection Failed
SQLState: '01S00'
SQLServerError: 0
[Microsoft][ODBC SQLServerDriver][dbnmpntw]Invalid Connection String Attribute
SQLState: '01000'
SQLServerError: 2
[Microsoft][ODBC SQLServerDriver][dbnmpntw]ConnectionOpen
(CreateFile())
SQLState: '08001'
SQLServerError: 6
[Microsoft][ODBC SQLServerDriver][dbnmpntw]Specified SQL Server not found
Thanks for your help.
Jerry
"Omnibuzz" wrote:
> Jerry..
> you have to tell us what is the error that you get when you import.
> What I usually do (if its only a few thousand rows to import), then I go
> forr opendatasource(ODS). Check out in BOL for ODS and they give an exampl
e
> of select from excel. U can then insert into the table in SQL Server.
> But to investigate on why this error is occuring, you need to give us more
> info on why its not getting imported.
> --
>
>
> "JerryWendell" wrote:
>sql
Import Excel file into SQL Server
Could someone provide me with a URL that explains how to set up DTS to import an Excel file into SQL Server?
I plan to run this infrequently so there is no need to schedule a recurring run.
Thanks,
CraigHere is sample how to get rows from Excel file in SQL - use OPENDATASOURCE:
select * from OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source="\\comp\share name\filename.xls";Extended Properties=Excel 8.0')...['2005$']
where ['2005$'] is Sheet name in Excel (2005).
Format ['name$'] uses only for sheets started with digit or contained spaces.
overwise:
Sheet1$ for sheet with name Sheet1|||Thanks for the reply. I will try that.
If I can get it imported, can I include it as a temp table in queries and would I need a linked server for that?
Craig
Import error
I am attempting to import an access mdb file from a remote server.
When I run the import wizard I get the follwing error:
"Cannot start your application. The Workgroup information file is missing or
opened exclusively by another user."
I have checked to see if the lock file is there but its not.
Any ideas ?Not sure if this will resolve the problem but you can check the Advanced
properties of the the Access connection and edit the System Database value
to point to the location of the mdw file.
Rand
This posting is provided "as is" with no warranties and confers no rights.sql
When I run the import wizard I get the follwing error:
"Cannot start your application. The Workgroup information file is missing or
opened exclusively by another user."
I have checked to see if the lock file is there but its not.
Any ideas ?Not sure if this will resolve the problem but you can check the Advanced
properties of the the Access connection and edit the System Database value
to point to the location of the mdw file.
Rand
This posting is provided "as is" with no warranties and confers no rights.sql
Monday, March 19, 2012
import database
I need to import a database from MS SQL 2000. I created the database. Then I create the database scripts and run them in sql express but I get this error.
"The specified schema name "username" either does not exist or you do not have permission to use it."
I have also tried to use DTS on MS SQL 2000 to export the data into text file. I do not have the ability to copy the mdf files and attach them in SQL express or make a backup.SQL Server introduced the schema whcih did not exist in SQL Server 200. YOu would either have to rename the object owner in the script to a existing schema (like dbo) or create the appropiate schemas in SQL Server 2005 which were fromer the owners of an object.
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de|||
Hi,
Its self Explainatery message, it means that the Schema or User (Owner) does not exists on the system you are trying to run the script, create a similar user /change the schema in script . Do you have proper privilege !!!? Refer some link below, it will clear your doubts
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=176311&SiteID=1
http://msdn.microsoft.com/msdnmag/issues/05/06/SQLServerSecurity/
http://support.esri.com/index.cfm?fa=knowledgebase.techArticles.articleShow&d=30620
Hemantgiri S. Goswami
|||I made the schema change and it worked. Thanks.|||Thanks for the links it make a little more sense now.|||Hi,
Thanks for the information, please click on Help full button if you feel answer is usefull.
Hemantgiri S. Goswami
Import data to sql Server 6.5
I have a datafile, Bendill.dat, from a SQL6.5 database I used to run some
time ago. The machine on which this used to run is not operational any more.
I have set up SQL Server 6.5 on a virtual machine, and want to import my old
data from Bendill.dat into this SQL6.5.
How do I do it?
When I have this running, my ultimate goal is to convert the SQL6.5 data to
SQL2000 and from there to SQL2005 (as there is no direct upgrade path from
SQL6.5 to SQL2005).
Regards,
Gudni
Gudni G. Sigurdsson (GudniGSigurdsson@.discussions.microsoft.com) writes:
> I have a datafile, Bendill.dat, from a SQL6.5 database I used to run
> some time ago. The machine on which this used to run is not operational
> any more.
> I have set up SQL Server 6.5 on a virtual machine, and want to import
> my old data from Bendill.dat into this SQL6.5.
> How do I do it?
Do you have any idea of how that file is organized? I assume that this
is a device file, such a file can have multiple databases, or even worse,
multiple fragments of databases. That is, a database can be on multiple
device files.
If you know that there is only a single database on the file, it's a
little easier, but it would still help to know what is the data and
what is the log.
I think the command to use would be DISK REINIT, but I don't think I
ever used it under all those years I ran 4.x and 6.x.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
|||Tibor Karaszi (tibor_please.no.email_karaszi@.hotmail.nomail.com) writes:
> Yes, DISK REINIT which will re-create the database devices. Then DISK
> REFIT, which will create the database based on those devices. DISK
> REINIT is the tricky part since ideally you want to run it so you get a
> proper layout of data vs. log allocations.
It would really help if Gudni has the contents of syssegments from the
6.5 master database saved somewhere.
That old architecture is certainly not missed at all.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
|||Hello and thanks for responses so far.
This file is a device file unfortunately rather than a backup file.
As far as I know there is only one database in this file bendill.dat and
that is the database I am interested in. And I believe this file stores the
whole database.
Later today I will try your suggestions and see how far that will get me.
"Tibor Karaszi" wrote:
> I think it is sysusages which is the one that is needed in order to get the database fragment
> layout.
>
> I couldn't agree more! :-)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
> news:Xns99EBB6194C187Yazorman@.127.0.0.1...
>
|||Thanks both of you Karaszi and Sommarskog.
I have not been working on this problem since 11/17/2007 but I will have to
come back to it. Unfortunately, I have had no success so far. I feel I have
to get hold of SQL Server 6.5 Books Online to better understand what I am
doing and how I should go about following your propositions.
So long,
Gudni
"Tibor Karaszi" wrote:
> You should be able to do DISK REINIT and use the size of your file to calculate the size of the file
> that you specify for your DISK REINIT. Depending on the original layout, you might end up with data
> pages where SQL Server expects log records and vice versa, but that should hopefully not prohibit
> you from exporting the data.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Gudni G. Sigurdsson" <GudniGSigurdsson@.discussions.microsoft.com> wrote in message
> news:69643E74-8ABA-45A7-8FA4-F6B506A94320@.microsoft.com...
>
|||Gudni G. Sigurdsson (GudniGSigurdsson@.discussions.microsoft.com) writes:
> I have not been working on this problem since 11/17/2007 but I will have
> to come back to it. Unfortunately, I have had no success so far. I
> feel I have to get hold of SQL Server 6.5 Books Online to better
> understand what I am doing and how I should go about following your
> propositions.
Isn't Books Online installed with the 6.5 installation you have?
If you have access to MSDN Subcriber Downloads, you can get SQL 6.5
with Books Online and all there.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
time ago. The machine on which this used to run is not operational any more.
I have set up SQL Server 6.5 on a virtual machine, and want to import my old
data from Bendill.dat into this SQL6.5.
How do I do it?
When I have this running, my ultimate goal is to convert the SQL6.5 data to
SQL2000 and from there to SQL2005 (as there is no direct upgrade path from
SQL6.5 to SQL2005).
Regards,
Gudni
Gudni G. Sigurdsson (GudniGSigurdsson@.discussions.microsoft.com) writes:
> I have a datafile, Bendill.dat, from a SQL6.5 database I used to run
> some time ago. The machine on which this used to run is not operational
> any more.
> I have set up SQL Server 6.5 on a virtual machine, and want to import
> my old data from Bendill.dat into this SQL6.5.
> How do I do it?
Do you have any idea of how that file is organized? I assume that this
is a device file, such a file can have multiple databases, or even worse,
multiple fragments of databases. That is, a database can be on multiple
device files.
If you know that there is only a single database on the file, it's a
little easier, but it would still help to know what is the data and
what is the log.
I think the command to use would be DISK REINIT, but I don't think I
ever used it under all those years I ran 4.x and 6.x.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
|||Tibor Karaszi (tibor_please.no.email_karaszi@.hotmail.nomail.com) writes:
> Yes, DISK REINIT which will re-create the database devices. Then DISK
> REFIT, which will create the database based on those devices. DISK
> REINIT is the tricky part since ideally you want to run it so you get a
> proper layout of data vs. log allocations.
It would really help if Gudni has the contents of syssegments from the
6.5 master database saved somewhere.
That old architecture is certainly not missed at all.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
|||Hello and thanks for responses so far.
This file is a device file unfortunately rather than a backup file.
As far as I know there is only one database in this file bendill.dat and
that is the database I am interested in. And I believe this file stores the
whole database.
Later today I will try your suggestions and see how far that will get me.
"Tibor Karaszi" wrote:
> I think it is sysusages which is the one that is needed in order to get the database fragment
> layout.
>
> I couldn't agree more! :-)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
> news:Xns99EBB6194C187Yazorman@.127.0.0.1...
>
|||Thanks both of you Karaszi and Sommarskog.
I have not been working on this problem since 11/17/2007 but I will have to
come back to it. Unfortunately, I have had no success so far. I feel I have
to get hold of SQL Server 6.5 Books Online to better understand what I am
doing and how I should go about following your propositions.
So long,
Gudni
"Tibor Karaszi" wrote:
> You should be able to do DISK REINIT and use the size of your file to calculate the size of the file
> that you specify for your DISK REINIT. Depending on the original layout, you might end up with data
> pages where SQL Server expects log records and vice versa, but that should hopefully not prohibit
> you from exporting the data.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Gudni G. Sigurdsson" <GudniGSigurdsson@.discussions.microsoft.com> wrote in message
> news:69643E74-8ABA-45A7-8FA4-F6B506A94320@.microsoft.com...
>
|||Gudni G. Sigurdsson (GudniGSigurdsson@.discussions.microsoft.com) writes:
> I have not been working on this problem since 11/17/2007 but I will have
> to come back to it. Unfortunately, I have had no success so far. I
> feel I have to get hold of SQL Server 6.5 Books Online to better
> understand what I am doing and how I should go about following your
> propositions.
Isn't Books Online installed with the 6.5 installation you have?
If you have access to MSDN Subcriber Downloads, you can get SQL 6.5
with Books Online and all there.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Friday, March 9, 2012
Import Data and Identity fields
When i run the "Import Data" task from SQL 2005 Managment Studio and select the "Copy data from one or more tables or views" option, I notice the task does not re-create the "Identity" field definitions on the destination table. (SQL 2000 EM did this.) Is there a way to tell the import task to include the identity field definition when creating the destiination tables?
The only work around i've found in SQL Mgmt Studio takes multiple steps:
1) run Generate scripts on the source database, and select all tables to create the table scripts (this includes the identity field on each table)
2) run the script on the destination database to created the tables
3) run the import task, edit mappings and select "enable identity insert".
this is really a pain since i could accomplish this all in 1 step in SQL 2000 EM.
Any suggestions? Please help!
Microsoft? You there? Anybody?|||
You might get more traction on this in the SSIS forum, but I'll give it a try. By the way, this is an all-volunteer forum, so if you need immediate support, see http://microsoft.com/support. It's not only us Microsoft working folks in here, we just maintain the forum and try to help as much as we can.
This series of posts might help:
http://www.developerdotstar.com/community/node/727#comment-8456
Basically it involves the "FAST LOAD" option.
Buck Woody
SQL Server Team
Import Data and Identity fields
When i run the "Import Data" task from SQL 2005 Managment Studio and select the "Copy data from one or more tables or views" option, I notice the task does not re-create the "Identity" field definitions on the destination table. (SQL 2000 EM did this.) Is there a way to tell the import task to include the identity field definition when creating the destiination tables?
The only work around i've found in SQL Mgmt Studio takes multiple steps:
1) run Generate scripts on the source database, and select all tables to create the table scripts (this includes the identity field on each table)
2) run the script on the destination database to created the tables
3) run the import task, edit mappings and select "enable identity insert".
this is really a pain since i could accomplish this all in 1 step in SQL 2000 EM.
Any suggestions? Please help!
Microsoft? You there? Anybody?|||You might get more traction on this in the SSIS forum, but I'll give it a try. By the way, this is an all-volunteer forum, so if you need immediate support, see http://microsoft.com/support. It's not only us Microsoft working folks in here, we just maintain the forum and try to help as much as we can.
This series of posts might help:
http://www.developerdotstar.com/community/node/727#comment-8456
Basically it involves the "FAST LOAD" option.
Buck Woody
SQL Server Team
Wednesday, March 7, 2012
Import CSV
I run the below command and it turns the first column in the csv file to
money as the data type. It thinks that the first column is data type of
money.
Any help would be appreciated.
SELECT *
FROM
OPENROWSET('MSDASQL',
'Driver={Microsoft Text Driver (*.txt; *.csv)};
DEFAULTDIR=C:\temp\;Extensions=CSV;',
'SELECT * FROM nessus.csv')It is not clear from your post what the exact question is. Are you loading
the data into a table and is it defaulting to money type? Have you looked
into the actual data in the CSV to see if it has any currency symbols like $
or # associated with the values?
--
Anith
money as the data type. It thinks that the first column is data type of
money.
Any help would be appreciated.
SELECT *
FROM
OPENROWSET('MSDASQL',
'Driver={Microsoft Text Driver (*.txt; *.csv)};
DEFAULTDIR=C:\temp\;Extensions=CSV;',
'SELECT * FROM nessus.csv')It is not clear from your post what the exact question is. Are you loading
the data into a table and is it defaulting to money type? Have you looked
into the actual data in the CSV to see if it has any currency symbols like $
or # associated with the values?
--
Anith
Import CSV
I run the below command and it turns the first column in the csv file to
money as the data type. It thinks that the first column is data type of
money.
Any help would be appreciated.
SELECT *
FROM
OPENROWSET('MSDASQL',
'Driver={Microsoft Text Driver (*.txt; *.csv)};
DEFAULTDIR=C:\temp\;Extensions=CSV;',
'SELECT * FROM nessus.csv')It is not clear from your post what the exact question is. Are you loading
the data into a table and is it defaulting to money type? Have you looked
into the actual data in the CSV to see if it has any currency symbols like $
or # associated with the values?
Anith
money as the data type. It thinks that the first column is data type of
money.
Any help would be appreciated.
SELECT *
FROM
OPENROWSET('MSDASQL',
'Driver={Microsoft Text Driver (*.txt; *.csv)};
DEFAULTDIR=C:\temp\;Extensions=CSV;',
'SELECT * FROM nessus.csv')It is not clear from your post what the exact question is. Are you loading
the data into a table and is it defaulting to money type? Have you looked
into the actual data in the CSV to see if it has any currency symbols like $
or # associated with the values?
Anith
Import CSV
I run the below command and it turns the first column in the csv file to
money as the data type. It thinks that the first column is data type of
money.
Any help would be appreciated.
SELECT *
FROM
OPENROWSET('MSDASQL',
'Driver={Microsoft Text Driver (*.txt; *.csv)};
DEFAULTDIR=C:\temp\;Extensions=CSV;',
'SELECT * FROM nessus.csv')
It is not clear from your post what the exact question is. Are you loading
the data into a table and is it defaulting to money type? Have you looked
into the actual data in the CSV to see if it has any currency symbols like $
or # associated with the values?
Anith
money as the data type. It thinks that the first column is data type of
money.
Any help would be appreciated.
SELECT *
FROM
OPENROWSET('MSDASQL',
'Driver={Microsoft Text Driver (*.txt; *.csv)};
DEFAULTDIR=C:\temp\;Extensions=CSV;',
'SELECT * FROM nessus.csv')
It is not clear from your post what the exact question is. Are you loading
the data into a table and is it defaulting to money type? Have you looked
into the actual data in the CSV to see if it has any currency symbols like $
or # associated with the values?
Anith
Import and Schedule DTS on MSDE
I have .dts file from another sql server. I would like to import and install
this file as a DTS package on MSDE and schedule it to run periodically.
How do I do this in MSDE 2000.
You can use DTSrun.exe
Look at
http://groups.google.it/groups?q=DTS...hx.gbl&rnum=18
"inquisite" <inquisite@.discussions.microsoft.com> wrote in message
news:392C38CD-4DB3-4D39-AF2A-0A14CED0D74F@.microsoft.com...
> I have .dts file from another sql server. I would like to import and
install
> this file as a DTS package on MSDE and schedule it to run periodically.
> How do I do this in MSDE 2000.
|||Will it be possible for anyone of you to help me with a example, for the syntax to use in command prompt with DTSrun.exe.
The situation is this. I need to uninstall SQL Server and install MSDE for my client. I have DTS running already in SQL Server. I need to take a back up, and make it run in MSDE. The package name is dtsPack.dts. I have it in the location "c:\"
Thanks for your help|||Here's the section from Books Online, including examples at the end:
The dtsrun utility executes a package created using Data Transformation Services (DTS). The DTS
package can be stored in the Microsoft SQL ServerT msdb database, a COM-structured storage file, or
SQL Server Meta Data Services.
Syntax
dtsrun
[/?] |
[
[
/[~]S server_name[\instance_name]
{ {/[~]U user_name [/[~]P password]} | /E }
]
{
{/[~]N package_name }
| {/[~]G package_guid_string}
| {/[~]V package_version_guid_string}
}
[/[~]M package_password]
[/[~]F filename]
[/[~]R repository_database_name]
[/A global_variable_name:typeid=value]
[/L log_file_name]
[/W NT_event_log_completion_status]
[/Z] [/!X] [/!D] [/!Y] [/!C]
]
Arguments/?
Displays the command prompt options.
~
Specifies that the parameter to follow is hexadecimal text representing the encrypted value of the
parameter. Can be used with the /S, /U, /P, /N, /G, /V, /M, /F, and /R options. Using encrypted
values increases the security of the command used to execute the DTS package because the server
name, password, and so on, are not visible. Use /!Y to determine the encrypted command.
/S server_name[\instance_name]
Specifies the instance of SQL Server to connect to. Specify server_name to connect to the default
instance of SQL Server on that server. Specify server_name\instance_name to connect to a named
instance of SQL Server 2000 on that server.
/U user_name
Is a login ID used to connect to an instance of SQL Server.
/P password
Is a user-specified password used with a login ID.
/E
Specifies a trusted connection (password not required).
/N package_name
Is the name of a DTS package assigned when the package was created.
/G package_guid_string
Is the package ID assigned to the DTS package when it was created. The package ID is a GUID.
/V package_version_guid_string
Is the version ID assigned to the DTS package when it was first saved or executed. A new version ID
is assigned to the DTS package each time it is modified. The version ID is a GUID.
/M package_password
Is an optional password assigned to the DTS package when it was created.
/F filename
Is the name of a structured storage file containing DTS packages. If server_name is also specified,
the DTS package retrieved from SQL Server is executed and that package is added to the structured
storage engine.
/R repository_database_name
Is the name of the repository database containing DTS packages. If no name is specified, the default
database name is used.
/A global_variable_name:typeid=value
Specifies a package global variable, where typeid = type identifier for the data type of the global
variable. The entire argument string can be quoted. This argument can be repeated to specify
multiple global variables. See the Remarks section for the different available type identifiers
available with global variables.
To set global variables with this command switch, you must have either Owner permission for the
package or the package must have been saved without DTS password protection enabled. If you do not
have Owner permission, you can specify global variables, but the values used will be those set in
the package, not those specified with the /A command switch.
/L log_file_name:
Specifies the name of the package log file.
/W Windows_Event_Log
Specifies whether or not to write the completion status of the package execution to the Windows
Application Log. Specify True or False.
/Z
Indicates that the command line for dtsrun is encrypted using SQL Server 2000 encryption.
/!X
Blocks execution of the selected DTS package. Use this command parameter when you want to create an
encrypted command line without executing the DTS package.
/!D
Deletes the DTS package from an instance of SQL Server. The package is not executed. It is not
possible to delete a specific DTS package from a structured storage file. The entire file needs to
be overwritten using the /F and /S options.
/!Y
Displays the encrypted command used to execute the DTS package without executing it.
/!C
Copies the command used to execute the DTS package to the Microsoft Windows clipboard. This option
can also be used in conjunction with /!X and /!Y.
Remarks
Security Note Batch files may contain credentials stored in plain text. Credentials may be echoed
to the user's screen during batch execution.
If you do not specify any command line switches, specify an incorrect command line switch, or your
command statement contains a syntax error, dtsrun returns error information and usage instructions.
If you enter dtsrunui on the command line without any command line switches, you will start the DTS
Run utility.
Spaces between command switches and values are optional. Embedded spaces in values must be embedded
between double quotation marks.
If an option is specified multiple times, the last occurrence takes precedence. One exception is the
/A command switch. Specifying more than one /A switch creates multiple global variables.
When specifying a global variable with the /A command switch, you must use a type identifier to
indicate the data type of the global variable.
A tilde (~) character after the forward slash (for example, /~Z) indicates that the parameter value
is encrypted and what follows is the hexadecimal text of the encrypted value.
The table shows the global variable data types and their IDs.
Data type Type ID
Integer (small) 2
Integer 3
Real (4-byte) 4
Real (8-byte) 5
Currency 6
Date 7
String 8
Boolean 11
Decimal 14
Integer (1-byte) 16
Unsigned int (1-byte) 17
Unsigned int (2-byte) 18
Unsigned int (4-byte) 19
Integer (8-byte) 20
Unsigned int (8-byte) 21
Int 22
Unsigned int 23
HRESULT 25
Pointer 26
LPSTR 30
LPWSTR 31
For information about where to find or how to run this utility, see Getting Started with Command
Prompt Utilities.
Examples
To execute a DTS package saved as a COM-structured storage file, use:
dtsrun /Ffilename /Npackage_name /Mpackage_password
To execute a DTS package saved in the SQL Server msdb database, use:
dtsrun /Sserver_name /Uuser_nName /Ppassword /Npackage_name /Mpackage_password
To execute a DTS package saved in Meta Data Services, use:
dtsrun /Sserver_name /Uuser_nrame /Ppassword /Npackage_name /Rrepository_name
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ngaya" <ngaya.1pivm3@.mail.mcse.ms> wrote in message news:ngaya.1pivm3@.mail.mcse.ms...
> Will it be possible for anyone of you to help me with a example, for the
> syntax to use in command prompt with DTSrun.exe.
> The situation is this. I need to uninstall SQL Server and install MSDE
> for my client. I have DTS running already in SQL Server. I need to take
> a back up, and make it run in MSDE. The package name is dtsPack.dts. I
> have it in the location "c:\"
> Thanks for your help
>
> --
> ngaya
> Posted via http://www.mcse.ms
> View this thread: http://www.mcse.ms/message1244207.html
>
|||THanks a lot for your time. I still have one more question to be clarified. Should i save the DTS in the database msdb? or will it automatically get saved? Ok if i save it, should i attach the database fresh to the MSDE?
Thanks in advance
Ngaya|||Thanks...I got it at last...I took a backup of msdb database and restored in MSDE, and tried
exec msdb..sp_enum_dtspackages
go
to see the list and it was there.
I was able to run the pacakge successfully using dtsrun.
Phew!...this took a lot of my timelah!... If i had known the simple thing that dts packages are stored in msdb database...this would have been over long back...thanks for that info..
ngaya|||You can also open a package on the originating server and save as file, copy file, open file on dest
server and save to msdb.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ngaya" <ngaya.1pm9u1@.mail.mcse.ms> wrote in message news:ngaya.1pm9u1@.mail.mcse.ms...
> Thanks...I got it at last...I took a backup of msdb database and
> restored in MSDE, and tried
> exec msdb..sp_enum_dtspackages
> go
> to see the list and it was there.
> I was able to run the pacakge successfully using dtsrun.
> Phew!...this took a lot of my timelah!... If i had known the simple
> thing that dts packages are stored in msdb database...this would have
> been over long back...thanks for that info..
> ngaya
>
> --
> ngaya
> Posted via http://www.mcse.ms
> View this thread: http://www.mcse.ms/message1244207.html
>
this file as a DTS package on MSDE and schedule it to run periodically.
How do I do this in MSDE 2000.
You can use DTSrun.exe
Look at
http://groups.google.it/groups?q=DTS...hx.gbl&rnum=18
"inquisite" <inquisite@.discussions.microsoft.com> wrote in message
news:392C38CD-4DB3-4D39-AF2A-0A14CED0D74F@.microsoft.com...
> I have .dts file from another sql server. I would like to import and
install
> this file as a DTS package on MSDE and schedule it to run periodically.
> How do I do this in MSDE 2000.
|||Will it be possible for anyone of you to help me with a example, for the syntax to use in command prompt with DTSrun.exe.
The situation is this. I need to uninstall SQL Server and install MSDE for my client. I have DTS running already in SQL Server. I need to take a back up, and make it run in MSDE. The package name is dtsPack.dts. I have it in the location "c:\"
Thanks for your help|||Here's the section from Books Online, including examples at the end:
The dtsrun utility executes a package created using Data Transformation Services (DTS). The DTS
package can be stored in the Microsoft SQL ServerT msdb database, a COM-structured storage file, or
SQL Server Meta Data Services.
Syntax
dtsrun
[/?] |
[
[
/[~]S server_name[\instance_name]
{ {/[~]U user_name [/[~]P password]} | /E }
]
{
{/[~]N package_name }
| {/[~]G package_guid_string}
| {/[~]V package_version_guid_string}
}
[/[~]M package_password]
[/[~]F filename]
[/[~]R repository_database_name]
[/A global_variable_name:typeid=value]
[/L log_file_name]
[/W NT_event_log_completion_status]
[/Z] [/!X] [/!D] [/!Y] [/!C]
]
Arguments/?
Displays the command prompt options.
~
Specifies that the parameter to follow is hexadecimal text representing the encrypted value of the
parameter. Can be used with the /S, /U, /P, /N, /G, /V, /M, /F, and /R options. Using encrypted
values increases the security of the command used to execute the DTS package because the server
name, password, and so on, are not visible. Use /!Y to determine the encrypted command.
/S server_name[\instance_name]
Specifies the instance of SQL Server to connect to. Specify server_name to connect to the default
instance of SQL Server on that server. Specify server_name\instance_name to connect to a named
instance of SQL Server 2000 on that server.
/U user_name
Is a login ID used to connect to an instance of SQL Server.
/P password
Is a user-specified password used with a login ID.
/E
Specifies a trusted connection (password not required).
/N package_name
Is the name of a DTS package assigned when the package was created.
/G package_guid_string
Is the package ID assigned to the DTS package when it was created. The package ID is a GUID.
/V package_version_guid_string
Is the version ID assigned to the DTS package when it was first saved or executed. A new version ID
is assigned to the DTS package each time it is modified. The version ID is a GUID.
/M package_password
Is an optional password assigned to the DTS package when it was created.
/F filename
Is the name of a structured storage file containing DTS packages. If server_name is also specified,
the DTS package retrieved from SQL Server is executed and that package is added to the structured
storage engine.
/R repository_database_name
Is the name of the repository database containing DTS packages. If no name is specified, the default
database name is used.
/A global_variable_name:typeid=value
Specifies a package global variable, where typeid = type identifier for the data type of the global
variable. The entire argument string can be quoted. This argument can be repeated to specify
multiple global variables. See the Remarks section for the different available type identifiers
available with global variables.
To set global variables with this command switch, you must have either Owner permission for the
package or the package must have been saved without DTS password protection enabled. If you do not
have Owner permission, you can specify global variables, but the values used will be those set in
the package, not those specified with the /A command switch.
/L log_file_name:
Specifies the name of the package log file.
/W Windows_Event_Log
Specifies whether or not to write the completion status of the package execution to the Windows
Application Log. Specify True or False.
/Z
Indicates that the command line for dtsrun is encrypted using SQL Server 2000 encryption.
/!X
Blocks execution of the selected DTS package. Use this command parameter when you want to create an
encrypted command line without executing the DTS package.
/!D
Deletes the DTS package from an instance of SQL Server. The package is not executed. It is not
possible to delete a specific DTS package from a structured storage file. The entire file needs to
be overwritten using the /F and /S options.
/!Y
Displays the encrypted command used to execute the DTS package without executing it.
/!C
Copies the command used to execute the DTS package to the Microsoft Windows clipboard. This option
can also be used in conjunction with /!X and /!Y.
Remarks
Security Note Batch files may contain credentials stored in plain text. Credentials may be echoed
to the user's screen during batch execution.
If you do not specify any command line switches, specify an incorrect command line switch, or your
command statement contains a syntax error, dtsrun returns error information and usage instructions.
If you enter dtsrunui on the command line without any command line switches, you will start the DTS
Run utility.
Spaces between command switches and values are optional. Embedded spaces in values must be embedded
between double quotation marks.
If an option is specified multiple times, the last occurrence takes precedence. One exception is the
/A command switch. Specifying more than one /A switch creates multiple global variables.
When specifying a global variable with the /A command switch, you must use a type identifier to
indicate the data type of the global variable.
A tilde (~) character after the forward slash (for example, /~Z) indicates that the parameter value
is encrypted and what follows is the hexadecimal text of the encrypted value.
The table shows the global variable data types and their IDs.
Data type Type ID
Integer (small) 2
Integer 3
Real (4-byte) 4
Real (8-byte) 5
Currency 6
Date 7
String 8
Boolean 11
Decimal 14
Integer (1-byte) 16
Unsigned int (1-byte) 17
Unsigned int (2-byte) 18
Unsigned int (4-byte) 19
Integer (8-byte) 20
Unsigned int (8-byte) 21
Int 22
Unsigned int 23
HRESULT 25
Pointer 26
LPSTR 30
LPWSTR 31
For information about where to find or how to run this utility, see Getting Started with Command
Prompt Utilities.
Examples
To execute a DTS package saved as a COM-structured storage file, use:
dtsrun /Ffilename /Npackage_name /Mpackage_password
To execute a DTS package saved in the SQL Server msdb database, use:
dtsrun /Sserver_name /Uuser_nName /Ppassword /Npackage_name /Mpackage_password
To execute a DTS package saved in Meta Data Services, use:
dtsrun /Sserver_name /Uuser_nrame /Ppassword /Npackage_name /Rrepository_name
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ngaya" <ngaya.1pivm3@.mail.mcse.ms> wrote in message news:ngaya.1pivm3@.mail.mcse.ms...
> Will it be possible for anyone of you to help me with a example, for the
> syntax to use in command prompt with DTSrun.exe.
> The situation is this. I need to uninstall SQL Server and install MSDE
> for my client. I have DTS running already in SQL Server. I need to take
> a back up, and make it run in MSDE. The package name is dtsPack.dts. I
> have it in the location "c:\"
> Thanks for your help
>
> --
> ngaya
> Posted via http://www.mcse.ms
> View this thread: http://www.mcse.ms/message1244207.html
>
|||THanks a lot for your time. I still have one more question to be clarified. Should i save the DTS in the database msdb? or will it automatically get saved? Ok if i save it, should i attach the database fresh to the MSDE?
Thanks in advance
Ngaya|||Thanks...I got it at last...I took a backup of msdb database and restored in MSDE, and tried
exec msdb..sp_enum_dtspackages
go
to see the list and it was there.
I was able to run the pacakge successfully using dtsrun.
Phew!...this took a lot of my timelah!... If i had known the simple thing that dts packages are stored in msdb database...this would have been over long back...thanks for that info..
ngaya|||You can also open a package on the originating server and save as file, copy file, open file on dest
server and save to msdb.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ngaya" <ngaya.1pm9u1@.mail.mcse.ms> wrote in message news:ngaya.1pm9u1@.mail.mcse.ms...
> Thanks...I got it at last...I took a backup of msdb database and
> restored in MSDE, and tried
> exec msdb..sp_enum_dtspackages
> go
> to see the list and it was there.
> I was able to run the pacakge successfully using dtsrun.
> Phew!...this took a lot of my timelah!... If i had known the simple
> thing that dts packages are stored in msdb database...this would have
> been over long back...thanks for that info..
> ngaya
>
> --
> ngaya
> Posted via http://www.mcse.ms
> View this thread: http://www.mcse.ms/message1244207.html
>
Import and Export SQL 2005 Maintenance Plan
I had created a maintenance plan and configure and scheduled my maintenace plan to run. I would like to save this package as a file into a source control. SO I use SSIS to export the package under stored packages->MSDB->Maintenace Plan. After that, I wanted to test my import process. So I deleted the packages under SSIS ->stored packages->MSDB->Maintenance plan and I use the import to add the package from my previously exported package stored in a file .dtxs extention.
So the problem is, after I imported my package. I lost the configured schedule and the job runs without doing anything. When I try to go in and make changes to the package, by adding a new schedule under SQL server -> management->maintenance plan. I receive a odd error message and it doesn't allow me to save the package.. The error message I got is...."GUID should contain 32 digits with 4 dashes (xxxxx-xxx-xx...)"
So my questions are: 1. why did the re-import loses the originally configure job run schedule. 2. why doesn't the re-import package works by backing up database as it was first setup. 3. why I cannot re-edit this package and saving the package error out?
Thank you for reading and for your helps! --Jon
I hope this should help. I have the same problem and working out on it
http://support.microsoft.com/default.aspx/kb/922651
http://sqlug.be/blogs/drivenbysql/archive/2006/10/21/374.aspx
|||I had similar issues. SSIS wasn't installed first, so I installed it then starting to get that GUID error. It was a simple fix actually. In studio, got to SQL Server Agent, Jobs and it had jobs for all my deleted plans. I cleared out the jobs, then created a brand new plan and it worked fine. I guess the errors created when I tried to create a plan prior to the SSIS installation caused it not to clear the jobs.Though, this may or may not be what happened to you.
Import and Export SQL 2005 Maintenance Plan
I had created a maintenance plan and configure and scheduled my maintenace plan to run. I would like to save this package as a file into a source control. SO I use SSIS to export the package under stored packages->MSDB->Maintenace Plan. After that, I wanted to test my import process. So I deleted the packages under SSIS ->stored packages->MSDB->Maintenance plan and I use the import to add the package from my previously exported package stored in a file .dtxs extention.
So the problem is, after I imported my package. I lost the configured schedule and the job runs without doing anything. When I try to go in and make changes to the package, by adding a new schedule under SQL server -> management->maintenance plan. I receive a odd error message and it doesn't allow me to save the package.. The error message I got is...."GUID should contain 32 digits with 4 dashes (xxxxx-xxx-xx...)"
So my questions are: 1. why did the re-import loses the originally configure job run schedule. 2. why doesn't the re-import package works by backing up database as it was first setup. 3. why I cannot re-edit this package and saving the package error out?
Thank you for reading and for your helps! --Jon
I hope this should help. I have the same problem and working out on it
http://support.microsoft.com/default.aspx/kb/922651
http://sqlug.be/blogs/drivenbysql/archive/2006/10/21/374.aspx
|||I had similar issues. SSIS wasn't installed first, so I installed it then starting to get that GUID error. It was a simple fix actually. In studio, got to SQL Server Agent, Jobs and it had jobs for all my deleted plans. I cleared out the jobs, then created a brand new plan and it worked fine. I guess the errors created when I tried to create a plan prior to the SSIS installation caused it not to clear the jobs.Though, this may or may not be what happened to you.
Subscribe to:
Posts (Atom)