Please help me on OPENROWSET

Question

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 1

I didn't try but maybe it depends on the fact that column "name" of "model.dbo.sysjobs" is data type "SYSNAME" which is a user defined type.

Try to use CONVERT with type NVARCHAR(256).

Greets

Flo

Ron McCullough SSC Guru Points: 63877 More actions · Answer 2

First - does the Excel workbook exist in the path specified? If not .create it.

Second - does it have in Row 1 column names? If not, note the HDR=No;portion of the command. If it does have headers in row 1 then HDR=Yes;

I tested this modification of your code and it did not report any errors

INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0',

'Excel 8.0;Database=C:\testing.xls;HDR=No;',

'SELECT * FROM [Sheet1$]')

SELECT Name FROM msdb.dbo.sysjobs

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

SQL* SSCertifiable Points: 6638 More actions · Answer 3

hi,

thanks for ur answer, but

INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0',

'Excel 8.0;Database=C:\testing.xls;HDR=No;',

'SELECT * FROM [Sheet1$]')

SELECT * FROM msdb.dbo.sysjobs

it is giving the error like

Insert Error: Column name or number of supplied values does not match table definition.

could u please help me.

🙂

kirankumark77 SSC Rookie Points: 25 More actions · Answer 4

This is a correct solution and it resolved my problem....