Weird Situation - Stored Procedure Executed Twice
Aug 16, 2006
In SQL 2005 I have a stored procedure as below:
@sub_no smallint OUTPUT
BEGIN
BEGIN TRANSACTION
INSERT...INTO
SET @user_no = (SELECT ...... FROM ....WHERE sub_no = @sub_no)
INSERT...INTO
EXE another_stored_procedure (it includes also BEGIN...COMMIT)
EXE another_stored_procedure (it includes also BEGIN...COMMIT)
SET @sub_no = .......
COMMIT TRANSACTION
When Visual Studio (ASP.NET 2005) is open and I run the program,
procedure is executed once without any problem. If I publish the
project and put files on another server (or even use the published
files from my machine) I have an error because stored procedure is
executed twice. @sub_no is used as input/output parameter.
I followed/trace the steps in procedure and it seems that procedure is
executed once with correct value of @sub_no. The second time procedure
is executed, the value that it was assigned before COMMIT is used,
which gives an error because the INSERT values have NULL values.
In ASP.NET I call the store procedure once.
What could be the reason ?
Thanks a lot for any help.
View 2 Replies
ADVERTISEMENT
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
Jun 21, 2004
We have 2 SQL tables being accessed through an Access form. The tables are an ORDER table and an ORDER-DETAIL table comprised of data regarding the Parts in any given Order. (Yes -- the classic Order-Entry situation.) The Access form is used to view/create new Orders, and shows ORDER data in fields, plus has a large field which presents a "spreadsheet"-like view of the related records from the ORDER-DETAIL table.
The users enter and modify data in the ORDER-DETAIL table directly through this "spreadsheet" in the Access form. However, because there is no PARTS table yet (that's part of what I'm working on), they have to enter part numbers and descriptions *manually* in each ORDER.
So... here's my question:
After I implement a PARTS table, I would like for users to be able to open an ORDER in the Access form, type in a Part # in a row of the ORDER-DETAIL "spreadsheet", and then have the rest of the row populate with the appropriate Part description and other data from the PARTS table. How do I go about making that a reality? Some kind of stored procedure triggered by a change in the Part # field? Ha ha – if so, I am clueless as to how to make that happen. ANY information would greatly appreciated!
Thanks!
whill96205 the Noob :confused:
View 3 Replies
View Related
Jun 19, 2008
hi,
i want log the query executed in stored procedure ..
any way of doing it in sql server 2000
regards.
View 1 Replies
View Related
Jan 6, 2005
CREATE PROCEDURE Proc_name
@_name nvarchar,
@LPrice money,
@HPrice money,
@Page int
AS
SET NOCOUNT ON
DECLARE @cPos int, @nPos int
SELECT @cPos = (@Page -1)*15
SELECT @nPos = (@Page*15)
SELECTa.Field2, a.Field3, b.Field3
FROM Tbl1 a
INNER JOIN Tbl2 b ON a.Field1 = b.Field1
WHERE(a.[Name] LIKE '%@_name%')
AND (a.ID BETWEEN @cPos AND @nPos)
AND (a.Price BETWEEN @LPrice AND @HPrice)
GO
if i remove @_name and put in '%%' it returns rows...but with @_name it doesnt work...any idea why?
check_syntax doesnt return any errors...so i dont kno waht to do
thanks
View 2 Replies
View Related
Apr 24, 2006
I have a stored procedure I'm using in a c# ASP.NET application. The SP is tied to an SQL DataSource which is, in turn, tied to a gridview control that displays the results obtained from the SP. The SP receives two parameters that are obtained from two dropdown list boxes.
ALTER PROCEDURE developers.fort_GetSearchResults
(
@propertyType int,
@county int
)
AS
IF ((@propertyType = 1) AND (@county = 1))
SELECT Experimental_Accommodation.id, Experimental_ContactDetails.name,
Experimental_ContactDetails.address, Experimental_ContactDetails.district,
Experimental_ContactDetails.town, Experimental_Counties.County,
Experimental_ContactDetails.postcode, Experimental_ContactDetails.telephone,
Experimental_ContactDetails.fax, Experimental_ContactDetails.email,
Experimental_ContactDetails.contactName,
Experimental_Accommodation.gradingDescription, Experimental_Accommodation.sleeps,
Experimental_Accommodation.pricing, Experimental_Accommodation.shortDescription,
Experimental_Accommodation.description, Experimental_Property_Type.name
AS Expr1, Experimental_Grading.grading, Experimental_Images.filename,
Experimental_Images.alt
FROM Experimental_Accommodation
INNER JOIN Experimental_ContactDetails
ON Experimental_Accommodation.contactDetails = Experimental_ContactDetails.id
INNER JOIN Experimental_Counties
ON Experimental_ContactDetails.county = Experimental_Counties.id
INNER JOIN Experimental_Property_Type
ON Experimental_Accommodation.propertyType = Experimental_Property_Type.id
INNER JOIN Experimental_Grading
ON Experimental_Accommodation.grading = Experimental_Grading.id
INNER JOIN Experimental_Images
ON Experimental_Grading.iconId = Experimental_Images.id
ORDER BY NEWID()
ELSE
IF ((@propertyType = 1) AND (@county != 1))
SELECT Experimental_Accommodation.id, Experimental_ContactDetails.name,
Experimental_ContactDetails.address, Experimental_ContactDetails.district,
Experimental_ContactDetails.town, Experimental_Counties.County,
Experimental_ContactDetails.postcode, Experimental_ContactDetails.telephone,
Experimental_ContactDetails.fax, Experimental_ContactDetails.email,
Experimental_ContactDetails.contactName,
Experimental_Accommodation.gradingDescription,
Experimental_Accommodation.sleeps, Experimental_Accommodation.pricing,
Experimental_Accommodation.shortDescription,
Experimental_Accommodation.description, Experimental_Property_Type.name
AS Expr1, Experimental_Grading.grading, Experimental_Images.filename,
Experimental_Images.alt
FROM Experimental_Accommodation
INNER JOIN Experimental_ContactDetails
ON Experimental_Accommodation.contactDetails = Experimental_ContactDetails.id
INNER JOIN Experimental_Counties
ON Experimental_ContactDetails.county = Experimental_Counties.id
INNER JOIN Experimental_Property_Type
ON Experimental_Accommodation.propertyType = Experimental_Property_Type.id
INNER JOIN Experimental_Grading
ON Experimental_Accommodation.grading = Experimental_Grading.id
INNER JOIN Experimental_Images
ON Experimental_Grading.iconId = Experimental_Images.id
WHERE (Experimental_Accommodation.propertyType = @propertyType)
ORDER BY NEWID()
ELSE
IF ((@county = 1) AND (@propertyType != 1))
SELECT Experimental_Accommodation.id, Experimental_ContactDetails.name, Experimental_ContactDetails.address, Experimental_ContactDetails.district,
Experimental_ContactDetails.town, Experimental_Counties.County, Experimental_ContactDetails.postcode, Experimental_ContactDetails.telephone,
Experimental_ContactDetails.fax, Experimental_ContactDetails.email, Experimental_ContactDetails.contactName,
Experimental_Accommodation.gradingDescription, Experimental_Accommodation.sleeps, Experimental_Accommodation.pricing,
Experimental_Accommodation.shortDescription, Experimental_Accommodation.description, Experimental_Property_Type.name AS Expr1,
Experimental_Grading.grading, Experimental_Images.filename, Experimental_Images.alt
FROM Experimental_Accommodation INNER JOIN
Experimental_ContactDetails ON Experimental_Accommodation.contactDetails = Experimental_ContactDetails.id INNER JOIN
Experimental_Counties ON Experimental_ContactDetails.county = Experimental_Counties.id INNER JOIN
Experimental_Property_Type ON Experimental_Accommodation.propertyType = Experimental_Property_Type.id INNER JOIN
Experimental_Grading ON Experimental_Accommodation.grading = Experimental_Grading.id INNER JOIN
Experimental_Images ON Experimental_Grading.iconId = Experimental_Images.id
WHERE (Experimental_ContactDetails.county = @county)
ORDER BY NEWID()
ELSE
IF ((@propertyType != 1) AND (@county != 1))
SELECT Experimental_Accommodation.id, Experimental_ContactDetails.name,
Experimental_ContactDetails.address, Experimental_ContactDetails.district,
Experimental_ContactDetails.town, Experimental_Counties.County,
Experimental_ContactDetails.postcode, Experimental_ContactDetails.telephone,
Experimental_ContactDetails.fax, Experimental_ContactDetails.email,
Experimental_ContactDetails.contactName, Experimental_Accommodation.gradingDescription,
Experimental_Accommodation.sleeps, Experimental_Accommodation.pricing,
Experimental_Accommodation.shortDescription, Experimental_Accommodation.description,
Experimental_Property_Type.name AS Expr1, Experimental_Grading.grading,
Experimental_Images.filename, Experimental_Images.alt
FROM Experimental_Accommodation
INNER JOIN Experimental_ContactDetails
ON Experimental_Accommodation.contactDetails = Experimental_ContactDetails.id
INNER JOIN Experimental_Counties
ON Experimental_ContactDetails.county = Experimental_Counties.id
INNER JOIN Experimental_Property_Type
ON Experimental_Accommodation.propertyType = Experimental_Property_Type.id
INNER JOIN Experimental_Grading
ON Experimental_Accommodation.grading = Experimental_Grading.id
INNER JOIN Experimental_Images
ON Experimental_Grading.iconId = Experimental_Images.id
WHERE (Experimental_Accommodation.propertyType = @propertyType)
AND (Experimental_ContactDetails.county = @county)
ORDER BY NEWID()
RETURN
Unfortunately only the first and fourth IF statement work. The second and third do not return any values and I'm unable to work out why.
View 1 Replies
View Related
Apr 24, 2006
I have a stored procedure I'm using in a c# ASP.NET application. The SP is tied to an SQL DataSource which is, in turn, tied to a gridview control that displays the results obtained from the SP. The SP receives two parameters that are obtained from two dropdown list boxes
Code:
ALTER PROCEDURE developers.fort_GetSearchResults
(
@propertyType int,
@county int
)
AS
IF ((@propertyType = 1) AND (@county = 1))
SELECT Experimental_Accommodation.id, Experimental_ContactDetails.name,
Experimental_ContactDetails.address, Experimental_ContactDetails.district,
Experimental_ContactDetails.town, Experimental_Counties.County,
Experimental_ContactDetails.postcode, Experimental_ContactDetails.telephone,
Experimental_ContactDetails.fax, Experimental_ContactDetails.email,
Experimental_ContactDetails.contactName,
Experimental_Accommodation.gradingDescription, Experimental_Accommodation.sleeps,
Experimental_Accommodation.pricing, Experimental_Accommodation.shortDescription,
Experimental_Accommodation.description, Experimental_Property_Type.name
AS Expr1, Experimental_Grading.grading, Experimental_Images.filename,
Experimental_Images.alt
FROM Experimental_Accommodation
INNER JOIN Experimental_ContactDetails
ON Experimental_Accommodation.contactDetails = Experimental_ContactDetails.id
INNER JOIN Experimental_Counties
ON Experimental_ContactDetails.county = Experimental_Counties.id
INNER JOIN Experimental_Property_Type
ON Experimental_Accommodation.propertyType = Experimental_Property_Type.id
INNER JOIN Experimental_Grading
ON Experimental_Accommodation.grading = Experimental_Grading.id
INNER JOIN Experimental_Images
ON Experimental_Grading.iconId = Experimental_Images.id
ORDER BY NEWID()
ELSE
IF ((@propertyType = 1) AND (@county != 1))
SELECT Experimental_Accommodation.id, Experimental_ContactDetails.name,
Experimental_ContactDetails.address, Experimental_ContactDetails.district,
Experimental_ContactDetails.town, Experimental_Counties.County,
Experimental_ContactDetails.postcode, Experimental_ContactDetails.telephone,
Experimental_ContactDetails.fax, Experimental_ContactDetails.email,
Experimental_ContactDetails.contactName,
Experimental_Accommodation.gradingDescription,
Experimental_Accommodation.sleeps, Experimental_Accommodation.pricing,
Experimental_Accommodation.shortDescription,
Experimental_Accommodation.description, Experimental_Property_Type.name
AS Expr1, Experimental_Grading.grading, Experimental_Images.filename,
Experimental_Images.alt
FROM Experimental_Accommodation
INNER JOIN Experimental_ContactDetails
ON Experimental_Accommodation.contactDetails = Experimental_ContactDetails.id
INNER JOIN Experimental_Counties
ON Experimental_ContactDetails.county = Experimental_Counties.id
INNER JOIN Experimental_Property_Type
ON Experimental_Accommodation.propertyType = Experimental_Property_Type.id
INNER JOIN Experimental_Grading
ON Experimental_Accommodation.grading = Experimental_Grading.id
INNER JOIN Experimental_Images
ON Experimental_Grading.iconId = Experimental_Images.id
WHERE (Experimental_Accommodation.propertyType = @propertyType)
ORDER BY NEWID()
ELSE
IF ((@county = 1) AND (@propertyType != 1))
SELECT Experimental_Accommodation.id, Experimental_ContactDetails.name, Experimental_ContactDetails.address, Experimental_ContactDetails.district,
Experimental_ContactDetails.town, Experimental_Counties.County, Experimental_ContactDetails.postcode, Experimental_ContactDetails.telephone,
Experimental_ContactDetails.fax, Experimental_ContactDetails.email, Experimental_ContactDetails.contactName,
Experimental_Accommodation.gradingDescription, Experimental_Accommodation.sleeps, Experimental_Accommodation.pricing,
Experimental_Accommodation.shortDescription, Experimental_Accommodation.description, Experimental_Property_Type.name AS Expr1,
Experimental_Grading.grading, Experimental_Images.filename, Experimental_Images.alt
FROM Experimental_Accommodation INNER JOIN
Experimental_ContactDetails ON Experimental_Accommodation.contactDetails = Experimental_ContactDetails.id INNER JOIN
Experimental_Counties ON Experimental_ContactDetails.county = Experimental_Counties.id INNER JOIN
Experimental_Property_Type ON Experimental_Accommodation.propertyType = Experimental_Property_Type.id INNER JOIN
Experimental_Grading ON Experimental_Accommodation.grading = Experimental_Grading.id INNER JOIN
Experimental_Images ON Experimental_Grading.iconId = Experimental_Images.id
WHERE (Experimental_ContactDetails.county = @county)
ORDER BY NEWID()
ELSE
IF ((@propertyType != 1) AND (@county != 1))
SELECT Experimental_Accommodation.id, Experimental_ContactDetails.name,
Experimental_ContactDetails.address, Experimental_ContactDetails.district,
Experimental_ContactDetails.town, Experimental_Counties.County,
Experimental_ContactDetails.postcode, Experimental_ContactDetails.telephone,
Experimental_ContactDetails.fax, Experimental_ContactDetails.email,
Experimental_ContactDetails.contactName, Experimental_Accommodation.gradingDescription,
Experimental_Accommodation.sleeps, Experimental_Accommodation.pricing,
Experimental_Accommodation.shortDescription, Experimental_Accommodation.description,
Experimental_Property_Type.name AS Expr1, Experimental_Grading.grading,
Experimental_Images.filename, Experimental_Images.alt
FROM Experimental_Accommodation
INNER JOIN Experimental_ContactDetails
ON Experimental_Accommodation.contactDetails = Experimental_ContactDetails.id
INNER JOIN Experimental_Counties
ON Experimental_ContactDetails.county = Experimental_Counties.id
INNER JOIN Experimental_Property_Type
ON Experimental_Accommodation.propertyType = Experimental_Property_Type.id
INNER JOIN Experimental_Grading
ON Experimental_Accommodation.grading = Experimental_Grading.id
INNER JOIN Experimental_Images
ON Experimental_Grading.iconId = Experimental_Images.id
WHERE (Experimental_Accommodation.propertyType = @propertyType)
AND (Experimental_ContactDetails.county = @county)
ORDER BY NEWID()
RETURN
Unfortunately only the first and fourth IF statements return a value. The second and third IF statements return nothing and I have no idea why.
View 2 Replies
View Related
Jul 26, 2006
I have 2 stored procedures:
the 1st uses the second sp. to fill a parameter.
the 2nd has one output parameter defined.
When I run the 2nd sp. on its own in MS SQL Server Manangement studio, there is no resultset shown.
BUT when the 2nd sp. is executed inside the 1st sp., I get a '3' in the resultset everytime the 2nd sp. executes. What is causing this?????
This is the way I execute the 2nd sp. inside the 1st sp.:
select 'TEST1'
execute dbo.uspMonthlyHeadCount @soe, @eoe, @Months, @count output
select 'TEST2'
Results are like this:
TEST1
3
TEST2
... (it's displayed more times because the sp. is inside a while)
Why is this happening and what can I do about it? (I'll provide more info if needed!)
View 1 Replies
View Related
Dec 2, 2005
Can a stored procedure be executed from within a select statement?
Given a store procedure named: sp_proc
I wish to do something like this:
For each row in the table
execute sp_proc 'parameter1', parameter2'...
end for
...but within a select statement. I know you can do this with stored functions, just not sure what the syntax is for a stored procedure.
View 2 Replies
View Related
Jul 23, 2005
HiOur SQL server has a lot of stored procedures and we want to get somecleaning up to be done. We want to delete the ones that have been notrun for like 2-3 months. How exactly will i find out which ones todelete. Enterprise manager only seesm to give the "Create Date"How exactly can I find the last called date ! I guess you could write aquery for that ! but how ???P.S I dont want to run a trace for 1 months and see what storedprocedures are not being used.
View 7 Replies
View Related
Nov 5, 2015
I could identify the last or all stored procedure, which was performed on a database, +/- something similar to what the profile of sql server identifies as below ?
View 2 Replies
View Related
Nov 29, 2006
Hi everyone,
I wonder how one can see or save the real sql statement that was executed by some stored procedure (including the one that used supplied parameters)?
Just need that for debugging purposes...
thanks!
View 1 Replies
View Related
Aug 21, 2015
I have a problem where a certain stored procedure disappears occasionally and I need to find out which script deletes it. I found this piece of code which gives the events related to the deletion of this stored procedure.
DECLARE @path NVARCHAR(260);
SELECT
@path = REVERSE(SUBSTRING(REVERSE([path]),
CHARINDEX(CHAR(92), REVERSE([path])), 260)) + N'log.trc'
FROM sys.traces
WHERE is_default = 1;
[code]...
Is there a way that I can find which stored procedure or event dropped this stored procedure?
View 4 Replies
View Related
Dec 3, 2006
I need to check the databes on the server side every 3 days and delete old data.
I am using SQL Express.
View 5 Replies
View Related
Feb 14, 2008
Hi all,
I have a Matrix report (SQL 2005 SP2) which uses a stored procedure to retrieve the result set. When I preview or view the report on IE, the SP gets executed thrice (instead of just once)? Anybody know about this? Is this is a bug in Rep Srvcs?
It is slowing down the report considerably.
Any help would be appreciated!
Thanks
SS
- I used SQL Profiler SP-Starting event to track this
View 2 Replies
View Related
Apr 27, 2007
With a Gridview Control, I set the SqlDataSource to be a stored procedure in a Sql Sever database.
Using the wizzard to configure the datasource, the test returns lots of rows. After completing the wizzard, the gridview control does not show the column names in the VS2005 designer. For the gridview column headers the values are Databound Col0, Databound Col1, Databound Col2, ....) This tells me I have a problem.
I tried the same thing with a simpler stored procedure. This test stored procedure does not call anything else, takes several input parameters, returns rows. The column names show in the gridview control as expected.
So I am trying to figure out why the first case of gridview with sqldatasource is not working and the second case works . The stored procedure that is not working with my gridview calls multiple inner stored procedures and has #TEMP tables. My complex stored procedure ends with Select * from #Temp.
Could the calling of other inner stored procedures and use of #temp tables contribute to the problem? If yes then what is the strategy for using a gridview and loading it with the data that the complex stored procedure returns?
View 8 Replies
View Related
Jul 22, 2015
isn't there an automatic log of some sort to check and see what exactly was changed by a given SQL command? A stored proc was ran and I need to figure out what exactly it changed in the underlying table.
View 3 Replies
View Related
Oct 6, 2015
Is it possible to find out the last executed date for any stored proc in the database using system tables or writing any other query.
View 2 Replies
View Related
Aug 9, 2006
I'm having a hard time to getting back an xml data back from a stored procedure executed by an Execute SQL task.
I'm passing in an XML data as a parameter and getting back resulting XML data as a parameter. The Execute SQL task is using ADO connection to do this job. The two parameters(in/out) are type of "string" and mapped as string.
When I execute the task, I get the following error message.
[Execute SQL Task] Error: Executing the query "dbo.PromissorPLEDataUpload" failed with the following error: "The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Parameter 2 ("@LogXML"): Data type 0xE7 has an invalid data length or metadata length.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
I also tried mapping the parameter as XML type, but that didn't work either.
If anyone knows what's going on or how to fix this problem please let me know. All I want to do is save returning XML data in the parameter to a local package variable.
Thanks
View 10 Replies
View Related
Nov 8, 2007
Hi All,
I am using sql server 2005. I stuck out in a strange problem.
I am using view in my stored procedure, when I run the stored procedure some of the rows get skipped out means if select query have to return 10 rows then it is returning 5 rows or any other but not all, also the records displyaing is randomly coming, some time it is displaying reords 12345 next time 5678, other time 2468.
But if I run seperately the querys written in SP then it returns all the rows. Please give me solution why it is happening like this.
There are indexes in the tables.
Once I shrink the database and rebuild the indexes, from then this problem is happening. I have rebuild the indexes several time, also updated the statistics but nothing improving.
But nothing is improving
View 7 Replies
View Related
Jul 20, 2005
Hi!How can I get percent of executed procedure in MSSQL Server, lik inEnterprise Manager and/or dbMgr2k ?I use Visual C# and MSDE.Thank's for help, gregory
View 1 Replies
View Related
May 29, 2002
Hi,
Is there any system tables or something , which one can query to get when a specific procedure was last run.
Thanks for you help.
View 1 Replies
View Related
Jul 9, 2007
I use sp_addrolemember inside a DML trigger and every thing goes well in my computer; I can insert or update into my table and sp_addrolemember is executed successfully, but my customer get this error every time he tries to insert or update into the table.
"the procedure 'sp_addrolemember' cannot be executed within a transaction"
Can anybody help me solve this funny error.
View 1 Replies
View Related
Apr 20, 2007
I was wondering the way in which stored procedures are executed.
For example if I had a stored procedure (A) that executed a sub stored procedure (B).
And two different users where to execute the Stored procedure at the same time what would happen.
Would the second user have to wait while the first user had finished (A and B), dose each user get a copy of the stored procedure or can one user be running stored procedure (B) whilst the other is running stored procedure (A).
View 6 Replies
View Related
Mar 13, 2002
I created DTS a while ago and placed in job to run once a day (it worked fine for 3 months)
2 days ago I changed sa password and now job fails with error (Login failed for user 'sa'.), but it run fine from DTS !!!
1. My DTS created with domain Account DomainSVCSQL2000( sa rights and local admin)
2. SVCSQL service use DomainSVCSQL2000 to run
3. SVCSQL agent use DomainSVCSQL2000 to run
4. DTS use 'osql -E
Where should look for reference to sa ?
Executed as user: MONTREALsvcsql2000. DTSRun: Loading... Error: -2147217843 (80040E4D); Provider Error: 18456 (4818) Error string: Login failed for user 'sa'. Error source: Microsoft OLE DB Provider for SQL Server Help file: Help context: 0. Process Exit Code 1. The step failed.
View 5 Replies
View Related
May 15, 2008
i have created the folowing function but keep geting an error.
Only functions and extended stored procedures can be executed from within a function.
Why am i getting this error!
Create Function myDateAdd
(@buildd nvarchar(4), @avdate as nvarchar(25))
Returns nvarchar(25)
as
Begin
declare @ret nvarchar(25)
declare @sqlval as nvarchar(3000)
set @sqlval = 'select ''@ret'' = max(realday) from (
select top '+ @buildd +' realday from v_caltable where realday >= '''+ @avdate +''' and prod = 1 )a'
execute sp_executesql @sqlval
return @ret
end
View 3 Replies
View Related
Apr 11, 2005
I'm trying to fire a DTS package through a stored procedure.
After running my one stored procedure I got my DTS file to create just fine. However after this point I'm stuck...I can't do anything else with this file and I need to update data with it.
How would I use the DTS file to update my data.
Thanks,
RB
View 1 Replies
View Related
Jan 30, 2006
I've all procedures running over EXECUTE permission. They're running properly without the SELECT or DRY permissions on involved tables.
But some procedures of above cited, in a particular tables or particular the procedure don't runs properly with out enabling SELECT permission on involved tables.
The EXECUTE permision runs overs other permissions on tables if the're not implicit denied, it's the best segurity practice. Then what is happen?? why need extra SELECT permision on some tables ?. The usser, and function role are ok.
You troube the same, some help please :)
View 7 Replies
View Related
Mar 31, 2015
I have a very anoying problem with SSIS. I've done new packages into same project, almost identical compared to old ones. They work well in visual studio, but I can't execute them using procedure. Old packages works just fine, but none of the new. Error message is in the end of my story.Visual Studio version is 9.0.30729.1 and SQL Server version is 10.0.4000.0. Is it possible, that these not not updated versions could cause this problem?Package ProtectionLevel is DonSaveSensitive.
Error messages, when package is executed by procedure:
Microsoft (R) SQL Server Execute Package Utility
Version 10.50.1600.1 for 64-bit
Copyright (C) Microsoft Corporation 2010. All rights reserved.
NULL
Started:Â 10:36:48 AM
Error: 2015-03-31 10:36:48.48
  Code: 0xC0016016
