Problem with OpenRowSet and String

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Problem with OpenRowSet and String

Author

Topic

mandrews1234
Starting Member

9 Posts

Posted - 2009-09-11 : 14:24:55

I have a sp that opens an excel file to insert values into a database. The problem I am having is that there is a part id column. Now in these ids could be something like 0500505 or 150-0505 so they are stored as a string. However, the openrowset is setting them to decimals and giving me null on numbers with a -. How do I tell it that it is a string. This is what I have tried
SET @sql = 'INSERT INTO dbo.TempParts (partKey, partKeyAlpha, PartNumber, [Description], Qty, Comment, notAvailable ,BatchID)
SELECT *, '+ CONVERT(nvarchar(20), @batchID) + '
FROM OPENROWSET(' + '''' + 'Microsoft.Jet.OLEDB.4.0' + '''' + ','
+ '''' + 'Excel 8.0;Database=' + @filePath + ';IMEX=1' + '''' + ',' + '''' + 'SELECT [Key], [partKeyAlpha],
' + CONVERT(nvarchar(20), '[Part #]')+', [Description], [Qty#],
[Comments], [Not Available] FROM [Sheet1$]'
+ '''' +')
WHERE [Key] IS NOT NULL OR [Part #] IS NOT NULL OR [Description] IS NOT NULL'

Look at the convert nvarchar method. That's where I'm talking about. Here is the whole SP just in case anyone has a different idea on how I can insert these.
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

ALTER PROCEDURE dbo.ImportExcelFile
(
@batchID int,
@filePath nvarchar(300)
)
AS

declare @sql nvarchar(3000)

SET @sql = 'INSERT INTO dbo.TempParts (partKey, partKeyAlpha, PartNumber, [Description], Qty, Comment, notAvailable ,BatchID)
SELECT *, '+ CONVERT(nvarchar(20), @batchID) + '
FROM OPENROWSET(' + '''' + 'Microsoft.Jet.OLEDB.4.0' + '''' + ','
+ '''' + 'Excel 8.0;Database=' + @filePath + ';IMEX=1' + '''' + ',' + '''' + 'SELECT [Key], [partKeyAlpha],
' + CONVERT(nvarchar(20), '[Part #]')+', [Description], [Qty#],
[Comments], [Not Available] FROM [Sheet1$]'
+ '''' +')
WHERE [Key] IS NOT NULL OR [Part #] IS NOT NULL OR [Description] IS NOT NULL'
--PRINT @sql
/*
SET @sql = 'INSERT INTO dbo.TempParts (partKey, partKeyAlpha, PartNumber, [Description], Qty, Comment, notAvailable ,BatchID)
SELECT *, '+CONVERT(nvarchar(20), @batchID)+' FROM OPENROWSET(''Microsoft.Jet.OLEDB.4.0'',
''Excel 8.0;Database=''' + @filePath + ''';IMEX=1;'', ''SELECT [Key], [partKeyAlpha], [Part #], [Description], [Qty#],
[Comments], [Not Available] FROM [Sheet1$] WHERE [Key] IS NOT NULL OR [Part #] IS NOT NULL OR [Description] IS NOT NULL'')'
*/
EXEC sp_executesql @sql
UPDATE dbo.TempParts
SET partKeyNumeric = CAST(ltrim(rtrim(partKey)) as int)
WHERE BatchID = @batchID

RETURN
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Thanks,

Michael Andrews
Web Developer
www.intellicominc.com

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-09-13 : 14:09:46

whats the datatype of PartNumber column in tempparts?

mandrews1234
Starting Member

9 Posts

Posted - 2009-09-14 : 11:47:43

nvarchar(10) I know it is correct because if you try to import an excel file with - starting the file then it works fine. I.e. if the very first entry has a part number 150-502 instead of 150502 then the sp works fine. Do you have any suggestions on how to fix this? Thanks for the help.

Michael Andrews
Web Developer
www.intellicominc.com

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-09-14 : 13:45:04

what's the property set in excel for column?

mandrews1234
Starting Member

9 Posts

Posted - 2009-09-14 : 14:59:45

That was it, the property was in numeric for some and text for the others. I do have a new problem now though. I get an sql timeout error.
System.Data.SqlClient.SqlException: Timeout expired. The timeout periodelapsed prior to completion of the operation or the server is notresponding. at System.Data.SqlClient.SqlConnection.OnError(SqlExceptionexception, Boolean breakConnection) atSystem.Data.SqlClient.SqlInternalConnection.OnError(SqlExceptionexception, Boolean breakConnection) atSystem.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObjectstateObj) at

I have set the timeout error in my web.config like this

<add name="orthmanConnectionString" connectionString="Data Source=10.2.25.11;Initial Catalog=partsonline_orthman_com_parts;User ID=partsonline_orthman_com_user;Password=hk7mzv;Connect Timeout=240;CommandTimeout = 240;" providerName="System.Data.SqlClient"/>

I have tried setting it in the code as well like this

Private Sub drawingsSqlDataSource_Selecting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceSelectingEventArgs) Handles drawingsSqlDataSource.Selecting
e.Command.CommandTimeout = 240
End Sub

drawingsSqlDataSource is the source I use to insert this file. I have also set the timeout in iis and I still only run for about 240 seconds and then it times out on me. Any ideas why?

Michael Andrews
Web Developer
www.intellicominc.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources