Iterate through a list of files with bcp

I have a folder full of date-stamped files: 010101.txt, 010201.txt,
010301.txt, etc.
I have a working bcp command that points to a specific file.
declare @.bcpCommand varchar(1000)
select @.bcpCommand = 'BCP "Import.dbo.NAV" in
"d:\folder\010101.txt" -c -T -t, -S "SERVER1\SERVER1"'
EXEC master.dbo.xp_cmdshell @.bcpCommand
How can I script the bcp command so it will import all files between say
010101 and 120105? Complicating factor; there are gaps in the sequence. Can
the script continue even if it doesn't find a file?
Alternatively, if it was possible to issue the bcp command against all files
in a folder no matter what the filename, that would work also.
This is a one-time data load with SQL Server 2000/SP4
Thanks to anyone who could help.For sake of simplicity, write a cursor script & get it off. Here is a quick
try:
DECLARE @.cmd VARCHAR(500), @.c VARCHAR(500)
CREATE TABLE #t ( c VARCHAR( 500 ) NOT NULL )
INSERT #t EXEC master.dbo.xp_cmdshell 'DIR d:\folder\*.txt /B'
DECLARE c CURSOR FOR SELECT f FROM #t
OPEN c
FETCH NEXT FROM c INTO @.c
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.cmd = 'BCP "Import.dbo.NAV" in "d:\folder\' + @.c + '" -c -T -t, -S
"SERVER1\SERVER1"'
EXEC master.dbo.xp_cmdshell @.cmd
FETCH NEXT FROM c
END
CLOSE c
DEALLOCATE c
DROP TABLE #t ;
Anithsql

It seems SQL editor do not support "Format" function. Is it right?

When I write SQL command in the editor, I can not find a menu to format my command.

Also the "Format Document" function in VS2005 Beta2 seems do not delete blank rows. I found a good Format tool here. I realy hope MSSQL 2005 has build-in function like that.
http://www.sqlinform.com/

Issues with DBCC SHOWCONTIG

