No field delimiters using bcp command

Hi,

I am using a bcp command to load data into a text file . The command is below:

C:\>bcp "select ltrim(rtrim(char25))+replicate ('X',25-len(char25)),CONVERT(varc
har(8),dateg,112) as [yyyymmdd],flag1,replace( replicate ('0',19-len(amount)) +
ltrim(rtrim(amount)),'.',','),replace(replicate ('0',9-len(dperc)) + ltrim(rtrim
(dperc)),'.',',') from Bank_Info.dbo.ddd" queryout c:\xxxx\replicate_replace.tx
t -c -U sax -S KARAFOKAS -C 1252 -P passsax

The command runs fine , the problem is , the output in the text file is with tab delimited form. I want the format NOT to have tab delimited form but the values actually to have a continuation. That is, nothing to split one value from the other.

This is the output with tab delimited format.

vvvXXXXXXXXXXXXXXXXXXXXXX 20071112 h 0000000000005555,70 066,50000
abcXXXXXXXXXXXXXXXXXXXXXX 19000101 y 0454545454523456,45 077,30000
xyzcccXXXXXXXXXXXXXXXXXXX 19000101 x 0000000000003456,00 077,99865
fXXXXXXXXXXXXXXXXXXXXXXXX 20030302 6 0000000000232323,45 005,00000

I want the output to have to tabs , as shown below:

vvvXXXXXXXXXXXXXXXXXXXXXX20071112h0000000000005555 ,70066,50000
abcXXXXXXXXXXXXXXXXXXXXXX19000101y0454545454523456 ,45077,30000
xyzcccXXXXXXXXXXXXXXXXXXX19000101x0000000000003456 ,00077,99865
fXXXXXXXXXXXXXXXXXXXXXXXX2003030260000000000232323 ,45005,00000

Columns values should not be seperated by tabs. Any thoughts?

Thank you
George

Quote:

Originally Posted by karafokas

Hi,

I am using a bcp command to load data into a text file . The command is below:

C:\>bcp "select ltrim(rtrim(char25))+replicate ('X',25-len(char25)),CONVERT(varc
har(8),dateg,112) as [yyyymmdd],flag1,replace( replicate ('0',19-len(amount)) +
ltrim(rtrim(amount)),'.',','),replace(replicate ('0',9-len(dperc)) + ltrim(rtrim
(dperc)),'.',',') from Bank_Info.dbo.ddd" queryout c:\xxxx\replicate_replace.tx
t -c -U sax -S KARAFOKAS -C 1252 -P passsax

The command runs fine , the problem is , the output in the text file is with tab delimited form. I want the format NOT to have tab delimited form but the values actually to have a continuation. That is, nothing to split one value from the other.

This is the output with tab delimited format.

vvvXXXXXXXXXXXXXXXXXXXXXX 20071112 h 0000000000005555,70 066,50000
abcXXXXXXXXXXXXXXXXXXXXXX 19000101 y 0454545454523456,45 077,30000
xyzcccXXXXXXXXXXXXXXXXXXX 19000101 x 0000000000003456,00 077,99865
fXXXXXXXXXXXXXXXXXXXXXXXX 20030302 6 0000000000232323,45 005,00000

I want the output to have to tabs , as shown below:

vvvXXXXXXXXXXXXXXXXXXXXXX20071112h0000000000005555 ,70066,50000
abcXXXXXXXXXXXXXXXXXXXXXX19000101y0454545454523456 ,45077,30000
xyzcccXXXXXXXXXXXXXXXXXXX19000101x0000000000003456 ,00077,99865
fXXXXXXXXXXXXXXXXXXXXXXXX2003030260000000000232323 ,45005,00000

Columns values should not be seperated by tabs. Any thoughts?

Thank you
George

looks like you want a fixed-length output. have you tried passing -t "" ? or something like that? -t is the bcp parameter for field terminator.sql

No distinct in a select into stement ?

Dear MSSQL experts,

I use MSSQL 2000 and encountered a strange problem wqhile I tried to
use a select into statement .

If I perform the command command below I get only one dataset which has
the described properties.
If I use the same statement in a select into statement (see the second
select) I get several datasets with the described properties like I
didn't use distinct
Is there any posiibility to use destinct in a select into statement

select distinct IDENTITY (int) as ID, Title1 as Title1, Title2 as
Title2, Title3 as Title3,
AggregationTitle1 as AggregationTitle1, AggregationTitle2 as
AggregationTitle2,
AggregationTitle3 as AggregationTitle3, AggregationTitle4 as
AggregationTitle4
from Variables where Title1 is not NULL or Title2 is not NULL or
Title3 is not NULL or
AggregationTitle1 is not NULL or AggregationTitle2 is not NULL or
AggregationTitle3 is not NULL or AggregationTitle4 is not NULL;

This is the same with select into :

select distinct IDENTITY (int) as ID, Title1 as Title1, Title2 as
Title2, Title3 as Title3,
AggregationTitle1 as AggregationTitle1, AggregationTitle2 as
AggregationTitle2,
AggregationTitle3 as AggregationTitle3, AggregationTitle4 as
AggregationTitle4
into VarTitles from Variables where Title1 is not NULL or Title2 is
not NULL or Title3 is not NULL or
AggregationTitle1 is not NULL or AggregationTitle2 is not NULL or
AggregationTitle3 is not NULL or
AggregationTitle4 is not NULL;

Hope anyone can help.

Best regards,

Daniel Wetzler

