Don't forget that now that it is in the tempdb table you can script out a field descriptor or a CREATE table statement. Use that for a permanent import table elsewhere.

USE tempdb
GO
SELECT *
INTO MyEXCELSheet
FROM OPENDATASOURCE(
'Microsoft.Jet.OLEDB.4.0',
'Data Source="E:\atest.xls";Extended properties=Excel 5.0'
)...Sheet1$
--
-- Now use Query Analyzer to script out the table in tempdb. Here's a sample script.
--
CREATE TABLE [dbo].[MyEXCELSheet] (
[CustomerID] [nvarchar] (255) NULL ,
[EmployeeID] [float] NULL ,
[Freight] [float] NULL ,
[OrderDate] [datetime] NULL ,
[OrderID] [float] NULL ,
[RequiredDate] [datetime] NULL ,
[ShipAddress] [nvarchar] (255) NULL ,
[ShipCity] [nvarchar] (255) NULL ,
[ShipCountry] [nvarchar] (255) NULL ,
[ShipName] [nvarchar] (255) NULL ,
[ShippedDate] [datetime] NULL ,
[ShipPostalCode] [float] NULL ,
[ShipRegion] [nvarchar] (255) NULL ,
[ShipVia] [float] NULL
) ON [PRIMARY]
END
GO

Posted in: EXCEL , SQL  Tags:

Actions: E-mail | Permalink | Comments (0) | Comment RSS | E-mail | Kick it! | DZone it! | del.icio.us

Easy linked server setup for a one hop query.

PC (A) to SERVER (B) will work.
PC (A) client tools to SERVER (B) and four-part queries from SERVER (B) connection to SERVER (C) will not work without some kind of delegation.

See "How to set up a Kerberos Authentication Scenario with SQL Server Linked Servers" and this "Allow a computer to be trusted for delegation for specific services"

-- Add a linked server
EXEC sp_addlinkedserver 
@server = N'SQLDEV',
@srvproduct  = N'SQL Server'
GO
-- And some options
exec sp_serveroption 
@server = N'SQLDEV', 
@optname = N'data access', 
@optvalue = N'true' 
exec sp_serveroption N'SQLDEV', N'collation compatible', N'true' 
GO
EXEC sp_addlinkedsrvlogin 
@rmtsrvname = 'SQLDEV'
,@useself = 'TRUE'
--     ,@locallogin = 'sa'    -- this doesn't seem to be required.
GO
EXEC sp_helpserver 'SQLDEV'
EXEC sp_helplinkedsrvlogin 'SQLDEV'
GO
-- This query will work from one hop. e.g. If you are querying directly from one local server to another.
-- You can't do this by Query Analyser on PC A, logging into server B, and running the 4 part query agains
-- Server C.
select * from sqldev.master.dbo.sysservers
GO
-- Some options
-- exec sp_serveroption N'SQLDEV', N'query timeout', 0 
-- exec sp_serveroption N'SQLDEV', N'collation name', N'null' 
-- exec sp_serveroption N'SQLDEV', N'use remote collation', N'true' 
-- exec sp_serveroption N'SQLDEV', N'rpc', N'true' 
-- exec sp_serveroption N'SQLDEV', N'rpc out', N'true' 
-- exec sp_serveroption N'SQLDEV', N'data access', N'true' 
-- exec sp_serveroption N'SQLDEV', N'collation compatible', N'true' 
-- exec sp_helplinkedsrvlogin N'SQLDEV'
-- exec sp_addlinkedserver N'SQLDEV' 
-- Clean up our test
--  Exec Sp_dropserver 'SQLDEV', droplogins

Posted in: SQL  Tags:

Actions: E-mail | Permalink | Comments (0) | Comment RSS | E-mail | Kick it! | DZone it! | del.icio.us

Sometimes this ERROR is just file system permissions. It is definatelya generic error and not reporting what is actually going on.

Test by trying to write the the directory e.g. from SQL

If you get a security message, change the permissions of the directory e.g. Tests, above, to RW.

Posted in: DOS , SQL  Tags: sql, xp_cmdshell, bcp

Actions: E-mail | Permalink | Comments (0) | Comment RSS | E-mail | Kick it! | DZone it! | del.icio.us

A further example from MSDN reference of a VB function, testing the behavior of the READ method, for different lengths, repeated.

SET nocount ON 
DECLARE @ForReading INT , 
@ForWriting INT , 
@ForAppending INT 
SELECT    @ForReading = 1, 
@ForWriting = 2, 
@ForAppending = 8 
DECLARE @fso INT, @file INT, @hr INT 
EXEC @hr = sp_OACreate 'Scripting.FileSystemObject', @fso OUT 
EXEC @hr = sp_OAMethod @fso, 'opentextfile', @file out, 'c:\testfile.txt', @ForWriting, 1 
EXEC @hr = sp_OAMethod @file, 'Write', NULL, '123456789 Hello My world!' 
EXEC @hr = sp_OADestroy @file 
EXEC master..xp_cmdshell 'dir c:\testfile.txt' 
DECLARE @vcOut VARCHAR(100) 
DECLARE @vcOut1 VARCHAR(100) 
EXEC @hr = sp_OAMethod @fso, 'opentextfile', @file out, 'c:\testfile.txt', @ForReading, 1 
EXEC @hr = sp_OAMethod @file, 'Read', @vcOut out , 7 
EXEC @hr = sp_OAMethod @file, 'Read', @vcOut1 out , 2 
EXEC @hr = sp_OADestroy @file 
EXEC @hr = sp_OADestroy @fso 
PRINT REPLICATE('-',100) 
PRINT @vcOUT 
PRINT @vcOUT1   

Posted in: Files , SQL  Tags:

Actions: E-mail | Permalink | Comments (0) | Comment RSS | E-mail | Kick it! | DZone it! | del.icio.us

Sometimes before I actually run dynamic SQL statement I want to know if there is any data, making it worthwhile do run it.

Here's some code that will test the T-SQL for data.

DECLARE @vcSQL VARCHAR(8000)
DECLARE @nvcSQL NVARCHAR(4000),
   @nvcParamDef NVARCHAR(128),
   @bEXISTS bit 
 -- Change the IF statement to test having data vs. not having data.
IF 'EMPTY' = 'EMPTYx'
   SELECT  @vcSQL = 'SELECT * FROM pubs.dbo.dtproperties'
ELSE
   SELECT  @vcSQL = 'SELECT * FROM pubs.dbo.authors'

SELECT     @nvcSQL = 'IF EXISTS (' + @vcSQL +
 ') SELECT @bEXISTS = 1 ELSE SELECT @bEXISTS = 0',
   @nvcParamDef = N'@bEXISTS int OUTPUT'

EXECUTE sp_executesql
   @nvcSQL,
   @nvcParamDef,
   @bEXISTS=@bEXISTS OUTPUT

IF @bEXISTS = 1
   PRINT 'Dynamic SQL [' + @vcSQL + '] data exists'
ELSE
   PRINT 'Dynamic SQL [' + @vcSQL + '] has no data'


Posted in: SQL  Tags: sp_executesql, dynamic sql

Actions: E-mail | Permalink | Comments (0) | Comment RSS | E-mail | Kick it! | DZone it! | del.icio.us

I still don't know what is causing SSMS to output rowcount differently from QA, but to be safe one should use @@ROWCOUNT to output counts for dynamic SQL statements.

SET NOCOUNT ON
DECLARE @vcSQL NVARCHAR(4000)
SET @vcSQL = 'SELECT * FROM pubs..authors;'
PRINT @vcSQL
SET NOCOUNT OFF
PRINT 'Test1: sp_executesql'
EXEC sp_executesql @vcSQL
PRINT 'Test2: EXEC ()'
EXEC (
@vcSQL
)
PRINT '(There were ' + cast(@@ROWCOUNT as varchar) + ' row(s) affected)'
/*
----------------------------------------
Test1 **SSMS output
SELECT * FROM pubs..authors;
Test1: sp_executesql
(23 row(s) affected)
Test2: EXEC ()
(23 row(s) affected)
(There were 23 row(s) affected)
----------------------------------------
Test2 **QueryAnalyzer output
SELECT * FROM pubs..authors;
Test1: sp_executesql
(23 row(s) affected)
Test2: EXEC ()
(There were 23 row(s) affected)
----------------------------------------
*/