[code]...
View 11 Replies
View Related
Jun 7, 2006
Hi, l've created an function [GSM].[KPIAging], and test it in studio by substitule declare value, i.e.
DECLARE @sCellName VARCHAR(8)
DECLARE @dDate DATETIME
SET @sCellName = "CELL1M_1"
SET @dDate = CAST('06/Jun/2006' AS DATETIME)
EXEC GSM.KPIAging @sCellName, 'CSSR', @dDate
It work fine and return the desired result, but when l used this function in SQL,
SELECT DATEKEY, CELLREGIONKEY, CELL_NAME, CELL_ID, CSSR, GSM.KPIAging(Cell_Name, 'CSSR', @dDate)
FROM GSM.GSMCellDaily_vw
WHERE CSSR BETWEEN 0 AND 85
AND FULLDATE = @dDate
AND CM_SERV > 30
AND (TCH_TRAFFIC > 2 AND TCH_SEIZURES_ATTS > 30)
I got the following error, i.e.
Msg 557, Level 16, State 2, Line 19Only functions and extended stored procedures can be executed from within a function.
Does anyone have any idea on this, and what's the workaround for this?
Thanks you!
View 15 Replies
View Related
May 7, 2008
Hi mister, I have this script sql but I get this error:
Mens. 557, Nivel 16, Estado 2, LÃnea 1
Only functions and extended stored procedures can be executed from within a function.
DROP FUNCTION ObtenerTablaPorNombre2
GO
CREATE FUNCTION ObtenerTablaPorNombre2 (@ParamNombreTabla VARCHAR(100))
RETURNS @T Table ( Descripcion VARCHAR(20) NOT NULL, CIF VARCHAR(8) NULL )
AS
BEGIN
DECLARE @cmd nvarchar(max)
DECLARE @params nvarchar(max)
DECLARE @NombreTabla VARCHAR(MAX)
DECLARE @Descripcion VARCHAR(MAX)
DECLARE @CIF VARCHAR(MAX)
SELECT @NombreTabla = [CD_NOMBRE_TABLA], @Descripcion = [DS_CAMPO_DESCRIPCION] , @CIF = [DS_CAMPO_CIF]
FROM [TABLA_MAESTRA] WHERE [CD_NOMBRE_TABLA] = @ParamNombreTabla
SET @cmd = 'SELECT ' + @Descripcion + ',' + @CIF + ' FROM ' + @NombreTabla
--EXEC (@cmd)
SET @cmd = 'SELECT @pDescripcion, @pCIF FROM @pNombreTabla'
SET @params = N'@pDescripcion varchar(100), @pCIF varchar(100), @pNombreTabla varchar(100) '
EXEC sp_executesql @cmd, @params, @pDescripcion = @Descripcion, @pCIF = @CIF, @pNombreTabla = @NombreTabla
RETURN
END
GO
SELECT * FROM [dbo].ObtenerTablaPorNombre2 ('tabla2')
-- Only functions and extended stored procedures can be executed from within a function
View 2 Replies
View Related
Jan 14, 2007
Hi all,
This is a problem for that I lost a weekend to reach to this conclusions:
Here is the scenario:
I need a file downloaded from a FTP server on my SQL 2005 DB Server to process it in several ways. This file is tens of MBs (let's say 50MB). I set up a CLR stored procedure to bring it locally on the machine.
Inside this procedure, call it getFtpFile, I use the FtpWebRequest to issue the commands I need to the FTP server (get the file size and download the file). For reading and writing I use a StreamReader sr and a StreamWriter. sw
I read the remote file in block with:
sr.ReadBlock(buf, 0, blockSizeBytes);
Everything work file until the last block that, most probably will not be of size blockSizeBytes, but smaller. So, what happens is that when I read this last block there is an exception raised from the call saying:
The underlying connection was closed: An unexpected error occurred on a receive.
This happens only if:
-> the code runs inside SQL (calling the SP) (it runs perfect on normal tet environment where I have another executable and call the getFtpFile method in the CLR dll);
-> 100 seconds (100000 miliseconds) (exactly) have passed (small files do not experiencing this problem)
I thought the reader writer scheme is not perfect and implemented again with a simple FileStream.WriteByte(response.GetResponseStream().Read());
This approach, even if very slow, still has the problem after 100 seconds, in a CLR SQL environment, when it reads the at the last character.
I have used all the combinations of KeepAlive and response.Closose(). Problems still there.
Conclusion: There must be a 100 seconds timeout that immediately after my last byte has been received, closes the connection and the stream of the response crashes during this last call.
Thank you all for the help!
View 2 Replies
View Related
Sep 11, 2013
I have this function called fn_GetTimedKey() when I run it in my SQL2005 environment it works fine.
When I run it in my SQL2008R2 environment I get the error:
Only functions and some extended stored procedures can be executed from within a function.
The SQL2008R2 server is new. What can I look for?
Here's the code for the function:
BEGIN
DECLARE @v_Key CHAR(12)
EXEC master..xp_usg_gettimedkey_VAL @v_Key OUTPUT
RETURN @v_Key
END
View 4 Replies
View Related