IDaniel Wetzler wrote:
> Dear MSSQL experts,
> I use MSSQL 2000 and encountered a strange problem wqhile I tried to
> use a select into statement .
> If I perform the command command below I get only one dataset which has
> the described properties.
> If I use the same statement in a select into statement (see the second
> select) I get several datasets with the described properties like I
> didn't use distinct
> Is there any posiibility to use destinct in a select into statement
> select distinct IDENTITY (int) as ID, Title1 as Title1, Title2 as
> Title2, Title3 as Title3,
> AggregationTitle1 as AggregationTitle1, AggregationTitle2 as
> AggregationTitle2,
> AggregationTitle3 as AggregationTitle3, AggregationTitle4 as
> AggregationTitle4
> from Variables where Title1 is not NULL or Title2 is not NULL or
> Title3 is not NULL or
> AggregationTitle1 is not NULL or AggregationTitle2 is not NULL or
> AggregationTitle3 is not NULL or AggregationTitle4 is not NULL;
>
> This is the same with select into :
> select distinct IDENTITY (int) as ID, Title1 as Title1, Title2 as
> Title2, Title3 as Title3,
> AggregationTitle1 as AggregationTitle1, AggregationTitle2 as
> AggregationTitle2,
> AggregationTitle3 as AggregationTitle3, AggregationTitle4 as
> AggregationTitle4
> into VarTitles from Variables where Title1 is not NULL or Title2 is
> not NULL or Title3 is not NULL or
> AggregationTitle1 is not NULL or AggregationTitle2 is not NULL or
> AggregationTitle3 is not NULL or
> AggregationTitle4 is not NULL;
> Hope anyone can help.
> Best regards,
> Daniel Wetzler

The IDENTITY function makes each row unique so DISTINCT doesn't
eliminate the duplicates in this case. Interestingly, this behaviour
seems to have changed in SQL Server 2005. If I run your SELECT INTO on
2005 I get a different execution plan with the IDENTITY value computed
after DISTINCT.

For 2000 the workaround is easy. The following should insert just one
row into vartitles.

CREATE TABLE variables (title1 VARCHAR(10) NULL, title2 VARCHAR(10)
NULL, title3 VARCHAR(10) NULL, aggregationtitle1 VARCHAR(10) NULL,
aggregationtitle2 VARCHAR(10) NULL, aggregationtitle3 VARCHAR(10) NULL,
aggregationtitle4 VARCHAR(10) NULL);

INSERT INTO variables VALUES ('1','1','1','1','1','1','1');
INSERT INTO variables VALUES ('1','1','1','1','1','1','1');

SELECT IDENTITY (INT) AS id,
title1, title2, title3, aggregationtitle1, aggregationtitle2,
aggregationtitle3, aggregationtitle4
INTO VarTitles
FROM (
SELECT DISTINCT
title1, title2, title3, aggregationtitle1, aggregationtitle2,
aggregationtitle3, aggregationtitle4
FROM variables
WHERE title1 IS NOT NULL
OR title2 IS NOT NULL
OR title3 IS NOT NULL
OR aggregationtitle1 IS NOT NULL
OR aggregationtitle2 IS NOT NULL
OR aggregationtitle3 IS NOT NULL
OR aggregationtitle4 IS NOT NULL) AS V ;

SELECT * FROM vartitles ;

Hope this helps.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--|||Thank you very much.
This was a very helpful hint.

Best regards,

Daniel|||Minor trick to make the code easier to read and maintain:

WHERE COALESCE (title1, title2, title3) IS NOT NULL
OR COALESCE (ggregation_title1, aggregation_title2,
aggregation_title3, aggregation_title4) IS NOT NULL

Unfortunately these columns look like repeated and a really bad 1NF
problem. I have the feeling that you might have wanted to use
COALESCE() in the SELECT list to get a non-null title and non-null
aggregation_title instead of this convoluted query.|||> Minor trick to make the code easier to read and maintain:
> WHERE COALESCE (title1, title2, title3) IS NOT NULL
> OR COALESCE (ggregation_title1, aggregation_title2,
> aggregation_title3, aggregation_title4) IS NOT NULL

and... bang goes performance too other than a probable clustered index scan
/ table scan.

Consider these two statements on my 800,000 row 834MByte message table for
the nntp forums...

There is a non-clustered index on nntp_author and there is non-clustered
index on author_id

-- Query 1
select count(*)
from mb_message_detail
where nntp_author is not null
or author_id is not null

-- Query 2
select count(*)
from mb_message_detail
where coalesce( nntp_author, author_id ) is not null

Query 1 will use the index author_id and give a half reasonable plan.
Query 2 will do a clustered index scan

Out of 100%, Query 1 is 12% and Query 2 is a whopping 88%

Seriously, go and get a junior job as a programmer and get some very needed
industrial / real world experience instead of bashing people down on here,
as far as 'newbie' goes - you have room to talk.....

--
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials

"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1145969845.021110.74070@.t31g2000cwb.googlegro ups.com...
> Minor trick to make the code easier to read and maintain:
> WHERE COALESCE (title1, title2, title3) IS NOT NULL
> OR COALESCE (ggregation_title1, aggregation_title2,
> aggregation_title3, aggregation_title4) IS NOT NULL
> Unfortunately these columns look like repeated and a really bad 1NF
> problem. I have the feeling that you might have wanted to use
> COALESCE() in the SELECT list to get a non-null title and non-null
> aggregation_title instead of this convoluted query.|||-------
Seriously, go and get a junior job as a programmer and get some very
needed
industrial / real world experience instead of bashing people down on
here,
as far as 'newbie' goes - you have room to talk.....
-------

<BIG GRIN> from on who has been the receiving side of one of CELKO's
many put-downs.

