Issue regarding stored procedure

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

General SQL Server Forums

New to SQL Server Programming

Issue regarding stored procedure

Author

Topic

nicky_river
Yak Posting Veteran

55 Posts

Posted - 2010-08-12 : 07:49:31

Hi Everyone,

I am having a problem regarding insertion of excelsheet values to a table in SQL database through a stored procedure only.

Here it goes,

First, I have created an excelfile by the name of "importdata.xls".
the values which i have put in it are as given below.

bank_code bank_transaction money_trn check_num
100 15000 12360 672249
200 25000 4354 873435
300 45000 9999 75466
400 65000 333 8523

Second, in SQL Server Business intelligence development studio, I have created a data flow task, which has an excel source and an OLE DB destination, in the excel source i have given the above excelsheet name and in OLE DB destination I have given the table name where the excelsheet data has to be inserted i.e. "dbo.import_data2". The name of the package is "Package.dtsx". When I am doing it through this method the data gets inserted into the "dbo.import_data2" properly.

But the problem is when I am doing the same operation through a stored procedure it is not happening.

See the given procedure below.

ALTER procedure dbo.import_data
as
declare @sql nvarchar(50),
@Bank_code numeric(35),
@Bank_transaction numeric(35),
@Money_trn numeric(35),
@Check_num varchar(25)
--as

set @sql = ''
set @sql = 'exec C:\Documents and Settings\varunj\My Documents\Visual Studio 2008\Projects\Integration Services Project2\Integration Services Project2\Package.dtsx'
--exec (@sql)

--select * from import_data1
--insert into import_data1 values (400,65000,3256,8523)
--truncate table import_data2
--select * from import_data2
--delete from import_data2 where bank_code = 400

--insert new records
insert into dbo.import_data2 (Bank_code,Bank_transaction, Money_trn, Check_num )
select a.Bank_code,a.Bank_transaction, a.Money_trn, a.Check_num
from dbo.import_data1 a left join
dbo.import_data2 b
on a.bank_code=b.bank_code
where b.bank_code is null

--select * from dbo.import_data2

-- update existing records
update dbo.import_data2
set money_trn = x.Money_trn
from
(select a.Bank_code,a.Bank_transaction, a.Money_trn, a.Check_num
from dbo.import_data1 a join
dbo.import_data2 b
on a.bank_code=b.bank_code) x
where import_data2.bank_code = x.bank_code

--clean up import_data1 (temp table)
truncate table dbo.import_data1

exec (@sql)

GO

The work that this procedure must do is, whenever an insertion and updation changes are made to the excelsheet, the changes must be reflected in the "dbo.import_data2" table using the "package.dtsx" file. (i need to execute "package.dtsx" through the stored procedure)

Please provide me the solution on how to go about accomplishing this task or if there are any flaws in the code please provide me a proper code regarding this issue.

Thanks and Regards

kashyap_sql
Posting Yak Master

174 Posts

Posted - 2010-08-12 : 07:59:00

are you trying to create a table in sql from excel file

With Regards
Kashyap M

kashyap_sql
Posting Yak Master

174 Posts

Posted - 2010-08-12 : 08:05:57

if my guess is right you can do it in a simple way that is


SELECT * INTO desriedtable_name 
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=d:\importdata.xls', 'SELECT * FROM [Sheet1$]')

try this

With Regards
Kashyap M

nicky_river
Yak Posting Veteran

55 Posts

Posted - 2010-08-12 : 08:17:26

hi kashyap_sql,

No, the table has already been created in SQL before. I cannot use the query that you have given me also because even updation and insertion takes place on this excelsheet it should be reflected in the "dbo.import_data2" table.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-08-12 : 08:20:56

The package and the excelfile are located on your client while the sql server is running on another machine?

No, you're never too old to Yak'n'Roll if you're too young to die.

nicky_river
Yak Posting Veteran

55 Posts

Posted - 2010-08-12 : 08:24:23

Hi webfred,

The package and the excelfile is on my machine, while SQL Server is running on the server, while I have access to the database that this operation is being performed.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-08-12 : 08:29:19

What you want to do isn't easy possible.

Place the excelfile in the filesystem of the server where sql server has the right to access the file.
Create the package as new one on this server like you have done before on your client.
Then you can try to start the package via SP.

Good luck.

No, you're never too old to Yak'n'Roll if you're too young to die.

nicky_river
Yak Posting Veteran

55 Posts

Posted - 2010-08-12 : 08:33:18

Hi webfred,

The package,excelfile and the SQL Server is located on my machine only. Sorry for the misinterpretaion. Now how can it be done. is there any problem with my stored procedure ?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-08-12 : 08:37:05

Ah I see...

Look for DTEXEC.EXE to execute a package.

No, you're never too old to Yak'n'Roll if you're too young to die.

nicky_river
Yak Posting Veteran

55 Posts

Posted - 2010-08-12 : 10:44:26

Hi webfred,

How do i call this in the stored procedure ? Please give me the code.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-08-12 : 10:58:59

quote:
Originally posted by nicky_river

Hi webfred,

How do i call this in the stored procedure ? Please give me the code.

You need to use xp_cmdshell to execute DTEXEC from a SP. See if these links help you.
http://www.codeproject.com/KB/database/Call_SSIS_from_SP.aspx
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=79508

nicky_river
Yak Posting Veteran

55 Posts

Posted - 2010-08-12 : 11:04:16

Hi,

where do I put this in my SP which I have given and how to I run it (SP) ?

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-08-12 : 11:06:30

Replace this piece of code

set @sql = ''
set @sql = 'exec C:\Documents and Settings\varunj\My Documents\Visual Studio 2008\Projects\Integration Services Project2\Integration Services Project2\Package.dtsx'
--exec (@sql)

with xp_cmdshell

nicky_river
Yak Posting Veteran

55 Posts

Posted - 2010-08-12 : 11:26:31

Hi,

Could you provide me the exact piece of code that i need to replace with my existing code.

Thanks

nicky_river
Yak Posting Veteran

55 Posts

Posted - 2010-08-13 : 01:35:54

Hi,

I am getting this message when i executed this SP by using xp_cmdshell.

"Cannot add rows to sysdepends for the current stored procedure because it depends on the missing object 'xp_cmdshell'. The stored procedure will still be created."

how do i resolve this ?

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-08-13 : 10:04:57

quote:
Originally posted by nicky_river

Hi,

I am getting this message when i executed this SP by using xp_cmdshell.

"Cannot add rows to sysdepends for the current stored procedure because it depends on the missing object 'xp_cmdshell'. The stored procedure will still be created."

how do i resolve this ?

Show us the entire SP..and also point out what you changed.

nicky_river
Yak Posting Veteran

55 Posts

Posted - 2010-08-14 : 00:30:54

Hi vijayisonly,

Please see the procedure below, the changes that i have made.
--------------------------------------------------------------
ALTER procedure dbo.import_data
as
declare @sql nvarchar(50),
@Bank_code numeric(35),
@Bank_transaction numeric(35),
@Money_trn numeric(35),
@Check_num varchar(25),
--as
@FilePath varchar(2000),

@Filename varchar(1000),

@cmd varchar(2000)

set @FilePath = 'C:\Documents and Settings\varunj\My Documents'

set @Filename = 'Package.dtsx'

select @cmd = 'DTExec /F "' + @FilePath + @Filename + '"'

print @cmd

--exec master..xp_cmdshell @cmd

set @sql = ''
--EXEC @sql = xp_cmdshell 'dtexec /f "C:\Documents and Settings\varunj\My Documents\Package.dtsx"'

--exec (@sql)

--select * from import_data1
--insert into import_data1 values (400,65000,3256,8523)
--truncate table import_data2
--select * from import_data2
--delete from import_data2 where bank_code = 400

--insert new records
insert into dbo.import_data2 (Bank_code,Bank_transaction, Money_trn, Check_num )
select a.Bank_code,a.Bank_transaction, a.Money_trn, a.Check_num
from dbo.import_data1 a left join
dbo.import_data2 b
on a.bank_code=b.bank_code
where b.bank_code is null

--select * from dbo.import_data2

-- update existing records
update dbo.import_data2
set money_trn = x.Money_trn, Bank_code = x.Bank_code, Bank_transaction = x.Bank_transaction, Check_num = x.Check_num
from
(select a.Bank_code,a.Bank_transaction, a.Money_trn, a.Check_num
from dbo.import_data1 a join
dbo.import_data2 b
on a.bank_code=b.bank_code) x
where import_data2.bank_code = x.bank_code

--clean up import_data1 (temp table)
truncate table dbo.import_data1

truncate table dbo.import_data2

--exec (@sql)

--EXEC @sql = xp_cmdshell 'dtexec /f "C:\Documents and Settings\varunj\My Documents\Package.dtsx"'

--EXEC @sql = SQL_Training..xp_CmdShell 'DTSRUN /S' + @@SERVERNAME + ' /E /N' + Package.dtsx + char(32) + '/Ags_server_name=' + @@SERVERNAME

--EXEC SQL_Training..xp_cmdshell 'DTExec/SQL "\Package.dtsx" /PARA3KSAN03 "." /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EW'

exec SQL_Training..xp_cmdshell @cmd

GO
------------------------------------------------------------------

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-14 : 01:01:20

Is xp_cmdshell enabled in your db?
see

http://www.mssqltips.com/tip.asp?tip=1020

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

nicky_river
Yak Posting Veteran

55 Posts

Posted - 2010-08-14 : 01:09:32

Hi,

No, "xp_cmdshell" is enabled in master database, it is an extended SP. Can it not be called in any other database also ?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-14 : 01:18:21

it can be called. I was asking if it was enabled as in SQL 2005 and above it would be disabled by default

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

nicky_river
Yak Posting Veteran

55 Posts

Posted - 2010-08-14 : 05:20:40

Hi,

In my procedure shown above I wanted to know if the insert and update statements will get executed once I execute the package.

For Eg.

- in my excelsheet i change or insert new values

- then i execute the package and then i run this SP

will the changes get affected in the import_data2 table ?

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources