Problems creating format file.

sql2k sp4

Howdy all. I havent used format files inside BCP in several years and am having trouble creating one now.

declare @.exec varchar(1026)
set @.exec = 'bcp faa_ivr.dbo.primary_informant format -SboxName\instanceName -c -T -f\\destination\FAAIVR\primary_informant_format.txt '
exec master..xp_cmdshell @.exec

output
----------------------------------------------------------------------------
SQLState = 08001, NativeError = 17
Error = [Microsoft][ODBC SQL Server Driver][Shared Memory]SQL Server does not exist or access denied.
SQLState = 01000, NativeError = 2
Warning = [Microsoft][ODBC SQL Server Driver][Shared Memory]ConnectionOpen (Connect()).
NULL

(5 row(s) affected)

I've tried brackets ([])around the box/ instance name. I've tried using the FQDN. I tried the SA account instead of WINNT authentication. All ideas are appreciated.it might be that the service account doesn't have permission to the location that the file is at. copy it to the server|||The file doesnt exist yet, Im trying to create it. I've also tried to create it locally, and that didn't work.|||I like to create my own

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[isp_GenFormatCards]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[isp_GenFormatCards]
GO

CREATE PROC isp_GenFormatCards
AS
DECLARE FormatCard CURSOR FOR
SELECT FORMAT_CARD, TABLE_NAME, TABLE_SCHEMA FROM (
/*
SELECT '--' + TABLE_NAME AS FORMAT_CARD
, TABLE_NAME, null AS COLUMN_NAME, 0 AS SQLGroup, 1 AS RowGrouping
FROM INFORMATION_SCHEMA.Tables
WHERE TABLE_TYPE = 'BASE TABLE'
UNION ALL
*/
SELECT '7.0' AS FORMAT_CARD
, TABLE_NAME, TABLE_SCHEMA, null AS COLUMN_NAME, 1 AS SQLGroup, 1 AS RowGrouping
FROM INFORMATION_SCHEMA.Tables
WHERE TABLE_TYPE = 'BASE TABLE'
UNION ALL
SELECT CONVERT(varchar(5),MAX(ORDINAL_POSITION)) AS FORMAT_CARD
, c.TABLE_NAME, c.TABLE_SCHEMA, null AS COLUMN_NAME, 2 AS SQLGroup, 1 AS RowGrouping
FROM INFORMATION_SCHEMA.Columns c
INNER JOIN INFORMATION_SCHEMA.Tables t
ON c.TABLE_NAME = t.TABLE_NAME
AND c.TABLE_SCHEMA = t.TABLE_SCHEMA
AND TABLE_TYPE = 'BASE TABLE'
GROUP BY c.TABLE_NAME, c.TABLE_SCHEMA
UNION ALL
SELECT CONVERT(varchar(3),ORDINAL_POSITION)+CHAR(9)+'SQLC HAR'+CHAR(9)+'0'+CHAR(9)
+ CONVERT(varchar(5),
CASE WHEN DATA_TYPE IN ('char','varchar','nchar','nvarchar') THEN CHARACTER_MAXIMUM_LENGTH
WHEN DATA_TYPE = 'int' THEN 14
WHEN DATA_TYPE = 'smallint' THEN 7
WHEN DATA_TYPE = 'tinyint' THEN 3
WHEN DATA_TYPE = 'bit' THEN 1
WHEN DATA_TYPE IN ('text','image') THEN 0
ELSE 26
END)
+ CHAR(9)+'""'+CHAR(9)+CONVERT(varchar(3),ORDINAL_POSITION)+CHA R(9)+COLUMN_NAME AS FORMAT_CARD
, c.TABLE_NAME, c.TABLE_SCHEMA, null AS COLUMN_NAME, 3 AS SQLGroup, ORDINAL_POSITION AS RowGrouping
FROM INFORMATION_SCHEMA.Columns c
INNER JOIN INFORMATION_SCHEMA.Tables t
ON c.TABLE_NAME = t.TABLE_NAME
AND c.table_schema = t.table_schema
AND TABLE_TYPE = 'BASE TABLE'
WHERE ORDINAL_POSITION < (SELECT MAX(ORDINAL_POSITION)
FROM INFORMATION_SCHEMA.Columns i
WHERE i.TABLE_NAME = c.TABLE_NAME)
UNION ALL
SELECT CONVERT(varchar(3),ORDINAL_POSITION)+CHAR(9)+'SQLC HAR'+CHAR(9)+'0'+CHAR(9)+CONVERT(VARCHAR(5),
CASE WHEN DATA_TYPE IN ('char','varchar','nchar','nvarchar') THEN CHARACTER_MAXIMUM_LENGTH
WHEN DATA_TYPE = 'int' THEN 14
WHEN DATA_TYPE = 'smallint' THEN 7
WHEN DATA_TYPE = 'tinyint' THEN 3
WHEN DATA_TYPE = 'bit' THEN 1
WHEN DATA_TYPE IN ('text','image') THEN 0
ELSE 26
END)
+ char(9)+'"\r\n"'+char(9)+CONVERT(varchar(3),ORDINAL_POSITION)+CHA R(9)+COLUMN_NAME AS FORMAT_CARD
, c.TABLE_NAME, c.TABLE_SCHEMA, null AS COLUMN_NAME, 4 AS SQLGroup, 1 AS RowGrouping
FROM INFORMATION_SCHEMA.Columns c
INNER JOIN INFORMATION_SCHEMA.Tables t
ON c.TABLE_NAME = t.TABLE_NAME
AND c.TABLE_SCHEMA = t.TABLE_SCHEMA
AND TABLE_TYPE = 'BASE TABLE'
WHERE ORDINAL_POSITION = (SELECT MAX(ORDINAL_POSITION)
FROM INFORMATION_SCHEMA.Columns i
WHERE i.TABLE_NAME = c.TABLE_NAME)
)AS XXX
ORDER BY TABLE_NAME, COLUMN_NAME, SQLGroup, RowGrouping