No ADO type command for Data Flow?

I'm probably not looking in the right place, but all I could find when creating a data flow task was OLE DB Commands. I was trying to utilize a dataaccesslayer piece of code that we use every where in our projects, but because it uses ADO and not OLE DB, it caused an issue between the column data types.

Is there an ADO command object available? Or are we forced to use the OLE DB command object? All I was looking to do was to Execute a SQL command. There's an object on the Control Flow level to do that, but not on the Data Flow levelnot sure why that is.

Thanks,

Jeff Tolman
E&M Electric

Its because executing a stand-alone SQL statement isn't relevant for a data-flow. As you have observed you can do that in the Execute SQL Task.

What exactly is it that you want to do? What is your SQL statement?

-Jamie

|||

You would only use the data flow task if you wanted to process many rows of data - either aggregating them to pass them to your command, or issuing the command for every row that passes.

If you have existing code, you can likely acheive that using the Script Component - but be sure your scenario is appropriate first.

Can you describe some more about what you are trying to acheive?

Donald

|||

It's not necessarily a particular command. It's using ADO vs. OLE DB. Our DataAccessLayer code uses ADO, which I was able to import into the VSA scripting editor and that seemed to work just fine. However the output of the Script Component feeds records to separate SQL commands, but the only thing that I see that's available is the OLE DB Command. I know I could probably do this within the Script component, but it just seems to be more logically designed and layed out this way.

Thanks,

Jeff

|||

I have a set of records coming in to a Script Component and based on the data and lookups into the destination table I'm determining if data should be deleted, inserted or updated in the destination table. It's basically a table copy routine with a little intelligence for deletes.

Jeff

|||

JazzGeek wrote:

It's not necessarily a particular command. It's using ADO vs. OLE DB. Our DataAccessLayer code uses ADO, which I was able to import into the VSA scripting editor and that seemed to work just fine. However the output of the Script Component feeds records to separate SQL commands, but the only thing that I see that's available is the OLE DB Command. I know I could probably do this within the Script component, but it just seems to be more logically designed and layed out this way.

Thanks,

Jeff

Sorry, I'm really really confused. In SSIS you don't access data using the OLE DB Command component. You do it with a source adapter. There are many source adapters including one for OLE DB and one for ADO.

Are you saying that you want apply modifications using ADO rather than OLE DB?

-Jamie

|||

Hey Jamie,

I was just hoping to use our DataAccessLayer code library (which utilized ADO) to perform DB commands against our databases. I realize that with the OLE DB Command object you can execute a SQL command based on any of the input column data. Since I couldn't find an ADO Command object, I thought I'd use our DataAccessLayer library within a Script component to do it.

Thanks for the comments!

Jeff

NLB drainstop command issues

Hello,

We use the NLB drainstop command to gracefully move connections from one database server (server A) to another (server B) but we discovered with one application in particular that the connections stay alive on the server A.

The applications make DB connections using standard .NET System.Data classes.

Has anyone experienced this before? Any pointers on what to check for?

Thanks

Alexwhich database is this?

you have posted in the non-database-specific "database concepts" forum, and it sounds like this thread should be moved to a database-specific forum|||Thanks. I'll post it in the sql server forum.|||Hello,

We use the NLB drainstop command to gracefully move connections from one database server (server A) to another (server B) but we discovered with one application in particular that the connections stay alive on the server A.

The applications make DB connections using standard .NET System.Data classes.
The database servers are SQL Server 2005.

Has anyone experienced this before? Any pointers on what to check for?

Thanks

Alex|||no, don't do that, we'll just move this thread over there

:)|||no, don't do that, we'll just move this thread over there

:)Hehe!! :) Thank you sir!

I hope someone here can assist!

newbies help 2 questions

There is 1 error when i tried to install msde into my window 2003 server web edition.
1. I use command prompt to enter c:\\sql2ksp3>setup SAPWD=aA1234567
the errors is "go to the control panel to install and configure system components"
How do i install ?

Sql client for 2003 server web edition
2. i tried to install sql client in window 2003 web edition but the msdn cd could not start after i double click on the setupsql.exe. I thought the sql client can be install but not the server on web edition ?I have solve Q1 above by using run the type in
Setup.exe /qb+ INSTANCENAME=NetSDK DISABLENETWORKPROTOCOLS=1 SAPWD=<YOUR SA PASSWORD
It seems you cannot double click on the setup.exe in the extracted folder and you cannot use the ms-dos command prompt becuase both return errors like,
"A strong SA password is required for security reason. Please us SAPWD switch to supplyt he same. Refer to readme for more details. Setup will now exits."
or
"Go to the control panel to install and configure system components"

Hope some one can help on Q2|||I think you can not install sql clinet tools on 2003 WE

Newbie-Page Torn Detection - ambigious results

Hi All,
I had a database tornpage detected and at traced the table which is causing the problem and executed the following command:
dbcc checktable('inbox',REPAIR_ALLOW_DATA_LOSS).
The output was. CHECKTABLE fixed 0 allocation errors and 90 consistency errors in table 'inbox'
after that i made the db to single user and executed dbcc checkdb and the output was
CHECKDB found 0 allocation errors and 88 consistency errors in database 'HelloDB'.
Server: Msg 8939, Level 16, State 98, Line 1
Table error: Object ID 37575172, index ID 0, page (1:96043). Test (IS_ON (BUF_IOERR, bp->bstat) && bp-
>berrcode) failed. Values are 2057 and -1.
Server: Msg 8939, Level 16, State 1, Line 1
Table error: Object ID 37575172, index ID 0, page (1:96045). Test (IS_ON (BUF_IOERR, bp->bstat) && bp-
>berrcode) failed. Values are 2057 and -1.
Server: Msg 8952, Level 16, State 1, Line 1
Table error: Database 'HelloDB', index 'Inbox.PK_Inbox' (ID 37575172) (index ID 2). Extra or invalid key for the keys:
Server: Msg 8956, Level 16, State 1, Line 1
Index row (1:152032:187) with values (MessageID =3D C9C9BD2B-
C135-487C-B26E-006FDB45B523d=A2=EE ) points to the data row identified by (RID =3D (1:96045:20)).
.
.
.
.
I am confused why it is showing inconsistency errors for DBCC Checkdb whereas it showed all the errors are fixed when checktable was run.
The improvement I found was, even it shows errors on DBCC CheckDB, it is not affecting the Operatins on DB.( before dbcc checktable the db was non operational)
Can anyone suggest on what is happening? and how to overcome this situation?
Hoping for a Quick reply
Thanks in Advance
Regards
ThirumalHi All,
At last I could find the soultion. KB says that Error:Server: Msg 8939, Level 16, State 98, Line 1
CAUSE
The sysindexes.status field should have a value of 2097152 so that the nonclustered index column can allow the insertion of NULL values for both SQL Server 7.0 and SQL Server 2000.
In SQL Server 7.0, if the sysindexes.status value is 0 for the nonclustered index in a table, the nonclustered index also allows the insertion of NULL values. If you run a DBCC CHECKDB statement on the SQL Server 7.0 database, the problem is not reported. After you upgrade the table in SQL Server 2000, the DBCC CHECKDB statement reports the error message described in the "Symptoms" section because a NULL value is not allowed when the sysindexes.status value is not 2097152.
I have run the DBCC DBREINDEX as
DBCC DBREINDEX ('inbox','',0)
after this i have run
dbcc checkdb
no errors Reported
I request the experts to suggest what I have done is proper or not?
Regards
Thirumal
>--Original Message--
>Hi All,
>I had a database tornpage detected and at traced the table >which is causing the problem and executed the following >command:
>dbcc checktable('inbox',REPAIR_ALLOW_DATA_LOSS).
>The output was. CHECKTABLE fixed 0 allocation errors and >90 consistency errors in table 'inbox'
>after that i made the db to single user and executed dbcc >checkdb and the output was >CHECKDB found 0 allocation errors and 88 consistency >errors in database 'HelloDB'. >Server: Msg 8939, Level 16, State 98, Line 1
>Table error: Object ID 37575172, index ID 0, page >(1:96043). Test (IS_ON (BUF_IOERR, bp->bstat) && bp-
>>berrcode) failed. Values are 2057 and -1.
>Server: Msg 8939, Level 16, State 1, Line 1
>Table error: Object ID 37575172, index ID 0, page >(1:96045). Test (IS_ON (BUF_IOERR, bp->bstat) && bp-
>>berrcode) failed. Values are 2057 and -1.
>Server: Msg 8952, Level 16, State 1, Line 1
>Table error: Database 'HelloDB', index 'Inbox.PK_Inbox' >(ID 37575172) (index ID 2). Extra or invalid key for the >keys:
>Server: Msg 8956, Level 16, State 1, Line 1
>Index row (1:152032:187) with values (MessageID =3D C9C9BD2B-
>C135-487C-B26E-006FDB45B523d=A2=EE ) points to the data row >identified by (RID =3D (1:96045:20)).
>..
>..
>..
>..
>I am confused why it is showing inconsistency errors for >DBCC Checkdb whereas it showed all the errors are fixed >when checktable was run.
>The improvement I found was, even it shows errors on DBCC >CheckDB, it is not affecting the Operatins on DB.( before >dbcc checktable the db was non operational)
>Can anyone suggest on what is happening? and how to >overcome this situation?
>Hoping for a Quick reply
>Thanks in Advance
>Regards
>Thirumal
>.
>

Newbie:why osql doesn't let me logon ?

