Stored Procedure Fails On Call From V6.5 To V7 MS SQL Server
Aug 24, 2000
I am having a problem trying to run a stored procedure
from a MS SQL Server version 6.5 server. The stored
procedure calls another stored procedure on a MS SQL
Server version 7.0 server.
The job task always fails with the error message...
"Login failed for user 'sa'. (Message 18456)".
I can run the stored procedure from the T-Query window
on the 6.5 server successfully but it will not run
as a job task.
Does anyone have any suggestions?
View 1 Replies
ADVERTISEMENT
Nov 27, 2001
Hi,
I was wondering is anyone can help me out on this one,
I want to run a query, but I need to reference a Database that exists on a different server. I am using SQL 6.5
Any suggestions would be welcomed,
Thanks a mill,
Fin
View 1 Replies
View Related
Mar 19, 2008
Hi I have asp.net page with approx 28 dropdowns. I need to insert these records using one stored procedure call. How can I do this while not sacrificing performance?
Thanks, Gary
View 9 Replies
View Related
Nov 2, 2015
We have a process that uses a stored procedure: sp_MysteryProcedure....
The problem I am having is: I am 95% certain the issue lies with this stored procedure, but I cannot find the process that calls this procedure. It could be a SQL Server Job that calls the procedure directly, it could be an SSIS (*.dtsx) process that calls this procedure, or some other random process.
One of the big issues is we have tons of *.dtsx packages that call a bunch of stored procedures, but it doesn't seem a normal Windows Search (Start --> Search) searches these files (or perhaps something else is going on with this search).
So my question is multi-part.....
1). How would one go about finding a rouge process that loads data via a stored procedure if we believe we know the stored procedures name?
2). How do you search *.dtsx files?
View 9 Replies
View Related
Nov 9, 2007
Using the MSSQL JDBC 1.2 driver (Oct 2007 release), we don't seem to be able to call stored procedures which internally uses app_name() function to fill into database tables .
This driver fails to access such stored procedures in both SQL Server 2000 and SQL Server 2005 databases.
The previous 1.1 driver (2006), suceeds in both cases.
Here is a test case which demonstrates this problem.
create table ICS_ConsraintTest (PrimaryIdentifier varchar(64),
ServiceProvider varchar(64),
SecondaryID varchar(64),
SecondaryServiceProvider varchar(64),
Description varchar(64),
PanoramaObject varchar(64),
Operation int,
ScalingFactor int,
DisplayAs float,
FeedType int,
InputValue int,
OutputValue float,
salary float,
birthdate datetime,
CONSTRAINT SimpleTestConstraint CHECK((len(ltrim([PrimaryIdentifier])) > 0
and len(ltrim([ServiceProvider])) > 0
and len(ltrim([SecondaryID])) > 0
and len(ltrim([PanoramaObject])) > 0
and [salary] <> 0.0)))
Stored procedure id defined as follows:
CREATE PROCEDURE SP_ICS_TestWithConstraints1(@PrimaryIdentifier varchar(64),
@PrimaryServiceProvider varchar(64),
@ServiceProvider varchar(64),
@SecondaryID varchar(64),
@SecondaryServiceProvider varchar(64),
@Description varchar(64),
@PanoramaObject varchar(64),
@Operation int,
@ScalingFactor int,
@DisplayAs float,
@FeedType int,
@InputValue int,
@OutputValue float,
@salary float,
@birthdate datetime) AS
BEGIN
BEGIN TRANSACTION
BEGIN
/* Insert */
INSERT INTO ICS_ConsraintTest ( PrimaryIdentifier,
ServiceProvider,
SecondaryID,
SecondaryServiceProvider,
Description,
PanoramaObject,
Operation,
ScalingFactor,
DisplayAs,
FeedType,
InputValue,
OutputValue,
salary,
birthdate)
VALUES ( @PrimaryIdentifier,
@ServiceProvider,
app_name(),
@SecondaryServiceProvider,
@Description,
@PanoramaObject,
@Operation,
@ScalingFactor,
@DisplayAs,
@FeedType,
@InputValue,
@OutputValue,
@salary,
@birthdate)
END
COMMIT TRANSACTION
END
Check out the app_name() is passed as the SecondaryID which causes the failure.
View 6 Replies
View Related
Jul 23, 2014
I have created a stored procedure with dynamic query and using sp_executesql . stored procedure is work fine.
Now i want to call stored procedure in select statement because stored procedure return a single value.
I search on google and i find openrowset but this generate a meta data error
So how i can resolve it ???
View 7 Replies
View Related
Mar 10, 2005
I didn't want to maintain similar/identical tables in a legacy FoxPro system and another system with SQL Server back end. Both systems are active, but some tables are shared.
Initially I was going to use a Linked Server to the FoxPro to pull the FP data when needed. This works. But, I've come up with what I believe is a better solution. Keep in mind that these tables are largely static - occassional changes, edits.
I will do a 1 time DTS from FP into SQL Server tables.
I then create INSERT and UPDATE triggers within FoxPro.
These triggers fire a stored procedure in FoxPro that establishes a connection to the SQL Server and fire the appropriate stored procedure on SQL Server to CREATE and/or UPDATE the corresponding table there.
In the end - the tables are local to both apps.
If the UPDATES or TRIGGERS fail I write to an error log - and in that rare case - I can manually fix. I could set it up to email me from within FoxPro as well if needed.
Here's the FoxPro and SQL Server code for reference for the Record Insert:
FOXPRO employee.dbf InsertTrigger:
employee_insert_trigger(VAL(Employee.ep_pk),Employ ee.fname,Employee.lname,Employee.email,Employee.us er_login,Employee.phone)
FOXPRO corresponding Stored Procedure:
FUNCTION EMPLOYEE_INSERT_TRIGGER
PARAMETERS wepk,wefname,welname,weemail,WEUSERID,WEPHONE
nhandle=SQLCONNECT('SS_PDITHP3','userid','password ')
IF nhandle<0
m.errclose=.f.
IF !USED("errorlog")
USE tisdata!errorlog IN SELECT(1)
m.errclose=.t.
ENDIF
SELECT errorlog
INSERT INTO errorlog (date, time, program,source,user) ;
values (DATE(), TIME(), 'EMPLOYEE_INSERT_TRIGGER','nhandle<0 PARAMS: '+STR(wepk)+wefname+welname+weemail+WEUSERID+WEPHO NE,GETENV("username"))
IF m.errclose
USE IN errorlog
ENDIF
RETURN
ENDIF
nquery="exec ewo_sp_insertNewEmployee @WEPK ="+STR(wepk)+",@WEFNAME ='"+wefname+"',@WELNAME ='"+welname+"',@WEEMAIL ='"+weemail+"',@WEUSERID ='"+weuserid+"',@WEPHONE='"+wephone+"',@RETCODE =0"
nsucc=SQLEXEC(nhandle,nquery)
SQLDISCONNECT(nhandle)
IF nSucc<0
m.errclose=.f.
IF !USED("errorlog")
USE tisdata!errorlog IN SELECT(1)
m.errclose=.t.
ENDIF
SELECT errorlog
INSERT INTO errorlog (date, time, program,source,user) ;
values (DATE(), TIME(), 'EMPLOYEE_INSERT_TRIGGER','nSucc<0 PARAMS: '+STR(wepk)+wefname+welname+weemail+WEUSERID+WEPHO NE,GETENV("username"))
IF m.errclose
USE IN errorlog
ENDIF
ENDIF
RETURN
SQL SERVER Stored Procedure called from FOXPRO Stored Procedure
CREATE procedure ewo_sp_insertNewEmployee (
@WEPK int,
@WEFNAME char(20),
@WELNAME char(20),
@WEEMAIL char(50),
@WEUSERID char(15),
@WEPHONE char(25),
@RETCODE int OUTPUT
)
AS
insert into WO_EMP (
WE_PK,
WE_FNAME,
WE_LNAME,
WE_EMAIL,
WE_USERID,
WE_PHONE
)
VALUES (
@WEPK,
@WEFNAME,
@WELNAME,
@WEEMAIL,
@WEUSERID,
@WEPHONE
)
IF @@ERROR <> 0
BEGIN
SET @RETCODE=@@ERROR
END
ELSE
BEGIN
-- SUCCESS!!
SET @RETCODE=0
END
return @RETCODE
GO
View 2 Replies
View Related
Mar 28, 2007
I have a stored procedure that calls a msdb stored procedure internally. I granted the login execute rights on the outer sproc but it still vomits when it tries to execute the inner. Says I don't have the privileges, which makes sense.
How can I grant permissions to a login to execute msdb.dbo.sp_update_schedule()? Or is there a way I can impersonate the sysadmin user for the call by using Execute As sysadmin some how?
Thanks in advance
View 9 Replies
View Related
Jul 29, 2015
I am replicating a stored procedure execution, which builds and executes the following dynamic SQL command:
IF EXISTS (select * from MyDB..sysfiles sf (nolock) where name = 'MyDB_201201')
ALTER DATABASE [MyDB] REMOVE FILE [MyDB_201201]
IF EXISTS (select * from MyDB..sysfilegroups sfg (nolock)
where groupname = 'MyDB_201201' and sfg.groupname not in(
SELECT distinct fg.name file_group_name
[Code] ....
I can run this SP with no errors on both the publisher and the subscriber. However, when I try to replicate the execution of this SP, I get an error in replication monitor:
ALTER DATABASE statement not allowed within multi-statement transaction. (Source: MSSQLServer, Error number: 226)
How can I change my code to workaround this? Perhaps some explicit transactions?
View 6 Replies
View Related
Sep 20, 2007
SQL server agent has to be executed form a user other than windows admin. A SSIS where an excel file is loaded into a SQL Server table has to be scheduled by a job.SSIS runs fine alone in the server but fails when called by agent job. http://support.microsoft.com/?kbid=918760#appliesto article has this specific error described but in a 64 bit server this error cannot be rectified. Any ideas?
View 1 Replies
View Related
Aug 29, 2007
I have a stored procedure I created in SQL server 2005. Now I need to call the stored procedure in C#. Can someone help me out here? What is the C# code I need to call this stored procedure? I have never done this before and need some help.
CREATE PROCEDURE [dbo].[MarketCreate]
( @MarketCode nvarchar(20), @MarketName nvarchar(100), @LastUpdateDate nvarchar(2),)
ASINSERT INTO Market( MarketCode MarketName LastUpdateDate)VALUES( @MarketCode @MarketName @LastUpdateDate
)
View 5 Replies
View Related
Mar 31, 2006
I have created a stored procedure only with an insert statement in sql server 2005.
How can i call this stored procedure through code in ASP.NET page using vb.
i want to pass one parameter that comes from a text box in asp.net page.
my emailid is: g12garg@yahoo.co.in pls reply.
Thank you
Gaurav
View 2 Replies
View Related
Aug 7, 2001
Does anyone give me syntax for adding a bcp script within a stored procedure..I had done it once 3 yrs back does'nt seem to work now, and I do not know where I am going wrong??
Thanks
View 2 Replies
View Related
Aug 7, 2001
Hi, If I have a dll file and I know the interface of that dll file. How can I use stored procedure to call this dll file? Thank you.
View 1 Replies
View Related
Aug 7, 2001
Hi, If I have a dll file and I know the interface of that dll file. How can I use stored procedure to call this dll file? Thank you.
View 2 Replies
View Related
Nov 9, 2005
Greetings,
Even though this may be not right place with this issue I would like to try!
I facing with the problem “Object Variable or With Block variable not set” while I am trying to execute the stored procedure from Ms. Access form.
I need some help very badly or maybe a good sample of code that works in this issue is very welcome.
Many thanks in Advance
View 1 Replies
View Related
Jul 4, 2006
hi,
i created a stored procedure and below is the code segment of it;
create Procedure test1
@price as varchar(50) output,
@table as varchar(50) = ''
AS
Declare @SQL_INS VarChar(1000)
SELECT @SQL_INS = 'select ['+@price+'] from ['+@table+']'
Exec (@SQL_INS)
Procedure gets 2 parameters, one of them is just INPUT parameter and the other one is both OUTPUT and INPUT. What i wanna do is to get the result set of this procedure to use in my application.
View 5 Replies
View Related
Dec 11, 2007
Hi, i wanna know if we can call a stored procedure from another one. If yes, what's the syntax?
Thanks
View 3 Replies
View Related
Oct 6, 2007
Respected Sir/MAm
i am working on VC++ (visual studio2005 with sql200).i have created one stored procedure to insert data into table.
i want to call this strd procedure in the VC++ main.cpp file....
Could you please help me for the correct syntax code with example.
Thank You.
Upali
View 4 Replies
View Related
Apr 7, 2008
Hi there. My problem is: I have two stored procedures.
1. SELECT A FROM B
2. SELECT C FROM D WHERE A = EXEC First procedure
The meaning: First procedure gets some Id from B table. The second one gets a DataSet by this Id number. The problem is that when I getting an Id from first proc I use SELECT, than in the second one I use EXEC, and in the end, seconf procedure returns two DataSets. The first contains an Id from first procedure, second contains a valid DataSet. Therefore my application falls because it suppose that valid data in first DataSet. Hoow can I call to stored procedure from another stored procedure without creating two DataSets?
P.S. I already tried to use return instead of select in the first procedure. Same result.
Thank you
View 8 Replies
View Related
Jan 29, 2008
I need to call a stored procedure to insert data into a table in SQL Server from SSIS data flow task.
I am currently trying to use OLe Db Destination, but I am not sure how to map inputs to OLE DB Destination to my stored procedure insert.
Thanks
View 6 Replies
View Related
Aug 3, 2015
We are using Microsoft jdbc driver 4.1 connecting to SQL 2012, which has a linked server to another SQL 2012 server.Will linked server calls work with kerberos authentication using Microsoft jdbc driver 4.1? connection string looks like this:
jdbc:sqlserver://SQL01;database= product_db; integrated Security= true;authenticationScheme=JavaKerberos..We have the linked server connection configured to use "Be made using the login's current security context"
Date      8/3/2015 4:19:56 PM
Log      SQL Server (Current - 8/3/2015 3:49:00 PM)
Source      Logon
Message
Login failed for user 'NT AUTHORITYANONYMOUS LOGON'. Reason: Could not find a login matching the name provided. [CLIENT: 10.196.21.4]
View 2 Replies
View Related
Jul 10, 2006
Hi,
I would like to trigger a DTS or a stored procedure from asp.net 1.1 BUT
I don't want to wait for it to finish. In fact the DTS/Storeproc calculates values into different tables.
Those values are not needed immediately. The calculation takes between 20 or 30 minutes.
Do you have any idea how to do it ?
Thanks
View 3 Replies
View Related
Oct 26, 2006
I have gridview display a list of users. I have added a column for a check box. If the box is checked I move the users to another table.I need to pass some parameter of or each row to a stored proc. My question. In the loop where I check if the checkbox is selected I need to call the stored procedure.Currently I do an open and closed inside the loop.What is best and most effficent method of doing this should I open and close the connection outside the loop and change the procs parameters as loop through. System.Data.SqlClient.SqlConnection conn =
new System.Data.SqlClient.SqlConnection(
System.Configuration.ConfigurationManager.ConnectionStrings["connString"].ConnectionString);
System.Data.SqlClient.SqlCommand commChk = new System.Data.SqlClient.SqlCommand("storedProc", conn);
commChk.CommandType = System.Data.CommandType.StoredProcedure;
commChk.Parameters.AddWithValue("@mUID", ddMainUser.SelectedValue.ToString());
commChk.Parameters.AddWithValue("@sUId", gvUsers.Rows[i].Cells[2].Text);
commChk.Connection.Open();
commChk.ExecuteNonQuery();
conn.Close(); If so exactly how do I do this? How do I reset the parmaters for the proc? I haven't done this before where I need to loop through passing parameter to the same proc. thanks
View 2 Replies
View Related
Jan 19, 2007
Hi All,
I'll admit that I'm not the greatest at stored procedure/functions but I want to learn as much as possible. So I have two questions:
1) I had VS2005 autogenerate a sqldatasource that created Select/Insert/Update stored procedures. When Updating a record and calling the stored procedure, I want to query another table (we'll call it tblBatchNo) that has only one record, Batchno. I want to put that current batchno into the Update statement and update the record with the current batchno. Can someone point me in the right direction? Remember that I'm still a beginner on this subject.
2) Can someone provide any links to online tutorials on t-sql?
Thanks in advance.
Curtis
View 2 Replies
View Related
Feb 13, 2007
Dear Masters;
How can I call a stored procedure with VB code?
Thanks
View 2 Replies
View Related
Nov 12, 2007
Hi.....I have problem and I need your helpI stored a procedure in the Projects Folder in my computerand I want to return the procedure result in a column inside tableHow I can do that?????????thank you
View 2 Replies
View Related
Feb 15, 2000
Can DTS make a call to a stored procedure on an AS/400 and accept data from that call. I need to access the AS/400 through OLE/DB for AS/400, execute the call to a stored procedure (the AS/400 stored procedure gets the data from DB2/400, executes some business logic, then presents the record set), and grab the record set returned and dump it into a SQL 7.0 table.
View 1 Replies
View Related
Feb 20, 2003
I'm using the sp_OAMethod method to call a method called "getDesc" from within a VB .dll I created. Within the .dll file, the method is called "getDesc()", but for some reason I'm getting an error saying it is an unknown name. I am able to create the object without errors, so I know the .dll is correctly registered and is being found by the server. Any idea what would cause this error when I know the method name is correct? The code I use is below (without error handling to make it shorter):
-- Decalre variables
DECLARE @object INT
DECLARE @hr INT
DECLARE @property VARCHAR(255)
DECLARE @return VARCHAR(255)
DECLARE @src VARCHAR(255)
DECLARE @desc VARCHAR(255)
-- Create object
EXEC @hr = sp_OACreate 'SQLActiveXTest.SQLActiveXTestClass', @object OUT
-- Call method
EXEC @hr = sp_OAMethod @object, 'getDesc', @return OUT
-- Destroy object
EXEC @hr = sp_OADestroy @object
View 1 Replies
View Related
Jan 31, 2006
Hello;
I am using an Access 2003 front-end, and an SQL backend to run my application. I have a pretty good handle on using stored procedures and assigning variables for criteria within the SPROC.
I am however having a problem when I try to use a variable in place of a named procedure. For example I have a function that runs 2 procedures, therefore I "Call" the function with the code that runs my procedure, and simply change the name of the SPROC with each call. My problem is that I cannot figure out the syntax to use a variable for the named procedure. My code always errors on the line "objConn.MySProc MyCalendar, objRs" because MySproc is of course not a named procedure.
So how do I refer to a procedures name using a variable?
Here's my code;
Function LieuBen()
MyCalendar = CurrTSCalendar
Call PopulateTmpFile("sp_DelTmpProctimesheetCalc")
Call PopulateTmpFile("sp_PopTmpCalcLieuBen")
End Function
Function PopulateTmpFile(MySProc As Variant)
Dim sp_PopulateTempOTTable As String
Const DS = "SOS-1"
Const db = "TIMS"
Const DP = "SQLOLEDB"
Dim objConn As New ADODB.Connection
Dim objRs As New ADODB.Recordset
Dim objComm As New ADODB.Command
ConnectionString = "Provider=" & DP & _
";Data Source=" & DS & _
";Initial Catalog=" & db & _
";Integrated Security=SSPI;"
' Connect to the data source.
objConn.Open ConnectionString
' Set a stored procedure
objComm.CommandText = MySProc
objComm.CommandType = adCmdStoredProc
Set objComm.ActiveConnection = objConn
objConn.MySProc MyCalendar, objRs
objConn.Close
Set objRs = Nothing
Set objConn = Nothing
Set objComm = Nothing
End Function
View 1 Replies
View Related
Sep 7, 2006
Hello all,
does anyone know if it's possible to call a stored procedure from a view.
Thnx,
Patrick
View 9 Replies
View Related
Sep 12, 2012
How to call a sql function in stored procedure using Sqlserver 2008?
View 4 Replies
View Related
Jul 18, 2014
I have created a DTS package which stores file data into Database table.
I want to pass file name dynamically to stored procedure from which DTS retrieve data and store it in table.
View 3 Replies
View Related