Posted in: SQL  Tags:

Actions: E-mail | Permalink | Comments (0) | Comment RSS | E-mail | Kick it! | DZone it! | del.icio.us

This problem occurs occasionally after working for a period of time.

The only way I've found to resolve this is to restart the SQL Server and Agent processes.

I've tried refreshing the registry entry (See MS Article ID: 209805) for the file Mstext40.dll / HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Text, without change.

Here's an example of the T-SQL query.

SELECT 
*
FROM   OPENROWSET('MSDASQL',  
'Driver={Microsoft Text Driver (*.txt; *.csv)};DefaultDir=E:\MSSQL\;', 
'SELECT * FROM [Myfile#txt];'
)

Here's another version of the error.

Server: Msg 7399, Level 16, State 1, Line 2
OLE DB provider 'MSDASQL' reported an error.  
[OLE/DB provider returned message: 
[Microsoft][ODBC Text Driver] System resource exceeded.]
OLE DB error trace [OLE/DB Provider 'MSDASQL' 
IColumnsInfo::GetColumnsInfo returned 0x80004005:   ].

Posted in: SQL , SQL Server  Tags:

Actions: E-mail | Permalink | Comments (0) | Comment RSS | E-mail | Kick it! | DZone it! | del.icio.us

Sometimes I want to start with clean information. I can always add back in the owner and identifiers.

DECLARE @vcTableName varchar(128)
SET @vcTableName = 'dbo.test table'
SET @vcTableName = 'mytable.test'
SET @vcTableName = QUOTENAME('mytable.test table')
IF CHARINDEX('[', @vcTableName) > 0
BEGIN
SET @vcTableName = REPLACE(REPLACE(@vcTableName, '[', ''), ']', '')
END
IF CHARINDEX('.', @vcTableName) > 0
BEGIN
SET @vcTableName = SUBSTRING(@vcTableName, CHARINDEX('.', @vcTableName) + 1, LEN(@vcTableName) - CHARINDEX('.', @vcTableName))
END
PRINT '***' + @vcTableName + '***'
PRINT 'dbo.' + QUOTENAME(@vcTableName)

Posted in: SQL  Tags:

Actions: E-mail | Permalink | Comments (0) | Comment RSS | E-mail | Kick it! | DZone it! | del.icio.us

PROBLEM Error message:

[OLE/DB provider returned message: [Microsoft][ODBC Text Driver] System resource exceeded.]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize returned 0x80004005:   ]

SOLUTION:

I got this error running an import of a text file using a bcp format file and BULK INSERT. The solution was to use a different server. I'd run the command on a SQL Server 2000, Windows 2000 server. I seem to remember something about an issue about some DLL mismatch with the OS or another server. Or maybe even after we installed SSMS for SQL 2005.

Trying the same command on a Windows Server 2003 with the SQL 2000 instance (SQL 2005 instance was also on that server) worked like a charm.

SELECT 
*
FROM   OPENROWSET('MSDASQL',  
'Driver={Microsoft Text Driver (*.txt; *.csv)};DefaultDir=E:\Data\;', 
'SELECT * FROM [ImportFile#txt];'
)

Posted in: SQL , SQL Server , WINDOWS  Tags:

Actions: E-mail | Permalink | Comments (0) | Comment RSS | E-mail | Kick it! | DZone it! | del.icio.us

Find your connection seems to hang?

 The problem presents itself using SSMS, and as it turns out it is running something like
    saving auto recovery information

 

Change this registry setting to 0. This location was on the server.
    HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL Server\90\Tools\ShellSEM\General\AutoRecover

On my PC it was
    [HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL Server\90\Tools\Shell\General\AutoRecover]

 

Change this:

 

Posted in: SQL Server  Tags: pc, hangs

Actions: E-mail | Permalink | Comments (0) | Comment RSS | E-mail | Kick it! | DZone it! | del.icio.us