Unable To Get Return Value Of Execute Sp_executeSql @sqlString
Aug 21, 2006
Hi All,
Create proc sproc_Insert
@TableName varchar(50),
@InsertColumns varchar(1000),
@InsertValues varchar(2000),
@WhereCondition varchar(200)
as
Begin
Declare @CheckStr nVarchar(2000)
Declare @RetVal int
Set @checkStr = 'Select * from '+ @TableName + ' '+ @WhereCondition
execute sp_executesql @checkStr,@RetVal output
print @RetVal
End
I am not able to retrieve the return value in the above procedure. For example if data exists then 1 else o
Thanks & Regards
Bijay
View 3 Replies
ADVERTISEMENT
Jun 3, 2006
Hi,
How can I check return value from
EXECUTE sp_executesql(SQLString)
Thanks,
View 4 Replies
View Related
May 13, 2002
This is what I am trying:
set @cntsql = 'select count(' + @dimky + ') as dimcnt from ' + @dimtb + ' where ' + @dimky +' is not null'
set @dimcnt = execute sp_executesql @cntsql
What I want to do is return the count from the dynamically selected database. If I type it in like:
set @dimcnt = (select count('ptky') as dimcnt from pttable where ptky is not null)
it works.... can anyone help... this code is in the middle of a cursor.
View 2 Replies
View Related
Jun 3, 2006
Hi,
How can I check return value from
EXECUTE sp_executesql(SQLStr)
Thanks,
View 1 Replies
View Related
Dec 20, 2006
please, in simple words, what is difference between :sp_executesqlandEXECUTEin sql2005?
View 3 Replies
View Related
Nov 10, 2003
I am trying to use dynamic sql with a return parameter, but with limited success. I am using WebMatrix, vb.net and MSDE to perform this routine. Can someone please clue me in. I have read two fine articles by <a href='http://www.algonet.se/~sommar/dyn-search.html>Erland Sommarskog</a> on dynamic sql using sp_executesql, as well as the somewhat opaque article by Microsoft (262499) on the subject.
While there may be other ways to accomplish this task, I am interested in making it work with dynamic SQL. In production, there will be over 20 parameters coming from the vb.net to the SQL, being driven from user input. Then those same variables will be used to actually retrieve the records to a datagrid.
So with a tip of the cap to Rod Serling, I submit this small code and SQL for your consideration from my Twilight Zone:
Public Function totalrecordsbysql(list as arraylist) as integer
dim RetVal as new integer
dim querystring as string
Dim cn As SqlConnection = New SqlConnection(ConfigurationSettings.AppSettings("Indiafriend"))
Dim cmd As SqlCommand = New SqlCommand("SimpleDynProfileCount", cn)
cmd.commandtype = commandtype.storedprocedure
dim mydr as SqlDataReader
cmd.Parameters.add("@TotalRecords",SqlDbType.int).direction=ParameterDirection.Output
cmd.Parameters.add("@age",sqldbtype.int).value = 18
cn.Open()
try
mydr=cmd.executereader()
catch e as sqlexception
dim err as sqlerror
dim strErrorString as string
for each err in e.Errors
strErrorString += "SqlError: #" & err.Number.ToString () & vbCRLF + err.Message
trace.write("sqlexception",strErrorString)
Next
finally
RetVal = cmd.parameters("@TotalRecords").value
end try
Return RetVal
cn.close()
End Function
Now here is the stored procedure:
CREATE PROCEDURE SimpleDynProfileCount
@age int,
@TotalRecords int output
AS
Declare @sql nvarchar(4000),
@paramlist nvarchar(4000)
select @sql = 'select @xTotalRecords = count(*) from profile where 1 = 1 '
// RAISERROR(@sql, 16, 1)
IF @age > 0
Select @sql = @sql + ' AND age > @xage '
Select @paramlist = '@xage int, @xTotalRecords int output'
Execute sp_executesql @sql,@paramlist,@age,@xTotalRecords = @TotalRecords output
select @TotalRecords
GO
Please note the commented RAISERROR statement. If I uncomment this statement, I will get a return value of 11 records. If I leave it out, I get zero records.
The data is the database should return 11 records, based on the criteria of age > 11
View 5 Replies
View Related
May 19, 2008
The basic syntax for the sp_executesql with a return value is:
Code Snippet
DECLARE @count int
DECLARE @ParmStr nvarchar(256)
set @count=0
set @ParmStr = N' @lvl tinyint, @cnt int OUTPUT'
execute sp_executesql
N'select @cnt=count(*) from pubs.dbo.employee where job_lvl = @lvl',
@ParmStr, @lvl = 35, @cnt=@count OUTPUT
print 'count: ' + cast(@count as nvarchar(4))
This returns a value of 3.
I need to change this to return the numbers of rows that were deleted, such as:
Code Snippet
DECLARE @count int
DECLARE @ParmStr nvarchar(256)
set @count=0
set @ParmStr = N' @lvl tinyint'
execute sp_executesql
N'delete from pubs.dbo.employee where job_lvl = @lvl',
@ParmStr, @lvl = 35
-- Need count
print 'count: ' + cast(@count as nvarchar(4))
Any ideas on how to modify this so that I can report on number of rows deleted?
View 4 Replies
View Related
Jan 11, 2000
Hi... Everybody,
I am new to using SQL Server and I present to you the following problem that I am facing:
Can I use the 'EXECUTE' or 'EXECUTE sp_executesql' in a SELECT query that assigns a value to a declared variable ?
To be more specific:
I have the following set of SQL Statements that do not seem to work:
------------------------------------------------------------------------------
DECLARE @CustomerID char(6)
DECLARE @OfficeID char(3)
DECLARE @DestinationAccountNo char(7)
DECLARE @TableName char(30)
SET @TableName = 'Users'
SET @CustomerID = '001001'
SET @OfficeID = '001'
SET @DestinationAccountNo = '0001011'
DECLARE @ExecuteString nvarchar(500)
DECLARE @CurDestPatIDChar char(2)
SET @ExecuteString = N'SELECT @CurDestPatIDChar = RTRIM(CAST(Max(CAST([UserID] AS decimal(2, 0))) AS char(2))) '
SET @ExecuteString = RTRIM(@ExecuteString) + N' FROM ' + RTRIM(@TableName)
SET @ExecuteString = RTRIM(@ExecuteString) + N' WHERE [CustID] = ''' + RTRIM(@CustomerID) + N''' AND [OfficeID] = ''' + RTRIM(@OfficeID) + N''' AND [AccNo] = ''' + RTRIM(@DestinationAccountNo) + N''''
EXECUTE SP_EXECUTESQL @ExecuteString
PRINT @CurDestPatIDChar
------------------------------------------------------------------------------
When I run this in the Query Ananlyzer I get the following error:
Server: Msg 137, Level 15, State 1, Line 0
Must declare the variable '@CurDestPatIDChar'.
The above set of statements do not seems to work with EXECUTE either.
Where as if I run the following query with the same variable declarations as above:
-----------------------------------------------------------------------------
SET @ExecuteString = N'SELECT @CurDestPatIDChar1 = RTRIM(CAST(Max(CAST([PatientID] AS decimal(2, 0))) AS char(2))) '
SET @ExecuteString = RTRIM(@ExecuteString) + N' FROM ' + RTRIM(@TableName)
SET @ExecuteString = RTRIM(@ExecuteString) + N' WHERE [CustID] = ''' + RTRIM(@CustomerID) + N''' AND [OfficeID] = ''' + RTRIM(@OfficeID) + N''' AND [AccNo] = ''' + RTRIM(@DestinationAccountNo) + N''''
EXECUTE SP_EXECUTESQL @ExecuteString, N'@CurDestPatIDChar1 char(2)', @CurDestPatIDChar1 = @CurDestPatIDChar
PRINT @CurDestPatIDChar
-----------------------------------------------------------------------------
I donot get any error messages but the variable '@CurDestPatIDChar' is not initialized.
The problem seems to be that the execute statement interprets any variable assignments (here it is '@CurDestPatIDChar', defined as part of the execute string in quotes) as local to the execute statement.
I shall be grateful if you can provide me with a solution for this,
BR,
Sudhakar
View 2 Replies
View Related
Mar 26, 2008
Hi There
Ok i have a piece of test ddl sql that is written to a varchar(max) column. Entered by a user.
GO's and missing semi colons seem to break it, if you try to execute it with EXEC or sp_executesql, however the sql can be executed on Management Studio as is, so how can i execute it as is successfully?
In a nutshell i retreive the DDL sql from the column into a nvarchar(max) variable called @SQL , and i need to execute it.
I have tried:
EXEC(@SQL) and sp_executesql @SQL, both return the error , incorrect syntax near 'GO'.
The problem is obviously i have to have the go statements, in order to create some fo the ddl objects correctly. But EXEC and sp_executesql do not like that. I also found that semi colons are required after every statement.
The sql is as follows:
--===============================================================================
--DDL script
CREATE LOGIN TEST_LOGIN WITH PASSWORD = 'Whatever!@(!';
CREATE USER TEST_USER FROM LOGIN TEST_LOGIN;
CREATE TABLE TEST_TABLE (Column1 int NULL);
GRANT INSERT ON TEST_TABLE TO TEST_USER;
CREATE CLUSTERED INDEX TEST_INDEX ON TEST_TABLE (Column1);
GO
CREATE PROCEDURE TEST_PROCEDURES
AS
SELECT GETDATE();
GO
CREATE VIEW TEST_VIEW
AS
SELECT * FROM TEST_TABLE;
GO
--ALTER DDL
ALTER VIEW TEST_VIEW
AS
SELECT GETDATE() AS 'DATE';
GO
ALTER PROCEDURE TEST_PROCEDURES
AS
SELECT * FROM TEST_TABLE;
GO
--DROP DDL
DROP INDEX TEST_TABLE.TEST_INDEX;
DROP TABLE TEST_TABLE;
DROP VIEW TEST_VIEW;
DROP USER TEST_USER;
DROP LOGIN TEST_LOGIN;
DROP PROCEDURE TEST_PROCEDURES;
--===============================================================================
View 5 Replies
View Related
Aug 21, 2007
Hello,
i need to create temporary table inside SP.
i having one string variable @strQuery which contain dynamic query inside SP.
i am executing that trhough execute sp_executesql @strQuery once query build.
now instead of select query , i want to creat hash table.
so i wrote :
set @strQuery = "Select * into #tmp_tbl from table_name..."
when i tried to execute it through
execute sp_executesql @strQuery , its giving error 'Invalid object name '#tmp_tbl'
If i removed Hash then it works fine. even for double Hash also its work fine.
but i want hash table only as i want that table local to that user.
Even direct execution of select statement without @strQuery works fine. but i want to execute @strQuery through execute sp_executesql @strQuery only as query is dynamic .
please guide me how to do this?
its very urgent for me.
thanks in advance.
View 4 Replies
View Related
Jun 25, 2007
I have a SSIS package contains an "Execute SQL Task". The SQL will raise error or succeed. However, it sounds the package won't pick up the raised error?
Or is it possible to conditional run other control flow items according the the status of SQL task execution?
View 1 Replies
View Related
May 7, 2008
I need to get a value from another Stored Procedure to use within another Stored Procedure. This is what I currently have, but it is not even close, I'm sure:
CREATE PROCEDURE dbo.sp_JT_BS01c_Calendar_Build_BufferSheet_DateRange AS
DECLARE @MinOfDMD_WK datetime, @MaxOfDMD_WK datetime
@MinOfDMD_WK = Exec sp_JT_BS01a_CalendarBuild_Min_PlanningWeek
@MaxOfDMD_WK = Exec sp_JT_BS01b_CalendarBuild_Max_PlanningWeek
SELECT @MinOfDMD_WK, [master - weekly_range].week,
@MaxOfDMD_WK, dbo.fn_Bucket_Range([week],@MinOfDMD_WK,@MaxOfDMD_WK) AS [Inclusion Range]
FROM [master - weekly_range], @MaxOfDMD_WK, @MinOfDMD_WK
WHERE dbo.fnBucket_Range([week],@MinOfDMD_WK,@MaxOfDMD_WK)=1;
Thanks,
Bob Larson
View 3 Replies
View Related
Sep 13, 2007
Hi all! I've been banging my head on this for way to long and decided it's time to reach out for help. I'll try to be complete and concise with this, but this will end up being lengthy most likely.
The crux of the issue is that I apparently do not have any control over what is returned from a stored proc via the SqlCommand.ExecuteNonQuery method. I want the procedure to explicitly return 0 if the proc was successful, otherwise it will return the error code generated. A value of -1 is consistently being returned when SET NOCOUNT ON is in place, and when not, a 1 is returned. This seems to be ignoring the fact that I have RETURN 0 at the end of the proc. Perhaps I'm missing something, but if I RETURN 0, I should get 0...here is the procedure:1 ALTER PROCEDURE [dbo].[epsp_EditCities]
2 @CityId int = null,
3 @City varchar(50),
4 @CreatedBy int,
5 @UpdatedBy int,
6 @ActionType varchar(1) = 'X',
7 @Identity int = 0 OUT,
8 @RowCount int = 0 OUT
9
10 AS
11 DECLARE @ERROR int -- Local @@ERROR
12
13
14 SET NOCOUNT ON
15 BEGIN TRAN
16
17 IF @ActionType = 'I' /* --- INSERT --- */
18 BEGIN
19
20 INSERT INTO [EnrollmentPrograms].[dbo].[Cities]
21 ([City]
22 ,[CreatedBy]
23 ,[UpdatedBy])
24 VALUES
25 (@City
26 ,@CreatedBy
27 ,@UpdatedBy)
28 SET @Identity = SCOPE_IDENTITY()
29
30 END
31
32 IF @ActionType = 'U' /* --- UPDATE --- */
33 BEGIN
34
35 UPDATE [EnrollmentPrograms].[dbo].[Cities]
36 SET [City] = @City
37 ,[UpdatedBy] = @UpdatedBy
38 WHERE CityId = @CityId
39 END
40
41 IF @ActionType = 'D' /* --- DELETE --- */
42 BEGIN
43
44 DELETE FROM [EnrollmentPrograms].[dbo].[Cities]
45 WHERE CityId = @CityId
46 END
47
48
49 -- Error checking (place this after every statement) --
50 SET @ERROR = @@ERROR
51 SET @RowCount = @@ROWCOUNT
52
53 IF @ERROR != 0 GOTO HANDLE_ERROR
54
55 COMMIT TRAN -- No Errors, so go ahead
56
57 RETURN 0
58
59 HANDLE_ERROR:
60 ROLLBACK TRAN
61 RETURN @ERRORAnd a fascinating tidbit is the results from this which shows the return value as 0:1 DECLARE@return_value int,
2 @Identity int--,
3 --@RowCount int
4
5 EXEC@return_value = [dbo].[epsp_EditCities]
6 @CityId = 1,
7 @City = N'Agoura Hills',
8 @CreatedBy = 1,
9 @UpdatedBy = 1,
10 @ActionType = N'U',
11 @Identity = @Identity OUTPUT--,
12 --@RowCount = @RowCount OUTPUT
13
14 SELECT@Identity as N'@Identity'--,
15 --@RowCount as N'@RowCount'
16
17 SELECTReturnValue = @return_value
And finally, here is the C# call that always returns -1:
retVal = cmd.ExecuteNonQuery();
Now, I've tried almost all variations and unit tests on the procedure that I could dream up. Commenting this out and putting this in etc....I still remain unable to fix the return code to 0 and get that result into my retVal var.
Any love? Much thanks in advance for spending time on my issue!
Cheers!
Wayne
View 5 Replies
View Related
Jan 2, 2007
Hi everyone
I am trying something that should be so simple but I cant get it to work. I am calling a stored procedure to lock a table and update a counter. I have tried to follow the exact code use in the MSDN examples but it doesnt work and always returns VT_EMPTY. It also returns a closed recordset so attempting to close it fails. The database is updated correctly and there are no exceptions just no return value.
Here is the stored procedure
CREATE PROCEDURE GETMODELID AS
DECLARE @i INT
BEGIN TRANSACTION
SET @i=(SELECT ModelID from tblModelID WITH (HOLDLOCK,TABLOCKX))+1
UPDATE tblModelID SET ModelID = @i
COMMIT TRANSACTION
RETURN @i
GO
Here is the ADO written in C++.
TDatabase DB;
try
{
if(DB.Open()==false)
{
AfxMessageBox(DB.m_ErrStr);
return FALSE;
}
_variant_t vtEmpty2 (DISP_E_PARAMNOTFOUND, VT_ERROR);
_CommandPtr spCMD;
CREATEiNSTANCE(spCMD,Command);
spCMD->ActiveConnection = DB.m_Cnn;
spCMD->CommandText = "GETMODELID" ;
spCMD->CommandType = adCmdStoredProc;
spCMD->Parameters->Refresh();
_RecordsetPtr spRS;
_variant_t vRa;
spRS = spCMD->Execute( &vRa, &vtEmpty2, adCmdStoredProc );
_variant_t rtn(DEF_PARAM(spCMD,0L));
DB.Close();
return (int) rtn;
}
View 3 Replies
View Related
Jul 23, 2005
This is a odd problem where a bad plan was chosen again and again, butthen not.Using the profiler, I identified an application-issued statement thatperformed poorly. It took this form:exec sp_executesql N'SELECT col1, col2 FROM t1 WHERE (t2= @Parm1)',N'@Parm1 int', @Parm1 = 8609t2 is a foreign key column, and is indexed.I took the statement into query analyzer and executed it there. Thequery plan showed that it was doing a scan of the primary key index,which is clustered. That's a bad choice.I then fiddled with it to see what would result in a good plan.1) I changed it to hard code the query value (but with the parmdefinition still in place. )It performed well, using the correct index.Here's how it looked.exec sp_executesql N'SELECT cbord.cbo1013p_AZItemElement.AZEl_Intid AS[Oid], cbord.cbo1013p_AZItemElement.incomplete_flag AS [IsIncomplete],cbord.cbo1013p_AZItemElement.traceflag AS [IsTraceAmount],cbord.cbo1013p_AZItemElement.standardqty AS [StandardAmount],cbord.cbo1013p_AZItemElement.Uitem_intid AS [NutritionItemOid],cbord.cbo1013p_AZItemElement.AZeldef_intid AS [AnalysisElementOid] FROMcbord.cbo1013p_AZItemElement WHERE (Uitem_intid= 8609)', N'@Parm1 int',@Parm1 = 8609After doing this, re-executing the original form still gave badresults.2) I restored the use of the parm, but removed the 'exec' from thestart.It performed well.After that (surprise!) it also performed well in the original form.What's going on here?
View 3 Replies
View Related
Aug 10, 2006
Hi
I fighting with a strange problem with a DTS package, we had a DTS package under a name of user who left the company and his account was deactivated, i openned that DTS package and saved it under my name and my user is part of sysadmin role, when i am trying to execute the DTS i gets the error message permission denied unable to create table (database name)
while i can crete/delete tables in that particular database manualy via query analyser as well as through enterprise manager but when i am trying to do that through a DTS as mentioned above i always gets the permission denied message
any help will be highly appreciated
thanks
Ramis
View 3 Replies
View Related
Feb 22, 2006
I have a pretty simple lookup query that returns an error that I can't figure out. It states that the query has too few parameters (2), but only 1 is required. I've investigated every component and everything seems correct. I have another lookup just like this and it works fine. Here is a simple description:
I have an Access table with values that have to be loaded into a SQL Server table. I defined a Row Transform task with an ActiveX script.
My lookup query is called Test1 and looks like this:
SELECT ConveyanceInstrumentId
FROM ConveyanceInstrumentMapping
WHERE (conveyanceinstrument = ?)
Here is the transform script:
'**********************************************************************
' Visual Basic Transformation Script
'************************************************************************
' Copy each source column to the destination column
Function Main()
dtsdestination("ConveyanceInstrumentId") = DTSLookups("test1").Execute(1)
Main = DTSTransformStat_OK
End Function
The Execute(1) is just there for testing purposes, but returns the same error as when I reference the field I want to look up.
When I test this, an error is returned: [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 2
Any suggestions or ideas are appreciated.
View 2 Replies
View Related
May 19, 2005
Hello,
The SqlString variable; if a null value is assigned, will Value return the text null? Also, if I use another type such as SqlInt32, when I do ToString(), if the value is null, will it return the text null, so that it will work with a SQL string? I was wondering if that was the case.
Brian
View 2 Replies
View Related
Sep 26, 2007
I have created a logging test package that I am attempting to execute in SQLAgent.
It uses an environment variable in Package Configurations to set a variable which is the expression value for the ConnectionManager's 'ole_src_Admin' ConnectionString.
The next three Package Configurations use that connection to retrieve SQL Server configuration types/
Using the same account I can execute the package succesfully from cmd line
The execution string is
dtexec /SQL "Parent Package" /SERVER "BLAHSQL-DEV" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING V
Attempting to execute this via SQLAgent (type CmdExec) I receive the following error:
Date 9/26/2007 9:47:45 AM
Log Job History (Logging Test)
Step ID 3
Server QRISQL-DEV
Job Name Logging Test
Step Name Execute 2005 Package
Duration 00:00:01
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0
Message
Microsoft (R) SQL Server Execute Package Utility
Version 9.00.3042.00 for 64-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.
Started: 9:47:45 AM
Info: 2007-09-26 09:47:46.28
Code: 0x40016038
Source: Parent Package
Description: The package is attempting to configure from the environment variable "SQL_Parameter_Connect_String".
End Info
Info: 2007-09-26 09:47:46.28
Code: 0x40016040
Source: Parent Package
Description: The package is attempting to configure from SQL Server using the configuration string ""ole_src_Admin";"[dbo].[Parameter]";"sv_ssis_package_store_connectionstring";".
End Info
Error: 2007-09-26 09:47:46.30
Code: 0xC0202009
Source: Parent Package Connection manager "ole_src_Admin"
Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E21.
End Error
Info: 2007-09-26 09:47:46.30
Code: 0x40016040
Source: Parent Package
Description: The package is attempting to configure from SQL
I have also succeffuly executed this package in the same manner locally using both the cmd line and SQLAgent.
Any clues would be appreciated.
View 1 Replies
View Related
Jun 26, 2001
I'm trying to execute a parameterized SQL string but need a return parameter (a multiplier) to include in a later SELECT statement.
What i'm looking for is equivalent to this paraphrased statement:
EXEC('SELECT @val = from @column where Value = @Value')
Where I would later use @val something like:
Select Value * @val as Total Value
Is there an easy way to do this..i've read and read to no avail.
TIA,
Charles
View 5 Replies
View Related
Jul 13, 2004
ok I have a stored procedure in my MS-SQL Server database.
It looks something like this.....
CREATE PROCEDURE updatePCPartsList
(
@Descriptionvarchar(255),
@ManCodevarchar(255),
@ProdCodevarchar(255),
@Pricedecimal(6,2),
@Commentsvarchar(255)
)
AS
declare @IDFound bigint
declare @LastChangedDate datetime
select @LastChangedDate = GetDate()
select @IDFound = PK_ID from PCPartsList where ProdCode = @ProdCode
if @IDFound > 0
begin
update PCPartsList set Description = @Description, ManCode = @ManCode, ProdCode = @ProdCode, Price = @Price, Comments = @Comments, LastChanged = @LastChangedDate where PK_ID = @IDFound
end
else
insert into PCPartsList (Description, ManCode, ProdCode, Price, Comments, LastChanged) values(@Description, @ManCode, @ProdCode, @Price, @Comments, @LastChangedDate)
GO
It executes fine so I know i've done that much right....
But what i'd like to know is how I can then return a value - specifically @LastDateChanged variable
I think this is a case of i've done the hard part but i'm stuck on the simple part - but i'm very slowly dragging my way through learning SQL.
Someone help?
View 3 Replies
View Related
Apr 10, 2007
Hi,
When I run the following queries they are working fine with MS Access but not working with SqlCe 3.1 on my desk top.
I am using Sql Server 2005 management studio to connect to SqlCe (.sdf) file and running the queries with in that.
Please guide me what is wrong here:
1)
select count(*) FROM (SELECT DISTINCT TIDVALUE FROM cdirmen)
Error Message:
Major Error 0x80040E14, Minor Error 25501
> select count(*) FROM (SELECT DISTINCT TIDVALUE FROM cdirmen)
There was an error parsing the query. [ Token line number = 1,Token line offset = 23,Token in error = SELECT ]
2)
SELECT distinct tidvalue FROM cdirmen WHERE objname='I_MAP' AND menuid<>0
AND 0<(SELECT COUNT(*) FROM cmenu WHERE cmenu.menuid=cdirmen.menuid)
AND 0=(SELECT COUNT(*) FROM cmenu WHERE cmenu.menuid=cdirmen.menuid AND cmenu.item like 'CL%')
Error Message:
Major Error 0x80040E14, Minor Error 25501
> SELECT distinct tidvalue FROM cdirmen WHERE objname='I_MAP' AND menuid<>0
AND 0<(SELECT COUNT(*) FROM cmenu WHERE cmenu.menuid=cdirmen.menuid)
AND 0=(SELECT COUNT(*) FROM cmenu WHERE cmenu.menuid=cdirmen.menuid AND cmenu.item like 'CL%')
There was an error parsing the query. [ Token line number = 2,Token line offset = 8,Token in error = SELECT ]
View 5 Replies
View Related
Jul 15, 2003
I want to pass a database parm to enlarge the maxsize for around 500 databases. Here is the primary script:
--====================================
declare @stringData varchar(200),
@stringLog varchar(200),
@databaseName varchar(25),
@returnCodeSize int
select @databaseName = 'ABC'
select @stringData = 'alter database ' + @databaseName +
' modify file (name = ABC_Data, maxsize = 1500MB, FILEGROWTH = 10%)'
print @stringData
exec (@stringData)
It works in above way.
But it did not work if
I use @returnCodeSize =exec(@stringData)
how could I get a return code from this exec. I can't use cmdshell since it is not an external operating command.
thanks
David
View 5 Replies
View Related
Mar 18, 2004
Hi,
I am getting an error:
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near ','.
This is my code. What is wrong here?
CREATE TABLE #TotalsTemp (InvoiceNum varchar(25),
ShipperNum varchar (20),
InvoiceDate datetime,
PickupTransDate datetime,
ShipperName varchar(50),
ShipperName2 varchar(50),
ShipperAddr varchar(50),
ShipperCity varchar(50),
ShipperState varchar(6),
ShipperZip varchar(15),
bName1 varchar(100),
bName2 varchar(50),
bAddr1 varchar(50),
bCity varchar(50),
bState varchar(6),
bZip varchar(15),
bCountry varchar(50),
bPhone varchar(50),
TrackingNum varchar(20),
CustRef1 varchar(50),
CustRef2 varchar(50),
UPSZone varchar(3),
ServiceLevel varchar(50),
Weight int,
Lading varchar(70),
SMPCodeDesc varchar(255),
GrossCharge decimal(12,2),
Incentive decimal(12,2),
NetCharge decimal(12,2),
AccessorialTotal decimal(12,2),
CodeRefDesc varchar(50),
HundredWeight varchar(3))
--Inbound
SET @LadingType = 'inbound'
SET @SQLStr = 'INSERT INTO #TotalsTemp ' +
'SELECT ' + @ReportData + '.InvoiceNum, ' +
@ReportData + '.ShipperNum, ' +
@ReportData + '.InvoiceDate, ' +
@InvoiceData + '.PickupTransDate, ' +
@AddrData + '.aName1, ' +
@AddrData + '.aName2, ' +
@AddrData + '.aAddr1, ' +
@AddrData + '.aCity, ' +
@AddrData + '.aState, ' +
@AddrData + '.aZip, ' +
@ReportData + '.bName1, ' +
@AddrData + '.bName2, ' +
@AddrData + '.bAddr1, ' +
@ReportData + '.bCity, ' +
@ReportData + '.bState, ' +
@AddrData + '.bZip AS, ' +
@AddrData + '.bCountry, ' +
@AddrData + '.bPhone, ' +
@ReportData + '.TrackingNum, ' +
@InvoiceData + '.CustRef1, ' +
@InvoiceData + '.CustRef2, ' +
@ReportData + '.UPSZone, ' +
'tblLegendServiceLevel.ServiceLevel, ' +
@ReportData + '.Weight, ' +
'tblLegendLading.Lading, ' +
'tblLegendSMPCodes.[Desc], ' +
@InvoiceData + '.GrossCharge, ' +
@ReportData + '.Incentive, ' +
@ReportData + '.NetCharge, ' +
@ReportData + '.AccessorialTotal, ' +
'tblCodeRef.[Desc], ' +
@InvoiceData + '.HundredWeight ' +
'FROM' + @ReportData +
' INNER JOIN ' + @InvoiceData + ' ON ' + @ReportData + '.DataID = ' + @InvoiceData + '.DataID ' +
'INNER JOIN ' + @AddrData + ' ON ' + @ReportData + '.DataID = ' + @AddrData + '.DataID ' +
'INNER JOIN tblLegendServiceLevel ON ' + @ReportData + '.ServiceStandard = tblLegendServiceLevel.ServiceStandard ' +
'INNER JOIN tblLegendLading ON ' + @ReportData + '.LadingCode = tblLegendLading.LadingCode ' +
'INNER JOIN tblLegendSMPCodes ON ' + @ReportData + '.SMP2 = tblLegendSMPCodes.SMPCode ' +
'INNER JOIN tblCodeRef ON ' + @InvoiceData + '.ComRes = tblCodeRef.Code ' +
'INNER JOIN tblShipperNumberLookUp AS LookUp ON ' + @ReportData + '.ShipperNum = LookUp.ShipperNumber ' +
'INNER JOIN tblOrg_Unit ON LookUp.OU_ID = tblOrg_Unit.OU_ID ' +
'INNER JOIN tblOrg_Unit_Hier ON tblOrg_Unit.OU_ID = tblOrg_Unit_Hier.child ' +
'INNER JOIN tblOrg_lvls ON tblOrg_Unit_Hier.child_level = tblOrg_lvls.OrgLvl ' +
'WHERE(' + @ReportData + '.InvoiceDate BETWEEN ''' + CAST(@startdate AS varchar) + ''' AND ''' + CAST(@enddate AS varchar) + ''') AND ' +
'(tblOrg_Unit_Hier.parent = ' + CAST(@Parent AS varchar) + ') AND ' +
'(tblOrg_lvls.Root = ' + CAST(@Root AS varchar) + ') AND ' +
'(tblOrg_lvls.[Name] = ''' + @OrgLvl + ''') AND ' +
'(tblLegendLading.LadingType = ''' + @LadingType + ''')'
EXEC (@SQLStr)
View 12 Replies
View Related
Jun 19, 2015
I have a periodic backup task, and when I need a development copy on which to test code, I restore my most recent backup to a different name, switch the ODBC link to that name on my development machine and have a current copy of the database to play with.
I've been doing this for years, and it works great. Just now I did it, and suddenly my development machine is unable to run any stored procedures that have the 'Execute as owner' clause in their definition. I'm using domain accounts, my personal account is the owner of the database, and everything works on the production copy, which is in the same instance on the same machine.
The test copy is identical to the production copy, which continues to work fine - it was just created using by restoring the backup of the production copy, but I can't run anything with this clause. As soon as I delete the 'Execute as owner' line, the procedure is suddenly available. If I put it back, I'm locked out again.
The error message is: The server principal “sa” is not able to access the database “WhateverDB” under the current security context
View 5 Replies
View Related
Mar 21, 2008
We have a request to build a report based on user input from an excel spreadsheet. We have a SSIS package that imports the data from Excel. This is run by a sql server agent job. Our stored procedure executes this job and runs this whole process just fine but when we execute the stored procedure from reporting services we get errors. Has anyone done this type of thing before and do you have any working solutions for how to get this reporting methodology to function?
Thanks!
View 4 Replies
View Related
Jan 18, 2008
Can anyone tell me how to capture the return code of a process launched by an Execute Process Task? I am able to capture the output by using the StandardOutputVariable but can't seem to capture the actual code.
View 4 Replies
View Related
Dec 3, 2007
I am trying to have an Excecute SQL Task return a single row result set executed on SQL Server 2005.
The query in the Execute SQL Task is:
select 735.234, 2454.123
I get a conversion error when trying to assign to SSIS variables of type Double.
I have nothing configured in the "Parameter Mapping" tab.
I have the two SSIS Double variables mapped to the Tesult Name 0 and 1 in the "Result Set" tab
I don't want to use a for loop enumerator since there is a single row returned.
I simply want to assign these two values to SSIS Double variables (double is the closest match)
I can't even hack this by converting the decimals as string and then using DirectCast to convert them to Double.
Thanks for the help
View 1 Replies
View Related
Jun 2, 2005
Hello, I have been working around this issue, but couldn't yet find any solution.I have a stored procedure that calls a method to do a certain repetitive work.In this function, I have a dynamic query, which means, that I am concatinating commands to the query depending on the input of the function.for example, there is an input for a function called "Id"Inside the function, if Id = 111I need to add " and ID <> 1" and if Id has another value I need to add " and ID = c.ID" something like that.Now, inside the function, I need to return a value by executing the above @SQLString as follows:EXEC @SQLStringWhen I need is something likeEXEC @SQLString, @Total OutputReturn (@Total)Are there any ideas ?regards
View 1 Replies
View Related
Jun 6, 2005
Anyone know how to return a sql command from a sql server stored procedure using asp.net c# and sql server 2000?I'm trying to debug the stored proc and thought the easiest way would be to return the insert command and debug it in the query analyzer.Thanks.Doug.
View 2 Replies
View Related
Mar 19, 2008
How do we do this in SS2k5?
in
EXEC(sqlstring)
sqlstring wants to pass back a resultset to the caller.
- Local temp tables are out of scope.
- Global temp table works but is a bad idea.
- Table variables not supported as OUTPUT parameters for EXEC.
Regards, Nick
View 4 Replies
View Related
Mar 30, 2006
I'm using a form that has a dropdown control. This dropdown control has items that can be selected that serves as a filter for displaying results on the page that is returned from a stored procedure call. I'm having trouble finding a way to execute the stored procedure to display the filtered results everytime a different item in the dropdown gets selected. Currently, the form does get submitted and the selected item does get saved, but the stored procedure never gets executed on a postback. Any ideas on resolving this issure? Your help is much appreciated.
View 1 Replies
View Related
Dec 8, 2006
Hi,
I am trying to programmatically execute a package that contains an Execute SQL Task component bound to a variable for its "SqlStatementSource" property (via an expression). The variable is of type String and contains a simple value of "SELECT 1". The Execute SQL Task contains an expression that sets the SqlStatementSource property to the value of this variable.
The package runs fine when I execute it via dtexec or BIDS, but when I attempt to run it via the object model, I receive the following error message:
The result of the expression ""@[User::Sql]"" on property "SqlStatementSource" cannot be written to the property. The expression was evaluated, but cannot be set on the property.
I did a search on this forum and noticed quite a few threads about this same issue, but no explanation/solution. We have quite a few packages that have dynamically constructed SQL statements for Execute SQL Tasks, and they are all failing to run via the object model. Is there something that I am missing?
Thanks,
Vitaly
View 1 Replies
View Related