I am trying to resolve database fragmentation and I am having two issues wit
h
the DBCC SHOWCONTIG command.
1. Running DBCC SHOWCONTIG WITH FAST, ALL_INDEXES, TABLERESULTS shows
different values for logical fragmentation then running DBCC
SHOWCONTIG(<tablename> ) WITH FAST, ALL_INDEXES, TABLERESULTS. Which value
should be considered as correct?
2. For some tables DBCC SHOWCONTIG(<tablename> ) WITH FAST, ALL_INDEXES,
TABLERESULTS shows results for indexes that do not appear in the QA Object
Browser or the EM Manage Indexes/Relationship window. Consequently, running
DBCC INDEXDEFRAG on those indexes results in an error on Parameter 3, the
index name. What is going on here?
BONUS QUESTION:
1. Which is a more accurate reflection of fragmentation: Logical
Fragmentation or Scan Density?1) Can you elaborate, or show an example?
2) What indexid? Remember that 0 is the datapages for a table, and that is n
ot an index.
3) Logical Fragmentation. Scan Density will be (as I remember) removed from
next version as it will
show bogus values of the filegroup have more than one file.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Gordon" <Gordon@.discussions.microsoft.com> wrote in message
news:D90B5D30-BAA3-4114-8A51-48ECF2DD714D@.microsoft.com...
>I am trying to resolve database fragmentation and I am having two issues wi
th
> the DBCC SHOWCONTIG command.
> 1. Running DBCC SHOWCONTIG WITH FAST, ALL_INDEXES, TABLERESULTS shows
> different values for logical fragmentation then running DBCC
> SHOWCONTIG(<tablename> ) WITH FAST, ALL_INDEXES, TABLERESULTS. Which value
> should be considered as correct?
> 2. For some tables DBCC SHOWCONTIG(<tablename> ) WITH FAST, ALL_INDEXES,
> TABLERESULTS shows results for indexes that do not appear in the QA Object
> Browser or the EM Manage Indexes/Relationship window. Consequently, runnin
g
> DBCC INDEXDEFRAG on those indexes results in an error on Parameter 3, the
> index name. What is going on here?
> BONUS QUESTION:
> 1. Which is a more accurate reflection of fragmentation: Logical
> Fragmentation or Scan Density?|||1. This is known bug in SQL Server 2000. If you specify ALL_INDEXES or all
tables (by not passing in any parameter), logical fragmentation information
for some indexes might be a little bit off (It does not matter if the index
is large; the difference will show up if the index is small).
The problem is fixed in SQL Server 2000 SP4 (which is in Beta stage now).
2. I have no clue on this. Remember, index could be dropped between you
call DBCC SHOWCONTIG and when you use DBCC INDEXDEFRAG. That might be the
problem you see.
Bonus 1: Agree with Tibor's reply, Logical Fragmentation, with one
correction: for backward compatiable, Scan Density will still be supported
in DBCC SHOWCONTIG when SQL Server 2005 ships. (DBCC SHOWCONTIG will be
deprecated and replaced by a dynamic managed view - checkout SQL Server 2005
Beta 2 for more details)
Hope this helps.
Stephen Jiang
Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Gordon" <Gordon@.discussions.microsoft.com> wrote in message
news:D90B5D30-BAA3-4114-8A51-48ECF2DD714D@.microsoft.com...
> I am trying to resolve database fragmentation and I am having two issues
with
> the DBCC SHOWCONTIG command.
> 1. Running DBCC SHOWCONTIG WITH FAST, ALL_INDEXES, TABLERESULTS shows
> different values for logical fragmentation then running DBCC
> SHOWCONTIG(<tablename> ) WITH FAST, ALL_INDEXES, TABLERESULTS. Which value
> should be considered as correct?
> 2. For some tables DBCC SHOWCONTIG(<tablename> ) WITH FAST, ALL_INDEXES,
> TABLERESULTS shows results for indexes that do not appear in the QA Object
> Browser or the EM Manage Indexes/Relationship window. Consequently,
running
> DBCC INDEXDEFRAG on those indexes results in an error on Parameter 3, the
> index name. What is going on here?
> BONUS QUESTION:
> 1. Which is a more accurate reflection of fragmentation: Logical
> Fragmentation or Scan Density?

Issues with DBCC SHOWCONTIG

I am trying to resolve database fragmentation and I am having two issues with
the DBCC SHOWCONTIG command.
1. Running DBCC SHOWCONTIG WITH FAST, ALL_INDEXES, TABLERESULTS shows
different values for logical fragmentation then running DBCC
SHOWCONTIG(<tablename>) WITH FAST, ALL_INDEXES, TABLERESULTS. Which value
should be considered as correct?
2. For some tables DBCC SHOWCONTIG(<tablename>) WITH FAST, ALL_INDEXES,
TABLERESULTS shows results for indexes that do not appear in the QA Object
Browser or the EM Manage Indexes/Relationship window. Consequently, running
DBCC INDEXDEFRAG on those indexes results in an error on Parameter 3, the
index name. What is going on here?
BONUS QUESTION:
1. Which is a more accurate reflection of fragmentation: Logical
Fragmentation or Scan Density?
1) Can you elaborate, or show an example?
2) What indexid? Remember that 0 is the datapages for a table, and that is not an index.
3) Logical Fragmentation. Scan Density will be (as I remember) removed from next version as it will
show bogus values of the filegroup have more than one file.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Gordon" <Gordon@.discussions.microsoft.com> wrote in message
news:D90B5D30-BAA3-4114-8A51-48ECF2DD714D@.microsoft.com...
>I am trying to resolve database fragmentation and I am having two issues with
> the DBCC SHOWCONTIG command.
> 1. Running DBCC SHOWCONTIG WITH FAST, ALL_INDEXES, TABLERESULTS shows
> different values for logical fragmentation then running DBCC
> SHOWCONTIG(<tablename>) WITH FAST, ALL_INDEXES, TABLERESULTS. Which value
> should be considered as correct?
> 2. For some tables DBCC SHOWCONTIG(<tablename>) WITH FAST, ALL_INDEXES,
> TABLERESULTS shows results for indexes that do not appear in the QA Object
> Browser or the EM Manage Indexes/Relationship window. Consequently, running
> DBCC INDEXDEFRAG on those indexes results in an error on Parameter 3, the
> index name. What is going on here?
> BONUS QUESTION:
> 1. Which is a more accurate reflection of fragmentation: Logical
> Fragmentation or Scan Density?
|||1. This is known bug in SQL Server 2000. If you specify ALL_INDEXES or all
tables (by not passing in any parameter), logical fragmentation information
for some indexes might be a little bit off (It does not matter if the index
is large; the difference will show up if the index is small).
The problem is fixed in SQL Server 2000 SP4 (which is in Beta stage now).
2. I have no clue on this. Remember, index could be dropped between you
call DBCC SHOWCONTIG and when you use DBCC INDEXDEFRAG. That might be the
problem you see.
Bonus 1: Agree with Tibor's reply, Logical Fragmentation, with one
correction: for backward compatiable, Scan Density will still be supported
in DBCC SHOWCONTIG when SQL Server 2005 ships. (DBCC SHOWCONTIG will be
deprecated and replaced by a dynamic managed view - checkout SQL Server 2005
Beta 2 for more details)
Hope this helps.
Stephen Jiang
Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Gordon" <Gordon@.discussions.microsoft.com> wrote in message
news:D90B5D30-BAA3-4114-8A51-48ECF2DD714D@.microsoft.com...
> I am trying to resolve database fragmentation and I am having two issues
with
> the DBCC SHOWCONTIG command.
> 1. Running DBCC SHOWCONTIG WITH FAST, ALL_INDEXES, TABLERESULTS shows
> different values for logical fragmentation then running DBCC
> SHOWCONTIG(<tablename>) WITH FAST, ALL_INDEXES, TABLERESULTS. Which value
> should be considered as correct?
> 2. For some tables DBCC SHOWCONTIG(<tablename>) WITH FAST, ALL_INDEXES,
> TABLERESULTS shows results for indexes that do not appear in the QA Object
> Browser or the EM Manage Indexes/Relationship window. Consequently,
running
> DBCC INDEXDEFRAG on those indexes results in an error on Parameter 3, the
> index name. What is going on here?
> BONUS QUESTION:
> 1. Which is a more accurate reflection of fragmentation: Logical
> Fragmentation or Scan Density?

Issues with DBCC SHOWCONTIG

I am trying to resolve database fragmentation and I am having two issues with
the DBCC SHOWCONTIG command.
1. Running DBCC SHOWCONTIG WITH FAST, ALL_INDEXES, TABLERESULTS shows
different values for logical fragmentation then running DBCC
SHOWCONTIG(<tablename>) WITH FAST, ALL_INDEXES, TABLERESULTS. Which value
should be considered as correct?
2. For some tables DBCC SHOWCONTIG(<tablename>) WITH FAST, ALL_INDEXES,
TABLERESULTS shows results for indexes that do not appear in the QA Object
Browser or the EM Manage Indexes/Relationship window. Consequently, running
DBCC INDEXDEFRAG on those indexes results in an error on Parameter 3, the
index name. What is going on here?
BONUS QUESTION:
1. Which is a more accurate reflection of fragmentation: Logical
Fragmentation or Scan Density?1) Can you elaborate, or show an example?
2) What indexid? Remember that 0 is the datapages for a table, and that is not an index.
3) Logical Fragmentation. Scan Density will be (as I remember) removed from next version as it will
show bogus values of the filegroup have more than one file.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Gordon" <Gordon@.discussions.microsoft.com> wrote in message
news:D90B5D30-BAA3-4114-8A51-48ECF2DD714D@.microsoft.com...
>I am trying to resolve database fragmentation and I am having two issues with
> the DBCC SHOWCONTIG command.
> 1. Running DBCC SHOWCONTIG WITH FAST, ALL_INDEXES, TABLERESULTS shows
> different values for logical fragmentation then running DBCC
> SHOWCONTIG(<tablename>) WITH FAST, ALL_INDEXES, TABLERESULTS. Which value
> should be considered as correct?
> 2. For some tables DBCC SHOWCONTIG(<tablename>) WITH FAST, ALL_INDEXES,
> TABLERESULTS shows results for indexes that do not appear in the QA Object
> Browser or the EM Manage Indexes/Relationship window. Consequently, running
> DBCC INDEXDEFRAG on those indexes results in an error on Parameter 3, the
> index name. What is going on here?
> BONUS QUESTION:
> 1. Which is a more accurate reflection of fragmentation: Logical
> Fragmentation or Scan Density?|||1. This is known bug in SQL Server 2000. If you specify ALL_INDEXES or all
tables (by not passing in any parameter), logical fragmentation information
for some indexes might be a little bit off (It does not matter if the index
is large; the difference will show up if the index is small).
The problem is fixed in SQL Server 2000 SP4 (which is in Beta stage now).
2. I have no clue on this. Remember, index could be dropped between you
call DBCC SHOWCONTIG and when you use DBCC INDEXDEFRAG. That might be the
problem you see.
Bonus 1: Agree with Tibor's reply, Logical Fragmentation, with one
correction: for backward compatiable, Scan Density will still be supported
in DBCC SHOWCONTIG when SQL Server 2005 ships. (DBCC SHOWCONTIG will be
deprecated and replaced by a dynamic managed view - checkout SQL Server 2005
Beta 2 for more details)
Hope this helps.
--
Stephen Jiang
Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Gordon" <Gordon@.discussions.microsoft.com> wrote in message
news:D90B5D30-BAA3-4114-8A51-48ECF2DD714D@.microsoft.com...
> I am trying to resolve database fragmentation and I am having two issues
with
> the DBCC SHOWCONTIG command.
> 1. Running DBCC SHOWCONTIG WITH FAST, ALL_INDEXES, TABLERESULTS shows
> different values for logical fragmentation then running DBCC
> SHOWCONTIG(<tablename>) WITH FAST, ALL_INDEXES, TABLERESULTS. Which value
> should be considered as correct?
> 2. For some tables DBCC SHOWCONTIG(<tablename>) WITH FAST, ALL_INDEXES,
> TABLERESULTS shows results for indexes that do not appear in the QA Object
> Browser or the EM Manage Indexes/Relationship window. Consequently,
running
> DBCC INDEXDEFRAG on those indexes results in an error on Parameter 3, the
> index name. What is going on here?
> BONUS QUESTION:
> 1. Which is a more accurate reflection of fragmentation: Logical
> Fragmentation or Scan Density?

ISSUE: After the SQL Server restart, Login failed for user 'XXX\Administrator'

Hello,
I've a problem, When I Execute the following COMMAND in a batch
command(.bat)
NET STOP MSSQLSERVER
NET START MSSQLSERVER
MY_DATABASE APPLICATION
I GOT Login failed for user 'XXX\Administrator' Exception, but when
after the SERVER started long time, MY_DATABASE APPLICATION will not
get the exception
My COnnection string is "Trusted_Connection=Yes;Integrated
security=SSPI;data source=(local);initial catalog=xxx"
Is there any help that can help me to get rid of it?
MSSQL SERVER 2005I've noticed that SQL Server 2005 takes longer to start. I suggest adding a
delay after starting SQL
Server and before starting your application. Or, modify your application cod
e so it has some
re-tries with some wait in between.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"zzppallas" <zzppallas@.gmail.com> wrote in message
news:1140253035.885476.318630@.g43g2000cwa.googlegroups.com...
> Hello,
> I've a problem, When I Execute the following COMMAND in a batch
> command(.bat)
> NET STOP MSSQLSERVER
> NET START MSSQLSERVER
> MY_DATABASE APPLICATION
> I GOT Login failed for user 'XXX\Administrator' Exception, but when
> after the SERVER started long time, MY_DATABASE APPLICATION will not
> get the exception
> My COnnection string is "Trusted_Connection=Yes;Integrated
> security=SSPI;data source=(local);initial catalog=xxx"
> Is there any help that can help me to get rid of it?
> MSSQL SERVER 2005
>|||Actually this is a weird explanation. If you start the server, are you
getting at this point the exception or at connection time with your
client application. If you are getting the exception at service start,
it seems that your account is a) not prvililedged any more or b) the
password you enetered in the service manager has changed.
If you are getting the exception in the connection via a application
you have to make sure that the user you are connecting with has the
appropiate right on the database. per default administrator are
priviledged on the server, but they can be removed from the autorized
list.
HTH, jens Suessmeyer.

ISSUE: After the SQL Server restart, Login failed for user 'XXX\Administrator'

Hello,
I've a problem, When I Execute the following COMMAND in a batch
command(.bat)
NET STOP MSSQLSERVER
NET START MSSQLSERVER
MY_DATABASE APPLICATION
I GOT Login failed for user 'XXX\Administrator' Exception, but when
after the SERVER started long time, MY_DATABASE APPLICATION will not
get the exception
My COnnection string is "Trusted_Connection=Yes;Integrated
security=SSPI;data source=(local);initial catalog=xxx"
Is there any help that can help me to get rid of it?
MSSQL SERVER 2005I've noticed that SQL Server 2005 takes longer to start. I suggest adding a delay after starting SQL
Server and before starting your application. Or, modify your application code so it has some
re-tries with some wait in between.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"zzppallas" <zzppallas@.gmail.com> wrote in message
news:1140253035.885476.318630@.g43g2000cwa.googlegroups.com...
> Hello,
> I've a problem, When I Execute the following COMMAND in a batch
> command(.bat)
> NET STOP MSSQLSERVER
> NET START MSSQLSERVER
> MY_DATABASE APPLICATION
> I GOT Login failed for user 'XXX\Administrator' Exception, but when
> after the SERVER started long time, MY_DATABASE APPLICATION will not
> get the exception
> My COnnection string is "Trusted_Connection=Yes;Integrated
> security=SSPI;data source=(local);initial catalog=xxx"
> Is there any help that can help me to get rid of it?
> MSSQL SERVER 2005
>|||Actually this is a weird explanation. If you start the server, are you
getting at this point the exception or at connection time with your
client application. If you are getting the exception at service start,
it seems that your account is a) not prvililedged any more or b) the
password you enetered in the service manager has changed.
If you are getting the exception in the connection via a application
you have to make sure that the user you are connecting with has the
appropiate right on the database. per default administrator are
priviledged on the server, but they can be removed from the autorized
list.
HTH, jens Suessmeyer.

Issue with SqlCeParameter (SqlCE 3.1)

Hi,

I have a simple query as follows:

SELECT COUNT(ID) FROM AI_DTREE DT WHERE PARENT =@.pPID AND

CARTRIDGE_ID = @.pCID AND COMMAND =@.pCMD AND OBJECT =@.pObj

Where @.pPID=16700130,@.pCID=43000000,@.pCMD=”=”, and

@.pObj=”the cecum, identified by appendiceal orifice & IC valve”

The filed OBJECT in AI_DTREE is of nvarchar(30)(of course the length of @.pObj is more than 30 in my current query).

I have build the SqlCeCommand sccmd object with the above sql text and the parameters.

