Converting Data from SqlServer Express to SqlServer 2005 beta 2??

Hi,
Was wondering if anyone could tell me how to convert the an SQL Server
Express (MDF) file into SQL Server 2005 Beta 2. I've been looking for a day
or so now and can't seem to find how to do this!!!
Any ideas?
Many Thanks
Wayne Gibson
Hi
Detach the DB in Express and attach it in SQL Server 2005. Or Backup and
Restore.
The backup and data files have 100% the same format.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Wayne Gibson" <wayne.gibson@.rendersoft.com> wrote in message
news:d9gca4$t42$1$8300dec7@.news.demon.co.uk...
> Hi,
> Was wondering if anyone could tell me how to convert the an SQL Server
> Express (MDF) file into SQL Server 2005 Beta 2. I've been looking for a
> day or so now and can't seem to find how to do this!!!
> Any ideas?
> Many Thanks
> Wayne Gibson
>
|||You should find you can just attach it with sp_attach_db. Did you try
that?
Please ask SQL Server 2005 questions in the appropriate newsgroups:
http://communities.microsoft.com/new...=sqlserver2005
David Portas
SQL Server MVP
|||Hi Mike,
Thanks for responding..
Could you tell me how I go about making a backup of a MS SQLServer Express
file.
I got MS SQLServer Express as part of the Visual Studio 2005 Beta 2, but
can't see any tools apart from those in the IDE.
Thanks
Wayne

Converting Data from SqlServer Express to SqlServer 2005 beta 2??

Hi,
Was wondering if anyone could tell me how to convert the an SQL Server
Express (MDF) file into SQL Server 2005 Beta 2. I've been looking for a day
or so now and can't seem to find how to do this!!!
Any ideas?
Many Thanks
Wayne Gibson
Hi
Detach the DB in Express and attach it in SQL Server 2005. Or Backup and
Restore.
The backup and data files have 100% the same format.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Wayne Gibson" <wayne.gibson@.rendersoft.com> wrote in message
news:d9gca4$t42$1$8300dec7@.news.demon.co.uk...
> Hi,
> Was wondering if anyone could tell me how to convert the an SQL Server
> Express (MDF) file into SQL Server 2005 Beta 2. I've been looking for a
> day or so now and can't seem to find how to do this!!!
> Any ideas?
> Many Thanks
> Wayne Gibson
>
|||You should find you can just attach it with sp_attach_db. Did you try
that?
Please ask SQL Server 2005 questions in the appropriate newsgroups:
http://communities.microsoft.com/new...=sqlserver2005
David Portas
SQL Server MVP
|||Hi Mike,
Thanks for responding..
Could you tell me how I go about making a backup of a MS SQLServer Express
file.
I got MS SQLServer Express as part of the Visual Studio 2005 Beta 2, but
can't see any tools apart from those in the IDE.
Thanks
Wayne

Converting Data from SqlServer Express to SqlServer 2005 beta 2??

Hi,
Was wondering if anyone could tell me how to convert the an SQL Server
Express (MDF) file into SQL Server 2005 Beta 2. I've been looking for a day
or so now and can't seem to find how to do this!!!
Any ideas?
Many Thanks
Wayne GibsonYou should find you can just attach it with sp_attach_db. Did you try
that?
Please ask SQL Server 2005 questions in the appropriate newsgroups:
http://communities.microsoft.com/newsgroups/default.asp?icp=sqlserver2005
--
David Portas
SQL Server MVP
--|||Hi
Detach the DB in Express and attach it in SQL Server 2005. Or Backup and
Restore.
The backup and data files have 100% the same format.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Wayne Gibson" <wayne.gibson@.rendersoft.com> wrote in message
news:d9gca4$t42$1$8300dec7@.news.demon.co.uk...
> Hi,
> Was wondering if anyone could tell me how to convert the an SQL Server
> Express (MDF) file into SQL Server 2005 Beta 2. I've been looking for a
> day or so now and can't seem to find how to do this!!!
> Any ideas?
> Many Thanks
> Wayne Gibson
>|||Hi Mike,
Thanks for responding..
Could you tell me how I go about making a backup of a MS SQLServer Express
file.
I got MS SQLServer Express as part of the Visual Studio 2005 Beta 2, but
can't see any tools apart from those in the IDE.
Thanks
Wayne

Converting Data from SqlServer Express to SqlServer 2005 beta 2??

Hi,
Was wondering if anyone could tell me how to convert the an SQL Server
Express (MDF) file into SQL Server 2005 Beta 2. I've been looking for a day
or so now and can't seem to find how to do this!!!
Any ideas?
Many Thanks
Wayne GibsonHi
Detach the DB in Express and attach it in SQL Server 2005. Or Backup and
Restore.
The backup and data files have 100% the same format.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Wayne Gibson" <wayne.gibson@.rendersoft.com> wrote in message
news:d9gca4$t42$1$8300dec7@.news.demon.co.uk...
> Hi,
> Was wondering if anyone could tell me how to convert the an SQL Server
> Express (MDF) file into SQL Server 2005 Beta 2. I've been looking for a
> day or so now and can't seem to find how to do this!!!
> Any ideas?
> Many Thanks
> Wayne Gibson
>|||You should find you can just attach it with sp_attach_db. Did you try
that?
Please ask SQL Server 2005 questions in the appropriate newsgroups:
http://communities.microsoft.com/ne...p=sqlserver2005
David Portas
SQL Server MVP
--|||Hi Mike,
Thanks for responding..
Could you tell me how I go about making a backup of a MS SQLServer Express
file.
I got MS SQLServer Express as part of the Visual Studio 2005 Beta 2, but
can't see any tools apart from those in the IDE.
Thanks
Waynesqlsql

converting access to sqlserver 7

I have a query which fixes area codes. it runs:
UPDATE (nocall INNER JOIN pildta ON (nocall.SUFF = pildta.suff) AND (nocall.PREF = pildta.preff) AND (nocall.AREA = pildta.area)) INNER JOIN ZipCodeUS ON pildta.Zip = ZipCodeUS.ZIP SET nocall.AREA = [a/c]
WHERE (((IIf([nocall]![area]<>[a/c],1,0))=1));

I managed to replace the IIF with a CASE for displaying the unmatched tables like this:
SELECT dbo.nocall.AREA, dbo.pildta.Zip, dbo.ZipCodeUS.[A/C], case when [a/c]=[nocall].[area] then 1 else 0 end as match
FROM dbo.pildta INNER JOIN
dbo.ZipCodeUS ON dbo.pildta.Zip = dbo.ZipCodeUS.ZIP INNER JOIN
dbo.nocall ON dbo.pildta.area = dbo.nocall.AREA AND dbo.pildta.preff = dbo.nocall.PREF AND dbo.pildta.suff = dbo.nocall.SUFF

How can I make this an update query based on [match] having a value of 1?How about moving the [a/c]=[nocall].[area] from a CASE clause to a WHERE clause (which should limit the results to records that would return a 1 in your CASE clause), and then making that query a subquery to your UPDATE command? E.g., UPDATE table SET column = value WHERE tableprimarykey IN (subquery).|||ok. What am I doing wrong. I'm now trying this:
UPDATE nocall
SET AREA = [a/c] IN (SELECT ZipCodeUS.[A/C] FROM ZipCodeUS INNER JOIN pildta ON ZipCodeUS.ZIP = pildta.Zip INNER JOIN nocall ON pildta.area = nocall.AREA AND pildta.preff = nocall.PREF AND pildta.suff = nocall.SUFF)
WHERE [ctr]=[ctr] IN (SELECT nocall.AREA, ZipCodeUS.[A/C], nocall.ctr FROM ZipCodeUS INNER JOIN pildta ON ZipCodeUS.ZIP = pildta.Zip INNER JOIN nocall ON pildta.area = nocall.AREA AND pildta.preff = nocall.PREF AND pildta.suff = nocall.SUFF AND ZipCodeUS.[A/C] <> nocall.AREA)

When i try to save, I get incorrect syntax near the keyword IN, incorrect syntax near the word WHERE|||Well, the first error in the query in your last post is "SET AREA = [a/c] IN (..." This would have to be like "SET AREA = [a/c] WHERE AREA IN (..."

I'm not sure I understand what the query is to accomplish well enough to give you complete rewrite, so tell me if this is correct:

Your goal is to replace values in NoCall.Area with values from ZipCodeUS.[A/C] when and only when:

1. NoCall.Area does not already equal ZipCodeUS.[A/C]
AND
2. ZipCodeUS has a matching record in Pildta based on .Zip
AND
3. NoCall has a record that matches Pildta's Area, Preff, and Suff

Converting a (+) to an outer join in SQLServer

Hi,
I'm having difficulties in translating the Oracle syntax into SQLServer.
Actually, I'm trying to translate something like :

Select Tab1.a, Tab1.b, Tab2.c, Tab2.d
from Tab1, Tab2
Where Tab1.a = Tab2.c (+)

I tried :

Select Tab1.a, Tab1.b, Tab2.c, Tab2.d
from Tab1
RIGHT OUTER JOIN Tab2 ON Tab1.a = Tab2.c

The results seem similar but it occurs that in Oracle, the "Where Tab1.a = Tab2.c (+)" performs some kind of filter !!!

Can anybody help ?Originally posted by Linirlan
Hi,
I'm having difficulties in translating the Oracle syntax into SQLServer.
Actually, I'm trying to translate something like :

Select Tab1.a, Tab1.b, Tab2.c, Tab2.d
from Tab1, Tab2
Where Tab1.a = Tab2.c (+)

I tried :

Select Tab1.a, Tab1.b, Tab2.c, Tab2.d
from Tab1
RIGHT OUTER JOIN Tab2 ON Tab1.a = Tab2.c

The results seem similar but it occurs that in Oracle, the "Where Tab1.a = Tab2.c (+)" performs some kind of filter !!!

Can anybody help ?
Counterintuitively, the ANSI equivalent here would be LEFT OUTER JOIN not RIGHT OUTER JOIN.|||Originally posted by Linirlan
Hi,
I'm having difficulties in translating the Oracle syntax into SQLServer.
Actually, I'm trying to translate something like :

Select Tab1.a, Tab1.b, Tab2.c, Tab2.d
from Tab1, Tab2
Where Tab1.a = Tab2.c (+)

I tried :

Select Tab1.a, Tab1.b, Tab2.c, Tab2.d
from Tab1
RIGHT OUTER JOIN Tab2 ON Tab1.a = Tab2.c

The results seem similar but it occurs that in Oracle, the "Where Tab1.a = Tab2.c (+)" performs some kind of filter !!!

Can anybody help ?

hello ,

Select Tab1.a, Tab1.b, Tab2.c, Tab2.d
from Tab1,Tab2
where Tab1.a =* Tab2.c

here it will includes all rows for Tab2 and Tab1 is a null suppling table.

hope this will help,
Pooja|||Thanks for your answers but err...
I'm sorry but (I'm tired this morning) there's a mistake in my question...

My problem occurs when compared to a constant :

SELECT
TAB1.A,
TAB1.B
FROM
TAB2,
TAB1
WHERE
TAB1.A= TAB2.C (+) AND
TAB2.D(+) = -1

How do you translate "TAB2.D(+) = -1" (which performs some kind of filter I don't understand) ?

Thx|||Originally posted by Linirlan
Thanks for your answers but err...
I'm sorry but (I'm tired this morning) there's a mistake in my question...

My problem occurs when compared to a constant :

SELECT
TAB1.A,
TAB1.B
FROM
TAB2,
TAB1
WHERE
TAB1.A= TAB2.C (+) AND
TAB2.D(+) = -1

How do you translate "TAB2.D(+) = -1" (which performs some kind of filter I don't understand) ?

hello,

i understand from this is that Tab2 is an outer table and u want to restirct the records on outer table...if my understanding is right

try removing (+) from TAB2.d (+)= -1 condition

guess so,
Pooja

Thx|||Originally posted by Linirlan
Thanks for your answers but err...
I'm sorry but (I'm tired this morning) there's a mistake in my question...

My problem occurs when compared to a constant :

SELECT
TAB1.A,
TAB1.B
FROM
TAB2,
TAB1
WHERE
TAB1.A= TAB2.C (+) AND
TAB2.D(+) = -1

