Consolidation Query: dynamic

Showing posts with label dynamic. Show all posts

Thursday, March 22, 2012

conversion char/nchar

Hi,
We are using sql server 2000..We intend to change the
char/varchar columns to nchar/nvarchat.Our application
contains lot's of dynamic tables.Hence there are no
standard no of tables and indexes in all sites..Can any
one help me out in writing scripts which queries the
dictionary objects and gives scripts which would work fine
in all sites...?
Thanks in advance
SridharThis script should get you started:
use tempdb
GO
CREATE TABLE foo (firstcol varchar(10), secondcol nvarchar(10), thircol =char(10))
go
SELECT 'ALTER TABLE ' + table_name +
' ALTER COLUMN ' + COLUMN_NAME +
CASE WHEN DATA_TYPE =3D 'char' THEN ' nchar ' ELSE ' nvarchar ' =END + ' (' + LTRIM(STR(CHARACTER_MAXIMUM_LENGTH)) + ')' +
char(13) + char(10) + 'GO'
FROM information_schema.columns WHERE DATA_TYPE IN ('varchar', 'char')
--now execute the statements that are returned from the select statement
go
DROP TABLE foo
-- Keith
<anonymous@.discussions.microsoft.com> wrote in message =news:869f01c4328f$e1ddd420$a401280a@.phx.gbl...
> Hi,
> > We are using sql server 2000..We intend to change the > char/varchar columns to nchar/nvarchat.Our application > contains lot's of dynamic tables.Hence there are no > standard no of tables and indexes in all sites..Can any > one help me out in writing scripts which queries the > dictionary objects and gives scripts which would work fine > in all sites...?
> > > Thanks in advance
> > Sridhar
>|||Hi,
Thanks..do some where sql server stores the index and
constraints structure some where in dictionary ...other
wise how do i recreate the indexes and constraints after
converting to nchar
Sridhar
>--Original Message--
>This script should get you started:
>use tempdb
>GO
>CREATE TABLE foo (firstcol varchar(10), secondcol nvarchar
(10), thircol char(10))
>go
>
>SELECT 'ALTER TABLE ' + table_name +
> ' ALTER COLUMN ' + COLUMN_NAME +
> CASE WHEN DATA_TYPE = 'char' THEN ' nchar ' ELSE '
nvarchar ' END +
> ' (' + LTRIM(STR(CHARACTER_MAXIMUM_LENGTH)) + ')' +
> char(13) + char(10) + 'GO'
>FROM information_schema.columns WHERE DATA_TYPE IN
('varchar', 'char')
>--now execute the statements that are returned from the
select statement
>go
>DROP TABLE foo
>
>
>--
>Keith
>
><anonymous@.discussions.microsoft.com> wrote in message
news:869f01c4328f$e1ddd420$a401280a@.phx.gbl...
>> Hi,
>> We are using sql server 2000..We intend to change the
>> char/varchar columns to nchar/nvarchat.Our application
>> contains lot's of dynamic tables.Hence there are no
>> standard no of tables and indexes in all sites..Can any
>> one help me out in writing scripts which queries the
>> dictionary objects and gives scripts which would work
fine
>> in all sites...?
>>
>> Thanks in advance
>> Sridhar
>.
>|||SQL Server stores this information in system tables, like sysindexes, syscomments etc. You can read off of
these and use that information to re-generate the statements needed to re-create your stuff. Or script the
stuff: http://www.karaszi.com/sqlserver/info_generate_script.asp.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
<anonymous@.discussions.microsoft.com> wrote in message news:898401c432a5$31453bb0$a601280a@.phx.gbl...
> Hi,
> Thanks..do some where sql server stores the index and
> constraints structure some where in dictionary ...other
> wise how do i recreate the indexes and constraints after
> converting to nchar
> Sridhar
> >--Original Message--
> >This script should get you started:
> >
> >use tempdb
> >GO
> >CREATE TABLE foo (firstcol varchar(10), secondcol nvarchar
> (10), thircol char(10))
> >go
> >
> >
> >SELECT 'ALTER TABLE ' + table_name +
> > ' ALTER COLUMN ' + COLUMN_NAME +
> > CASE WHEN DATA_TYPE = 'char' THEN ' nchar ' ELSE '
> nvarchar ' END +
> > ' (' + LTRIM(STR(CHARACTER_MAXIMUM_LENGTH)) + ')' +
> > char(13) + char(10) + 'GO'
> >FROM information_schema.columns WHERE DATA_TYPE IN
> ('varchar', 'char')
> >
> >--now execute the statements that are returned from the
> select statement
> >
> >go
> >DROP TABLE foo
> >
> >
> >
> >
> >--
> >Keith
> >
> >
> ><anonymous@.discussions.microsoft.com> wrote in message
> news:869f01c4328f$e1ddd420$a401280a@.phx.gbl...
> >> Hi,
> >>
> >> We are using sql server 2000..We intend to change the
> >> char/varchar columns to nchar/nvarchat.Our application
> >> contains lot's of dynamic tables.Hence there are no
> >> standard no of tables and indexes in all sites..Can any
> >> one help me out in writing scripts which queries the
> >> dictionary objects and gives scripts which would work
> fine
> >> in all sites...?
> >>
> >>
> >> Thanks in advance
> >>
> >> Sridhar
> >>
> >.
> >

conversion char/nchar

Saturday, February 25, 2012

Containstable variable usage

I have a stored procedure that uses containstable and want to make it a little dynamic so I was going to add a parameter that consist of the column names that needed to be search. But when I add a variable I get an error saying incorrect syntax....

Can you not use a variable as a column list? I have a variable for search criteria and it works fine...

Here is my syntax

containstable([tablename],@.columnlist,@.srch)

I have been looking online and can't seem to find anything that says I can or cannot use a variable.

Column list cannot be replaced by variable. You have to use dynamic SQL to form and execute the SELECT statement if you want to parameterize CONTAINS/CONTAINSTABLE column list.

Friday, February 24, 2012

Contains function in sql server 2005

H!
I use SQL Server 2005 and ASP.Net to program a dynamic web site. I use
full-text queries against plain character-based data with
'contains' predicat.
When my research contains more than one word separated by space (for
example: pierre baby), I have this error:
System.Data.SqlClient.SqlException: Syntax error near 'baby' in the
full-text search condition 'pierre baby'.
But the research works perfectly with (pierre|baby).
To have more user friendly tool, I would like to replace space by
"|", "&" by "+" etc.
I learn in the Internet about thesaurus function. And I performed the
following steps:
=B7 I add to the tsGLOBAL.xml file (in ../ Microsoft SQL
Server\MSSQL.1\MSSQL\FTDATA\ directory) the lines :
<XML ID=3D"Microsoft Search Thesaurus">
<thesaurus xmlns=3D"x-schema:tsSchema.xml">
<diacritics_sensitive>0</diacritics_sensitive>
<expansion>
 
|
</expansion>
<expansion>
+
&
</expansion>
<replacement>
<pat> </pat>
|
</replacement>
<replacement>
<pat>+</pat>
&
</replacement>
</thesaurus>
</XML>
=B7 I modified my SQLquery like that:
dim requeteMC as string =3D "Select id , title from Table_V where
contains(*, 'formsof(thesaurus, " & keywords & ") ');"
But I had the same error:
System.Data.SqlClient.SqlException: Syntax error near 'baby' in the
full-text search condition 'pierre baby'.
Can any one help me about that? So when the user enter (baby Pierre)
the program converts it on baby|Pierre and we will not have an error.
Thank you very much,
regrads,
Djamila.Perhaps you get better responses by posting this to
microsoft.public.sqlserver.fulltext, which by the way, is one of the
newsgroups that you missed as you post-bombed multiple newsgroups.
Often, the quality of the responses received is related to our ability to
'bounce' ideas off of each other. In the future, to make it easier for us to
give you ideas, and to prevent folks from wasting time on already answered
questions, please:
Don't post to multiple newsgroups. Choose the one that best fits your
question and post there. Only post to another newsgroup if you get no answer
in a day or two (or if you accidentally posted to the wrong newsgroup -and
you indicate that you've already posted elsewhere).
If you really think that a question belongs into more than one newsgroup,
then use your newsreader's capability of multi-posting, i.e., posting one
occurrence of a message into several newsgroups at once. If you multi-post
appropriately, answers 'should' appear in all the newsgroups. Folks
responding in different newsgroups will see responses from each other, even
if the responses were posted in a different newsgroup.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"djamila" <djamilabouzid@.gmail.com> wrote in message
news:1164826388.137537.34310@.j72g2000cwa.googlegroups.com...
H!
I use SQL Server 2005 and ASP.Net to program a dynamic web site. I use
full-text queries against plain character-based data with
'contains' predicat.
When my research contains more than one word separated by space (for
example: pierre baby), I have this error:
System.Data.SqlClient.SqlException: Syntax error near 'baby' in the
full-text search condition 'pierre baby'.
But the research works perfectly with (pierre|baby).
To have more user friendly tool, I would like to replace space by
"|", "&" by "+" etc.
I learn in the Internet about thesaurus function. And I performed the
following steps:
I add to the tsGLOBAL.xml file (in ../ Microsoft SQL
Server\MSSQL.1\MSSQL\FTDATA\ directory) the lines :
<XML ID="Microsoft Search Thesaurus">
<thesaurus xmlns="x-schema:tsSchema.xml">
<diacritics_sensitive>0</diacritics_sensitive>
<expansion>
 
|
</expansion>
<expansion>
+
&
</expansion>
<replacement>
<pat> </pat>
|
</replacement>
<replacement>
<pat>+</pat>
&
</replacement>
</thesaurus>
</XML>
I modified my SQLquery like that:
dim requeteMC as string = "Select id , title from Table_V where
contains(*, 'formsof(thesaurus, " & keywords & ") ');"
But I had the same error:
System.Data.SqlClient.SqlException: Syntax error near 'baby' in the
full-text search condition 'pierre baby'.
Can any one help me about that? So when the user enter (baby Pierre)
the program converts it on baby|Pierre and we will not have an error.
Thank you very much,
regrads,
Djamila.

CONTAINS function and OR

I am attempting to create a query with a "dynamic" CONTAINS query, e.g.
DECLARE @.Keywords VARCHAR(128)
SET @.Keywords = NULL
SELECT * FROM Jobs WHERE (@.Keywords IS NULL OR CONTAINS(JobTitle,
@.Keywords))
However, the CONTAINS function seems to behave very weirdly when OR is
involved. Even though @.Keywords is null, it still evaluates the contains,
and fails because of the null.
Server: Msg 7603, Level 15, State 1, Line 40
Syntax error in search condition, or empty or null search condition ''.
On the other and, if I change the query to this:
SELECT * FROM Jobs WHERE (@.Keywords IS NULL OR CONTAINS(JobTitle,
@.Keywords)) AND JobID IN (SELECT JobID FROM JobSkills WHERE SkillID = 2)
It works fine.
But that's not it. If I move the order of the clauses:
SELECT * FROM Jobs WHERE JobID IN (SELECT JobID FROM JobSkills WHERE SkillID
= 2) AND (@.Keywords IS NULL OR CONTAINS(JobTitle, @.Keywords))
I get the original error. Same goes for adding another contains clause at
the beginning. It seems that the original CONTAINS works fine, others defy
all logic.
Can someone suggest what is causing this behaviour (or why it works like
this - doesn't seem to make any sense) and a possible workaround short of
using hideous dynamic SQL?
Edward wrote on Fri, 20 Jan 2006 09:38:58 +1100:

> I am attempting to create a query with a "dynamic" CONTAINS query, e.g.
> DECLARE @.Keywords VARCHAR(128)
> SET @.Keywords = NULL
> SELECT * FROM Jobs WHERE (@.Keywords IS NULL OR CONTAINS(JobTitle,
> @.Keywords))
> However, the CONTAINS function seems to behave very weirdly when OR is
> involved. Even though @.Keywords is null, it still evaluates the contains,
> and fails because of the null.
> Server: Msg 7603, Level 15, State 1, Line 40
> Syntax error in search condition, or empty or null search condition
> ''.
> On the other and, if I change the query to this:
> SELECT * FROM Jobs WHERE (@.Keywords IS NULL OR CONTAINS(JobTitle,
> @.Keywords)) AND JobID IN (SELECT JobID FROM JobSkills WHERE SkillID = 2)
> It works fine.
> But that's not it. If I move the order of the clauses:
> SELECT * FROM Jobs WHERE JobID IN (SELECT JobID FROM JobSkills WHERE
> SkillID = 2) AND (@.Keywords IS NULL OR CONTAINS(JobTitle, @.Keywords))
> I get the original error. Same goes for adding another contains clause at
> the beginning. It seems that the original CONTAINS works fine, others defy
> all logic.
> Can someone suggest what is causing this behaviour (or why it works like
> this - doesn't seem to make any sense) and a possible workaround short of
> using hideous dynamic SQL?
It depends on the query parser and how it decides to process the query.
Depending on the order it processes clauses, and what they contain, it might
skip the CONTAINS clause completely (which it appears to do in the 2nd
case). Using FTS clauses when unnecessary will impact performance as the FTS
process is external to SQL Server. You could try doing the following:
IF COALESCE(@.Keywords,'') = ''
SELECT * FROM Jobs
ELSE
SELECT * FROM Jobs WHERE CONTAINS(JobTitle, @.Keywords)
END
This avoids dynamic SQL, and prevents the error is @.Keywords is NULL or
empty (an empty string will also cause an error, not just a NULL)
Dan

Sunday, February 19, 2012

consuming result sets from dynamic queries within a cursor

Hi SQLers
I have the flowing scenario. I have a cursor that amongst other things
returns me the name of a stored procedure that needs to run and a parameter
to pass to it. Depending on whether the stored proc returns a result I then
decide what to do with the other data I get from the cursor. But I want to
suppress the result sets coming from the inner stored proc calls.
I basically want something like this in my cursor loop
insert into #tmp exec ( @.StoredProcedureName + ' ' + @.messageID )
if exists (select 1 from #tmp)
-- do something with cursor data
else
-- do something else with cursor data
-- where @.StoredProcedureName and @.messageID are data from the cursor
but I get an error saying invalid object #tmp
I cannot declare #tmp as a table variable as unfortunately the stored
proceedures called are different for different iterations of the cursor, tha
t
is @.StoredProcedureName changes and the result set from the different
procedures is different. I cannot make them the same as each of these proc
does a different job and they are called by different callers and so cannot
be changed.
and I dont want to say
exec ( @.StoredProcedureName + ' ' + @.messageID )
if (@.@.ROWCOUNT > 0)
-- do something
else
-- do something else
as the result set of the exec is then returned to the caller.
If anyone has any ideas I would be very greatful. I know some ways to solve
this in C# .NET but I was just wondering if there is a SQL solution. I expec
t
there is not but thought I would ask those that are likely to know.
Many thanks
DerekI don't understand why you would want to execute a stored proc dynamically
based on a cursor. TSQL does have IF statements for flow control but what
you are describing is a very procedural solution. Are you sure there isn't a
set-based SQL alternative to all this?
Read this article on how to do dynamic SQL (and why NOT to do it):
http://www.sommarskog.se/dynamic_sql.html
This one explains how you can process result sets from an SP:
http://www.sommarskog.se/share_data.html
David Portas
SQL Server MVP
--|||Hi David,
Thanks for your time.
Basically my cursor is finding all entries in a MessageReceipients table
that have NULL as their SentDateTime and once armed with the MessageID and
@.MessageType for these intended recipients, I need to call the appropriate
stored procedures (the actual stored proc will depend on the MessageType) to
see if these receipients can still have messages sent to them.
For example it is possible that a message receipient is in the
MessageRecipient table and that the mail system originally failed to send th
e
message but by the time we come to do this check (to resend the message) the
intended recipient my no longer want or be able to receive messages. This
fact can only be determined by calling the @.StoredProcedureName (in my
original code sample) and the reason this is dynamic is that the procedure
that needs to be checked to determin if a message can still be sent is
different for different message types.
I know I can solve this problem in my C# code and perhaps this is the place
to do it. For example I could just return all receipients that have got
potentially outstanding messages. And in the C# check the @.MessageType and
@.MessageID and then call the @.StoredProcedureName with @.MessageID to deterim
whether it is really appropriate to send the message to the receipient.
I was just hoping to not have to do this step in the C# application if I
could avoid it.
I also realise that re writing the various @.StoredProcedureName SPs would
make life easier but unfortunately they are used by other parts of the app
and so I cannot change them. So I would have to esentially duplicate there
functionality with the exception of how they return their results and that
would not be good from a maintenance perspective.
Having said that for now all I am doing in the C# application is looking at
the last result set returned from my SP as this is the result set that I
want. But this is only ok as the results sets are quite small and so I do no
t
need to worry to much about returning unwanted data to the C# application.
Hope that make things a little clearer
Many thanks
Derek
"David Portas" wrote:

> I don't understand why you would want to execute a stored proc dynamically
> based on a cursor. TSQL does have IF statements for flow control but what
> you are describing is a very procedural solution. Are you sure there isn't
a
> set-based SQL alternative to all this?
> Read this article on how to do dynamic SQL (and why NOT to do it):
> http://www.sommarskog.se/dynamic_sql.html
> This one explains how you can process result sets from an SP:
> http://www.sommarskog.se/share_data.html
> --
> David Portas
> SQL Server MVP
> --
>
>

Thursday, March 22, 2012

conversion char/nchar

conversion char/nchar

conversion char/nchar

Saturday, February 25, 2012

Containstable variable usage

Friday, February 24, 2012

Contains function in sql server 2005

CONTAINS function and OR

Sunday, February 19, 2012

consuming result sets from dynamic queries within a cursor

Consolidation Query

Blog Archive

About Me