how do I copy a table, structure only from one database to another? I select copy in the table view of one database, but then paste is not available to me in table view of the other database. I am transitioning two db tables from a development web server to the production server, in order to go live... I don't want to have to type all of the design in the new table dialog!
Please help quickly!From Enterprise Manager if you left-click the tablename and under the menu option All Tasks you will see anoth menu option called Emport Data... . This will allow you to export the data and the table structure (if the destination is SQL Server).|||the quickest way to do it is to go into enterprise manager, and click (single click) on the table that must have its structure copied. press Ctrl-C (ie copy), open query analyser, select the database in which the new table must be created and press Ctrl-V (ie paste). The sql code for creating the table should now appear.
This works in SQL Server 7. I have not tried it in 2000.|||For Generating SQL Script:
1. You can select whatever tables you want in Enterprise manager
2. Right click and then in "All Tasks" select "Generate SQL Script"
This is the correct way of generating SQL Script for any objects.
Have fun.
Varad01
Showing posts with label view. Show all posts
Showing posts with label view. Show all posts
Sunday, March 25, 2012
Sunday, March 11, 2012
Copy Database From 2000 To 2005 Failing To Create View
I have 180+ databases on a production SQL Server 2000 system that I would
like to copy over to a new SQL Server 2005 system for testing prior to
rolling out a new database server.
Unfortunately, the Copy Database wizard using SQL MO fails. The event log
shows that it could not create a view because of an "invalid object name".
This, I assume, is because the Dependencies are not set so the SQLISPackage
doesn't know which order to create the objects. That is, the databases use
views that did not specify SCHEMABINDING when they were created, so when
using View Dependencies, the view only shows itself, even if the view query
makes use of other views/tables.
Is there a way to stop this check from occuring, so that the view is created
regardless of whether a view dependency has been fulfilled?
Andrew
Have you tried to BACKUP all user databases and then RESTORE on SQL Server
2005?
"Andrew Hayes" <AndrewHayes@.discussions.microsoft.com> wrote in message
news:F323D970-8FC8-4CBB-8418-3137CD980046@.microsoft.com...
>I have 180+ databases on a production SQL Server 2000 system that I would
> like to copy over to a new SQL Server 2005 system for testing prior to
> rolling out a new database server.
> Unfortunately, the Copy Database wizard using SQL MO fails. The event log
> shows that it could not create a view because of an "invalid object name".
> This, I assume, is because the Dependencies are not set so the
> SQLISPackage
> doesn't know which order to create the objects. That is, the databases use
> views that did not specify SCHEMABINDING when they were created, so when
> using View Dependencies, the view only shows itself, even if the view
> query
> makes use of other views/tables.
> Is there a way to stop this check from occuring, so that the view is
> created
> regardless of whether a view dependency has been fulfilled?
|||I was hoping to avoid that. Performing a backup and restore of 180+ databases
is going to take some time...
"Uri Dimant" wrote:
> Andrew
> Have you tried to BACKUP all user databases and then RESTORE on SQL Server
> 2005?
>
|||Andrew
Well , I don't think so , especially comparing it with copy wizard ,but try
detach\attach , for more details please refer to the BOL
"Andrew Hayes" <AndrewHayes@.discussions.microsoft.com> wrote in message
news:152BB668-35A7-4698-8379-438DB8DA3157@.microsoft.com...
>I was hoping to avoid that. Performing a backup and restore of 180+
>databases
> is going to take some time...
> "Uri Dimant" wrote:
>
|||On Feb 27, 5:06 am, Andrew Hayes
<AndrewHa...@.discussions.microsoft.com> wrote:
> I was hoping to avoid that. Performing a backup and restore of 180+ databases
> is going to take some time...
>
Backup/restore will be MUCH faster than the Copy Database wizard.
It's trivial to write a script to loop through sysdatabases to run a
BACKUP command for each database.
|||Uri - Can't use Detach/Attach since it's a production server in use 24/7. Or
if I do, it means spending several weeks doing it so I can minimise the
impact on our customers.
Tracy - OK. So I write a script to backup every database from the source
server, copy each backup file over the WAN to the destination server and then
perform a restore, changing the file locations for each database MDF. That
would probably take several days.
In either case, it doesn't resolve the issue that the Copy Database Wizard
fails to create views because it's performing a logic/object check during the
copy. Why can't I just turn that off?
|||On Feb 27, 7:36 am, Andrew Hayes
<AndrewHa...@.discussions.microsoft.com> wrote:
> Uri - Can't use Detach/Attach since it's a production server in use 24/7. Or
> if I do, it means spending several weeks doing it so I can minimise the
> impact on our customers.
> Tracy - OK. So I write a script to backup every database from the source
> server, copy each backup file over the WAN to the destination server and then
> perform a restore, changing the file locations for each database MDF. That
> would probably take several days.
> In either case, it doesn't resolve the issue that the Copy Database Wizard
> fails to create views because it's performing a logic/object check during the
> copy. Why can't I just turn that off?
How long do you think it's going to take the copy wizard to complete?
The first thing that gizmo does is script all of the objects in your
source database, run those scripts on the destination database (to
create the objects), and then imports/exports the data from the source
to the destination. That is going to take twice as long as going the
backup/restore route. Trust me, backup/restore is the way to go, you
can AUTOMATE the whole thing, and you have FULL CONTROL over what's
happening.
If you insist on using the wizard, I'm afraid I can't help you out. I
don't use it, it's just too klunky to work with.
like to copy over to a new SQL Server 2005 system for testing prior to
rolling out a new database server.
Unfortunately, the Copy Database wizard using SQL MO fails. The event log
shows that it could not create a view because of an "invalid object name".
This, I assume, is because the Dependencies are not set so the SQLISPackage
doesn't know which order to create the objects. That is, the databases use
views that did not specify SCHEMABINDING when they were created, so when
using View Dependencies, the view only shows itself, even if the view query
makes use of other views/tables.
Is there a way to stop this check from occuring, so that the view is created
regardless of whether a view dependency has been fulfilled?
Andrew
Have you tried to BACKUP all user databases and then RESTORE on SQL Server
2005?
"Andrew Hayes" <AndrewHayes@.discussions.microsoft.com> wrote in message
news:F323D970-8FC8-4CBB-8418-3137CD980046@.microsoft.com...
>I have 180+ databases on a production SQL Server 2000 system that I would
> like to copy over to a new SQL Server 2005 system for testing prior to
> rolling out a new database server.
> Unfortunately, the Copy Database wizard using SQL MO fails. The event log
> shows that it could not create a view because of an "invalid object name".
> This, I assume, is because the Dependencies are not set so the
> SQLISPackage
> doesn't know which order to create the objects. That is, the databases use
> views that did not specify SCHEMABINDING when they were created, so when
> using View Dependencies, the view only shows itself, even if the view
> query
> makes use of other views/tables.
> Is there a way to stop this check from occuring, so that the view is
> created
> regardless of whether a view dependency has been fulfilled?
|||I was hoping to avoid that. Performing a backup and restore of 180+ databases
is going to take some time...
"Uri Dimant" wrote:
> Andrew
> Have you tried to BACKUP all user databases and then RESTORE on SQL Server
> 2005?
>
|||Andrew
Well , I don't think so , especially comparing it with copy wizard ,but try
detach\attach , for more details please refer to the BOL
"Andrew Hayes" <AndrewHayes@.discussions.microsoft.com> wrote in message
news:152BB668-35A7-4698-8379-438DB8DA3157@.microsoft.com...
>I was hoping to avoid that. Performing a backup and restore of 180+
>databases
> is going to take some time...
> "Uri Dimant" wrote:
>
|||On Feb 27, 5:06 am, Andrew Hayes
<AndrewHa...@.discussions.microsoft.com> wrote:
> I was hoping to avoid that. Performing a backup and restore of 180+ databases
> is going to take some time...
>
Backup/restore will be MUCH faster than the Copy Database wizard.
It's trivial to write a script to loop through sysdatabases to run a
BACKUP command for each database.
|||Uri - Can't use Detach/Attach since it's a production server in use 24/7. Or
if I do, it means spending several weeks doing it so I can minimise the
impact on our customers.
Tracy - OK. So I write a script to backup every database from the source
server, copy each backup file over the WAN to the destination server and then
perform a restore, changing the file locations for each database MDF. That
would probably take several days.
In either case, it doesn't resolve the issue that the Copy Database Wizard
fails to create views because it's performing a logic/object check during the
copy. Why can't I just turn that off?
|||On Feb 27, 7:36 am, Andrew Hayes
<AndrewHa...@.discussions.microsoft.com> wrote:
> Uri - Can't use Detach/Attach since it's a production server in use 24/7. Or
> if I do, it means spending several weeks doing it so I can minimise the
> impact on our customers.
> Tracy - OK. So I write a script to backup every database from the source
> server, copy each backup file over the WAN to the destination server and then
> perform a restore, changing the file locations for each database MDF. That
> would probably take several days.
> In either case, it doesn't resolve the issue that the Copy Database Wizard
> fails to create views because it's performing a logic/object check during the
> copy. Why can't I just turn that off?
How long do you think it's going to take the copy wizard to complete?
The first thing that gizmo does is script all of the objects in your
source database, run those scripts on the destination database (to
create the objects), and then imports/exports the data from the source
to the destination. That is going to take twice as long as going the
backup/restore route. Trust me, backup/restore is the way to go, you
can AUTOMATE the whole thing, and you have FULL CONTROL over what's
happening.
If you insist on using the wizard, I'm afraid I can't help you out. I
don't use it, it's just too klunky to work with.
Copy Database From 2000 To 2005 Failing To Create View
I have 180+ databases on a production SQL Server 2000 system that I would
like to copy over to a new SQL Server 2005 system for testing prior to
rolling out a new database server.
Unfortunately, the Copy Database wizard using SQL MO fails. The event log
shows that it could not create a view because of an "invalid object name".
This, I assume, is because the Dependencies are not set so the SQLISPackage
doesn't know which order to create the objects. That is, the databases use
views that did not specify SCHEMABINDING when they were created, so when
using View Dependencies, the view only shows itself, even if the view query
makes use of other views/tables.
Is there a way to stop this check from occuring, so that the view is created
regardless of whether a view dependency has been fulfilled?Andrew
Have you tried to BACKUP all user databases and then RESTORE on SQL Server
2005?
"Andrew Hayes" <AndrewHayes@.discussions.microsoft.com> wrote in message
news:F323D970-8FC8-4CBB-8418-3137CD980046@.microsoft.com...
>I have 180+ databases on a production SQL Server 2000 system that I would
> like to copy over to a new SQL Server 2005 system for testing prior to
> rolling out a new database server.
> Unfortunately, the Copy Database wizard using SQL MO fails. The event log
> shows that it could not create a view because of an "invalid object name".
> This, I assume, is because the Dependencies are not set so the
> SQLISPackage
> doesn't know which order to create the objects. That is, the databases use
> views that did not specify SCHEMABINDING when they were created, so when
> using View Dependencies, the view only shows itself, even if the view
> query
> makes use of other views/tables.
> Is there a way to stop this check from occuring, so that the view is
> created
> regardless of whether a view dependency has been fulfilled?|||I was hoping to avoid that. Performing a backup and restore of 180+ database
s
is going to take some time...
"Uri Dimant" wrote:
> Andrew
> Have you tried to BACKUP all user databases and then RESTORE on SQL Serve
r
> 2005?
>|||Andrew
Well , I don't think so , especially comparing it with copy wizard ,but try
detach\attach , for more details please refer to the BOL
"Andrew Hayes" <AndrewHayes@.discussions.microsoft.com> wrote in message
news:152BB668-35A7-4698-8379-438DB8DA3157@.microsoft.com...
>I was hoping to avoid that. Performing a backup and restore of 180+
>databases
> is going to take some time...
> "Uri Dimant" wrote:
>
>|||On Feb 27, 5:06 am, Andrew Hayes
<AndrewHa...@.discussions.microsoft.com> wrote:
> I was hoping to avoid that. Performing a backup and restore of 180+ databa
ses
> is going to take some time...
>
Backup/restore will be MUCH faster than the Copy Database wizard.
It's trivial to write a script to loop through sysdatabases to run a
BACKUP command for each database.|||> It's trivial to write a script to loop through sysdatabases to run a
> BACKUP command for each database.
And automating a restore is pretty trivial as well. The code in
http://www.karaszi.com/SQLServer/ut...all_in_file.asp can be a good
starting point.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tracy McKibben" <tracy.mckibben@.gmail.com> wrote in message
news:1172582161.712054.88030@.8g2000cwh.googlegroups.com...
> On Feb 27, 5:06 am, Andrew Hayes
> <AndrewHa...@.discussions.microsoft.com> wrote:
> Backup/restore will be MUCH faster than the Copy Database wizard.
> It's trivial to write a script to loop through sysdatabases to run a
> BACKUP command for each database.
>|||Uri - Can't use Detach/Attach since it's a production server in use 24/7. Or
if I do, it means spending several weeks doing it so I can minimise the
impact on our customers.
Tracy - OK. So I write a script to backup every database from the source
server, copy each backup file over the WAN to the destination server and the
n
perform a restore, changing the file locations for each database MDF. That
would probably take several days.
In either case, it doesn't resolve the issue that the Copy Database Wizard
fails to create views because it's performing a logic/object check during th
e
copy. Why can't I just turn that off?|||On Feb 27, 7:36 am, Andrew Hayes
<AndrewHa...@.discussions.microsoft.com> wrote:
> Uri - Can't use Detach/Attach since it's a production server in use 24/7.
Or
> if I do, it means spending several weeks doing it so I can minimise the
> impact on our customers.
> Tracy - OK. So I write a script to backup every database from the source
> server, copy each backup file over the WAN to the destination server and t
hen
> perform a restore, changing the file locations for each database MDF. That
> would probably take several days.
> In either case, it doesn't resolve the issue that the Copy Database Wizard
> fails to create views because it's performing a logic/object check during
the
> copy. Why can't I just turn that off?
How long do you think it's going to take the copy wizard to complete?
The first thing that gizmo does is script all of the objects in your
source database, run those scripts on the destination database (to
create the objects), and then imports/exports the data from the source
to the destination. That is going to take twice as long as going the
backup/restore route. Trust me, backup/restore is the way to go, you
can AUTOMATE the whole thing, and you have FULL CONTROL over what's
happening.
If you insist on using the wizard, I'm afraid I can't help you out. I
don't use it, it's just too klunky to work with.
like to copy over to a new SQL Server 2005 system for testing prior to
rolling out a new database server.
Unfortunately, the Copy Database wizard using SQL MO fails. The event log
shows that it could not create a view because of an "invalid object name".
This, I assume, is because the Dependencies are not set so the SQLISPackage
doesn't know which order to create the objects. That is, the databases use
views that did not specify SCHEMABINDING when they were created, so when
using View Dependencies, the view only shows itself, even if the view query
makes use of other views/tables.
Is there a way to stop this check from occuring, so that the view is created
regardless of whether a view dependency has been fulfilled?Andrew
Have you tried to BACKUP all user databases and then RESTORE on SQL Server
2005?
"Andrew Hayes" <AndrewHayes@.discussions.microsoft.com> wrote in message
news:F323D970-8FC8-4CBB-8418-3137CD980046@.microsoft.com...
>I have 180+ databases on a production SQL Server 2000 system that I would
> like to copy over to a new SQL Server 2005 system for testing prior to
> rolling out a new database server.
> Unfortunately, the Copy Database wizard using SQL MO fails. The event log
> shows that it could not create a view because of an "invalid object name".
> This, I assume, is because the Dependencies are not set so the
> SQLISPackage
> doesn't know which order to create the objects. That is, the databases use
> views that did not specify SCHEMABINDING when they were created, so when
> using View Dependencies, the view only shows itself, even if the view
> query
> makes use of other views/tables.
> Is there a way to stop this check from occuring, so that the view is
> created
> regardless of whether a view dependency has been fulfilled?|||I was hoping to avoid that. Performing a backup and restore of 180+ database
s
is going to take some time...
"Uri Dimant" wrote:
> Andrew
> Have you tried to BACKUP all user databases and then RESTORE on SQL Serve
r
> 2005?
>|||Andrew
Well , I don't think so , especially comparing it with copy wizard ,but try
detach\attach , for more details please refer to the BOL
"Andrew Hayes" <AndrewHayes@.discussions.microsoft.com> wrote in message
news:152BB668-35A7-4698-8379-438DB8DA3157@.microsoft.com...
>I was hoping to avoid that. Performing a backup and restore of 180+
>databases
> is going to take some time...
> "Uri Dimant" wrote:
>
>|||On Feb 27, 5:06 am, Andrew Hayes
<AndrewHa...@.discussions.microsoft.com> wrote:
> I was hoping to avoid that. Performing a backup and restore of 180+ databa
ses
> is going to take some time...
>
Backup/restore will be MUCH faster than the Copy Database wizard.
It's trivial to write a script to loop through sysdatabases to run a
BACKUP command for each database.|||> It's trivial to write a script to loop through sysdatabases to run a
> BACKUP command for each database.
And automating a restore is pretty trivial as well. The code in
http://www.karaszi.com/SQLServer/ut...all_in_file.asp can be a good
starting point.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tracy McKibben" <tracy.mckibben@.gmail.com> wrote in message
news:1172582161.712054.88030@.8g2000cwh.googlegroups.com...
> On Feb 27, 5:06 am, Andrew Hayes
> <AndrewHa...@.discussions.microsoft.com> wrote:
> Backup/restore will be MUCH faster than the Copy Database wizard.
> It's trivial to write a script to loop through sysdatabases to run a
> BACKUP command for each database.
>|||Uri - Can't use Detach/Attach since it's a production server in use 24/7. Or
if I do, it means spending several weeks doing it so I can minimise the
impact on our customers.
Tracy - OK. So I write a script to backup every database from the source
server, copy each backup file over the WAN to the destination server and the
n
perform a restore, changing the file locations for each database MDF. That
would probably take several days.
In either case, it doesn't resolve the issue that the Copy Database Wizard
fails to create views because it's performing a logic/object check during th
e
copy. Why can't I just turn that off?|||On Feb 27, 7:36 am, Andrew Hayes
<AndrewHa...@.discussions.microsoft.com> wrote:
> Uri - Can't use Detach/Attach since it's a production server in use 24/7.
Or
> if I do, it means spending several weeks doing it so I can minimise the
> impact on our customers.
> Tracy - OK. So I write a script to backup every database from the source
> server, copy each backup file over the WAN to the destination server and t
hen
> perform a restore, changing the file locations for each database MDF. That
> would probably take several days.
> In either case, it doesn't resolve the issue that the Copy Database Wizard
> fails to create views because it's performing a logic/object check during
the
> copy. Why can't I just turn that off?
How long do you think it's going to take the copy wizard to complete?
The first thing that gizmo does is script all of the objects in your
source database, run those scripts on the destination database (to
create the objects), and then imports/exports the data from the source
to the destination. That is going to take twice as long as going the
backup/restore route. Trust me, backup/restore is the way to go, you
can AUTOMATE the whole thing, and you have FULL CONTROL over what's
happening.
If you insist on using the wizard, I'm afraid I can't help you out. I
don't use it, it's just too klunky to work with.
Copy Database From 2000 To 2005 Failing To Create View
I have 180+ databases on a production SQL Server 2000 system that I would
like to copy over to a new SQL Server 2005 system for testing prior to
rolling out a new database server.
Unfortunately, the Copy Database wizard using SQL MO fails. The event log
shows that it could not create a view because of an "invalid object name".
This, I assume, is because the Dependencies are not set so the SQLISPackage
doesn't know which order to create the objects. That is, the databases use
views that did not specify SCHEMABINDING when they were created, so when
using View Dependencies, the view only shows itself, even if the view query
makes use of other views/tables.
Is there a way to stop this check from occuring, so that the view is created
regardless of whether a view dependency has been fulfilled?Andrew
Have you tried to BACKUP all user databases and then RESTORE on SQL Server
2005?
"Andrew Hayes" <AndrewHayes@.discussions.microsoft.com> wrote in message
news:F323D970-8FC8-4CBB-8418-3137CD980046@.microsoft.com...
>I have 180+ databases on a production SQL Server 2000 system that I would
> like to copy over to a new SQL Server 2005 system for testing prior to
> rolling out a new database server.
> Unfortunately, the Copy Database wizard using SQL MO fails. The event log
> shows that it could not create a view because of an "invalid object name".
> This, I assume, is because the Dependencies are not set so the
> SQLISPackage
> doesn't know which order to create the objects. That is, the databases use
> views that did not specify SCHEMABINDING when they were created, so when
> using View Dependencies, the view only shows itself, even if the view
> query
> makes use of other views/tables.
> Is there a way to stop this check from occuring, so that the view is
> created
> regardless of whether a view dependency has been fulfilled?|||I was hoping to avoid that. Performing a backup and restore of 180+ databases
is going to take some time...
"Uri Dimant" wrote:
> Andrew
> Have you tried to BACKUP all user databases and then RESTORE on SQL Server
> 2005?
>|||Andrew
Well , I don't think so , especially comparing it with copy wizard ,but try
detach\attach , for more details please refer to the BOL
"Andrew Hayes" <AndrewHayes@.discussions.microsoft.com> wrote in message
news:152BB668-35A7-4698-8379-438DB8DA3157@.microsoft.com...
>I was hoping to avoid that. Performing a backup and restore of 180+
>databases
> is going to take some time...
> "Uri Dimant" wrote:
>> Andrew
>> Have you tried to BACKUP all user databases and then RESTORE on SQL
>> Server
>> 2005?
>|||On Feb 27, 5:06 am, Andrew Hayes
<AndrewHa...@.discussions.microsoft.com> wrote:
> I was hoping to avoid that. Performing a backup and restore of 180+ databases
> is going to take some time...
>
Backup/restore will be MUCH faster than the Copy Database wizard.
It's trivial to write a script to loop through sysdatabases to run a
BACKUP command for each database.|||> It's trivial to write a script to loop through sysdatabases to run a
> BACKUP command for each database.
And automating a restore is pretty trivial as well. The code in
http://www.karaszi.com/SQLServer/util_restore_all_in_file.asp can be a good starting point.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tracy McKibben" <tracy.mckibben@.gmail.com> wrote in message
news:1172582161.712054.88030@.8g2000cwh.googlegroups.com...
> On Feb 27, 5:06 am, Andrew Hayes
> <AndrewHa...@.discussions.microsoft.com> wrote:
>> I was hoping to avoid that. Performing a backup and restore of 180+ databases
>> is going to take some time...
> Backup/restore will be MUCH faster than the Copy Database wizard.
> It's trivial to write a script to loop through sysdatabases to run a
> BACKUP command for each database.
>|||Uri - Can't use Detach/Attach since it's a production server in use 24/7. Or
if I do, it means spending several weeks doing it so I can minimise the
impact on our customers.
Tracy - OK. So I write a script to backup every database from the source
server, copy each backup file over the WAN to the destination server and then
perform a restore, changing the file locations for each database MDF. That
would probably take several days.
In either case, it doesn't resolve the issue that the Copy Database Wizard
fails to create views because it's performing a logic/object check during the
copy. Why can't I just turn that off?|||On Feb 27, 7:36 am, Andrew Hayes
<AndrewHa...@.discussions.microsoft.com> wrote:
> Uri - Can't use Detach/Attach since it's a production server in use 24/7. Or
> if I do, it means spending several weeks doing it so I can minimise the
> impact on our customers.
> Tracy - OK. So I write a script to backup every database from the source
> server, copy each backup file over the WAN to the destination server and then
> perform a restore, changing the file locations for each database MDF. That
> would probably take several days.
> In either case, it doesn't resolve the issue that the Copy Database Wizard
> fails to create views because it's performing a logic/object check during the
> copy. Why can't I just turn that off?
How long do you think it's going to take the copy wizard to complete?
The first thing that gizmo does is script all of the objects in your
source database, run those scripts on the destination database (to
create the objects), and then imports/exports the data from the source
to the destination. That is going to take twice as long as going the
backup/restore route. Trust me, backup/restore is the way to go, you
can AUTOMATE the whole thing, and you have FULL CONTROL over what's
happening.
If you insist on using the wizard, I'm afraid I can't help you out. I
don't use it, it's just too klunky to work with.
like to copy over to a new SQL Server 2005 system for testing prior to
rolling out a new database server.
Unfortunately, the Copy Database wizard using SQL MO fails. The event log
shows that it could not create a view because of an "invalid object name".
This, I assume, is because the Dependencies are not set so the SQLISPackage
doesn't know which order to create the objects. That is, the databases use
views that did not specify SCHEMABINDING when they were created, so when
using View Dependencies, the view only shows itself, even if the view query
makes use of other views/tables.
Is there a way to stop this check from occuring, so that the view is created
regardless of whether a view dependency has been fulfilled?Andrew
Have you tried to BACKUP all user databases and then RESTORE on SQL Server
2005?
"Andrew Hayes" <AndrewHayes@.discussions.microsoft.com> wrote in message
news:F323D970-8FC8-4CBB-8418-3137CD980046@.microsoft.com...
>I have 180+ databases on a production SQL Server 2000 system that I would
> like to copy over to a new SQL Server 2005 system for testing prior to
> rolling out a new database server.
> Unfortunately, the Copy Database wizard using SQL MO fails. The event log
> shows that it could not create a view because of an "invalid object name".
> This, I assume, is because the Dependencies are not set so the
> SQLISPackage
> doesn't know which order to create the objects. That is, the databases use
> views that did not specify SCHEMABINDING when they were created, so when
> using View Dependencies, the view only shows itself, even if the view
> query
> makes use of other views/tables.
> Is there a way to stop this check from occuring, so that the view is
> created
> regardless of whether a view dependency has been fulfilled?|||I was hoping to avoid that. Performing a backup and restore of 180+ databases
is going to take some time...
"Uri Dimant" wrote:
> Andrew
> Have you tried to BACKUP all user databases and then RESTORE on SQL Server
> 2005?
>|||Andrew
Well , I don't think so , especially comparing it with copy wizard ,but try
detach\attach , for more details please refer to the BOL
"Andrew Hayes" <AndrewHayes@.discussions.microsoft.com> wrote in message
news:152BB668-35A7-4698-8379-438DB8DA3157@.microsoft.com...
>I was hoping to avoid that. Performing a backup and restore of 180+
>databases
> is going to take some time...
> "Uri Dimant" wrote:
>> Andrew
>> Have you tried to BACKUP all user databases and then RESTORE on SQL
>> Server
>> 2005?
>|||On Feb 27, 5:06 am, Andrew Hayes
<AndrewHa...@.discussions.microsoft.com> wrote:
> I was hoping to avoid that. Performing a backup and restore of 180+ databases
> is going to take some time...
>
Backup/restore will be MUCH faster than the Copy Database wizard.
It's trivial to write a script to loop through sysdatabases to run a
BACKUP command for each database.|||> It's trivial to write a script to loop through sysdatabases to run a
> BACKUP command for each database.
And automating a restore is pretty trivial as well. The code in
http://www.karaszi.com/SQLServer/util_restore_all_in_file.asp can be a good starting point.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tracy McKibben" <tracy.mckibben@.gmail.com> wrote in message
news:1172582161.712054.88030@.8g2000cwh.googlegroups.com...
> On Feb 27, 5:06 am, Andrew Hayes
> <AndrewHa...@.discussions.microsoft.com> wrote:
>> I was hoping to avoid that. Performing a backup and restore of 180+ databases
>> is going to take some time...
> Backup/restore will be MUCH faster than the Copy Database wizard.
> It's trivial to write a script to loop through sysdatabases to run a
> BACKUP command for each database.
>|||Uri - Can't use Detach/Attach since it's a production server in use 24/7. Or
if I do, it means spending several weeks doing it so I can minimise the
impact on our customers.
Tracy - OK. So I write a script to backup every database from the source
server, copy each backup file over the WAN to the destination server and then
perform a restore, changing the file locations for each database MDF. That
would probably take several days.
In either case, it doesn't resolve the issue that the Copy Database Wizard
fails to create views because it's performing a logic/object check during the
copy. Why can't I just turn that off?|||On Feb 27, 7:36 am, Andrew Hayes
<AndrewHa...@.discussions.microsoft.com> wrote:
> Uri - Can't use Detach/Attach since it's a production server in use 24/7. Or
> if I do, it means spending several weeks doing it so I can minimise the
> impact on our customers.
> Tracy - OK. So I write a script to backup every database from the source
> server, copy each backup file over the WAN to the destination server and then
> perform a restore, changing the file locations for each database MDF. That
> would probably take several days.
> In either case, it doesn't resolve the issue that the Copy Database Wizard
> fails to create views because it's performing a logic/object check during the
> copy. Why can't I just turn that off?
How long do you think it's going to take the copy wizard to complete?
The first thing that gizmo does is script all of the objects in your
source database, run those scripts on the destination database (to
create the objects), and then imports/exports the data from the source
to the destination. That is going to take twice as long as going the
backup/restore route. Trust me, backup/restore is the way to go, you
can AUTOMATE the whole thing, and you have FULL CONTROL over what's
happening.
If you insist on using the wizard, I'm afraid I can't help you out. I
don't use it, it's just too klunky to work with.
Copy database fails because of invalid object name in a view?
I'm getting the below error when attempting to copy a database. The view works just fine but the package keeps giving me the below error:
07 3:26:37 PM,4/11/2007 3:26:37 PM,0,0x,ERROR : errorCode=-1073548784 description=Executing the query "
CREATE VIEW [app].[vwbaseTransfer]
AS
Select a.app_id, a.app_year, u.first_name, u.middle_name, u.last_name, app_phoneAM, email, CONVERT(varchar(10), app_dob, 101) as app_dob, app_citizen,
CASE WHEN app_untilDate > GetDate() Then app_addr1 ELSE app_pAddr1 END AS app_addr1,
CASE WHEN app_untilDate > GetDate() Then app_addr2 ELSE app_pAddr2 END AS app_addr2,
CASE WHEN app_untilDate > GetDate() Then app_addr3 ELSE app_pAddr3 END AS app_addr3,
CASE WHEN app_untilDate > GetDate() Then app_addr3 ELSE app_pAddr3 END AS app_addr4,
CASE WHEN app_untilDate > GetDate() Then app_city ELSE app_pcity END AS app_city,
CASE WHEN app_untilDate > GetDate() Then app_state ELSE app_pstate END AS app_state,
CASE WHEN app_untilDate > GetDate() Then app_zipcode ELSE app_pzipcode END AS app_zipcode,
app_sex, app_racecode, app_accept_letter, app_acceptDate, app_orientation_date, app_ssn
FROM applicant a inner join uop_user u on u.id = a.app_id
where app_accept_letter is not null and app_acceptDate is not null
" failed with the following error: "Invalid object name 'applicant'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
helpFile= helpContext=0 idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-DA807BCDC2AC}
StackTrace: at Microsoft.SqlServer.Management.Dts.DtsTransferProvider.ExecuteTransfer()
at Microsoft.SqlServer.Management.Smo.Transfer.TransferData()
jwelch wrote:
Maybe it is trying to copy the view before the applicant table?
Ooooo, I like that thought!|||That's not good. Anyone tried this before?|||Should I assume that this will not work and manually copy databases?|||
Should I assume that this will not work and manually copy databases?
What task are you using?
Also, isn't there a better method of "copying" databases? (That is, a backup/restore process.) Are you going for just the schema, or data as well?|||
That's not good. Anyone tried this before?
If you feel like wading through it, you could check your sys.sql_dependencies view to see if it has the dependency between the view and the table. Not sure if that is used to determine the order when db objects are copied, but it might be worth a shot.
|||I'm using the copy database task. If I do a backup and restore then I manually must reset the logins, correct. I was hoping that a simple copy database would take care of this.
thanks.
Wednesday, March 7, 2012
Copy and Paste from access into columns
Is there a way to copy and paste data into columns when view the table columns?
I would like to be able to copy and paste data from an access database.
TIA
I don't believe SSMSE supports this functionality. If you need to move data from Access (Jet) to SQL Server, you have a number of choices:
Export the data from within Access.|||Thanks Mike.
copy an object in the procedure
Hi.
Is there any simple way to copy an object ( table,view, procedure) and write
it with another name or different owner. A new object should have the same
CONSTRAINTS, IDENTITY column etc.. For tables I've tried to create a new
table and later add columns or change data type with ALTER, but there are to
many exceptions for ALTER command and it does not work properly. I need to
synchronize a structures of one table with another or create a copy if it
does not exist yet in sysobjects and ...all those things must be done
automatically in the stored procedure:)
Thanks for help.
Toosietoosie
I'd backup the database a nd restore with a new name
The next step is changing an owner of the objects
--This script changes all objects that don't have 'dbo' to 'dbo' owner.
SELECT 'EXEC sp_changeobjectowner '''+ROUTINE_NAME+''',''dbo'''
FROM INFORMATION_SCHEMA.ROUTINES
WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_SCHEMA+
'.'+ROUTINE_NAME),
'IsMsShipped')=0
AND ROUTINE_SCHEMA != 'dbo'
After the objects have changed you can run a cusros through to alter
their names
SELECT 'ALTER TABLE '+t1.name+ ' new name'
FROM sysobjects t1
INNER JOIN sysindexes t2
ON t1.id = t2.id
WHERE t2.indid <= 1
AND t2.rows > 0
AND OBJECTPROPERTY(t1.id,'IsUserTable') = 1
"toosie" <toosie@.wp.pl> wrote in message
news:d2tjor$1pbi$1@.news2.ipartners.pl...
> Hi.
> Is there any simple way to copy an object ( table,view, procedure) and
write
> it with another name or different owner. A new object should have the
same
> CONSTRAINTS, IDENTITY column etc.. For tables I've tried to create a new
> table and later add columns or change data type with ALTER, but there are
to
> many exceptions for ALTER command and it does not work properly. I need to
> synchronize a structures of one table with another or create a copy if it
> does not exist yet in sysobjects and ...all those things must be done
> automatically in the stored procedure:)
> Thanks for help.
> Toosie
>
Is there any simple way to copy an object ( table,view, procedure) and write
it with another name or different owner. A new object should have the same
CONSTRAINTS, IDENTITY column etc.. For tables I've tried to create a new
table and later add columns or change data type with ALTER, but there are to
many exceptions for ALTER command and it does not work properly. I need to
synchronize a structures of one table with another or create a copy if it
does not exist yet in sysobjects and ...all those things must be done
automatically in the stored procedure:)
Thanks for help.
Toosietoosie
I'd backup the database a nd restore with a new name
The next step is changing an owner of the objects
--This script changes all objects that don't have 'dbo' to 'dbo' owner.
SELECT 'EXEC sp_changeobjectowner '''+ROUTINE_NAME+''',''dbo'''
FROM INFORMATION_SCHEMA.ROUTINES
WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_SCHEMA+
'.'+ROUTINE_NAME),
'IsMsShipped')=0
AND ROUTINE_SCHEMA != 'dbo'
After the objects have changed you can run a cusros through to alter
their names
SELECT 'ALTER TABLE '+t1.name+ ' new name'
FROM sysobjects t1
INNER JOIN sysindexes t2
ON t1.id = t2.id
WHERE t2.indid <= 1
AND t2.rows > 0
AND OBJECTPROPERTY(t1.id,'IsUserTable') = 1
"toosie" <toosie@.wp.pl> wrote in message
news:d2tjor$1pbi$1@.news2.ipartners.pl...
> Hi.
> Is there any simple way to copy an object ( table,view, procedure) and
write
> it with another name or different owner. A new object should have the
same
> CONSTRAINTS, IDENTITY column etc.. For tables I've tried to create a new
> table and later add columns or change data type with ALTER, but there are
to
> many exceptions for ALTER command and it does not work properly. I need to
> synchronize a structures of one table with another or create a copy if it
> does not exist yet in sysobjects and ...all those things must be done
> automatically in the stored procedure:)
> Thanks for help.
> Toosie
>
Copy a view
Hi
At home i have sqlserver and at work we have sqlserver
At home i have make some views i also would like to use
on the server at work
To day i try to use import from my server to the server at work
but when i import my views the views become a table on the server at work
Is there a way to copy/import the views on from my server at home to the server at work
On my work can i connect to my server at home, so this is not the problem.
Or shall i make new views on the server at work?
Alvin
You could script the views to files (at home) and then take the scripted views to work, open them and execute and the views will get created on your work Server
In Server Management Studio, right click the view you want to script and choose
Script View >> as CREATE >> to File...
The file created will have an extension of .sql but it is a text file...
OR
If you can connect to your server from work, you can do the above, but change >> to File TO >> to New Query Editor Window and then you can cut/paste the script into your work server and execute to create the view...
|||
Thanks
Alvin
Friday, February 24, 2012
Coplicated view - picking from a=b or from c&d =e
I realized there was something wrong with my view when an entire line
was missing. I believe it is caused by an inner join I did on one
column to create another column which may not actually have a value.
let me show you.
Table A ViewA ViewB
type1 type1
type2 type2
type3 type3
answer answer
Pretend that any / all items in Table A can be null. What I want the
view to do is either 1) have both viewA and ViewB answer show up even
if its null or 2) try to get viewB answer but if not, then go to ViewA
answer, then null.
Yes, that is a VIEW doing this...or so I hope.
Any ideas?
John
RocketMan,
Try:
select
coalesce(c.c1, b.c1, a.c1)
from
tableA as a
full outer join
viewA as b
a.c1 = b.c1
full outer join
viewB as c
on a.c1 = c.c1
GO
AMB
"RocketMan" wrote:
> I realized there was something wrong with my view when an entire line
> was missing. I believe it is caused by an inner join I did on one
> column to create another column which may not actually have a value.
> let me show you.
> Table A ViewA ViewB
> type1 type1
> type2 type2
> type3 type3
> answer answer
> Pretend that any / all items in Table A can be null. What I want the
> view to do is either 1) have both viewA and ViewB answer show up even
> if its null or 2) try to get viewB answer but if not, then go to ViewA
> answer, then null.
> Yes, that is a VIEW doing this...or so I hope.
> Any ideas?
> John
>
|||Better try this one:
SELECT
coalesce(c.c1, b.c1, a.c1) AS whatever
from
tableA as a
full outer JOIN
(
viewA as b
full outer join
viewB as c
ON b.c1 = c.c1
)
ON a.c1 = COALESCE(c.c1, b.c1)
GO
AMB
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> RocketMan,
> Try:
> select
> coalesce(c.c1, b.c1, a.c1)
> from
> tableA as a
> full outer join
> viewA as b
> a.c1 = b.c1
> full outer join
> viewB as c
> on a.c1 = c.c1
> GO
> AMB
> "RocketMan" wrote:
|||In a way, I answered my own question with a question...
When is a null not a null? When its a ''
Adding this to the views ( of other tables) made the join work and I
was able to get the line to appear.
THANKS for the help. I KNOW someone sometime will be searching for
this kind of help
was missing. I believe it is caused by an inner join I did on one
column to create another column which may not actually have a value.
let me show you.
Table A ViewA ViewB
type1 type1
type2 type2
type3 type3
answer answer
Pretend that any / all items in Table A can be null. What I want the
view to do is either 1) have both viewA and ViewB answer show up even
if its null or 2) try to get viewB answer but if not, then go to ViewA
answer, then null.
Yes, that is a VIEW doing this...or so I hope.
Any ideas?
John
RocketMan,
Try:
select
coalesce(c.c1, b.c1, a.c1)
from
tableA as a
full outer join
viewA as b
a.c1 = b.c1
full outer join
viewB as c
on a.c1 = c.c1
GO
AMB
"RocketMan" wrote:
> I realized there was something wrong with my view when an entire line
> was missing. I believe it is caused by an inner join I did on one
> column to create another column which may not actually have a value.
> let me show you.
> Table A ViewA ViewB
> type1 type1
> type2 type2
> type3 type3
> answer answer
> Pretend that any / all items in Table A can be null. What I want the
> view to do is either 1) have both viewA and ViewB answer show up even
> if its null or 2) try to get viewB answer but if not, then go to ViewA
> answer, then null.
> Yes, that is a VIEW doing this...or so I hope.
> Any ideas?
> John
>
|||Better try this one:
SELECT
coalesce(c.c1, b.c1, a.c1) AS whatever
from
tableA as a
full outer JOIN
(
viewA as b
full outer join
viewB as c
ON b.c1 = c.c1
)
ON a.c1 = COALESCE(c.c1, b.c1)
GO
AMB
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> RocketMan,
> Try:
> select
> coalesce(c.c1, b.c1, a.c1)
> from
> tableA as a
> full outer join
> viewA as b
> a.c1 = b.c1
> full outer join
> viewB as c
> on a.c1 = c.c1
> GO
> AMB
> "RocketMan" wrote:
|||In a way, I answered my own question with a question...
When is a null not a null? When its a ''
Adding this to the views ( of other tables) made the join work and I
was able to get the line to appear.
THANKS for the help. I KNOW someone sometime will be searching for
this kind of help
Coplicated view - picking from a=b or from c&d =e
I realized there was something wrong with my view when an entire line
was missing. I believe it is caused by an inner join I did on one
column to create another column which may not actually have a value.
let me show you.
Table A ViewA ViewB
type1 type1
type2 type2
type3 type3
answer answer
Pretend that any / all items in Table A can be null. What I want the
view to do is either 1) have both viewA and ViewB answer show up even
if its null or 2) try to get viewB answer but if not, then go to ViewA
answer, then null.
Yes, that is a VIEW doing this...or so I hope.
Any ideas?
JohnRocketMan,
Try:
select
coalesce(c.c1, b.c1, a.c1)
from
tableA as a
full outer join
viewA as b
a.c1 = b.c1
full outer join
viewB as c
on a.c1 = c.c1
GO
AMB
"RocketMan" wrote:
> I realized there was something wrong with my view when an entire line
> was missing. I believe it is caused by an inner join I did on one
> column to create another column which may not actually have a value.
> let me show you.
> Table A ViewA ViewB
> type1 type1
> type2 type2
> type3 type3
> answer answer
> Pretend that any / all items in Table A can be null. What I want the
> view to do is either 1) have both viewA and ViewB answer show up even
> if its null or 2) try to get viewB answer but if not, then go to ViewA
> answer, then null.
> Yes, that is a VIEW doing this...or so I hope.
> Any ideas?
> John
>|||Better try this one:
SELECT
coalesce(c.c1, b.c1, a.c1) AS whatever
from
tableA as a
full outer JOIN
(
viewA as b
full outer join
viewB as c
ON b.c1 = c.c1
)
ON a.c1 = COALESCE(c.c1, b.c1)
GO
AMB
"Alejandro Mesa" wrote:
> RocketMan,
> Try:
> select
> coalesce(c.c1, b.c1, a.c1)
> from
> tableA as a
> full outer join
> viewA as b
> a.c1 = b.c1
> full outer join
> viewB as c
> on a.c1 = c.c1
> GO
> AMB
> "RocketMan" wrote:
> > I realized there was something wrong with my view when an entire line
> > was missing. I believe it is caused by an inner join I did on one
> > column to create another column which may not actually have a value.
> > let me show you.
> >
> > Table A ViewA ViewB
> > type1 type1
> > type2 type2
> > type3 type3
> > answer answer
> >
> > Pretend that any / all items in Table A can be null. What I want the
> > view to do is either 1) have both viewA and ViewB answer show up even
> > if its null or 2) try to get viewB answer but if not, then go to ViewA
> > answer, then null.
> >
> > Yes, that is a VIEW doing this...or so I hope.
> >
> > Any ideas?
> >
> > John
> >|||In a way, I answered my own question with a question...
When is a null not a null? When its a ''
Adding this to the views ( of other tables) made the join work and I
was able to get the line to appear.
THANKS for the help. I KNOW someone sometime will be searching for
this kind of help
was missing. I believe it is caused by an inner join I did on one
column to create another column which may not actually have a value.
let me show you.
Table A ViewA ViewB
type1 type1
type2 type2
type3 type3
answer answer
Pretend that any / all items in Table A can be null. What I want the
view to do is either 1) have both viewA and ViewB answer show up even
if its null or 2) try to get viewB answer but if not, then go to ViewA
answer, then null.
Yes, that is a VIEW doing this...or so I hope.
Any ideas?
JohnRocketMan,
Try:
select
coalesce(c.c1, b.c1, a.c1)
from
tableA as a
full outer join
viewA as b
a.c1 = b.c1
full outer join
viewB as c
on a.c1 = c.c1
GO
AMB
"RocketMan" wrote:
> I realized there was something wrong with my view when an entire line
> was missing. I believe it is caused by an inner join I did on one
> column to create another column which may not actually have a value.
> let me show you.
> Table A ViewA ViewB
> type1 type1
> type2 type2
> type3 type3
> answer answer
> Pretend that any / all items in Table A can be null. What I want the
> view to do is either 1) have both viewA and ViewB answer show up even
> if its null or 2) try to get viewB answer but if not, then go to ViewA
> answer, then null.
> Yes, that is a VIEW doing this...or so I hope.
> Any ideas?
> John
>|||Better try this one:
SELECT
coalesce(c.c1, b.c1, a.c1) AS whatever
from
tableA as a
full outer JOIN
(
viewA as b
full outer join
viewB as c
ON b.c1 = c.c1
)
ON a.c1 = COALESCE(c.c1, b.c1)
GO
AMB
"Alejandro Mesa" wrote:
> RocketMan,
> Try:
> select
> coalesce(c.c1, b.c1, a.c1)
> from
> tableA as a
> full outer join
> viewA as b
> a.c1 = b.c1
> full outer join
> viewB as c
> on a.c1 = c.c1
> GO
> AMB
> "RocketMan" wrote:
> > I realized there was something wrong with my view when an entire line
> > was missing. I believe it is caused by an inner join I did on one
> > column to create another column which may not actually have a value.
> > let me show you.
> >
> > Table A ViewA ViewB
> > type1 type1
> > type2 type2
> > type3 type3
> > answer answer
> >
> > Pretend that any / all items in Table A can be null. What I want the
> > view to do is either 1) have both viewA and ViewB answer show up even
> > if its null or 2) try to get viewB answer but if not, then go to ViewA
> > answer, then null.
> >
> > Yes, that is a VIEW doing this...or so I hope.
> >
> > Any ideas?
> >
> > John
> >|||In a way, I answered my own question with a question...
When is a null not a null? When its a ''
Adding this to the views ( of other tables) made the join work and I
was able to get the line to appear.
THANKS for the help. I KNOW someone sometime will be searching for
this kind of help
Cookie problem in reporting services
Hi, I'm using URL access to view my reports from within my website. My report
is being viewed from within an IFrame on the site. IE 6 with default security
blocks cookies coming from the IFrame. Apparently - setting
"UseSessionCookies" to false in the site settings is supposed to cause RS to
manage session via the querystring and instead. I have done this and RS is
still sending cookies? (I am running SP1)
Is this a bug is RS or am i missing something?Hi Jarrod,
From your descriptions, I understood cookies are continuing sending even
you have set UseSessionCookies to false. Have I understood you? Correct me
if I was wrong.
Based on my scope, UseSessionCookies is a system property and you could set
that to false by using the SOAP API SetSystemProperties method.
I am not sure why it is continuing sending cookies as you said. I have
surveied known issues internally and do not find anything related with
UseSessionCookies so that is it possible for you to generate a samll sampel
for me to reproduce it?
Thank you for your patience and corperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||Hi Jarrod,
I wanted to post a quick note to see if you would like additional
assistance or information regarding this particular issue. We appreciate
your patience and look forward to hearing from you!
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
is being viewed from within an IFrame on the site. IE 6 with default security
blocks cookies coming from the IFrame. Apparently - setting
"UseSessionCookies" to false in the site settings is supposed to cause RS to
manage session via the querystring and instead. I have done this and RS is
still sending cookies? (I am running SP1)
Is this a bug is RS or am i missing something?Hi Jarrod,
From your descriptions, I understood cookies are continuing sending even
you have set UseSessionCookies to false. Have I understood you? Correct me
if I was wrong.
Based on my scope, UseSessionCookies is a system property and you could set
that to false by using the SOAP API SetSystemProperties method.
I am not sure why it is continuing sending cookies as you said. I have
surveied known issues internally and do not find anything related with
UseSessionCookies so that is it possible for you to generate a samll sampel
for me to reproduce it?
Thank you for your patience and corperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||Hi Jarrod,
I wanted to post a quick note to see if you would like additional
assistance or information regarding this particular issue. We appreciate
your patience and look forward to hearing from you!
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
Sunday, February 12, 2012
Converting Rrom Access To Sql Syntax
Ok I am tying to convert access syntax to Sql syntax to put it in a stored procedure or view..
Here is the part that I need to convert:
SELECT [2007_hours].proj_name, [2007_hours].task_name, [2007_hours].Employee,
IIf(Mid([task_name],1,3)='PTO','PTO_Holiday',
IIf(Mid([task_name],1,7)='Holiday','PTO_Holiday',
IIf(Mid([proj_name],1,9) In ('9900-2831','9900-2788'),'II Internal',
IIf(Mid([proj_name],1,9)='9900-2787','Sales',
IIf(Mid([proj_name],1,9)='9910-2799','Sales',
IIf(Mid([proj_name],1,9)='9920-2791','Sales',
)
)
)
)
) AS timeType, Sum([2007_hours].Hours) AS SumOfHours
from................
how can you convert it to sql syntax
I need to have a nested If statment which I can't do in sql (in sql I have to have select and from Together for example ( I can't do this in sql):
select ID, FName, LName
if(SUBSTRING(FirstName, 1, 4)= 'Mike')
Begin
Replace(FirstNam,'Mike','MikeTest')
if(SUBSTRING(LastName, 1, 4)= 'Kong')
Begin
Replace(LastNam,'Kong,'KongTest')
if(SUBSTRING(Address, 1, 4)= '1245')
Begin
.........
End
End
end
Case Statement might be the solution but i could not do it.
Your input will be appreciated
Thank you
you rightly said that CASE Statement is the right choice...
check this....
select ID, FName, LName,
Case
|||Thank you for you respond. This won't really work for one simple reason.When SUBSTRING(FirstName, 1, 4)= 'Mike' Then Replace(FirstName,'Mike','MikeTest')
End as FN,
Case
When SUBSTRING(LastName, 1, 4)= 'Kong' Then Replace(LastNam,'Kong,'KongTest')
End as LN,
Case
When SUBSTRING(Address, 1, 4)= '1245') Then ...........End As Add
From YourTableName Where <Conditions>
Madhu
It will execute each case statement. In other words if you have two people with the last name Kong both of their last names will change to KongTes. Even if you change it to which it will work
Case
When SUBSTRING(FirstName, 1, 4)= 'Mike' Then Replace(FirstName,'Mike','MikeTest')
End as FN,
Case
When SUBSTRING(LastName, 1, 4)= 'Kong' and When SUBSTRING(FirstName, 1, 4)= 'Mike'
Then Replace(LastNam,'Kong,'KongTest')
End as LN,
Case
When SUBSTRING(Address, 1, 4)= '1245') Then ...........End As Add
From YourTableName Where <Conditions>
But it is not equal to this
SELECT [2007_hours].proj_name, [2007_hours].task_name, [2007_hours].Employee,
IIf(Mid([task_name],1,3)='PTO','PTO_Holiday',
IIf(Mid([task_name],1,7)='Holiday','PTO_Holiday',
IIf(Mid([proj_name],1,9) In ('9900-2831','9900-2788'),'II Internal',
IIf(Mid([proj_name],1,9)='9900-2787','Sales',
IIf(Mid([proj_name],1,9)='9910-2799','Sales',
IIf(Mid([proj_name],1,9)='9920-2791','Sales',
)
)
)
)
) AS timeType, Sum([2007_hours].Hours) AS SumOfHours
Here is what i am trying to do:
select case
when SUBSTRING(task_name, 1, 3)= 'PTO'
then Replace(task_name,'PTO','PTO_Holiday') and Sum(td_hours) AS SumOfHours (this will give me an error)
when SUBSTRING(task_name, 1, 7)= 'Holiday' and SUBSTRING(task_name, 1, 3)= 'PTO'
then Replace(task_name,'Holiday','PTO_Holiday')and Sum(td_hours) AS SumOfHours (this will give me an error)
ELSE task_name
This does not work i can't have two things happening after "Then"
Do you think i can convertt this to sql Syntax?
thanks
Converting Rrom Access Syntax To Sql Syntax
Ok I am tying to convert access syntax to Sql syntax to put it in a stored procedure or view..
Here is the part that I need to convert:
SELECT [2007_hours].proj_name, [2007_hours].task_name, [2007_hours].Employee,
IIf(Mid([task_name],1,3)='PTO','PTO_Holiday',
IIf(Mid([task_name],1,7)='Holiday','PTO_Holiday',
IIf(Mid([proj_name],1,9) In ('9900-2831','9900-2788'),'II Internal',
IIf(Mid([proj_name],1,9)='9900-2787','Sales',
IIf(Mid([proj_name],1,9)='9910-2799','Sales',
IIf(Mid([proj_name],1,9)='9920-2791','Sales',
)
)
)
)
) AS timeType, Sum([2007_hours].Hours) AS SumOfHours
from................
how can you convert it to sql syntax
I need to have a nested If statment which I can't do in sql (in sql I have to have select and from Together for example ( I can't do this in sql):
select ID, FName, LName
if(SUBSTRING(FirstName, 1, 4)= 'Mike')
Begin
Replace(FirstNam,'Mike','MikeTest')
if(SUBSTRING(LastName, 1, 4)= 'Kong')
Begin
Replace(LastNam,'Kong,'KongTest')
if(SUBSTRING(Address, 1, 4)= '1245')
Begin
.........
End
End
end
Case Statement might be the solution but i could not do it.
Your input will be appreciated
Thank you
you rightly said that CASE Statement is the right choice...
check this....
select ID, FName, LName,
Case
|||Thank you for you respond. This won't really work for one simple reason.When SUBSTRING(FirstName, 1, 4)= 'Mike' Then Replace(FirstName,'Mike','MikeTest')
End as FN,
Case
When SUBSTRING(LastName, 1, 4)= 'Kong' Then Replace(LastNam,'Kong,'KongTest')
End as LN,
Case
When SUBSTRING(Address, 1, 4)= '1245') Then ...........End As Add
From YourTableName Where <Conditions>
Madhu
It will execute each case statement. In other words if you have two people with the last name Kong both of their last names will change to KongTes. Even if you change it to which it will work
Case
When SUBSTRING(FirstName, 1, 4)= 'Mike' Then Replace(FirstName,'Mike','MikeTest')
End as FN,
Case
When SUBSTRING(LastName, 1, 4)= 'Kong' and When SUBSTRING(FirstName, 1, 4)= 'Mike'
Then Replace(LastNam,'Kong,'KongTest')
End as LN,
Case
When SUBSTRING(Address, 1, 4)= '1245') Then ...........End As Add
From YourTableName Where <Conditions>
But it is not equal to this
SELECT [2007_hours].proj_name, [2007_hours].task_name, [2007_hours].Employee,
IIf(Mid([task_name],1,3)='PTO','PTO_Holiday',
IIf(Mid([task_name],1,7)='Holiday','PTO_Holiday',
IIf(Mid([proj_name],1,9) In ('9900-2831','9900-2788'),'II Internal',
IIf(Mid([proj_name],1,9)='9900-2787','Sales',
IIf(Mid([proj_name],1,9)='9910-2799','Sales',
IIf(Mid([proj_name],1,9)='9920-2791','Sales',
)
)
)
)
) AS timeType, Sum([2007_hours].Hours) AS SumOfHours
Here is what i am trying to do:
select case
when SUBSTRING(task_name, 1, 3)= 'PTO'
then Replace(task_name,'PTO','PTO_Holiday') and Sum(td_hours) AS SumOfHours (this will give me an error)
when SUBSTRING(task_name, 1, 7)= 'Holiday' and SUBSTRING(task_name, 1, 3)= 'PTO'
then Replace(task_name,'Holiday','PTO_Holiday')and Sum(td_hours) AS SumOfHours (this will give me an error)
ELSE task_name
This does not work i can't have two things happening after "Then"
Do you think i can convertt this to sql Syntax?
thanks
Subscribe to:
Posts (Atom)