I need to move tempdb from c: drive to d:on the local server.
I try to go to osql to perform the deattach database command, but here is
what I get:
My user is an administrator on the local SQL server.
C:>osql -U myuser
Password:
Login failed for user 'myuser. Reason: not associated with a trusted SQL
Server connectionIf you are local administrator and you have mixed authentification activated
you can easily login with
OSQL -E
else you have to provide a password with
OSQL -UUsername -PPassword
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"Magoo" <magoo-nospam@.hotmail.com> schrieb im Newsbeitrag
news:OgpRnrYUFHA.3244@.TK2MSFTNGP15.phx.gbl...
>I need to move tempdb from c: drive to d:on the local server.
> I try to go to osql to perform the deattach database command, but here is
> what I get:
> My user is an administrator on the local SQL server.
> C:>osql -U myuser
> Password:
> Login failed for user 'myuser. Reason: not associated with a trusted SQL
> Server connection
>
>|||Does your SQL Server allow SQL server authentication, or only NT
authentication?
"Magoo" <magoo-nospam@.hotmail.com> wrote in message
news:OgpRnrYUFHA.3244@.TK2MSFTNGP15.phx.gbl...
>I need to move tempdb from c: drive to d:on the local server.
> I try to go to osql to perform the deattach database command, but here is
> what I get:
> My user is an administrator on the local SQL server.
> C:>osql -U myuser
> Password:
> Login failed for user 'myuser. Reason: not associated with a trusted SQL
> Server connection
>
>|||Only NT auth.
"Lisa Pearlson" <no@.spam.plz> wrote in message
news:OGeDv5aUFHA.228@.TK2MSFTNGP12.phx.gbl...
> Does your SQL Server allow SQL server authentication, or only NT
> authentication?
>
> "Magoo" <magoo-nospam@.hotmail.com> wrote in message
> news:OgpRnrYUFHA.3244@.TK2MSFTNGP15.phx.gbl...
> >I need to move tempdb from c: drive to d:on the local server.
> > I try to go to osql to perform the deattach database command, but here
is
> > what I get:
> > My user is an administrator on the local SQL server.
> >
> > C:>osql -U myuser
> > Password:
> > Login failed for user 'myuser. Reason: not associated with a trusted SQL
> > Server connection
> >
> >
> >
> >
>|||Then OSQL -U won´t work. YOu hav to use OSQL -E instead (to use Integrated
authentification)
HTH, Jens Suessmeyer.
--
https://www.sqlserver2005.de
--
"Magoo" <magoo-nospam@.hotmail.com> schrieb im Newsbeitrag
news:%23waUTUbUFHA.2128@.TK2MSFTNGP15.phx.gbl...
> Only NT auth.
> "Lisa Pearlson" <no@.spam.plz> wrote in message
> news:OGeDv5aUFHA.228@.TK2MSFTNGP12.phx.gbl...
>> Does your SQL Server allow SQL server authentication, or only NT
>> authentication?
>>
>> "Magoo" <magoo-nospam@.hotmail.com> wrote in message
>> news:OgpRnrYUFHA.3244@.TK2MSFTNGP15.phx.gbl...
>> >I need to move tempdb from c: drive to d:on the local server.
>> > I try to go to osql to perform the deattach database command, but here
> is
>> > what I get:
>> > My user is an administrator on the local SQL server.
>> >
>> > C:>osql -U myuser
>> > Password:
>> > Login failed for user 'myuser. Reason: not associated with a trusted
>> > SQL
>> > Server connection
>> >
>> >
>> >
>> >
>>
>|||Ok, so now I do osql -E
Then a prompt appears.
I go there and I do:
1>sp_detach_db tempdb
Nothing happens. I see that my tempdb continues where it is.
How can I move the tempdb to another drive then ?
"Jens Süßmeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:ezj%23gZbUFHA.3280@.TK2MSFTNGP09.phx.gbl...
> Then OSQL -U won´t work. YOu hav to use OSQL -E instead (to use Integrated
> authentification)
> HTH, Jens Suessmeyer.
> --
> https://www.sqlserver2005.de
> --
> "Magoo" <magoo-nospam@.hotmail.com> schrieb im Newsbeitrag
> news:%23waUTUbUFHA.2128@.TK2MSFTNGP15.phx.gbl...
> > Only NT auth.
> >
> > "Lisa Pearlson" <no@.spam.plz> wrote in message
> > news:OGeDv5aUFHA.228@.TK2MSFTNGP12.phx.gbl...
> >> Does your SQL Server allow SQL server authentication, or only NT
> >> authentication?
> >>
> >>
> >> "Magoo" <magoo-nospam@.hotmail.com> wrote in message
> >> news:OgpRnrYUFHA.3244@.TK2MSFTNGP15.phx.gbl...
> >> >I need to move tempdb from c: drive to d:on the local server.
> >> > I try to go to osql to perform the deattach database command, but
here
> > is
> >> > what I get:
> >> > My user is an administrator on the local SQL server.
> >> >
> >> > C:>osql -U myuser
> >> > Password:
> >> > Login failed for user 'myuser. Reason: not associated with a trusted
> >> > SQL
> >> > Server connection
> >> >
> >> >
> >> >
> >> >
> >>
> >>
> >
> >
>|||And GO ! ;-)
> 1>sp_detach_db tempdb
> 2>GO
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"Magoo" <magoo-nospam@.hotmail.com> schrieb im Newsbeitrag
news:%23cS9YfbUFHA.1044@.TK2MSFTNGP10.phx.gbl...
> Ok, so now I do osql -E
> Then a prompt appears.
> I go there and I do:
> 1>sp_detach_db tempdb
> Nothing happens. I see that my tempdb continues where it is.
> How can I move the tempdb to another drive then ?
> "Jens Süßmeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote
> in
> message news:ezj%23gZbUFHA.3280@.TK2MSFTNGP09.phx.gbl...
>> Then OSQL -U won´t work. YOu hav to use OSQL -E instead (to use
>> Integrated
>> authentification)
>> HTH, Jens Suessmeyer.
>> --
>> https://www.sqlserver2005.de
>> --
>> "Magoo" <magoo-nospam@.hotmail.com> schrieb im Newsbeitrag
>> news:%23waUTUbUFHA.2128@.TK2MSFTNGP15.phx.gbl...
>> > Only NT auth.
>> >
>> > "Lisa Pearlson" <no@.spam.plz> wrote in message
>> > news:OGeDv5aUFHA.228@.TK2MSFTNGP12.phx.gbl...
>> >> Does your SQL Server allow SQL server authentication, or only NT
>> >> authentication?
>> >>
>> >>
>> >> "Magoo" <magoo-nospam@.hotmail.com> wrote in message
>> >> news:OgpRnrYUFHA.3244@.TK2MSFTNGP15.phx.gbl...
>> >> >I need to move tempdb from c: drive to d:on the local server.
>> >> > I try to go to osql to perform the deattach database command, but
> here
>> > is
>> >> > what I get:
>> >> > My user is an administrator on the local SQL server.
>> >> >
>> >> > C:>osql -U myuser
>> >> > Password:
>> >> > Login failed for user 'myuser. Reason: not associated with a trusted
>> >> > SQL
>> >> > Server connection
>> >> >
>> >> >
>> >> >
>> >> >
>> >>
>> >>
>> >
>> >
>>
>

