Incorrect syntax near '='

Hi,
I'm using multiple select statements in MS-Access with logical
expressions like (ID=10)*50 instead of an if statement. In this manner
i'm able to get the same results as with if statements (if ID=10 then
result=50) only faster (in MS-Access that is).
when trying to get the same sql statement running in SQL-server I
receive an Incorrect syntax near '=' error message.
Are there any solutions? I will try to avoid updating all queries
because MS-Access will also be used as backend next to a version for
SQL-server backend.
Maybe anyone can help me.You can use CASE statement
SELECT result =
CASE WHEN ID = 10 THEN 50
ELSE something END
"Niels Verkaart" wrote:

> Hi,
> I'm using multiple select statements in MS-Access with logical
> expressions like (ID=10)*50 instead of an if statement. In this manner
> i'm able to get the same results as with if statements (if ID=10 then
> result=50) only faster (in MS-Access that is).
> when trying to get the same sql statement running in SQL-server I
> receive an Incorrect syntax near '=' error message.
> Are there any solutions? I will try to avoid updating all queries
> because MS-Access will also be used as backend next to a version for
> SQL-server backend.
> Maybe anyone can help me.
>|||Thank you Jack,
now I only have to write a clever function to convert dynamic queries
like:
INSERT INTO _CalcAfzet_2394 ( ID, K001, K002, K003, K004 ) SELECT
VerkoopsoortID,Sum(-(Year([Datum])=2006)*[Aantal]),Sum(-(Year([Datum])=2
007)*[Aantal]),Sum(-(Year([Datum])=2008)*[Aantal]),Sum(-(Year([Datum])=2
009)*[Aantal]) FROM CalcAfzetPeriode WHERE CalcID=1528 AND
VerkoopsoortID NOT IN (0) GROUP BY VerkoopsoortID
That's a nice little problem for me. but i'll get there.
> You can use CASE statement
> SELECT result =
> CASE WHEN ID = 10 THEN 50
> ELSE something END
> "Niels Verkaart" wrote:
>|||Hi Niels,
This is a typical crosstab (FAQ). The solution is basically the same as
what Jack has posted:
INSERT INTO _CalcAfzet_2394 ( ID, K001, K002, K003, K004 )
SELECT VerkoopsoortID
, SUM(CASE Year(Datum) WHEN 2006 THEN Aantal END)
, SUM(CASE Year(Datum) WHEN 2007 THEN Aantal END)
, SUM(CASE Year(Datum) WHEN 2008 THEN Aantal END)
, SUM(CASE Year(Datum) WHEN 2009 THEN Aantal END)
FROM CalcAfzetPeriode
WHERE CalcID=1528
AND VerkoopsoortID NOT IN (0)
GROUP BY VerkoopsoortID
HTH,
Gert-Jan
Niels Verkaart wrote:
> Thank you Jack,
> now I only have to write a clever function to convert dynamic queries
> like:
> INSERT INTO _CalcAfzet_2394 ( ID, K001, K002, K003, K004 ) SELECT
> VerkoopsoortID,Sum(-(Year([Datum])=2006)*[Aantal]),Sum(-(Year([Datum])=2
> 007)*[Aantal]),Sum(-(Year([Datum])=2008)*[Aantal]),Sum(-(Year([Datum])=2
> 009)*[Aantal]) FROM CalcAfzetPeriode WHERE CalcID=1528 AND
> VerkoopsoortID NOT IN (0) GROUP BY VerkoopsoortID
> That's a nice little problem for me. but i'll get there.
>|||Great Gert-Jan,
thank you both Gert-Jan and Jack!
Gert-Jan Strik wrote:
> Hi Niels,
> This is a typical crosstab (FAQ). The solution is basically the same
> as what Jack has posted:
> INSERT INTO _CalcAfzet_2394 ( ID, K001, K002, K003, K004 )
> SELECT VerkoopsoortID
> , SUM(CASE Year(Datum) WHEN 2006 THEN Aantal END)
> , SUM(CASE Year(Datum) WHEN 2007 THEN Aantal END)
> , SUM(CASE Year(Datum) WHEN 2008 THEN Aantal END)
> , SUM(CASE Year(Datum) WHEN 2009 THEN Aantal END)
> FROM CalcAfzetPeriode
> WHERE CalcID=1528
> AND VerkoopsoortID NOT IN (0)
> GROUP BY VerkoopsoortID
> HTH,
> Gert-Jan
>
> Niels Verkaart wrote:

Incorrect results from FTS on multiple columns

I'm writing a FTS query which needs to search on two different columns.

E.g. Table contains "Location" and "LocationDescription" columns.
Both columns are FT indexed.
The query also uses AND/OR operators to filter out the results.

I found the following article which gives the solution to the same problem.
Link: http://support.microsoft.com/default.aspx?scid=kb;en-us;286787
Is this problem associated with SQL Server 2005 also?

Making a third column which hold data from first two column is the
only solution or is there any other way to acheive better results?

Shailesh Patel...

The KB article only applies to SQL Server 7.0 as indicated. If you are having problems with your implementation please post a repro script with some sample data and the expected results.

|||My database contains the following structure:
Table name: Resumes
Columns:
ResumeID bigint identity(1,1),
Title varchar(100),
ResumeText varchar(max),
Skills varchar(250)

The ResumeID is set as primary key and columns Title, ResumeText and Skills
are FT indexed.

A record contains "Software Developer .NET" in Title,
resume text which also contains word "india" in ResumeText and
"C#, ASP.NET and SQL Server" in Skills columns.

If I pass a query as ("software" AND "developer" AND "india" AND "asp.net") in CONTAINSTABLE or CONTAINS which looks for a resume which statisfies this condition.

The queries which I wrote search on all the column which are FT indexed as a
result of which even if I pass the above query it does not returns any result.

It seems that the FT query is working on each columns individually.
That's why if I pass ("software" AND "developer") in a query it locates the record.

Should I create another column and dump the data of
Title, ResumeText and Skills in it and perform a FTS on this new column?
or
Is there any other feasible method to solve this problem?

inconsistent Timeout Expired with extended SP execute

All,
I've successfully executed my extended stored proc multiple times via
the following. If I directly execute(e.g., right-click 'Execute'
within my DB editor) this stored procedure, it runs everytime and
finishes in 1-2 seconds. If I try to execute the SP via my trigger, it
returns a 'Timeout Expired' message. But - not every time. I have got
the trigger to fire the SP a few (seemingly random) times without
timing out. Any help would be fantastic...
Pre-Thanks,
sara
my SP:
ALTER procedure dbo.WriteDNFiles
as
declare @.return varchar
EXEC @.return = master.dbo.XYRunProc 'C:\WRITE_DN_FILES.exe'
my trigger:
ALTER TRIGGER TriggerEmp
ON dbo.Employees
FOR INSERT, UPDATE
AS
exec WriteDNFilesHi sara
Running a program in a trigger is not a good idea as it will prolong the
transaction and there increase contention which could possibly be what you
are experiencing. You may want to make sure that you are not having any file
access problems if two process are wanting to call the program simultanously
.
Another general point is that you should change @.@.ROWCOUNT in the trigger to
see if anything has changed, if not don't call your procedure.
John
"skeddy@.gmail.com" wrote:

> All,
> I've successfully executed my extended stored proc multiple times via
> the following. If I directly execute(e.g., right-click 'Execute'
> within my DB editor) this stored procedure, it runs everytime and
> finishes in 1-2 seconds. If I try to execute the SP via my trigger, it
> returns a 'Timeout Expired' message. But - not every time. I have got
> the trigger to fire the SP a few (seemingly random) times without
> timing out. Any help would be fantastic...
> Pre-Thanks,
> sara
> my SP:
> ALTER procedure dbo.WriteDNFiles
> as
> declare @.return varchar
> EXEC @.return = master.dbo.XYRunProc 'C:\WRITE_DN_FILES.exe'
> my trigger:
> ALTER TRIGGER TriggerEmp
> ON dbo.Employees
> FOR INSERT, UPDATE
> AS
> exec WriteDNFiles
>

inconsistent Timeout Expired with extended SP execute

