Convert a Unicode Database into a non-unicode database

Hi there.

We have an application that can run on a non-unicode or a unicode sql
server
database.

Currently the application is running in a unicode database, as a
non-unicode database is less than half the size, I would prefer to
have a non-unicode database for demo purposes to be on my laptop, etc
etc

Is it possible to change a unicode sql server 2000 database into a
non-unicode database?

And if so, how would I go about doing this?

Any help would be greatly appreciated.

Thanks

RodgerUnicode data uses NCHAR / NVARCHAR / NTEXT datatypes rather than CHAR /
VARCHAR / TEXT. This is defined at the column level rather than the database
level.

To change your database you could script all the tables, replace the unicode
datatypes with their non-unicode equivalents and then run the creation
script to create the new database. This might affect the behaviour of stored
procedures and other code but if the app is designed to cope with this then
presumably it shouldn't be a problem. Correctly converting your unicode data
to a non-unicode collation might be more of a challenge though.

--
David Portas
SQL Server MVP
--|||wsbackup@.hotmail.com (Rodger Dodger) wrote in message news:<c1467e75.0408102040.6037ee2f@.posting.google.com>...
> Hi there.
> We have an application that can run on a non-unicode or a unicode sql
> server
> database.
> Currently the application is running in a unicode database, as a
> non-unicode database is less than half the size, I would prefer to
> have a non-unicode database for demo purposes to be on my laptop, etc
> etc
> Is it possible to change a unicode sql server 2000 database into a
> non-unicode database?
> And if so, how would I go about doing this?
> Any help would be greatly appreciated.
> Thanks
> Rodger

I'm not sure I understand your question - MSSQL always supports
Unicode in all databases. You can change a database's collation, but
that doesn't affect Unicode support, so I guess you may want to know
how to change all your nvarchar columns to varchar? If so, then one
way is to execute the results of a query like this:

SELECT 'ALTER TABLE ' + TABLE_SCHEMA + '.' + TABLE_NAME + ' ALTER
COLUMN ' + COLUMN_NAME + ' VARCHAR(' + CAST(CHARACTER_MAXIMUM_LENGTH
AS VARCHAR(10)) + ')'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE = 'NVARCHAR'

This is a rather heavy-handed approach, however, so make sure you have
a backup before running it. If this doesn't help, or isn't what you're
trying to do, then please clarify what you need to achieve.

Simon

Conversion of "True" or "False" textfields to display "Yes" or &qu

Hi All,

I've created a report that has some textfields containing "TRUE" or "FALSE" as data output when I run the report. How do I convert these textfields to display "YES" or "NO" in the output when I run my report. (bits)

Please advise.

Thanks for your time and help.

=iif(Fields!ThisRow.Value = "True", "Yes", "No")|||

Thanks Brad !! I have another question...Is it possible to represent this column data as a Checkbox?

Please advise.

THanks.

|||

You could use wingdings font checkmark & a border around a textbox, with a conditional expression to display of the checkmark.

cheers,

Andrew

|||

Set properties of your textbox like this:

Borderstyle
Default = Solid
Font
FontFamily = Wingdings2
Expression
Value =iif(Fields!ThisRow.Value = "True", "P", "" )

Hope this helps.

Jarret

|||

Hi

Check this link:http://www.sqljunkies.com/HowTo/0D746276-352D-42D9-A2BB-225C0B2A3AB5.scuk

Hope this helps.

|||Thanks to you all for your help

Conversion of "True" or "False" textfields to display "Yes" or

Hi All,

I've created a report that has some textfields containing "TRUE" or "FALSE" as data output when I run the report. How do I convert these textfields to display "YES" or "NO" in the output when I run my report. (bits)

Please advise.

Thanks for your time and help.

=iif(Fields!ThisRow.Value = "True", "Yes", "No")|||

Thanks Brad !! I have another question...Is it possible to represent this column data as a Checkbox?

Please advise.

THanks.

|||

You could use wingdings font checkmark & a border around a textbox, with a conditional expression to display of the checkmark.

cheers,

Andrew

|||

Set properties of your textbox like this:

Borderstyle
Default = Solid
Font
FontFamily = Wingdings2
Expression
Value =iif(Fields!ThisRow.Value = "True", "P", "" )

Hope this helps.

Jarret

|||

Hi

Check this link:http://www.sqljunkies.com/HowTo/0D746276-352D-42D9-A2BB-225C0B2A3AB5.scuk

Hope this helps.

|||Thanks to you all for your help

Conversion failed when converting datetime from character string

Hi,

I receive an Error Message: Conversion failed when converting datetime from character string when I try to run this.

Can someone point out what I'm doing wrong?

SELECT Principal,

SUM(CASE WHEN Recdate BETWEEN '=@.LYbegin' AND '=@.LYend' THEN

Amount ELSE 0 END) AS LY,

SUM(CASE WHEN Recdate BETWEEN '=@.TYbegin' AND '=@.TYend' THEN

Amount ELSE 0 END) AS TY

FROM dbo.Checks

GROUP BY Principal

If I execute the query with the dates it works fine:

SELECT Principal,

SUM(Case When RecDate BETWEEN '1-1-2005 00:00:00.000' AND '1-30-2005 00:00:00.000' THEN Amount else 0 end)AS LY,

SUM(Case When RecDate BETWEEN '2-1-2005 00:00:00.000' AND '2-28-2005 00:00:00.000' THEN Amount else 0 end)AS TY

FROM Checks

GROUP BY Principal

Thanks,

Terry McCullagh

I suppose you are trying to use a parameterized query statement in the RS query designer. You should use the following commandtext to have query parameters being detected and working:

SELECT Principal,
SUM(CASE WHEN Recdate BETWEEN @.LYbegin AND @.LYend THEN Amount ELSE 0 END) AS LY,
SUM(CASE WHEN Recdate BETWEEN @.TYbegin AND @.TYend THEN Amount ELSE 0 END) AS TY
FROM dbo.Checks
GROUP BY Principal

-- Robert

|||

Robert,

That works great.

Thank you,

Terry McCullagh

Conversion

Hi,
I am in the process writing scripts for CHAR/VARCHAR to
NCHAR/NVARCHAR conversion..Infact i have completed
it..When I run these scripts against DB is verly
slow..This is happening when i run convert scripts
eaxctly..Even select name from sysobjects doesn't return
values..There are heavy IO is going on the DB..what are
the areas i should concentrate the to improve performance..
Basically I have written cursor whic executes one by one
the alter scripts..
Sridhar...Changing a CHAR to VARCHAR will most likely involve each row being affected
and hence all the I/O. If you are altering more than one column per table
you might get better performance by creating a second table with the changed
columns and Inserting all the rows into it. Drop the original, rename the
new to the old and add the appropriate indexes, RI etc.. Just make sure you
have good backups first.
Andrew J. Kelly SQL MVP
<anonymous@.discussions.microsoft.com> wrote in message
news:297601c47e07$62bd4ae0$a501280a@.phx.gbl...
> Hi,
> I am in the process writing scripts for CHAR/VARCHAR to
> NCHAR/NVARCHAR conversion..Infact i have completed
> it..When I run these scripts against DB is verly
> slow..This is happening when i run convert scripts
> eaxctly..Even select name from sysobjects doesn't return
> values..There are heavy IO is going on the DB..what are
> the areas i should concentrate the to improve performance..
>
> Basically I have written cursor whic executes one by one
> the alter scripts..
>
> Sridhar...
>

Conversion

Hi,
I am in the process writing scripts for CHAR/VARCHAR to
NCHAR/NVARCHAR conversion..Infact i have completed
it..When I run these scripts against DB is verly
slow..This is happening when i run convert scripts
eaxctly..Even select name from sysobjects doesn't return
values..There are heavy IO is going on the DB..what are
the areas i should concentrate the to improve performance..
Basically I have written cursor whic executes one by one
the alter scripts..
Sridhar...
Changing a CHAR to VARCHAR will most likely involve each row being affected
and hence all the I/O. If you are altering more than one column per table
you might get better performance by creating a second table with the changed
columns and Inserting all the rows into it. Drop the original, rename the
new to the old and add the appropriate indexes, RI etc.. Just make sure you
have good backups first.
Andrew J. Kelly SQL MVP
<anonymous@.discussions.microsoft.com> wrote in message
news:297601c47e07$62bd4ae0$a501280a@.phx.gbl...
> Hi,
> I am in the process writing scripts for CHAR/VARCHAR to
> NCHAR/NVARCHAR conversion..Infact i have completed
> it..When I run these scripts against DB is verly
> slow..This is happening when i run convert scripts
> eaxctly..Even select name from sysobjects doesn't return
> values..There are heavy IO is going on the DB..what are
> the areas i should concentrate the to improve performance..
>
> Basically I have written cursor whic executes one by one
> the alter scripts..
>
> Sridhar...
>

Conversion

Hi,
I am in the process writing scripts for CHAR/VARCHAR to
NCHAR/NVARCHAR conversion..Infact i have completed
it..When I run these scripts against DB is verly
slow..This is happening when i run convert scripts
eaxctly..Even select name from sysobjects doesn't return
values..There are heavy IO is going on the DB..what are
the areas i should concentrate the to improve performance..
Basically I have written cursor whic executes one by one
the alter scripts..
Sridhar...Can you tell us how you are performing the upgrade i.e.
Have a different database or are you performing an alter
column ?
Thanks
Peter
>--Original Message--
>Hi,
> I am in the process writing scripts for CHAR/VARCHAR
to
>NCHAR/NVARCHAR conversion..Infact i have completed
>it..When I run these scripts against DB is verly
>slow..This is happening when i run convert scripts
>eaxctly..Even select name from sysobjects doesn't return
>values..There are heavy IO is going on the DB..what are
>the areas i should concentrate the to improve
performance..
>
>Basically I have written cursor whic executes one by one
>the alter scripts..
>
>Sridhar...
>.
>|||By alter columns...
>--Original Message--
>Can you tell us how you are performing the upgrade i.e.
>Have a different database or are you performing an alter
>column ?
>Thanks
>Peter
>>--Original Message--
>>Hi,
>> I am in the process writing scripts for CHAR/VARCHAR
>to
>>NCHAR/NVARCHAR conversion..Infact i have completed
>>it..When I run these scripts against DB is verly
>>slow..This is happening when i run convert scripts
>>eaxctly..Even select name from sysobjects doesn't return
>>values..There are heavy IO is going on the DB..what are
>>the areas i should concentrate the to improve
>performance..
>>
>>Basically I have written cursor whic executes one by one
>>the alter scripts..
>>
>>Sridhar...
>>.
>.
>|||Changing a CHAR to VARCHAR will most likely involve each row being affected
and hence all the I/O. If you are altering more than one column per table
you might get better performance by creating a second table with the changed
columns and Inserting all the rows into it. Drop the original, rename the
new to the old and add the appropriate indexes, RI etc.. Just make sure you
have good backups first.
--
Andrew J. Kelly SQL MVP
<anonymous@.discussions.microsoft.com> wrote in message
news:297601c47e07$62bd4ae0$a501280a@.phx.gbl...
> Hi,
> I am in the process writing scripts for CHAR/VARCHAR to
> NCHAR/NVARCHAR conversion..Infact i have completed
> it..When I run these scripts against DB is verly
> slow..This is happening when i run convert scripts
> eaxctly..Even select name from sysobjects doesn't return
> values..There are heavy IO is going on the DB..what are
> the areas i should concentrate the to improve performance..
>
> Basically I have written cursor whic executes one by one
> the alter scripts..
>
> Sridhar...
>|||Consider what Andrew has said, but also regardless of
which way you are doing it take off all indexes before you
start.
How many rows of data are you converting ?
>--Original Message--
>By alter columns...
>>--Original Message--
>>Can you tell us how you are performing the upgrade i.e.
>>Have a different database or are you performing an alter
>>column ?
>>Thanks
>>Peter
>>--Original Message--
>>Hi,
>> I am in the process writing scripts for CHAR/VARCHAR
>>to
>>NCHAR/NVARCHAR conversion..Infact i have completed
>>it..When I run these scripts against DB is verly
>>slow..This is happening when i run convert scripts
>>eaxctly..Even select name from sysobjects doesn't
return
>>values..There are heavy IO is going on the DB..what are
>>the areas i should concentrate the to improve
>>performance..
>>
>>Basically I have written cursor whic executes one by
one
>>the alter scripts..
>>
>>Sridhar...
>>.
>>.
>.
>

Controlling the cursor position

Let's say I run a query that returns a 100k records. Is there a way to
control where the cursor is in the result set. For example, can I get it
to jump to row 50k and return the next 500 rows?Taken from Transact SQL help:
-- Fetch the row that is two rows prior to the current row.
FETCH RELATIVE -2 FROM authors_cursor
Hope that helps
"preston" wrote:

> Let's say I run a query that returns a 100k records. Is there a way to
> control where the cursor is in the result set. For example, can I get it
> to jump to row 50k and return the next 500 rows?
>|||Hi, preston
See: http://www.aspfaq.com/show.asp?id=2120
Razvan

controlling merge syncronisation at subscriber with ActiveX

Hi,
If I am to use the merge activex control to run syncs at the subscriber, in
what system tables will I find merge history, error messages etc ?
I read in another thread about querying the subscriber prior to attempting a
sync to determine if the subscriber db needed reinitialisation (by comparing
version number values in a user table) - so that active x could flag the
subscription for reinitialisation automatically. Any idea how this would
actually be done ?
It would be great to have the sunscriber db be automatically flagged for
reinitialisation when needed, and not require user input.
Thanks for your help
Darren
query the msmerge_history table in the distribution database on the
subscriber if its a pull.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Darren Wallace" <darren@.pcresources.com.au> wrote in message
news:e5pPdbNlEHA.3392@.TK2MSFTNGP14.phx.gbl...
> Hi,
> If I am to use the merge activex control to run syncs at the subscriber,
in
> what system tables will I find merge history, error messages etc ?
> I read in another thread about querying the subscriber prior to attempting
a
> sync to determine if the subscriber db needed reinitialisation (by
comparing
> version number values in a user table) - so that active x could flag the
> subscription for reinitialisation automatically. Any idea how this would
> actually be done ?
> It would be great to have the sunscriber db be automatically flagged for
> reinitialisation when needed, and not require user input.
> Thanks for your help
> Darren
>

Controlling log file size

What's the best practice to keep log file size low. I have some databases
that run DTS packages to import data every night. The logs get huge, 17
gigs. I have an Arcserve agent backing them up, but it does not seem to
shrink them.
Any ideas?
Thanks
Greg
What recovery model are you using? Do you care about your transaction logs?
If you don't care about the data within the logs you can set your recovery
model to simple. You can also perform a BACKUP LOG <databasename> WITH
NO_LOG between each export to clear the transaction log.
NOTE: these steps will break transaction log backup/restore. Make sure that
you know what you are doing and how it will impact your database backups
before setting the recovery model or truncating the transaction log.
Keith
"Greg Richards" <grichards@.matrixwebs.com> wrote in message
news:uyL9M0FYEHA.3520@.TK2MSFTNGP10.phx.gbl...
> What's the best practice to keep log file size low. I have some databases
> that run DTS packages to import data every night. The logs get huge, 17
> gigs. I have an Arcserve agent backing them up, but it does not seem to
> shrink them.
> Any ideas?
> Thanks
> Greg
>

Controlling log file size

What's the best practice to keep log file size low. I have some databases
that run DTS packages to import data every night. The logs get huge, 17
gigs. I have an Arcserve agent backing them up, but it does not seem to
shrink them.
Any ideas?
Thanks
GregWhat recovery model are you using? Do you care about your transaction logs?
If you don't care about the data within the logs you can set your recovery
model to simple. You can also perform a BACKUP LOG <databasename> WITH
NO_LOG between each export to clear the transaction log.
NOTE: these steps will break transaction log backup/restore. Make sure that
you know what you are doing and how it will impact your database backups
before setting the recovery model or truncating the transaction log.
--
Keith
"Greg Richards" <grichards@.matrixwebs.com> wrote in message
news:uyL9M0FYEHA.3520@.TK2MSFTNGP10.phx.gbl...
> What's the best practice to keep log file size low. I have some databases
> that run DTS packages to import data every night. The logs get huge, 17
> gigs. I have an Arcserve agent backing them up, but it does not seem to
> shrink them.
> Any ideas?
> Thanks
> Greg
>

Controlling log file size

What's the best practice to keep log file size low. I have some databases
that run DTS packages to import data every night. The logs get huge, 17
gigs. I have an Arcserve agent backing them up, but it does not seem to
shrink them.
Any ideas?
Thanks
GregWhat recovery model are you using? Do you care about your transaction logs?
If you don't care about the data within the logs you can set your recovery
model to simple. You can also perform a BACKUP LOG <databasename> WITH
NO_LOG between each export to clear the transaction log.
NOTE: these steps will break transaction log backup/restore. Make sure that
you know what you are doing and how it will impact your database backups
before setting the recovery model or truncating the transaction log.
Keith
"Greg Richards" <grichards@.matrixwebs.com> wrote in message
news:uyL9M0FYEHA.3520@.TK2MSFTNGP10.phx.gbl...
> What's the best practice to keep log file size low. I have some databases
> that run DTS packages to import data every night. The logs get huge, 17
> gigs. I have an Arcserve agent backing them up, but it does not seem to
> shrink them.
> Any ideas?
> Thanks
> Greg
>

Controlling flow in a stored procedure

I have a stored procedure with two UPDATE statements in it. The second UPDATE statement relies on the completion of the first UPDATE statement to run correctly.

The problem I am running into is that SQL Server sometimes runs the second statement before completing the first.

To get around this, I tried putting the second UPDATE statement in a different stored procedure called within the first procedure, but I am still having problems.

I do not believe I am doing anything wrong, but just in case, here is the relevant code from the proc:

-- Look up County ID

BEGIN TRANSACTION

UPDATE tmpZoneTypes

SET CountyID =

(SELECT CountyID

FROM tblCountyLkp

WHERE tblCountyLkp.CountyName = LTRIM(RTRIM(tmpZoneTypes.CountyName)))

COMMIT TRANSACTION

-- Look up existing Zone Type IDs

BEGIN TRANSACTION

UPDATE tmpZoneTypes

SET ZoneTypeID =

(SELECT tblZoneTypes.ZoneTypeID

FROM tblZoneTypes

WHERE tblZoneTypes.CountyID = tmpZoneTypes.CountyID

AND tblZoneTypes.FieldNbr = tmpZoneTypes.FieldNbr

AND LTRIM(RTRIM(tblZoneTypes.ZoneAbbrev)) = LTRIM(RTRIM(tmpZoneTypes.ZoneAbbrev))

AND LTRIM(RTRIM(tblZoneTypes.ZoneFull)) = LTRIM(RTRIM(tmpZoneTypes.ZoneFull)))

COMMIT TRANSACTION

Is there a way to control the flow so the second update statement won't run until the first statement has been completed? I thought about maybe using a trigger to fire whenever the CountyID field is updated. Other options?

chris

1 variant (for SQL 2000 & SQL 2005):

begin transaction

declare @.ErrorVar int

update .... --The First Update

set @.ErrorVar = @.@.Error

if @.ErrorVar <>0

begin

-- Insert your error hadling code

rollback --For Example rollback transaction

end

else

begin

update ... --The second update

commit

end

2 variant (for SQL 2005 only):

begin tran

begin try

update ... --The first update

--If you have error in fist update you go to catch block

update ... - The second update

commit

end try

begin catch

-- Insert your error hadling code

rollback --For Example rollback transaction

end catch

|||SQL always executes "top down" and completes the first statement before starting the 2nd. What makes you think it is not complete?

The only way I see you would get different results than expected with what you posted, would be if you have the isolation level set to "read uncommitted". You can set the isolation level by using:

SET TRANSACTION ISOLATION LEVEL

SERIALIZABLE

at the top of your stored proc and that will force all updates to be committed and locks to be placed on the data until you are done.|||

Thanks for the suggestions from both of you. It turns out the problem was a bug in a subsequent UPDATE statement that was changing my ZoneTypeID back to NULL. I fixed the bug, and now the proc works perfectly.

chris

Controling Replation agent actions

Hello there
I some questions:
1. Iw'd like to run some store procedures before i'm starging the
replication every day. Dose it enouth to add the task before the replication
task?
2. If the replication faild i would like to make sure that none of the
changes will be made.
3. IF an error occur can i nevegat it to the type of error:
if the error is data error(validation of foreign key) or connection error.
So i can give it order to run 5 minutes afterword?
4. Can i output the errors to outside files in order that other programs
that use sql server (Access) can give a message to the user?
Roy,
adding another step would be fine, or another job which runs sp_start_job
after your code had finished.
Trapping errors is really well catered fro in SQL 2005 but not so easy in
SQL 2000. You'd be better testing your changes in code rather than
attempting your changes eg look for a PK value before trying the FK insert.
If the PK doesn't exist, then logging this to a table and taking appropriate
actions eg making your own error message available to Access.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||1) I would make these procs the first job step of your replication job
2) Double click on the job step, select advanced and have it run a job or
script on failure.
3) This is a little tricky - the error should be in the msrepl_errors table
in the distribution database where the agent is run. You can query it there,
but the error may not be there depending on the error message. What you
would need to do is restart the agent on failure, but this time use the
verbose agent profile. The complete error message will now be in the
msrepl_Errors table. Errors are also logged in text files and dumped in
%WindDir%\system32 and will have an err extension. You can poll for them
using FileSystemWatcher.
4) You might want to poll msrepl_errors as discussed above and write to an
access database. Or you could fire something using the Replication Alert
Agent Failure.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:ePub0LyAGHA.2656@.tk2msftngp13.phx.gbl...
> Hello there
> I some questions:
> 1. Iw'd like to run some store procedures before i'm starging the
> replication every day. Dose it enouth to add the task before the
> replication
> task?
> 2. If the replication faild i would like to make sure that none of the
> changes will be made.
> 3. IF an error occur can i nevegat it to the type of error:
> if the error is data error(validation of foreign key) or connection error.
> So i can give it order to run 5 minutes afterword?
> 4. Can i output the errors to outside files in order that other programs
> that use sql server (Access) can give a message to the user?
>
>
|||Whell Hilary
I've made an error in the replication and no MSRepl_Errors table were
created
After i got the error when i run the agent on the enterprise manager i
opened the query anlyser with the distribution database and i counldn't see
the table
When will the table appear?
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:OYG7KczAGHA.272@.TK2MSFTNGP09.phx.gbl...
> 1) I would make these procs the first job step of your replication job
> 2) Double click on the job step, select advanced and have it run a job or
> script on failure.
> 3) This is a little tricky - the error should be in the msrepl_errors
> table in the distribution database where the agent is run. You can query
> it there, but the error may not be there depending on the error message.
> What you would need to do is restart the agent on failure, but this time
> use the verbose agent profile. The complete error message will now be in
> the msrepl_Errors table. Errors are also logged in text files and dumped
> in %WindDir%\system32 and will have an err extension. You can poll for
> them using FileSystemWatcher.
> 4) You might want to poll msrepl_errors as discussed above and write to an
> access database. Or you could fire something using the Replication Alert
> Agent Failure.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Roy Goldhammer" <roy@.hotmail.com> wrote in message
> news:ePub0LyAGHA.2656@.tk2msftngp13.phx.gbl...
>

