Stored Procedure In Query Analyzer Vs Linked Procedure In MS Access
Jan 12, 2007
For some reason, I run a stored procedure in Query Analyzer and it works fine. When I run the very same procedure in MS access by clicking on its link I have to run it twice. The first run gives me the message that the stored procedure ran correctly but returned no records. The second run gives me the correct number of records but I have to run it twice. I am running month-to-month data. The first run is Jan thru March. Jan and Feb have no records so I run three months on the first set. The ensuing runs are individual months from April onward. The output is correct but any ideas on why I have to do it twice in Access? I am a bit new to stored procedures but my supervisor assures me that it should be exactly the same.
ddave
View 2 Replies
ADVERTISEMENT
Jul 28, 2004
Hi all,
I have a problem with a stored procedure that executes properly when running it in Query Analyzer. When I call the SP from an ASP.NET application, it doesn't seem to run properly. I have verified that the parameter values are correct, but there is one update command that does not update any rows when it executes although it should. I tried stepping through the SP from within Visual Studio and it still does not work properly even though all parameters have the correct values.
Why would a SP execute properly when used in QA but not when an application executes it?
View 4 Replies
View Related
Apr 17, 2001
I am trying to see if there is anyway to BCP a stored procedure from SQL query analyzer. The statement works fine from the command prompt or from within DTS but not from SQL QA.
The bcp statement is as follows:
master..xp_cmdshell "bcp "exec pubs.dbo.sp_employee" queryout dev01e$emp.txt /c /o dev01e$emp.out /T /SDEV01"
sp_employee has the script:
SELECT * FROM EMPLOYEE
Any help is appreciated. Thanks.
AJ
View 2 Replies
View Related
Jun 3, 2007
I am trying to execute a stored procedure in Query anaylzer for SQL server 2005. I am not sure I am doing it correctly though, because I am getting an error message. Here is the command I am typing:
execute DetaHistoryGet(84,885,34,"EndDate")
Here is the error message I get:
Msg 102,Level 15, State 1, Line 1
Incorrect syntax near '84'
Here are a few lines from the stored procedure. I have not included it all here.
CREATE PROCEDURE [dbo].[DetaHistoryGet]
( @MarketId decimal, @OwnerId decimal, @QuarterId decimal, @SortExp nvarchar(50) )
AS SET NOCOUNT ON
View 3 Replies
View Related
Jul 20, 2005
hi ng,i am newbie to sqlserver and my problem seems simple, but i didn't findinformation about it:How can i display theRETURN @xvalue of a stored procedure in the sql analyzer of the sqlserver?thanks a lot,d
View 2 Replies
View Related
Feb 21, 2002
HI,
I have an interesting situation. I have created a stored procedure which has a select union query and it accepts some parameters. When I execute this procedure it takes 8 minutes. When I copy the script in stored procedure and run it directly in Query Analyzer it takes 2 1/2 minutes?? Same numbers of rows are returned either way in the result set with about 13,000.
I cannot figure this out and it is almost the same thing except that in Query Analyzer I declare the parameters variables and its values?
Any feedback would be appreciated!
Thanks in advance...
View 2 Replies
View Related
Jun 13, 2007
Hi. I am having a problem using DTSRun in a stored procedure or in query analyzer.
My DTS package truncates a table and then imports data from an Excel spreadsheet into the table.
The DTS package runs fine if I run it manually on Enterprise Manager or if I use DTSRun in a command prompt.
However when I try to run the package in a stored procedure or in query analyzer as follows:
ie Exec master..xp_cmdshell 'DTSRUN /SSQL SERVER /NPackageName /Uuserid /Ppassword'
The package does not return an error but it does not actually import any data from the Excel spreadsheet. It is able to truncate the table without any problem.
I've tried all kinds of combination of using Windows user id that I know has access to the excel file instead of a sql login.
I've run Filemon on the excel file and it seems like DTS is not accessing the file when I use the stored proc or query analyzer method.
If anyone could shed any light on this I would be most grateful. Thanks.
View 4 Replies
View Related
Jul 20, 2005
Hi group,I have a select statement that if run against a 1 million recorddatabase directly in query analyzer takes less than 1 second.However, if I execute the select statement in a stored procedureinstead, calling the stored proc from query analyzer, then it takes12-17 seconds.Here is what I execute in Query Analyzer when bypassing the storedprocedure:USE VerizonGODECLARE @phonenumber varchar(15)SELECT @phonenumber = '6317898493'SELECT Source_Identifier,BADD_Sequence_Number,Record_Type,BAID ,Social_Security_Number ,Billing_Name,Billing_Address_1,Billing_Address_2,Billing_Address_3,Billing_Address_4,Service_Connection_Date,Disconnect_Date,Date_Final_Bill,Behavior_Score,Account_Group,Diconnect_Reason,Treatment_History,Perm_Temp,Balance_Due,Regulated_Balance_Due,Toll_Balance_Due,Deregulated_Balance_Due,Directory_Balance_Due,Other_Category_BalanceFROM BadDebtWHERE (Telephone_Number = @phonenumber) OR (Telephone_Number_Redef =@phonenumber)order by Service_Connection_Date descRETURNGOHere is what I execute in Query Analyzer when calling the storedprocedure:DECLARE @phonenumber varchar(15)SELECT @phonenumber = '6317898493'EXEC Verizon.dbo.baddebt_phonelookup @phonenumberHere is the script that created the stored procedure itself:CREATE PROCEDURE dbo.baddebt_phonelookup @phonenumber varchar(15)ASSELECT Source_Identifier,BADD_Sequence_Number,Record_Type,BAID ,Social_Security_Number ,Billing_Name,Billing_Address_1,Billing_Address_2,Billing_Address_3,Billing_Address_4,Service_Connection_Date,Disconnect_Date,Date_Final_Bill,Behavior_Score,Account_Group,Diconnect_Reason,Treatment_History,Perm_Temp,Balance_Due,Regulated_Balance_Due,Toll_Balance_Due,Deregulated_Balance_Due,Directory_Balance_Due,Other_Category_BalanceFROM BadDebtWHERE (Telephone_Number = @phonenumber) OR (Telephone_Number_Redef =@phonenumber)order by Service_Connection_Date descRETURNGOUsing SQL Profiler, I also have the execution trees for each of thesetwo different ways of running the same query.Here is the Execution tree when running the whole query in theanalyzer, bypassing the stored procedure:--------------------------------------Sort(ORDER BY:([BadDebt].[Service_Connection_Date] DESC))|--Bookmark Lookup(BOOKMARK:([Bmk1000]),OBJECT:([Verizon].[dbo].[BadDebt]))|--Sort(DISTINCT ORDER BY:([Bmk1000] ASC))|--Concatenation|--IndexSeek(OBJECT:([Verizon].[dbo].[BadDebt].[Telephone_Index]),SEEK:([BadDebt].[Telephone_Number]=[@phonenumber]) ORDERED FORWARD)|--IndexSeek(OBJECT:([Verizon].[dbo].[BadDebt].[Telephone_Redef_Index]),SEEK:([BadDebt].[Telephone_Number_Redef]=[@phonenumber]) ORDEREDFORWARD)--------------------------------------Finally, here is the execution tree when calling the stored procedure:--------------------------------------Sort(ORDER BY:([BadDebt].[Service_Connection_Date] DESC))|--Filter(WHERE:([BadDebt].[Telephone_Number]=[@phonenumber] OR[BadDebt].[Telephone_Number_Redef]=[@phonenumber]))|--Compute Scalar(DEFINE:([BadDebt].[Telephone_Number_Redef]=substring(Convert([BadDebt].[Telephone_Number]),1, 10)))|--Table Scan(OBJECT:([Verizon].[dbo].[BadDebt]))--------------------------------------Thanks for any help on my path to optimizing this query for ourproduction environment.Regards,Warren WrightScorex Development Team
View 5 Replies
View Related
Mar 7, 2008
Hi i have a script works in sql query analyzer;
declare @id decimal
declare mycur CURSOR SCROLL for select myRowID from myTable order by myRowID
open mycur;
Fetch ABSOLUTE 30 from mycur into @id
close mycur;
deallocate mycur;
select @id
this script turns me a value.
i create a stored procedure from above script and its syntax is ok;
CREATE PROCEDURE SELECT_MyRow
AS
declare @cur cursor
declare @RowID decimal
set @cur = CURSOR SCROLL
for select myRowID from myTable order by myRowID
open @cur
Fetch ABSOLUTE 30 from @cur into @RowID
close @cur
deallocate @cur
select @RowID
GO
my c# code using stored procedure is below;
Code Snippet
try
{
OleDbCommand cmd = new OleDbCommand("SELECT_MyRow", myconnection);
cmd.CommandType = CommandType.StoredProcedure;
myconnection.Open();
OleDbDataReader reader = cmd.ExecuteReader();
MessageBox.Show(reader.GetName(0));//here fails
while (reader.Read())
{
MessageBox.Show(reader.GetDecimal(0).ToString());
}
reader.Close();
myconnection.Close();
}
catch(Exception ex)
{
MessageBox.Show(ex.Message);
}
The code above fails because reader reads no values, error message is "No data exists for the row/column"
but i know exists. Can anyone help me, what is the difference between stored procedure and script ?
View 4 Replies
View Related
Jul 23, 2005
Hi,Is there a way to align/indent the stored procedure script ( like VBAAdd-Ins Smart Indenter )Thanks, Eugene
View 2 Replies
View Related
Jul 20, 2004
I tried to display return value from stored procedure output parameter in Query Analyzer, but I do not know how to do it. Below is my stored procedure:
CREATE PROCEDURE UserLogin
(
@Email nvarchar(100),
@Password nvarchar(50),
@UserName nvarchar(100) OUTPUT
)
AS
SELECT @UserName = Name FROM Users
WHERE Email = @Email AND Password = @Password
GO
If I run the query in Query Analyzer directly, I can display @UserName:
DECLARE @UserName as nvarchar(100)
SELECT @UserName = Name FROM Users
WHERE Email = @Email AND Password = @Password
Select @UserName
But how can I display @UserName if I call stored procedure:
exec UserLogin 'email', 'password', ''
I believed it return int, right?
Thanks in advance for help.
View 2 Replies
View Related
Apr 7, 2008
Hi I've run into a slight problem with my stored procedure.If I fire a query in a stored procedure, how can I access the results of that query in the stored procedure itself ?? ALTER PROCEDURE GetDetailsOfWebsiteUserForMail @request_id intAS declare @k int select person_id from Website_user w, Request r where r.user_id=w.user_id and r.request_id=@request_id set @k =person_id /* What do I do here??, value returned is unique */ if (@k!=0) /* Exists */ begin /* do something */ end else /* entry on person table does not exist */ begin /* do something */
end
View 1 Replies
View Related
Nov 9, 2001
I am switching my database from MS access to SQL server, and i want the following query to br converted to SQL stored procedure
CREATE PROCEDURE FORUM_MESSAGE AS
SELECT *
FROM FORUM_MESSAGES
WHERE ID=MessageID;
here "MessageID" is a run time generated parameter, and is not a field in the database.
thanx
View 1 Replies
View Related
Jun 6, 2008
I'm trying to convert an Access database application to an ASP.NET application with SQL Server 2005 as the database backend. However, I'm having trouble converting some of the queries to SQL stored procedures. Here's an example Access query that I'm trying to convert into working SQL:
SELECT DISTINCTROW EXISTS (SELECT AccountCodeID
FROM AccountCodes
WHERE CodeID = 22 AND AccountCodes.AccountID = Accounts.AccountID) AS FullTimeInfo,
EXISTS (SELECT AccountCodeID
FROM AccountCodes
WHERE CodeID = 24 AND AccountCodes.AccountID = Accounts.AccountID) AS ShortTermInfo,
EXISTS (SELECT AccountCodeID
FROM AccountCodes
WHERE CodeID = 10 AND AccountCodes.AccountID = Accounts.AccountID) AS GeneralInfo,
Accounts.*
FROM Accounts
INNER JOIN AccountCodes
ON Accounts.AccountID = AccountCodes.AccountID
WHERE (((Accounts.SummitID)=@SummitID) AND ((AccountCodes.CodeID)=10 Or (AccountCodes.CodeID)=22 Or (AccountCodes.CodeID)=24))
ORDER BY Accounts.LastName, Accounts.FirstName
My understanding is that EXISTS can only be used in the WHERE clause in SQL. Any suggestions on how to properly rewrite this?
Jon
View 1 Replies
View Related
Jan 24, 2004
Hi All
I have a stored procedure, sp_GetNameDetail, which return a one row, multiple columns result set.
Yet I have another storede procedure which would call sp_GetNameDetail, and would like to access this result set. Is there a way I can do this?
Thanks,
View 1 Replies
View Related
May 26, 2008
Just wonder whether is there any indicator or system parameters that can indicate whether stored procedure A is executed inside query analyzer or executed inside application itself so that if execution is done inside query analyzer then i can block it from being executed/retrieve sensitive data from it?
What i'm want to do is to block someone executing stored procedure using query analyzer and retrieve its sensitive results.
Stored procedure A has been granted execution for public user but inside application, it will prompt access denied message if particular user has no rights to use system although knew public user name and password. Because there is second layer of user validation inside system application.
However inside query analyzer, there is no way control execution of stored procedure A it as user knew the public user name and password.
Looking forward for replies from expert here. Thanks in advance.
Note: Hope my explaination here clearly describe my current problems.
View 4 Replies
View Related
Jan 23, 2008
Has anyone encountered cases in which a proc executed by DTS has the following behavior:
1) underperforms the same proc when executed in DTS as opposed to SQL Server Managemet Studio
2) underperforms an ad-hoc version of the same query (UPDATE) executed in SQL Server Managemet Studio
What could explain this?
Obviously,
All three scenarios are executed against the same database and hit the exact same tables and indices.
Query plans show that one step, a Clustered Index Seek, consumes most of the resources (57%) and for that the estimated rows = 1 and actual rows is 10 of 1000's time higher. (~ 23000).
The DTS execution effectively never finishes even after many hours (10+)
The Stored procedure execution will finish in 6 minutes (executed after the update ad-hoc query)
The Update ad-hoc query will finish in 2 minutes
View 1 Replies
View Related
Mar 11, 2006
HiWe're trying to use call a stored procedure to update information in aremote Ingres database. We've linked the server, and can readinformation using SELECT * FROM OPENQUERY (..........), but we can'tfind a suitable syntax for executing a procedure.Using SELECT * FROM OPENQUERY and passing the EXEC statement in astring gives a message about not returning any columns - not surprisingas there aren't any, and trying to execute the procedure more directlyusing:-EXECUTE abrs..vipdba.ats_reader_pi0 ........Gives the errorCould not execute procedure 'ats_reader_pi0' on remote server 'abrs'.[OLE/DB provider returned message: Parameter type cannot be determinedfor at least one variant parameter.]Any bright ideas?Chloe
View 1 Replies
View Related
May 23, 2007
Hello to all,
I have a stored procedure. If i give this command exce ShortestPath 3418, '4125', 5 in a script and excute it. It takes more 30 seconds time to be excuted.
but i excute it with the same parameters direct in Microsoft SQL Server Management Studio , It takes only under 1 second time
I don't know why?
Maybe can somebody help me?
thanks in million
best Regards
Pinsha
My Procedure Codes are here:set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[ShortestPath] (@IDMember int, @IDOther varchar(1000),@Level int, @Path varchar(100) = null output )
AS
BEGIN
if ( @Level = 1)
begin
select @Path = convert(varchar(100),IDMember)
from wtcomValidRelationships
where wtcomValidRelationships.[IDMember]= @IDMember
and PATINDEX('%'+@IDOther+'%',(select RelationshipIDs from wtcomValidRelationships where IDMember = @IDMember) ) > 0
end
if (@Level = 2)
begin
select top 1 @Path = convert(varchar(100),A.IDMember)+'-'+convert(varchar(100),B.IDMember)
from wtcomValidRelationships as A, wtcomValidRelationships as B
where A.IDMember = @IDMember and charindex(convert(varchar(100),B.IDMember),A.RelationshipIDS) > 0
and PATINDEX('%'+@IDOther+'%',B.RelationshipIDs) > 0
end
if (@Level = 3)
begin
select top 1 @Path = convert(varchar(100),A.IDMember)+ '-'+convert(varchar(100),B.IDMember)+'-'+convert(varchar(100),C.IDMember)
from wtcomValidRelationships as A, wtcomValidRelationships as B, wtcomValidRelationships as C
where A.IDMember = @IDMember and charindex(convert(varchar(100),B.IDMember),A.RelationshipIDS) > 0
and charindex(convert(varchar(100),C.IDMember),B.RelationshipIDs) > 0 and PATINDEX('%'+@IDOther+'%',C.RelationshipIDs) > 0
end
if ( @Level = 4)
begin
select top 1 @Path = convert(varchar(100),A.IDMember)+ '-'+convert(varchar(100),B.IDMember)+'-'+convert(varchar(100),C.IDMember)+'-'+convert(varchar(100),D.IDMember)
from wtcomValidRelationships as A, wtcomValidRelationships as B, wtcomValidRelationships as C, wtcomValidRelationships as D
where A.IDMember = @IDMember and charindex(convert(varchar(100),B.IDMember),A.RelationshipIDS) > 0
and charindex(convert(varchar(100),C.IDMember),B.RelationshipIDs) > 0 and charindex(convert(varchar(100),D.IDMember), C.RelationshipIDs) > 0
and PATINDEX('%'+@IDOther+'%',D.RelationshipIDs) > 0
end
if (@Level = 5)
begin
select top 1 @Path = convert(varchar(100),A.IDMember)+ '-'+convert(varchar(100),B.IDMember)+'-'+convert(varchar(100),C.IDMember)+'-'+convert(varchar(100),D.IDMember)+'-'+convert(varchar(100),E.IDMember)
from wtcomValidRelationships as A, wtcomValidRelationships as B, wtcomValidRelationships as C, wtcomValidRelationships as D, wtcomValidRelationships as E
where A.IDMember = @IDMember and charindex(convert(varchar(100),B.IDMember),A.RelationshipIDS) > 0
and charindex(convert(varchar(100),C.IDMember),B.RelationshipIDs) > 0 and charindex(convert(varchar(100),D.IDMember), C.RelationshipIDs) > 0
and charindex(convert(varchar(100),E.IDMember),D.RelationshipIDs) > 0 and PATINDEX('%'+@IDOther+'%',E.RelationshipIDs) > 0
end
if (@Level = 6)
begin
select top 1 @Path = '' from wtcomValidRelationships
end
END
View 6 Replies
View Related
Jul 3, 2006
What is the syntax for creating a update trigger and passing the values which were updated to a stored procedure on a linked server?? Specifically need syntax for updated value, as well as the syntax for executing the stored proc on the linked server.
Thank you
View 1 Replies
View Related
Jul 18, 2006
Is there a way to bypass the syntax checking when adding a stored procedure via a script?
I have a script that has a LINKed server reference (see below) .
INSERT
INTO ACTDMSLINKED.ACTDMS.DBO.COILS ..etc.
ACTDMSLINKED does not exist at the time I need to add the stored procedure that references it.
PLEASE to not tell me to add the LINK and then run the script. This is not an option in this scenerio.
Thanks,
Terry
View 4 Replies
View Related
Mar 3, 2008
Hi all,
I have 2 sets of sql code in my SQL Server Management Stidio Express (SSMSE):
(1) /////--spTopSixAnalytes.sql--///
USE ssmsExpressDB
GO
CREATE Procedure [dbo].[spTopSixAnalytes]
AS
SET ROWCOUNT 6
SELECT Labtests.Result AS TopSixAnalytes, LabTests.Unit, LabTests.AnalyteName
FROM LabTests
ORDER BY LabTests.Result DESC
GO
(2) /////--spTopSixAnalytesEXEC.sql--//////////////
USE ssmsExpressDB
GO
EXEC spTopSixAnalytes
GO
I executed them and got the following results in SSMSE:
TopSixAnalytes Unit AnalyteName
1 222.10 ug/Kg Acetone
2 220.30 ug/Kg Acetone
3 211.90 ug/Kg Acetone
4 140.30 ug/L Acetone
5 120.70 ug/L Acetone
6 90.70 ug/L Acetone
/////////////////////////////////////////////////////////////////////////////////////////////
Now, I try to use this Stored Procedure in my ADO.NET-VB 2005 Express programming:
//////////////////--spTopSixAnalytes.vb--///////////
Public Class Form1
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim sqlConnection As SqlConnection = New SqlConnection("Data Source = .SQLEXPRESS; Integrated Security = SSPI; Initial Catalog = ssmsExpressDB;")
Dim sqlDataAdapter As SqlDataAdapter = New SqlDataAdaptor("[spTopSixAnalytes]", sqlConnection)
sqlDataAdapter.SelectCommand.Command.Type = CommandType.StoredProcedure
'Pass the name of the DataSet through the overloaded contructor
'of the DataSet class.
Dim dataSet As DataSet ("ssmsExpressDB")
sqlConnection.Open()
sqlDataAdapter.Fill(DataSet)
sqlConnection.Close()
End Sub
End Class
///////////////////////////////////////////////////////////////////////////////////////////
I executed the above code and I got the following 4 errors:
Error #1: Type 'SqlConnection' is not defined (in Form1.vb)
Error #2: Type 'SqlDataAdapter' is not defined (in Form1.vb)
Error #3: Array bounds cannot appear in type specifiers (in Form1.vb)
Error #4: 'DataSet' is not a type and cannot be used as an expression (in Form1)
Please help and advise.
Thanks in advance,
Scott Chang
More Information for you to know:
I have the "ssmsExpressDB" database in the Database Expolorer of VB 2005 Express. But I do not know how to get the SqlConnection and the SqlDataAdapter into the Form1. I do not know how to get the Fill Method implemented properly.
I try to learn "Working with SELECT Statement in a Stored Procedure" for printing the 6 rows that are selected - they are not parameterized.
View 11 Replies
View Related
Apr 1, 2008
Hi everybody,
How can I access a webservice from inside a stored procedure? any help is greatly appriciated.
View 3 Replies
View Related
Jan 20, 2006
Hi everyone, im having alot of trouble trying to execute a stored proc from sql express. heres my code
DbProviderFactory db = DbProviderFactories.GetFactory("System.Data.SqlClient");
using(DbConnection conn = db.CreateConnection()){
ConnectionStringSettings s = ConfigurationManager.ConnectionStrings["constrolservicetest"];
conn.ConnectionString = s.ConnectionString;
conn.Open();
DbCommand cmd = conn.CreateCommand();
cmd.CommandText = "StoredProcedure1";
cmd.CommandType = CommandType.StoredProcedure;
DbParameter param = db.CreateParameter();
param.ParameterName = "@test";
param.Value = 2;
cmd.Parameters.Add(param);
cmd.ExecuteNonQuery();
}
****************************procedure code***************************
Create PROCEDURE dbo.StoredProcedure1
(
@test int
)
AS
Update temp
Set test = @test
******************************************************************
The code just doesn't update. I know my connection string is correct because i got the datareader to work, but i just cant get the stored proc to call. Any help will be greatly appreciated.
Thanks,
-D
View 1 Replies
View Related
Jul 27, 2001
Can someone tell me how to call a stored procedure from Access?
Thanks,
Dianne
View 1 Replies
View Related
Jun 30, 2004
Hi all,
I have an urgent problem. Can a stored procedure create a connection or access data from another database?
Thanks in advace. :)
View 11 Replies
View Related
Feb 8, 2008
Hi. I'm trying to write a stored procedure that will access another database on a different server. How can I set this up in a stored procedure? It is a SQL Server 2000 database and it will be using SQL server authentication. Thanks!
View 3 Replies
View Related
Mar 27, 2007
How can I access Oracle stored procedure from MS SQL Server?
View 1 Replies
View Related
Feb 22, 2006
I tried to write a CLR stored procedure using C# in SQL 2005 to access an Access
database.
When I use the OleDbConnection class in System.Data, the procedure throws SecurityException at runtime.
Output as following:
System.Security.SecurityException: Request for the permission of type 'System.Data.OleDb.OleDbPermission, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.
at System.Security.CodeAccessSecurityEngine.Check(Object demand, StackCrawlMark& stackMark, Boolean isPermSet)
at System.Security.PermissionSet.Demand()
at System.Data.Common.DbConnectionOptions.DemandPermission()
at System.Data.OleDb.OleDbConnection.PermissionDemand()
at System.Data.OleDb.OleDbConnectionFactory.PermissionDemand(DbConnection outerConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.OleDb.OleDbConnection.Open()
at Dbbest.Data.BulkStuff.bulkcopy(String source_oledb_connection_string, String source_table, String destination_table, Int32 batchSize, Int32 notifyAfter)
The action that failed was:
Demand
The type of the first permission that failed was:
System.Data.OleDb.OleDbPermission
The Zone of the assembly that failed was:
MyComputer
View 1 Replies
View Related
Nov 1, 2007
Hi all - I'm trying to optimized my stored procedures to be a bit easier to maintain, and am sure this is possible, not am very unclear on the syntax to doing this correctly. For example, I have a simple stored procedure that takes a string as a parameter, and returns its resolved index that corresponds to a record in my database. ie
exec dbo.DeriveStatusID 'Created'
returns an int value as 1
(performed by "SELECT statusID FROM statusList WHERE statusName= 'Created')
but I also have a second stored procedure that needs to make reference to this procedure first, in order to resolve an id - ie:
exec dbo.AddProduct_Insert 'widget1'
which currently performs:SET @statusID = (SELECT statusID FROM statusList WHERE statusName='Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID)
I want to simply the insert to perform (in one sproc):
SET @statusID = EXEC deriveStatusID ('Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID)
This works fine if I call this stored procedure in code first, then pass it to the second stored procedure, but NOT if it is reference in the second stored procedure directly (I end up with an empty value for @statusID in this example).
My actual "Insert" stored procedures are far more complicated, but I am working towards lightening the business logic in my application ( it shouldn't have to pre-vet the data prior to executing a valid insert).
Hopefully this makes some sense - it doesn't seem right to me that this is impossible, and am fairly sure I'm just missing some simple syntax - can anyone assist?
View 1 Replies
View Related
Apr 25, 2006
I have a Stored Procedure
CREATE PROCEDURE test
AS
BEGIN
SELECT Count(*) FROM dbo.test
END
I can using the unbox get the return value
but if i direct return a value form a Stored Procedure like this
CREATE PROCEDURE test
AS
BEGIN
return 100
END
I can not get the VALUE
I do not know how to
Please Help Me
thx
View 1 Replies
View Related
Apr 25, 2006
I have a Stored Procedure
CREATE PROCEDURE test
AS
BEGIN
SELECT Count(*) FROM dbo.test
END
I can using the unbox get the return value
but if i direct return a value form a Stored Procedure like this
CREATE PROCEDURE test
AS
BEGIN
return 100
END
I can not get the VALUE
I do not know how to
Please Help Me
thx
View 1 Replies
View Related
Sep 29, 2006
Hi,
I'm new to SQL server, still in the beginning stage of learning SQL Server. I'm here would like know, besides using the Connectivity from the DTS Designer to connect to different databases, is it possible to connect the database, i.e: Access via stored procedure? and how? Pls advise...
I'm have been trying to look for the solution via a lot of SQL Server site, but fail to get what I want.
What I'm trying to do is something like :
First connect to the Database and Query the data, after that insert it into another database....
View 3 Replies
View Related