All,
I've successfully executed my extended stored proc multiple times via
the following. If I directly execute(e.g., right-click 'Execute'
within my DB editor) this stored procedure, it runs everytime and
finishes in 1-2 seconds. If I try to execute the SP via my trigger, it
returns a 'Timeout Expired' message. But - not every time. I have got
the trigger to fire the SP a few (seemingly random) times without
timing out. Any help would be fantastic...
Pre-Thanks,
sara
my SP:
ALTER procedure dbo.WriteDNFiles
as
declare @.return varchar
EXEC @.return = master.dbo.XYRunProc 'C:\WRITE_DN_FILES.exe'
my trigger:
ALTER TRIGGER TriggerEmp
ON dbo.Employees
FOR INSERT, UPDATE
AS
exec WriteDNFilesHi sara
Running a program in a trigger is not a good idea as it will prolong the
transaction and there increase contention which could possibly be what you
are experiencing. You may want to make sure that you are not having any file
access problems if two process are wanting to call the program simultanously.
Another general point is that you should change @.@.ROWCOUNT in the trigger to
see if anything has changed, if not don't call your procedure.
John
"skeddy@.gmail.com" wrote:
> All,
> I've successfully executed my extended stored proc multiple times via
> the following. If I directly execute(e.g., right-click 'Execute'
> within my DB editor) this stored procedure, it runs everytime and
> finishes in 1-2 seconds. If I try to execute the SP via my trigger, it
> returns a 'Timeout Expired' message. But - not every time. I have got
> the trigger to fire the SP a few (seemingly random) times without
> timing out. Any help would be fantastic...
> Pre-Thanks,
> sara
> my SP:
> ALTER procedure dbo.WriteDNFiles
> as
> declare @.return varchar
> EXEC @.return = master.dbo.XYRunProc 'C:\WRITE_DN_FILES.exe'
> my trigger:
> ALTER TRIGGER TriggerEmp
> ON dbo.Employees
> FOR INSERT, UPDATE
> AS
> exec WriteDNFiles
>

Including multiple values in where clause

Guys

I'm building a reporting application where users can create bespoke reports, one area I'm having an issue with is having multiple values for a particular column. It is possible to say in english "select all contacts from 'houston' and 'dallas' ", the expected result would be all contacts who have offices in both houston and dallas. In SQL server this is a little harder to make work as a location can't equal both houston and dallas (just on or the other and subsquently returns nothing). An "or" will return either dallas or houston, one or the other, or both.

I'm starting to think this may not be possible, but just can't believe that that's the case.

Hope this makes some sort of sense, as I would like to put this issue to bed once and for all.

Duncanselect <columns> from <table> where <city> in ('houston','dallas')|||