returnval = sccmd.ExecuteScalar();

When I execute the above statement I am getting the following error:

ex.Message = "@.pObj : String truncation: max=30, len=55, value='the cecum, identified by appendiceal orifice & IC valve'."

But when I execute the same in Sql Server Management Studio against SqlCE db, it works fine and the result returnval =0.

How to overcome this SqlCeParameter issue?, for me it is difficult to messure the length of the filed before I exeuting the command.

Thanks

G Sreenaiah

You must ensure that the length of @.pObj is no more than 30 chars, or make the field bigger!|||

Hi Erik,
Thanks for your prompt reply.

I am just executing the above command from my C# code. If I want to ensure the length of the parameter is less than the length of field, then I should go for one more database hit to fetch field’s length first.

This is not the way happening with i) OleDbCommand, OleDbParameter, ii) OracleParameter, OracleCommand

Please let me know if anybody else has an idea how this can be resolved with workout having one more db hit to fetch fields’ length.

Why it is happening only with SqlCeCommand, SqlCeParameter? why not with OracleParameter, OracleCommand ?

Thanks
G Sreenaiah

Issue with restoring master db

Hi all,

I am trying to restore my master database to a new server in sql 2000. I have started sql in single user mode from the command line and connected to enterprise manager and attempted to do the restore. I also tried query analyzer and used the restore database from disk ='file path of backup' used the move option with the correct file path and on both counts received the same error message...

Error: 9001, Severity: 21, State: 1.
Error: 3151, Severity: 21, State: 0.

Any ideas? The connection is then broken (which I would expect anyway as restoring master shuts down sql but, I checked and it has not restored the db)

Thanks!This article may be of some help:

http://www.dbarecovery.com/restoremasterdb.html|||lmao @. your sig ... rdjabarov ... nice one !!!|||That is mostly what I have done (did not need to do the rebuild part) When I try to do the restore it does not complete but generates the error messages I listed in my first posting. I have done this before and it worked but, for some reason it is not allowing me to do the restore but, is breaking the connection.

Any other suggestions?|||I did some more checking - could there be an issue with one server having a windows service pack 3 and the other server having windows service pack 4?

Issue with OLE DB Command while writing to DB2 database

Hi,

I have created a package which uses the OLE DB Command as the target where I write the sql command to insert data into the table. The issue which I am facing is, while at the OLE DB COmmand , the package fails. I notices that it is not able to get the input columns which are mapped to the target columns.

The same package works fine when the target is on Oracle database or a SQL Server database.

For DB2, i have tried using the Microsoft OLE DB Driver for Db2, as the IBM DB2 Driver doesnt work for insert properly.

Any suggestion regarding this would be really helpful.

Thanks,

Manish

Some thoughts on this:

1. It is better to use the OLE DB Destination to insert data into a table instead of the OLE DB Command with an insert statement. You should give that a try.

2. Are you using the Microsoft OLE DB provider for DB2 from our SQL Server 2005 Feature Pack? That's the provider that's been tested with SSIS.

3. I don't understand what you mean by not able to get input columns. What are the error messages when the package fails?

|||

Yes, I am using the Microsoft DB2 drivers only. Using the OLE DB Destination, it worls only for insert. But, I wanted to simulate a scenario, where we do, both insert and update to the target table. So, we used OLE DB Command, and wrote the insert/update query.

The same works fine on Oracle and SQL Server target database. But on DB2, it doesnt work, as in the OLE DB command, we need to map the input columns to the parameters value. The drivers are not able to fetch the input data, and everytime, it insert NULL as it is not able to get the input values.

Let me know if you need any more information on the same.

ISSUE WHILE CONNECTING TO ORACLE SOURCE WITH 64 BIT processor SQL SERVER SSIS

Hello All,

I have a unique problem while connecting to oracle source with a 64 bit processor. I can connect to the oracle from the command prompt in the 64 bit processor but not from SSIS.