DECLARE @.Card varchar(200), @.TABLE_NAME sysname, @.TABLE_SCHEMA sysname, @.cmd varchar(200), @.x char(2), @.Command_String varchar(8000)
, @.TABLE_NAME_OLD sysname, @.TABLE_SCHEMA_OLD sysname

SELECT @.x = '> ', @.TABLE_NAME_OLD = '', @.TABLE_SCHEMA_OLD = ''

OPEN FormatCard

FETCH NEXT FROM FormatCard INTO @.Card, @.TABLE_NAME, @.TABLE_SCHEMA

WHILE @.@.FETCH_STATUS = 0
BEGIN
SELECT @.x = '>>'
IF @.TABLE_SCHEMA+@.TABLE_NAME <> @.TABLE_SCHEMA_OLD+@.TABLE_NAME_OLD
BEGIN
SELECT @.TABLE_SCHEMA_OLD = @.TABLE_SCHEMA
, @.TABLE_NAME_OLD = @.TABLE_NAME
, @.x = '> '
END

SET @.cmd = 'echo ' + @.Card + ' '+ @.x +' d:\Data\Tax\Format\'+@.TABLE_SCHEMA+'_'+@.TABLE_NAME +'.fmt'
SET @.Command_string = 'EXEC master..xp_cmdshell ''' + @.cmd + ''', NO_OUTPUT'
PRINT @.Command_String
Exec(@.Command_String)

FETCH NEXT FROM FormatCard INTO @.Card, @.TABLE_NAME, @.TABLE_SCHEMA
END

CLOSE FormatCard
DEALLOCATE FormatCard

GO

--master..xp_cmdshell 'dir d:\Data\Tax\Format\*.*'|||Your a crazy mo fo.

Problems creating Error File when using Bulk Insert or BCP from xp_cmdshell.

BCP thru xp_cmdshell from stored procedure:

EXEC sp_configure 'show advanced options', 1;

RECONFIGURE

EXEC sp_configure 'xp_cmdshell', 1;

RECONFIGURE

EXEC xp_cmdshell 'bcp database.dbo.table in c:\scheduled.csv -S SERVER\SQLEXPRESS -T -t, -r\n -c -e "error.txt"';

This is returning the following error code. I even tried placing the command in a seperate command file and calling that with no success. If I run this from the command line the error file generation does work.

=================================================================

SQLState = HY000, NativeError = 0

Error = [Microsoft][SQL Native Client]Unable to open BCP error-file

=================================================================

Error message when using BULK INSERT as follows:

BULK INSERT database.dbo.table from 'c:\unscheduled.csv' with

(FIELDTERMINATOR = ',', ERRORFILE = 'c:\error.txt');

Returns the following error message:

=================================================================

Msg 4861, Level 16, State 1, Procedure pro_cedure, Line 9

Cannot bulk load because the file "c:\error.txt" could not be opened. Operating system error code 80(The file exists.).

Msg 4861, Level 16, State 1, Procedure pro_cedure, Line 9

Cannot bulk load because the file "c:\error.txt.Error.Txt" could not be opened. Operating system error code 80(The file exists.).

=================================================================

The Bulk Insert actually creates a empty error.txt file (0kb) and never preforms the insert, I can not find any examples of anyone using the -ERRORFILE switch on BULK INSERT. Prolly some default security setting to allow file creation/modification I am missing. Anyone help me out? Thanks.

EDIT: SQL SERVER EXPRESS 2005 - WINXP PRO SP2

I had this error too. It looks like a bug as BULK INSERT works fine without the -ERRORFILE option. It seems that the execution of the BULK INSERT first creates the file without closing it which results in the error message afterwards. As Workaround just don't use the option

Nobsay

Problem: Stored Procedures not completing from Web Application

I have several stored procedures that run fine from my SQL Server
database (via the exec command.), though when I call these procedures
from my web application, they do not complete. I have other
procedures that in fact do run fine through my web application though,
so I do not believe its a front-end problem. The procedures only take
about 30 seconds to run from the back-end, so I know its not a time
out issue as well. Does anyone have any ideas?

Thanks in advance."Mike J" <mjewett_2000@.yahoo.com> wrote in message
news:1a9d60fa.0404261152.322ed838@.posting.google.c om...
> I have several stored procedures that run fine from my SQL Server
> database (via the exec command.), though when I call these procedures
> from my web application, they do not complete. I have other
> procedures that in fact do run fine through my web application though,
> so I do not believe its a front-end problem. The procedures only take
> about 30 seconds to run from the back-end, so I know its not a time
> out issue as well. Does anyone have any ideas?
> Thanks in advance.

Can you clarify what you mean by "does not complete"? Do you get an error
message? What version of MSSQL? What's the web platform, eg. ASP, PHP etc.?
If it works fine from Query Analyzer then that suggests an issue on the web
side, but without more information it's hard to say.

Simon|||Mike J (mjewett_2000@.yahoo.com) writes:
> I have several stored procedures that run fine from my SQL Server
> database (via the exec command.), though when I call these procedures
> from my web application, they do not complete. I have other
> procedures that in fact do run fine through my web application though,
> so I do not believe its a front-end problem. The procedures only take
> about 30 seconds to run from the back-end, so I know its not a time
> out issue as well. Does anyone have any ideas?

30 seconds is the default time-out, so timeout problems cannot be ruled
out completely. Do you have proper error handling in your web app?

There are a couple of possible reasons for this, but since you provided
very little information about your code, I can only give some general
comments.

Do you use indexed views or indexes on computed columns? In such case,
you need to issue SET ARITHABORT ON when you connect from your web app
(or make this default for the database with ALTER DATABASE). This setting
is on by default when you run from Query Analyzer.

Even if you don't use indexed views or indexed computed columns, SET
ARITHABORT ON, can still be useful, as you now will get the same plan
as Query Analyzer does.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"Simon Hayes" <sql@.hayes.ch> wrote in message news:<408d7223$1_3@.news.bluewin.ch>...
> "Mike J" <mjewett_2000@.yahoo.com> wrote in message
> news:1a9d60fa.0404261152.322ed838@.posting.google.c om...
> > I have several stored procedures that run fine from my SQL Server
> > database (via the exec command.), though when I call these procedures
> > from my web application, they do not complete. I have other
> > procedures that in fact do run fine through my web application though,
> > so I do not believe its a front-end problem. The procedures only take
> > about 30 seconds to run from the back-end, so I know its not a time
> > out issue as well. Does anyone have any ideas?
> > Thanks in advance.
> Can you clarify what you mean by "does not complete"? Do you get an error
> message? What version of MSSQL? What's the web platform, eg. ASP, PHP etc.?
> If it works fine from Query Analyzer then that suggests an issue on the web
> side, but without more information it's hard to say.
> Simon

Simon,

Here's some more elaboration. By "does not complete" I mean that the
procedures simply never finish running. They generate no error
messages, and do not lock any objects -- they just dont finish. I
verify this by manually running the procedures in query analyzer
(where they finish quickly) and viewing the results. The strange
thing is that we use over 50 stored procedures, and almost all of them
work -- from both the web application and query analyzer. The couple
procedures that are "not finishing" are not any more complex than the
others.

The web platform we are using is Microsoft asp .NET. We are using SQL
Server 2000 as well.
Any ideas? Thanks.|||Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns94D7EF10D40A3Yazorman@.127.0.0.1>...
> Mike J (mjewett_2000@.yahoo.com) writes:
> > I have several stored procedures that run fine from my SQL Server
> > database (via the exec command.), though when I call these procedures
> > from my web application, they do not complete. I have other
> > procedures that in fact do run fine through my web application though,
> > so I do not believe its a front-end problem. The procedures only take
> > about 30 seconds to run from the back-end, so I know its not a time
> > out issue as well. Does anyone have any ideas?
> 30 seconds is the default time-out, so timeout problems cannot be ruled
> out completely. Do you have proper error handling in your web app?
> There are a couple of possible reasons for this, but since you provided
> very little information about your code, I can only give some general
> comments.
> Do you use indexed views or indexes on computed columns? In such case,
> you need to issue SET ARITHABORT ON when you connect from your web app
> (or make this default for the database with ALTER DATABASE). This setting
> is on by default when you run from Query Analyzer.
> Even if you don't use indexed views or indexed computed columns, SET
> ARITHABORT ON, can still be useful, as you now will get the same plan
> as Query Analyzer does.

Erland, yes, we have proper error handling in our web application. We
in fact run many (around 50) stored procedures from our web
application and they complete just fine. The couple procedures that
do not run from the application are not any more complex than the
others.

To elaborate some more on our system, we are using Microsoft .NET, SQL
Server 2000. I have run many tests in query analyzer on our database
server with the procedures in question. They complete accurately and
quickly every time, with no errors. Its only when I call them from
the web app where do not finish. We do not use any indexed views or
computed columns, though I will try to ARITHABOR ON property anyhow.
Any other ideas?
Thanks.|||Mike J (mjewett_2000@.yahoo.com) writes:
> To elaborate some more on our system, we are using Microsoft .NET, SQL
> Server 2000. I have run many tests in query analyzer on our database
> server with the procedures in question. They complete accurately and
> quickly every time, with no errors. Its only when I call them from
> the web app where do not finish. We do not use any indexed views or
> computed columns, though I will try to ARITHABOR ON property anyhow.
> Any other ideas?

With that miniscule of information, no. Well, while you ruled out blocking
in another posting, one possibility is that you manage to block yourself
in the app. When you have a procedure which does not complete, execute
sp_who, and see if any process has a non-zero value in the Blk column.

If there is no blocking, use the Profiler to see where the process gets
stuck.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||I had experienced issues with a store proc executing very quickly in Query Analyzer, but not from my ASP app against a development database. I noticed recently the same issue with the procedure not executing quickly in either QA or the web app.

After checking my table indexes on the dev database I found one joined table that had no index for the fields I was joining on. The index existed in the production database. Once adding that index. the procedure executed in < 1 second in QA and only a couple seconds from the web app.

Hope this helps anyone who's frustrated with similar issues.

Dave

Problem: Stored Procedures not completing from Web Application

I have several stored procedures that run fine from my SQL Server
database (via the exec command.), though when I call these procedures
from my web application, they do not complete. I have other
procedures that in fact do run fine through my web application though,
so I do not believe its a front-end problem. The procedures only take
about 30 seconds to run from the back-end, so I know its not a time
out issue as well. Does anyone have any ideas?

Thanks in advance."Mike J" <mjewett_2000@.yahoo.com> wrote in message
news:1a9d60fa.0404261152.322ed838@.posting.google.c om...
> I have several stored procedures that run fine from my SQL Server
> database (via the exec command.), though when I call these procedures
> from my web application, they do not complete. I have other
> procedures that in fact do run fine through my web application though,
> so I do not believe its a front-end problem. The procedures only take
> about 30 seconds to run from the back-end, so I know its not a time
> out issue as well. Does anyone have any ideas?
> Thanks in advance.

Can you clarify what you mean by "does not complete"? Do you get an error
message? What version of MSSQL? What's the web platform, eg. ASP, PHP etc.?
If it works fine from Query Analyzer then that suggests an issue on the web
side, but without more information it's hard to say.

Simon|||Mike J (mjewett_2000@.yahoo.com) writes:
> I have several stored procedures that run fine from my SQL Server
> database (via the exec command.), though when I call these procedures
> from my web application, they do not complete. I have other
> procedures that in fact do run fine through my web application though,
> so I do not believe its a front-end problem. The procedures only take
> about 30 seconds to run from the back-end, so I know its not a time
> out issue as well. Does anyone have any ideas?

30 seconds is the default time-out, so timeout problems cannot be ruled
out completely. Do you have proper error handling in your web app?

There are a couple of possible reasons for this, but since you provided
very little information about your code, I can only give some general
comments.

Do you use indexed views or indexes on computed columns? In such case,
you need to issue SET ARITHABORT ON when you connect from your web app
(or make this default for the database with ALTER DATABASE). This setting
is on by default when you run from Query Analyzer.

Even if you don't use indexed views or indexed computed columns, SET
ARITHABORT ON, can still be useful, as you now will get the same plan
as Query Analyzer does.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"Simon Hayes" <sql@.hayes.ch> wrote in message news:<408d7223$1_3@.news.bluewin.ch>...
> "Mike J" <mjewett_2000@.yahoo.com> wrote in message
> news:1a9d60fa.0404261152.322ed838@.posting.google.c om...
> > I have several stored procedures that run fine from my SQL Server
> > database (via the exec command.), though when I call these procedures
> > from my web application, they do not complete. I have other
> > procedures that in fact do run fine through my web application though,
> > so I do not believe its a front-end problem. The procedures only take
> > about 30 seconds to run from the back-end, so I know its not a time
> > out issue as well. Does anyone have any ideas?
> > Thanks in advance.
> Can you clarify what you mean by "does not complete"? Do you get an error
> message? What version of MSSQL? What's the web platform, eg. ASP, PHP etc.?
> If it works fine from Query Analyzer then that suggests an issue on the web
> side, but without more information it's hard to say.
> Simon

Simon,

Here's some more elaboration. By "does not complete" I mean that the
procedures simply never finish running. They generate no error
messages, and do not lock any objects -- they just dont finish. I
verify this by manually running the procedures in query analyzer
(where they finish quickly) and viewing the results. The strange
thing is that we use over 50 stored procedures, and almost all of them
work -- from both the web application and query analyzer. The couple
procedures that are "not finishing" are not any more complex than the
others.

The web platform we are using is Microsoft asp .NET. We are using SQL
Server 2000 as well.
Any ideas? Thanks.|||Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns94D7EF10D40A3Yazorman@.127.0.0.1>...
> Mike J (mjewett_2000@.yahoo.com) writes:
> > I have several stored procedures that run fine from my SQL Server
> > database (via the exec command.), though when I call these procedures
> > from my web application, they do not complete. I have other
> > procedures that in fact do run fine through my web application though,
> > so I do not believe its a front-end problem. The procedures only take
> > about 30 seconds to run from the back-end, so I know its not a time
> > out issue as well. Does anyone have any ideas?
> 30 seconds is the default time-out, so timeout problems cannot be ruled
> out completely. Do you have proper error handling in your web app?
> There are a couple of possible reasons for this, but since you provided
> very little information about your code, I can only give some general
> comments.
> Do you use indexed views or indexes on computed columns? In such case,
> you need to issue SET ARITHABORT ON when you connect from your web app
> (or make this default for the database with ALTER DATABASE). This setting
> is on by default when you run from Query Analyzer.
> Even if you don't use indexed views or indexed computed columns, SET
> ARITHABORT ON, can still be useful, as you now will get the same plan
> as Query Analyzer does.

Erland, yes, we have proper error handling in our web application. We
in fact run many (around 50) stored procedures from our web
application and they complete just fine. The couple procedures that
do not run from the application are not any more complex than the
others.

To elaborate some more on our system, we are using Microsoft .NET, SQL
Server 2000. I have run many tests in query analyzer on our database
server with the procedures in question. They complete accurately and
quickly every time, with no errors. Its only when I call them from
the web app where do not finish. We do not use any indexed views or
computed columns, though I will try to ARITHABOR ON property anyhow.
Any other ideas?
Thanks.|||Mike J (mjewett_2000@.yahoo.com) writes:
> To elaborate some more on our system, we are using Microsoft .NET, SQL
> Server 2000. I have run many tests in query analyzer on our database
> server with the procedures in question. They complete accurately and
> quickly every time, with no errors. Its only when I call them from
> the web app where do not finish. We do not use any indexed views or
> computed columns, though I will try to ARITHABOR ON property anyhow.
> Any other ideas?

With that miniscule of information, no. Well, while you ruled out blocking
in another posting, one possibility is that you manage to block yourself
in the app. When you have a procedure which does not complete, execute
sp_who, and see if any process has a non-zero value in the Blk column.

If there is no blocking, use the Profiler to see where the process gets
stuck.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp