Thursday, March 22, 2012
Converting (too big)Raw into Hex
Here at my work(in Germany) i have a big problem:
Once a week there is a sqlscript, which should run and store data to a txt file.
There is a tablle in which imagedata is stored in RAW format(that one with small boxes, Qmarks and dots...).
Now i want to export it to the txtfile as HEX, so i can decode it by php further.
With the thumbnail, also stores there everything is fine, the SQL looks that way:
SELECt hex(thumbnail) FROM picTable where id_pic=54985
The Data is then displayed as HEX. But the standart picture is to big. When i use the statement:
SELECt hex(bigpic) FROM picTable where id_pic=54985
i got the error message:
An error occurred while running the query.
The data type, length, or value of argument 1 of HEX is invalid.
(SQL code = -171, SQL state = 42815)
After some research i found that the maximum lenght is 16 336 bytes for the HEX-operation. The picture is nearly 30 000 bytes... :shocked:
Have anyone an idea how to fix that?
I am not able here to accsess to the database direct...
I have been working for days on it.Uahh, wrong forum. Can anyone move this to SQL?|||the SQL forum is for standard SQL
i'm guessing you want this moved to the SQL Server forum
:)|||Still not solved...|||What is this "hex" function you are referring to? I could not find any reference to it in BOL (though it certainly would be a handy one to have!).
Regardless, assuming that there actually IS such an animal, can you consider breaking up the input data by sub-sections under your maximum size in the hex() function?
Such as using SUBSTRING ( expression , start , length )?
And then loop until the whole RAW data block is processed completely in bite-size chunks?
Tuesday, March 20, 2012
Converted Access database gives problems due to field names
I have been working with asp.net against an Access database (created by someone else several years ago). Now I have to move the database to run under MSSQL Server. I successfully updated it via the Access migration tool, and it's running fine under MSDE.
However, many of the fields in one table are 'named' as 'numbers' - eg 101, 102, 34 etc. Therefore SQL commands such as:
update Mytable set 101 = "test" where ID = 2
fail with Incorrect syntax near '101'.
I would never set up such a database structure myself, but I am stuck with it. Is there any way to make MSSQL commands work with this sort of naming?
Thanks for any help you can give.
LeeYou could try:
update Mytable set [101] = "test" where ID = 2
I have not tried this with fields that are all numbers, but I expect it should work.sqlsql
Convert varchar to uniqueidentifier
SELECT PROGRAM_NAME FROM master..sysprocesses WHERE program_name LIKE
'SQLAgent - TSQL JobStep (Job %'
The result might be something like this:
SQLAgent - TSQL JobStep (Job 0x9BD0927CE9086241B582AAAAD7D3B86D : Step
1)
When I want to have more info about that particular job, I run this
query:
SELECT * FROM msdb..sysjobs WHERE job_id =
0x9BD0927CE9086241B582AAAAD7D3B86D
Now how do I combine these two queries? The first version you might
suggest is:
SELECT *
FROM msdb..sysjobs
WHERE job_id IN
(SELECT SUBSTRING(program_name,30,34)
FROM master..sysprocesses
WHERE program_name LIKE 'SQLAgent - TSQL JobStep (Job %')
This gives the result
Syntax error converting from a character string to uniqueidentifier.
Can you help me with this? How to convert the result of the subquery to
uniqueidentifier? I didn't succeed to use the usual convert function
CONVERT(UNIQUEIDENTIFIER,SUBSTRING(progr
am_name,30,34)).
MarkTry this (untested):
SELECT *
FROM msdb..sysjobs
WHERE cast(job_id as nvarchar(128)) IN
(SELECT cast(SUBSTRING(program_name,30,34) as nvarchar(128))
FROM master..sysprocesses
WHERE program_name LIKE 'SQLAgent - TSQL JobStep (Job %')
ML|||"ML" <ML@.discussions.microsoft.com> wrote in message
news:9FB96207-78A4-4566-9AEC-8294830AABAB@.microsoft.com...
> Try this (untested):
> SELECT *
> FROM msdb..sysjobs
> WHERE cast(job_id as nvarchar(128)) IN
> (SELECT cast(SUBSTRING(program_name,30,34) as nvarchar(128))
> FROM master..sysprocesses
> WHERE program_name LIKE 'SQLAgent - TSQL JobStep (Job %')
>
> ML
Thanks, but that doesn't work either - I tested. But at least that doesn't
give any errors, just an empty result.
However, I found out, that this works. Quite ugly one, but it works.
SELECT *
FROM msdb..sysjobs
WHERE job_id IN
(SELECT SUBSTRING(program_name,38,2) +
SUBSTRING(program_name,36,2) +
SUBSTRING(program_name,34,2) +
SUBSTRING(program_name,32,2) + '-' +
SUBSTRING(program_name,42,2) +
SUBSTRING(program_name,40,2) + '-' +
SUBSTRING(program_name,46,2) +
SUBSTRING(program_name,44,2) + '-' +
SUBSTRING(program_name,48,4) + '-' +
SUBSTRING(program_name,52,12)
FROM master..sysprocesses
WHERE program_name LIKE 'SQLAgent - TSQL JobStep (Job %')
Mark
Thursday, March 8, 2012
Convert Sting to Date
I am a Newbie to SQL and am therefore sorry if this is a stupid
question
I am trying to run the following command
SELECT OriginationNumber, DestinationNumber, StartTime, State, Duration
FROM dbo.IpPbxCDR
WHERE OriginationNumber Like 'MMColParam' or DestinationNumber Like
'MMColParam' and StartTime Between '%MMColParam2%' AND
'%MMColParam3%'
I am passing the variables MMColParam from a Web page but I always get
the following error when I try to run the command
Error converting string Datetime from Charactor String.
I have checked the DB and the value is defined as DateTime entry
here is an example of Table entry 16/11/2005 18:27:24
Can anyone help me with this?
Many Thanks
IanCrumb (rowan.ian@.gmail.com) writes:
> I am a Newbie to SQL and am therefore sorry if this is a stupid
> question
> I am trying to run the following command
> SELECT OriginationNumber, DestinationNumber, StartTime, State, Duration
> FROM dbo.IpPbxCDR
> WHERE OriginationNumber Like 'MMColParam' or DestinationNumber Like
> 'MMColParam' and StartTime Between '%MMColParam2%' AND
> '%MMColParam3%'
> I am passing the variables MMColParam from a Web page but I always get
> the following error when I try to run the command
> Error converting string Datetime from Charactor String.
> I have checked the DB and the value is defined as DateTime entry
> here is an example of Table entry 16/11/2005 18:27:24
Datetime values are not strings, but binary values. Strings can be
implicitly converted to dates, but there is no datetime interpretation
of the string '%MMColParam2%'. I don't know what you intended the
% to mean, but they don't mean anything to SQL Server in this context.
The same goes for
OriginationNumber Like 'MMColParam'
This will give a match if you have an origination number with the exact
of the strnig 'MMColParam'.
Maybe you meant something like:
SELECT OriginationNumber, DestinationNumber, StartTime, State, Duration
FROM dbo.IpPbxCDR
WHERE OriginationNumber Like @.MMColParam or
DestinationNumber Like @.MMColParam and
StartTime Between @.MMColParam2 AND @.MMColParam3
Although, you may be thinking of MMcolParam as parameters in your web
page. In such you need to pass them as parameters to SQL.
By the way, what is the intended logic? The above will not consider
StartTime if there is a match in OriginationNumber, only if there is
a match in DestinationNumber.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Many Thanks for the reply, I am one step nearer as this now runs
without errors:)
What I am trying to do is as follows:
I have a table that contains all the phone calls in and out, (Called
and Destination numbers). I am trying to create a asp page that will
search the db for a telephone number (MMColParam) in either called or
destinaition fields but only between two dates (MMColParam1 and 2)
The information you gave me helped me return a result but it returns
all calls and not just the ones between the dates specified.
Do you know what I am doing wrong in the SQL command?
Thanks Again
Ian|||Crumb (rowan.ian@.gmail.com) writes:
> Many Thanks for the reply, I am one step nearer as this now runs
> without errors:)
> What I am trying to do is as follows:
> I have a table that contains all the phone calls in and out, (Called
> and Destination numbers). I am trying to create a asp page that will
> search the db for a telephone number (MMColParam) in either called or
> destinaition fields but only between two dates (MMColParam1 and 2)
> The information you gave me helped me return a result but it returns
> all calls and not just the ones between the dates specified.
Yes, I made a remark on this flaw, but I could not be sure what you wanted.
AND binds harder than OR, so you must use parentheses:
WHERE (OriginationNumber Like @.MMColParam or
DestinationNumber Like @.MMColParam) and
StartTime Between @.MMColParam2 AND @.MMColParam3
Else you get all rows with a matching Originationumber, no matter
the start time.
Think of OR as + and AND as *.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi Again,
Thanks so much for the advice but if possible I would like to pick your
brains once more.
The search still does not work, now I get nothing back because of the
date search, even if I run the date search on its own I still get no
returns
Do I need to convert the MMColParam2 and 3 to another data type?
The value of these variables is in dd/mm/yyyy format as in the data in
my DB
Many Thanks again
Ian|||Stab in the dark but, have you tried a SET DATEFORMAT DMY at the start
of your query?|||Always use Universal format yyyy-mm-dd to avoid conflict with local
settings
Also refer this
http://www.sql-server-performance.com/fk_datetime.asp
Madhivanan|||Crumb (rowan.ian@.gmail.com) writes:
> Thanks so much for the advice but if possible I would like to pick your
> brains once more.
> The search still does not work, now I get nothing back because of the
> date search, even if I run the date search on its own I still get no
> returns
> Do I need to convert the MMColParam2 and 3 to another data type?
> The value of these variables is in dd/mm/yyyy format as in the data in
> my DB
No, the data in your DB is in binary format, assuming that you store the
dates in datetime. (If you store them as strings, BETWEEN is obviously
not going to work well. Not with that format.)
Since the original snippet you posted was a bit of nonsense, it is not
clear to how exactly you get the values of these variables into the SQL
statement. In fact, I don't even know if they are T-SQL variables, or
client-side variables. Could you post that you have now, including where
you declare and set these MMColParam things?
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
Convert Sting to Date
I am a Newbie to SQL and am therefore sorry if this is a stupid
question
I am trying to run the following command
SELECT OriginationNumber, DestinationNumber, StartTime, State, Duration
FROM dbo.IpPbxCDR
WHERE OriginationNumber Like 'MMColParam' or DestinationNumber Like
'MMColParam' and StartTime Between '%MMColParam2%' AND
'%MMColParam3%'
I am passing the variables MMColParam from a Web page but I always get
the following error when I try to run the command
Error converting string Datetime from Charactor String.
I have checked the DB and the value is defined as DateTime entry
here is an example of Table entry 16/11/2005 18:27:24
Can anyone help me with this?
Many Thanks
IanSee if these help:
http://www.karaszi.com/SQLServer/info_datetime.asp
Guide to Datetimes
http://www.sqlservercentral.com/col...sqldatetime.asp
Datetimes
http://www.murach.com/books/sqls/article.htm
Datetime Searching
Andrew J. Kelly SQL MVP
"Crumb" <rowan.ian@.gmail.com> wrote in message
news:1141251396.268280.282650@.p10g2000cwp.googlegroups.com...
> Hi,
> I am a Newbie to SQL and am therefore sorry if this is a stupid
> question
>
> I am trying to run the following command
>
> SELECT OriginationNumber, DestinationNumber, StartTime, State, Duration
> FROM dbo.IpPbxCDR
> WHERE OriginationNumber Like 'MMColParam' or DestinationNumber Like
> 'MMColParam' and StartTime Between '%MMColParam2%' AND
> '%MMColParam3%'
>
> I am passing the variables MMColParam from a Web page but I always get
> the following error when I try to run the command
>
> Error converting string Datetime from Charactor String.
>
> I have checked the DB and the value is defined as DateTime entry
>
> here is an example of Table entry 16/11/2005 18:27:24
>
> Can anyone help me with this?
>
> Many Thanks
>
> Ian
>
Convert Sting to Date
I am a Newbie to SQL and am therefore sorry if this is a stupid
question
I am trying to run the following command
SELECT OriginationNumber, DestinationNumber, StartTime, State, Duration
FROM dbo.IpPbxCDR
WHERE OriginationNumber Like 'MMColParam' or DestinationNumber Like
'MMColParam' and StartTime Between '%MMColParam2%' AND
'%MMColParam3%'
I am passing the variables MMColParam from a Web page but I always get
the following error when I try to run the command
Error converting string Datetime from Charactor String.
I have checked the DB and the value is defined as DateTime entry
here is an example of Table entry 16/11/2005 18:27:24
Can anyone help me with this?
Many Thanks
Ian
See if these help:
http://www.karaszi.com/SQLServer/info_datetime.asp
Guide to Datetimes
http://www.sqlservercentral.com/colu...qldatetime.asp
Datetimes
http://www.murach.com/books/sqls/article.htm
Datetime Searching
Andrew J. Kelly SQL MVP
"Crumb" <rowan.ian@.gmail.com> wrote in message
news:1141251396.268280.282650@.p10g2000cwp.googlegr oups.com...
> Hi,
> I am a Newbie to SQL and am therefore sorry if this is a stupid
> question
>
> I am trying to run the following command
>
> SELECT OriginationNumber, DestinationNumber, StartTime, State, Duration
> FROM dbo.IpPbxCDR
> WHERE OriginationNumber Like 'MMColParam' or DestinationNumber Like
> 'MMColParam' and StartTime Between '%MMColParam2%' AND
> '%MMColParam3%'
>
> I am passing the variables MMColParam from a Web page but I always get
> the following error when I try to run the command
>
> Error converting string Datetime from Charactor String.
>
> I have checked the DB and the value is defined as DateTime entry
>
> here is an example of Table entry 16/11/2005 18:27:24
>
> Can anyone help me with this?
>
> Many Thanks
>
> Ian
>
Convert Sting to Date
I am a Newbie to SQL and am therefore sorry if this is a stupid
question
I am trying to run the following command
SELECT OriginationNumber, DestinationNumber, StartTime, State, Duration
FROM dbo.IpPbxCDR
WHERE OriginationNumber Like 'MMColParam' or DestinationNumber Like
'MMColParam' and StartTime Between '%MMColParam2%' AND
'%MMColParam3%'
I am passing the variables MMColParam from a Web page but I always get
the following error when I try to run the command
Error converting string Datetime from Charactor String.
I have checked the DB and the value is defined as DateTime entry
here is an example of Table entry 16/11/2005 18:27:24
Can anyone help me with this?
Many Thanks
IanSee if these help:
http://www.karaszi.com/SQLServer/info_datetime.asp
Guide to Datetimes
http://www.sqlservercentral.com/columnists/bsyverson/sqldatetime.asp
Datetimes
http://www.murach.com/books/sqls/article.htm
Datetime Searching
--
Andrew J. Kelly SQL MVP
"Crumb" <rowan.ian@.gmail.com> wrote in message
news:1141251396.268280.282650@.p10g2000cwp.googlegroups.com...
> Hi,
> I am a Newbie to SQL and am therefore sorry if this is a stupid
> question
>
> I am trying to run the following command
>
> SELECT OriginationNumber, DestinationNumber, StartTime, State, Duration
> FROM dbo.IpPbxCDR
> WHERE OriginationNumber Like 'MMColParam' or DestinationNumber Like
> 'MMColParam' and StartTime Between '%MMColParam2%' AND
> '%MMColParam3%'
>
> I am passing the variables MMColParam from a Web page but I always get
> the following error when I try to run the command
>
> Error converting string Datetime from Charactor String.
>
> I have checked the DB and the value is defined as DateTime entry
>
> here is an example of Table entry 16/11/2005 18:27:24
>
> Can anyone help me with this?
>
> Many Thanks
>
> Ian
>
Wednesday, March 7, 2012
convert SQL 2005 back to SQL 2000
Our new test environment running MS SQL 2005. We have finished an
application and need to migrate that to a live server which run SQL 2000.
I have tried enough but with no luck. How can I convert our new SQL 2005
back to 2000 server? The new database contain table schema and quiet some
initial data already. How should I do this?
regards,
Guoqi Zheng
http://www.ureader.com"guoqi zheng" <no@.sorry.com> wrote in message
news:2ea036c11bb84a0eba336a26d25d5624@.ur
eader.com...
> Dear Sir,
> Our new test environment running MS SQL 2005. We have finished an
> application and need to migrate that to a live server which run SQL 2000.
> I have tried enough but with no luck. How can I convert our new SQL 2005
> back to 2000 server? The new database contain table schema and quiet some
> initial data already. How should I do this?
There's no real easy way to do this.
Best bet is to script out the schema, bulk copy out the data and rebuild on
the new box.
> regards,
> Guoqi Zheng
> http://www.ureader.com|||You have to script out all of the objects and then transfer the data using
either BCP, DTS, or SSIS. A 2005 database can not be loaded into 2000 and
it can NOT be downgraded.
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"guoqi zheng" <no@.sorry.com> wrote in message
news:2ea036c11bb84a0eba336a26d25d5624@.ur
eader.com...
> Dear Sir,
> Our new test environment running MS SQL 2005. We have finished an
> application and need to migrate that to a live server which run SQL 2000.
> I have tried enough but with no luck. How can I convert our new SQL 2005
> back to 2000 server? The new database contain table schema and quiet some
> initial data already. How should I do this?
> regards,
> Guoqi Zheng
> http://www.ureader.com|||It can be downgraded by setting the DB options to 80 compatibility level IF
the DB doesn't use any of the newer 2005 features.
However, I think it is a mistake to not just upgrade the other server to
2005. Upgrading takes so much work, and if the DB is already working in
2005, then why go through all the hassle downgrading and upgrading when that
same fundamental expense can be borne one time to just pay for the 2005
license.
"Michael Hotek" <mike@.solidqualitylearning.com> wrote in message
news:ec7TGrEYGHA.3328@.TK2MSFTNGP02.phx.gbl...
> You have to script out all of the objects and then transfer the data using
> either BCP, DTS, or SSIS. A 2005 database can not be loaded into 2000 and
> it can NOT be downgraded.
> --
> Mike
> http://www.solidqualitylearning.com
> Disclaimer: This communication is an original work and represents my sole
> views on the subject. It does not represent the views of any other person
> or entity either by inference or direct reference.
>
> "guoqi zheng" <no@.sorry.com> wrote in message
> news:2ea036c11bb84a0eba336a26d25d5624@.ur
eader.com...
>|||Changing the database compatibility level will not allow Guoqi Zheng to go
back to SQL 2000. The on-disk database format will still be SQL 2005 and
will not be recognized by older versions of SQL Server.
Hope this helps.
Dan Guzman
SQL Server MVP
"AFN" <replywithingroup@.notreal.com> wrote in message
news:EL90g.3630$3W1.59@.tornado.socal.rr.com...
> It can be downgraded by setting the DB options to 80 compatibility level
> IF the DB doesn't use any of the newer 2005 features.
> However, I think it is a mistake to not just upgrade the other server to
> 2005. Upgrading takes so much work, and if the DB is already working in
> 2005, then why go through all the hassle downgrading and upgrading when
> that same fundamental expense can be borne one time to just pay for the
> 2005 license.
>
> "Michael Hotek" <mike@.solidqualitylearning.com> wrote in message
> news:ec7TGrEYGHA.3328@.TK2MSFTNGP02.phx.gbl...
>|||If you're not using any SQL 2005 specific features, you can use
Transactional Replication to keep a warm SQL 2000 fallback option open.
Regards,
Greg Linwood
SQL Server MVP
"guoqi zheng" <no@.sorry.com> wrote in message
news:2ea036c11bb84a0eba336a26d25d5624@.ur
eader.com...
> Dear Sir,
> Our new test environment running MS SQL 2005. We have finished an
> application and need to migrate that to a live server which run SQL 2000.
> I have tried enough but with no luck. How can I convert our new SQL 2005
> back to 2000 server? The new database contain table schema and quiet some
> initial data already. How should I do this?
> regards,
> Guoqi Zheng
> http://www.ureader.com
convert SQL 2005 back to SQL 2000
Our new test environment running MS SQL 2005. We have finished an
application and need to migrate that to a live server which run SQL 2000.
I have tried enough but with no luck. How can I convert our new SQL 2005
back to 2000 server? The new database contain table schema and quiet some
initial data already. How should I do this?
regards,
Guoqi Zheng
http://www.ureader.com"guoqi zheng" <no@.sorry.com> wrote in message
news:2ea036c11bb84a0eba336a26d25d5624@.ureader.com...
> Dear Sir,
> Our new test environment running MS SQL 2005. We have finished an
> application and need to migrate that to a live server which run SQL 2000.
> I have tried enough but with no luck. How can I convert our new SQL 2005
> back to 2000 server? The new database contain table schema and quiet some
> initial data already. How should I do this?
There's no real easy way to do this.
Best bet is to script out the schema, bulk copy out the data and rebuild on
the new box.
> regards,
> Guoqi Zheng
> http://www.ureader.com|||You have to script out all of the objects and then transfer the data using
either BCP, DTS, or SSIS. A 2005 database can not be loaded into 2000 and
it can NOT be downgraded.
--
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"guoqi zheng" <no@.sorry.com> wrote in message
news:2ea036c11bb84a0eba336a26d25d5624@.ureader.com...
> Dear Sir,
> Our new test environment running MS SQL 2005. We have finished an
> application and need to migrate that to a live server which run SQL 2000.
> I have tried enough but with no luck. How can I convert our new SQL 2005
> back to 2000 server? The new database contain table schema and quiet some
> initial data already. How should I do this?
> regards,
> Guoqi Zheng
> http://www.ureader.com|||It can be downgraded by setting the DB options to 80 compatibility level IF
the DB doesn't use any of the newer 2005 features.
However, I think it is a mistake to not just upgrade the other server to
2005. Upgrading takes so much work, and if the DB is already working in
2005, then why go through all the hassle downgrading and upgrading when that
same fundamental expense can be borne one time to just pay for the 2005
license.
"Michael Hotek" <mike@.solidqualitylearning.com> wrote in message
news:ec7TGrEYGHA.3328@.TK2MSFTNGP02.phx.gbl...
> You have to script out all of the objects and then transfer the data using
> either BCP, DTS, or SSIS. A 2005 database can not be loaded into 2000 and
> it can NOT be downgraded.
> --
> Mike
> http://www.solidqualitylearning.com
> Disclaimer: This communication is an original work and represents my sole
> views on the subject. It does not represent the views of any other person
> or entity either by inference or direct reference.
>
> "guoqi zheng" <no@.sorry.com> wrote in message
> news:2ea036c11bb84a0eba336a26d25d5624@.ureader.com...
>> Dear Sir,
>> Our new test environment running MS SQL 2005. We have finished an
>> application and need to migrate that to a live server which run SQL 2000.
>> I have tried enough but with no luck. How can I convert our new SQL 2005
>> back to 2000 server? The new database contain table schema and quiet some
>> initial data already. How should I do this?
>> regards,
>> Guoqi Zheng
>> http://www.ureader.com
>|||Changing the database compatibility level will not allow Guoqi Zheng to go
back to SQL 2000. The on-disk database format will still be SQL 2005 and
will not be recognized by older versions of SQL Server.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"AFN" <replywithingroup@.notreal.com> wrote in message
news:EL90g.3630$3W1.59@.tornado.socal.rr.com...
> It can be downgraded by setting the DB options to 80 compatibility level
> IF the DB doesn't use any of the newer 2005 features.
> However, I think it is a mistake to not just upgrade the other server to
> 2005. Upgrading takes so much work, and if the DB is already working in
> 2005, then why go through all the hassle downgrading and upgrading when
> that same fundamental expense can be borne one time to just pay for the
> 2005 license.
>
> "Michael Hotek" <mike@.solidqualitylearning.com> wrote in message
> news:ec7TGrEYGHA.3328@.TK2MSFTNGP02.phx.gbl...
>> You have to script out all of the objects and then transfer the data
>> using either BCP, DTS, or SSIS. A 2005 database can not be loaded into
>> 2000 and it can NOT be downgraded.
>> --
>> Mike
>> http://www.solidqualitylearning.com
>> Disclaimer: This communication is an original work and represents my sole
>> views on the subject. It does not represent the views of any other
>> person or entity either by inference or direct reference.
>>
>> "guoqi zheng" <no@.sorry.com> wrote in message
>> news:2ea036c11bb84a0eba336a26d25d5624@.ureader.com...
>> Dear Sir,
>> Our new test environment running MS SQL 2005. We have finished an
>> application and need to migrate that to a live server which run SQL
>> 2000.
>> I have tried enough but with no luck. How can I convert our new SQL 2005
>> back to 2000 server? The new database contain table schema and quiet
>> some
>> initial data already. How should I do this?
>> regards,
>> Guoqi Zheng
>> http://www.ureader.com
>>
>|||If you're not using any SQL 2005 specific features, you can use
Transactional Replication to keep a warm SQL 2000 fallback option open.
Regards,
Greg Linwood
SQL Server MVP
"guoqi zheng" <no@.sorry.com> wrote in message
news:2ea036c11bb84a0eba336a26d25d5624@.ureader.com...
> Dear Sir,
> Our new test environment running MS SQL 2005. We have finished an
> application and need to migrate that to a live server which run SQL 2000.
> I have tried enough but with no luck. How can I convert our new SQL 2005
> back to 2000 server? The new database contain table schema and quiet some
> initial data already. How should I do this?
> regards,
> Guoqi Zheng
> http://www.ureader.com
Friday, February 24, 2012
Convert MySQL script to SQL Server script
I have a few MySQL scripts that I need to run in SQL Server. However the
syntax is just slightly different and I was wondering if there is a tool out
there that can quickly convert these scripts so that they will work with SQL
Server?
Anyone have any ideas (other than manually converting them)?
Thanks,
Wes
Hi,
There is one possible way how to do it, however, it's not as direct as
you may wish.
Let me outline what to do. You will have to run the scripts "above"
MySQL database and reverse engineer them in CASE Studio 2 (a database
modeling tool) that allows you to do the conversion to MSSQL in just a
few clicks. Then you can generate a script compatible with MSSQL.
You can try to do it in the CASE Studio 2 demo version, which is for
free >
http://www.casestudio.com/enu/download.aspx.
To learn more what this product is about, please feel free to visit:
http://www.casestudio.com/enu/default.aspx.
Finally, I think this could help you until somebody else gives you
better advice.
Good luck.
Vladka
Wes <Wes@.discussions.microsoft.com> wrote in message news:<2C048E65-7A06-49C1-BE96-8A5F3716E4EF@.microsoft.com>...
> Hi there,
> I have a few MySQL scripts that I need to run in SQL Server. However the
> syntax is just slightly different and I was wondering if there is a tool out
> there that can quickly convert these scripts so that they will work with SQL
> Server?
> Anyone have any ideas (other than manually converting them)?
> Thanks,
> Wes
|||Hi Vladka,
I downloaded, installed and tried CASE Studio 2, but to no advantage. It
will not allow me to import or open an existing script. I tried everything I
could think of, but could not get it to allow me to use my existing script.
Thanks for trying though!
Wes
"Vaclav Frolik" wrote:
> Hi,
> There is one possible way how to do it, however, it's not as direct as
> you may wish.
> Let me outline what to do. You will have to run the scripts "above"
> MySQL database and reverse engineer them in CASE Studio 2 (a database
> modeling tool) that allows you to do the conversion to MSSQL in just a
> few clicks. Then you can generate a script compatible with MSSQL.
> You can try to do it in the CASE Studio 2 demo version, which is for
> free >
> http://www.casestudio.com/enu/download.aspx.
> To learn more what this product is about, please feel free to visit:
> http://www.casestudio.com/enu/default.aspx.
> Finally, I think this could help you until somebody else gives you
> better advice.
> Good luck.
> Vladka
>
> Wes <Wes@.discussions.microsoft.com> wrote in message news:<2C048E65-7A06-49C1-BE96-8A5F3716E4EF@.microsoft.com>...
>
|||Hi Wes,
I'm sorry for the inaccurate formulation. You're right, CASE Studio 2
(CS2) can load only the physical database, not SQL scripts. (As I
wrote - it wasn't a direct way... ) However, I'd like to specify my
previous message.
1.Please, try to generate the script physically in MySQL database.
2.Then reverse engineer this database in CS2 again. For the details
concerning reverse engineering, please have a look at the Help file
Contents folder > Reverse engineering.
3.Furthermore, you will have to do the database conversion. Please, see
the Help file of CS2 > Contents
folder > Model conversion for the details.
4.Finally, you'll be allowed to generate the scripts of the converted
database automatically. Please, see the Help file again > Contents
folder > Databases > MS SQL > Generating SQL scripts.
Well, this will certainly help you. Good luck!
Here are the needed links once again:
http://www.casestudio.com/enu/default.aspx
http://www.casestudio.com/enu/download.aspx
Let me also add a link where you will find some useful movies that could
help you: http://www.casestudio.com/enu/dmovies.aspx
I look forward to your reply.
Vladka
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
Convert MySQL script to SQL Server script
I have a few MySQL scripts that I need to run in SQL Server. However the
syntax is just slightly different and I was wondering if there is a tool out
there that can quickly convert these scripts so that they will work with SQL
Server?
Anyone have any ideas (other than manually converting them)?
Thanks,
Wes"Wes Graves" <wesgraves@.hotmail.com> wrote in message news:<XSbfd.27529$Pl.741@.pd7tw1no>...
> Hi there,
> I have a few MySQL scripts that I need to run in SQL Server. However the
> syntax is just slightly different and I was wondering if there is a tool out
> there that can quickly convert these scripts so that they will work with SQL
> Server?
> Anyone have any ideas (other than manually converting them)?
> Thanks,
> Wes
I think, you can start mysql in ANSI mode and generate the scripts
with this standard which is ready to use in SQL server with no
modification. see the following page for more details:
http://dev.mysql.com/doc/mysql/en/ANSI_mode.html|||Thanks, I'll give that a try.
Wes
"Muharram Mansoorizadeh" <muharram_m@.yahoo.com> wrote in message
news:a85d0c0e.0410261616.2ce82b84@.posting.google.c om...
> "Wes Graves" <wesgraves@.hotmail.com> wrote in message
> news:<XSbfd.27529$Pl.741@.pd7tw1no>...
>> Hi there,
>>
>> I have a few MySQL scripts that I need to run in SQL Server. However the
>> syntax is just slightly different and I was wondering if there is a tool
>> out
>> there that can quickly convert these scripts so that they will work with
>> SQL
>> Server?
>>
>> Anyone have any ideas (other than manually converting them)?
>>
>> Thanks,
>>
>> Wes
> I think, you can start mysql in ANSI mode and generate the scripts
> with this standard which is ready to use in SQL server with no
> modification. see the following page for more details:
> http://dev.mysql.com/doc/mysql/en/ANSI_mode.html
Tuesday, February 14, 2012
Convert getdate to week and day of week
Please help..Look up the DATEPART() function.
CONVERT function
insert into Inventory (Item_ID,
Inventory_ID,Perishable_indicator, Perishable_Date,
Department_ID, Category, Description, Unit_of_Measure,
Quantity_on_Hand, Unit_Cost, Master_Pack_Quantity,
Supplier_ID)
values
('23456','8263458', 'Yes', 'Feb 25 2005
12:00AM','695', 'Produce', 'Apple', 'per lb', '6000',
'0.99', '10000', '893')
Server: Msg 260, Level 16, State 1, Line 1
Disallowed implicit conversion from data type varchar
to data type money, table 'Final
Project.dbo.Inventory', column 'Unit_Cost'. Use the
CONVERT function to run this query.Have you tried looking up CAST and CONVERT in Books Online?
And why are you submitting your numeric values as strings in the first place?|||I found out something. I was not suppose to enter numeric values in quotations|||I just took the numerics out of quotations but now I get this error
insert into Inventory (Item_ID, Inventory_ID, Perishable_indicator, Perishable_Date, Department_ID, Category, Description, Unit_of_Measure, Quantity_on_Hand, Unit_Cost, Master_Pack_Quantity, Supplier_ID)
values
('23456','8263458', 'Yes', 'Feb 25 2005 12:00AM','695', 'Produce', 'Apple', 'per lb', '6000', 0.99, '10000', '893')
Server: Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint 'PK_Inventory'. Cannot insert duplicate key in object 'Inventory'.
The statement has been terminated.|||Pretty high probability is that you've already got a row with an Item_id value of 23456. If there is a primary key defined, there can only be one row with a given value for the PK.
-PatP|||oh ok, that makes sense. i entered in this info via table previously but now i'm just trying to put that info into code.|||so.....
update inventory set unitcost = .99 where item_id = 23456
:eek:
Sunday, February 12, 2012
Convert EXISTS to JOIN or? Reformulated!
Thanks for the followups. I think I was too unclear: The
function was not the focus, more an appendix to the setup.
So run the setup and the function.
My question was how to fix the query so that I can access
the matching row that makes the EXISTS true. Either via a
rewrite of the EXISTS condition using a join (which I have
attempted but ultimately failed) or something else, like
TOP 1 on the combined xfers / intervals set.
This query returns the correct result, yet I do not have
access to the (first found) matching row in the EXISTS:
-- Desired rows, yet I cannot access the matching row in
the EXISTS test
SELECT
nodes.nodeid, nodes.nodename,
source_nodeid, volume
FROM nodes
LEFT OUTER JOIN xfers
ON target_nodeid = nodes.nodeid AND EXISTS
( SELECT *
FROM intervals sourceIntervals
JOIN intervals nodeIntervals
ON
nodeIntervals.lot = sourceIntervals.lot
AND dbo.Intersection( nodeIntervals.start,
nodeIntervals.stop, sourceIntervals.start,
sourceIntervals.stop ) = 1
WHERE
sourceIntervals.nodes_nodeid = xfers.source_nodeid
AND nodeIntervals.nodes_nodeid = xfers.target_nodeid
)
LEFT OUTER JOIN nodes sourceNodes
ON sourceNodes.nodeid = source_nodeid
Here is the setup script again, nicely formatted:
http://authors.aspalliance.com/ayla...asteCodeID=4019
Here is the problem query and one of my feeble attempts:
http://authors.aspalliance.com/ayla...asteCodeID=4020
Ali: The case is nicer, thanks.
/mawi
>--Original Message--
>mawi
>What do you want to return? What is an output?
>I'd rewrite a little bit the function
>CREATE FUNCTION Intersection ( @.startA int, @.stopA int,
> @.startB int, @.stopB int )
> RETURNS bit
> AS
> BEGIN
> RETURN CASE
> WHEN @.startA BETWEEN @.startB AND @.stopB THEN
1 ELSE 0 END
> END
> go
>
>"mawi" <anonymous@.discussions.microsoft.com> wrote in message
>news:24d101c5344c$d11c2a50$a401280a@.phx.gbl...
>
>.
>mawi
You cannot specify columns as parameters to the UDF.
Perhasp you want to use a CASE expression to compare the dates.
"mawi" <anonymous@.discussions.microsoft.com> wrote in message
news:259101c53462$4ed023b0$a401280a@.phx.gbl...
> Hello all!
> Thanks for the followups. I think I was too unclear: The
> function was not the focus, more an appendix to the setup.
> So run the setup and the function.
> My question was how to fix the query so that I can access
> the matching row that makes the EXISTS true. Either via a
> rewrite of the EXISTS condition using a join (which I have
> attempted but ultimately failed) or something else, like
> TOP 1 on the combined xfers / intervals set.
> This query returns the correct result, yet I do not have
> access to the (first found) matching row in the EXISTS:
> -- Desired rows, yet I cannot access the matching row in
> the EXISTS test
> SELECT
> nodes.nodeid, nodes.nodename,
> source_nodeid, volume
> FROM nodes
> LEFT OUTER JOIN xfers
> ON target_nodeid = nodes.nodeid AND EXISTS
> ( SELECT *
> FROM intervals sourceIntervals
> JOIN intervals nodeIntervals
> ON
> nodeIntervals.lot = sourceIntervals.lot
> AND dbo.Intersection( nodeIntervals.start,
> nodeIntervals.stop, sourceIntervals.start,
> sourceIntervals.stop ) = 1
> WHERE
> sourceIntervals.nodes_nodeid = xfers.source_nodeid
> AND nodeIntervals.nodes_nodeid = xfers.target_nodeid
> )
> LEFT OUTER JOIN nodes sourceNodes
> ON sourceNodes.nodeid = source_nodeid
> Here is the setup script again, nicely formatted:
> http://authors.aspalliance.com/ayla...asteCodeID=4019
> Here is the problem query and one of my feeble attempts:
> http://authors.aspalliance.com/ayla...asteCodeID=4020
>
> Ali: The case is nicer, thanks.
> /mawi
> 1 ELSE 0 END|||
>--Original Message--
>mawi
>You cannot specify columns as parameters to the UDF.
>Perhasp you want to use a CASE expression to compare the
dates.
True. But nevermind the function, it is just a shorthand
and works fine as it is.
How about that query, any ideas?
http://authors.aspalliance.com/ayla...asteCodeID=4019
http://authors.aspalliance.com/ayla...asteCodeID=4020
message
have many
gotten
INT,
VARCHAR(3),
>
>.
>|||mawi
There will be some ideas if post desired output.
<anonymous@.discussions.microsoft.com> wrote in message
news:0bef01c53469$caddc870$a501280a@.phx.gbl...
>
> dates.
> True. But nevermind the function, it is just a shorthand
> and works fine as it is.
> How about that query, any ideas?
>
> http://authors.aspalliance.com/ayla...asteCodeID=4019
> http://authors.aspalliance.com/ayla...asteCodeID=4020
> message
> have many
> gotten
> INT,
> VARCHAR(3),
Convert Dbs from SQL Server 2005 to 2000
that will run SQL 2000. How do I do this?
If you haven't used any of the new 2005 features or datatypes you should be
able to script out the schema and run it against the 2000 box. If you have
data to migrate you can bcp it although you may need to use one of the
character formats.
Andrew J. Kelly SQL MVP
"FredC" <FredC@.discussions.microsoft.com> wrote in message
news:3E3ADB6F-9100-4AAC-AB09-09B1BB4F9CDF@.microsoft.com...
>I need to convert my SQL Server 2005 databases (and Stored procs) to ones
> that will run SQL 2000. How do I do this?
convert DB drivers from MS Access to SQL Server in run
functions. I am trying to convert DB drivers from MS Access to SQL Server. I
am using PELogOnServer, PESetNthTableLogOnInfo, PESetNthTableLocation and I
am getting err. msg "Unable to open database." on execution StartPrintJob
API function. Is any body had same problem?
Thank you in advance.Check the values in logoninfo structure.|||I did. I hardcoded my server, db, uid, psw. everything looks OK, but...?
Thank you.