correct syntax for this select in SQL Server?

This (demo) statement is fine in Access, and so far as I can see, should
be OK in SQL Server.

But Enterprise Manager barfs at the final bracket. Can anyone help
please?

select sum(field1) as sum1, sum(field2) as sum2 from
(SELECT * from test where id < 3
union
SELECT * from test where id 2)

In fact, I can reduce it to :-

select * from
(SELECT * from test)

with the same effect - clearly I just need telling :-)

cheers,
Jim
--
Jim
a Yorkshire polymothJim Lawton (usenet1@.jimlawton.TAKEOUTinfo) writes:

Quote:

Originally Posted by

This (demo) statement is fine in Access, and so far as I can see, should
be OK in SQL Server.
>
But Enterprise Manager barfs at the final bracket. Can anyone help
please?
>
select sum(field1) as sum1, sum(field2) as sum2 from
(SELECT * from test where id < 3
union
SELECT * from test where id 2)
>
In fact, I can reduce it to :-
>
select * from
(SELECT * from test)
>
with the same effect - clearly I just need telling :-)

In SQL Server, you need to provide an alias for the derived table:

SELECT * FROM (SELECT *FROM test) AS x

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Derived tables must be assigned an alias.

select * from
(SELECT * from test) as X

Roy Harvey
Beacon Falls, CT

On Fri, 22 Jun 2007 21:19:31 GMT, Jim Lawton
<usenet1@.jimlawton.TAKEOUTinfowrote:

Quote:

Originally Posted by

>This (demo) statement is fine in Access, and so far as I can see, should
>be OK in SQL Server.
>
>But Enterprise Manager barfs at the final bracket. Can anyone help
>please?
>
>select sum(field1) as sum1, sum(field2) as sum2 from
>(SELECT * from test where id < 3
>union
>SELECT * from test where id 2)
>
>In fact, I can reduce it to :-
>
>select * from
>(SELECT * from test)
>
>with the same effect - clearly I just need telling :-)
>
>
>cheers,
>Jim

|||On Fri, 22 Jun 2007 21:30:17 +0000 (UTC), Erland Sommarskog
<esquel@.sommarskog.sewrote:

Quote:

Originally Posted by

>Jim Lawton (usenet1@.jimlawton.TAKEOUTinfo) writes:

Quote:

Originally Posted by

>This (demo) statement is fine in Access, and so far as I can see, should
>be OK in SQL Server.
>>
>But Enterprise Manager barfs at the final bracket. Can anyone help
>please?
>>
>select sum(field1) as sum1, sum(field2) as sum2 from
>(SELECT * from test where id < 3
>union
>SELECT * from test where id 2)
>>
>In fact, I can reduce it to :-
>>
>select * from
>(SELECT * from test)
>>
>with the same effect - clearly I just need telling :-)

>
>In SQL Server, you need to provide an alias for the derived table:
>
SELECT * FROM (SELECT *FROM test) AS x

Thanks to both you and Roy.
--
Jim
a Yorkshire polymoth|||On Sat, 23 Jun 2007 06:32:00 GMT, Jim Lawton
<usenet1@.jimlawton.TAKEOUTinfowrote:

Quote:

Originally Posted by

>On Fri, 22 Jun 2007 21:30:17 +0000 (UTC), Erland Sommarskog
><esquel@.sommarskog.sewrote:
>

Quote:

Originally Posted by

>>Jim Lawton (usenet1@.jimlawton.TAKEOUTinfo) writes:

Quote:

Originally Posted by

>>This (demo) statement is fine in Access, and so far as I can see, should
>>be OK in SQL Server.
>>>
>>But Enterprise Manager barfs at the final bracket. Can anyone help
>>please?
>>>
>>select sum(field1) as sum1, sum(field2) as sum2 from
>>(SELECT * from test where id < 3
>>union
>>SELECT * from test where id 2)
>>>
>>In fact, I can reduce it to :-
>>>
>>select * from
>>(SELECT * from test)
>>>
>>with the same effect - clearly I just need telling :-)

