Converting problem: servers

Showing posts with label servers. Show all posts

Thursday, March 29, 2012

copy many ssis packages between servers

Hi.
Is there any simple way of copying all of the ssis packages between two
servers?
I have just installed sql 2005 on a new, better equipped machine and I have
to move there all the data. Including ssis of course. There are about 40-50
packages to do (stored in the database, not in filesystem) and I hope there
is a better way than just to import packages (one by one) to a text file
and export them to another server.
BTW, I'm going to copy the job definitions as well, there are about 20 jobs
defined on the server, is there any handy way of doing this? Any hints?
PLTry backing up and restoring MSDB and changing the appropriate fields.
We do this with jobs every week to keep 2 servers in sync and this is what
we use to bring the jobs up to date. I would imagine that it could be just
as easy for SSIS packages, though I haven't tried it officially.
UPDATE sysjobs
SET originating_server = 'MyServer'
So just run that on the new server once you have MSDB restored on there and
you should be OK. And just check the SSIS tables to discover similar entrie
s
that may need to be changed.
"Piotr Lipski" wrote:

> Hi.
> Is there any simple way of copying all of the ssis packages between two
> servers?
> I have just installed sql 2005 on a new, better equipped machine and I hav
e
> to move there all the data. Including ssis of course. There are about 40-5
0
> packages to do (stored in the database, not in filesystem) and I hope ther
e
> is a better way than just to import packages (one by one) to a text file
> and export them to another server.
> BTW, I'm going to copy the job definitions as well, there are about 20 job
s
> defined on the server, is there any handy way of doing this? Any hints?
> --
> PL
>|||Sean McCown wrote:
> Try backing up and restoring MSDB and changing the appropriate fields.
> We do this with jobs every week to keep 2 servers in sync and this is what
> we use to bring the jobs up to date. I would imagine that it could be jus
t
> as easy for SSIS packages, though I haven't tried it officially.
> UPDATE sysjobs
> SET originating_server = 'MyServer'
> So just run that on the new server once you have MSDB restored on there an
d
> you should be OK. And just check the SSIS tables to discover similar entr
ies
> that may need to be changed.
Right. But how to restore the msdb database? When I try to do it from
SMS (right click on database -> tasks -> restore) I get failure message
"Exclusive access could not be obtained because the database is in
use.", which makes sense but does not resolve the problem. There is also
no option to go off-line or detach for that database.
Should I just replace the msdb datafiles (mdf/ldf) with the ones from
'old' server?
PL|||OK, there are a couple things here...
first, to restore MSDB just startup SQL with -T3608 and -m. 3608 prevents
startup of all system DBs except master. -m is single-user mode. So you
should now be able to restore MSDB with no problem. If you're doing it from
the command line, then use -c.
Second, You can always just setup a linked server and copy the raw data over
without restoring.
Good luck.
"Piotr Lipski" wrote:

> Sean McCown wrote:
> Right. But how to restore the msdb database? When I try to do it from
> SMS (right click on database -> tasks -> restore) I get failure message
> "Exclusive access could not be obtained because the database is in
> use.", which makes sense but does not resolve the problem. There is also
> no option to go off-line or detach for that database.
> Should I just replace the msdb datafiles (mdf/ldf) with the ones from
> 'old' server?
> --
> PL
>

copy many ssis packages between servers

Hi.
Is there any simple way of copying all of the ssis packages between two
servers?
I have just installed sql 2005 on a new, better equipped machine and I have
to move there all the data. Including ssis of course. There are about 40-50
packages to do (stored in the database, not in filesystem) and I hope there
is a better way than just to import packages (one by one) to a text file
and export them to another server.
BTW, I'm going to copy the job definitions as well, there are about 20 jobs
defined on the server, is there any handy way of doing this? Any hints?
--
PLTry backing up and restoring MSDB and changing the appropriate fields.
We do this with jobs every week to keep 2 servers in sync and this is what
we use to bring the jobs up to date. I would imagine that it could be just
as easy for SSIS packages, though I haven't tried it officially.
UPDATE sysjobs
SET originating_server = 'MyServer'
So just run that on the new server once you have MSDB restored on there and
you should be OK. And just check the SSIS tables to discover similar entries
that may need to be changed.
"Piotr Lipski" wrote:
> Hi.
> Is there any simple way of copying all of the ssis packages between two
> servers?
> I have just installed sql 2005 on a new, better equipped machine and I have
> to move there all the data. Including ssis of course. There are about 40-50
> packages to do (stored in the database, not in filesystem) and I hope there
> is a better way than just to import packages (one by one) to a text file
> and export them to another server.
> BTW, I'm going to copy the job definitions as well, there are about 20 jobs
> defined on the server, is there any handy way of doing this? Any hints?
> --
> PL
>|||Sean McCown wrote:
> Try backing up and restoring MSDB and changing the appropriate fields.
> We do this with jobs every week to keep 2 servers in sync and this is what
> we use to bring the jobs up to date. I would imagine that it could be just
> as easy for SSIS packages, though I haven't tried it officially.
> UPDATE sysjobs
> SET originating_server = 'MyServer'
> So just run that on the new server once you have MSDB restored on there and
> you should be OK. And just check the SSIS tables to discover similar entries
> that may need to be changed.
Right. But how to restore the msdb database? When I try to do it from
SMS (right click on database -> tasks -> restore) I get failure message
"Exclusive access could not be obtained because the database is in
use.", which makes sense but does not resolve the problem. There is also
no option to go off-line or detach for that database.
Should I just replace the msdb datafiles (mdf/ldf) with the ones from
'old' server?
--
PL|||OK, there are a couple things here...
first, to restore MSDB just startup SQL with -T3608 and -m. 3608 prevents
startup of all system DBs except master. -m is single-user mode. So you
should now be able to restore MSDB with no problem. If you're doing it from
the command line, then use -c.
Second, You can always just setup a linked server and copy the raw data over
without restoring.
Good luck.
"Piotr Lipski" wrote:
> Sean McCown wrote:
> > Try backing up and restoring MSDB and changing the appropriate fields.
> > We do this with jobs every week to keep 2 servers in sync and this is what
> > we use to bring the jobs up to date. I would imagine that it could be just
> > as easy for SSIS packages, though I haven't tried it officially.
> >
> > UPDATE sysjobs
> > SET originating_server = 'MyServer'
> >
> > So just run that on the new server once you have MSDB restored on there and
> > you should be OK. And just check the SSIS tables to discover similar entries
> > that may need to be changed.
> Right. But how to restore the msdb database? When I try to do it from
> SMS (right click on database -> tasks -> restore) I get failure message
> "Exclusive access could not be obtained because the database is in
> use.", which makes sense but does not resolve the problem. There is also
> no option to go off-line or detach for that database.
> Should I just replace the msdb datafiles (mdf/ldf) with the ones from
> 'old' server?
> --
> PL
>

copy many ssis packages between servers

Hi.
Is there any simple way of copying all of the ssis packages between two
servers?
I have just installed sql 2005 on a new, better equipped machine and I have
to move there all the data. Including ssis of course. There are about 40-50
packages to do (stored in the database, not in filesystem) and I hope there
is a better way than just to import packages (one by one) to a text file
and export them to another server.
BTW, I'm going to copy the job definitions as well, there are about 20 jobs
defined on the server, is there any handy way of doing this? Any hints?
PL
Try backing up and restoring MSDB and changing the appropriate fields.
We do this with jobs every week to keep 2 servers in sync and this is what
we use to bring the jobs up to date. I would imagine that it could be just
as easy for SSIS packages, though I haven't tried it officially.
UPDATE sysjobs
SET originating_server = 'MyServer'
So just run that on the new server once you have MSDB restored on there and
you should be OK. And just check the SSIS tables to discover similar entries
that may need to be changed.
"Piotr Lipski" wrote:

> Hi.
> Is there any simple way of copying all of the ssis packages between two
> servers?
> I have just installed sql 2005 on a new, better equipped machine and I have
> to move there all the data. Including ssis of course. There are about 40-50
> packages to do (stored in the database, not in filesystem) and I hope there
> is a better way than just to import packages (one by one) to a text file
> and export them to another server.
> BTW, I'm going to copy the job definitions as well, there are about 20 jobs
> defined on the server, is there any handy way of doing this? Any hints?
> --
> PL
>
|||Sean McCown wrote:
> Try backing up and restoring MSDB and changing the appropriate fields.
> We do this with jobs every week to keep 2 servers in sync and this is what
> we use to bring the jobs up to date. I would imagine that it could be just
> as easy for SSIS packages, though I haven't tried it officially.
> UPDATE sysjobs
> SET originating_server = 'MyServer'
> So just run that on the new server once you have MSDB restored on there and
> you should be OK. And just check the SSIS tables to discover similar entries
> that may need to be changed.
Right. But how to restore the msdb database? When I try to do it from
SMS (right click on database -> tasks -> restore) I get failure message
"Exclusive access could not be obtained because the database is in
use.", which makes sense but does not resolve the problem. There is also
no option to go off-line or detach for that database.
Should I just replace the msdb datafiles (mdf/ldf) with the ones from
'old' server?
PL
|||OK, there are a couple things here...
first, to restore MSDB just startup SQL with -T3608 and -m. 3608 prevents
startup of all system DBs except master. -m is single-user mode. So you
should now be able to restore MSDB with no problem. If you're doing it from
the command line, then use -c.
Second, You can always just setup a linked server and copy the raw data over
without restoring.
Good luck.
"Piotr Lipski" wrote:

> Sean McCown wrote:
> Right. But how to restore the msdb database? When I try to do it from
> SMS (right click on database -> tasks -> restore) I get failure message
> "Exclusive access could not be obtained because the database is in
> use.", which makes sense but does not resolve the problem. There is also
> no option to go off-line or detach for that database.
> Should I just replace the msdb datafiles (mdf/ldf) with the ones from
> 'old' server?
> --
> PL
>
sql sql

Copy maintenance plan to two different servers

SQL Server 2005:
I've created a number of maintenance plans on a development server that I'd
like to copy this to a number of different servers all running SQL Server
2005 with identical databases/structures. What is the best way to do this?
I'm not adverse to automating this with code.
Thanks,
Mark
Satya SKJ has written about this in another forum.
"Create a SSIS package to perform this maintenance plan task and use DTUTIL
to deploy on multiple servers."
http://www.microsoft.com/technet/prodtechnol/sql/2005/mgngssis.mspx#ERGAE
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1203074&SiteID=1
I have developed a stored procedure based maintenance solution that is easy
to deploy.
http://blog.ola.hallengren.com/blog/_archives/2008/1/1/3440068.html
http://blog.ola.hallengren.com/_attachments/3440068/Documentation.html
Ola Hallengren
http://ola.hallengren.com
"Mark" wrote:

> SQL Server 2005:
> I've created a number of maintenance plans on a development server that I'd
> like to copy this to a number of different servers all running SQL Server
> 2005 with identical databases/structures. What is the best way to do this?
> I'm not adverse to automating this with code.
> Thanks,
> Mark
>
>

Copy maintenance plan to two different servers

SQL Server 2005:
I've created a number of maintenance plans on a development server that I'd
like to copy this to a number of different servers all running SQL Server
2005 with identical databases/structures. What is the best way to do this?
I'm not adverse to automating this with code.
Thanks,
MarkSatya SKJ has written about this in another forum.
"Create a SSIS package to perform this maintenance plan task and use DTUTIL
to deploy on multiple servers."
http://www.microsoft.com/technet/prodtechnol/sql/2005/mgngssis.mspx#ERGAE
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1203074&SiteID=1
I have developed a stored procedure based maintenance solution that is easy
to deploy.
http://blog.ola.hallengren.com/blog/_archives/2008/1/1/3440068.html
http://blog.ola.hallengren.com/_attachments/3440068/Documentation.html
Ola Hallengren
http://ola.hallengren.com
"Mark" wrote:
> SQL Server 2005:
> I've created a number of maintenance plans on a development server that I'd
> like to copy this to a number of different servers all running SQL Server
> 2005 with identical databases/structures. What is the best way to do this?
> I'm not adverse to automating this with code.
> Thanks,
> Mark
>
>

Tuesday, March 27, 2012

Copy job from one server to another

How do you copy a job from one server to another. Identical databases different servers with different names
Hi,
There are 3 options:-
1.
You can generate the script for all jobs ( Enterprise manager -- Right click
on the jobs
node in SQl Agent -- Jobs| All tasks| Generate sql script, Save it as file).
Run this
script in the destination server.
2.
"Transfer Jobs" Task in DTS, That will transfer the jobs to new server.
3.
Jobs, operators , Alerts ,...are stored in msdb database.If the destination
server
is a new one and if you do not have any existing stuffs in msdb , you can
even restore a msdb backup from the source server.
-
Thanks
Hari
MCDBA
"cbriscoejr" <cbriscoejr@.discussions.microsoft.com> wrote in message
news:B1336E38-C8C8-4F1B-BB8D-87E92ADD338F@.microsoft.com...
> How do you copy a job from one server to another. Identical databases
different servers with different names

copy files

Hi,
I have two servers within the same domain. Using CMD Prompt, when I copy
files from one server to another, it works flawlessly, eg. COPY
D:\SQL8Bkup4DRP\*.BAK \\<otherServerName>\D$\SQL8Bkup4DRP
However, when I use the same command in a CMDEXEC step of a job, it gives me
an Access Denied error message.
Any help. Thanks in advance for all your help.Make sure that the account that the SQL Server Agent runs in has permissions
on the source/ destination. Thats the account that jobs run in.
"Rob" <Rob@.discussions.microsoft.com> wrote in message
news:3FED8D10-A43F-431A-94EF-0F51271B5E82@.microsoft.com...
> Hi,
> I have two servers within the same domain. Using CMD Prompt, when I copy
> files from one server to another, it works flawlessly, eg. COPY
> D:\SQL8Bkup4DRP\*.BAK \\<otherServerName>\D$\SQL8Bkup4DRP
> However, when I use the same command in a CMDEXEC step of a job, it gives
me
> an Access Denied error message.
> Any help. Thanks in advance for all your help.

copy files

Hi,
I have two servers within the same domain. Using CMD Prompt, when I copy
files from one server to another, it works flawlessly, eg. COPY
D:\SQL8Bkup4DRP\*.BAK \\<otherServerName>\D$\SQL8Bkup4DRP
However, when I use the same command in a CMDEXEC step of a job, it gives me
an Access Denied error message.
Any help. Thanks in advance for all your help.
Make sure that the account that the SQL Server Agent runs in has permissions
on the source/ destination. Thats the account that jobs run in.
"Rob" <Rob@.discussions.microsoft.com> wrote in message
news:3FED8D10-A43F-431A-94EF-0F51271B5E82@.microsoft.com...
> Hi,
> I have two servers within the same domain. Using CMD Prompt, when I copy
> files from one server to another, it works flawlessly, eg. COPY
> D:\SQL8Bkup4DRP\*.BAK \\<otherServerName>\D$\SQL8Bkup4DRP
> However, when I use the same command in a CMDEXEC step of a job, it gives
me
> an Access Denied error message.
> Any help. Thanks in advance for all your help.

copy error

Can someone point me in the right direction to figure out
what's wrong here?
I'm trying to copy a very simple DB between two servers
using the SQL 2000 Enterprise Manager DB Copy Wizard. All
steps looks good up to detaching the source DB, but then
the actual copy of the share database mdf file to the
destination server fails. The Error Code in the Task
Detail box is 'failed'. The Error Code in the Step Detail
box is '-2147467259'.
The Package Log Error Description is:
Step Error Source: Microsoft Data Transformation Services
(DTS) Package
Step Error Description:Unspecified error
Step Error code: 80004005
Step Error Help File:sqldts80.hlp
Step Error Help Context ID:1100The reason and solution to your exact error/problem are given at:
http://support.microsoft.com/default.aspx?scid=kb;en-us;274463&Product=sql2k
"Tech - SBT" <bglynn@.sbtontheweb.com> wrote in message
news:1MCjb.147527$Of2.4215035@.twister.tampabay.rr.com...
> Can someone point me in the right direction to figure out
> what's wrong here?
> I'm trying to copy a very simple DB between two servers
> using the SQL 2000 Enterprise Manager DB Copy Wizard. All
> steps looks good up to detaching the source DB, but then
> the actual copy of the share database mdf file to the
> destination server fails. The Error Code in the Task
> Detail box is 'failed'. The Error Code in the Step Detail
> box is '-2147467259'.
> The Package Log Error Description is:
> Step Error Source: Microsoft Data Transformation Services
> (DTS) Package
> Step Error Description:Unspecified error
> Step Error code: 80004005
> Step Error Help File:sqldts80.hlp
> Step Error Help Context ID:1100
>|||I read that and don't see the exact problem. MORE INFO does not give me any
details nor an exact error.
"I_AM_DON_AND_YOU?" <user@.domain.com> wrote in message
news:%23eK7ukClDHA.2060@.tk2msftngp13.phx.gbl...
> The reason and solution to your exact error/problem are given at:
>
http://support.microsoft.com/default.aspx?scid=kb;en-us;274463&Product=sql2k
>
> "Tech - SBT" <bglynn@.sbtontheweb.com> wrote in message
> news:1MCjb.147527$Of2.4215035@.twister.tampabay.rr.com...
> > Can someone point me in the right direction to figure out
> > what's wrong here?
> >
> > I'm trying to copy a very simple DB between two servers
> > using the SQL 2000 Enterprise Manager DB Copy Wizard. All
> > steps looks good up to detaching the source DB, but then
> > the actual copy of the share database mdf file to the
> > destination server fails. The Error Code in the Task
> > Detail box is 'failed'. The Error Code in the Step Detail
> > box is '-2147467259'.
> >
> > The Package Log Error Description is:
> >
> > Step Error Source: Microsoft Data Transformation Services
> > (DTS) Package
> > Step Error Description:Unspecified error
> >
> > Step Error code: 80004005
> > Step Error Help File:sqldts80.hlp
> > Step Error Help Context ID:1100
> >
> >
>
>

Sunday, March 25, 2012

copy databases

Hi, how can i copy databases from one server in one domain to another
server. Since the servers isnt in the same domain its not possible to do
with the copy in enterprise manager (even with same username and password on
Administrator). Is there som 3de party program to use for this? the customer
has nearly 100 databases and to manualy detach, copy and attach then is a
hell of a job.
/Per W.See if this helps: http://vyaskn.tripod.com/moving_sql_server.htm
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Per W." <fungererikke@.ikkegyldig.no.invalid> wrote in message
news:e6udnk$95i$1@.troll.powertech.no...
Hi, how can i copy databases from one server in one domain to another
server. Since the servers isnt in the same domain its not possible to do
with the copy in enterprise manager (even with same username and password on
Administrator). Is there som 3de party program to use for this? the customer
has nearly 100 databases and to manualy detach, copy and attach then is a
hell of a job.
/Per W.|||"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> skrev i melding
news:uRVRAIVkGHA.5036@.TK2MSFTNGP04.phx.gbl...
> See if this helps: http://vyaskn.tripod.com/moving_sql_server.htm
>
No, sorry. I have 2 SBS servers, one 2000 that i will move the database over
to the new 2003, and since both servers are up and running i cant do this,
and i need something to copy all databases since the databases are on
different folders on the harddisk then ther is a lot of job copying the
files manualy. I cant understand why MS have made restrictions on the copy
options in the enterprise manager since this is a situation where it will be
realy good to use.
/Per W.

Tuesday, March 20, 2012

Copy Database Wizard

We have member SQL servers in a workgroup, running in a netware environment.
We do not have a domain. The wizard fails when I try to copy a database f
rom one server to another. What's the solution or workaround?
Thank you.Part of the process for the Copy Database Wizard involves the SQL Server
startup account on the destination accessing a mapped drive on the source
machine. If this account cannot access a mapped drive the Copy Database
Wizard will fail.
Rand
This posting is provided "as is" with no warranties and confers no rights.

Monday, March 19, 2012

copy database tables

Hi!

Can someone tell me how I can copy my database which is made with SQLExpress or the tables to another SQL server. The servers could be SQL server 2000 and SQLExpress.

Thanks!

SQL Server Express --> SQL Server Express (or any other 2005er edition), just detach the mdf file (if possible) and copy it to the destination location, then attach the orginal one to the server again, and attach the copy to the remote server -done. If you have no maintainance window to do so, make a backup and a restore on the other server (could take a bit longer but keeps your database online during the whole time)

SQL Server Express --> downgade versions: Create scripts of your objects and data. Make sure that the scripts are compatible with the versions you want to deploy it to.

HTH, jens Suessmeyer.

http://www.sqlserver2005.de
|||

At first I made backup file of my SQLExpress database and and copy it to another PC witch have SQLExpress also, but I didn't work.

Then I tryed the script and it did work. Now I have to test this also to SQL Server 2000.

The question, is what did I do wrong with the backup?

|||Doing a copy from SQL Server Express to SQL Server Express should work fine, which error message are you getting ?

If you have a SQL Server 2000 Dev/Std/Ent you can use the DTS Wizard to copy the database from SQl Server Express to SQL 2000 (Import).

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

Copy database from one server to other

We had two physical SQL servers and copying one database using the wizard to
the other sqlserver was easy because both services used the same domain
account. Now i will have to copy databases from one server to another
outside the domain. This means they will have seperate logins for the
sqlserver service. how can i then use the copy database wizard? It fails
because it can't create a file on the ditination server'
does this mean i have to use log shipping or use a script and then copy just
data'
thanks.You can use backup/restore or sp_attachdb, sp_detachdb. You will have to
manually script out the logins. See this page for more info:
http://support.microsoft.com/defaul...kb;en-us;246133
Ray Higdon MCSE, MCDBA, CCNA
--
"Richard K." <google@.walkersca.com> wrote in message
news:uCR%23ry98DHA.3404@.TK2MSFTNGP09.phx.gbl...
> We had two physical SQL servers and copying one database using the wizard
to
> the other sqlserver was easy because both services used the same domain
> account. Now i will have to copy databases from one server to another
> outside the domain. This means they will have seperate logins for the
> sqlserver service. how can i then use the copy database wizard? It fails
> because it can't create a file on the ditination server'
> does this mean i have to use log shipping or use a script and then copy
just
> data'
> thanks.
>

