hi focks;
through the jobs is possible it s working fine but
is it possible to run the packages through the stired procedure 2005
ok if possible how please help me
regards
koti
I'm trying something new, and have hit a snag.
basically, I'm attempting to use two INSERT commands in a single SP. I want to use the PK from the new entry and apply that to the second.
here's the code;
:::::::::::::::::::::::::::::::::::::::::::::::::: :::::::::::::::
CREATE PROCEDURE spInsertNewUser
@fn varchar(50),
@ln varchar(50),
@add varchar(50),
@add2 varchar(50),
@ct varchar(50),
@cty varchar(50),
@st varchar(4),
@pc varchar(50),
@ph varchar(50),
@cp varchar(50),
@fx varchar(50),
@em varchar(50),
@pw varchar(50),
@uid varchar(50),
@rc bit,
--@indentity int OUTPUT,
@cid int
AS
SET NOCOUNT ON
INSERT INTO tblUserInfo
(
firstName,
lastName,
address,
address2,
city,
county,
state,
postalCode,
phone,
cellPhone,
fax,
email,
pword,
userUID,
regConfirmed
)
VALUES (
@fn,
@ln,
@add,
@add2,
@ct,
@cty,
@st,
@pc,
@ph,
@cp,
@fx,
@em,
@pw,
@uid,
@rc
)
SELECT @@IDENTITY FROM tblUserInfo
--SET @indentity = @@IDENTITY
INSERT INTO tblUserToCountySubscribedLKP (
UserID, CountyID
) VALUES(
@@indentity, @cid
)
:::::::::::::::::::::::::::::::::::::::::::::::::: :::::::::::::::
I'm sure the problem is largely due to the fact that I am close to completely clueless on such things, but a bit of timely advice from the sage sql wizards here would be most welcome and greatly appreciated.
Hello.
I have a SQL table with one column that has the type = image.
I have a Stored procedure that inserts an image into that table.
CREATE PROCEDURE [dbo].[Test]
@test image
AS
BEGIN
insert into Table_1(test)values(@test)
END
I am using ADO in C++ for creating a connection,command, and parameters.
Code Block
VARIANT par;
par.vt = VT_ARRAY;
par.pbVal = (BYTE*)pBuffer;
pCommand->Parameters->Append(pCommand->CreateParameter("test",DataTypeEnum::adArray, ParameterDirectionEnum::adParamInput,strlen(pBuffer),par));
pCommand->Execute(NULL,NULL,CommandTypeEnum::adCmdStoredProc);
where
Code Block
_CommandPtr pCommand;
pCommand->CommandText = (_bstr_t)procedureName;
pCommand->CommandType = CommandTypeEnum::adCmdStoredProc;
//When I append the created parameter, in comutil.h, at these lines :
namespace _com_util {
inline void CheckError(HRESULT hr) throw(...)
{
if (FAILED(hr)) {
_com_issue_error(hr);
}
}
}
I get an exception : m_hresult 0x80020008 Bad variable type.
If I try to insert a string or an number, it works. But with BYTE* it dosen't.
Can someone help me?
Thanks
Hi All,I want to catch the next MSSQL error in my SQL code with following continuecalculationsServer: Msg 17, Level 16, State 1, Line 1SQL Server does not exist or access denied.If REMOTE_SERVER_1 is inaccessible (as in (a) below) the executing of SQLwill not continue with (b) - I need the code in (b) to run despite whetherthe previous exec was successful or not - Any ideas?begin transaction(a) exec REMOTE_SERVER_1...bankinsert '1' , '1' , 1 , 0 , 0(b) print @@errorcommit transactionwhere REMOTE_SERVER_1 is link to server created byEXEC sp_addlinkedserver @server = 'REMOTE_SERVER_1', @srvproduct = '',@provider = 'SQLOLEDB', @datasrc = 'MYCOMP1', @catalog = 'mirror2'EXEC sp_addlinkedsrvlogin @rmtsrvname = 'REMOTE_SERVER_1', .....Exec sp_serveroption 'REMOTE_SERVER_1', 'data access', 'true'Exec sp_serveroption 'REMOTE_SERVER_1', 'rpc', 'true'Exec sp_serveroption 'REMOTE_SERVER_1', 'rpc out', 'true'Exec sp_serveroption 'REMOTE_SERVER_1', 'collation compatible', 'true'Any help will be greatly appreciated
View 1 Replies View RelatedHi everyone.
What is meant by stored procedure?
DECLARE @newyear int ]
set @newyear = 2007 ]----> is it a stored procedure?
what about the CSVTable? is CSVTable a stored procedure?
Thanks. Im just confused.
-Ron-
Hi there,
I am creating a report from a stored procedure that returns
multiples record sets. For an example, my stored procedure is as follows
CREATE PROCEDURE MYPROCEDURE
AS
BEGIN
SELECT * FROM TABLE1
SELECT * FROM TABLE2
SELECT * FROM TABLE3
END
Now lets say I want to create a report that will display the
result of the query no 2 (which is SELECT * FROM TABLE2 in this example). How
can I do this? Is there any way to bind the second record set to the report
dataset with out changing the stored procedure?
I will appreciate any kind of suggestions on this
Thanks
Moim
Hello ,please help me
Is it possible to pass a column name through a parameter
something like this
create procedure dbo.paramerers
@parameter varchar
as
select @parameter from table1 where....
because it doesn't give me an error but it also doesn't work
I'm trying to add a column trough command "ALTER" and I've tried in many different ways.
Here is the result of statement..
Hi,
I have a table users where there is a user_id and an department column.
Also i have a table called KRS where there are the same columns, when a userid is given i want to auto fill in the departmentid,
Can someone help me with this?
Cheers Wim
I lost few days to solve a problem regarding the connection to an Oracle database server (10) with goal to execute a distributed transaction.
The environment was:
Windows Server 2003 Standard
MSSQL Server 2000 Standard
Oracle 10g Production Edition
The connection was quite easy to make with the Microsoft driver, but the error was:
Server: Msg 7391, Level 16, State 1, Line 3
The operation could not be performed because the OLE DB provider 'MSDAORA' was unable to begin a distributed transaction.
OLE DB error trace [OLE/DB Provider 'MSDAORA' ITransactionJoin::JoinTransaction returned 0x8004d01b].
I tried to use the oracle odbc driver, but all kinds of errors were raised, regarding the TNS name and stuff like this. The real cause was that when you create the linked server you must to check the €œAllow inbound process€? in the provider options.
The Oracle ODBC driver allow distributed transactions.
I have got two user-defined functions: fn_Top and fn_Nested. The first one, fn_Top, is structured in this way:
CREATE FUNCTION [dbo].[fn_Top]
RETURNS @Results table (
MyField1 nvarchar(2000),
MyField2 nvarchar(2000)
[code]....
ENDI would like to perform a sort of dynamic parameter passing to the second function, fn_Nested, reading the values of the numeric field MyCounter in the table OtherTable.Fact is, x.MyCounter is not recognized as a valid value. Everything works fine, on the other hand, if I set in fn_Nested a static parameter, IE dbo.fn_Nested(17).
Hi guys,
Can somebody tell me where i can find ( or explain me ) the steps to
connect to sql server using the internet. I am using VB6 (SP6) and SQL SERVER 2000
I want to set up asql server on a central location ( PC ) so that 4 different sites can access the data stored
Thanks for all help
grtz Robert
Hi guys,
When I thought everything is okay with this script, I got a new problem...
I have a VBA's script from Excel 2003 that builds sql script and retrieves data from SQL SERVER 2000.
in order to make the sql running, I need to use a multi - batch processing, to pass and execute every command line once a time.
Up to here, I am using a test case with Account number = '123456' and getting the desire results.
The code below is running okay with the test case, but when changing the account number (mark as yellow in the code) to include all the accounts (or just one other account), I am getting the following ERROR:
run - time error '-2147217871 (80040e31)' - [Microsoft] [ODBC SQL Server Driver] time out expired.
Now, if I take the same code, with the condition that generates the ERROR, and try it into SQL Server, I get the results without errors.
Thanks in advance,
Aldo.
Below the code:
Code Snippet
Function QuerySalesAging()
'--------------------------------------------------------------
'MUST !!! References: Microsoft ActiveX Data Object 2.1 Library
'--------------------------------------------------------------
Dim ConnString As New ADODB.Connection
Dim RecordSet As New ADODB.RecordSet
'Setting Connection String
Driver = "{SQL Server}"
ServerName = "SERVER"
DB_Name = CompanyName
ConnString = "Driver=" & Driver & ";" & "Server=" & ServerName & ";" _
& "Database=" & DB_Name & ";" & "Uid=" & SQLLoginName & ";" & "Pwd=" & SQLPassword & ";"
'Report Criterias
Criteria05 = " AND " & "Accounts.ACCOUNTKEY Between " & AccountKeyAsRange
' -- ==> With AccountKeyAsRange = '123456' AND '123456' it works okay.
' -- ==> With any other value, in example AccountKeyAsRange = '123456' AND '9999999999' it get's ERROR.
CmdLine01 = " USE " & CompanyName
' Check and drop temporary table
TemporaryTableName = "CTE" ' The table is a regular one
CmdLine02 = " if object_id('" & TemporaryTableName & "') is not null exec('DROP TABLE " & TemporaryTableName & "') "
CmdLine03 = " SELECT ..."
CmdLine03 = CmdLine03 & " INTO " & TemporaryTableName
CmdLine03 = CmdLine03 & " FROM ..."
CmdLine03 = CmdLine03 & " WHERE " & "(" & Replace(Criteria05, "AND", "") & ")"
CmdLine03 = CmdLine03 & " ORDER BY ..."
CmdLine04 = CmdLine04 & " ALTER TABLE " & TemporaryTableName ...
CmdLine05 = CmdLine05 & " UPDATE " & TemporaryTableName ...
CmdLine06 = CmdLine06 & " SELECT ..."
CmdLine06 = CmdLine06 & " FROM ..."
ConnString.Open
ConnString.Execute CmdLine01
ConnString.Execute CmdLine02
RecordSet.Open CmdLine01, ConnString
RecordSet.Open CmdLine02, ConnString
RecordSet.Open CmdLine03, ConnString
RecordSet.Open CmdLine04, ConnString
RecordSet.Open CmdLine05, ConnString
RecordSet.Open CmdLine06, ConnString
ConnString.Execute CmdLine01
ConnString.Execute CmdLine02 ' The debbuger stops here:" if object_id('CTE') is not null exec('DROP TABLE CTE') "
ConnString.Execute CmdLine03
ConnString.Execute CmdLine04
ConnString.Execute CmdLine05
ConnString.Execute CmdLine06
ConnString.Execute CmdLine02
'Retrieve Field titles
For ColNr = 1 To RecordSet.Fields.Count
ActiveSheet.Cells(1, ColNr).Value = RecordSet.Fields(ColNr - 1).Name
Next
ActiveSheet.Cells(2, 1).CopyFromRecordset RecordSet
'Cleanup & Close ADO objects
ConnString.Execute "USE master"
ConnString.Close
Set RecordSet = Nothing
Set ConnString = Nothing
End Function
I am trying to set up a DTS package that selects data from one table on server A into another table on server B. I want to do a select statement that will do the following:
select store_name
from store (server A)
where date_created >= (select max(date_created) from store (server B)
I use EM 7.0 to manage all of my extracts, however, the data is moving from a Syabase (adaptive Server)onto another Syabase(adaptive server) machine.
Unfortunately, there is no functionality for a linked server connection.
I tried the following, (which doesn't error out), but is not displaying the source columns in the destination tab of the DTS package when setting up the transformations.
declare @maxdate datetime
exec serverB.dbo.sp_max_date_from_store @maxdate
select store_name
from store --(server A)
where date_created >= @store
Any help or suggestions would be greatly appreciated!
trevorb
Good day all! :cool:
I have a DTS package that consists of 2 tasks, one empties a table(1) to receive data from a transformation task(2)...
I found 'code' for a stored procedure to run a DTS package.. problem is that it is only running the first task... I need it to run the whole she-bang!
Anyone know how to do this?
Here is the stored proc:
/************************************************** **************************************/
/* Filename: spExecutePKG.sql*/
/* Description:Execute DTS Packages via sp_OAxxxx*/
/* Created:Darren Green 200006013*/
/************************************************** **************************************/
if exists (select * from sysobjects where id = object_id(N'[dbo].[spExecutePKG]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[spExecutePKG]
GO
CREATE PROC spExecutePKG
@Server varchar(255),
@PkgName varchar(255), -- Package Name (Defaults to most recent version)
@ServerPWD varchar(255) = Null,-- Server Password if using SQL Security to load Package (UID is SUSER_NAME())
@IntSecurity bit = 0,-- 0 = SQL Server Security, 1 = Integrated Security
@PkgPWD varchar(255) = ''-- Package Password
AS
SET NOCOUNT ON
/*
Return Values
- 0 Successfull execution of Package
- 1 OLE Error
- 9 Failure of Package
*/
DECLARE @hr int, @ret int, @oPKG int, @Cmd varchar(1000)
-- Create a Pkg Object
EXEC @hr = sp_OACreate 'DTS.Package', @oPKG OUTPUT
IF @hr <> 0
BEGIN
PRINT '*** Create Package object failed'
EXEC sp_displayoaerrorinfo @oPKG, @hr
RETURN 1
END
-- Evaluate Security and Build LoadFromSQLServer Statement
IF @IntSecurity = 0
SET @Cmd = 'LoadFromSQLServer("' + @Server +'", "' + SUSER_SNAME() + '", "' + @ServerPWD + '", 0, "' + @PkgPWD + '", , , "' + @PkgName + '")'
ELSE
SET @Cmd = 'LoadFromSQLServer("' + @Server +'", "", "", 256, "' + @PkgPWD + '", , , "' + @PkgName + '")'
EXEC @hr = sp_OAMethod @oPKG, @Cmd, NULL
IF @hr <> 0
BEGIN
PRINT '*** LoadFromSQLServer failed'
EXEC sp_displayoaerrorinfo @oPKG , @hr
RETURN 1
END
-- Execute Pkg
EXEC @hr = sp_OAMethod @oPKG, 'Execute'
IF @hr <> 0
BEGIN
PRINT '*** Execute failed'
EXEC sp_displayoaerrorinfo @oPKG , @hr
RETURN 1
END
-- Check Pkg Errors
EXEC @ret=spDisplayPkgErrors @oPKG
-- Unitialize the Pkg
EXEC @hr = sp_OAMethod @oPKG, 'UnInitialize'
IF @hr <> 0
BEGIN
PRINT '*** UnInitialize failed'
EXEC sp_displayoaerrorinfo @oPKG , @hr
RETURN 1
END
-- Clean Up
EXEC @hr = sp_OADestroy @oPKG
IF @hr <> 0
BEGIN
EXEC sp_displayoaerrorinfo @oPKG , @hr
RETURN 1
END
RETURN @ret
GO
how can a go about running a stored proc when my dts package is finished running?
View 2 Replies View RelatedHi
I have a SSIS package stored on the database server under Stored Packages within the Integration Services section.
Does anyone know how to run this package using SQL?
Thanks
Hi Friends,
In MS SQL SERVER 2005 we created one new SSIS package.
Where it will be stored in MS SQL SERVER 2005.
Default location SSIS package storage in MS SQL SERVER 2005
Ths & Regaurds
shiv
Meti BEST OF THE BEST
Hi, I am new to SQL 2005 and to SSIS Packages so please forgive the silly question.
I just right-clicked on my database and chose ALLTASKSImport Data.
I selected my flat file and went through the rest of the wizard.
I saved the package when prompted so I can run it later with SQL server Agent.
But now I can't find where the Package is so that I can run it.
In SQL2k I could just look under the DTS folder and find my package.
thanks
ICW
I created a DTS package to transfer data from a remote database into my local database, I want to run this DTS in my stored procedure, can I do that?
Please help me, thanks a lot
I'm relatively new to SSIS, but I have read all the info/threads on Package Config. and I'm really confused.
Our production env. is clustered, and we are running multiple sql instances, so the sys admin does not want me to use Env. Variables or XML files for the package configurations (it's too hard for him to maintain the info across multiple instances, etc). So, I am storing the package configurations in SQL.
Just as a simple test, I used the wizard to create the 1 package configuration for production. The configured value as a complete connection string
When I run the package, it completely ignores the package configuration. How do I change the connections on my tasks to read from the package configuration instead of the connection manager they are currently associated with?
TIA,
Mary Jo
I created a dts package and I can execute it.
I want to include the dts package execution in a stored procedure, but I can't get the stored procedure to execute it from the cmdshell.
I have sql integration services and mssql 2005 services running under a domain account.
I have saved the package as a FILE System stored package.
I just can't find a reason why it won't execute from stored procedure.....
Ron
I have used Import-Export wizard to import some data from a flat file into a db table. When prompted, I have chosen to store the package in SQL Server. The package is executing correctly, scheduled to run (job) once a day...the problem is that I can not see this package in EM and I do not know how to open it in BIDS (since i can not point to a certain file). I can see the package in msdb sysdtspackages90 table, but nowhere else in EM - under management->Maintenance Plans I can see the packages created for db maintenance, but not the one created using import-export wizard. I can se the GUID in the job (command line tab) but the search for the GUID # does not yield any results. The searc for .dtsx does not show this package either. Where is it? If I wanted to open and edit this package with BIDS, how would I do that?
Thanks in advance!
Do you know, if there are any ways to start DTS package from the stored procedure?
Thank you,
Anastasia
I have a DTS Package to populate data from excel sheet to direct simple table. I like to call this DTS from User interface. thanks in advance
View 2 Replies View RelatedI have a DTS package written to import data from a txt file into a SQL Server 7.0 table
but before importing the data i need to delete the existing data from the table...
for this i need to use the stored procedure to delete data first...
how do i run the dts package from the stored procedure..does anyone know the command for this...
regards,
reshma
My current task in my company is...
I got a Spreadsheet from which i have to get the data into a "Staging Table" and from there the data has to go into 10 different Tables in my database..
I had created a DTS package using ActiveX script and written a Stored Proc to get the records from Staging table to Database tables...every thing is working fine... but...
they asked me to send "TapeType' and "DealName" into two fields and "current datetime" into one more field of the stagingtable fields which are not present inthe Spreadsheet....
so in the Activex script i hard coded... destination"TapeType"= "BidTape" and destination"DealName"= "ABCD" and its working...
and for currentdate i had defined a DTS Task and in that i had written a SQL stmts..
update table....
set currentdate = getdate()
where TapeType = 'BidTape' ..... which is also working....but the problem is
if i got one more spread sheet with same "TapeType" i.e. BidTape but with different "DealName" i.e. "XYZ".... every thing is working out but....the Currentdate field is getting updated every where with as one date..???
can u suggest me a better solution here.....
and after this Iam planning to create one more Task to Execute the Proc..... but I have to pass 2parameters i.e. "Dealname" and "TapeType"... they want every thing to be get done with single mouse click...
can u suggest me here how to pass these 2parameters in the DTS package,..
I will be waiting for any one your valuable suggestions....
When I run the code microsoft give to launch a DTS package from a stored procedure, the procedure runs continuously, never ending.
When I run the DTS package manually, or from a dtsrun utility, it only takes seconds.
I'm trying to automate this package so it will run after a field in a table has been updated.
Any suggestions are more than welcome.
PS. Here's the code I've been using (that doesn't seem to work):
--- Declare Variables
Declare @hr int
Declare @oPkg int
--- Create Package Object
Exec @hr = sp_OACreate 'DTS.Package', @oPkg OUT
If @hr <> 0
Begin
Print '*** Create Package Object Failed'
Exec sp_oageterrorinfo @oPkg, @hr
Return
End
--- Load Package
---DTSSQLStorageFlags :
---DTSSQLFlag_Default = 0
---DTSSQLStgFlag_UseTrustedConnection = 256
Exec @hr = sp_OAMEthod @oPkg,'LoadFromSqlServer("JENNSERVER", "", "", 256, , , , "RunMS")',null
If @hr <> 0
Begin
Print '*** Load Package Failed'
Exec sp_oageterrorinfo @oPkg, @hr
Return
End
--- Execute Package
Exec @hr = sp_OAMethod @oPkg, 'Execute'
If @hr <> 0
Begin
Print '*** Execute Failed'
Exec sp_oageterrorinfo @oPkg, @hr
Return
End
--- Clean Up Package
Exec @hr = sp_oadestroy @oPkg
If @hr <> 0
Begin
Print '*** Destroy Package Failed'
Exec sp_oageterrorinfo @oPkg, @hr
Return
End
I need to automate the following function. I know I can do this in .NET but I need to have a DTS package setup so it executes every evening.
I have a view that produces a list of sales errors. I want to grab the SaleID from each record and insert a record into a "Current Queue" table.
Here is another example of what I'm trying to do...
SELECT SaleID FROM vw_SalesErrors
- with the results of the SQL statement above
INSERT INTO tbl_QueueRecords
(SaleID, QueueID)
VALUES
(@SaleID, 14)
I'm assuming there is a way to automate this into a single stored procedure or at least a DTS pacakge.
Thanks for all of your help.
Hi everybody,
I would like to ask that how can I execute a package from a stored procedure.
Laci
Hello,I have a stored procedure that processes an individual file from adirectory and archives it in a subdirectory.Now, the problem is, when iexecute it , it will only process one file. What i want to do is to checkto see if there are any files in the folder, and if there are , processthem all, and once done, go to the next part in a DTS package, if there areno files, simply go to the next part in the DTS package. I tried an activexscript that would get the filecount in the folder, and if there were morethan 0 files in the folder, then DTS-sUCCESS and on "success" workflow , itwould run the stored procedure, and thus it woould process one file, then"on completion" the workflow connected it back to the activeX script(thuslooping), which would count the files again. Now if there were 0 files, itwould report DTS_FAILIURE, and I had it set up ,"on failiure" to go to thenext step in the package, but it wouldn't run.Someone mind showing me a ray of light?
View 1 Replies View RelatedI'm a rookie with MSSQL. I need to run a DTS package to export a result setto an MX Excel spread sheet. I need to call the DTS from a stored procedureand pass it three values, depending on the input parameters to the storedproc.DTS package is no problem. Pretty easy with the DTS wizard. My problem isthat I can't figure out how to instansiate the DTS package object from astored proc and pass the three values as parameters to the DTS package sothey can populate the parameters I created in it.I found an article related to it, but I'm too much of a rookie to grasp it.It showed how to do this from a stored procedure:EXEC @hr = sp_OASetProperty @oPKG, 'GlobalVariables("MyGVName").Value,'MyGVValue'IF @hr <> 0BEGINPRINT '*** GlobalVariable Assignment Failed'EXEC sp_displayoaerrorinfo @oPKG, @hrENDI have three values and tree global variables to populate. Do I need to dothe above 3 times?How do I instantiate the package object? I've read up some on sp_OACreate,but I don't get it, yet.How do I initiate the variable @oPKG?It contains the name of the sp_OACreate string, right? How do I address aDTS package in the sp_OACreate string?I would really appreciate just writing out the sp_OACreate string and how Ipass values for three existing global variables to a DTS package named"DTS_1".I'm under some real pressure to get this done.Thanks for any help I can get.Gunny
View 1 Replies View Relatedis there a way to execute SSIS Package through stored proceedure.
Or any other method of executing the SSIS Package command line in stored proceedure
Thanks,
jas