>>
>>In SQL Server, you need to provide an alias for the derived table:
>>
> SELECT * FROM (SELECT *FROM test) AS x

>

And, actually, and for the record, let's just add, that for this to work
on Oracle we have to omit the "as" - which is OK for SQL Server as well.
--
Jim
a Yorkshire polymoth

copying views

I have created several views via Enterprise Manager in a
SQL database that supports software provided by an outside
source. These views will be effected during software
version upgrades and may have some nagative impact on the
execution of upgrades.
Is there some simple way to preserve these views during a
software upgrade?
I hoped that I would just be able to cut and paste the
views (perhaps into another database where they didn't
really belong) temporarily during an upgrade and then move
them back. I don't see any way to do this. I can right
click on the views and copy them, but I don't see any way
to paste it anywhere for temporary storage.
I would prefer not to copy the query text from each view,
save it somewhere temporarily, and then recreate each view
and paste the query text back into it. I have a lot of
views created and this would be a time consuming solution.
Thanks.generate scripts and run those after the update
"allen" <anonymous@.discussions.microsoft.com> wrote in message
news:01b601c3c4e2$08ce32d0$a601280a@.phx.gbl...
> I have created several views via Enterprise Manager in a
> SQL database that supports software provided by an outside
> source. These views will be effected during software
> version upgrades and may have some nagative impact on the
> execution of upgrades.
> Is there some simple way to preserve these views during a
> software upgrade?
> I hoped that I would just be able to cut and paste the
> views (perhaps into another database where they didn't
> really belong) temporarily during an upgrade and then move
> them back. I don't see any way to do this. I can right
> click on the views and copy them, but I don't see any way
> to paste it anywhere for temporary storage.
> I would prefer not to copy the query text from each view,
> save it somewhere temporarily, and then recreate each view
> and paste the query text back into it. I have a lot of
> views created and this would be a time consuming solution.
> Thanks.|||Thanks!
>--Original Message--
>generate scripts and run those after the update
>"allen" <anonymous@.discussions.microsoft.com> wrote in
message
>news:01b601c3c4e2$08ce32d0$a601280a@.phx.gbl...
>> I have created several views via Enterprise Manager in a
>> SQL database that supports software provided by an
outside
>> source. These views will be effected during software
>> version upgrades and may have some nagative impact on
the
>> execution of upgrades.
>> Is there some simple way to preserve these views during
a
>> software upgrade?
>> I hoped that I would just be able to cut and paste the
>> views (perhaps into another database where they didn't
>> really belong) temporarily during an upgrade and then
move
>> them back. I don't see any way to do this. I can right
>> click on the views and copy them, but I don't see any
way
>> to paste it anywhere for temporary storage.
>> I would prefer not to copy the query text from each
view,
>> save it somewhere temporarily, and then recreate each
view
>> and paste the query text back into it. I have a lot of
>> views created and this would be a time consuming
solution.
>> Thanks.
>
>.
>sql

copying stored procedures to C drive on local machine

I have a project with around 50 stored procedures, can not seem to see the
dbase when using explorer but have access to it with enterprize manager and
visual studio.net server explorer. Just wondering if there is an easy way to
copy the stored procedures onto the workstation possibly using enterprize
manager or .net server explorer? thanks.
Paul G
Software engineer.
The stored procedures don't really have any meaning outside of the database,
and you should only see MDF/NDF/LDF files in windows explorer (they are not
separate files). What exactly are you trying to accomplish?
http://www.aspfaq.com/
(Reverse address to reply.)
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:E3D99B30-FBA9-41E8-9F87-413D02810F43@.microsoft.com...
> I have a project with around 50 stored procedures, can not seem to see the
> dbase when using explorer but have access to it with enterprize manager
and
> visual studio.net server explorer. Just wondering if there is an easy way
to
> copy the stored procedures onto the workstation possibly using enterprize
> manager or .net server explorer? thanks.
> --
> Paul G
> Software engineer.
|||You can generate a script of the stored procedures. In SQL Enterprise
manager, right click your database name and go to SQL Scripting... It will
walk you through the process..
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:E3D99B30-FBA9-41E8-9F87-413D02810F43@.microsoft.com...
> I have a project with around 50 stored procedures, can not seem to see the
> dbase when using explorer but have access to it with enterprize manager
and
> visual studio.net server explorer. Just wondering if there is an easy way
to
> copy the stored procedures onto the workstation possibly using enterprize
> manager or .net server explorer? thanks.
> --
> Paul G
> Software engineer.
|||Hi thanks for the response. Just wanted to put the stored procedure code
onto a laptop for reference, so copying out to a client machine is sufficient.
"Aaron [SQL Server MVP]" wrote:

> The stored procedures don't really have any meaning outside of the database,
> and you should only see MDF/NDF/LDF files in windows explorer (they are not
> separate files). What exactly are you trying to accomplish?
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:E3D99B30-FBA9-41E8-9F87-413D02810F43@.microsoft.com...
> and
> to
>
>
|||ok thanks will give it a try. Hopefully it allows you to select all the
stored procedures in some fashion as it would take a bit of time to do each
one individually.
"Wayne Snyder" wrote:

> You can generate a script of the stored procedures. In SQL Enterprise
> manager, right click your database name and go to SQL Scripting... It will
> walk you through the process..
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:E3D99B30-FBA9-41E8-9F87-413D02810F43@.microsoft.com...
> and
> to
>
>
|||Yes, it does, try it out...
http://www.aspfaq.com/
(Reverse address to reply.)
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:ABF8BA22-734A-472A-9019-91B0D16BD502@.microsoft.com...
> ok thanks will give it a try. Hopefully it allows you to select all the
> stored procedures in some fashion as it would take a bit of time to do
each[vbcol=seagreen]
> one individually.
> "Wayne Snyder" wrote:
will[vbcol=seagreen]
the[vbcol=seagreen]
manager[vbcol=seagreen]
way[vbcol=seagreen]
enterprize[vbcol=seagreen]

copying stored procedures to C drive on local machine

I have a project with around 50 stored procedures, can not seem to see the
dbase when using explorer but have access to it with enterprize manager and
visual studio.net server explorer. Just wondering if there is an easy way to
copy the stored procedures onto the workstation possibly using enterprize
manager or .net server explorer? thanks.
--
Paul G
Software engineer.The stored procedures don't really have any meaning outside of the database,
and you should only see MDF/NDF/LDF files in windows explorer (they are not
separate files). What exactly are you trying to accomplish?
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:E3D99B30-FBA9-41E8-9F87-413D02810F43@.microsoft.com...
> I have a project with around 50 stored procedures, can not seem to see the
> dbase when using explorer but have access to it with enterprize manager
and
> visual studio.net server explorer. Just wondering if there is an easy way
to
> copy the stored procedures onto the workstation possibly using enterprize
> manager or .net server explorer? thanks.
> --
> Paul G
> Software engineer.|||You can generate a script of the stored procedures. In SQL Enterprise
manager, right click your database name and go to SQL Scripting... It will
walk you through the process..
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:E3D99B30-FBA9-41E8-9F87-413D02810F43@.microsoft.com...
> I have a project with around 50 stored procedures, can not seem to see the
> dbase when using explorer but have access to it with enterprize manager
and
> visual studio.net server explorer. Just wondering if there is an easy way
to
> copy the stored procedures onto the workstation possibly using enterprize
> manager or .net server explorer? thanks.
> --
> Paul G
> Software engineer.|||Hi thanks for the response. Just wanted to put the stored procedure code
onto a laptop for reference, so copying out to a client machine is sufficient.
"Aaron [SQL Server MVP]" wrote:
> The stored procedures don't really have any meaning outside of the database,
> and you should only see MDF/NDF/LDF files in windows explorer (they are not
> separate files). What exactly are you trying to accomplish?
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:E3D99B30-FBA9-41E8-9F87-413D02810F43@.microsoft.com...
> > I have a project with around 50 stored procedures, can not seem to see the
> > dbase when using explorer but have access to it with enterprize manager
> and
> > visual studio.net server explorer. Just wondering if there is an easy way
> to
> > copy the stored procedures onto the workstation possibly using enterprize
> > manager or .net server explorer? thanks.
> > --
> > Paul G
> > Software engineer.
>
>|||ok thanks will give it a try. Hopefully it allows you to select all the
stored procedures in some fashion as it would take a bit of time to do each
one individually.
"Wayne Snyder" wrote:
> You can generate a script of the stored procedures. In SQL Enterprise
> manager, right click your database name and go to SQL Scripting... It will
> walk you through the process..
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:E3D99B30-FBA9-41E8-9F87-413D02810F43@.microsoft.com...
> > I have a project with around 50 stored procedures, can not seem to see the
> > dbase when using explorer but have access to it with enterprize manager
> and
> > visual studio.net server explorer. Just wondering if there is an easy way
> to
> > copy the stored procedures onto the workstation possibly using enterprize
> > manager or .net server explorer? thanks.
> > --
> > Paul G
> > Software engineer.
>
>|||Yes, it does, try it out...
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:ABF8BA22-734A-472A-9019-91B0D16BD502@.microsoft.com...
> ok thanks will give it a try. Hopefully it allows you to select all the
> stored procedures in some fashion as it would take a bit of time to do
each
> one individually.
> "Wayne Snyder" wrote:
> > You can generate a script of the stored procedures. In SQL Enterprise
> > manager, right click your database name and go to SQL Scripting... It
will
> > walk you through the process..
> >
> > --
> > Wayne Snyder, MCDBA, SQL Server MVP
> > Mariner, Charlotte, NC
> > www.mariner-usa.com
> > (Please respond only to the newsgroups.)
> >
> > I support the Professional Association of SQL Server (PASS) and it's
> > community of SQL Server professionals.
> > www.sqlpass.org
> >
> > "Paul" <Paul@.discussions.microsoft.com> wrote in message
> > news:E3D99B30-FBA9-41E8-9F87-413D02810F43@.microsoft.com...
> > > I have a project with around 50 stored procedures, can not seem to see
the
> > > dbase when using explorer but have access to it with enterprize
manager
> > and
> > > visual studio.net server explorer. Just wondering if there is an easy
way
> > to
> > > copy the stored procedures onto the workstation possibly using
enterprize
> > > manager or .net server explorer? thanks.
> > > --
> > > Paul G
> > > Software engineer.
> >
> >
> >

copying stored procedures to C drive on local machine

I have a project with around 50 stored procedures, can not seem to see the
dbase when using explorer but have access to it with enterprize manager and
visual studio.net server explorer. Just wondering if there is an easy way t
o
copy the stored procedures onto the workstation possibly using enterprize
manager or .net server explorer? thanks.
--
Paul G
Software engineer.The stored procedures don't really have any meaning outside of the database,
and you should only see MDF/NDF/LDF files in windows explorer (they are not
separate files). What exactly are you trying to accomplish?
http://www.aspfaq.com/
(Reverse address to reply.)
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:E3D99B30-FBA9-41E8-9F87-413D02810F43@.microsoft.com...
> I have a project with around 50 stored procedures, can not seem to see the
> dbase when using explorer but have access to it with enterprize manager
and
> visual studio.net server explorer. Just wondering if there is an easy way
to
> copy the stored procedures onto the workstation possibly using enterprize
> manager or .net server explorer? thanks.
> --
> Paul G
> Software engineer.|||You can generate a script of the stored procedures. In SQL Enterprise
manager, right click your database name and go to SQL Scripting... It will
walk you through the process..
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:E3D99B30-FBA9-41E8-9F87-413D02810F43@.microsoft.com...
> I have a project with around 50 stored procedures, can not seem to see the
> dbase when using explorer but have access to it with enterprize manager
and
> visual studio.net server explorer. Just wondering if there is an easy way
to
> copy the stored procedures onto the workstation possibly using enterprize
> manager or .net server explorer? thanks.
> --
> Paul G
> Software engineer.|||Hi thanks for the response. Just wanted to put the stored procedure code
onto a laptop for reference, so copying out to a client machine is sufficien
t.
"Aaron [SQL Server MVP]" wrote:

