How To Get Recordset From Only Last SELECT Stmt Executed???
Feb 14, 2005
How to get recordset from only last SELECT stmt executed???
this is part of my code.. in T-Sql
Create proc some mySp
...
AS
...
set nocount on
if (@SelUserID is null)
select 0
else if (@SelUserID = @userID)
select 1
else
begin
select * -- select a.
from dms_prsn_trx_log
where @incNo = ult_incid_no and
prsn_trx_no = 10 and
trx_log_txt = @logText
if (@@rowcount != 0)
set @isForwardedByUser = 1
select 2 -- select b. I NEED This value.
end
set nocount off
GO
here it executes select a, b.
But, I want mySp return last executed select result. which is here "select 2"
I thought set nocount ON does that, but it doesn't.
How can I do???
View 2 Replies
ADVERTISEMENT
Oct 18, 2000
Hi
Can anyone help me in modifying this select statement in order to add two more columns to see if the column is identity and if the column is indexed.
SELECT SUBSTRING(SYSOBJECTS.NAME, 1, 45) AS TABLE_NAME,
SUBSTRING(SYSCOLUMNS.NAME, 1, 40) AS COLUMN_NAME,
SUBSTRING(SYSCOLUMNS.NAME, 1, 15) AS DATA_TYPE,
SYSCOLUMNS.LENGTH,
SYSCOLUMNS.XPREC AS PRECISIONS,
SYSCOLUMNS.XSCALE AS SCALE,
SYSCOLUMNS.ISNULLABLE AS ALLOW_NULLS
FROM SYSREFERENCES
RIGHT OUTER JOIN
SYSCOLUMNS INNER JOIN SYSTYPES ON SYSCOLUMNS.XTYPE = SYSTYPES.XTYPE
INNER JOIN
SYSOBJECTS ON SYSCOLUMNS.ID = SYSOBJECTS.ID ON
SYSREFERENCES.CONSTID = SYSOBJECTS.ID
WHERE SYSOBJECTS.XTYPE <> 'S' and SYSOBJECTS.XTYPE <> 'V' AND SYSOBJECTS.XTYPE <> 'P'
ORDER BY TABLE_NAME
thanks
Venu
View 1 Replies
View Related
Mar 27, 2008
How do I use the select statement to create a blank field in a temp table, so I can use the update statement to populate the data later.
I tried [Select ' ' as field] and then tried the update statement to later populate data and I am getting the following error:
Msg 8152, Level 16, State 14, Line 1
String or binary data would be truncated.
The statement has been terminated.
Please help
View 3 Replies
View Related
Sep 20, 2006
select uid, OrderID, Count(OrderID)As DupCnt
from OrdDetails
group by uid, OrderID
having count(OrderID) > 1
this returns no rows, can't I show another column to identify which uid goes with the dups, I did it before and now it doesn't work, probably something silly I'm missing.
thx,
Kat
View 9 Replies
View Related
Aug 2, 2004
hey all,
I am writing a sproc:
select @strCourseNameRegFor = sal.CourseName , @strSectionNoRegFor = sal.SectionNo,
@dteStartDateRegFor = sal.StartDate, @dteEndDateRegFor = sal.EndDate,
@dteStartTimeRegFor = sal.StartTime, @dteEndTimeRegFor = sal.EndTime,
@strDaysOfWeekRegFor = sal.DaysOfWeek
from lars.dbo.tblSalesCourse as sal, lars.dbo.tblCourseCatalog as cat
where sal.SchoolYr = @intRegForYear and rtrim(sal.SchoolTerm) = rtrim(@strRegForTerm) and upper(rtrim(sal.CourseName)) = upper(rtrim(@strCourseNamePrev))
and cat.NewStuAllowed = 0 and sal.CourseName = cat.CourseName and sal.Cancelled <> 1 and cat.SchoolYr = sal.SchoolYr
and sal.MaxNoStudents > sal.CurrNoStudents
I want to check if the select returned an empty set or not. I cannot use @@rowcount because i am assigning the values to the local vars. I tried
if @strCourseNameRegFor is null
begin
set @err = 'No courses';
end
but for some reason even if there are any records in the set, the if condition is getting satisfied. Can anyone help?
View 3 Replies
View Related
Mar 24, 2006
Hi Everyone,
For my application, i am writing a generalized Sp for the Select Stmt.
I have started with as shown below,
Create procedure proSelect
@TabName varchar(1000),
@ColName varchar(1000),
@ConName varchar(1000)
As
Begin
Declare @str Varchar(8000)
If @ColName = '' Set @ColName = '* '
else Set @ColName = @ColName + ' '
Set @str = 'Select ' +@ColName+ 'From ' +@tabname
If @ConName <> ''
Set @str = @str + ' Where '+ @ConName
exec (@str)
End;
I wan to more generalize it.. so that all the functionalities of select stmt can be accomplished with this sp...
Can anyone help ???
Thanks in advance...
one more small doubt(personal): All these days i was a starting member of this forum,but today it has changed to 'Yak Veteran Posting'.. What does that mean.. If not to be disclosed in forum, Do mail me at
satishr@kggroup.com
Regards,
satish.r
"Known is a drop, Unknown is an Ocean"
View 8 Replies
View Related
Mar 17, 2008
Hello friends , I have table (MoneyTrans) with following structure
[Id] [bigint] NOT NULL,
[TransDate] [smalldatetime] NOT NULL,
[TransName] [varchar](30) NOT NULL, -- CAN have values 'Deposit' / 'WithDraw'
[Amount] [money] NOT NULL
I need to write a query to generate following output <br>
Trans Date, total deposits, total withdrawls, closing balance <br>
i.e. Trans Date, sum(amount) for TransName='Deposit' and Date=TransDate , sum(amount) for TransName=Withdraw and Date=TransDate , Closing balance (Sum of deposit - sum of withdraw for date < = TransDate )
I am working on this for past two days with out getting a right solution. Any help is appreciated
Sara
View 2 Replies
View Related
Jan 11, 2007
Hello Everybody,I have a problem, with select stmt:SELECT TOP 15 *FROM oaVIEW_MainData AS TOP_VIEW,oaLanguageData_TAB AS RwQualifierJoin with (nolock)WHERE (c_dateTime>='2007.01.10 00:00:00' AND c_dateTime<='2007.01.1023:59:59')AND RwQualifierJoin.text_id = c_cfgRegPointAND (((RwQualifierJoin.local1 LIKE N'Position of any bubu')))AND TOP_VIEW.c_dateTime=(SELECT MAX(SUB_VIEW.c_dateTime)FROM oaVIEW_MainData AS SUB_VIEW,oaLanguageData_TAB ASRwQualifierJoin1 with (nolock)WHERE (c_dateTime>='2007.01.10 00:00:00' AND c_dateTime<='2007.01.1023:59:59')AND RwQualifierJoin1.text_id = c_cfgRegPointAND (((RwQualifierJoin1.local1 LIKE N'Position of any bubu')))AND TOP_VIEW.c_dsmIdent=SUB_VIEW.c_dsmIdent)order by c_dateTime descPlease consider:- top doesn't metter, if I will use one or 10000 result is always thesame.- oaVIEW_MainData, is a view on major big table, holding lot of recordsjoinden with small table containing configuration data, over left outerjoin; both tables are with nolock option,- quersy supose to return last record from major table/view, in giventime, additionaly, with other where conditions (like in this case withtext),- on major table, are indexes which one is on id field (not used inthis query at all), which is a pk clustered, and other is on dateEvt(c_dateTime) which is a desc index with fill level 90%- table has also other indexes, on three different fields, one oftheses is dsmIdent,Now, if I'm using max(id) works very fast, and ok for me, but theproblem is, I should not use id, because might be, that the recordswill be written in the table with random order, so the only one sayingwhich is newest, will be dateEvt.Using dateEvt as max(), dramaticly slows query, so I'm acctualy unableto get result. What is much more funny, server is totaly busy with thisquery, and it's procesor jumps on 100%.Now, because the query is builded dynamicly, by a user selections,that's why we decided on such a parser ... problem is, it is notworking :(Can I change index on dateEvt somehow, to sped this up?Maybe construct query somehow different, to get this over max() date?Please helpMatik
View 1 Replies
View Related
Feb 7, 2008
I've created a Stored Procedure which performs a Select against my table, and displays the rows returned via these stmts -
@RowCount int Output
SELECT @rowcount = @@RowCount
This Works fine when Executed from SQL Server, but when trying to invoke the SP from my ASP page it complains that the SP expects parameter '@RowCount' which was not supplied.
I don't need to supply it when invoking the SP directly, why do I need to supply it from ASP?
I tried defining it as NULL within my SP, but can't seem to get it to accept both the NULL & Output parms.
And while I'm at it, how do I get my ASP page to display this @RowCount value?
Many Thanks.
View 21 Replies
View Related
Nov 21, 2005
Friends,
What are the possible usuages of a SELECT query stmt inside a stored procedure ??
How can we process the results of the SELECT query other than for documentation/Reporting purposes(Correct me if i'm wrong in this) ??
can any one throw some lite on this ..
Thanks,
SqlPgmr
View 1 Replies
View Related
Apr 30, 2004
I'm trying to execute a different SELECT statement depdning on a certain condition (my codes below). However, Query Analyzer complains that 'Table #Endresult already exists in the database', even though only one of those statements would be executed depending on the condition. Any ideas as to a work around? I need the result in an end temporary table.
IF @ShiftPeriod = 'Day'
SELECT * INTO #EndResult FROM #NursesAvailable WHERE CanWorkDays = 1
ELSE IF @ShiftPeriod = 'Night'
SELECT * INTO #EndResult FROM #NursesAvailable WHERE CanWorkNights = 1
ELSE IF @ShiftPeriod = 'Evenings'
SELECT * INTO #EndResult FROM #NursesAvailable WHERE CanWorkEvenings = 1
ELSE
SELECT * INTO #EndResult FROM #NursesAvailable
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
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
Feb 17, 2006
I have a interger stored in x.
I want to use x in a SELECT statement like so :
SELECT * from aTable WHERE A_Column = x
This select statement is then assigned to and passed as a string like :
sql = "SELECT * from aTable WHERE A_Column = x"
How does the x get interpreted correctly ?
View 1 Replies
View Related
Jul 20, 2005
Is there any SQL Error?Or I have to use Select case in VB code to control SQL instead.Thank you for any ans.Nuno
View 4 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 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 3, 2001
I have a need to execute a SQL stmt on SQL 6.5 from a 7.0 box and vice
versa. I know that in 7.0, you can create a remote server and execute
statements across 7.0 servers, but can it be done from 6.5 to 7.0 and
7.0 to 6.5?
It's for a conversion project and time is the essence.
TIA,
Mike
View 1 Replies
View Related
Nov 2, 2005
does anyone have a script that cleans up profiler traces by removing the variables from tsql. thereby giving you the procedure text as written.
I saw this a few months ago and havent had any luck in finding it.
anyone?
View 1 Replies
View Related
May 10, 2004
hi, all..
the following is part of my sp
I want to know how to assign result of stmt.1 to @tp
declare @tp datetime
declare @tableName varchar(100)
SET @tableName = 'tblState'
Exec ('SELECT MAX(UpdateTime) FROM ' + @tableName) -- stmt.1
thank you..
View 6 Replies
View Related
Apr 1, 2008
I am using the kill statement to terminate a process, and this is done through my VB.net program. But I get the error
User does not have permission to use the KILL statement
This is the store procedure to call for kill statement..Is there any problem to execute it on dinamic sql?
CREATE PROCEDURE Kill_Process
@DBName VARCHAR(100),
@TableName VARCHAR(1000)
AS
SET NOCOUNT ON;
DECLARE @spid smallint;
DECLARE @spid2 smallint;
DECLARE @loginame nchar(128);
DECLARE @nsql NVARCHAR(4000);
SET @loginame = 'xxxx'
EXEC Find_Lock_Info @DBName, @TableName
DECLARE ProcessCursor CURSOR FOR
SELECT spid FROM master.dbo.sysprocesses
WHERE dbid = db_id(@dbname) AND loginame = rtrim(@loginame) AND spid <> @@spid
AND spid IN (SELECT spid FROM dbo.tbl_Lock_Info where dbid = db_id(@DBName) AND OBJECT_NAME(ObjId) = @TableName)
OPEN ProcessCursor;
FETCH NEXT FROM ProcessCursor INTO @spid;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @nsql = 'KILL ' + CONVERT(nvarchar,@spid) + '';
EXEC sp_executesql @nsql;
FETCH NEXT FROM ProcessCursor INTO @spid;
END
CLOSE ProcessCursor;
DEALLOCATE ProcessCursor;
GO
CREATE PROCEDURE Find_Lock_Info
@DBName VARCHAR(100),
@TableName VARCHAR(1000)
AS
SET NOCOUNT ON
BEGIN
CREATE TABLE #tmpLockInfo
(
spid SMALLINT,
dbid SMALLINT,
ObjId INT,
IndIdSMALLINT,
Type NCHAR(4),
ResourceNCHAR(32),
ModeNVARCHAR(8),
StatusNVARCHAR(5)
)
INSERT INTO #tmpLockInfo
(
spid, dbid, ObjId, IndId, Type, Resource, Mode, Status
)
EXEC Lock_Info
TRUNCATE TABLE tbl_Lock_Info
INSERT tbl_Lock_Info
SELECT
spid, dbid, ObjId, Type, Status
FROM #tmpLockInfo WHERE dbid = db_id(@DBName) AND ObjId <> 0 AND OBJECT_NAME(ObjId) = @TableName
END
GO
View 5 Replies
View Related
May 11, 2007
Hi,
I have two tables:
1. RubricReport
2. RubricReportDetail
How can I code this step in my stored procedure:
If @ReportID is NULL, insert a row into RubricReport table, and set @ReportID=@@IDENTITY; otherwise, update table RubricReport for columns LastUpdate and LastUpdateBy, and delete table RubricReportDetail where ReportID=@ReportID.
Table RubricReport has columns ReportID, County,Dsitrict, DataYears, LastUpdate and LastUpdateBy
Table RubricReportDetail has columns ReportID, IndicatorID, LocalPerf
Kindly help me.
Thanks in advance
View 3 Replies
View Related
Mar 5, 2008
Hi experts,
I have a job that has several steps.
One of the steps is of type T-SQL. I have an IF...ELSE block in it. If the if statement is true...perform an action.
Else exit from the job reporting failure.
I would like to know what sql stmts should i use to exit from the job (in the ELSE block)
I tried to use EXIT in the else stmt but it is not exiting from the job step.
Thanks in advance
View 4 Replies
View Related
Oct 16, 2006
assume connection is established and photo cloumn as data type as image in sql table.str_insert = "INSERT INTO SIS_TeachingStaff VALUES('TSMT2','V.RAJANIKANTH','LECTURER','TEACHING STAFF','Msc(Maths)','" & Emp_pic.Image &"' " cmd = New SqlCommand(str_insert, conn)cmd.Connection = conncmd.ExecuteNonQuery()Error at insert stmt: Operator '&' is not defined for types 'string' and 'system.drawing.image'
View 1 Replies
View Related
May 4, 2008
I'm trying to execute the following stmt within a Stored Procedure on SQL Server 2005 -
SELECT DISTINCT PkgActions.[PKG ID] AS PKG_ID, [APPR STATUS] AS Status, [END EXEC DATE] AS "Exec Date", [COMMENT] AS Comment FROM [PkgActions]
JOIN PkgApprovers ON PkgActions.[PKG ID] = PkgApprovers.[PKG ID]
WHERE 1=1 and ([APPR STATUS] = 'approved' OR [APPR STATUS] = 'Denied')
It fails with the message - Incorrect syntax near 'approved'.
However when I run the same stmt within the Query editor it works properly.
I can't see what the SP doesn't like about this. Any thoughts?
Thanks
View 4 Replies
View Related
Jul 1, 1999
I am trying to create a stored procedure for automating Bulk inserting into tables
for one database to another.
Is there any way i can pass the table name as variable to insert and select stmt
thanks in advance
View 1 Replies
View Related
Nov 26, 1998
We have a MS SQL Server 6.5 database table with 643,000 records.
There are several indexes including some clustered indexes.
We do a statement: update wo set udf3 = '1234567890123456' where woid = '123'
this returns immediately.
Then we try the same statement where the string is 1 character longer and it
takes 45 minutes to return. There is no indication of what the server is doing
during this time.
There is no index on UDF3 and WOID is the primary key.
Any suggestions what is happening? What can we do to correct it?
DBCC CheckTable finds no errors.
name rows reserved data index_size unused
-------------------- ----------- ------------------ ------------------ ------------------ ------------------
WO 643124 493418 KB 321580 KB 169824 KB 2014 KB
View 1 Replies
View Related
May 5, 2004
I have a relatively simple update statement that runs fine from Query Analyzer and Enterprise Manager (two rows updated in less than 1 sec) but times out when I run it from a VBScript file.
Any ideas?
Thavalai
View 8 Replies
View Related
Jul 20, 2005
Hi,Don't worry about the vars, they are defined,the following line give me an err of "Incorrect syntax near '.'."Goal: to rename nonstardard column name.EXEC sp_rename '+@tbuffer+'.['+@cbuffer+']','+Replace(+@cbuffer+','%[^A-Za-z0-9_#$@]%','')','COLUMN';Thanks.
View 6 Replies
View Related
Jul 20, 2005
Hi all,I am doing the change from having worked in Oracle for a long time toMS SQL server and am frustrated with a couple of simple SQL stmt's. Orat least they have always been easy.The SQL is pretty straightforward. I am updating a field with a Maxeffective dated row criteria. (PepopleSoft app)update PS_JOB as A set BAS_GROUP_ID = ' 'where EMPL_STATUS in ('D', 'L', 'R', 'S', 'T')and EFFDT = (select max(EFFDT) from PS_JOB where EMPLID = A.EMPLID)This stmt is not working. I am getting an error on the keyword 'as'. Ihave tried:update PS_JOB A set...update PS_JOB from PS_JOB A set...Same result, error on 'A' or error on 'from'.I also tried to add the table alias to the sub query, whichtechnically worked, but with wrong data result.So my question comes down to: How do I use a table alias in an updatestatement in MS SQL server?I worked around this by creating a temp table. But that does notfulfill my curiosity, nor is it an ideal solution.Thanks a lot,-OK
View 14 Replies
View Related
Aug 30, 2001
I'd like to alter a table and add a column:
add_date datetime
Is there a way, in the ALTER statement, to have the value default to the current date -- GETDATE() -- anytime a row is inserted w/out an explicit value for the column.
Thx
View 1 Replies
View Related
Nov 28, 2007
Hi there,
The following is my table whereby i have joined projects table with issue table (this is 1 to many relationship).
I have the following query:
SELECT
odf.mbb_sector sectorid,
SUM(case when odf.mbb_projecttype = 'lkp_val_appl' then 1 else 0 end) total_appl,
SUM(case when odf.mbb_projecttype = 'lkp_val_infrastructure' then 1 else 0 end) total_infra,
SUM(case when odf.mbb_projecttype = 'lkp_val_eval' then 1 else 0 end) total_eval,
SUM(case when odf.mbb_projecttype = 'lkp_val_subproject' then 1 else 0 end) total_subprj,
SUM(case when odf.mbb_projecttype = 'lkp_val_nonit' then 1 else 0 end) total_nonit,
SUM(case when odf.mbb_projecttype = 'lkp_val_adhocrptdataextract' or
odf.mbb_projecttype = 'lkp_val_productionproblem' or
odf.mbb_projecttype = 'lkp_val_maintwoprogchange' then 1 else 0 end) total_others,
COUNT(distinct prj.prid) total_prj
FROM
PRJ_PROJECTS AS PRJ,
SRM_PROJECTS AS SRM,
ODF_CA_PROJECT AS ODF
LEFT JOIN RIM_RISKS_AND_ISSUES AS RRI ON RRI.pk_id = odf.id
WHERE
prj.prid = srm.id
AND srm.id = odf.id
AND srm.is_active =1
AND odf.mbb_projecttype not in ('lkp_val_budget','lkp_val_itpc')
AND odf.mbb_funcunit = 'lkp_val_operation'
GROUP BY
odf.mbb_sector
which returns me the following result :
.
The problem is at the lkp_val_infosystem where it returns 3 instead of 1 in the total_infra column. How do I correct my case stmt to return the correct no of projects breakdown by different project type? Currently, only the total_prj which returns correct data.
Thanks
View 3 Replies
View Related
Aug 24, 2007
How do I pass a parameter from a SSRS report to the sql stmt in a SSIS package?
Mainly need to know the correct syntax of the connection string to use for the datasource in the SSRS report. Every time I add the /SET part of the string the connection breaks.
The connection string i've been using is:
/file "C:\PackageName.dtsx /Set Package.Variables[StartDate];"&Parameters!StartDate.Value
View 26 Replies
View Related