Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2003
    Posts
    15

    Unanswered: 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.
    Thanks in advance.
    ChrisR

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    it might be that the service account doesn't have permission to the location that the file is at. copy it to the server
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Oct 2003
    Posts
    15
    The file doesnt exist yet, Im trying to create it. I've also tried to create it locally, and that didn't work.
    Thanks in advance.
    ChrisR

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I like to create my own

    Code:
    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)+'SQLCHAR'+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)+CHAR(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)+'SQLCHAR'+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)+CHAR(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\*.*'
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Oct 2003
    Posts
    15
    Your a crazy mo fo.
    Thanks in advance.
    ChrisR

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •