I am doing the change from having worked in Oracle for a long time to
MS SQL server and am frustrated with a couple of simple SQL stmt's. Or
at least they have always been easy.
The SQL is pretty straightforward. I am updating a field with a Max
effective 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'. I
have 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, which
technically worked, but with wrong data result.
So my question comes down to: How do I use a table alias in an update
statement in MS SQL server?
I worked around this by creating a temp table. But that does not
fulfill my curiosity, nor is it an ideal solution.
I have some stored procedures that do updates using table aliases, like this:
UPDATE TableAlias SET ColVal = 1 FROM RealTable AS TableAlias WHERE TableAlias.ColVal <> 1
This allows me to include joins and stuff in the update (not shown) and make it all more readable for me.
It all works fine, and the SSMS parser says it's fine. But I also have another script which looks at sys.sql_expression_dependencies and sys.objects to find stored procedures with invalid object references (see below), and it's understandably saying that all of the above type stored procedures have invalid references.
SELECT OBJECT_NAME(DEP.referencing_id) AS referencing_name, DEP.referenced_entity_name FROM sys.sql_expression_dependencies AS DEP WHERE -- Only validate local references: ( DEP.referenced_database_name = DB_NAME()
[Code] ....
So I have a couple questions.
1. Is the UPDATE syntax I'm using kosher? 2. Can you recommend any updates to my stored procedure validation script that will better accommodate table aliases like mine?
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
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
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.
I am using MsSql2005 and wondering how to create aliases inside update statements. I need this, for a table variable inside a procedure.
UPDATE @TempTable SET Field1 = ( SELECT RealTable.Field2 FROM RealTable WHERE RealTable.Field1 = @TempTable.Field1) This statement doesn't work because I need to use an alias for @TempTable.
We will be moving 2 different databases (SS2005 & SS2008) to a new SS2014 SQL Server. Currently our codes looks something like Server1DBInstance1... & Server2DBInstance2... Is it possible to move the objects from these 2 instances to Server3DBInstance3 and then use an alias to reference the objects? Or does Server3 need to have DBInstance1 & DBIstance2? Basically, is the alias just for the database or for the instance too? Can I create an alias "Server1DBInstance1' on Server3DBInstance3 and assign objects to that alias?
I have query related to using of Alias names for the Tables in SQL query, will the performance of the query affected if we use Alias name for tables in joins. considering the fact that there was no need to use alias name in those joins.
Hi, I have a problem which i need to get the NewDate which the NewDate is Generated thru Computation of DateDiff Here's my Statement
SELECT DISTINCT RBUCode, DCO, Prov, District, DATEDIFF(mi, ReportedDate + ' ' + ReportedTime, CompletedDate + ' ' + CompletedTime) / 60 AS NewDate FROM PostedFaultTable WHERE (DCO = 'La Union')
I need to get the Less than 5 Hrs of the NewDate
I tried This but it's not working RBUCode, DCO, Prov, District, DATEDIFF(mi, ReportedDate + ' ' + ReportedTime, CompletedDate + ' ' + CompletedTime) / 60 AS NewDate FROM PostedFaultTable WHERE (DCO = 'La Union') and NewDate <= 5
Is table alias using in a query only for Readablity? Is there any difference in performence between a query with table alias and a query without table alias?
We have very big table names in our database like T_SALES_INDIA_TEST_EMPLOYEE_DETAILS . instead of retriving the complete table name i would like to comment the table name as employye Details and use it for display purpose on user interfaces.
So that when i retrive the list of tables from information_schema.tables.I would like to retrive the comment which i have given instead of the table name.
I have a query from an ACCESS database that I want to move over to SISS, but it will not run and says the syntax is wrong. How to correct the syntax on the query below?
ACCESS Query:
UPDATE PROPERTYOWNERS INNER JOIN PROPERTYOWNERS AS PROPERTYOWNERS_1 ON PROPERTYOWNERS.gislink = PROPERTYOWNERS_1.gislink SET PROPERTYOWNERS.mixedownership = "MIXED" WHERE (((PROPERTYOWNERS_1.secondaryclass)<>[PROPERTYOWNERS]![secondaryclass]));
I have an Access database, that is in connection with sql server. On my computer with access2007 i have no problems with viewing tables and stuff.
But on other computers with access2003 it gives an error when i use this query:
INSERT INTO [tbl_sap-staffel] ( ItemCode, CardCode, [Amount-0], [Price-0], [Amount-1], [Price-1], [Amount-2], [Price-2] ) SELECT [qry_sap-spp-0].ItemCode, [qry_sap-spp-0].CardCode, [qry_sap-spp-0].Amount, [qry_sap-spp-0].Price, [qry_sap-spp-1].Amount, [qry_sap-spp-1].Price, [qry_sap-spp-2].Amount, [qry_sap-spp-2].Price FROM ([qry_sap-spp-0] LEFT JOIN [qry_sap-spp-1] ON ([qry_sap-spp-0].ItemCode = [qry_sap-spp-1].ItemCode) AND ([qry_sap-spp-0].CardCode = [qry_sap-spp-1].CardCode)) LEFT JOIN [qry_sap-spp-2] ON ([qry_sap-spp-1].ItemCode = [qry_sap-spp-2].ItemCode) AND ([qry_sap-spp-1].CardCode = [qry_sap-spp-2].CardCode);
It says: ODBC call failed [Microsoft][ODBC SQL Server Driver][SQL Server] The column prefix 'MS1' does not match with a table name or alias name used in the query. The column prefix 'MS2' does not match with a table name or alias name used in the query.
I have a program that connects to SQLServer 2000 through ADO connection.
the program executes the following query:
SELECT ax.AccNo, (SELECT Accounts.ProductCode FROM Accounts WHERE h.ID=Accounts.ID) As Product FROM dbo.History h LEFT OUTER JOIN dbo.AccXRef ax ON h.ID= ax.ID LEFT OUTER JOIN dbo.States ON h.[HistoryItemsub-Type] = dbo.States.Type LEFT OUTER JOIN dbo.CustXRef cx ON h.CustomerNo = cx.CustomerNo WHERE HistoryItemDate <= getdate() ORDER BY HistoryItemDate ASC
This query works in th program and in Query Analyer on my machine. However, On a different Machine (and different SQLServer) the query works in Query Analyser but does not work in the program, the following exception is thrown:
The column prefix 'h' does not match with a table name or alias name used in the query
Can someone please answer a problem that I've run into. I know that it's probably something stupid. I keep getting this error:Server: Msg 107, Level 16, State 3, Line 1The column prefix 'vFirstTimeEntered' does not match with a table name or alias name used in the query.Here is my query:-----------------------------------------------------------------Update TimeSheetSectionSet TimesheetSection.SECSTARTDT = vFirstTimeEntered.schlstuidWhere timesheetsection.schlstuid = vFirstTimeEntered.schlstuid AND timesheetsection.sectionid = vFirstTimeEntered.sectionid AND Timesheetsection.secstartdt < '2005-08-01'------------------------------------------------------------------vFirstTimeEntered is a view that I created.Do I need a sub query? I know that if this was a select query I'd need to put vFirstTimeEntered in the FROM part but I don't know where it should go here.Thanks for any assistance.Scott
I get the error "The column prefix 'contacts' does not match with a table name or alias used in the query".I am trying to obtain all fields from the communications table whether it is used or NULL.
I have data in a table (@Outer) that I am matching to a lookup table (@Inner) which contains multiple "matches" where nulls can match any value. By sorting the inner table and grabbing the top record, I find the "best" match. I know the sort and the null matches work but I don't understand why the correlated sub query below doesn't understand that the OJ prefix refers to the outer table.DECLARE @Outer TABLE ( OuterID int IDENTITY (1, 1) NOT NULL, MethodID int NULL, CompID int NULL, FormID int NULL, InnerID int NULL )
-- UPDATE Outer Table with best match from Inner table UPDATE @Outer SET InnerID = IJ.InnerID FROM @Outer OJ INNER JOIN ( SELECT TOP 1 I.* FROM @Inner I WHERE IsNull(I.MethodID, OJ.MethodID) = OJ.MethodID AND IsNull(I.CompID, OJ.CompID) = OJ.CompID AND IsNull(I.FormID, OJ.FormID) = OJ.FormID ORDER BY I.MethodID DESC, I.CompID DESC, I.FormID DESC ) IJ ON OJ.MethodID = IsNull(IJ.MethodID, OJ.MethodID) AND OJ.CompID = IsNull(IJ.CompID, OJ.CompID) AND OJ.FormID = IsNull(IJ.FormID, OJ.FormID) SELECT * FROM @Outer The result should be OuterID 1 matched to Inner ID 3 and OuterID 2 matched to Inner ID 5. Can anyone help me? Thanks in advance.
Hi,I have table with three columns as belowtable name:expNo(int) name(char) refno(int)I have data as belowNo name refno1 a2 b3 cI need to update the refno with no values I write a query as belowupdate exp set refno=(select no from exp)when i run the query i got error asSubquery returned more than 1 value. This is not permitted when thesubquery follows =, !=, <, <= , >, >= or when the subquery is used asan expression.I need to update one colum with other column value.What is the correct query for this ?Thanks,Mani
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
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.
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.
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.
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
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.
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.
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'
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?
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
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???