> The stored procedures don't really have any meaning outside of the databas
e,
> and you should only see MDF/NDF/LDF files in windows explorer (they are no
t
> separate files). What exactly are you trying to accomplish?
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:E3D99B30-FBA9-41E8-9F87-413D02810F43@.microsoft.com...
> and
> to
>
>|||ok thanks will give it a try. Hopefully it allows you to select all the
stored procedures in some fashion as it would take a bit of time to do each
one individually.
"Wayne Snyder" wrote:

> You can generate a script of the stored procedures. In SQL Enterprise
> manager, right click your database name and go to SQL Scripting... It will
> walk you through the process..
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:E3D99B30-FBA9-41E8-9F87-413D02810F43@.microsoft.com...
> and
> to
>
>|||Yes, it does, try it out...
http://www.aspfaq.com/
(Reverse address to reply.)
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:ABF8BA22-734A-472A-9019-91B0D16BD502@.microsoft.com...
> ok thanks will give it a try. Hopefully it allows you to select all the
> stored procedures in some fashion as it would take a bit of time to do
each[vbcol=seagreen]
> one individually.
> "Wayne Snyder" wrote:
>
will[vbcol=seagreen]
the[vbcol=seagreen]
manager[vbcol=seagreen]
way[vbcol=seagreen]
enterprize[vbcol=seagreen]sql

Copying Reports in Report Manager

Is it possible to create a copy of a report in the report manager?
I want to be able to deploy one copy of the report and have users create
multliple copies - each with their own default parameter and name.You can use the Web Service CreateReport method.
Check out this link http://msdn.microsoft.com/library/default.asp?url=3D/library/en-us/RSPROG=
/htm/rsp_ref_soapapi_service_ak_1xfd.asp
You will have to manipulate the rdl file from code.
>--Original Message--
>Is it possible to create a copy of a report in the report
manager?
>I want to be able to deploy one copy of the report and
have users create >multliple copies - each with their own default parameter
and name.
>.
>sql

Copying entire databases to remote server w/o Enterprise Manager

Is there any possible way to copy an entire MSDE database from my local system to a remote server using a program like 'osql'?

Or, is there any other GUI available for MSDE?

Thanks in advance.

GrierSure. There are a few ways. A couple that come to mind are to use the sp_detach_db system stored procedure in osql, copy the database files to the server, and use sp_attach_db to reattach them. Another is to run dtsrun.exe to run a DTS package that copies it over. You could also write ADO.NET code to do it, but it would be a lot of work to get all the objects copied.

There are several tools you can use to administer MSDE:

ASP.NET Enterprise Manager, an open source SQL Server and MSDE management tool.

ASP.NET WebMatrix (which includes a database management tool) from this web site (click on the Web Matrix tab at the top of this page).

Microsoft's Web Data Administrator is a free web-based MSDE management program written using C# and ASP.NET, and includes source code.

You can also access MSDE using Access. I'm not sure if this will do what you want, though.

Any of these work for you?
Don

Copying deleted into temp table in trigger

For some reason in Enterprise Manager for SQL Server 2000, I cannot
put the following line into a trigger:
select * into #deleted from deleted
When I hit the Apply button I get the following error:
Cannot use text, ntext, or image columns in the 'inserted' or
'deleted' tables

This seems like a weird error, since I am not actually doing anything
to the inserted or deleted tables, I am just trying to make a temp
copy.

I have another workaround but I am just curious why this happens.