The acutal problem is, when check the properties of the connection manager and provide a provider for oracle, and then provide username and password and click on test connection. I get the following error:

"Test Connection failed because of an error in initializing provider.ORA-06413: Connection not open"

Regards,

Raju

Hello All,

The above package is running fine with sql server 32 bit development but not running in 64 bit production server.

The oracle client that has been installed on the production server is a 32 bit, because our source database is an oracle 32 bit not 64 bit which is residing in another server.

Hope this additional information will help you people get it resolved.

Regards,

Raju

|||

Hi Raju,

If you want to connect to Oracle using a 32bit driver, then you should execute the SSIS package using the 32bit version of DTEXEC.

Regards,

Christian

isqlw and std out

When using the ISQLW command line I woul like to output to screen rather
than file. How can I do this ?
Hi GMG,
Thanks for your post.
From you descriptions, I understood your ISQLW command line will
automatically output to a file instead of screen.
However, ISQLW will launch Query Analyzer and osql is the command line for
SQL Server. Both QA and osql will defaultly output to screen instead of
files. Would you please show me more detailed steps about your scenario?
More detailed informaiton, I believe, will make us closer to the resolution.
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
|||I am automating a number of scripts to be run via ISQL, but I have found
particular ANSI characters are corrupted. When running ISQLW via the command
line everything is fine, but it outputs to file which is not ideal I would
the output summary to display to the DOS screen.
"Michael Cheng [MSFT]" <v-mingqc@.online.microsoft.com> wrote in message
news:WjTmruQrFHA.3532@.TK2MSFTNGXA01.phx.gbl...
> Hi GMG,
> Thanks for your post.
> From you descriptions, I understood your ISQLW command line will
> automatically output to a file instead of screen.
> However, ISQLW will launch Query Analyzer and osql is the command line for
> SQL Server. Both QA and osql will defaultly output to screen instead of
> files. Would you please show me more detailed steps about your scenario?
> More detailed informaiton, I believe, will make us closer to the
resolution.
> Thank you for your patience and cooperation. If you have any questions or
> concerns, don't hesitate to let me know. We are always here to be of
> assistance!
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ================================================== ===
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
|||What exactly do you mean by "ANSI characters are corrupted"? Have you tried
using oSql instead? oSql replaces iSql in 2000.
Andrew J. Kelly SQL MVP
"GMG" <nospam@.nospam.com> wrote in message
news:%23rbzxafrFHA.2228@.TK2MSFTNGP10.phx.gbl...
>I am automating a number of scripts to be run via ISQL, but I have found
> particular ANSI characters are corrupted. When running ISQLW via the
> command
> line everything is fine, but it outputs to file which is not ideal I would
> the output summary to display to the DOS screen.
> "Michael Cheng [MSFT]" <v-mingqc@.online.microsoft.com> wrote in message
> news:WjTmruQrFHA.3532@.TK2MSFTNGXA01.phx.gbl...
> resolution.
> rights.
>
|||Hi GMG,
Thanks for the follow up.
I am also a little bit confused with "ANSI characters are corrupted". I
understood it was some characters will not be displayed correctly. If I
have misunderstood your concern, please feel free to point it out.
I am afraid we may not have a better way doing this directly. If you want
to get the output from file, you could generate a batch and output the file
after execute the isqlw in your scenario. You could use DOS command type
like this: type [driver:][filepath][filename]
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
|||I am sorry I was not very clear in my explanation.
I was able to solve the issue by going to 'Client Network Utility' - 'DB
Library Options' tab and disabling 'Automatic ANSI to OEM conversion'.
The ANSI characters are now correct when using ISQL to run my scripts.
"Michael Cheng [MSFT]" <v-mingqc@.online.microsoft.com> wrote in message
news:n%23tA9MesFHA.3604@.TK2MSFTNGXA01.phx.gbl...
> Hi GMG,
> Thanks for the follow up.
> I am also a little bit confused with "ANSI characters are corrupted". I
> understood it was some characters will not be displayed correctly. If I
> have misunderstood your concern, please feel free to point it out.
> I am afraid we may not have a better way doing this directly. If you want
> to get the output from file, you could generate a batch and output the
file
> after execute the isqlw in your scenario. You could use DOS command type
> like this: type [driver:][filepath][filename]
> Thank you for your patience and cooperation. If you have any questions or
> concerns, don't hesitate to let me know. We are always here to be of
> assistance!
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ================================================== ===
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
|||Hi GMG,
Thanks for the update and it's great to know it was resolved.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.

