conversion tools for sql7 databases

is there any tool / utility , online service / websites that will help me convert a MSSQL 7 database to be compatible to MSSQL2k ?
i have enterprise manager for 7 , but when i try to connect to server which has sql2000 ... i cant .
thanx .You need to uninstall Client Tools for SQL 7.0 and install Client Tools for 2K.

Conversion problems between mssql and access

Hello

When I trying to insert data with datatype datetime or smalldatetime from SQL Server into a table in a linked access database I get this error :

Server: Msg 257, Level 16, State 3, Line 1
Implicit conversion from data type smalldatetime to float is not allowed. Use the CONVERT function to run this query.

I dont understand why it try to insert it as a float?!Because from a SQL Server point of view DateTime values ARE float values.

So you cannot do an implict cast but you have to do explictly by using the T-SQL statement "CONVERT"

Look in Books On Line for better help on conversions.|||Hello

I have tried CONVERT. But I dont know to which datatype I should convert my source value.
I have converted to varchar and nvarchar but I still got the same error.|||can you post your code, and specify from what kind of data type you what convert to?|||INSERT INTO LINKEDACCESS...ContactTarget
Select ChangedBy, ChangedDate, PersonIdNo,IdNo,TargetCode,TargetCodeproductCode,T argetCodePotentialCode
from tblContactTarget
WHERE Id NOT IN(select Id from LINKEDACCESS...ContactTarget CT
WHERE CT.Target = tblContactTarget.TargetCode)

ChangedDate is of datatype Datetime in SQL and Date/Time in Access.
This INSERT will trigger the error I wrote about.|||Try to convert it to a varchar. use the CONVERT function so that you can even specify the dateformat you need to have

Conversion from MSSQL 2000 to MSSQL 2005

We have a membership web site that is heavily database driven using MSSQL 2000. We are looking at converting to MSSQL 2005. What is the level of pain in doing this? Will all database calls have to be rewritten, or does Microsoft provide any kind of automatic conversion routine. Thanks in advance.U need to upgrade the database to the next version.

u dont have to rewrite the procs/functions.

Under the watch ful eye of an able SQL-DBA u can do it with out much hassles.

conversion from Access query to mssql query

Hello Guys

I am chaging the connectivity of MSaccess2K to sqlserver
the code is written in vb editor of access
i have established the connection string
but the following query is generating error of
invalid object name

strSQL = SELECT DISTINCT [Sites Union Controls].Description,
[Sites Union Controls].[Rous Reportable Site], [Sites Union Controls].[Type], Samples.SequenceNumber FROM Jobs INNER JOIN ([Sites Union Controls] INNER JOIN Samples ON [Sites Union Controls].SiteSerial = Samples.SiteSerial) ON Jobs.JobSerial = Samples.JobSerial
WHERE ((Jobs.JobSerial) = " & intJobSerial & ") ORDER BY Samples.SequenceNumber

I am getting an error invalid object name sites union controls

can't we do union of two tables as above in mssql
Please Help

Thanks In AdvanceI'm sure some one will correct me if Im wrong but I dont think this query will ever work in SQL Server, it does look like something that might run in access though.

Ive had problems like this before, Access loves adding in brackets () that just get in the way and confuse things in SQL Server. It also seams to list all the tables then join them in the FROM statement which is not s SQL thing either.

I think your also going to have a problem with the WHERE clause, namely the part " & intJobSerial & " reefers to a variable in Access. Even if you have created the variable in SQL Server the syntax is still wrong

Your query doesnt look like a union query, but a standard select query gone a bit wrong in the FROM part. Your query should look something like

DECLARE @.intJobSerial VARCHAR(50) -- this creates the variable as a 50 character text field, don't need this bit if youve done it already

SET @.intJobSerial = 'XXX' -- this sets the variable to XXX, don't need this bit if youve done it already

SELECT DISTINCT [Sites Union Controls].[Description],[Sites Union Controls].[Rous Reportable Site], [Sites Union Controls].Type, Samples.SequenceNumber

FROM [Sites Union Controls]
INNER JOIN Samples
ON [Sites Union Controls].SiteSerial = Samples.SiteSerial
INNER JOIN Jobs
ON Jobs.JobSerial = Samples.JobSerial

WHERE Jobs.JobSerial = @.intJobSerial

ORDER BY Samples.SequenceNumber

The invalid object sites union controls in actually the table used the query, Im guessing its because the FROM Part was all messed up at it was the first thing it came across after it went wrong.

Hope this helps|||Hello,

Trying by used the next name Sites_Union_Controls because I'm not sure that you can have a name with blanc characters

Good luck

Sylvie

Quote:

Originally Posted by aakash

Hello Guys

I am chaging the connectivity of MSaccess2K to sqlserver
the code is written in vb editor of access
i have established the connection string
but the following query is generating error of
invalid object name

strSQL = SELECT DISTINCT [Sites Union Controls].Description,
[Sites Union Controls].[Rous Reportable Site], [Sites Union Controls].[Type], Samples.SequenceNumber FROM Jobs INNER JOIN ([Sites Union Controls] INNER JOIN Samples ON [Sites Union Controls].SiteSerial = Samples.SiteSerial) ON Jobs.JobSerial = Samples.JobSerial
WHERE ((Jobs.JobSerial) = " & intJobSerial & ") ORDER BY Samples.SequenceNumber

I am getting an error invalid object name sites union controls

can't we do union of two tables as above in mssql
Please Help

Thanks In Advance

|||Hey, a couple of points...
SQL uses + for concatenation so you need to change your where statement to
((Jobs.JobSerial) = " + @.intJobSerial + ")

JohnK is right, if intJobSerial is a local variable then it needs to be declared and it must begin with an @..

There is nothing wrong with the FROM statement, it's a little unusual to delay the two ON clauses at the end but this gives a different result set because nulls in the 3rd table, in your query SAMPLES, can be handled differently this way. It's more effective if your mixing Left Outer and Inner Joins, however.

The biggest thing I see is that the permissions on the SQL table [Sites Union Controls] may be different than what you expect. You should probably be using a full reference to it as [database].[owner].[table] to at least eliminate the possibility that your error is a security setup problem.

Tom|||Sites Union Controls, was query in old ms access project which was connected to ms access database i am changing connectivity to ms sql 2000 ,none of the above solutions seem to be working ,
please help

CONTAINS simple terms search acting like prefix search.

MSSQL 2000 sp3a, on Windows 2003.
I will use examples from BOL. I am searching with a CONTAINS like this:
WHERE CONTAINS(ProductName, ' "sasquatch ale" OR "steeleye stout" ')
and am receiving results like 'sasquatch's ale' and 'steeleye's stout'.
Plurals.
Strange. I have performed a full population and this continues to
happen.
Charlie
Those aren't plurals - those are possessives.
Try "sasquatchs ale" OR "steeleyes stout"
"sqldba" <sqldba@.comcast.net> wrote in message
news:1129915220.977952.192440@.f14g2000cwb.googlegr oups.com...
> MSSQL 2000 sp3a, on Windows 2003.
> I will use examples from BOL. I am searching with a CONTAINS like this:
> WHERE CONTAINS(ProductName, ' "sasquatch ale" OR "steeleye stout" ')
> and am receiving results like 'sasquatch's ale' and 'steeleye's stout'.
> Plurals.
> Strange. I have performed a full population and this continues to
> happen.
> Charlie
>
|||Plurals - Possessives. Wither way I ask for the phrase "sasquatch ale"
and I get 'sasquatch's ale' . Why?
|||Pardon the spelling the last message. I think I may have resolved the issue.
I changed from an English[United States] Language for Word Breaker to Neutral
and it seems to work now. Not sure if this is going to introduce any new
anomalies or not but....we just testing now so bring them on.
"sqldba" wrote:

> Plurals - Possessives. Wither way I ask for the phrase "sasquatch ale"
> and I get 'sasquatch's ale' . Why?
>