convert Access DB to MS SQL with dtswiz(command line)

Hi!

I' m sitting here now for days and I still get no conclusion on my
problem. I have an MS ACCESS .mdb File and I would like to export it
with dtswiz.exe (from MS SQL Utility Tools) via command line. (If you
know other tools which are able to do that via command line I would
appreciate any suggestion)

I tried to find another Provider Parameter (like MSDASQL) for Access
but somehow it seems to me that Microsoft only got one. I' m also able
to address a Server which appears in the GUI but I don t need one for
my ACCess DB (I only tried, if the command parameters have any effect
on the converter).

I would be glad if someone could help me on this.

Thank you
Klaus Baumgarten"Klaus Baumgarten" <klaus.baumgarten@.gmx.net> wrote in message
news:c562e80d.0405110819.6eb845bc@.posting.google.c om...
> Hi!
> I' m sitting here now for days and I still get no conclusion on my
> problem. I have an MS ACCESS .mdb File and I would like to export it
> with dtswiz.exe (from MS SQL Utility Tools) via command line. (If you
> know other tools which are able to do that via command line I would
> appreciate any suggestion)
> I tried to find another Provider Parameter (like MSDASQL) for Access
> but somehow it seems to me that Microsoft only got one. I' m also able
> to address a Server which appears in the GUI but I don t need one for
> my ACCess DB (I only tried, if the command parameters have any effect
> on the converter).
> I would be glad if someone could help me on this.
> Thank you
> Klaus Baumgarten

I'm not totally sure what your issue is - are you asking how to execute a
DTS package from the command line? If so, you can save your package to a
..dts file and execute it with dtsrun.exe; you can use dtsrunui.exe to build
a working command line first.

If your problem is moving data from an Access database to an MSSQL one, then
this may help:

http://support.microsoft.com/default.aspx?kbid=285829

Simon

convert

can someone give me the command to conver varchar to date please.
In my table I have varchar(50) Tue Nov 21 00:00:06 EST 2006 for the column
but need to update the colum with 2006-11-21 00:00:06.
Thanks,
Stoneystoney wrote:
> can someone give me the command to conver varchar to date please.
> In my table I have varchar(50) Tue Nov 21 00:00:06 EST 2006 for the column
> but need to update the colum with 2006-11-21 00:00:06.
> Thanks,
> Stoney
Try to look up the CONVERT command in BOL. Here you can see the various
styles you can use. I think in your case you can use 20/120.
Regards
Steen Schlüter Persson
Database Administrator / System Administratorsqlsql

Conversion of DTS to SSIS command Line

I am trying to convert a command line using the dtexecui utility. I need to pass three parameters ; account number ,begin and end date to project.

What am i doing wrong ?

DTEXEC /DTS "\File System\Archive Data" /SERVER SRV2 /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EW \package /SET "Account_Number";"'00001'" /SET "File_Name";"'C:\Inetpub\wwwroot\output\Archive\'" /SET "Begin_Date";"'04/03/2006'" /SET "End_Date";"'04/04/2006'"

Error I get

Microsoft (R) SQL Server Execute Package Utility
Version 9.00.1399.06 for 32-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.

Started: 9:52:49 AM
Warning: 2006-04-05 09:52:51.58
Code: 0x80012018
Source: Archive Data
Description: The configuration entry, "Account_Number", has an incorrect form
at because it does not begin with the package delimiter. Prepend "\package" to t
he package path.
End Warning
Warning: 2006-04-05 09:52:51.58
Code: 0x80012017
Source: Archive Data
Description: The package path referenced an object that cannot be found: "Acc
ount_Number". This occurs when an attempt is made to resolve a package path to a
n object that cannot be found.
End Warning
DTExec: Could not set Account_Number value to '00001'.
Started: 9:52:49 AM
Finished: 9:52:51 AM
Elapsed: 2.172 seconds

Your command line is not correct as each set command needs a package path starting with \package just as the error message indicates. As I don't know tasks these properties belong to I can't give you the exact path but in general the set option should look something like "\Package.rest_of_path_to_property". You can use the configurations on the package to identify what the package path should look like. You should also remove the \package from the command line outside of the set because that is invalid.