Thanks,
RebeccaRebecca Lovelace (usagikawai@.yahoo.com) writes:
> For some reason in Enterprise Manager for SQL Server 2000, I cannot
> put the following line into a trigger:
> select * into #deleted from deleted
> When I hit the Apply button I get the following error:
> Cannot use text, ntext, or image columns in the 'inserted' or
> 'deleted' tables
> This seems like a weird error, since I am not actually doing anything
> to the inserted or deleted tables, I am just trying to make a temp
> copy.
> I have another workaround but I am just curious why this happens.

The message is very clear: there is a text, image, or ntext column in
your table, and cannot access that column. And since SELECT * implies
all columns, you access that column.

On another note, I fond recently that "SELECT * INTO #deleted FROM deleted"
in a trigger can be detrimental to performance. In my case, I was
running a one-by-one processing in a long transaction, and one table
had a trigger with a SELECT INTO like this. I had about given up to
get better speed, when I found that taking out the SELECT INTO and
using "inserted" directly gave a tremendous boost,

The reason this was such a winner, was that the locks on the system
tables in tempdb needed for all these temp tables were eating
resources. I also found that SELECT INTO #temp required more locks and
resources than CREATE TABLE #temp did. But there is a better alternative:
table variables, they don't need any tempdb locks at all.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||> The message is very clear: there is a text, image, or ntext column in
> your table, and cannot access that column. And since SELECT * implies
> all columns, you access that column.

It would be clear, if there were any of those types of columns in my
table. But there aren't. It's just varchars and ints.

Does this work for you in SQL Server 2000?

It's more of a matter of curiousity at this point, I know this isn't
the best way to go, I just want to know why I can't do it.

Rebecca|||Rebecca Lovelace (usagikawai@.yahoo.com) writes:
>> The message is very clear: there is a text, image, or ntext column in
>> your table, and cannot access that column. And since SELECT * implies
>> all columns, you access that column.
> It would be clear, if there were any of those types of columns in my
> table. But there aren't. It's just varchars and ints.

That sounds very strange. I'm afraid that I don't have any answer. Is
possible for you to produce a script with a CREATE TABLE statement and
a CREATE TRIGGER that demonstrates the problem? In such case, I could
bring it up with Microsoft.

> Does this work for you in SQL Server 2000?

Yes, I have used SELECT * FROM #deleted FROM deleted in triggers with
success.

Well, success and success I have ran into two problems, but I have not
gotten that weird error message you got.

One problem I have mentioned: performance. The other problem may be
worth mentioning too. Just like you I called the temp table #deleted.
But then I had a trigger that updated another table which did the same
thing. This caused problems because when the second trigger was compiled,
#deleted already existed, but with different columns. So *if* you
this kind of thing, don't call the temp tables #inserted and #deleted,
but use some part of the table name to get a unique name.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

copying a table from one database to another "last" doesnt appear the same

In enterprise manager I am copying a table from one database to
another. I am using the dts wizard to import the data. After I
successfully import the data, I open both tables to compare the
records to make sure they are the same. I right click on a field and
click "last" for both tables. However, the record is different for
both. If I do a query the record is still there but they do not show
up in the same order. Why does'nt the import wizard import the
records in the same order? Any help would be greatly appreciated.Rows in a table are not ordered. The Wizard does an INSERT... SELECT from
one table to another and unless there's a clustered index on the table
there's a good chance that the rows will be physically stored in a different
order.

Don't worry about the physical order - just use SELECT... ORDER BY if you
want to see the rows ordered.

--
David Portas
----
Please reply only to the newsgroup
--|||Great! Thank you very much for your help! After submitting this
question I noticed that microsoft mentions this on their site:

"Because SQL Server can use parallel scans to retrieve data, the data
bulk copied from an instance of SQL Server is not guaranteed to be in
any specific order unless you bulk copy from a query and specify an
ORDER BY clause."

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||But note that even if you manage to insert the rows in a specific order
there is no guarantee that they will be sorted in that order when you view
the data. If you require a specific order then you need to use ORDER BY
every time you want to view the data.

--
David Portas
----
Please reply only to the newsgroup
--

Copying a table from one database to another

Hi,
Could someone please tell me if this can be done in a query? I know how to
accomplish this using Enterprise Manager, but what I need to do is have it
done programmatically.
Thanks,
Stevecreate database a
go
create database b
go
create table a.dbo.mytable(id int)
go
create table b.dbo.mytable(id int)
go
insert b.dbo.mytable
select * from a.dbo.mytable
go
"Steve Caliendo" <scaliendo@.epion.com> wrote in message
news:OCQ1BjHVEHA.3692@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Could someone please tell me if this can be done in a query? I know how
to
> accomplish this using Enterprise Manager, but what I need to do is have it
> done programmatically.
> Thanks,
> Steve
>|||I forgot to show you this other way:
select *
into b.dbo.mytable2
from a.dbo.mytable
go
Note that this won't copy indexes, constraints, etc.
"Steve Caliendo" <scaliendo@.epion.com> wrote in message
news:OCQ1BjHVEHA.3692@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Could someone please tell me if this can be done in a query? I know how
to
> accomplish this using Enterprise Manager, but what I need to do is have it
> done programmatically.
> Thanks,
> Steve
>

copy/paste bugs

try copying a connection manager from one package to another

it does not work

e.g. excel connection manager

it loses the name of the connection manager and also the file you are pointing to

so it is basically no different to 'add new connection manager'

also

try copying the name of the connection manager to another connection

select connection, hit F2 (edit) ctrl C,

try pasting!

in order to paste you have to right click on the selected text and select copy from the context menu

this is not a bug

this sloppy, poorly tested software

So get busy posting these bugs over at http://connect.microsoft.com/sqlserver/feedback.

Copy Table structure only - not Data

We have SQL Enterprise Manager (8.0). Is there any way to Copy a Table from one database to another with only the Structure (design) - not all the Data?

I can't find any option in the Import Data Wizard that only copies the Table structure.

Any help is appreciated. Thanks

You can generate script for the table and compile it on the target server. Right click on the table in EM, All Tasks -> Generate SQL Script...

Copy Stored Procedures From 1 Database to Another Using Query

