Select STMT
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
ADVERTISEMENT
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
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
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
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
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 3 Replies
View Related
Apr 29, 2008
I'm troubleshooting a performance issue , Looking at Profiler - for the given statement, I'm getting the following figures , why would there be such a disparity between the figures. ? How can I go about finding out why there is such difference?
SQL:Stmt Completed:CPU = 31, Reads = 129 , Duration = 32
SQL:Batch Completed: CPU = 2531, Reads = 6087 , Duration = 2593
Jack Vamvas
--------------------
Search IT jobs from multiple sources- http://www.ITjobfeed.com
View 2 Replies
View Related
Apr 29, 2008
Hello all,
I have two mult-value parameters in my report. Both of them working with selecting one or more values. But, when I test using "(Select All)" values for both parameters , only one parameter works. The "available values" for these two parameters are both from the data set.
select distinct ProductType
from Product
order by ProductType
Any suggestion? thx
View 12 Replies
View Related
Aug 28, 2015
I am unable to the access on table even after providing the SELECT permission on table.
Used Query by me :
Here Test is schema ; Card is table ; User is Satish
To grant select on Table
GRANT SELECT ON TEST.Card TO satish
Even after this it is not working, So provided select on schema also.
used query : GRANT SELECT ON SCHEMA::TESTÂ TO Satish.
View 8 Replies
View Related
Jan 11, 2008
Hi all,
I copied and executed the following sql code in my SQL Server Management Studio Express (SSMSE):
--SELECTeg8.sql from SELECT-Using correlated subqueries of MSDN2 SELECT Examples--
USE AdventureWorks ;
GO
SELECT DISTINCT Name
FROM Production.Product p
WHERE EXISTS
(SELECT *
FROM Production.ProductModel pm
WHERE p.ProductModelID = pm.ProductModelID
AND pm.Name = 'Long-sleeve logo jersey') ;
GO
-- OR
USE AdventureWorks ;
GO
SELECT DISTINCT Name
FROM Production.Product
WHERE ProductModelID IN
(SELECT ProductModelID
FROM Production.ProductModel
WHERE Name = 'Long-sleeve logo jersey') ;
GO
=========================================
I got:
Results Messages
Name o row affected
========================================
I think I did not get a complete output from this job. Please help and advise whether I should search somewhere in the SSMSE for the complete results or I should correct some code statements in my SELECTeg8.sql for obtaining the complete results.
Thanks in advance,
Scott Chang
View 5 Replies
View Related
Aug 4, 2015
I have a report that uses different datasets based on the year selected by a user.
I have a year_id parameter that sets a report variable named dataset_chosen. I have varified that these are working correctly together.
I have attempted populating table cell data to display from the chosen dataset. As yet to no avail.
How could I display data from the dataset a user selects via the year_id options?
View 4 Replies
View Related
Aug 16, 2007
Dear All
I need to cerate a SP that SELECTS all the records from a table WHERE the first letter of each records starts with 'A' or 'B' or 'C' and so on. The letter is passed via a parameter from a aspx web page, I was wondering that someone can help me in the what TSQL to use I am not looking for a solution just a poin in the right direction. Can you help.
Thanks Ross
View 3 Replies
View Related