HTH,

Matt

control transaction duration

Hi guys,
can I control the duration of a transaction ?
Using ADO I can set a command timeout, but using T-SQL or
modifying some SQLserver parameter, can I set a sort
of timeout on a transaction and get the same result (i.e. prevent
a transaction from running too long) ?
Many thanks for your kind help
Max
You can control max time you wait when you wait to be granted a lock. Check out SET LOCK_TIMEOUT.
However, you cannot set the max time you hold a transaction open or how long a query can run at the TSQL level
(ignoring the query governor), this has to be done in the client app (using ADO, ADO.NET of whatever API you
are using).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"madmax" <madmax@.discussions.microsoft.com> wrote in message
news:52836935-399C-4C37-9240-E23ABCC9F7B4@.microsoft.com...
> Hi guys,
> can I control the duration of a transaction ?
> Using ADO I can set a command timeout, but using T-SQL or
> modifying some SQLserver parameter, can I set a sort
> of timeout on a transaction and get the same result (i.e. prevent
> a transaction from running too long) ?
> Many thanks for your kind help
> Max

control transaction duration

Hi guys,
can I control the duration of a transaction ?
Using ADO I can set a command timeout, but using T-SQL or
modifying some SQLserver parameter, can I set a sort
of timeout on a transaction and get the same result (i.e. prevent
a transaction from running too long) ?
Many thanks for your kind help
MaxYou can control max time you wait when you wait to be granted a lock. Check out SET LOCK_TIMEOUT.
However, you cannot set the max time you hold a transaction open or how long a query can run at the TSQL level
(ignoring the query governor), this has to be done in the client app (using ADO, ADO.NET of whatever API you
are using).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"madmax" <madmax@.discussions.microsoft.com> wrote in message
news:52836935-399C-4C37-9240-E23ABCC9F7B4@.microsoft.com...
> Hi guys,
> can I control the duration of a transaction ?
> Using ADO I can set a command timeout, but using T-SQL or
> modifying some SQLserver parameter, can I set a sort
> of timeout on a transaction and get the same result (i.e. prevent
> a transaction from running too long) ?
> Many thanks for your kind help
> Max

control transaction duration

Hi guys,
can I control the duration of a transaction ?
Using ADO I can set a command timeout, but using T-SQL or
modifying some SQLserver parameter, can I set a sort
of timeout on a transaction and get the same result (i.e. prevent
a transaction from running too long) ?
Many thanks for your kind help
MaxYou can control max time you wait when you wait to be granted a lock. Check
out SET LOCK_TIMEOUT.
However, you cannot set the max time you hold a transaction open or how long
a query can run at the TSQL level
(ignoring the query governor), this has to be done in the client app (using
ADO, ADO.NET of whatever API you
are using).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"madmax" <madmax@.discussions.microsoft.com> wrote in message
news:52836935-399C-4C37-9240-E23ABCC9F7B4@.microsoft.com...
> Hi guys,
> can I control the duration of a transaction ?
> Using ADO I can set a command timeout, but using T-SQL or
> modifying some SQLserver parameter, can I set a sort
> of timeout on a transaction and get the same result (i.e. prevent
> a transaction from running too long) ?
> Many thanks for your kind help
> Max

contains keyword for integer field type

I have a field ID of type integer, I want to put two numbers in that field: 3 and 7, so what I do is just store the number 37.

Now, is there a command in SQL server which checks if the ID field contains a number I look for, say 7.
Something like a CONTAINS keyword...

If there is, could someone please tell me what it is AND tell me a bit more about it :memory usage, (dis)advantages etc.

try something like this:

select

ID/10 firstDigit,
ID- ID/10*10 secondDigit
from(select 37 [ID])aa

It will work as long as Your ID is Int type if it is not int do this

select ID/10 firstDigit,
ID- ID/10*10 secondDigit
from(select cast(37.00 as int) [ID])aa

You can also use substring
selectsubstring(ID,1,1) first,substring(ID,2,1) second
from(selectcast(37asvarchar(20)) [ID])aa

Good luck