Newbie:why osql doesn't let me logon ?

I need to move tempdb from c: drive to d:on the local server.
I try to go to osql to perform the deattach database command, but here is
what I get:
My user is an administrator on the local SQL server.
C:>osql -U myuser
Password:
Login failed for user 'myuser. Reason: not associated with a trusted SQL
Server connectionIf you are local administrator and you have mixed authentification activated
you can easily login with
OSQL -E
else you have to provide a password with
OSQL -UUsername -PPassword
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Magoo" <magoo-nospam@.hotmail.com> schrieb im Newsbeitrag
news:OgpRnrYUFHA.3244@.TK2MSFTNGP15.phx.gbl...
>I need to move tempdb from c: drive to d:on the local server.
> I try to go to osql to perform the deattach database command, but here is
> what I get:
> My user is an administrator on the local SQL server.
> C:>osql -U myuser
> Password:
> Login failed for user 'myuser. Reason: not associated with a trusted SQL
> Server connection
>
>|||Does your SQL Server allow SQL server authentication, or only NT
authentication?
"Magoo" <magoo-nospam@.hotmail.com> wrote in message
news:OgpRnrYUFHA.3244@.TK2MSFTNGP15.phx.gbl...
>I need to move tempdb from c: drive to d:on the local server.
> I try to go to osql to perform the deattach database command, but here is
> what I get:
> My user is an administrator on the local SQL server.
> C:>osql -U myuser
> Password:
> Login failed for user 'myuser. Reason: not associated with a trusted SQL
> Server connection
>
>|||Only NT auth.
"Lisa Pearlson" <no@.spam.plz> wrote in message
news:OGeDv5aUFHA.228@.TK2MSFTNGP12.phx.gbl...
> Does your SQL Server allow SQL server authentication, or only NT
> authentication?
>
> "Magoo" <magoo-nospam@.hotmail.com> wrote in message
> news:OgpRnrYUFHA.3244@.TK2MSFTNGP15.phx.gbl...
is[vbcol=seagreen]
>|||Then OSQL -U wont work. YOu hav to use OSQL -E instead (to use Integrated
authentification)
HTH, Jens Suessmeyer.
https://www.sqlserver2005.de
--
"Magoo" <magoo-nospam@.hotmail.com> schrieb im Newsbeitrag
news:%23waUTUbUFHA.2128@.TK2MSFTNGP15.phx.gbl...
> Only NT auth.
> "Lisa Pearlson" <no@.spam.plz> wrote in message
> news:OGeDv5aUFHA.228@.TK2MSFTNGP12.phx.gbl...
> is
>|||Ok, so now I do osql -E
Then a prompt appears.
I go there and I do:
1>sp_detach_db tempdb
Nothing happens. I see that my tempdb continues where it is.
How can I move the tempdb to another drive then ?
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:ezj%23gZbUFHA.3280@.TK2MSFTNGP09.phx.gbl...
> Then OSQL -U wont work. YOu hav to use OSQL -E instead (to use Integrated
> authentification)
> HTH, Jens Suessmeyer.
> --
> https://www.sqlserver2005.de
> --
> "Magoo" <magoo-nospam@.hotmail.com> schrieb im Newsbeitrag
> news:%23waUTUbUFHA.2128@.TK2MSFTNGP15.phx.gbl...
here[vbcol=seagreen]
>|||And GO ! ;-)

> 1>sp_detach_db tempdb
> 2>GO
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Magoo" <magoo-nospam@.hotmail.com> schrieb im Newsbeitrag
news:%23cS9YfbUFHA.1044@.TK2MSFTNGP10.phx.gbl...
> Ok, so now I do osql -E
> Then a prompt appears.
> I go there and I do:
> 1>sp_detach_db tempdb
> Nothing happens. I see that my tempdb continues where it is.
> How can I move the tempdb to another drive then ?
> "Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote
> in
> message news:ezj%23gZbUFHA.3280@.TK2MSFTNGP09.phx.gbl...
> here
>

Newbie:why osql doesn't let me logon ?