How do you translate "TAB2.D(+) = -1" (which performs some kind of filter I don't understand) ?

Thx
What is does is only try to match TAB1 records to TAB2 records where D = -1.

I think with ANSI syntax you probably have to do the filter before the outer join:

SELECT TAB1.A, TAB1.B
FROM TAB1
LEFT OUTER JOIN (SELECT * FROM TAB2 WHERE d=-1) t2
ON TAB1.a = T2.c;

But unless you actually want to select some columns from TAB2, this outer join is pointless - i.e. the result will be same as:

SELECT TAB1.A, TAB1.B
FROM TAB1;|||This is very useful, thank you for your help.

(and yes, I'll select some columns from TAB2)|||I found another way to do it, maybe it is more quick to execute...

SELECT TAB1.A, TAB1.B, TAB2.e
FROM TAB1
LEFT OUTER JOIN TAB2 ON TAB1.a = TAB2.c AND TAB2.d=-1;

What do you think ?|||Originally posted by Linirlan
I found another way to do it, maybe it is more quick to execute...

SELECT TAB1.A, TAB1.B, TAB2.e
FROM TAB1
LEFT OUTER JOIN TAB2 ON TAB1.a = TAB2.c AND TAB2.d=-1;

What do you think ?
Yes, if that works it's probably better than using an inline view. I can't check at the moment (no 9i here), but I had a doubt about doing that for some reason I can't remember.

Convert, downsize, SQL Server database to Microsoft Access

. . . I know, WHY would I want to do this?! Well I like to develop databases in SQLServer, but there is a chance that when I transition this database to the client, they will only have Access at their disposal. Hence before I begin creating the database in SQL Server, I would like to know if it's easy to convert to Access should the need arise.

Thank you in advance,

DanRegards.
Why Access?

Convertion to SQL Server 2005 Express would be so much easier. Smaller chance of client changing things, also.

Just one warning from the license:
http://download.microsoft.com/download/8/9/1/891cf345-643c-49a7-98bc-58bc04def37a/SQLServer2005ExpressEdnDistributionLicense.rtf

3. INTERNET-BASED SERVICES. Microsoft provides Internet-based services with the software. It may change or cancel them at any time
|||

Good point. As for why Access, the one IT person in the office uses Access, and I don't think it will be in my scope to switch her to SQL Server or even SQL Server express.

Convert Syntax Error?

Hi!
I have in one sqlserver 2000 table 3 fields with type datetime (8).
when I look at that table with the SQL Query Analyzer:
in field 'time_' --> 16.01.2003 00:00:04
in field 'date_' --> 01.01.2002
in field 'time_date_new' --> 01.01.2002 00:00:04 (I want to have!)
I want with a update statement write the time part from field 'time_' into
the field
'time_date_new' and then the date part from field 'date_' write also into
the field 'time_date_new'
my statement:
UPDATE Anrufe_2003_Test
SET time_date_new = convert(CHAR(11),date_,126) +
convert(CHAR(12),time_,114)
but I get in query analyser a syntax error on converting.
who has an idea?
ThanksAre you 100% certain that the columns are of the datetime datatype? I'm
surprised to the way that the dateformat is represented and also that the
time portion isn't returned for one row. Did you configure QA to honor the
machine's regional settings?
Anyhow, below should work:
DECLARE @.time_ datetime
DECLARE @.date_ datetime
DECLARE @.dt datetime
SET @.time_ = '14:00:05'
SET @.date_ = '2003-10-17'
SELECT @.time_, @.date_
SET @.dt = CONVERT(char(8), @.date_, 112) + ' ' + CONVERT(char(8), @.time_,
108)
SELECT @.dt
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Hubert Mayr" <huma1@.gmx.net> wrote in message
news:1068471140.682741@.news.liwest.at...
> Hi!
> I have in one sqlserver 2000 table 3 fields with type datetime (8).
> when I look at that table with the SQL Query Analyzer:
> in field 'time_' --> 16.01.2003 00:00:04
> in field 'date_' --> 01.01.2002
> in field 'time_date_new' --> 01.01.2002 00:00:04 (I want to have!)
>
> I want with a update statement write the time part from field 'time_' into
> the field
> 'time_date_new' and then the date part from field 'date_' write also into
> the field 'time_date_new'
> my statement:
> UPDATE Anrufe_2003_Test
> SET time_date_new = convert(CHAR(11),date_,126) +
> convert(CHAR(12),time_,114)
> but I get in query analyser a syntax error on converting.
> who has an idea?
> Thanks
>|||Hi Tibor!
Columns are datetime (8) datatype. I have a German Sqlserver 2000 Version
running.
Where could I see the machines regional settings in QA?
in field 'time_' --> 16.01.2003 00:00:04
in field 'date_' --> 01.01.2002
the values in that two fiels came from a odbc import from a navision own
database. There is a field with time only and a other field with date only.
when I made with dts a automatic import with the navision odbc driver field
'time_' got the systemdate to the time value.
That way I got these values into sqlserver.
Thanks!
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
schrieb im Newsbeitrag news:Or0E4N5pDHA.2964@.tk2msftngp13.phx.gbl...
> Are you 100% certain that the columns are of the datetime datatype? I'm
> surprised to the way that the dateformat is represented and also that the
> time portion isn't returned for one row. Did you configure QA to honor the
> machine's regional settings?
> Anyhow, below should work:
> DECLARE @.time_ datetime
> DECLARE @.date_ datetime
> DECLARE @.dt datetime
> SET @.time_ = '14:00:05'
> SET @.date_ = '2003-10-17'
> SELECT @.time_, @.date_
> SET @.dt = CONVERT(char(8), @.date_, 112) + ' ' + CONVERT(char(8), @.time_,
> 108)
> SELECT @.dt
>
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
>
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Hubert Mayr" <huma1@.gmx.net> wrote in message
> news:1068471140.682741@.news.liwest.at...
> > Hi!
> >
> > I have in one sqlserver 2000 table 3 fields with type datetime (8).
> > when I look at that table with the SQL Query Analyzer:
> >
> > in field 'time_' --> 16.01.2003 00:00:04
> > in field 'date_' --> 01.01.2002
> > in field 'time_date_new' --> 01.01.2002 00:00:04 (I want to have!)
> >
> >
> > I want with a update statement write the time part from field 'time_'
into
> > the field
> > 'time_date_new' and then the date part from field 'date_' write also
into
> > the field 'time_date_new'
> >
> > my statement:
> >
> > UPDATE Anrufe_2003_Test
> > SET time_date_new = convert(CHAR(11),date_,126) +
> > convert(CHAR(12),time_,114)
> >
> > but I get in query analyser a syntax error on converting.
> > who has an idea?
> >
> > Thanks
> >
> >
>|||> Where could I see the machines regional settings in QA?
Tools, Options, Connections.
However, is you have datetime, then it is most probably fine. Personally, I
don't like it when tools "hides" the time portions just because you have
00:00:00, but I think that this is what it going on.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Hubert Mayr" <huma1@.gmx.net> wrote in message
news:1068474028.262656@.news.liwest.at...
> Hi Tibor!
> Columns are datetime (8) datatype. I have a German Sqlserver 2000 Version
> running.
> Where could I see the machines regional settings in QA?
> in field 'time_' --> 16.01.2003 00:00:04
> in field 'date_' --> 01.01.2002
> the values in that two fiels came from a odbc import from a navision own
> database. There is a field with time only and a other field with date
only.
> when I made with dts a automatic import with the navision odbc driver
field
> 'time_' got the systemdate to the time value.
> That way I got these values into sqlserver.
> Thanks!
>
>
> "Tibor Karaszi"
<tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
> schrieb im Newsbeitrag news:Or0E4N5pDHA.2964@.tk2msftngp13.phx.gbl...
> > Are you 100% certain that the columns are of the datetime datatype? I'm
> > surprised to the way that the dateformat is represented and also that
the
> > time portion isn't returned for one row. Did you configure QA to honor
the
> > machine's regional settings?
> >
> > Anyhow, below should work:
> >
> > DECLARE @.time_ datetime
> > DECLARE @.date_ datetime
> > DECLARE @.dt datetime
> > SET @.time_ = '14:00:05'
> > SET @.date_ = '2003-10-17'
> > SELECT @.time_, @.date_
> > SET @.dt = CONVERT(char(8), @.date_, 112) + ' ' + CONVERT(char(8), @.time_,
> > 108)
> > SELECT @.dt
> >
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > Archive at:
> >
>
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
> >
> >
> > "Hubert Mayr" <huma1@.gmx.net> wrote in message
> > news:1068471140.682741@.news.liwest.at...
> > > Hi!
> > >
> > > I have in one sqlserver 2000 table 3 fields with type datetime (8).
> > > when I look at that table with the SQL Query Analyzer:
> > >
> > > in field 'time_' --> 16.01.2003 00:00:04
> > > in field 'date_' --> 01.01.2002
> > > in field 'time_date_new' --> 01.01.2002 00:00:04 (I want to have!)
> > >
> > >
> > > I want with a update statement write the time part from field 'time_'
> into
> > > the field
> > > 'time_date_new' and then the date part from field 'date_' write also
> into
> > > the field 'time_date_new'
> > >
> > > my statement:
> > >
> > > UPDATE Anrufe_2003_Test
> > > SET time_date_new = convert(CHAR(11),date_,126) +
> > > convert(CHAR(12),time_,114)
> > >
> > > but I get in query analyser a syntax error on converting.
> > > who has an idea?
> > >
> > > Thanks
> > >
> > >
> >
> >
>

convert SQLServer Expresss database to sqlserver2000

I have some question about SQLServer Expresss.

1. I want attach a SQLServer Expresss database to sqlserver2005.is it possible? How?

2. Can I get script by SQLServer Expresss?

3. And my important question is: How can I convert a SQLServer Expresss database to sqlserver2000?

Please help me.

Thanks in advance.

If both Express and the other edition are in the same network, register the Express to the full edition and use the Backup and Restore Wizard to Backup the database in Express then Restore in the full edition. You could try auto attach which may not work because regular attach require both MDF(Microsoft data file) and LDF(log data file). If you did not create the database with .SQL code or the GUI in Management Studio in Express then you may not have the LDF.

http://msdn2.microsoft.com/en-us/library/ms190631.aspx

Now to 2000 create a blank database in 2000 and then use DTS in 2000 to move your data and tables to 2000 because there are differences. I have posted a FAQ of how to move databases in the thread below. Hope this helps.

http://forums.asp.net/thread/1454694.aspx

|||

Hello

as you said I tried to Backup the database in Express but I couldn't Restore it in full edition .

I get this errore: Error 3205

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

Too many backup devices specified for backup or restore; only 64 are allowed.
RESTORE HEADERONLY is terminating abnormally. (.Net SqlClient Data Provider)

What's the problem? :(

Thanks for your helping

|||

The Backup and Restore wizard have nothing to do with .NET you are supposed to do it in Management Studio, so I have posted a FAQ which includes a Backup free chapter of a book use it. Hope this helps.

http://forums.asp.net/thread/1454694.aspx

convert SQLServer 2005 to SQLServer 2000

hello,
does anyone know a tool to convert a SQLServer2005 database to
SQLServer2k?
candide_sh
Hello,
As far as I know there is no toool to do that. Only possibility is by
installing SQL 2000 and use DTS / SSIS to copy objects and Data from SQL
2005.
Thanks
Hari
<candide_sh@.yahoo.de> wrote in message
news:1169726869.451045.129600@.a75g2000cwd.googlegr oups.com...
> hello,
> does anyone know a tool to convert a SQLServer2005 database to
> SQLServer2k?
> --
> candide_sh
>
|||hello,
I downloaded DB Publishing Wizard, had a little issue with one UNICODE
value, did a manual workaround and all is fine.
thanks
On 26 Jan., 07:25, Gerald Aichholzer <gerald.aichhol...@.gmx.net>
wrote:
> Hello,
> candide...@.yahoo.de wrote:
> <quote>
> SQL Server Database Publishing Wizard enables the deployment of SQL
> Server databases into a hosted environment on either a SQL Server 2000
> or 2005 server. It generates a single SQL script file which can be used
> to recreate a database (both schema and data) in a shared hosting
> environment where the only connectivity to a server is through a
> web-based control panel with a script execution window. If supported by
> the hosting service provider, the Database Publishing Wizard can also
> directly upload databases to servers located at the shared hosting provider.
> </quote>
> HTH,
> Gerald

convert SQL Server DB to Access DB

Hi,
IS it possible to take a SQLServer DB and convert it into an access
version? If so, can you easily convert stored procedures in access
queries? Or is all this not possible?Using SSIS in SQL Server 2005 and using DTS in SQL Server 2000, you can
easily export your SQL Server tables and views into a Microsoft Access
database.
To do this, go to your database in SQL Server and right click on it.
Tasks\Export Data. Choose your source from SQL Server and choose your target
as Microsoft Access from the list and go on...
--
Ekrem Önsoy
"bcap" <rayh@.patriots.com> wrote in message
news:1190648932.092871.210280@.19g2000hsx.googlegroups.com...
> Hi,
> IS it possible to take a SQLServer DB and convert it into an access
> version? If so, can you easily convert stored procedures in access
> queries? Or is all this not possible?
>|||Hi
This is not the way most people go! Is SQL Express not an option? AFAIK
there is not a way to convert a stored procedure to an Access query. Your
stored procedure may contain code and logic that is not possible to do in an
Access query, so unless they are very simple single statements being run in
the query you will need to write the logic into the client application. You
can import/export the tables and data into Access quite easily.
John
"bcap" wrote:
> Hi,
> IS it possible to take a SQLServer DB and convert it into an access
> version? If so, can you easily convert stored procedures in access
> queries? Or is all this not possible?
>

Convert SQL in ASP 3.0 site to UPDATE ntext

Greetings...
I have an ASP 3.0 website that I am usizing from an MS Access DB to MS SQL
server 2000. While most of my SQL seems to be working perfectly, my memo
fields (ntext) it appears require updating using WRITETEXT.
The following is the sub I tried to use, however, was asked to declare the
pointer variable. After many attempts I'm stuck...
sub db_update_add_note
sql ="SELECT @.ptrval = TEXTPTR(ProjectDesc) " & _
"FROM pmProjects " & _
"WHERE ProjectId = " & ProjectId & " " & _
"WRITETEXT newnote @.ptrval"
response.write sql
on error resume next
cn.execute(sql)
if err.number <> 0 then
b_error = true
error_list.add "db_update_add_note" & err.Number ,"The database update
failed: " & err.Description
else
end if
on error goto 0
end sub
As I'm new to the differences between MS Access SQL and T-SQL I could use
some help.
Thanks...
Message posted via http://www.droptable.com
Sorry... This is the code I attempted to use:
sub db_update_add_note
sql = "DECLARE @.ptrval CURSOR " & _
"SELECT @.ptrval = TEXTPTR(ProjectDesc) " & _
"FROM pmProjects " & _
"WHERE ProjectId = " & ProjectId & " " & _
"WRITETEXT newnote @.ptrval"
response.write sql
on error resume next
cn.execute(sql)
if err.number <> 0 then
b_error = true
error_list.add "db_update_add_note" & err.Number ,"The database update
failed: " & err.Description
else
end if
on error goto 0
end sub
I know I'm missing something here...
Kev
Message posted via http://www.droptable.com
|||There are a number of problems with your SQL script. @.ptrval needs to be a
binary(16) rather than CURSOR. Also, you have not specified a column value
in the WRITETEXT statement.
If your ntext data are reasonably sized, you might consider using a regular
UPDATE statement instead like the example below. In any case, use command
parameters to prevent SQL injection.
myCommand.CommandText = "UPDATE pmProjects SET ProjectDescription = ? WHERE
ProjectId = ?"
Set projectDescriptionParameter = myCommand.CreateParameter( _
"@.ProjectDescription", _
adLongVarWChar, _
adParamInput, _
Len(ProjectDescription))
myCommand.Parameters.Append projectDescriptionParameter
projectDescriptionParameter.Value = ProjectDescription
Set projectIdParameter = myCommand.CreateParameter( _
"@.ProjectIdParameter", _
adInteger, _
adParamInput)
myCommand.Parameters.Append projectIdParameter
projectIdParameter.Value = ProjectId
myCommand.Execute
Hope this helps.
Dan Guzman
SQL Server MVP
"Kevin Dearinger via droptable.com" <forum@.droptable.com> wrote in message
news:cbbeac9230f74b41929c9ef12396c26a@.droptable.co m...
> Sorry... This is the code I attempted to use:
> sub db_update_add_note
> sql = "DECLARE @.ptrval CURSOR " & _
> "SELECT @.ptrval = TEXTPTR(ProjectDesc) " & _
> "FROM pmProjects " & _
> "WHERE ProjectId = " & ProjectId & " " & _
> "WRITETEXT newnote @.ptrval"
> response.write sql
> on error resume next
> cn.execute(sql)
> if err.number <> 0 then
> b_error = true
> error_list.add "db_update_add_note" & err.Number ,"The database update
> failed: " & err.Description
> else
> end if
> on error goto 0
> end sub
> I know I'm missing something here...
> Kev
> --
> Message posted via http://www.droptable.com

Convert SQL in ASP 3.0 site to UPDATE ntext

Greetings...
I have an ASP 3.0 website that I am usizing from an MS Access DB to MS SQL
server 2000. While most of my SQL seems to be working perfectly, my memo
fields (ntext) it appears require updating using WRITETEXT.
The following is the sub I tried to use, however, was asked to declare the
pointer variable. After many attempts I'm stuck...
sub db_update_add_note
sql = "SELECT @.ptrval = TEXTPTR(ProjectDesc) " & _
"FROM pmProjects " & _
"WHERE ProjectId = " & ProjectId & " " & _
"WRITETEXT newnote @.ptrval"
response.write sql
on error resume next
cn.execute(sql)
if err.number <> 0 then
b_error = true
error_list.add "db_update_add_note" & err.Number ,"The database update
failed: " & err.Description
else
end if
on error goto 0
end sub
As I'm new to the differences between MS Access SQL and T-SQL I could use
some help.
Thanks...
Message posted via http://www.droptable.comSorry... This is the code I attempted to use:
sub db_update_add_note
sql = "DECLARE @.ptrval CURSOR " & _
"SELECT @.ptrval = TEXTPTR(ProjectDesc) " & _
"FROM pmProjects " & _
"WHERE ProjectId = " & ProjectId & " " & _
"WRITETEXT newnote @.ptrval"
response.write sql
on error resume next
cn.execute(sql)
if err.number <> 0 then
b_error = true
error_list.add "db_update_add_note" & err.Number ,"The database update
failed: " & err.Description
else
end if
on error goto 0
end sub
I know I'm missing something here...
Kev
Message posted via http://www.droptable.com|||There are a number of problems with your SQL script. @.ptrval needs to be a
binary(16) rather than CURSOR. Also, you have not specified a column value
in the WRITETEXT statement.
If your ntext data are reasonably sized, you might consider using a regular
UPDATE statement instead like the example below. In any case, use command
parameters to prevent SQL injection.
myCommand.CommandText = "UPDATE pmProjects SET ProjectDescription = ? WHERE
ProjectId = ?"
Set projectDescriptionParameter = myCommand.CreateParameter( _
"@.ProjectDescription", _
adLongVarWChar, _
adParamInput, _
Len(ProjectDescription))
myCommand.Parameters.Append projectDescriptionParameter
projectDescriptionParameter.Value = ProjectDescription
Set projectIdParameter = myCommand.CreateParameter( _
"@.ProjectIdParameter", _
adInteger, _
adParamInput)
myCommand.Parameters.Append projectIdParameter
projectIdParameter.Value = ProjectId
myCommand.Execute
Hope this helps.
Dan Guzman
SQL Server MVP
"Kevin Dearinger via droptable.com" <forum@.droptable.com> wrote in message
news:cbbeac9230f74b41929c9ef12396c26a@.dr
optable.com...
> Sorry... This is the code I attempted to use:
> sub db_update_add_note
> sql = "DECLARE @.ptrval CURSOR " & _
> "SELECT @.ptrval = TEXTPTR(ProjectDesc) " & _
> "FROM pmProjects " & _
> "WHERE ProjectId = " & ProjectId & " " & _
> "WRITETEXT newnote @.ptrval"
> response.write sql
> on error resume next
> cn.execute(sql)
> if err.number <> 0 then
> b_error = true
> error_list.add "db_update_add_note" & err.Number ,"The database update
> failed: " & err.Description
> else
> end if
> on error goto 0
> end sub
> I know I'm missing something here...
> Kev
> --
> Message posted via http://www.droptable.com

Convert Informix to SQL Server

Hello, i need to convert this store procedure from Informix to SQL
Server and only obtain error's. Can anyone help me?
CREATE PROCEDURE atribui_num_carta
@.dia_env INT,
@.mes_env INT,
@.ano_env INT
DECLARE @.aux_numcarta INT,
@.aux_numcarta1 INT,
@.var_medant LIKE mudmed.medant,
@.var_cartatipo LIKE mudmed.cartatipo,
@.var_ramo LIKE mudmed.ramo,
@.var_apolice LIKE mudmed.apolice,
@.var_dataalter LIKE mudmed.dataalter,
@.var_registo LIKE mudmed.registo,
@.aux_medant LIKE mudmed.medant,
@.aux_cartatipo LIKE mudmed.cartatipo,
@.aux_ramo LIKE mudmed.ramo,
@.aux_apolice LIKE mudmed.apolice,
@.aux_dataenvio LIKE mudmed.dataenvio,
@.aux_dataenvio_comp LIKE mudmed.dataenvio_comp,
@.ja_esta_em_trans INT,
@.sql_error_num INT,
@.isam_error_num INT,
@.error_msg VARCHAR(100)
--ON EXCEPTION
-- SET sql_error_num, isam_error_num, error_msg
IF ja_esta_em_trans <> 1
ROLLBACK WORK
RAISE EXCEPTION sql_error_num, isam_error_num, error_msg;
END EXCEPTION;
LET ja_esta_em_trans = 0;
BEGIN
ON EXCEPTION IN (-535)
LET ja_esta_em_trans = 1;
END EXCEPTION;
BEGIN WORK;
END
SELECT Max(M1.NUMCARTA) INTO aux_numcarta
FROM mudmed_grupo AS M1
WHERE Year(M1.DATAENVIO) = ano_env;
SELECT Max(M1.NUMCARTA) INTO aux_numcarta1
FROM mudmedhist_grupo AS M1
WHERE Year(M1.DATAENVIO) = ano_env;
IF aux_numcarta IS NULL
LET aux_numcarta = 0
IF aux_numcarta1 IS NULL
LET aux_numcarta1 = 0
IF aux_numcarta1 > aux_numcarta
LET aux_numcarta = aux_numcarta1
END IF
LET @.aux_medant = -1;
LET @.aux_cartatipo = -1;
LET @.aux_ramo = -1;
LET @.aux_apolice = -1;
LET @.aux_numcarta1 = -1;
LET aux_dataenvio_comp = CURRENT;
FOREACH
SELECT medant, cartatipo, ramo, apolice, dataalter, registo
INTO var_medant, var_cartatipo, var_ramo, var_apolice,
var_dataalter, var_registo
FROM mudmed_grupo
WHERE (numcarta IS NULL)
ORDER BY medant, cartatipo, ramo, apolice, dataalter DESC,
registo DESC
IF var_cartatipo < 1 OR var_cartatipo > 3 OR var_medant IS NULL
OR ((var_medant>=800000 AND var_medant<=899999)
OR (var_medant>=5003000 AND var_medant<=5003500) OR
(var_medant>=5012000 AND var_medant<=5012999))
LET aux_medant = var_medant;
LET aux_cartatipo = var_cartatipo;
LET aux_numcarta1 = -1;
ELSE
IF aux_numcarta1 = -1 OR aux_medant <> var_medant OR
aux_cartatipo <> var_cartatipo
LET aux_medant = var_medant;
LET aux_cartatipo = var_cartatipo;
LET aux_numcarta = aux_numcarta + 1;
LET aux_numcarta1 = aux_numcarta;
IF aux_cartatipo == var_cartatipo AND
aux_ramo = var_ramo AND aux_apolice = var_apolice THEN
LET aux_dataenvio = NULL;
ELSE
LET aux_ramo = var_ramo;
LET aux_apolice = var_apolice;
LET aux_dataenvio = MDY( mes_env, dia_env, ano_env );
END IF;
UPDATE mudmed
SET numcarta = aux_numcarta1,
dataenvio = aux_dataenvio,
dataenvio_comp = aux_dataenvio_comp
WHERE registo = var_registo;
END FOREACH ;
IF ja_esta_em_trans <> 1 THEN
COMMIT WORK;
END IF;
END PROCEDURE;
Thanks,
Apaxe2000If you understand t-SQL as well as the Informix's SQL dialect, it shouldn't
be hard. Instead of translating line by line, understand the overall logic
in the stored procedure and re-write using t-SQL.
Anith|||But the problem is i don't know SQL Server dialect and need to convert
this.
This is the only thing i don't know how resolve in the project i have
to do. Programming in VB is easy. Programming in SQL Server not.|||Apaxe2000 wrote:

> But the problem is i don't know SQL Server dialect and need to convert
> this.
> This is the only thing i don't know how resolve in the project i have
> to do. Programming in VB is easy. Programming in SQL Server not.
You need to spec the problem properly and work from a spec rather than
someone else's code. At least that may be what you'll have to do if you
want an answer from this forum.
The code you posted appears to be a cursor and that probably isn't the
best way to accomplish the same thing in SQL Server. But without more
information on keys, constraints and your data it's difficult to give
you a good answer.
The following article explains the best way to describe your problem
here:
http://www.aspfaq.com/etiquette.asp?id=5006
David Portas
SQL Server MVP
--|||It seems that you've tried converting the procedure but you are getting
errors.
Why not show us what you've done and the errors that you're getting.
You would have to be lucky to find someone here that knows both dialects and
is inclined to do all the work for you.
Besides, there are few comments in the procedure showing us what's going on
and we don't know your table structures.
@.var_medant LIKE mudmed.medant - nothing like this in SQL. You'll have to
use the parameter name @.var_medant in the procedure
LET = SET in SQL server
http://www.sommarskog.se/ for error handling in SQL server
FOREACH would be a Cursor in SQL server although you may be able to write
this without one
CURRENT = getdate()
"Apaxe2000" <Apaxe2000@.gmail.com> wrote in message
news:1135096937.302611.60280@.g14g2000cwa.googlegroups.com...
> Hello, i need to convert this store procedure from Informix to SQL
> Server and only obtain error's. Can anyone help me?
> CREATE PROCEDURE atribui_num_carta
> @.dia_env INT,
> @.mes_env INT,
> @.ano_env INT
> DECLARE @.aux_numcarta INT,
> @.aux_numcarta1 INT,
> @.var_medant LIKE mudmed.medant,
> @.var_cartatipo LIKE mudmed.cartatipo,
> @.var_ramo LIKE mudmed.ramo,
> @.var_apolice LIKE mudmed.apolice,
> @.var_dataalter LIKE mudmed.dataalter,
> @.var_registo LIKE mudmed.registo,
> @.aux_medant LIKE mudmed.medant,
> @.aux_cartatipo LIKE mudmed.cartatipo,
> @.aux_ramo LIKE mudmed.ramo,
> @.aux_apolice LIKE mudmed.apolice,
> @.aux_dataenvio LIKE mudmed.dataenvio,
> @.aux_dataenvio_comp LIKE mudmed.dataenvio_comp,
> @.ja_esta_em_trans INT,
> @.sql_error_num INT,
> @.isam_error_num INT,
> @.error_msg VARCHAR(100)
> --ON EXCEPTION
> -- SET sql_error_num, isam_error_num, error_msg
> IF ja_esta_em_trans <> 1
> ROLLBACK WORK
>
> RAISE EXCEPTION sql_error_num, isam_error_num, error_msg;
> END EXCEPTION;
> LET ja_esta_em_trans = 0;
> BEGIN
> ON EXCEPTION IN (-535)
> LET ja_esta_em_trans = 1;
> END EXCEPTION;
> BEGIN WORK;
> END
> SELECT Max(M1.NUMCARTA) INTO aux_numcarta
> FROM mudmed_grupo AS M1
> WHERE Year(M1.DATAENVIO) = ano_env;
> SELECT Max(M1.NUMCARTA) INTO aux_numcarta1
> FROM mudmedhist_grupo AS M1
> WHERE Year(M1.DATAENVIO) = ano_env;
> IF aux_numcarta IS NULL
> LET aux_numcarta = 0
> IF aux_numcarta1 IS NULL
> LET aux_numcarta1 = 0
> IF aux_numcarta1 > aux_numcarta
> LET aux_numcarta = aux_numcarta1
> END IF
>
> LET @.aux_medant = -1;
> LET @.aux_cartatipo = -1;
> LET @.aux_ramo = -1;
> LET @.aux_apolice = -1;
> LET @.aux_numcarta1 = -1;
> LET aux_dataenvio_comp = CURRENT;
> FOREACH
> SELECT medant, cartatipo, ramo, apolice, dataalter, registo
> INTO var_medant, var_cartatipo, var_ramo, var_apolice,
> var_dataalter, var_registo
> FROM mudmed_grupo
> WHERE (numcarta IS NULL)
> ORDER BY medant, cartatipo, ramo, apolice, dataalter DESC,
> registo DESC
> IF var_cartatipo < 1 OR var_cartatipo > 3 OR var_medant IS NULL
> OR ((var_medant>=800000 AND var_medant<=899999)
> OR (var_medant>=5003000 AND var_medant<=5003500) OR
> (var_medant>=5012000 AND var_medant<=5012999))
> LET aux_medant = var_medant;
> LET aux_cartatipo = var_cartatipo;
> LET aux_numcarta1 = -1;
> ELSE
> IF aux_numcarta1 = -1 OR aux_medant <> var_medant OR
> aux_cartatipo <> var_cartatipo
> LET aux_medant = var_medant;
> LET aux_cartatipo = var_cartatipo;
> LET aux_numcarta = aux_numcarta + 1;
> LET aux_numcarta1 = aux_numcarta;
> IF aux_cartatipo == var_cartatipo AND
> aux_ramo = var_ramo AND aux_apolice = var_apolice THEN
> LET aux_dataenvio = NULL;
> ELSE
> LET aux_ramo = var_ramo;
> LET aux_apolice = var_apolice;
> LET aux_dataenvio = MDY( mes_env, dia_env, ano_env );
> END IF;
> UPDATE mudmed
> SET numcarta = aux_numcarta1,
> dataenvio = aux_dataenvio,
> dataenvio_comp = aux_dataenvio_comp
> WHERE registo = var_registo;
> END FOREACH ;
> IF ja_esta_em_trans <> 1 THEN
> COMMIT WORK;
> END IF;
> END PROCEDURE;
> Thanks,
> Apaxe2000
>|||Hi.
This is the procedure i used:
CREATE PROCEDURE dbo.atribui_num_carta
@.dia_env INT,
@.mes_env INT,
@.ano_env INT
AS
DECLARE @.aux_numcarta INT
DECLARE @.aux_numcarta1 INT
DECLARE @.var_medant LIKE mudmed.medant
DECLARE @.var_cartatipo LIKE mudmed.cartatipo
DECLARE @.var_ramo LIKE mudmed.ramo
DECLARE @.var_apolice LIKE mudmed.apolice
DECLARE @.var_dataalter LIKE mudmed.dataalter
DECLARE @.var_registo LIKE mudmed.registo
DECLARE @.aux_medant LIKE mudmed.medant
DECLARE @.aux_cartatipo LIKE mudmed.cartatipo
DECLARE @.aux_ramo LIKE mudmed.ramo
DECLARE @.aux_apolice LIKE mudmed.apolice
DECLARE @.aux_dataenvio LIKE mudmed.dataenvio
DECLARE @.aux_dataenvio_comp LIKE mudmed.dataenvio_comp
DECLARE @.ja_esta_em_trans INT
DECLARE @.sql_error_num INT
DECLARE @.isam_error_num INT
DECLARE @.error_msg VARCHAR(100)
IF @.ja_esta_em_trans <> 1 BEGIN
ROLLBACK WORK
RAISERROR (@.sql_error_num, @.isam_error_num, @.error_msg);
END EXCEPTION;
SET @.ja_esta_em_trans = 0;
BEGIN
ON EXCEPTION IN (-535)
SET ja_esta_em_trans = 1;
END EXCEPTION;
BEGIN WORK;
END
SELECT Max(M1.NUMCARTA) INTO aux_numcarta
FROM mudmed_grupo AS M1
WHERE Year(M1.DATAENVIO) = ano_env;
SELECT Max(M1.NUMCARTA) INTO aux_numcarta1
FROM mudmedhist_grupo AS M1
WHERE Year(M1.DATAENVIO) = ano_env;
IF (aux_numcarta) IS NULL BEGIN
SET @.aux_numcarta = 0
END
IF aux_numcarta1 IS NULL BEGIN
SET @.aux_numcarta1 = 0
END
IF aux_numcarta1 > aux_numcarta
SET @.aux_numcarta = @.aux_numcarta1
END
SET aux_medant = -1;
SET aux_cartatipo = -1;
SET aux_ramo = -1;
SET aux_apolice = -1;
SET aux_numcarta1 = -1;
SET aux_dataenvio_comp = CURRENT;
FOREACH
SELECT medant, cartatipo, ramo, apolice, dataalter, registo
INTO var_medant, var_cartatipo, var_ramo, var_apolice,
var_dataalter, var_registo
FROM mudmed_grupo
WHERE (numcarta IS NULL)
ORDER BY medant, cartatipo, ramo, apolice, dataalter DESC,
registo DESC
IF var_cartatipo < 1 OR var_cartatipo > 3 OR var_medant IS NULL
OR ((var_medant>=800000 AND var_medant<=899999)
OR (var_medant>=5003000 AND var_medant<=5003500) OR
(var_medant>=5012000 AND var_medant<=5012999)) BEGIN
SET aux_medant = var_medant;
SET aux_cartatipo = var_cartatipo;
SET aux_numcarta1 = -1;
ELSE
IF aux_numcarta1 = -1 OR aux_medant <> var_medant OR
aux_cartatipo <> var_cartatipo BEGIN
SET aux_medant = var_medant;
SET aux_cartatipo = var_cartatipo;
SET aux_numcarta = aux_numcarta + 1;
SET aux_numcarta1 = aux_numcarta;
END
IF aux_cartatipo == var_cartatipo AND aux_ramo = var_ramo AND
aux_apolice = var_apolice BEGIN
SET aux_dataenvio = NULL;
ELSE
SET aux_ramo = var_ramo;
SET aux_apolice = var_apolice;
SET aux_dataenvio = MDY( mes_env, dia_env, ano_env );
END;
UPDATE mudmed
SET numcarta = aux_numcarta1,
dataenvio = aux_dataenvio,
dataenvio_comp = aux_dataenvio_comp
WHERE registo = var_registo;
END FOREACH ;
IF ja_esta_em_trans <> 1 THEN
COMMIT WORK;
END IF;
END PROCEDURE;
I obtain this errors:
Error 156: Incorrect Syntax near the keyword 'LIKE'
Line 32: Incorrect Syntax near 'EXCEPTION'
Incorrect Syntax near the keyword 'ON'
Incorrect Syntax near the keyword 'END'
Line 75: Incorrect Syntax near ','
Line 82: Incorrect Syntax near '='
Line 87: Incorrect Syntax near '='
Line 94: Incorrect Syntax near '='
Line 95: Incorrect Syntax near '='
Line 108: Incorrect Syntax near 'FOREACH'
Incorrect Syntax near the keyword 'END'
The variable are:
LIKE mudmed.medant -> integer
LIKE mudmed.cartatipo -> smallint
LIKE mudmed.ramo -> smallint
LIKE mudmed.apolice -> smallint
LIKE mudmed.dataalter -> datetime
LIKE mudmed.registo -> integer
LIKE mudmed.dataenvio -> datetime
LIKE mudmed.dataenvio_comp -> datetime
Thanks for your help,
Apaxe2000|||Apaxe2000 (Apaxe2000@.gmail.com) writes:
> This is the procedure i used:
I don't know if you seriously expect someone to rewrite your Informix
code to SQL Server for you for free. Since I don't know Informix, I
don't know how far from Informix you have strayed, but you have quite
some way to go, before you are in SQL Server land.
If you are not interesting in learning T-SQL, and this is just a one-off,
I suggest that you try the local phonebook for consultants.
If you want to learn SQL Server, I will give you some hints.

> DECLARE @.var_medant LIKE mudmed.medant
Oracle has a similar feature, but SQL Server does not. You can only
declare a variable to be of a certain type. You can create your
own data-type alias, so that you can say:
EXEC sp_addtype mytype, 'varchar(12)'
and then you can can use mytype both in tables and for variables. But
I suspect that this does not help you here, as your tables supposedly
already exists. You will have to declare @.var_medant explicitly as
whatever type you need.

> IF @.ja_esta_em_trans <> 1 BEGIN
> ROLLBACK WORK
> RAISERROR (@.sql_error_num, @.isam_error_num, @.error_msg);
Note that if you pass a number to RAISERROR, this must be a number >= 50000,
and should have been defined with sp_addmessage. The most common is to
pass a string to RAISERROR with the error message.
The other two parameters are severity and state. Severity should be >= 11
and <= 16 and state is best set to 1.

> BEGIN
> ON EXCEPTION IN (-535)
> SET ja_esta_em_trans = 1;
> END EXCEPTION;
This syntax is not in SQL Server. You did not say which SQL Server you
are using, and this is an area where there are great difference. In SQL
2005, you can say:
BEGIN TRY
.. statements here
END TRY
BEGIN CATCH
.. error handling here
END CATCH
But in SQL 2000, you have no other choice than checking the global
variable @.@.error after each statement. Notice that this variable changes
value after each statement, so you need to put it into a local variable.
Also, note that in SQL 2000, there are errors you cannot catch at all,
since they abort the batch and rolls back the current transaction.
As for what you should check for in your error handler, I don't know,
but -535 is not an error number in SQL Server.

> SELECT Max(M1.NUMCARTA) INTO aux_numcarta
> FROM mudmed_grupo AS M1
> WHERE Year(M1.DATAENVIO) = ano_env;
I don't know what this statement does in Informnix, but in SQL Server
it creates a table, and it fails since you did not provide a name
for the column. If aux_numcarta is a variable, the syntax is
SELECT @.aux_numcarta = Max(M1.NUMCARTA)
FROM mudmed_grupo AS M1
WHERE Year(M1.DATAENVIO) = @.ano_env;
Assuming that DATAENVIO is indexes, the WHERE clause is best written as
WHERE M1:DATAENVIO BETWEEN ltrim(str(@.ano_env)) + '0101' AND
ltrim(str(@.ano_env)) + '1231'
This is because, if you put an indexed column into an expresson, you
nullify the benefit of the index. (I would suspect that this applies to
Informix as well.)

> SET aux_dataenvio_comp = CURRENT;
This means nothing in SQL Server.

> FOREACH
> SELECT medant, cartatipo, ramo, apolice, dataalter, registo
> INTO var_medant, var_cartatipo, var_ramo, var_apolice,
> var_dataalter, var_registo
> FROM mudmed_grupo
> WHERE (numcarta IS NULL)
> ORDER BY medant, cartatipo, ramo, apolice, dataalter DESC,
> registo DESC
I don't know what this FORACH means, but it looks like a syntax for a
cursor. But looking at the code, I can't see anything that calls for
an iteration at all. You should proably rewrite this as a single
UPDATE statement in Informix first.
The CASE expression can be handy:
SELECT @.x = CASE WHEN @.y > 10 THEN 234
WHEN @.y > 6 AND @.w > 12 THEN 123
ELSE -23
END
This is ANSI, so it should work on Informix as well.
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|||At 15/01/06 i go to another project in Oracle and VB.NET but i need to
do this before, but i continue obtain errors and can anyone helpme only
this time?
Thanks,
Apaxe2000|||Apaxe2000 wrote:
> At 15/01/06 i go to another project in Oracle and VB.NET but i need to
> do this before, but i continue obtain errors and can anyone helpme only
> this time?
> Thanks,
> Apaxe2000
Do you really think that "continue obtain errors" is an adequate
specification of a problem? No-one's likely to be able to offer much
help without a fuller description.
I don't want to give you bad advice based on my wild guesses about the
meaning of your legacy code. That's why I asked for the extra
information I did and gave you the article explaining how to do it. You
still haven't provided any DDL, sample data or even shown us what end
result you want. If you take the time to do that you'll be pretty sure
to get more responses.
David Portas
SQL Server MVP
--

Convert Informix Stored Procedure

I would like to convert a couple informix stored procedures to SQL
Server stored procedures. I have no idea how to accomplish this. Here
is an example of one of the procedures I need to convert.

drop function mnaf_calc_calendar_quarter;

CREATE FUNCTION mnaf_calc_calendar_quarter(pEndDate Date)
--************************************************** ***************************
-- Name: mnaf_calc_calendar_quarter
-- Description/Notes:
-- Calculates the most recent calendar quarter based on the end date.
--
-- Parms:
-- End Date.
-- Returns:
-- The calculated period start date and end date.
--
--************************************************** ***************************
-- Revisions:
-- PgmrDate # Description
-- HPI05/03/2005
--************************************************** ***************************

RETURNING date, date;

DEFINE dtStartDate date;
DEFINE dtEndDate date;

LET dtStartDate = mdy(12,31,1899);
LET dtEndDate = pEndDate;

-- If the end date parameter is equal to a calendar quarter,
-- calculate the start date by subtracting three months.
IF month(pEndDate) = 3 or month(pEndDate) = 6 or
month(pEndDate) = 9 or month(pEndDate) = 12 then

LET dtEndDate = pEndDate;

ELSE

-- Otherwise find the closest previous calendar quarter end date
-- then calculate the start date.
IF month(pEndDate) = 1 or month(pEndDate) = 4 or
month(pEndDate) = 7 or month(pEndDate) = 10 then

-- Subtract 1 month off end date parameter to get the calendar
qtr end date
LET dtEndDate = mnaf_eomonth(mnaf_bomonth(dtEndDate) - 1 units
month);

ELSE

-- Month must be equal to 2, 5, 8, 11
-- Subtract 2 months off end date parameter to get the calendar
qtr end date
LET dtEndDate = mnaf_eomonth(mnaf_bomonth(dtEndDate) - 2 units
month);

END IF;

END IF;

-- Calcuate the start date by subtracting off two months
LET dtStartDate = (mnaf_bomonth(dtEndDate) - 2 units month);

RETURN dtStartDate, dtEndDate;

END FUNCTION;

grant execute on mnaf_calc_calendar_quarter to public;"Matt" <matt_marshall@.manning-napier.com> wrote in message
news:1118858793.011839.15120@.g49g2000cwa.googlegro ups.com...
>I would like to convert a couple informix stored procedures to SQL
> Server stored procedures. I have no idea how to accomplish this. Here
> is an example of one of the procedures I need to convert.
> drop function mnaf_calc_calendar_quarter;
> CREATE FUNCTION mnaf_calc_calendar_quarter(pEndDate Date)
> --************************************************** ***************************
> -- Name: mnaf_calc_calendar_quarter
> -- Description/Notes:
> -- Calculates the most recent calendar quarter based on the end date.
> --
> -- Parms:
> -- End Date.
> -- Returns:
> -- The calculated period start date and end date.
> --
> --************************************************** ***************************
> -- Revisions:
> -- Pgmr Date # Description
> -- HPI 05/03/2005
> --************************************************** ***************************
> RETURNING date, date;
> DEFINE dtStartDate date;
> DEFINE dtEndDate date;
> LET dtStartDate = mdy(12,31,1899);
> LET dtEndDate = pEndDate;
> -- If the end date parameter is equal to a calendar quarter,
> -- calculate the start date by subtracting three months.
> IF month(pEndDate) = 3 or month(pEndDate) = 6 or
> month(pEndDate) = 9 or month(pEndDate) = 12 then
> LET dtEndDate = pEndDate;
> ELSE
> -- Otherwise find the closest previous calendar quarter end date
> -- then calculate the start date.
> IF month(pEndDate) = 1 or month(pEndDate) = 4 or
> month(pEndDate) = 7 or month(pEndDate) = 10 then
> -- Subtract 1 month off end date parameter to get the calendar
> qtr end date
> LET dtEndDate = mnaf_eomonth(mnaf_bomonth(dtEndDate) - 1 units
> month);
> ELSE
> -- Month must be equal to 2, 5, 8, 11
> -- Subtract 2 months off end date parameter to get the calendar
> qtr end date
> LET dtEndDate = mnaf_eomonth(mnaf_bomonth(dtEndDate) - 2 units
> month);
> END IF;
> END IF;
> -- Calcuate the start date by subtracting off two months
> LET dtStartDate = (mnaf_bomonth(dtEndDate) - 2 units month);
> RETURN dtStartDate, dtEndDate;
> END FUNCTION;
> grant execute on mnaf_calc_calendar_quarter to public;

See "Date and Time Functions" in Books Online - DATEPART() and DATEADD()
will probably be the ones you're looking for. This article might also be
useful for general background information about manipulating datetime data:

http://www.karaszi.com/sqlserver/info_datetime.asp

Simon

convert from db2 to sqlserver

I have an application running in db2 udb v7.2 fp7. there are 76 tables for this app. i want to convert all these tables over to sqlserver 2000. what is the easiest method to get the data from db2 to sqlserver??It depends on easiest for who...

Is the schema complex or simple (foreign keys, constraints, etc)? What is the DB2 device topography? Are there 100 rows of data, or 100 billion?

Too many questions!

-PatP|||I am going to reinstall the app in sql server. the files are not large a couple thousand rows is the largest. i just want to move the data. the ri is good, as they sit in db2 currently.|||Try this tool :

http://www.swissql.com/products/datamigration/data-migration.html|||There is actually a tool in the SQL Server Resource Kit that does a pretty good job. You might want to check it out.

-PatP|||Why would anyone want to move away from DB2?

convert from char(6) to datetime

Hi there
I receive a very much unstructured flat file, which I import into a SQL
Server database using DTS. After the import, one of the flat file columns is
in the format char(6) rather than datetime. The problem seems to be that the
text file column is in the format 'ddmmyy'. When I use the CONVERT statement
(ie. SELECT CONVERT(datetime, WEDate) As Expr1) I get the error message that
"The conversion of char data type to smalldatetime data type resulted in an
out-of-range smalldatetime value".
Any ideas?
Best regards
Loane
Loane,
Try this (untested), using one of the format codes
from the Books Online article CAST and CONVERT:
CONVERT(datetime,RIGHT(WEDate,2)+SUBSTRING(WEDate, 3,2)+LEFT(WEDate,2),12)
Loane Sharp wrote:

>Hi there
>I receive a very much unstructured flat file, which I import into a SQL
>Server database using DTS. After the import, one of the flat file columns is
>in the format char(6) rather than datetime. The problem seems to be that the
>text file column is in the format 'ddmmyy'. When I use the CONVERT statement
>(ie. SELECT CONVERT(datetime, WEDate) As Expr1) I get the error message that
>"The conversion of char data type to smalldatetime data type resulted in an
>out-of-range smalldatetime value".
>Any ideas?
>Best regards
>Loane
>
>

convert from char(6) to datetime

Hi there
I receive a very much unstructured flat file, which I import into a SQL
Server database using DTS. After the import, one of the flat file columns is
in the format char(6) rather than datetime. The problem seems to be that the
text file column is in the format 'ddmmyy'. When I use the CONVERT statement
(ie. SELECT CONVERT(datetime, WEDate) As Expr1) I get the error message that
"The conversion of char data type to smalldatetime data type resulted in an
out-of-range smalldatetime value".
Any ideas?
Best regards
LoaneLoane,
Try this (untested), using one of the format codes
from the Books Online article CAST and CONVERT:
CONVERT(datetime,RIGHT(WEDate,2)+SUBSTRI
NG(WEDate,3,2)+LEFT(WEDate,2),12)
Loane Sharp wrote:

>Hi there
>I receive a very much unstructured flat file, which I import into a SQL
>Server database using DTS. After the import, one of the flat file columns i
s
>in the format char(6) rather than datetime. The problem seems to be that th
e
>text file column is in the format 'ddmmyy'. When I use the CONVERT statemen
t
>(ie. SELECT CONVERT(datetime, WEDate) As Expr1) I get the error message tha
t
>"The conversion of char data type to smalldatetime data type resulted in an
>out-of-range smalldatetime value".
>Any ideas?
>Best regards
>Loane
>
>

Convert from Access to SQL Server

Can I convert .MDB file to SQL Server database or I need to develop SQL
server database from begin, every table, every field?Can I convert .MDB file to SQL Server database or I need to develop

Quote:

Originally Posted by

SQL server database from begin, every table, every field?

You can choose.

You can "convert" an MDB (definition + data) to a SQL server database either
using "Upsize Wizard" or "SQL Server Migration Assistant for Access".
You can manually create a new SQL server database via "SQL Server Management
Studio".

--
PBsoft di Gabriele Bertolucci
www.pbsoft.it
skype:pbsoftsolution|||Igor wrote:

Quote:

Originally Posted by

Can I convert .MDB file to SQL Server database or I need to develop SQL
server database from begin, every table, every field?

Access has an Upsizing Wizard that will port the table structures and
the data to SQL Server.

The problem is that many Access databases aren't designed for the SQL
DBMS model. So performing a simple migration isn't necessarily going to
give you a sensible or even workable solution in SQL Server. Also, the
migrated database typically may not perform well unless you redesign
some of the logic to take advantage of server-side execution and stored
procedures.

For these reasons I would recommend you view the Upsizing Wizard as
just one step in the process. You should still review the logical
design and you should probably assume that some re-design will be
necessary. Re-writing to use TSQL stored procs should also be an
essential part of the migration.

If you don't make the effort to do these things then the end result
won't be as effective as it could be. In that case you may even be
better sticking with Access until you are able to do the job properly.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--