Hi everyone, is there any way to turn off SQL server re-writing the syntax of certain queries? An example would be if in the where you set primary keys equal to foreign keys and then it converts it to inner-joins. Thanks for your help.
Hi all, I have the "Northwind" database in my Sql Server Management Studio Express.
In my C:ProSSEAppsSamplesForChapter02Chapter02 folder, I have the following 2 files: (1) ListColumnValues (MS-DOS Batch File) sqlcmd -S .sqlexpress -v DBName = "Northwind" CName = "CompanyName" TName = "Shippers" -i c:prosseappschapter02ListListColumnVales.sql -o c:prosseappschapter02ColumnValuesOut.rpt (2) ListColumnValues (Microsoft SQL Server Query File) USE $(Northwind) GO SELECT $(CompanyName) FROM $(Shippers) GO When I ran the following SQLcmd: C:ProSSEAppsSamplesForChapter02Chapter02>ListColumnValues.bat I got the following "ColumnValuesOut.rpt" with error messages:
'Northwind' scripting variable not defined. Msg 102, Level 15, State 1, Server L1P2P3SQLEXPRESS, Line 1 Incorrect syntax near '$'. 'CompanyName' scripting variable not defined. 'Shippers' scripting variable not defined. Msg 102, Level 15, State 1, Server L1P2P3SQLEXPRESS, Line 1 Incorrect syntax near 'CompanyName'.
I copied these T-SQL statements from a book and I do not know how to correct them. Please help and tell me how to correct these errors.
Select e.name, e.employee_id, d.department_name from employees e,departments d where e.department_id = d.department_id
An associate recommended that I change this query to add the words INNER JOIN like this:
Select e.name, e.employee_id, d.department_name from employees e inner join departments d on e.department_id = d.department_id
I was of the understanding that the first query was Microsoft SQL Syntax, and that the second example was ANSI SQL syntax, but that they executed on the server the same way. My associate however thinks that the second example will execute faster.
select Count(*) from iCalls_Events where Call_ID = " & Session("Call_ID") & " select Count(*) from iCalls_Events where Call_ID = "& Session("Call_ID") & " and Events_Flag <> 0
in this query
Code:
select iCalls_Calls.Call_ID,iCalls_Calls.Requestor,Type,Scope,iCalls_Calls.Status_ID,iCalls_Status.Status_I D, iCalls_Status.Status_Label from ((iCalls_Calls inner join iCalls_Status on iCalls_Calls.Status_ID=iCalls_Status.Status_ID ) inner join iCalls_Users on iCalls_Calls.Requestor=iCalls_Users.User_ID) left outer join iCalls_Messages on iCalls_Calls.Call_ID=iCalls_Messages.Call_ID where Requestor='" & Session("User_ID") & "' AND iCalls_Calls.Status_ID <> 6 order by iCalls_Calls.Call_ID
((iCalls_Calls inner join iCalls_Status on iCalls_Calls.Status_ID=iCalls_Status.Status_ID )
I want to add ( / ) in between these 2 queries. The reason is for example first query will return '5' and second '10' , so the output i need is 5 / 10. And i need to put this query in a variable (Countrec) like
Code:
select Count(*) from iCalls_Events where Call_ID = " & Session("Call_ID") & " ( / ) select Count(*) from iCalls_Events where Call_ID = "& Session("Call_ID") & " and Events_Flag <> 0 as Countrec
The Final Query would be something like this
Code:
select iCalls_Calls.Call_ID,iCalls_Calls.Requestor,Type,Scope,iCalls_Calls.Status_ID,iCalls_Status.Status_I D, iCalls_Status.Status_Label, select Count(*) from iCalls_Events where Call_ID = " & Session("Call_ID") & " ( / ) select Count(*) from iCalls_Events where Call_ID = "& Session("Call_ID") & " and Events_Flag <> 0 as Countrec from ((iCalls_Calls inner join iCalls_Status on iCalls_Calls.Status_ID=iCalls_Status.Status_ID ) inner join iCalls_Users on iCalls_Calls.Requestor=iCalls_Users.User_ID) left outer join iCalls_Messages on iCalls_Calls.Call_ID=iCalls_Messages.Call_ID where Requestor='" & Session("User_ID") & "' AND iCalls_Calls.Status_ID <> 6 order by iCalls_Calls.Call_ID
but this syntax is not correct..Please can U get me the Correct Syntax.
I have a query that I am running out of sql server 2000 that is pulling duplicate records. I can probably figure it out but I am wondering if someone could look at it and point out errors in my syntax and/or structure.
Thanks!
code:-------------------------------------------------------------------------------- SELECT p.ParticipantID, pr.RaceID, p.FirstName, p.LastName, pr.Bib, p.Gender, pr.Age, pr.AgeGrp, p.DOB, p.Address, p.City, p.St, p.Zip, pr.Clyde, pr.WhlChr, pr.RcWlk, p.Phone, p.Email, reg.ShrtSize, reg.ShrtStyle, reg.WhereReg, reg.DateReg, reg.AmtPd FROM Participant p INNER JOIN PartReg reg ON p.ParticipantID = reg.ParticipantID JOIN PartRace pr ON pr.ParticipantID = p.ParticipantID JOIN RaceData rd ON pr.RaceID = rd.RaceID WHERE (rd.EventID = 45 AND pr.RaceID = reg.RaceID) ORDER BY p.LastName --------------------------------------------------------------------------------
I have a query that I am running out of sql server 2000 that is pulling duplicate records. I can probably figure it out but I am wondering if someone could look at it and point out errors in my syntax and/or structure.
Thanks!
code:-------------------------------------------------------------------------------- SELECT p.ParticipantID, pr.RaceID, p.FirstName, p.LastName, pr.Bib, p.Gender, pr.Age, pr.AgeGrp, p.DOB, p.Address, p.City, p.St, p.Zip, pr.Clyde, pr.WhlChr, pr.RcWlk, p.Phone, p.Email, reg.ShrtSize, reg.ShrtStyle, reg.WhereReg, reg.DateReg, reg.AmtPd FROM Participant p INNER JOIN PartReg reg ON p.ParticipantID = reg.ParticipantID JOIN PartRace pr ON pr.ParticipantID = p.ParticipantID JOIN RaceData rd ON pr.RaceID = rd.RaceID WHERE (rd.EventID = 45 AND pr.RaceID = reg.RaceID) ORDER BY p.LastName --------------------------------------------------------------------------------
am looking to make a select query that has some condition where it should look like: select if(SomeField is null) then 'Not Set' else SomeField from SomeTable
Hi all,I have the following tables:T1==ID Name-- ----1 Name12 Name2T2==ID Color-- ----1 Color11 Color22 Color22 Color3and I would like to get the following output (without duplicates):ID Colors-- ------1 Color1, Color22 Color2, Color3Is it possible?
Hello All,I have the following table:CREATE TABLE [dbo].[TBL_NAME] ([NAME] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[STANDARD_NAME] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_ASNULL) ON [PRIMARY]GOWith values:insert into tbl_namevalues('DAN', 'DANIEL')insert into tbl_namevalues('DANNY', 'DANIEL')insert into tbl_namevalues('DANYY', 'DANIEL')Question is:I need want to construct a query which returns all names for a standardname plus the standard name itself.e.g.if name = 'DAN' then return 'DAN', 'DANNY', 'DANYY', 'DANIEL'ff name = 'DANIEL', then return 'DAN', 'DANNY', 'DANYY', 'DANIEL'i have the following sql:declare @name varchar(50)select @name = 'DANIEL'select standard_name from tbl_name where name = @nameunionselect name from tbl_name where standard_name = (select standard_namefrom tbl_name where name = @name)unionselect name from tbl_name where standard_name = @nameunionselect standard_name from tbl_name where standard_name = @name----declare @name varchar(50)select @name = 'DANNY'select standard_name from tbl_name where name = @nameunionselect name from tbl_name where standard_name = (select standard_namefrom tbl_name where name = @name)unionselect name from tbl_name where standard_name = @nameunionselect standard_name from tbl_name where standard_name = @name----Both appear to work fine..can anyone see a fault or suggest a cleanerway to achieve the above ?Suggestions/pointers appreciatedThanks in advance
sql query syntax?i write this query but error get : column not foundhow can i write ?SELECT c.stock_cat_name, a.stock_code, c.description, a.date,IIF(MID(a.type,2,1)="I",SUM(a.quantity),0) AS iqty,IIF(MID(a.type,2,1)="O",SUM(a.quantity),0) AS oqtyFROM stock_tran AS a LEFT JOIN stock AS c ON c.stock_code=a.stock_codeWHERE a.date>=[from_date] And a.date<=[to_date]GROUP BY c.stock_cat_name, a.stock_code, c.description, a.date,MID(a.type,2,1)ORDER BY c.stock_cat_name, a.stock_code, a.date, MID(a.type,2,1);this database driver sage line 50 odbci write a simpale query of this database, successful run but when iwrite in condition (IF) is not run error comes : column not found.rashesh
Hi all! I am new here and I'm also new to SQL.. I hope somebody could help me regarding my problem.
I don't know if this is possible but I would like to have a code that can integrate data from 3 tables. The names of my tables are Savings, Loans and Insurances. Their common field is the MemberID. One member could have zero or more Savings Accounts. At the same time, a member could also have one or more accounts on Loans or Insurances.
How can I get the data that would appear like this:
I would like to combine the production times and down times into one summary where the down time is in the same date and shift as the production time. As you can see in my results below, I can group them correctly, but the down totals obviously repeat for each match. Is there any way of getting to the "Desired results"?
I am getting a SQLExcepetion error near , in this query string...so obvicously my query string is wrong... could someone help me get this query string right please...
Thanks "Select OrgID, OrgName From aspnet_OrgNames Where UserID = @UserID, OrgID = @OrgID"
I have modelled this query after another query which works fine and retrieves a result set. It accepts a search parameter and brings up all the websites in the database by searching on various columns for the search text. The article search query works the same way. I just changed the tables and parameters to match the tables. It uses the "view" and I had to go into the query and check a few columns in the query designer to add the column to the view. Here's the query with the syntax error: 1 set ANSI_NULLS ON 2 3 set QUOTED_IDENTIFIER ON 4 5 GO 6 7 ALTER PROCEDURE [dbo].[_spArticleSearch] 8 9 @search varchar (100), 10 11 @orderBy varchar(200)='ActiveMemberShip DESC, PageRank DESC, ArticleTitle, DateAdded DESC' 12 13 AS 14 15 EXEC('SELECT * from vArticle 16 17 where Active=1 AND ShowInDirectory=1 AND 18 19 ( 20 21 Articletitle like ''%' + @search + '%'' 22 23 OR Articletext LIKE ''%' + @search + '%'' 24 25 OR ShortDesc LIKE ''%' + @search + '%'' 26 27 OR Keywords LIKE ''%' + @search + '%'' 28 29 IN (SELECT ACategoryID FROM tblArticleCategory WHERE AActive=1 AND (ACategoryName LIKE ''%' + @search + '%'' OR AParentID IN(SELECT ACategoryID FROM tblArticleCategory WHERE AActive=1 AND ACategoryName LIKE ''%' + @search + '%''))) 30 31 ) 32 33 ORDER BY ' + @OrderBy ) 34 35 36
When I run the stored procedure and input a word into the search box, and it gives me this: Msg 156, Level 15, State 1, Line 8 Incorrect syntax near the keyword 'IN'. Msg 102, Level 15, State 1, Line 9 Incorrect syntax near ')'. (1 row(s) affected) This is the query it was modelled after which works fine and retrieves all the websites. 1 set ANSI_NULLS ON2 set QUOTED_IDENTIFIER ON3 GO4 5 ALTER PROCEDURE [dbo].[_spWebSiteSearch]6 @search varchar (100),7 @orderBy varchar(200)='ActiveMemberShip DESC, PageRank DESC, TotalExchangedLinks Desc, SiteTitle, SiteURL, DateAdded DESC'8 9 AS10 11 EXEC('SELECT * from vWebSite12 where Active=1 AND ShowInDirectory=1 AND13 (14 sitetitle like ''%' + @search + '%''15 OR SiteURL LIKE ''%' + @search + '%''16 OR SiteDescription LIKE ''%' + @search + '%''17 OR CategoryID IN18 (SELECT CategoryID FROM tblCategory WHERE Active=1 AND (CategoryName LIKE ''%' + @search + '%'' OR ParentID IN(SELECT CategoryID FROM tblCategory WHERE Active=1 AND CategoryName LIKE ''%' + @search + '%'')))19 )20 ORDER BY ' + @OrderBy )21 Does anyone know where the problem may be? Do the columns in the view have to be in order? When I added my columns, it adds them to the very far right side of the view. Does the columns to be in order in the same way it searches in the query?
Ok I can run the query below in SQL Query Analyzer with no problems. However when I place the SQL query in my asp.net page I get a syntax error. It looks like there is some issue I am not seeing can someone help me. The error i get is "Incorrect syntax near 'pb_sub_recipes_1'. " I have narrowed it down to the area in bold as to where the syntax error appears to be occuring.
SELECT DISTINCT pb_customers.customer_name FROM pb_sub_recipes AS pb_sub_recipes_1 INNER JOIN ((((((pb_jobs INNER JOIN pb_jobs_lots ON pb_jobs.job_id = pb_jobs_lots.job_id) INNER JOIN pb_recipes ON pb_jobs.recipe_id = pb_recipes.recipe_id) INNER JOIN pb_recipes_sub_recipes ON pb_recipes.recipe_id = pb_recipes_sub_recipes.recipe_id) INNER JOIN pb_customers ON pb_jobs.customer_id = pb_customers.customer_id) INNER JOIN pb_sub_recipes ON pb_recipes_sub_recipes.sub_recipe_id = pb_sub_recipes.sub_recipe_id) LEFT JOIN pb_report_shippers ON pb_jobs.job_id = pb_report_shippers.job_id) ON pb_sub_recipes_1.sub_recipe_id = pb_recipes_sub_recipes.sub_recipe_id WHERE (((pb_jobs.date_time)> '5/30/2004') AND pb_customers.customer_id ='228' AND ((pb_report_shippers.shipper_date_time) Is Null) AND ((pb_jobs.job_deleted)=0)) GROUP BY pb_customers.customer_name, pb_jobs.date_time, pb_sub_recipes.energy,pb_sub_recipes.dose,pb_jobs.job_id,pb_sub_recipes.specie,pb_sub_recipes_1.cost_per_wafer, pb_sub_recipes_1.setup_cost pb_sub_recipes_1.wafers_in_batch"
I am using SQL query analyzer and typed the following,
Update xupaddress as A1, xupaddress as B1 SET A1.address1 = upper(B1.address1) set A1.address2 = upper(B1.address2) set A1.city = upper(B1.city) set A1.state = upper(B1.state) where A1.ProfileID = B1.ProfileID
I am getting followin error : Server: Msg 156, Level 15, State 1, Line 1 Incorrect syntax near the keyword 'as'.
Set @complete = (SELECT Count(pass) as num FROM tblAttempts WHERE userID = @userID AND pass = 1)
If (@complete = 24) Begin Set @status = 'OK' End Else Begin Set @status = 'NOT OK' End GO
CREATE PROCEDURE [dbo].[checkComplete] @userID int,@status varchar (6) output AS DECLARE @complete int SET @complete = (SELECT COUNT(pass) AS num FROM tblAttempts WHERE userID = @userID AND pass = 1) IF (@complete = 24) BEGIN SET @status = 'OK' ENDELSE BEGIN SET @status = 'NOT OK' ENDGO
For reference, I have tried changing intUserID to be a different value (e.g. 13) to ensure it is not that '4' in question - likewise for @complete = 24 (e.g. 23).
Any ideas where the 4 is coming from and what is wrong here?
I have a MSSQL table (account_info) with the following fields:
SOLDTO_ACC_NO SOLDTO_ACC_NAME DUP_CODE BRANCH_NO
There are multiple account numbers for each dup_code (duplication code) in the table
What I need to get is ONE record for EACH dup_code that exists in the table. It doesn't matter which one, as long as there's one record pulled for each dup_code in there. I can do it with two queries, but I'd rather not do that, since I'm going to have to re-query for sorting and other reasons.
I believe this can be done with just one query, but I'm not sure how to do it. If I haven't explained well enough please let me know.
Hi all i am using a query SELECT DISTINCT lcactivityT.activitycategory_id, code, sort_order, description, lccategoryT.code_alias, lccategoryT.description_alias FROM {oj ActivityCategory AS categoryT INNER JOIN LicensedClientActivities AS lcactivityT ON lcactivityT.activitycategory_id = categoryT.activitycategory_id LEFT OUTER JOIN LicensedClientCategories AS lccategoryT ON categoryT.activitycategory_id = lccategoryT.activitycategory_id AND lccategoryT.licensedclient_id = '1'} WHERE lcactivityT.licensedclient_id = '1'
This works fine in mssql query analyser but when i use it in code, using mssql jdbc driver, i am getting following error
java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]Syntax error a t token ON , line 0 offset 84. at com.microsoft.jdbc.base.BaseExceptions.createExcep tion(Unknown Source .......
i removed the white spaces and i also have the correct driver, what can i do?
I need to query several tables in order to get the data that I want. What is the correct syntax for multiple SELECT statements? Do I need a seperator or similar?
Example:
select something, somethingelse from somewhere where something='1' AND somethingelse='True'
select anotherthing from anywhere where anotherthing='1'
I am a newbie when it comes to MS SQL Server administration and I am hoping you can help me out. We are migrating from a shared webhosting platform to our own internal dedicated web / MS SQL (2005) server and have encountered an error that appears to be stemming form Query Syntax.
In our old system we could simply query via the following format:
"Select [Column] from [Table Name]"
But on the SQL Server I just set up we have to query via this format:
"Select [Column] from [Database Name].[Table Name]"
We have literally hundreds of preprogrammed Queries and it would be quite difficult to change them all. Does anyone know how I can set up SQL Server to run so that our quieries do not require the DatabaseName in the statement? I have placed the connection code below, if that helps any.
Set objConn= Server.CreateObject("ADODB.Connection") objConn.Open "Provider=SQLOLEDB; Data Source =; Initial Catalog =; User Id =; Password=" Set objRec = Server.CreateObject("ADODB.Recordset").
HiCan someone please tell me whats wrong with the last line of the querybelow. The first three lines work fine but when i add the fourth line i getan error message (see text at ERROR MESSAGE)sql_HTTermijnRecords = "select * from Orders where FaktuurGeprint ='J'" & _"AND dathergestuurd Is Not Null " & _"AND PerBankKas Is Null " & _"AND " & HTdatumMinAantalDagen & " > " & dathergestuurdThe last line is a comparison between two dates.ERROR MESSAGE============================================Run-time error 3075Syntax error (missing operator) in query expression 'FaktuurGeprint ='J' AND dathergestuurd Is Not Null AND PerBankKas Is Null AND22-11-2005 >'========= end error message==============================The first date (22-11-2005) is visible in the error message but the seconddate is missing.As you can see there is nothing after the > but there should be dateinformation from a DB-cell named dathergestuurd.I tried a lot of things but i keep getting the same message.What is wrong with the syntax of the last line ???T.i.a.RegardsTino WintershovenThe Netherlands
I have some data that I'd like to order by a certain attribute.. but if there is a tie, then it should order by a secondary attribute.. and if there's still a tie.. then a 3rd attribute.
Currently the query looks like this:
SELECT * FROM Players WHERE ORDER BY Points
But I want it to look something like this (I know this doesn't work.. but it's just to give an idea):
SELECT * FROM Players WHERE ORDER BY (Points desc AND Games asc AND Goals desc)
Does anyone know the proper syntax for a multiple ORDER BY query like I described above?
