Hi,
I suddenly ran into a serious problem. We installed a new SQL Express 2005
SP1 on a different server and now it seems to be case sensitive with the
variables in stored procedures:
----
ALTER PROCEDURE [dbo].[GetUserID] (@.UserName varchar(50))
AS
BEGIN
DECLARE @.ret bigint;
SET NOCOUNT ON;
SELECT @.ret = UserID FROM Users WHERE UserName Like @.Username;
RETURN ISNULL(@.ret,-1);
END
----
It fails on the @.Username which is declared as @.UserName
This did not happen on our other SQL Express server. It does not have SP1
either.
Please help!!
DavidHi David,
Most likely your problem is related to different instance collation on the
different servers. All identifiers for variables, GOTO labels and temp
tables are in the default collation of the instance. Probably your new
instance of SQL Server Express is installed with a case sensitive collation.
Here is how to change it if needed:
http://msdn2.microsoft.com/en-us/library/ms179254.aspx
HTH,
Plamen Ratchev
http://www.SQLStudio.com
Showing posts with label variables. Show all posts
Showing posts with label variables. Show all posts
Sunday, March 25, 2012
Case sensitive variables SQL Express
Hi,
I suddenly ran into a serious problem. We installed a new SQL Express 2005
SP1 on a different server and now it seems to be case sensitive with the
variables in stored procedures:
----
ALTER PROCEDURE [dbo].[GetUserID] (@.UserName varchar(50))
AS
BEGIN
DECLARE @.ret bigint;
SET NOCOUNT ON;
SELECT @.ret = UserID FROM Users WHERE UserName Like @.Username;
RETURN ISNULL(@.ret,-1);
END
----
It fails on the @.Username which is declared as @.UserName
This did not happen on our other SQL Express server. It does not have SP1
either.
Please help!!
DavidHi David,
Most likely your problem is related to different instance collation on the
different servers. All identifiers for variables, GOTO labels and temp
tables are in the default collation of the instance. Probably your new
instance of SQL Server Express is installed with a case sensitive collation.
Here is how to change it if needed:
http://msdn2.microsoft.com/en-us/library/ms179254.aspx
HTH,
Plamen Ratchev
http://www.SQLStudio.com
I suddenly ran into a serious problem. We installed a new SQL Express 2005
SP1 on a different server and now it seems to be case sensitive with the
variables in stored procedures:
----
ALTER PROCEDURE [dbo].[GetUserID] (@.UserName varchar(50))
AS
BEGIN
DECLARE @.ret bigint;
SET NOCOUNT ON;
SELECT @.ret = UserID FROM Users WHERE UserName Like @.Username;
RETURN ISNULL(@.ret,-1);
END
----
It fails on the @.Username which is declared as @.UserName
This did not happen on our other SQL Express server. It does not have SP1
either.
Please help!!
DavidHi David,
Most likely your problem is related to different instance collation on the
different servers. All identifiers for variables, GOTO labels and temp
tables are in the default collation of the instance. Probably your new
instance of SQL Server Express is installed with a case sensitive collation.
Here is how to change it if needed:
http://msdn2.microsoft.com/en-us/library/ms179254.aspx
HTH,
Plamen Ratchev
http://www.SQLStudio.com
Case sensitive problem
Hi,
I'm working on a SQL 2000 server that was not set up by me. I tried creating
stored procedure on it and found all variables are case-sensitive. How do I
change it to case insensitive?
For example, if I use QueryAnalyzer and enter in the following two lines:
DECLARE @.VAR1 int
SET @.var1 = 3
I would get the following error,
Must declare the variable '@.var1'.
Any help would be greatly appreciatedYou would change the database's collation to a case-insensitive collation.
You may want to find out why it's using a case-sensitive collation before
changing it though; there could be a method to the madness.
"Ming" wrote:
> Hi,
> I'm working on a SQL 2000 server that was not set up by me. I tried creati
ng
> stored procedure on it and found all variables are case-sensitive. How do
I
> change it to case insensitive?
> For example, if I use QueryAnalyzer and enter in the following two lines:
> DECLARE @.VAR1 int
> SET @.var1 = 3
> I would get the following error,
> Must declare the variable '@.var1'.
> Any help would be greatly appreciated|||I used EM and right clicked on that database and clicked the property page,
the database collation name is: SQL_Latin1_General_CP1_CI_AS, which I believ
e
is case insensitive. Any idea?
"KH" wrote:
> You would change the database's collation to a case-insensitive collation.
> You may want to find out why it's using a case-sensitive collation before
> changing it though; there could be a method to the madness.
>
> "Ming" wrote:
>|||I think that variable name sensitivity is determined by the server's
collation, not the local database's collation.
The server's collation is chosen in the installation, and is the collation
set for all system databases.
You can check the server's collation by running sp_helpsort, or select
serverproperty ('collation').
If the server's collation is case sensitive, that would explain your issues.
Changing the server's collation is not a simple task, even if you don't have
any applications that rely on or require case sensitivity.
You can try to enforce a policy where all variable names are, say, always
lower case.
BG, SQL Server MVP
www.SolidQualityLearning.com
"Ming" <Ming@.discussions.microsoft.com> wrote in message
news:7B241490-F50A-41C1-B269-AB4F18EA778F@.microsoft.com...
>I used EM and right clicked on that database and clicked the property page,
> the database collation name is: SQL_Latin1_General_CP1_CI_AS, which I
> believe
> is case insensitive. Any idea?
> "KH" wrote:
>
I'm working on a SQL 2000 server that was not set up by me. I tried creating
stored procedure on it and found all variables are case-sensitive. How do I
change it to case insensitive?
For example, if I use QueryAnalyzer and enter in the following two lines:
DECLARE @.VAR1 int
SET @.var1 = 3
I would get the following error,
Must declare the variable '@.var1'.
Any help would be greatly appreciatedYou would change the database's collation to a case-insensitive collation.
You may want to find out why it's using a case-sensitive collation before
changing it though; there could be a method to the madness.
"Ming" wrote:
> Hi,
> I'm working on a SQL 2000 server that was not set up by me. I tried creati
ng
> stored procedure on it and found all variables are case-sensitive. How do
I
> change it to case insensitive?
> For example, if I use QueryAnalyzer and enter in the following two lines:
> DECLARE @.VAR1 int
> SET @.var1 = 3
> I would get the following error,
> Must declare the variable '@.var1'.
> Any help would be greatly appreciated|||I used EM and right clicked on that database and clicked the property page,
the database collation name is: SQL_Latin1_General_CP1_CI_AS, which I believ
e
is case insensitive. Any idea?
"KH" wrote:
> You would change the database's collation to a case-insensitive collation.
> You may want to find out why it's using a case-sensitive collation before
> changing it though; there could be a method to the madness.
>
> "Ming" wrote:
>|||I think that variable name sensitivity is determined by the server's
collation, not the local database's collation.
The server's collation is chosen in the installation, and is the collation
set for all system databases.
You can check the server's collation by running sp_helpsort, or select
serverproperty ('collation').
If the server's collation is case sensitive, that would explain your issues.
Changing the server's collation is not a simple task, even if you don't have
any applications that rely on or require case sensitivity.
You can try to enforce a policy where all variable names are, say, always
lower case.
BG, SQL Server MVP
www.SolidQualityLearning.com
"Ming" <Ming@.discussions.microsoft.com> wrote in message
news:7B241490-F50A-41C1-B269-AB4F18EA778F@.microsoft.com...
>I used EM and right clicked on that database and clicked the property page,
> the database collation name is: SQL_Latin1_General_CP1_CI_AS, which I
> believe
> is case insensitive. Any idea?
> "KH" wrote:
>
Tuesday, February 14, 2012
Can't we use variables in OPENQUERY, FREETEXT("@searchstring")?
I'm writing a stored procedure for a keyword search in a Word or PDF
doc which i've done through Index Server and linked the results to SQL
Server.
Part of my stored proc is shown below in which for a FREETEXT keyword
search i'm using a variable "@.searchstring", which i have to, is not
working.
I know it works with hard text but
Is there any way i can use a Variable in OPENQUERIES or is this my DEAD
END?
Can anyone please guide me how to use a variable in FREETEXT
Thanks in Advance
DECLARE @.searchstring varchar(22)
SET @.searchstring = 'aspnet'
SELECT * FROM OPENQUERY(FileSystem,'SELECT Directory, FileName,
DocAuthor, Size, Create, Write, Path FROM SCOPE(''
"c:\inetpub\wwwroot\sap-resources\Uploads" '') WHERE
FREETEXT(''@.searchstring'')')Hi,
You need to resort to dynamic SQL, i.e....
SET @.sql = 'SELECT TOP 50 *
FROM (
SELECT DISTINCT
kba.idKBArticle,
[Rank],
Characterization
FROM ( SELECT DISTINCT TOP 50 [FileName],
[Rank],
Characterization
FROM OPENQUERY( lsIndexServer,
''SELECT FileName, Rank, Characterization
FROM TORVERSRVH3.SQLServerUG2..SCOPE() WHERE ' + CASE
WHEN @.OpType='C' THEN 'CONTAINS' ELSE 'FREETEXT' END +
'( '' +
@.SearchKeywords + '' )'' )
WHERE LEFT( Characterization, 12 ) <>
''vti_encoding''
) AS qry
INNER JOIN KBArticle kba ON kba.ArticleFileName =
qry.[FileName]'
REMEMBER!!!!!! ====>>>>>>>
To prevent injection make absolutely sure you replace any single quotes with
2 single quotes...
-- this one fails and is subject to injection...
declare @.searchtext varchar(100)
set @.searchtext = 'tony''s injection'
exec( 'print ''' + @.searchtext + '''' )
go
-- this one works because prevent injection...
declare @.searchtext varchar(100)
set @.searchtext = 'tony''s injection'
set @.searchtext = REPLACE( @.searchtext, '''', ''' )
exec( 'print ''' + @.searchtext + '''' )
go
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"savvy" <johngera@.gmail.com> wrote in message
news:1137755654.791956.239920@.z14g2000cwz.googlegroups.com...
> I'm writing a stored procedure for a keyword search in a Word or PDF
> doc which i've done through Index Server and linked the results to SQL
> Server.
> Part of my stored proc is shown below in which for a FREETEXT keyword
> search i'm using a variable "@.searchstring", which i have to, is not
> working.
> I know it works with hard text but
> Is there any way i can use a Variable in OPENQUERIES or is this my DEAD
> END?
> Can anyone please guide me how to use a variable in FREETEXT
> Thanks in Advance
> DECLARE @.searchstring varchar(22)
> SET @.searchstring = 'aspnet'
> SELECT * FROM OPENQUERY(FileSystem,'SELECT Directory, FileName,
> DocAuthor, Size, Create, Write, Path FROM SCOPE(''
> "c:\inetpub\wwwroot\sap-resources\Uploads" '') WHERE
> FREETEXT(''@.searchstring'')')
>|||Thanks for your help
i tried using above idea and some other examples.
The code shown below is working perfectly in the analyzer. I want to
create a view with the results
Is it possible ?
Thanks in Advance
DECLARE @.searchstring varchar(22)
SET @.searchstring = 'aspnet'
declare @.strSQL varchar(244)
select @.strSQL='select FileName,Path from scope(''''
"c:\inetpub\wwwroot\sap-resources\Uploads" '''') where contains ('
select @.strSQL=@.strSQL +char(39)+ char(39)+ @.searchstring +char(39)+
char(39)+')'
select @.strSQL='select * from openquery(FileSystem,'+ char(39)+
@.strSQL+ char(39)+ ')'
exec (@.strSQL)
Something like
CREATE VIEW FileSearchResults AS (@.strSQL)
which is not working|||Hi Savvy,
Sorry - you won't be able to create a view for that unless your search
string is hard-coded and never changes.
You could write a stored procedure that accepts the search string as a
parameter.
Tony.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"savvy" <johngera@.gmail.com> wrote in message
news:1137764139.455350.325970@.o13g2000cwo.googlegroups.com...
> Thanks for your help
> i tried using above idea and some other examples.
> The code shown below is working perfectly in the analyzer. I want to
> create a view with the results
> Is it possible ?
> Thanks in Advance
>
> DECLARE @.searchstring varchar(22)
> SET @.searchstring = 'aspnet'
> declare @.strSQL varchar(244)
> select @.strSQL='select FileName,Path from scope(''''
> "c:\inetpub\wwwroot\sap-resources\Uploads" '''') where contains ('
> select @.strSQL=@.strSQL +char(39)+ char(39)+ @.searchstring +char(39)+
> char(39)+')'
> select @.strSQL='select * from openquery(FileSystem,'+ char(39)+
> @.strSQL+ char(39)+ ')'
> exec (@.strSQL)
>
> Something like
> CREATE VIEW FileSearchResults AS (@.strSQL)
> which is not working
>|||Thank you very much for your help and time Tony Rogerson
This is my complete stored procedure which is perfectly working when i
hardcore the @.searchstring with the word which doesn't change.
I just want to use a variable working over there. Can u please help me
in this
Thanks in Advance
CREATE PROCEDURE SelectIndexServerCVpaths
(
@.searchstring varchar(100)
)
AS
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_NAME = 'FileSearchResults')
DROP VIEW FileSearchResults
EXEC ('CREATE VIEW FileSearchResults AS SELECT * FROM
OPENQUERY(FileSystem,''SELECT Directory, FileName,
DocAuthor, Size, Create, Write, Path FROM
SCOPE('''' "c:\inetpub\wwwroot\sap-resources\Uploads" '''') WHERE
FREETEXT(''''@.searchstring'''')'')')
SELECT * FROM CVdetails C, FileSearchResults F WHERE C.CV_Path =
F.PATH AND C.DefaultID=1
GO|||CREATE PROCEDURE SelectIndexServerCVpaths
(
@.searchstring varchar(100)
)
AS
SET @.searchstring = REPLACE( @.searchstring, '''', ''' )
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_NAME = 'FileSearchResults')
DROP VIEW FileSearchResults
EXEC ('CREATE VIEW FileSearchResults AS SELECT * FROM
OPENQUERY(FileSystem,''SELECT Directory, FileName,
DocAuthor, Size, Create, Write, Path FROM
SCOPE('''' "c:\inetpub\wwwroot\sap-resources\Uploads" '''') WHERE
FREETEXT('' + @.searchstring + '')'')')
SELECT * FROM CVdetails C, FileSearchResults F WHERE C.CV_Path =
F.PATH AND C.DefaultID=1
GO
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"savvy" <johngera@.gmail.com> wrote in message
news:1137766282.280852.185740@.g14g2000cwa.googlegroups.com...
> Thank you very much for your help and time Tony Rogerson
> This is my complete stored procedure which is perfectly working when i
> hardcore the @.searchstring with the word which doesn't change.
> I just want to use a variable working over there. Can u please help me
> in this
> Thanks in Advance
> CREATE PROCEDURE SelectIndexServerCVpaths
> (
> @.searchstring varchar(100)
> )
> AS
> IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS
> WHERE TABLE_NAME = 'FileSearchResults')
> DROP VIEW FileSearchResults
> EXEC ('CREATE VIEW FileSearchResults AS SELECT * FROM
> OPENQUERY(FileSystem,''SELECT Directory, FileName,
> DocAuthor, Size, Create, Write, Path FROM
> SCOPE('''' "c:\inetpub\wwwroot\sap-resources\Uploads" '''') WHERE
> FREETEXT(''''@.searchstring'''')'')')
> SELECT * FROM CVdetails C, FileSearchResults F WHERE C.CV_Path =
> F.PATH AND C.DefaultID=1
> GO
>|||Thanks for your Great help Tony
I have a strange problem its above code is working in the Query
Analyzer but not working if execute the stored procedure as shown below
i tried but i'm not able to figure out where the problem is
Thanks in Advance
Exec SelectIndexServerCVpaths
@.searchstring = 'aspnet'|||whats the error?
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"savvy" <johngera@.gmail.com> wrote in message
news:1137770531.573885.223780@.f14g2000cwb.googlegroups.com...
> Thanks for your Great help Tony
> I have a strange problem its above code is working in the Query
> Analyzer but not working if execute the stored procedure as shown below
> i tried but i'm not able to figure out where the problem is
> Thanks in Advance
> Exec SelectIndexServerCVpaths
> @.searchstring = 'aspnet'
>|||I'm sorry Tony
i didn't copy the code properly in my stored procedure this part
exactly FREETEXT('' + @.searchstring + '')'')')
when i copied again
its working perfectly Tony
You dont know how much your help is worth to me
I cant just express in words
I needed to complete project today which i did with your help
Thank you very very very much Tony Rogerson|||I'm really grateful to you Tony
Thanks onceagain
doc which i've done through Index Server and linked the results to SQL
Server.
Part of my stored proc is shown below in which for a FREETEXT keyword
search i'm using a variable "@.searchstring", which i have to, is not
working.
I know it works with hard text but
Is there any way i can use a Variable in OPENQUERIES or is this my DEAD
END?
Can anyone please guide me how to use a variable in FREETEXT
Thanks in Advance
DECLARE @.searchstring varchar(22)
SET @.searchstring = 'aspnet'
SELECT * FROM OPENQUERY(FileSystem,'SELECT Directory, FileName,
DocAuthor, Size, Create, Write, Path FROM SCOPE(''
"c:\inetpub\wwwroot\sap-resources\Uploads" '') WHERE
FREETEXT(''@.searchstring'')')Hi,
You need to resort to dynamic SQL, i.e....
SET @.sql = 'SELECT TOP 50 *
FROM (
SELECT DISTINCT
kba.idKBArticle,
[Rank],
Characterization
FROM ( SELECT DISTINCT TOP 50 [FileName],
[Rank],
Characterization
FROM OPENQUERY( lsIndexServer,
''SELECT FileName, Rank, Characterization
FROM TORVERSRVH3.SQLServerUG2..SCOPE() WHERE ' + CASE
WHEN @.OpType='C' THEN 'CONTAINS' ELSE 'FREETEXT' END +
'( '' +
@.SearchKeywords + '' )'' )
WHERE LEFT( Characterization, 12 ) <>
''vti_encoding''
) AS qry
INNER JOIN KBArticle kba ON kba.ArticleFileName =
qry.[FileName]'
REMEMBER!!!!!! ====>>>>>>>
To prevent injection make absolutely sure you replace any single quotes with
2 single quotes...
-- this one fails and is subject to injection...
declare @.searchtext varchar(100)
set @.searchtext = 'tony''s injection'
exec( 'print ''' + @.searchtext + '''' )
go
-- this one works because prevent injection...
declare @.searchtext varchar(100)
set @.searchtext = 'tony''s injection'
set @.searchtext = REPLACE( @.searchtext, '''', ''' )
exec( 'print ''' + @.searchtext + '''' )
go
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"savvy" <johngera@.gmail.com> wrote in message
news:1137755654.791956.239920@.z14g2000cwz.googlegroups.com...
> I'm writing a stored procedure for a keyword search in a Word or PDF
> doc which i've done through Index Server and linked the results to SQL
> Server.
> Part of my stored proc is shown below in which for a FREETEXT keyword
> search i'm using a variable "@.searchstring", which i have to, is not
> working.
> I know it works with hard text but
> Is there any way i can use a Variable in OPENQUERIES or is this my DEAD
> END?
> Can anyone please guide me how to use a variable in FREETEXT
> Thanks in Advance
> DECLARE @.searchstring varchar(22)
> SET @.searchstring = 'aspnet'
> SELECT * FROM OPENQUERY(FileSystem,'SELECT Directory, FileName,
> DocAuthor, Size, Create, Write, Path FROM SCOPE(''
> "c:\inetpub\wwwroot\sap-resources\Uploads" '') WHERE
> FREETEXT(''@.searchstring'')')
>|||Thanks for your help
i tried using above idea and some other examples.
The code shown below is working perfectly in the analyzer. I want to
create a view with the results
Is it possible ?
Thanks in Advance
DECLARE @.searchstring varchar(22)
SET @.searchstring = 'aspnet'
declare @.strSQL varchar(244)
select @.strSQL='select FileName,Path from scope(''''
"c:\inetpub\wwwroot\sap-resources\Uploads" '''') where contains ('
select @.strSQL=@.strSQL +char(39)+ char(39)+ @.searchstring +char(39)+
char(39)+')'
select @.strSQL='select * from openquery(FileSystem,'+ char(39)+
@.strSQL+ char(39)+ ')'
exec (@.strSQL)
Something like
CREATE VIEW FileSearchResults AS (@.strSQL)
which is not working|||Hi Savvy,
Sorry - you won't be able to create a view for that unless your search
string is hard-coded and never changes.
You could write a stored procedure that accepts the search string as a
parameter.
Tony.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"savvy" <johngera@.gmail.com> wrote in message
news:1137764139.455350.325970@.o13g2000cwo.googlegroups.com...
> Thanks for your help
> i tried using above idea and some other examples.
> The code shown below is working perfectly in the analyzer. I want to
> create a view with the results
> Is it possible ?
> Thanks in Advance
>
> DECLARE @.searchstring varchar(22)
> SET @.searchstring = 'aspnet'
> declare @.strSQL varchar(244)
> select @.strSQL='select FileName,Path from scope(''''
> "c:\inetpub\wwwroot\sap-resources\Uploads" '''') where contains ('
> select @.strSQL=@.strSQL +char(39)+ char(39)+ @.searchstring +char(39)+
> char(39)+')'
> select @.strSQL='select * from openquery(FileSystem,'+ char(39)+
> @.strSQL+ char(39)+ ')'
> exec (@.strSQL)
>
> Something like
> CREATE VIEW FileSearchResults AS (@.strSQL)
> which is not working
>|||Thank you very much for your help and time Tony Rogerson
This is my complete stored procedure which is perfectly working when i
hardcore the @.searchstring with the word which doesn't change.
I just want to use a variable working over there. Can u please help me
in this
Thanks in Advance
CREATE PROCEDURE SelectIndexServerCVpaths
(
@.searchstring varchar(100)
)
AS
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_NAME = 'FileSearchResults')
DROP VIEW FileSearchResults
EXEC ('CREATE VIEW FileSearchResults AS SELECT * FROM
OPENQUERY(FileSystem,''SELECT Directory, FileName,
DocAuthor, Size, Create, Write, Path FROM
SCOPE('''' "c:\inetpub\wwwroot\sap-resources\Uploads" '''') WHERE
FREETEXT(''''@.searchstring'''')'')')
SELECT * FROM CVdetails C, FileSearchResults F WHERE C.CV_Path =
F.PATH AND C.DefaultID=1
GO|||CREATE PROCEDURE SelectIndexServerCVpaths
(
@.searchstring varchar(100)
)
AS
SET @.searchstring = REPLACE( @.searchstring, '''', ''' )
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_NAME = 'FileSearchResults')
DROP VIEW FileSearchResults
EXEC ('CREATE VIEW FileSearchResults AS SELECT * FROM
OPENQUERY(FileSystem,''SELECT Directory, FileName,
DocAuthor, Size, Create, Write, Path FROM
SCOPE('''' "c:\inetpub\wwwroot\sap-resources\Uploads" '''') WHERE
FREETEXT('' + @.searchstring + '')'')')
SELECT * FROM CVdetails C, FileSearchResults F WHERE C.CV_Path =
F.PATH AND C.DefaultID=1
GO
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"savvy" <johngera@.gmail.com> wrote in message
news:1137766282.280852.185740@.g14g2000cwa.googlegroups.com...
> Thank you very much for your help and time Tony Rogerson
> This is my complete stored procedure which is perfectly working when i
> hardcore the @.searchstring with the word which doesn't change.
> I just want to use a variable working over there. Can u please help me
> in this
> Thanks in Advance
> CREATE PROCEDURE SelectIndexServerCVpaths
> (
> @.searchstring varchar(100)
> )
> AS
> IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS
> WHERE TABLE_NAME = 'FileSearchResults')
> DROP VIEW FileSearchResults
> EXEC ('CREATE VIEW FileSearchResults AS SELECT * FROM
> OPENQUERY(FileSystem,''SELECT Directory, FileName,
> DocAuthor, Size, Create, Write, Path FROM
> SCOPE('''' "c:\inetpub\wwwroot\sap-resources\Uploads" '''') WHERE
> FREETEXT(''''@.searchstring'''')'')')
> SELECT * FROM CVdetails C, FileSearchResults F WHERE C.CV_Path =
> F.PATH AND C.DefaultID=1
> GO
>|||Thanks for your Great help Tony
I have a strange problem its above code is working in the Query
Analyzer but not working if execute the stored procedure as shown below
i tried but i'm not able to figure out where the problem is
Thanks in Advance
Exec SelectIndexServerCVpaths
@.searchstring = 'aspnet'|||whats the error?
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"savvy" <johngera@.gmail.com> wrote in message
news:1137770531.573885.223780@.f14g2000cwb.googlegroups.com...
> Thanks for your Great help Tony
> I have a strange problem its above code is working in the Query
> Analyzer but not working if execute the stored procedure as shown below
> i tried but i'm not able to figure out where the problem is
> Thanks in Advance
> Exec SelectIndexServerCVpaths
> @.searchstring = 'aspnet'
>|||I'm sorry Tony
i didn't copy the code properly in my stored procedure this part
exactly FREETEXT('' + @.searchstring + '')'')')
when i copied again
its working perfectly Tony
You dont know how much your help is worth to me
I cant just express in words
I needed to complete project today which i did with your help
Thank you very very very much Tony Rogerson|||I'm really grateful to you Tony
Thanks onceagain
Can't use variables in SQL-Task
Hi,
I have a problem in my SSIS-package: If I try to execute a SQL-statement which uses a variable, it say "syntax-error". Here is how I tried:
1. a) Declared a variable. Name: tableName; Bereich (Sorry, I use the german version, no clue whats that on english versions): Package2; Type: STRING; Value: staticDB.StaticData_provider.dbo.C0123
b) Assigned this variable on SQL-Task->"Parameterzuordnung" as VARCHAR, parametername= NewParameterName
c) Used this on my SQL-Statement, SQLSourceType is directinput. Statement: "DELETE
FROM @.NewParameterName"
d) Running this results in following error: "Der Parametername wird nicht erkannt" Translation: "Parametername was not recognized."
2. a) see 1.a)
b) see 1.b)
c) Used this on my SQL-Statement, SQLSourceType is directinput. Statement: "DELETE
FROM ?"
d) Running this results in following error: "Syntaxfehler, Berechtigungsversto? oder anderer allgemeiner Fehler" Translation: "Syntaxerror, permision violation or other common error."
Any ideas?
Regards,
Jan
You cannot use a parameter to substitue in the name of a table. They are typically used as arguments in WHERE clause predicates.
If you want to dynamically set the table name then use an expression. This explains how: http://blogs.conchango.com/jamiethomson/archive/2005/12/09/2480.aspx
-Jamie
|||
Thanks a lot for that link, now my package is working fine :-).
Best Regards,
Jan Wagner
Subscribe to:
Posts (Atom)