isql query output issue

Hi
I am planning to get a report out from isql using command
c:\> isql -Usa -P -d master -i c:\l.sql -o c:\op.lst
c:\> type l.sql
select * from sysobjects;
In op.lst I see 3 blank lines for every line of the output. How to avoid this.
I tried by creating a job to avoid this but there I found the first line of
the output says this
Job 'TSQL_Job_1' : Step 1, 'Step 1' : Began Executing 2005-02-14 14:54:16
---------
Please share with me how to avoid this unncessary data in report.
Thanks
MangeshHi
Thee are 3 blank lines, but that is the spacing for the column 'name'. It is
of datatype sysname, char(128).
Regards
Mike
"Mangesh Deshpande" wrote:
> Hi
> I am planning to get a report out from isql using command
> c:\> isql -Usa -P -d master -i c:\l.sql -o c:\op.lst
> c:\> type l.sql
> select * from sysobjects;
> In op.lst I see 3 blank lines for every line of the output. How to avoid this.
> I tried by creating a job to avoid this but there I found the first line of
> the output says this
>
> Job 'TSQL_Job_1' : Step 1, 'Step 1' : Began Executing 2005-02-14 14:54:16
>
>
> ---------
>
> Please share with me how to avoid this unncessary data in report.
> Thanks
> Mangesh|||Hi
Thanks for the reply. How do we get rid of this space. Is there a way to
this?
"Mangesh Deshpande" wrote:
> Hi
> I am planning to get a report out from isql using command
> c:\> isql -Usa -P -d master -i c:\l.sql -o c:\op.lst
> c:\> type l.sql
> select * from sysobjects;
> In op.lst I see 3 blank lines for every line of the output. How to avoid this.
> I tried by creating a job to avoid this but there I found the first line of
> the output says this
>
> Job 'TSQL_Job_1' : Step 1, 'Step 1' : Began Executing 2005-02-14 14:54:16
>
>
> ---------
>
> Please share with me how to avoid this unncessary data in report.
> Thanks
> Mangesh|||Hi
Don't use SELECT *, it is a bad practice. Rather specify each column.
RTRIM will trim trailing spaces.
SELECT RTRIM([Name]) AS MyName, ........
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Mangesh Deshpande" <MangeshDeshpande@.discussions.microsoft.com> wrote in
message news:AA4199DE-C15A-4507-BB15-C1BD1D835F3B@.microsoft.com...
> Hi
> Thanks for the reply. How do we get rid of this space. Is there a way to
> this?
> "Mangesh Deshpande" wrote:
> > Hi
> >
> > I am planning to get a report out from isql using command
> > c:\> isql -Usa -P -d master -i c:\l.sql -o c:\op.lst
> > c:\> type l.sql
> > select * from sysobjects;
> >
> > In op.lst I see 3 blank lines for every line of the output. How to avoid
this.
> >
> > I tried by creating a job to avoid this but there I found the first line
of
> > the output says this
> >
> >
> > Job 'TSQL_Job_1' : Step 1, 'Step 1' : Began Executing 2005-02-14
14:54:16
> >
> >
> >
> >
> ----
----
---
> >
> >
> > Please share with me how to avoid this unncessary data in report.
> >
> > Thanks
> > Mangesh