Something like this could work. (Of course, since you didn't provide the table DDL or sample data, it is impossible to 'get it right' for you.)

Code Snippet

SELECT *
FROM Contacts
WHERE ContactID EXIST (SELECT ContactID
FROM Contacts

WHERE Location IN ( 'Dallas', 'Houston' )

GROUP BY ContactID

HAVING count( ContactID ) > 1

)

|||

Duncan,

How about

select

<contact columns>

from <table>

where <city> = 'houston'

intersect

select

<contact columns>

from <table>

where <city> = 'dallas'

That way you only get the contacts with offices in both cities.

Dan

P.S. I think INTERSECT is only available in SQL Server 2005.

|||

( Arnie: give you code another look. )

(Also, won't INTERSECT result in zero rows returned in all cases? Do you mean Union?)

|||

INTERSECT should work if no <city> information is in the selected columns.

He wants entries that match BOTH the Dallas and Houston criteria.

Dan

|||I suppose your query is amorphous enough that it isn't clear whether or not you are including the LOCATION column, but If both of the queries that compose the INTERSECT include the LOCATION column -- 'Dallas' in one case and 'Houston' in the other -- won't the INTERSECT will exclude them? In fact won't the INTERSECT operator exclude the rows if there is any variation between them?|||

Thanks Kent -it was early...

This will work for you in both SQL 2000 and SQL 2005.

Code Snippet

DECLARE @.MyContacts table
( ContactID int,
City varchar(20)
)

SET NOCOUNT ON

INSERT INTO @.MyContacts VALUES ( 1, 'Dallas' )
INSERT INTO @.MyContacts VALUES ( 2, 'Houston' )
INSERT INTO @.MyContacts VALUES ( 2, 'Dallas' )
INSERT INTO @.MyContacts VALUES ( 1, 'Austin' )
INSERT INTO @.MyContacts VALUES ( 3, 'Houston' )
INSERT INTO @.MyContacts VALUES ( 3, 'Beaumont' )

SELECT *
FROM @.MyContacts
WHERE ContactID IN ( SELECT ContactID
FROM @.MyContacts
WHERE City IN ( 'Dallas', 'Houston' )
GROUP BY ContactID
HAVING count( DISTINCT City) > 1
)

ContactID City
-- --
2 Houston
2 Dallas

|||

Dan:

I just got done kicking myself for giving you a bit of "business" -- and you sure don't deserve it. I sometimes have problems with "pet" dislikes. I didn't realize that EXCLUDE and INTERSECT might be among them but I am certainly behaving that way. Yes, the truth is to some extent I try to avoid EXCLUDE and I feel that sometimes INTERSECT can get you in trouble too. I am sorry that I gave you a bit of stuff over this. Please forgive me.

Kent

|||

DanR1 wrote:

INTERSECT should work if no <city> information is in the selected columns.

He wants entries that match BOTH the Dallas and Houston criteria.

Dan

However, if he does indeed want the <city> information included in the resultset, INTERSECT will not work for him without wrapping in an outer query.

|||

Kent,

I also admit that my post to which you responded did not make it clear that "city-related" information must be excluded in order for the query to work. I presumed that the initial post in this thread was from someone who would realize that. (Sometimes I like to say only enough to help someone along, until I realize that I should post more details.)

I like to think of INTERSECT in terms of Venn diagrams. I think it is an incredibly useful set-theoretic capability to have in SQL Server.

I also see EXCEPT as a very useful function, e.g., to use when comparing a new dataset (table) received to see if it differs from the existing dataset. (I used it a lot with ORACLE, in the form of MINUS.)

You are certainly forgiven, but I don't think you did anything that required you to ask for forgiveness.

Cheers.

Dan

|||Thanks, Dan. Cheers. :-)|||Arnie, your queries has a typical mistake - what about a contact who has two offices in Dallas and none in Houston?

However, it can be easily fixed - replace HAVING() line in your query with this one:

having count(distinct city) > 1|||

Ennor,

Right after I hit the [Post] button, I actually thought about that. But from the OP, I assumed that wouldn't be an issue -and if if was, it would come up and we would 'fix' it.

However, your suggested alteration does indeed clear up the situation and make it a more robust solution.

Thanks for mentioning it.

|||

Guys

Thanks for all the replies, using the "where in" works a treat, wasn't sure that would work for this but, seems to.

Including multiple values in where clause

Guys

I'm building a reporting application where users can create bespoke reports, one area I'm having an issue with is having multiple values for a particular column. It is possible to say in english "select all contacts from 'houston' and 'dallas' ", the expected result would be all contacts who have offices in both houston and dallas. In SQL server this is a little harder to make work as a location can't equal both houston and dallas (just on or the other and subsquently returns nothing). An "or" will return either dallas or houston, one or the other, or both.

I'm starting to think this may not be possible, but just can't believe that that's the case.

Hope this makes some sort of sense, as I would like to put this issue to bed once and for all.

Duncanselect <columns> from <table> where <city> in ('houston','dallas')|||

Something like this could work. (Of course, since you didn't provide the table DDL or sample data, it is impossible to 'get it right' for you.)

Code Snippet

SELECT *
FROM Contacts
WHERE ContactID EXIST (SELECT ContactID
FROM Contacts

WHERE Location IN ( 'Dallas', 'Houston' )

GROUP BY ContactID

HAVING count( ContactID ) > 1

)

|||

Duncan,

How about

select

<contact columns>

from <table>

where <city> = 'houston'

intersect

select

<contact columns>

from <table>

where <city> = 'dallas'

That way you only get the contacts with offices in both cities.

Dan

P.S. I think INTERSECT is only available in SQL Server 2005.

|||

( Arnie: give you code another look. )

(Also, won't INTERSECT result in zero rows returned in all cases? Do you mean Union?)

|||

INTERSECT should work if no <city> information is in the selected columns.

He wants entries that match BOTH the Dallas and Houston criteria.

Dan

|||I suppose your query is amorphous enough that it isn't clear whether or not you are including the LOCATION column, but If both of the queries that compose the INTERSECT include the LOCATION column -- 'Dallas' in one case and 'Houston' in the other -- won't the INTERSECT will exclude them? In fact won't the INTERSECT operator exclude the rows if there is any variation between them?|||

Thanks Kent -it was early...

This will work for you in both SQL 2000 and SQL 2005.

Code Snippet

DECLARE @.MyContacts table
( ContactID int,
City varchar(20)
)

SET NOCOUNT ON

INSERT INTO @.MyContacts VALUES ( 1, 'Dallas' )
INSERT INTO @.MyContacts VALUES ( 2, 'Houston' )
INSERT INTO @.MyContacts VALUES ( 2, 'Dallas' )
INSERT INTO @.MyContacts VALUES ( 1, 'Austin' )
INSERT INTO @.MyContacts VALUES ( 3, 'Houston' )
INSERT INTO @.MyContacts VALUES ( 3, 'Beaumont' )

SELECT *
FROM @.MyContacts
WHERE ContactID IN ( SELECT ContactID
FROM @.MyContacts
WHERE City IN ( 'Dallas', 'Houston' )
GROUP BY ContactID
HAVING count( DISTINCT City) > 1
)

ContactID City
-- --
2 Houston
2 Dallas

|||

Dan:

I just got done kicking myself for giving you a bit of "business" -- and you sure don't deserve it. I sometimes have problems with "pet" dislikes. I didn't realize that EXCLUDE and INTERSECT might be among them but I am certainly behaving that way. Yes, the truth is to some extent I try to avoid EXCLUDE and I feel that sometimes INTERSECT can get you in trouble too. I am sorry that I gave you a bit of stuff over this. Please forgive me.

Kent

|||

DanR1 wrote:

INTERSECT should work if no <city> information is in the selected columns.

He wants entries that match BOTH the Dallas and Houston criteria.

Dan

However, if he does indeed want the <city> information included in the resultset, INTERSECT will not work for him without wrapping in an outer query.

|||

Kent,

I also admit that my post to which you responded did not make it clear that "city-related" information must be excluded in order for the query to work. I presumed that the initial post in this thread was from someone who would realize that. (Sometimes I like to say only enough to help someone along, until I realize that I should post more details.)

I like to think of INTERSECT in terms of Venn diagrams. I think it is an incredibly useful set-theoretic capability to have in SQL Server.

I also see EXCEPT as a very useful function, e.g., to use when comparing a new dataset (table) received to see if it differs from the existing dataset. (I used it a lot with ORACLE, in the form of MINUS.)

You are certainly forgiven, but I don't think you did anything that required you to ask for forgiveness.

Cheers.

Dan

|||Thanks, Dan. Cheers. :-)|||Arnie, your queries has a typical mistake - what about a contact who has two offices in Dallas and none in Houston?

However, it can be easily fixed - replace HAVING() line in your query with this one:

having count(distinct city) > 1|||

Ennor,

Right after I hit the [Post] button, I actually thought about that. But from the OP, I assumed that wouldn't be an issue -and if if was, it would come up and we would 'fix' it.

However, your suggested alteration does indeed clear up the situation and make it a more robust solution.

Thanks for mentioning it.

|||

Guys

Thanks for all the replies, using the "where in" works a treat, wasn't sure that would work for this but, seems to.

Included columns in index.. performance implications ?

How detrimental is it to create multiple indexes that have included columns
as opposed to creating composite indexes that include that all those columns
?
I want to add the index with included columns but also concerned about
slowness in performance on writes. Either way I want to create the index..
the only difference will be whether I decide to have those included columns
or not..
ThanksIt is not really a performance decision. It depends if those columns will be
used, for example, on the WHERE clause or just on the SELECT clause. If thes
e
columns will be listed only in the SELECT clause then use included columns.
If you are going to use those columns as a search criteria then included
columns would not help.
Regarding performance, include columns are better because they are only
stored at the leaf level of the index.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Hassan" wrote:

> How detrimental is it to create multiple indexes that have included column
s
> as opposed to creating composite indexes that include that all those colum
ns
> ?
> I want to add the index with included columns but also concerned about
> slowness in performance on writes. Either way I want to create the index.
.
> the only difference will be whether I decide to have those included column
s
> or not..
> Thanks
>

Included columns in index.. performance implications ?

How detrimental is it to create multiple indexes that have included columns
as opposed to creating composite indexes that include that all those columns
?
I want to add the index with included columns but also concerned about
slowness in performance on writes. Either way I want to create the index..
the only difference will be whether I decide to have those included columns
or not..
Thanks
It is not really a performance decision. It depends if those columns will be
used, for example, on the WHERE clause or just on the SELECT clause. If these
columns will be listed only in the SELECT clause then use included columns.
If you are going to use those columns as a search criteria then included
columns would not help.
Regarding performance, include columns are better because they are only
stored at the leaf level of the index.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Hassan" wrote:

> How detrimental is it to create multiple indexes that have included columns
> as opposed to creating composite indexes that include that all those columns
> ?
> I want to add the index with included columns but also concerned about
> slowness in performance on writes. Either way I want to create the index..
> the only difference will be whether I decide to have those included columns
> or not..
> Thanks
>

Included columns in index.. performance implications ?

How detrimental is it to create multiple indexes that have included columns
as opposed to creating composite indexes that include that all those columns
?
I want to add the index with included columns but also concerned about
slowness in performance on writes. Either way I want to create the index..
the only difference will be whether I decide to have those included columns
or not..
ThanksIt is not really a performance decision. It depends if those columns will be
used, for example, on the WHERE clause or just on the SELECT clause. If these
columns will be listed only in the SELECT clause then use included columns.
If you are going to use those columns as a search criteria then included
columns would not help.
Regarding performance, include columns are better because they are only
stored at the leaf level of the index.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Hassan" wrote:
> How detrimental is it to create multiple indexes that have included columns
> as opposed to creating composite indexes that include that all those columns
> ?
> I want to add the index with included columns but also concerned about
> slowness in performance on writes. Either way I want to create the index..
> the only difference will be whether I decide to have those included columns
> or not..
> Thanks
>

In SSIS, what is the best way to take a column with comma separated strings and separate them to

Hi There,

Can anybody suggest me what is the best way to take a column with comma separated stings and output them into multiple columns with strings?

for example if I have a column with "watertown, newton" as a string, I need to separate them to two columns with watertown and newton values?

Is Derived column transformation the best way to do it?

Thanks.

Sam.

From what I've seen your 2 choices are using a derived column or writing a script. For your case the derived column seems best - just use a combination of Substring and findstring.|||Yeah that's what I thought. Thanks for the quick response.|||

Chris,

While I am doing this, I need to look up for an id based on the city name and add as a new column. So in one row of data if I have four cities, I need to have 4 columns of their corresponding ids. I am using the look up transformation to get the id, but do I need to have 4 look up transformations to do that or can it be done by 1 look up transformation?

Thanks.

Sam.

|||Unpivot ur data, do the lookup and get the id using a single loopkup transformation, pivot it back.

In SSIS, what is the best way to take a column with comma separated strings and separate the

Hi There,

Can anybody suggest me what is the best way to take a column with comma separated stings and output them into multiple columns with strings?

for example if I have a column with "watertown, newton" as a string, I need to separate them to two columns with watertown and newton values?

Is Derived column transformation the best way to do it?

Thanks.

Sam.

From what I've seen your 2 choices are using a derived column or writing a script. For your case the derived column seems best - just use a combination of Substring and findstring.|||Yeah that's what I thought. Thanks for the quick response.|||

Chris,

While I am doing this, I need to look up for an id based on the city name and add as a new column. So in one row of data if I have four cities, I need to have 4 columns of their corresponding ids. I am using the look up transformation to get the id, but do I need to have 4 look up transformations to do that or can it be done by 1 look up transformation?

Thanks.

Sam.

|||Unpivot ur data, do the lookup and get the id using a single loopkup transformation, pivot it back.