why are you set on using QA for this?
why not use DTS from Enterprise Manager? that would be much easier, as
you can do all of them at once.
you can do it from QA, but it would have to be done one at a time, e.g.
* open the object explorer
* expand your WebCentral database \ Stored Procedures
* right-click on the proc to copy
* select "Script Object to new window as \ [ Create | Alter ]" (create
or alter depending on if it's in the WebPPO database already)
* in the new window that pops up, switch to the WebPPO database using
the database drop-down, or the Ctrl+U dialog
* execute the query
RitaG wrote:
> Hi.
> I have some Stored Procedures (SPs) in one database (WebCentral).
> I have a table in another database (WebPPO) that holds the names of
> databases that I want to copy SPs from WebCentral to.
> I want to copy these SPs via code within Query Analyzer but have no idea h
ow
> to accomplish this.
> Any suggestions would be greatly appreciated!
> Thanks,
> Rita
>Hi Trey.
Thanks for the response.
I tested out using a DTS package and that worked well - thank you.
The only reason I would have preferred to use QA is so that it could be
executed dynamically. If another database was added that needed a copy of th
e
SPs then I wouldn't have to change the DTS package.
I'll play around with your suggestions on how to use QA and see if I can
come up with something.
Thanks,
Rita
"Trey Walpole" wrote:

> why are you set on using QA for this?
> why not use DTS from Enterprise Manager? that would be much easier, as
> you can do all of them at once.
> you can do it from QA, but it would have to be done one at a time, e.g.
> * open the object explorer
> * expand your WebCentral database \ Stored Procedures
> * right-click on the proc to copy
> * select "Script Object to new window as \ [ Create | Alter ]" (create
> or alter depending on if it's in the WebPPO database already)
> * in the new window that pops up, switch to the WebPPO database using
> the database drop-down, or the Ctrl+U dialog
> * execute the query
>
> RitaG wrote:
>

Copy SQL Server 2005 Express database to SQL Server 2000

Hello
I have a copy of a SQL Server 2005 Express database - the mdf and .ldf
files. If I copy to my machine and in the Enterprise manager try to attach
the files I get error 602 - and apparently you cannot do this as the
database structure has changed so much.
Bearing in mind SQL Server 2005 is customersw so don't really want to
install too much software on their machine. so if I can do this all my end
that would be preferable. Can I somehow convert the 2005 database to run on
my SQL Server 2000? What would I need to install on my machine to achieve
this? The SQL Server 2005 Express does not seem to provide much in the way
of data export etc.
Angus> Bearing in mind SQL Server 2005 is customersw so don't really want to
> install too much software on their machine. so if I can do this all my
> end
> that would be preferable. Can I somehow convert the 2005 database to run
> on
> my SQL Server 2000? What would I need to install on my machine to achieve
> this? The SQL Server 2005 Express does not seem to provide much in the
> way
> of data export etc.
Well, you could create an empty database, script all the objects, create
them empty, and the populate them using DTS / SSIS. You would need a
management tool on one side (Enterprise Manager, Management Studio, not sure
if Management Studio Express has any of this).
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006|||Why don't you just install SQL Express on your machine?
The two versions live together very well.
On May 10, 12:43 pm, "Angus" <nos...@.gmail.com> wrote:
> Hello
> I have a copy of a SQL Server 2005 Express database - the mdf and .ldf
> files. If I copy to my machine and in the Enterprise manager try to attac
h
> the files I get error 602 - and apparently you cannot do this as the
> database structure has changed so much.
> Bearing in mind SQL Server 2005 is customersw so don't really want to
> install too much software on their machine. so if I can do this all my en
d
> that would be preferable. Can I somehow convert the 2005 database to run
on
> my SQL Server 2000? What would I need to install on my machine to achieve
> this? The SQL Server 2005 Express does not seem to provide much in the wa
y
> of data export etc.
> Angus

Copy SQL Server 2005 Express database to SQL Server 2000

Hello
I have a copy of a SQL Server 2005 Express database - the mdf and .ldf
files. If I copy to my machine and in the Enterprise manager try to attach
the files I get error 602 - and apparently you cannot do this as the
database structure has changed so much.
Bearing in mind SQL Server 2005 is customersw so don't really want to
install too much software on their machine. so if I can do this all my end
that would be preferable. Can I somehow convert the 2005 database to run on
my SQL Server 2000? What would I need to install on my machine to achieve
this? The SQL Server 2005 Express does not seem to provide much in the way
of data export etc.
Angus> Bearing in mind SQL Server 2005 is customersw so don't really want to
> install too much software on their machine. so if I can do this all my
> end
> that would be preferable. Can I somehow convert the 2005 database to run
> on
> my SQL Server 2000? What would I need to install on my machine to achieve
> this? The SQL Server 2005 Express does not seem to provide much in the
> way
> of data export etc.
Well, you could create an empty database, script all the objects, create
them empty, and the populate them using DTS / SSIS. You would need a
management tool on one side (Enterprise Manager, Management Studio, not sure
if Management Studio Express has any of this).
--
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006|||Why don't you just install SQL Express on your machine?
The two versions live together very well.
On May 10, 12:43 pm, "Angus" <nos...@.gmail.com> wrote:
> Hello
> I have a copy of a SQL Server 2005 Express database - the mdf and .ldf
> files. If I copy to my machine and in the Enterprise manager try to attach
> the files I get error 602 - and apparently you cannot do this as the
> database structure has changed so much.
> Bearing in mind SQL Server 2005 is customersw so don't really want to
> install too much software on their machine. so if I can do this all my end
> that would be preferable. Can I somehow convert the 2005 database to run on
> my SQL Server 2000? What would I need to install on my machine to achieve
> this? The SQL Server 2005 Express does not seem to provide much in the way
> of data export etc.
> Angus