I need to move tempdb from c: drive to d:on the local server.
I try to go to osql to perform the deattach database command, but here is
what I get:
My user is an administrator on the local SQL server.
C:>osql -U myuser
Password:
Login failed for user 'myuser. Reason: not associated with a trusted SQL
Server connection
If you are local administrator and you have mixed authentification activated
you can easily login with
OSQL -E
else you have to provide a password with
OSQL -UUsername -PPassword
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"Magoo" <magoo-nospam@.hotmail.com> schrieb im Newsbeitrag
news:OgpRnrYUFHA.3244@.TK2MSFTNGP15.phx.gbl...
>I need to move tempdb from c: drive to d:on the local server.
> I try to go to osql to perform the deattach database command, but here is
> what I get:
> My user is an administrator on the local SQL server.
> C:>osql -U myuser
> Password:
> Login failed for user 'myuser. Reason: not associated with a trusted SQL
> Server connection
>
>
|||Does your SQL Server allow SQL server authentication, or only NT
authentication?
"Magoo" <magoo-nospam@.hotmail.com> wrote in message
news:OgpRnrYUFHA.3244@.TK2MSFTNGP15.phx.gbl...
>I need to move tempdb from c: drive to d:on the local server.
> I try to go to osql to perform the deattach database command, but here is
> what I get:
> My user is an administrator on the local SQL server.
> C:>osql -U myuser
> Password:
> Login failed for user 'myuser. Reason: not associated with a trusted SQL
> Server connection
>
>
|||Only NT auth.
"Lisa Pearlson" <no@.spam.plz> wrote in message
news:OGeDv5aUFHA.228@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> Does your SQL Server allow SQL server authentication, or only NT
> authentication?
>
> "Magoo" <magoo-nospam@.hotmail.com> wrote in message
> news:OgpRnrYUFHA.3244@.TK2MSFTNGP15.phx.gbl...
is
>
|||Then OSQL -U wont work. YOu hav to use OSQL -E instead (to use Integrated
authentification)
HTH, Jens Suessmeyer.
https://www.sqlserver2005.de
"Magoo" <magoo-nospam@.hotmail.com> schrieb im Newsbeitrag
news:%23waUTUbUFHA.2128@.TK2MSFTNGP15.phx.gbl...
> Only NT auth.
> "Lisa Pearlson" <no@.spam.plz> wrote in message
> news:OGeDv5aUFHA.228@.TK2MSFTNGP12.phx.gbl...
> is
>
|||Ok, so now I do osql -E
Then a prompt appears.
I go there and I do:
1>sp_detach_db tempdb
Nothing happens. I see that my tempdb continues where it is.
How can I move the tempdb to another drive then ?
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:ezj%23gZbUFHA.3280@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Then OSQL -U wont work. YOu hav to use OSQL -E instead (to use Integrated
> authentification)
> HTH, Jens Suessmeyer.
> --
> https://www.sqlserver2005.de
> --
> "Magoo" <magoo-nospam@.hotmail.com> schrieb im Newsbeitrag
> news:%23waUTUbUFHA.2128@.TK2MSFTNGP15.phx.gbl...
here
>
|||And GO ! ;-)

> 1>sp_detach_db tempdb
> 2>GO
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"Magoo" <magoo-nospam@.hotmail.com> schrieb im Newsbeitrag
news:%23cS9YfbUFHA.1044@.TK2MSFTNGP10.phx.gbl...
> Ok, so now I do osql -E
> Then a prompt appears.
> I go there and I do:
> 1>sp_detach_db tempdb
> Nothing happens. I see that my tempdb continues where it is.
> How can I move the tempdb to another drive then ?
> "Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote
> in
> message news:ezj%23gZbUFHA.3280@.TK2MSFTNGP09.phx.gbl...
> here
>

newbie: TSQL command for number of rows ?

I couldnt find on google a command to get the number of rows in a table.
Any suggestions ?
TIA
-steveSELECT COUNT(*)
FROM YourTable
David Portas
SQL Server MVP
--|||Use the TOP statement
Example:
USE NORTHWIND
GO
Select TOP 5 From Orders
But remember that TOP is also recommended with the use of order because the
TOP clause will produce an unpredictable subset of n-Rows.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"steve" <steve@.here.com> schrieb im Newsbeitrag
news:Ph4ie.56688$th3.616072@.wagner.videotron.net...
>I couldnt find on google a command to get the number of rows in a table.
> Any suggestions ?
> TIA
> -steve
>|||SELECT COUNT(*) FROM tbl
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"steve" <steve@.here.com> wrote in message news:Ph4ie.56688$th3.616072@.wagner.videotron.net.
.
>I couldnt find on google a command to get the number of rows in a table.
> Any suggestions ?
> TIA
> -steve
>|||Ok, guess I didnt get your questions that right X-)
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> schrieb
im Newsbeitrag news:u7Wq6ljWFHA.1796@.TK2MSFTNGP15.phx.gbl...
> Use the TOP statement
> Example:
> USE NORTHWIND
> GO
> Select TOP 5 From Orders
> But remember that TOP is also recommended with the use of order because
> the TOP clause will produce an unpredictable subset of n-Rows.
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "steve" <steve@.here.com> schrieb im Newsbeitrag
> news:Ph4ie.56688$th3.616072@.wagner.videotron.net...
>|||I am useless!
Thanx a lot!!
-steve
"steve" <steve@.here.com> a crit dans le message de news:
Ph4ie.56688$th3.616072@.wagner.videotron.net...
>I couldnt find on google a command to get the number of rows in a table.
> Any suggestions ?
> TIA
> -steve
>

Newbie: Now getting return value, but need help debugging OLE DB Command Transformation

Good morning, all,

OK, I have read a ton of posting on this issue, but either they don't give enough information or they are for packages which use the Execute SQL command, whereas I am using the OLE DB Command Data Flow Transformation.

I have an Excel spreadsheet that we are receiving from agencies with rows of client data which I have to load into an application that is ready to go live. I also have a stored procedure spClientsInsertRcd, which was written for the application. In the normal flow of the application, the stored procedure is called from a Coldfusion page, which does some processing prior to calling it. So, I have written a 'wrapper' stored procedure, spImportAgencyData, which does the processing and then calls the spClientInsertRcd.

My dataflow has the following components:

An Excel Source, containing my test data, consisting of just one row of data,

which points to a

Derived Column Transformation, which reformats the SSN and adds a user variable, named returnValue with an Expression value of @.[User::returnvariable] set to a four-byte signed integer, which (i think) I need to get the value out of the stored procedure.

which points to a

Data Conversion Transformation, which takes care of all the datatype conversions

which points to a

OLE DB Command, which contains the following as the SQL Command:

exec ?= spImportAgencyData ?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?

In the OLE DB Command, I have mapped returnValue, my user variable to @.RETURN_VALUE.