Control flow drawing bug

Minor issue here. But I just wanted to point it out.

I think this is how to reproduce it. If I disable a dataflow task and run the package, after it is complete, but yet still in the debugger, reenable the dataflow task. When I close the debugger, the dataflow task is still shaded as if it were disabled, but it will execute on the next run.

Close the package and re-open it and the work flow task is drawn correctly.

Thanks for the report, Jim. What build is this with? We've fixed one of these occurrences recently.

regards,
ash|||I am still on the April CTP.|||OK. I believe this has been fixed in the next CTP (CTP16). Please review when you get that, thanks!

Context Change and Cursor

I have T-SQL code that is used to run utilities against a particular list of databases that are stored in a table. I have a cursor that gets the list of DBs from the sysdatabases table based on entries in a table in a specific database. The list of databases will vary from server to server, so I need to have it use variables rather than code the db names into the script.

declare @.DatabaseId char(8)

declare DatabaseLoop cursor for
select name from master..sysdatabases where name in
(select <column name> from <db name..table> )

open DatabaseLoop
fetch next from DatabaseLoop into @.DatabaseId
while (@.@.fetch_status <> -1)
begin

...

So far, the context has not mattered, for example - backup @.DatabaseID works fine within the cursor since the context doesn't need to change. The problem is that I want to run particular scripts that require the context to be changed to the database. But, of course, USE @.DatabaseID does not work inside the cursor.

So, is there another way to be able to run my script against multiple variables other than using a cursor?

I am not necessarily looking for you to write my code, but if someone could point me in the right direction, I would appreciate it.

I would suggest that you use dynamic SQL for this and you can use a use in there.

declare @.query varchar(8000) -- I am assuming 2000 since you

--used sysdatabases

set @.query = 'use ' + @.database + ' select * from sysobjects'

exec (@.query)

I know this works in 2005, and am pretty sure it worked in 2000

Edit: Sorry, sent the message from my phone and it did a terrible job with the formatting

Contains()

I have the phrase
'One Two Three'
as the value of a column that is indexed for full text searching.
I run the query
SELECT * FROM table WHERE CONTAINS(column, 'One NEAR Two' );
and it returns the row with the above value, which is all well
and fine. Now, I need it so that the row would NOT be
returned for the following query:
SELECT * FROM table WHERE CONTAINS(column, 'Two NEAR One' );
Basically, I want it so that it looks for the words NEAR each
other but only in the order specified in the query. Such that it
returns rows where the first word is NEAR the second word
but also preceeds it as well.
Looking in the documentation, I didn't see how or if this is
possible. Is it? If so, how?
thnx,
ChristophChristoph,
See my reply in the newsgroup: microsoft.public.sqlserver.fulltext.
Thanks,
John
"Christoph Boget" <jcboget@.yahoo.com> wrote in message
news:eGKLwqNyEHA.3120@.TK2MSFTNGP12.phx.gbl...
> I have the phrase
> 'One Two Three'
> as the value of a column that is indexed for full text searching.
> I run the query
> SELECT * FROM table WHERE CONTAINS(column, 'One NEAR Two' );
> and it returns the row with the above value, which is all well
> and fine. Now, I need it so that the row would NOT be
> returned for the following query:
> SELECT * FROM table WHERE CONTAINS(column, 'Two NEAR One' );
> Basically, I want it so that it looks for the words NEAR each
> other but only in the order specified in the query. Such that it
> returns rows where the first word is NEAR the second word
> but also preceeds it as well.
> Looking in the documentation, I didn't see how or if this is
> possible. Is it? If so, how?
> thnx,
> Christoph
>
>|||> See my reply in the newsgroup: microsoft.public.sqlserver.fulltext.
Thanks. I posted a follow up.
thnx,
Christoph

