Hi all;
How do I enclose multiple filters in a variable, for instance how would I put the following filter into a variable and also is it actually possible or do I have to do something else before performing this type of operation:
tel1 like '072%' or tel1 like '+27 72%' or tel1 like '072-%'
or tel1 like '+2772%' or tel1 like '72%'
and len(tel1) > 7
or tel2 like '072%' or tel2 like '+27 72%' or tel2 like '072-%'
or tel2 like '+2772%' or tel2 like '72%'
and len(tel2) > 7
or tel3 like '072%' or tel3 like '+27 72%' or tel3 like '072-%'
or tel3 like '+2772%' or tel3 like '72%'
and len(tel3) > 7
or tel4 like '072%' or tel4 like '+27 72%' or tel4 like '072-%'
or tel4 like '+2772%' or tel4 like '72%'
and len(tel4) > 7
or tel5 like '072%' or tel5 like '+27 72%' or tel5 like '072-%'
or tel5 like '+2772%' or tel5 like '72%'
and len(tel5) > 7
or tel_other like '072%' or tel_other like '+27 72%' or tel_other like '072-%'
or tel_other like '+2772%' or tel_other like '72%'
and len(tel_other) > 7
the problem is that it's got a couple of apostrophes which when declaring variables pulls it out of that mode, I have looked on the internet but can't seem to find anythingthats no problem - just use an extra quote for each quote
set @.statement = N'master.dbo.xp_smtp_sendmail @.subject= N''Purchase Failure (Dont Reply To This Mail)'''|||Hi all;
thanks for the assistance how would I use the variable in a where section in other words it would follow as this
declare @.var varchar(10)
set @.var = 'multiple line like and or filters'
select * from table1
where @.var
thanks all, reaally appreciate the help
cheers|||Hi all;
thanks for the assistance how would I use the variable in a where section in other words it would follow as this
declare @.var varchar(10)
set @.var = 'multiple line like and or filters'
select * from table1
where @.var
thanks all, reaally appreciate the help
cheers
for this u need to use dynamic sql something like:
declare @.var varchar(10),@.Sql nvarchar(1000)
set @.var = 'userid>1000'
set @.sql='select * from users
where '+@.var
exec sp_executesql @.sql|||Hi all;
I am trying the following query:
declare @.072 varchar(5000),@.073 varchar(5000),@.Sql nvarchar(1000)
set @.072 = 'tel1 like ''072%'' or tel1 like ''+27 72%'' or tel1 like ''072-%''
or tel1 like ''+2772%'' or tel1 like ''72%''
and len(tel1) > 7
or tel2 like ''072%'' or tel2 like ''+27 72%'' or tel2 like ''072-%''
or tel2 like ''+2772%'' or tel2 like ''72%''
and len(tel2) > 7
or tel3 like ''072%'' or tel3 like ''+27 72%'' or tel3 like ''072-%''
or tel3 like ''+2772%'' or tel3 like ''72%''
and len(tel3) > 7
or tel4 like ''072%'' or tel4 like ''+27 72%'' or tel4 like ''072-%''
or tel4 like ''+2772%'' or tel4 like ''72%''
and len(tel4) > 7
or tel5 like ''072%'' or tel5 like ''+27 72%'' or tel5 like ''072-%''
or tel5 like ''+2772%'' or tel5 like ''72%''
and len(tel5) > 7
or tel_other like ''072%'' or tel_other like ''+27 72%'' or tel_other like ''072-%''
or tel_other like ''+2772%'' or tel_other like ''72%''
and len(tel_other) > 7'
set @.073 = 'or tel1 like ''073%'' or tel1 like ''+27 73%'' or tel1 like ''073-%''
or tel1 like ''+2773%'' or tel1 like ''73%''
and len(tel1) > 7
or tel2 like ''073%'' or tel2 like ''+27 73%'' or tel2 like ''073-%''
or tel2 like ''+2773%'' or tel2 like ''73%''
and len(tel2) > 7
or tel3 like ''073%'' or tel3 like ''+27 73%'' or tel3 like ''073-%''
or tel3 like ''+2773%'' or tel3 like ''73%''
and len(tel3) > 7
or tel4 like ''073%'' or tel4 like ''+27 73%'' or tel4 like ''073-%''
or tel4 like ''+2773%'' or tel4 like ''73%''
and len(tel4) > 7
or tel5 like ''073%'' or tel5 like ''+27 73%'' or tel5 like ''073-%''
or tel5 like ''+2773%'' or tel5 like ''73%''
and len(tel5) > 7
or tel_other like ''073%'' or tel_other like ''+27 73%'' or tel_other like ''073-%''
or tel_other like ''+2773%'' or tel_other like ''73%''
and len(tel_other) > 7'
set @.sql='select * from fl1_pcode
where '+@.072 +@.073
exec sp_executesql @.sql
but then i get the following error:
Server: Msg 170, Level 15, State 1, Line 23
Line 23: Incorrect syntax near 'tel'.
Firstly is this the right method of doing it and how does this error occur, I have tried only using the 072 variable and it pulls out a result fine, but the minute I add the second variable or insert the info from the second variable into the first (make the first variable longer) I get that error.
THanks 4 all the help so far ;)|||hi
check out you concatenation and unicode - put a space between two vars
and specify unicode..(N)
set @.sql= N'select * from fl1_pcode
where '+@.072 +' '+@.073
exec sp_executesql @.sql
if it still gives trouble syntax wise, instead of executing @.sql, select it,
and past it into a new query window - then youll be able to fine the syntax error location and make amends,
goodluck,
Des|||Hi Desmond;
Sorry about my ignorance but how would i pass it into a new query window ?
Thanks|||spelling was meant to be "paste" on my side -
in query analyzer, just open another window, and paste the results from
your select @.sql query, then see if it runs - amend if necessary
des|||Where should I look for the information that it compiles ?
Thanks
Showing posts with label instance. Show all posts
Showing posts with label instance. Show all posts
Wednesday, March 7, 2012
Sunday, February 26, 2012
Enabling full text search on existing sql server instance
Hi
I have an existing instance of sql express 2005 w/advanced services installed. When I installed I did not install full text search. Is there a way I can enable full text search on this existing instance?
Thanks
Try this:
sp_configure 'user instances enabled', '0'
sp_fulltext_database 'enable'
Then go ahead and try create your catalogs and indexes as normal.
enabled/disabled protocols for an msde instance
Hi folks,
Wondering if someone could help me out with an msde/windows sp2 problem.
Upon installing sp2, the network protocols for my msde instance become
disabled: Sm, Np, Tcp, Via. Is there a command line syntax I can use to
re-enable these network protocols? Or is there a windows interface to enable
them? basically my msde instance is now standalone, no other workstations
can talk to that sql instance. I can't install sp3 for msde because the
manufcaturer used merge modules (bleh!).
Any help would be greatly appreciated.
Thanks,
S. Purkiss
sheldon@.REMOVETHISthedoctorcompany.com
hi,
"S Purkiss" <shel@.shel.com> ha scritto nel messaggio
news:w7idnY9fmfsFnOLcRVn-rA@.rogers.com
> Hi folks,
> Wondering if someone could help me out with an msde/windows sp2
> problem.
> Upon installing sp2, the network protocols for my msde instance become
> disabled: Sm, Np, Tcp, Via. Is there a command line syntax I can
> use to re-enable these network protocols? Or is there a windows
> interface to enable them? basically my msde instance is now
> standalone, no other workstations can talk to that sql instance. I
> can't install sp3 for msde because the manufcaturer used merge
> modules (bleh!).
> Any help would be greatly appreciated.
please run Server Network Utility (svrnetcn.exe) and re-enable the desired
protocol...
with MSDE sp2 level you are exposed at Slammer/Saphire troubles, which have
ben solved with service pack 3 - 3/a..
lot of security problems has been addressed to with that sp, so please
"force" your ISV to ship sp3, as it's been out for quite 1 year, and it
should already had enougth time to test and ship it...
my $0.02
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
Wondering if someone could help me out with an msde/windows sp2 problem.
Upon installing sp2, the network protocols for my msde instance become
disabled: Sm, Np, Tcp, Via. Is there a command line syntax I can use to
re-enable these network protocols? Or is there a windows interface to enable
them? basically my msde instance is now standalone, no other workstations
can talk to that sql instance. I can't install sp3 for msde because the
manufcaturer used merge modules (bleh!).
Any help would be greatly appreciated.
Thanks,
S. Purkiss
sheldon@.REMOVETHISthedoctorcompany.com
hi,
"S Purkiss" <shel@.shel.com> ha scritto nel messaggio
news:w7idnY9fmfsFnOLcRVn-rA@.rogers.com
> Hi folks,
> Wondering if someone could help me out with an msde/windows sp2
> problem.
> Upon installing sp2, the network protocols for my msde instance become
> disabled: Sm, Np, Tcp, Via. Is there a command line syntax I can
> use to re-enable these network protocols? Or is there a windows
> interface to enable them? basically my msde instance is now
> standalone, no other workstations can talk to that sql instance. I
> can't install sp3 for msde because the manufcaturer used merge
> modules (bleh!).
> Any help would be greatly appreciated.
please run Server Network Utility (svrnetcn.exe) and re-enable the desired
protocol...
with MSDE sp2 level you are exposed at Slammer/Saphire troubles, which have
ben solved with service pack 3 - 3/a..
lot of security problems has been addressed to with that sp, so please
"force" your ISV to ship sp3, as it's been out for quite 1 year, and it
should already had enougth time to test and ship it...
my $0.02
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
Sunday, February 19, 2012
Enable and disable case sensitivity
Hi,
I use a SQL Server 2000.
Is there a chance to change the case sensitivity option for a running instance of sql server?
I know, during the install process, i can activate the case sensitivity. But can I change this option on an already installed server (to case sensitiv or back)?
ThanksYes you can,
this option is called collation. You can set it diferently as default for each database at the same SQL server. Also it can be set for each collumn in each table separately.
Marf.
Originally posted by jenswitte
Hi,
I use a SQL Server 2000.
Is there a chance to change the case sensitivity option for a running instance of sql server?
I know, during the install process, i can activate the case sensitivity. But can I change this option on an already installed server (to case sensitiv or back)?
Thanks|||I think for that you will need to rebuild master ... which is not highly recommended.|||Originally posted by MarF
Yes you can,
this option is called collation. You can set it diferently as default for each database at the same SQL server. Also it can be set for each collumn in each table separately.
Marf.
Thanks! It worked. I changed the collation of a database from case insensitive to case sensitive and back.|||ummmm...huh?
I use a SQL Server 2000.
Is there a chance to change the case sensitivity option for a running instance of sql server?
I know, during the install process, i can activate the case sensitivity. But can I change this option on an already installed server (to case sensitiv or back)?
ThanksYes you can,
this option is called collation. You can set it diferently as default for each database at the same SQL server. Also it can be set for each collumn in each table separately.
Marf.
Originally posted by jenswitte
Hi,
I use a SQL Server 2000.
Is there a chance to change the case sensitivity option for a running instance of sql server?
I know, during the install process, i can activate the case sensitivity. But can I change this option on an already installed server (to case sensitiv or back)?
Thanks|||I think for that you will need to rebuild master ... which is not highly recommended.|||Originally posted by MarF
Yes you can,
this option is called collation. You can set it diferently as default for each database at the same SQL server. Also it can be set for each collumn in each table separately.
Marf.
Thanks! It worked. I changed the collation of a database from case insensitive to case sensitive and back.|||ummmm...huh?
Subscribe to:
Posts (Atom)