Right now, I am in initial testing. The dataflow shows that it is succeeding, but my one data record for testing is not getting inserted. I need to get the value of returnValue to figure out what is happening.

How do I get the value of the returnValue? I have tried putting a recordset destination after the OLE DB command, but that just gives me the data that went into the OLE DB Command.

Thanks,
Kathryn

OK,

I have made a few changes to the package and I can get a return value. The problem is that the data, which is currently only one row in an Excel worksheet, is not getting loaded.

The OLE DB Command's SQL Command is now:

exec spImportAgencyData ?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,? output

with the final parameter being an output parameter. On the Column Mappings tab, I have mapped the output parameter @.returnValue which is in the Available Destination Columns to the returnValue variable (in the Available Input Columns) I created earlier in the Derived Column Transformation.

I have added a recordset Destination after the OLE DB Command and I only have the returnValue column in it.

The returnValue is always being returned as 0, which is either because I am not mapping it correctly or because the data is not getting loaded, I'm not sure which.

I can run the stored procedure from SQL Server Management Studio as

DECLARE @.returnValue int

EXEC [dbo].[spImportAgencyData]

@.URN = NULL,

@.FirstName = N'Kathryn',

@........

@.returnValue = @.returnValue OUTPUT

SELECT @.returnValue as N'@.returnValue'

and the correct new ClientID is returned, so the stored procedure is working. I have put a Data Viewer right before the OLE DB Command and run the stored procedure with the exact data coming from the Excel spreadsheet, just to double check and it runs and returns the new Client ID.

Does anyone have any tips on debugging this problem?

Thanks,
Kathryn

|||I had a similar issue (return a value from a stored procedure with 2 Varchar parameters), and eventually this is how I configured the Execute SQL Task:

General:

Connection Type: OLE DB
SQL Statemenet: EXEC ? = AUDIT.LoggingStoredProcedure ?, ?
Bypass Prepare: True

Parameter Mapping:

[Variable Name] | [Direction] | [Data Type] | [Parameter Name]
User::ReturnValue | Return Value | Long | 0
User::Parameter1 | Input | VARCHAR | 1
USer::Parameter2 | Input | VARCHAR | 2

Newbie: Now getting return value, but need help debugging OLE DB Command Transformation

Good morning, all,

OK, I have read a ton of posting on this issue, but either they don't give enough information or they are for packages which use the Execute SQL command, whereas I am using the OLE DB Command Data Flow Transformation.

I have an Excel spreadsheet that we are receiving from agencies with rows of client data which I have to load into an application that is ready to go live. I also have a stored procedure spClientsInsertRcd, which was written for the application. In the normal flow of the application, the stored procedure is called from a Coldfusion page, which does some processing prior to calling it. So, I have written a 'wrapper' stored procedure, spImportAgencyData, which does the processing and then calls the spClientInsertRcd.

My dataflow has the following components:

An Excel Source, containing my test data, consisting of just one row of data,

which points to a

Derived Column Transformation, which reformats the SSN and adds a user variable, named returnValue with an Expression value of @.[User::returnvariable] set to a four-byte signed integer, which (i think) I need to get the value out of the stored procedure.

which points to a

Data Conversion Transformation, which takes care of all the datatype conversions

which points to a

OLE DB Command, which contains the following as the SQL Command:

exec ?= spImportAgencyData ?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?

In the OLE DB Command, I have mapped returnValue, my user variable to @.RETURN_VALUE.

Right now, I am in initial testing. The dataflow shows that it is succeeding, but my one data record for testing is not getting inserted. I need to get the value of returnValue to figure out what is happening.

How do I get the value of the returnValue? I have tried putting a recordset destination after the OLE DB command, but that just gives me the data that went into the OLE DB Command.

Thanks,
Kathryn

OK,

I have made a few changes to the package and I can get a return value. The problem is that the data, which is currently only one row in an Excel worksheet, is not getting loaded.

The OLE DB Command's SQL Command is now:

exec spImportAgencyData ?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,? output

with the final parameter being an output parameter. On the Column Mappings tab, I have mapped the output parameter @.returnValue which is in the Available Destination Columns to the returnValue variable (in the Available Input Columns) I created earlier in the Derived Column Transformation.

I have added a recordset Destination after the OLE DB Command and I only have the returnValue column in it.

The returnValue is always being returned as 0, which is either because I am not mapping it correctly or because the data is not getting loaded, I'm not sure which.

I can run the stored procedure from SQL Server Management Studio as

DECLARE @.returnValue int

EXEC [dbo].[spImportAgencyData]

@.URN = NULL,

@.FirstName = N'Kathryn',

@........

@.returnValue = @.returnValue OUTPUT

SELECT @.returnValue as N'@.returnValue'

and the correct new ClientID is returned, so the stored procedure is working. I have put a Data Viewer right before the OLE DB Command and run the stored procedure with the exact data coming from the Excel spreadsheet, just to double check and it runs and returns the new Client ID.

Does anyone have any tips on debugging this problem?

Thanks,
Kathryn

|||I had a similar issue (return a value from a stored procedure with 2 Varchar parameters), and eventually this is how I configured the Execute SQL Task:

General:

Connection Type: OLE DB
SQL Statemenet: EXEC ? = AUDIT.LoggingStoredProcedure ?, ?
Bypass Prepare: True

Parameter Mapping:

[Variable Name] | [Direction] | [Data Type] | [Parameter Name]
User::ReturnValue | Return Value | Long | 0
User::Parameter1 | Input | VARCHAR | 1
USer::Parameter2 | Input | VARCHAR | 2