Contains()

I have the phrase
'One Two Three'
as the value of a column that is indexed for full text searching.
I run the query
SELECT * FROM table WHERE CONTAINS(column, 'One NEAR Two' );
and it returns the row with the above value, which is all well
and fine. Now, I need it so that the row would NOT be
returned for the following query:
SELECT * FROM table WHERE CONTAINS(column, 'Two NEAR One' );
Basically, I want it so that it looks for the words NEAR each
other but only in the order specified in the query. Such that it
returns rows where the first word is NEAR the second word
but also preceeds it as well.
Looking in the documentation, I didn't see how or if this is
possible. Is it? If so, how?
thnx,
ChristophChristoph,
See my reply in the newsgroup: microsoft.public.sqlserver.fulltext.
Thanks,
John
"Christoph Boget" <jcboget@.yahoo.com> wrote in message
news:eGKLwqNyEHA.3120@.TK2MSFTNGP12.phx.gbl...
> I have the phrase
> 'One Two Three'
> as the value of a column that is indexed for full text searching.
> I run the query
> SELECT * FROM table WHERE CONTAINS(column, 'One NEAR Two' );
> and it returns the row with the above value, which is all well
> and fine. Now, I need it so that the row would NOT be
> returned for the following query:
> SELECT * FROM table WHERE CONTAINS(column, 'Two NEAR One' );
> Basically, I want it so that it looks for the words NEAR each
> other but only in the order specified in the query. Such that it
> returns rows where the first word is NEAR the second word
> but also preceeds it as well.
> Looking in the documentation, I didn't see how or if this is
> possible. Is it? If so, how?
> thnx,
> Christoph
>
>|||> See my reply in the newsgroup: microsoft.public.sqlserver.fulltext.
Thanks. I posted a follow up.
thnx,
Christoph

Contains()

I have the phrase
'One Two Three'
as the value of a column that is indexed for full text searching.
I run the query
SELECT * FROM table WHERE CONTAINS(column, 'One NEAR Two' );
and it returns the row with the above value, which is all well
and fine. Now, I need it so that the row would NOT be
returned for the following query:
SELECT * FROM table WHERE CONTAINS(column, 'Two NEAR One' );
Basically, I want it so that it looks for the words NEAR each
other but only in the order specified in the query. Such that it
returns rows where the first word is NEAR the second word
but also preceeds it as well.
Looking in the documentation, I didn't see how or if this is
possible. Is it? If so, how?
thnx,
Christoph
Christoph,
See my reply in the newsgroup: microsoft.public.sqlserver.fulltext.
Thanks,
John
"Christoph Boget" <jcboget@.yahoo.com> wrote in message
news:eGKLwqNyEHA.3120@.TK2MSFTNGP12.phx.gbl...
> I have the phrase
> 'One Two Three'
> as the value of a column that is indexed for full text searching.
> I run the query
> SELECT * FROM table WHERE CONTAINS(column, 'One NEAR Two' );
> and it returns the row with the above value, which is all well
> and fine. Now, I need it so that the row would NOT be
> returned for the following query:
> SELECT * FROM table WHERE CONTAINS(column, 'Two NEAR One' );
> Basically, I want it so that it looks for the words NEAR each
> other but only in the order specified in the query. Such that it
> returns rows where the first word is NEAR the second word
> but also preceeds it as well.
> Looking in the documentation, I didn't see how or if this is
> possible. Is it? If so, how?
> thnx,
> Christoph
>
>
|||> See my reply in the newsgroup: microsoft.public.sqlserver.fulltext.
Thanks. I posted a follow up.
thnx,
Christoph

Contains()