Copy database from one server to other

We had two physical SQL servers and copying one database using the wizard to
the other sqlserver was easy because both services used the same domain
account. Now i will have to copy databases from one server to another
outside the domain. This means they will have seperate logins for the
sqlserver service. how can i then use the copy database wizard? It fails
because it can't create a file on the ditination server'
does this mean i have to use log shipping or use a script and then copy just
data'
thanks.You can use backup/restore or sp_attachdb, sp_detachdb. You will have to
manually script out the logins. See this page for more info:
http://support.microsoft.com/default.aspx?scid=kb;en-us;246133
--
Ray Higdon MCSE, MCDBA, CCNA
--
"Richard K." <google@.walkersca.com> wrote in message
news:uCR%23ry98DHA.3404@.TK2MSFTNGP09.phx.gbl...
> We had two physical SQL servers and copying one database using the wizard
to
> the other sqlserver was easy because both services used the same domain
> account. Now i will have to copy databases from one server to another
> outside the domain. This means they will have seperate logins for the
> sqlserver service. how can i then use the copy database wizard? It fails
> because it can't create a file on the ditination server'
> does this mean i have to use log shipping or use a script and then copy
just
> data'
> thanks.
>

Sunday, March 11, 2012

Copy Database Between Servers

Hi All,
I have a need to copy a database from the production server to a new test
server. I tried using the Copy Database Wizard, but it fails because there
are active connections to the production database. Unfortunately, I cannot
close those connections because it is in use 24/7, and the next maintenance
cycle is the end of April.
I also tried backing up the database to a disk device, copying the backup to
the new test server, and restoring from the disk device to a new database
but it fails, saying "device activation error" and something to do with "Use
WITH MOVE to identify a valid location", so I tried running the restore
command from the query analyser, specifiying the WITH MOVE for the 2 files
but it gives the same error.
Any ideas on what I'm missing?
Regards...Andrew> I also tried backing up the database to a disk device, copying the backup
to
> the new test server, and restoring from the disk device to a new database
> but it fails, saying "device activation error" and something to do with
"Use
> WITH MOVE to identify a valid location", so I tried running the restore
> command from the query analyser, specifiying the WITH MOVE for the 2 files
> but it gives the same error.
Backup / Restore should work. Check again whether your Restore syntax is
correct and whether SQL Server can realy access the folder(s) specified.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com|||> I also tried backing up the database to a disk device, copying the backup
to
> the new test server, and restoring from the disk device to a new database
> but it fails, saying "device activation error" and something to do with
"Use
> WITH MOVE to identify a valid location", so I tried running the restore
> command from the query analyser, specifiying the WITH MOVE for the 2 files
> but it gives the same error.
Backup / Restore should work. Check again whether your Restore syntax is
correct and whether SQL Server can realy access the folder(s) specified.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com|||SQL command is as follows:
RESTORE FILELISTONLY
FROM DISK = 'C:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\PayrollASP_Syngentatest.bak'
RESTORE DATABASE PayrollASP_Syngentatest
FROM DISK = 'C:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\PayrollASP_Syngentatest.bak'
WITH RECOVERY,
MOVE 'PayrollASP_Data' TO 'C:\DataFiles\Microsoft SQL
Server\MSSQL\data\PayrollASP_Syngentatest.mdf',
MOVE 'PayrollASP_Log' TO 'C:\DataFiles\Microsoft SQL
Server\MSSQL\data\PayrollASP_Syngentatest_log.ldf'
Results are as follows:
LogicalName
PhysicalName
Type FileGroupName
Size MaxSize
----
---- --
----
----
----
-- -- ---
----
-- -- --
PayrollASP_Data
D:\DataFiles\Microsoft SQL Server\MSSQL\data\PayrollASP_Syngentatest.mdf
D PRIMARY
201326592 35184372080640
PayrollASP_Log
D:\DataFiles\Microsoft SQL Server\MSSQL\data\PayrollASP_Syngentatest_log.ldf
L NULL
121372672 35184372080640
(2 row(s) affected)
Server: Msg 5105, Level 16, State 2, Line 5
Device activation error. The physical file name 'C:\DataFiles\Microsoft SQL
Server\MSSQL\data\PayrollASP_Syngentatest.mdf' may be incorrect.
Server: Msg 3156, Level 16, State 1, Line 5
File 'PayrollASP_Data' cannot be restored to 'C:\DataFiles\Microsoft SQL
Server\MSSQL\data\PayrollASP_Syngentatest.mdf'. Use WITH MOVE to identify a
valid location for the file.
Server: Msg 5105, Level 16, State 1, Line 5
Device activation error. The physical file name 'C:\DataFiles\Microsoft SQL
Server\MSSQL\data\PayrollASP_Syngentatest_log.ldf' may be incorrect.
Server: Msg 3156, Level 16, State 1, Line 5
File 'PayrollASP_Log' cannot be restored to 'C:\DataFiles\Microsoft SQL
Server\MSSQL\data\PayrollASP_Syngentatest_log.ldf'. Use WITH MOVE to
identify a valid location for the file.
Server: Msg 3013, Level 16, State 1, Line 5
RESTORE DATABASE is terminating abnormally.
I even tried creating an empty database of the correct name first so that
the mdf and ldf files existed, then included the REPLACE keyword. No go I'm
afraid.
The main thing is that the DB's on the production server are on drive D:,
but the test server doesn't have a D: drive, hence the need to use the MOVE
keywords.
Thanks in advance for any help you may give.
Regards...Andrew|||Hi Andrew
Have you verified that the folder where you try to place the new data and
log files do exist? SQL can't create the folder on it's own, so it have to
exist before the RESTORE command can put the files there.
Regards
Steen
Andrew Hayes wrote:
> SQL command is as follows:
> RESTORE FILELISTONLY
> FROM DISK = 'C:\Program Files\Microsoft SQL
> Server\MSSQL\BACKUP\PayrollASP_Syngentatest.bak'
> RESTORE DATABASE PayrollASP_Syngentatest
> FROM DISK = 'C:\Program Files\Microsoft SQL
> Server\MSSQL\BACKUP\PayrollASP_Syngentatest.bak'
> WITH RECOVERY,
> MOVE 'PayrollASP_Data' TO 'C:\DataFiles\Microsoft SQL
> Server\MSSQL\data\PayrollASP_Syngentatest.mdf',
> MOVE 'PayrollASP_Log' TO 'C:\DataFiles\Microsoft SQL
> Server\MSSQL\data\PayrollASP_Syngentatest_log.ldf'
> Results are as follows:
> LogicalName
> PhysicalName
> Type FileGroupName
> Size MaxSize
> ----
--
> ----
> --
> ----
--
> ----
--
> ----
--
> -- --
> ---
> ----
--
> -- -- --
> PayrollASP_Data
> D:\DataFiles\Microsoft SQL
> Server\MSSQL\data\PayrollASP_Syngentatest.mdf
> D PRIMARY
> 201326592 35184372080640
> PayrollASP_Log
> D:\DataFiles\Microsoft SQL
> Server\MSSQL\data\PayrollASP_Syngentatest_log.ldf L
> NULL 121372672 35184372080640
> (2 row(s) affected)
> Server: Msg 5105, Level 16, State 2, Line 5
> Device activation error. The physical file name
> 'C:\DataFiles\Microsoft SQL
> Server\MSSQL\data\PayrollASP_Syngentatest.mdf' may be incorrect.
> Server: Msg 3156, Level 16, State 1, Line 5
> File 'PayrollASP_Data' cannot be restored to 'C:\DataFiles\Microsoft
> SQL Server\MSSQL\data\PayrollASP_Syngentatest.mdf'. Use WITH MOVE to
> identify a valid location for the file.
> Server: Msg 5105, Level 16, State 1, Line 5
> Device activation error. The physical file name
> 'C:\DataFiles\Microsoft SQL
> Server\MSSQL\data\PayrollASP_Syngentatest_log.ldf' may be incorrect.
> Server: Msg 3156, Level 16, State 1, Line 5
> File 'PayrollASP_Log' cannot be restored to 'C:\DataFiles\Microsoft
> SQL Server\MSSQL\data\PayrollASP_Syngentatest_log.ldf'. Use WITH MOVE
> to identify a valid location for the file.
> Server: Msg 3013, Level 16, State 1, Line 5
> RESTORE DATABASE is terminating abnormally.
> I even tried creating an empty database of the correct name first so
> that the mdf and ldf files existed, then included the REPLACE
> keyword. No go I'm afraid.
> The main thing is that the DB's on the production server are on drive
> D:, but the test server doesn't have a D: drive, hence the need to
> use the MOVE keywords.
> Thanks in advance for any help you may give.
> Regards...Andrew|||Yes. The folder exists, but since I didn't have time to mess about trying to
figure out why SQL Server wouldn't restore from a backup to a different
server with a different folder structure, I just generated the creation
scripts, ran those, and exported the data.
Thanks for the help though Steen. Not your fault SQL Server is finiky.
It would be nice if SQL Server, when it reports "Device activation error.
The physical file name '...' may be incorrect." was a little more
forthcoming with why it may be incorrect.
Maybe, "the folder doesn't exist and I can't create it", or "a file of the
same name already exists (and either couldn't be overwritten because it was
read-only, or the [whoever] account used by this operation does not have the
appropriate access rights."
Maybe verbose error reporting is a feature of SQL Server 2005? One can but
hope.
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:ewQ2EHrNFHA.3220@.TK2MSFTNGP14.phx.gbl...
> Hi Andrew
> Have you verified that the folder where you try to place the new data and
> log files do exist? SQL can't create the folder on it's own, so it have to
> exist before the RESTORE command can put the files there.
> Regards
> Steen
> Andrew Hayes wrote:
> > SQL command is as follows:
> >
> > RESTORE FILELISTONLY
> > FROM DISK = 'C:\Program Files\Microsoft SQL
> > Server\MSSQL\BACKUP\PayrollASP_Syngentatest.bak'
> >
> > RESTORE DATABASE PayrollASP_Syngentatest
> > FROM DISK = 'C:\Program Files\Microsoft SQL
> > Server\MSSQL\BACKUP\PayrollASP_Syngentatest.bak'
> > WITH RECOVERY,
> > MOVE 'PayrollASP_Data' TO 'C:\DataFiles\Microsoft SQL
> > Server\MSSQL\data\PayrollASP_Syngentatest.mdf',
> > MOVE 'PayrollASP_Log' TO 'C:\DataFiles\Microsoft SQL
> > Server\MSSQL\data\PayrollASP_Syngentatest_log.ldf'
> >
> > Results are as follows:
> >
> > LogicalName
> > PhysicalName
> > Type FileGroupName
> > Size MaxSize
> ----
> --
> > ----
> > --
> ----
> --
> ----
> --
> ----
> --
> > -- --
> > ---
> ----
> --
> > -- -- --
> > PayrollASP_Data
> > D:\DataFiles\Microsoft SQL
> > Server\MSSQL\data\PayrollASP_Syngentatest.mdf
> >
> > D PRIMARY
> > 201326592 35184372080640
> > PayrollASP_Log
> > D:\DataFiles\Microsoft SQL
> > Server\MSSQL\data\PayrollASP_Syngentatest_log.ldf L
> > NULL 121372672 35184372080640
> >
> > (2 row(s) affected)
> >
> > Server: Msg 5105, Level 16, State 2, Line 5
> > Device activation error. The physical file name
> > 'C:\DataFiles\Microsoft SQL
> > Server\MSSQL\data\PayrollASP_Syngentatest.mdf' may be incorrect.
> > Server: Msg 3156, Level 16, State 1, Line 5
> > File 'PayrollASP_Data' cannot be restored to 'C:\DataFiles\Microsoft
> > SQL Server\MSSQL\data\PayrollASP_Syngentatest.mdf'. Use WITH MOVE to
> > identify a valid location for the file.
> > Server: Msg 5105, Level 16, State 1, Line 5
> > Device activation error. The physical file name
> > 'C:\DataFiles\Microsoft SQL
> > Server\MSSQL\data\PayrollASP_Syngentatest_log.ldf' may be incorrect.
> > Server: Msg 3156, Level 16, State 1, Line 5
> > File 'PayrollASP_Log' cannot be restored to 'C:\DataFiles\Microsoft
> > SQL Server\MSSQL\data\PayrollASP_Syngentatest_log.ldf'. Use WITH MOVE
> > to identify a valid location for the file.
> > Server: Msg 3013, Level 16, State 1, Line 5
> > RESTORE DATABASE is terminating abnormally.
> >
> > I even tried creating an empty database of the correct name first so
> > that the mdf and ldf files existed, then included the REPLACE
> > keyword. No go I'm afraid.
> >
> > The main thing is that the DB's on the production server are on drive
> > D:, but the test server doesn't have a D: drive, hence the need to
> > use the MOVE keywords.
> >
> > Thanks in advance for any help you may give.
> >
> > Regards...Andrew
>|||Hi Andrew
I agree that the SQL server error messages isn't always very clear - but
isn't that a problem in every software program...;-).
/Steen
Andrew Hayes wrote:
> Yes. The folder exists, but since I didn't have time to mess about
> trying to figure out why SQL Server wouldn't restore from a backup to
> a different server with a different folder structure, I just
> generated the creation scripts, ran those, and exported the data.
> Thanks for the help though Steen. Not your fault SQL Server is finiky.
> It would be nice if SQL Server, when it reports "Device activation
> error. The physical file name '...' may be incorrect." was a little
> more forthcoming with why it may be incorrect.
> Maybe, "the folder doesn't exist and I can't create it", or "a file
> of the same name already exists (and either couldn't be overwritten
> because it was read-only, or the [whoever] account used by this
> operation does not have the appropriate access rights."
> Maybe verbose error reporting is a feature of SQL Server 2005? One
> can but hope.
> "Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
> news:ewQ2EHrNFHA.3220@.TK2MSFTNGP14.phx.gbl...
>> Hi Andrew
>> Have you verified that the folder where you try to place the new
>> data and log files do exist? SQL can't create the folder on it's
>> own, so it have to exist before the RESTORE command can put the
>> files there.
>> Regards
>> Steen
>> Andrew Hayes wrote:
>> SQL command is as follows:
>> RESTORE FILELISTONLY
>> FROM DISK = 'C:\Program Files\Microsoft SQL
>> Server\MSSQL\BACKUP\PayrollASP_Syngentatest.bak'
>> RESTORE DATABASE PayrollASP_Syngentatest
>> FROM DISK = 'C:\Program Files\Microsoft SQL
>> Server\MSSQL\BACKUP\PayrollASP_Syngentatest.bak'
>> WITH RECOVERY,
>> MOVE 'PayrollASP_Data' TO 'C:\DataFiles\Microsoft SQL
>> Server\MSSQL\data\PayrollASP_Syngentatest.mdf',
>> MOVE 'PayrollASP_Log' TO 'C:\DataFiles\Microsoft SQL
>> Server\MSSQL\data\PayrollASP_Syngentatest_log.ldf'
>> Results are as follows:
>> LogicalName
>> PhysicalName
>> Type FileGroupName
>> Size MaxSize
>> ----
-
>> --
>> ----
>> --
>> ----
-
>> --
>> ----
-
>> --
>> ----
-
>> --
>> -- --
>> ---
>> ----
-
>> --
>> -- -- --
>> PayrollASP_Data
>> D:\DataFiles\Microsoft SQL
>> Server\MSSQL\data\PayrollASP_Syngentatest.mdf
>> D PRIMARY
>> 201326592 35184372080640
>> PayrollASP_Log
>> D:\DataFiles\Microsoft SQL
>> Server\MSSQL\data\PayrollASP_Syngentatest_log.ldf L
>> NULL 121372672 35184372080640
>> (2 row(s) affected)
>> Server: Msg 5105, Level 16, State 2, Line 5
>> Device activation error. The physical file name
>> 'C:\DataFiles\Microsoft SQL
>> Server\MSSQL\data\PayrollASP_Syngentatest.mdf' may be incorrect.
>> Server: Msg 3156, Level 16, State 1, Line 5
>> File 'PayrollASP_Data' cannot be restored to 'C:\DataFiles\Microsoft
>> SQL Server\MSSQL\data\PayrollASP_Syngentatest.mdf'. Use WITH MOVE to
>> identify a valid location for the file.
>> Server: Msg 5105, Level 16, State 1, Line 5
>> Device activation error. The physical file name
>> 'C:\DataFiles\Microsoft SQL
>> Server\MSSQL\data\PayrollASP_Syngentatest_log.ldf' may be incorrect.
>> Server: Msg 3156, Level 16, State 1, Line 5
>> File 'PayrollASP_Log' cannot be restored to 'C:\DataFiles\Microsoft
>> SQL Server\MSSQL\data\PayrollASP_Syngentatest_log.ldf'. Use WITH
>> MOVE to identify a valid location for the file.
>> Server: Msg 3013, Level 16, State 1, Line 5
>> RESTORE DATABASE is terminating abnormally.
>> I even tried creating an empty database of the correct name first so
>> that the mdf and ldf files existed, then included the REPLACE
>> keyword. No go I'm afraid.
>> The main thing is that the DB's on the production server are on
>> drive D:, but the test server doesn't have a D: drive, hence the
>> need to use the MOVE keywords.
>> Thanks in advance for any help you may give.
>> Regards...Andrew

Copy Database Between Servers

Thursday, March 8, 2012

Copy data and objects between SQL Servers

I have recently moved from a Microsoft SQL Server 2000 to a SQL Server
2005.

In the good old Enterprise Manager, when I imported a table from a SQL
server to another, I could choose "Copy objects and data between SQL
Server
databases". When choosen, all primary keys, and default values was
copied.

My question is:
Is it possible to do the same in Microsoft SQL Server Management Studio
with
SQL Server 2005? And how do I do it?

I have tried using the "SELECT * INTO NewTable FROM OldTable"
statement, but
it just drops all information about primary keys and default values.

Best Regards

The functionality you refer to is now part of SSIS (SQL Server Integration Services). You can access it from the Management Studio in a number of ways. The simplest is to right click on the database you want to import the data into, select all tasks, then select import data...

If you didn't install SSIS with the Database Engine, I'm not sure if you'll be able to access the import functionalty.

See this article on msdn for more info.

Friday, February 10, 2012

Converting MS query to SQL Server 2000

I am struggling rewriting my query from MS Access' IIF, Then to SQL
Servers TSQL language. I am hoping osme one can give me some
guidance. I believe I have the first portion of the query correct but
do belive this requires a "NESTED" argument. This si where I am lost.
My Original MS ACCESS Query reads--
SELECT DISTINCTROW REGION_TRAFIC.*,
IIf(Mid([SWITCH CLLI],5,2)=[TERM STATE],
IIf([CARRIER]="VENDOR4",[DOMESTIC LD RATES]![INTRA_VENDOR4],
IIf([CARRIER]="VENDOR3",[DOMESTIC LD RATES]![INTRA_VENDOR3],
IIf([CARRIER]="VENDOR2",[DOMESTIC LD RATES]![INTRA_VENDOR2],
IIf([Carrier]="VENDOR1",[DOMESTIC LD
RATES]![INTRA_VENDOR1])))),
IIf([CARRIER]="VENDOR4",[DOMESTIC LD RATES]![INTER_VENDOR4],
IIf([CARRIER]="VENDOR3",[DOMESTIC LD RATES]![INTER_VENDOR3],
IIf([CARRIER]="VENDOR2",[DOMESTIC LD RATES]![INTER_VENDOR2],
IIf([Carrier]="VENDOR1",[DOMESTIC LD
RATES]![INTER_VENDOR1]))))) AS CPM,
[CPM]*[MOU] AS COST
INTO INTRALATA_LD
FROM REGION_TRAFIC LEFT JOIN [DOMESTIC LD RATES] ON
REGION_TRAFIC.RATEKEY = [DOMESTIC LD RATES].RATEKEY
WHERE (((REGION_TRAFIC.[TERM LATA])=[REGION_TRAFIC]![LATA]))
ORDER BY REGION_TRAFIC.[TERM LATA] DESC;
I have tried to re-write this in SQL SERVER as --
SELET DISTINCT REGION TRAFIC.*,
CASE
WHEN [CARRIER]="VENDOR4" THEN [DOMESTIC LD
RATES].INTRA_VENDOR4
WHEN [CARRIER]="VENDOR3" THEN [DOMESTIC LD
RATES].INTRA_VENDOR3
WHEN [CARRIER]="VENDOR2" THEN [DOMESTIC LD
RATES].INTRA_VENDOR2
WHEN [CARRIER]="VENDOR1" THEN [DOMESTIC LD
RATES].INTRA_VENDOR1
ELSE
WHEN [CARRIER]="VENDOR4" THEN [DOMESTIC LD
RATES].INTER_VENDOR4
WHEN [CARRIER]="VENDOR3" THEN [DOMESTIC LD
RATES].INTER_VENDOR3
WHEN [CARRIER]="VENDOR2" THEN [DOMESTIC LD
RATES].INTER_VENDOR2
WHEN [CARRIER]="VENDOR1" THEN [DOMESTIC LD
RATES].INTER_VENDOR1
END
AS CPM
CPM*MOU AS COST
INTO INTRALATA_LD
FROM REGION_TRAFIC LEFT JOIN [DOMESTIC LD RATES] ON
REGION_TRAFIC.RATEKEY = [DOMESTIC LD RATES].RATEKEY
WHERE (((REGION_TRAFIC.[TERM LATA])=[REGION_TRAFIC]![LATA]))
ORDER BY REGION_TRAFIC.[TERM LATA] DESC
My cahllenge is the Case portion of the query and the nesting! I
amnot sure if I hae the correc syntax or even chose the correct
argument for my purpose.
Any guidance is apprecaited.DISTINCTROW becomes DISTINCT, MID becomes SUBSTRING, IIF becomes CASE, !
becomes .
> My cahllenge is the Case portion of the query and the nesting! I
> amnot sure if I hae the correc syntax or even chose the correct
> argument for my purpose.
Well, are you getting an error message, the wrong information, ...?
--
http://www.aspfaq.com/
(Reverse address to reply.)
"wiredog" <wiredog@.comcast.net> wrote in message
news:b3af3ec6.0406291612.73be51bc@.posting.google.com...
>I am struggling rewriting my query from MS Access' IIF, Then to SQL
> Servers TSQL language. I am hoping osme one can give me some
> guidance. I believe I have the first portion of the query correct but
> do belive this requires a "NESTED" argument. This si where I am lost.
> My Original MS ACCESS Query reads--
> SELECT DISTINCTROW REGION_TRAFIC.*,
> IIf(Mid([SWITCH CLLI],5,2)=[TERM STATE],
> IIf([CARRIER]="VENDOR4",[DOMESTIC LD RATES]![INTRA_VENDOR4],
> IIf([CARRIER]="VENDOR3",[DOMESTIC LD RATES]![INTRA_VENDOR3],
> IIf([CARRIER]="VENDOR2",[DOMESTIC LD RATES]![INTRA_VENDOR2],
> IIf([Carrier]="VENDOR1",[DOMESTIC LD
> RATES]![INTRA_VENDOR1])))),
> IIf([CARRIER]="VENDOR4",[DOMESTIC LD RATES]![INTER_VENDOR4],
> IIf([CARRIER]="VENDOR3",[DOMESTIC LD RATES]![INTER_VENDOR3],
> IIf([CARRIER]="VENDOR2",[DOMESTIC LD RATES]![INTER_VENDOR2],
> IIf([Carrier]="VENDOR1",[DOMESTIC LD
> RATES]![INTER_VENDOR1]))))) AS CPM,
> [CPM]*[MOU] AS COST
> INTO INTRALATA_LD
> FROM REGION_TRAFIC LEFT JOIN [DOMESTIC LD RATES] ON
> REGION_TRAFIC.RATEKEY = [DOMESTIC LD RATES].RATEKEY
> WHERE (((REGION_TRAFIC.[TERM LATA])=[REGION_TRAFIC]![LATA]))
> ORDER BY REGION_TRAFIC.[TERM LATA] DESC;
> I have tried to re-write this in SQL SERVER as --
> SELET DISTINCT REGION TRAFIC.*,
> CASE
> WHEN [CARRIER]="VENDOR4" THEN [DOMESTIC LD
> RATES].INTRA_VENDOR4
> WHEN [CARRIER]="VENDOR3" THEN [DOMESTIC LD
> RATES].INTRA_VENDOR3
> WHEN [CARRIER]="VENDOR2" THEN [DOMESTIC LD
> RATES].INTRA_VENDOR2
> WHEN [CARRIER]="VENDOR1" THEN [DOMESTIC LD
> RATES].INTRA_VENDOR1
> ELSE
> WHEN [CARRIER]="VENDOR4" THEN [DOMESTIC LD
> RATES].INTER_VENDOR4
> WHEN [CARRIER]="VENDOR3" THEN [DOMESTIC LD
> RATES].INTER_VENDOR3
> WHEN [CARRIER]="VENDOR2" THEN [DOMESTIC LD
> RATES].INTER_VENDOR2
> WHEN [CARRIER]="VENDOR1" THEN [DOMESTIC LD
> RATES].INTER_VENDOR1
> END
> AS CPM
> CPM*MOU AS COST
> INTO INTRALATA_LD
> FROM REGION_TRAFIC LEFT JOIN [DOMESTIC LD RATES] ON
> REGION_TRAFIC.RATEKEY = [DOMESTIC LD RATES].RATEKEY
> WHERE (((REGION_TRAFIC.[TERM LATA])=[REGION_TRAFIC]![LATA]))
> ORDER BY REGION_TRAFIC.[TERM LATA] DESC
> My cahllenge is the Case portion of the query and the nesting! I
> amnot sure if I hae the correc syntax or even chose the correct
> argument for my purpose.
> Any guidance is apprecaited.|||First issue is the need to bracket REGION TRAFFIC you have a space there and
T-SQL does not like that. If the table name has a "_" then put that in.
In your WHERE clause you need to replace [REGION_TRAFIC]![LATA] with
[REGION_TRAFIC].[LATA] or REGION_TRAFIC.LATA
As far as whether or not you are choosing the proper function you need to
post the DDL and what it is you wish to accomplish.
--
Andrew C. Madsen
Information Architect
Harley-Davidson Motor Company
"wiredog" <wiredog@.comcast.net> wrote in message
news:b3af3ec6.0406291612.73be51bc@.posting.google.com...
> I am struggling rewriting my query from MS Access' IIF, Then to SQL
> Servers TSQL language. I am hoping osme one can give me some
> guidance. I believe I have the first portion of the query correct but
> do belive this requires a "NESTED" argument. This si where I am lost.
> My Original MS ACCESS Query reads--
> SELECT DISTINCTROW REGION_TRAFIC.*,
> IIf(Mid([SWITCH CLLI],5,2)=[TERM STATE],
> IIf([CARRIER]="VENDOR4",[DOMESTIC LD RATES]![INTRA_VENDOR4],
> IIf([CARRIER]="VENDOR3",[DOMESTIC LD RATES]![INTRA_VENDOR3],
> IIf([CARRIER]="VENDOR2",[DOMESTIC LD RATES]![INTRA_VENDOR2],
> IIf([Carrier]="VENDOR1",[DOMESTIC LD
> RATES]![INTRA_VENDOR1])))),
> IIf([CARRIER]="VENDOR4",[DOMESTIC LD RATES]![INTER_VENDOR4],
> IIf([CARRIER]="VENDOR3",[DOMESTIC LD RATES]![INTER_VENDOR3],
> IIf([CARRIER]="VENDOR2",[DOMESTIC LD RATES]![INTER_VENDOR2],
> IIf([Carrier]="VENDOR1",[DOMESTIC LD
> RATES]![INTER_VENDOR1]))))) AS CPM,
> [CPM]*[MOU] AS COST
> INTO INTRALATA_LD
> FROM REGION_TRAFIC LEFT JOIN [DOMESTIC LD RATES] ON
> REGION_TRAFIC.RATEKEY = [DOMESTIC LD RATES].RATEKEY
> WHERE (((REGION_TRAFIC.[TERM LATA])=[REGION_TRAFIC]![LATA]))
> ORDER BY REGION_TRAFIC.[TERM LATA] DESC;
> I have tried to re-write this in SQL SERVER as --
> SELET DISTINCT REGION TRAFIC.*,
> CASE
> WHEN [CARRIER]="VENDOR4" THEN [DOMESTIC LD
> RATES].INTRA_VENDOR4
> WHEN [CARRIER]="VENDOR3" THEN [DOMESTIC LD
> RATES].INTRA_VENDOR3
> WHEN [CARRIER]="VENDOR2" THEN [DOMESTIC LD
> RATES].INTRA_VENDOR2
> WHEN [CARRIER]="VENDOR1" THEN [DOMESTIC LD
> RATES].INTRA_VENDOR1
> ELSE
> WHEN [CARRIER]="VENDOR4" THEN [DOMESTIC LD
> RATES].INTER_VENDOR4
> WHEN [CARRIER]="VENDOR3" THEN [DOMESTIC LD
> RATES].INTER_VENDOR3
> WHEN [CARRIER]="VENDOR2" THEN [DOMESTIC LD
> RATES].INTER_VENDOR2
> WHEN [CARRIER]="VENDOR1" THEN [DOMESTIC LD
> RATES].INTER_VENDOR1
> END
> AS CPM
> CPM*MOU AS COST
> INTO INTRALATA_LD
> FROM REGION_TRAFIC LEFT JOIN [DOMESTIC LD RATES] ON
> REGION_TRAFIC.RATEKEY = [DOMESTIC LD RATES].RATEKEY
> WHERE (((REGION_TRAFIC.[TERM LATA])=[REGION_TRAFIC]![LATA]))
> ORDER BY REGION_TRAFIC.[TERM LATA] DESC
> My cahllenge is the Case portion of the query and the nesting! I
> amnot sure if I hae the correc syntax or even chose the correct
> argument for my purpose.
> Any guidance is apprecaited.|||wiredog, try this:
SELECT RT.*
, CASE
WHEN SUBSTRING(Switch_CLLI,5,2) = "TERM STATE" THEN
CASE Carrier
WHEN "VENDOR4" THEN DLR.Intra_Vendor4
WHEN "VENDOR3" THEN DLR.Intra_Vendor3
WHEN "VENDOR2" THEN DLR.Intra_Vendor2
WHEN "VENDOR1" THEN DLR.Intra_Vendor1
-- ELSE '?
END
ELSE
CASE Carrier
WHEN "VENDOR4" THEN DLR.Inter_Vendor4
WHEN "VENDOR3" THEN DLR.Inter_Vendor3
WHEN "VENDOR2" THEN DLR.Inter_Vendor2
WHEN "VENDOR1" THEN DLR.Inter_Vendor1
-- ELSE '
END
END AS CPM
, CPM * MOU AS Cost
INTO INTRALATA_LD
FROM REGION_TRAFIC RT
LEFT JOIN "DOMESTIC LD RATES" DLR
ON RT.RATEKEY = DLR.RATEKEY
WHERE RT."TERM LATA" = RT.LATA
ORDER BY RT."TERM LATA" DESC
Hope this helps,
Gert-Jan
wiredog wrote:
> I am struggling rewriting my query from MS Access' IIF, Then to SQL
> Servers TSQL language. I am hoping osme one can give me some
> guidance. I believe I have the first portion of the query correct but
> do belive this requires a "NESTED" argument. This si where I am lost.
> My Original MS ACCESS Query reads--
> SELECT DISTINCTROW REGION_TRAFIC.*,
> IIf(Mid([SWITCH CLLI],5,2)=[TERM STATE],
> IIf([CARRIER]="VENDOR4",[DOMESTIC LD RATES]![INTRA_VENDOR4],
> IIf([CARRIER]="VENDOR3",[DOMESTIC LD RATES]![INTRA_VENDOR3],
> IIf([CARRIER]="VENDOR2",[DOMESTIC LD RATES]![INTRA_VENDOR2],
> IIf([Carrier]="VENDOR1",[DOMESTIC LD
> RATES]![INTRA_VENDOR1])))),
> IIf([CARRIER]="VENDOR4",[DOMESTIC LD RATES]![INTER_VENDOR4],
> IIf([CARRIER]="VENDOR3",[DOMESTIC LD RATES]![INTER_VENDOR3],
> IIf([CARRIER]="VENDOR2",[DOMESTIC LD RATES]![INTER_VENDOR2],
> IIf([Carrier]="VENDOR1",[DOMESTIC LD
> RATES]![INTER_VENDOR1]))))) AS CPM,
> [CPM]*[MOU] AS COST
> INTO INTRALATA_LD
> FROM REGION_TRAFIC LEFT JOIN [DOMESTIC LD RATES] ON
> REGION_TRAFIC.RATEKEY = [DOMESTIC LD RATES].RATEKEY
> WHERE (((REGION_TRAFIC.[TERM LATA])=[REGION_TRAFIC]![LATA]))
> ORDER BY REGION_TRAFIC.[TERM LATA] DESC;
> I have tried to re-write this in SQL SERVER as --
> SELET DISTINCT REGION TRAFIC.*,
> CASE
> WHEN [CARRIER]="VENDOR4" THEN [DOMESTIC LD
> RATES].INTRA_VENDOR4
> WHEN [CARRIER]="VENDOR3" THEN [DOMESTIC LD
> RATES].INTRA_VENDOR3
> WHEN [CARRIER]="VENDOR2" THEN [DOMESTIC LD
> RATES].INTRA_VENDOR2
> WHEN [CARRIER]="VENDOR1" THEN [DOMESTIC LD
> RATES].INTRA_VENDOR1
> ELSE
> WHEN [CARRIER]="VENDOR4" THEN [DOMESTIC LD
> RATES].INTER_VENDOR4
> WHEN [CARRIER]="VENDOR3" THEN [DOMESTIC LD
> RATES].INTER_VENDOR3
> WHEN [CARRIER]="VENDOR2" THEN [DOMESTIC LD
> RATES].INTER_VENDOR2
> WHEN [CARRIER]="VENDOR1" THEN [DOMESTIC LD
> RATES].INTER_VENDOR1
> END
> AS CPM
> CPM*MOU AS COST
> INTO INTRALATA_LD
> FROM REGION_TRAFIC LEFT JOIN [DOMESTIC LD RATES] ON
> REGION_TRAFIC.RATEKEY = [DOMESTIC LD RATES].RATEKEY
> WHERE (((REGION_TRAFIC.[TERM LATA])=[REGION_TRAFIC]![LATA]))
> ORDER BY REGION_TRAFIC.[TERM LATA] DESC
> My cahllenge is the Case portion of the query and the nesting! I
> amnot sure if I hae the correc syntax or even chose the correct
> argument for my purpose.
> Any guidance is apprecaited.
--
(Please reply only to the newsgroup)

Thursday, March 29, 2012

copy many ssis packages between servers

copy many ssis packages between servers

copy many ssis packages between servers

Copy maintenance plan to two different servers

Copy maintenance plan to two different servers

Tuesday, March 27, 2012

Copy job from one server to another

copy files

copy files

copy files

copy error

Sunday, March 25, 2012

copy databases

Tuesday, March 20, 2012

Copy Database Wizard

Monday, March 19, 2012

copy database tables

Copy database from one server to other

Copy database from one server to other

Sunday, March 11, 2012

Copy Database Between Servers

Copy Database Between Servers

Copy Database Between Servers

Thursday, March 8, 2012

Copy data and objects between SQL Servers

Friday, February 10, 2012

Converting MS query to SQL Server 2000

Converting problem

Blog Archive

About Me