I have the phrase
'One Two Three'
as the value of a column that is indexed for full text searching.
I run the query
SELECT * FROM table WHERE CONTAINS(column, 'One NEAR Two' );
and it returns the row with the above value, which is all well
and fine. Now, I need it so that the row would NOT be
returned for the following query:
SELECT * FROM table WHERE CONTAINS(column, 'Two NEAR One' );
Basically, I want it so that it looks for the words NEAR each
other but only in the order specified in the query. Such that it
returns rows where the first word is NEAR the second word
but also preceeds it as well.
Looking in the documentation, I didn't see how or if this is
possible. Is it? If so, how?
thnx,
Christoph
Christoph,
Unfortunately, this is not possible using the SQL Server 2000 & MSSearch
service implementation of the CONTAINS* or FREETEXT* predicates. While not
documented, the FTS/MSSearch solution defines NEAR as any two words or
phrases that are *near* each other in any order, but within 50 words of each
other. At best you would need to roll your own FTS engine that identity's
the all words, as well as their sequence in the row and then develop an
index that can be queried to get your results. It is doable, but is a
non-trivial effort.
Regards,
John
"Christoph Boget" <jcboget@.yahoo.com> wrote in message
news:#2nl1dNyEHA.1392@.TK2MSFTNGP14.phx.gbl...
> I have the phrase
> 'One Two Three'
> as the value of a column that is indexed for full text searching.
> I run the query
> SELECT * FROM table WHERE CONTAINS(column, 'One NEAR Two' );
> and it returns the row with the above value, which is all well
> and fine. Now, I need it so that the row would NOT be
> returned for the following query:
> SELECT * FROM table WHERE CONTAINS(column, 'Two NEAR One' );
> Basically, I want it so that it looks for the words NEAR each
> other but only in the order specified in the query. Such that it
> returns rows where the first word is NEAR the second word
> but also preceeds it as well.
> Looking in the documentation, I didn't see how or if this is
> possible. Is it? If so, how?
> thnx,
> Christoph
>
|||> other. At best you would need to roll your own FTS engine that identity's
> the all words, as well as their sequence in the row and then develop an
> index that can be queried to get your results. It is doable, but is a
> non-trivial effort.
Actually, I found a really useful work around.
SELECT
*
FROM
table
WHERE
CONTAINS(column, 'One NEAR Two' )
AND
PATINDEX('%One%Two%', column) != 0;
Using PATINDEX() ensures that word one preceeds word two if the
words are NEAR each other.
thnx,
Christoph
|||Christoph,
A most interesting approach to this problem! While this seems to he a very
useful workaround, I've done some quick testing using SQL Server 2000 on
Win2003 with the pubs database and FT-enabled table pub_info and my results
vary somewhat. If you use "moon" and "books" as actual examples for word One
and Two respectively, and then vary the contains and patindex order, I get
the following results:
-- Test #1 with the order changed in the PATINDEX clause changed
SELECT pub_id, pr_info from pub_info -- order of PATINDEX same that order of
CONTAINS
WHERE CONTAINS(pr_info, 'books NEAR moon') AND PATINDEX('%books%moon%',
pr_info) != 0
-- returns: 1 row, pub_id = 0736 - expected results are correct.
/* -- actual text:
pub_id pr_info
-- ---
0736 This is sample text data for New Moon Books, publisher 0736 in the
pubs database. New Moon Books is
(1 row(s) affected)
*/
SELECT pub_id, pr_info from pub_info -- order of PATINDEX different that
order of CONTAINS
WHERE CONTAINS(pr_info, 'books NEAR moon') AND PATINDEX('%moon%books%',
pr_info) != 0
-- returns: 1 row, pub_id = 0736 -- expected results are not correct
-- Test #2 with the order changed in the CONTAINS clause changed
SELECT pub_id, pr_info from pub_info -- order of CONTAINS same that order of
PATINDEX, but reversed from actual order
WHERE CONTAINS(pr_info, 'moon NEAR books') AND PATINDEX('%moon%books%',
pr_info) != 0
-- returns: 1 row, pub_id = 0736 -- expected results are not correct
SELECT pub_id, pr_info from pub_info -- order of CONTAINS different that
order of PATINDEX, but reversed from actual order
WHERE CONTAINS(pr_info, 'books NEAR moon') AND PATINDEX('%moon%books%',
pr_info) != 0
-- returns: 1 row, pub_id = 0736 -- expected results are not correct
I have a feeling that while this approach may be useful, it still may be
dependent upon the actual text and what specific order the searcher is
requesting. Still a very good approach with additional refine might be most
useful!
Regards,
John
"Christoph Boget" <jcboget@.yahoo.com> wrote in message
news:OsRdnxxyEHA.3408@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
identity's
> Actually, I found a really useful work around.
> SELECT
> *
> FROM
> table
> WHERE
> CONTAINS(column, 'One NEAR Two' )
> AND
> PATINDEX('%One%Two%', column) != 0;
> Using PATINDEX() ensures that word one preceeds word two if the
> words are NEAR each other.
> thnx,
> Christoph
>