Correct syntax on this SQL INNER JOIN Query.

Hi :)

Trying to get this sql - query to run under Query Analyzer but not sure how to correct anything in it to the right :)

______INFO_______
Table : iptable
Fields : ip_start, ip_end, location

Table : PageLog
Fields : pl_ipaddress, pl_sessionid, pl_remotehost
_________________

______________CODE_______________
select
iptable.location
, count(pl_ipaddress)
from (
select distinct
pl_sessionid
, pl_ipaddress
, pl_remotehost
from PageLog
where pl_datetime
between '2003-12-25 00:00:00'
and '2003-12-25 23:59:59'
and pl_ipaddress <> ''
) as dt_pagelog
inner
join iptable
on dt_pagelog.pl_ipaddress
between iptable.ip_start
and iptable.ip_end
group
by iptable.location
order
by count(pl_ipaddress) desc
___________END CODE_________________

1) If i run this i get :
--> "Column dt_pagelog.pl_ipaddress is invalid in the select list because it's not contained in a aggregate function or in the GROUP BY clause.

2) If i include it in the GROUP BY i get :
--> The text, ntext and image datatypes cannot be used in WHERE, HAVING or ON clause, except with the LIKE or IS NULL predicate.

Soo.. how on earth should i put this right to get to use it with a INNER JOIN, since that has to have a ON to it ?

Not very familiar with INNER JOIN's so any help will be very much appreciated..

Best regards
Mirador.select a.col1, a.col2, b.col1, b.colxxxx
from table1 as a
inner join table2 as b
on a.col1 = b.col1
where a.col2 = value|||Hi Rushi and thanx for your reply..

What am i to do with the SELECT DISTINCT ......... FROM PageLog ?

Should i add PageLog.fieldname to each of the sentences in the select there to ?

Mirador.|||select distinct a.location, .........
from pagelog as a
inner join iptable as b
on a.--- = b.========

In inner join syntax
you have to join the common fields of the 2 tables in inner join clause and the actual where condition in where clause.|||the error message doesn't make sense

"dt_pagelog.pl_ipaddress is invalid in the select list because it's not contained in a aggregate function"

dt_pagelog.pl_ipaddress is contained in a aggregate function -- the COUNT()

maybe it's the subquery's DISTINCT, although i seriously doubt it

try this:select iptable.location
, count(dt_pagelog.pl_ipaddress)
from (
select pl_sessionid
, pl_ipaddress
, pl_remotehost
from PageLog
where pl_datetime
between '2003-12-25 00:00:00'
and '2003-12-25 23:59:59'
and pl_ipaddress <> ''
group
by pl_sessionid
, pl_ipaddress
, pl_remotehost
) as dt_pagelog
inner
join iptable
on dt_pagelog.pl_ipaddress
between iptable.ip_start
and iptable.ip_end
group
by iptable.location
order
by count(dt_pagelog.pl_ipaddress) desc|||Hi again Rudy :)

This is indeed a bit weird...
Tried the exact query u posted and got this errormsg :

-----Error------

Server: Msg 306, Level 16, State 1, Line 1
The text, ntext, and image data types cannot be used in the WHERE, HAVING, or ON clause, except with the LIKE or IS NULL predicates.

Server: Msg 306, Level 16, State 1, Line 1
The text, ntext, and image data types cannot be used in the WHERE, HAVING, or ON clause, except with the LIKE or IS NULL predicates.
------------|||okay, see if you can understand where i'm going with this...

which one of your columns is text, ntext, or image?

and just to give you a little advnace notice, my next question will be why

session id, ip address, remote host, location -- those all sound like varchars to me|||Oh my... :( i finally got it...

The ip_start and ip_end was text while the others were varchar..

As soon as i put all of them to varchar it worked...

Well.. ended up with a easy solution after all!... I thought this had to be something really really tricky stuff..

Now i know :) hehe.. varchars dont match very good with text when it comes to comparing..

Thanx for all your help Rudy...

Best regards
Terje.

Copying tables

I am able to query several different msde databases on my network using
Query Analyzer from my pc. Using SQL Query Analyzer from my pc, I need to
copy a table from a database found on my local msde installation to a
database on another pc's msde installation. I was trying to find the SQL
Query syntax on Books Online but I had no luck. Can you help?
Thanks,
Ademar Nunes
Hi,
See BCP OUT and BCP IN in books online.
Thanks
Hari
SQL Server MVP
"Ademar" <Ademar@.noneofyourbusiness.com> wrote in message
news:uo2iGb6uEHA.1260@.TK2MSFTNGP12.phx.gbl...
>I am able to query several different msde databases on my network using
> Query Analyzer from my pc. Using SQL Query Analyzer from my pc, I need
> to
> copy a table from a database found on my local msde installation to a
> database on another pc's msde installation. I was trying to find the SQL
> Query syntax on Books Online but I had no luck. Can you help?
> --
> Thanks,
> Ademar Nunes
>
|||I did, but I was unable to make it work. I tried again, and I'm still
unable. Can you help?
Thanks,
Ademar Nunes
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:eFagfq8uEHA.568@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Hi,
> See BCP OUT and BCP IN in books online.
>
> --
> Thanks
> Hari
> SQL Server MVP
>
> "Ademar" <Ademar@.noneofyourbusiness.com> wrote in message
> news:uo2iGb6uEHA.1260@.TK2MSFTNGP12.phx.gbl...
SQL
>
sql

Copying files between servers

I'm trying to copy files between 2 servers on a local network from within a
SQL Job (and Query Analyzer) using xp_cmdshell.xcopy but get an access
denied message returned.

I'm able to successfully do the copy from within a command window so think
the problem has something to do with using the default SQL Server account
but as yet I don't know how to resolve.

Any help/suggestions would be much appreciated.Am guessng that you are running MS-SQL using the local SYSTEM account.
System does not have access to network devices.

Your two options are to create another account and configure MS-SQL and
agent to use that account. You may beable to get away with just
configuring agent for that but depends on how you are doing the
command.

Or to go into policy editor and allowing the system account to have
netowrk priviledges. This is a major security hole and should not be
done.

copying a table from one database to another

Hey

in query analyzer, how do you copy a table form one db to another db

i thort it was something like

select * into dbo.databaseA.tableNew from dbo.databaseB.tableOld

cheers

insert into databaseA..tableNewselect *From databaseb..tableOld

|||

The difference between SELECT INTO and INSERT INTO is that with INSERT the table must already exist. SELECT INTO creates a new table.

Your original query looked okay, assuming that you wanted a new table tableNew. What error were you getting? You might also have a permissions problem since you are going from one database to another.

Don

|||

hey

yeah... thats why i 'd like to use select into or otherwise i'll have to create the other table ( not as fast )

when i try to run

select * into dbo.databaseA.tableNew from dbo.databaseB.tableOld

i get

Server: Msg 208, Level 16, State 1, Line 1
Invalid object name dbo.databaseB.tableOld

i've tripple checked the spelling and tried the same thing with other databases on other computers and got the same error so i'm sure its not a permission error or anything, must be syntax

cheers

|||Use this:select * into databaseA..tableNew from databaseB..tableOld|||

Matt-dot-net:

Use this:select * into databaseA..tableNew from databaseB..tableOld

<groan> I HATE when I miss things like that!

Don

|||

cheers bruva, just what i needed

copy stored procedure from dbase 1 to dbase2

Hi just wondering if there is an easy way to copy stored procedures from one
dbase to another on the same server, possibly using query analyzer, or vs.net
server explorer? There are around 100 procedures so hoping do not have to do
each one individually. Thanks.
Paul G
Software engineer.
You could use Enterprise Manager to script all stored procedures to a file.
You could then execute that file within your other database.
If you have your stored procedures stored within a version control system
you could simply grab the most recent files and execute them within the
appropriate database. Source control is nice because you can look back at
the changes of the stored procedure. You can see what changed, who changed
it, and why. You can also roll back to a previous version if necessary. If
you don't store your stored procedures within source control I encourage you
to do so.
Keith
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:656816D1-61F2-406C-8B0E-32466BD8F812@.microsoft.com...
> Hi just wondering if there is an easy way to copy stored procedures from
one
> dbase to another on the same server, possibly using query analyzer, or
vs.net
> server explorer? There are around 100 procedures so hoping do not have to
do
> each one individually. Thanks.
> --
> Paul G
> Software engineer.
|||Thanks for the response. I have enterprise manager but am not too familiar
with it, not even sure how to connect to the desired database. Also have
visual source safe on my machine, not sure if vss would have to be on the
server.
Paul.
"Keith Kratochvil" wrote:

> You could use Enterprise Manager to script all stored procedures to a file.
> You could then execute that file within your other database.
> If you have your stored procedures stored within a version control system
> you could simply grab the most recent files and execute them within the
> appropriate database. Source control is nice because you can look back at
> the changes of the stored procedure. You can see what changed, who changed
> it, and why. You can also roll back to a previous version if necessary. If
> you don't store your stored procedures within source control I encourage you
> to do so.
> --
> Keith
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:656816D1-61F2-406C-8B0E-32466BD8F812@.microsoft.com...
> one
> vs.net
> do
>
|||EM:
connect to the server
expand the databases tab
right-click a database that you want to script the stored procedures for
choose All Tasks and Generate SQL Script
Click the "show all" button
put a checkmark in the "all stored procedures" checkbox
The options tab allows you to create one file or one file per object
hit preview (or ok)
VSS is more of a client side tool. We create stored procedures using Query
Analyzer. We save each stored procedure to a text file of the same name
(with a .sql extension) and then we check these into VSS. To modify a
stored procedure we check it our of VSS, open the file within Query
Analyzer, make the change, test it, save the file, recreate the stored
procedure (Ctrl-E), and check the file back into VSS.
Keith
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:2EEEA26E-913E-4313-8EDB-C1842CD918F3@.microsoft.com...
> Thanks for the response. I have enterprise manager but am not too
familiar[vbcol=seagreen]
> with it, not even sure how to connect to the desired database. Also have
> visual source safe on my machine, not sure if vss would have to be on the
> server.
> Paul.
> "Keith Kratochvil" wrote:
file.[vbcol=seagreen]
system[vbcol=seagreen]
at[vbcol=seagreen]
changed[vbcol=seagreen]
If[vbcol=seagreen]
you[vbcol=seagreen]
from[vbcol=seagreen]
have to[vbcol=seagreen]
|||thanks for the additional information, just wondering how to connect to the
database with EM? paul.
"Keith Kratochvil" wrote:

> EM:
> connect to the server
> expand the databases tab
> right-click a database that you want to script the stored procedures for
> choose All Tasks and Generate SQL Script
> Click the "show all" button
> put a checkmark in the "all stored procedures" checkbox
> The options tab allows you to create one file or one file per object
> hit preview (or ok)
> VSS is more of a client side tool. We create stored procedures using Query
> Analyzer. We save each stored procedure to a text file of the same name
> (with a .sql extension) and then we check these into VSS. To modify a
> stored procedure we check it our of VSS, open the file within Query
> Analyzer, make the change, test it, save the file, recreate the stored
> procedure (Ctrl-E), and check the file back into VSS.
> --
> Keith
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:2EEEA26E-913E-4313-8EDB-C1842CD918F3@.microsoft.com...
> familiar
> file.
> system
> at
> changed
> If
> you
> from
> have to
>
|||When you generate the script for your stored procedures, you also have the
choice the generate the permissions for each SP (that's if you will have the
same database users on the destination database).
Sasan Saidi, MSc in CS
Senior DBA
Brascan Business Services
"I saw it work in a cartoon once so I am pretty sure I can do it."
"Paul" wrote:
[vbcol=seagreen]
> thanks for the additional information, just wondering how to connect to the
> database with EM? paul.
> "Keith Kratochvil" wrote:
|||ok thanks for the information. Actually my application does authentication,
password and username check and the connection uses a dedicated username and
password for the application so all stored procedures are accessable for this
dedicated username and password.
"Sasan Saidi" wrote:
[vbcol=seagreen]
> When you generate the script for your stored procedures, you also have the
> choice the generate the permissions for each SP (that's if you will have the
> same database users on the destination database).
> --
> Sasan Saidi, MSc in CS
> Senior DBA
> Brascan Business Services
> "I saw it work in a cartoon once so I am pretty sure I can do it."
>
> "Paul" wrote:
|||In order to connect to a [database] server you have to register that server
within the Enterprise Manager GUI. I am guessing that you already did this
step and you want to know how to "connect" to a specific database.
Open Enterprise Manager
Connect to a specific server.
Expand the databases folder
Now you will see each of the databases
Right-click on one and choose All Tasks and Generate SQL Script
Keith
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:7DF6A163-273B-4314-AFCB-AE46382D3CA3@.microsoft.com...
> thanks for the additional information, just wondering how to connect to
the[vbcol=seagreen]
> database with EM? paul.
> "Keith Kratochvil" wrote:
Query[vbcol=seagreen]
have[vbcol=seagreen]
the[vbcol=seagreen]
a[vbcol=seagreen]
the[vbcol=seagreen]
back[vbcol=seagreen]
necessary.[vbcol=seagreen]
encourage[vbcol=seagreen]
procedures[vbcol=seagreen]
analyzer, or[vbcol=seagreen]
|||actually have not registered it yet, just getting started. Thanks for the
information.
"Keith Kratochvil" wrote:

> In order to connect to a [database] server you have to register that server
> within the Enterprise Manager GUI. I am guessing that you already did this
> step and you want to know how to "connect" to a specific database.
> Open Enterprise Manager
> Connect to a specific server.
> Expand the databases folder
> Now you will see each of the databases
> Right-click on one and choose All Tasks and Generate SQL Script
> --
> Keith
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:7DF6A163-273B-4314-AFCB-AE46382D3CA3@.microsoft.com...
> the
> Query
> have
> the
> a
> the
> back
> necessary.
> encourage
> procedures
> analyzer, or
>

copy stored procedure from dbase 1 to dbase2

Hi just wondering if there is an easy way to copy stored procedures from one
dbase to another on the same server, possibly using query analyzer, or vs.net
server explorer? There are around 100 procedures so hoping do not have to do
each one individually. Thanks.
--
Paul G
Software engineer.You could use Enterprise Manager to script all stored procedures to a file.
You could then execute that file within your other database.
If you have your stored procedures stored within a version control system
you could simply grab the most recent files and execute them within the
appropriate database. Source control is nice because you can look back at
the changes of the stored procedure. You can see what changed, who changed
it, and why. You can also roll back to a previous version if necessary. If
you don't store your stored procedures within source control I encourage you
to do so.
--
Keith
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:656816D1-61F2-406C-8B0E-32466BD8F812@.microsoft.com...
> Hi just wondering if there is an easy way to copy stored procedures from
one
> dbase to another on the same server, possibly using query analyzer, or
vs.net
> server explorer? There are around 100 procedures so hoping do not have to
do
> each one individually. Thanks.
> --
> Paul G
> Software engineer.|||Thanks for the response. I have enterprise manager but am not too familiar
with it, not even sure how to connect to the desired database. Also have
visual source safe on my machine, not sure if vss would have to be on the
server.
Paul.
"Keith Kratochvil" wrote:
> You could use Enterprise Manager to script all stored procedures to a file.
> You could then execute that file within your other database.
> If you have your stored procedures stored within a version control system
> you could simply grab the most recent files and execute them within the
> appropriate database. Source control is nice because you can look back at
> the changes of the stored procedure. You can see what changed, who changed
> it, and why. You can also roll back to a previous version if necessary. If
> you don't store your stored procedures within source control I encourage you
> to do so.
> --
> Keith
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:656816D1-61F2-406C-8B0E-32466BD8F812@.microsoft.com...
> > Hi just wondering if there is an easy way to copy stored procedures from
> one
> > dbase to another on the same server, possibly using query analyzer, or
> vs.net
> > server explorer? There are around 100 procedures so hoping do not have to
> do
> > each one individually. Thanks.
> > --
> > Paul G
> > Software engineer.
>|||EM:
connect to the server
expand the databases tab
right-click a database that you want to script the stored procedures for
choose All Tasks and Generate SQL Script
Click the "show all" button
put a checkmark in the "all stored procedures" checkbox
The options tab allows you to create one file or one file per object
hit preview (or ok)
VSS is more of a client side tool. We create stored procedures using Query
Analyzer. We save each stored procedure to a text file of the same name
(with a .sql extension) and then we check these into VSS. To modify a
stored procedure we check it our of VSS, open the file within Query
Analyzer, make the change, test it, save the file, recreate the stored
procedure (Ctrl-E), and check the file back into VSS.
--
Keith
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:2EEEA26E-913E-4313-8EDB-C1842CD918F3@.microsoft.com...
> Thanks for the response. I have enterprise manager but am not too
familiar
> with it, not even sure how to connect to the desired database. Also have
> visual source safe on my machine, not sure if vss would have to be on the
> server.
> Paul.
> "Keith Kratochvil" wrote:
> > You could use Enterprise Manager to script all stored procedures to a
file.
> > You could then execute that file within your other database.
> >
> > If you have your stored procedures stored within a version control
system
> > you could simply grab the most recent files and execute them within the
> > appropriate database. Source control is nice because you can look back
at
> > the changes of the stored procedure. You can see what changed, who
changed
> > it, and why. You can also roll back to a previous version if necessary.
If
> > you don't store your stored procedures within source control I encourage
you
> > to do so.
> >
> > --
> > Keith
> >
> >
> > "Paul" <Paul@.discussions.microsoft.com> wrote in message
> > news:656816D1-61F2-406C-8B0E-32466BD8F812@.microsoft.com...
> > > Hi just wondering if there is an easy way to copy stored procedures
from
> > one
> > > dbase to another on the same server, possibly using query analyzer, or
> > vs.net
> > > server explorer? There are around 100 procedures so hoping do not
have to
> > do
> > > each one individually. Thanks.
> > > --
> > > Paul G
> > > Software engineer.
> >
> >|||thanks for the additional information, just wondering how to connect to the
database with EM? paul.
"Keith Kratochvil" wrote:
> EM:
> connect to the server
> expand the databases tab
> right-click a database that you want to script the stored procedures for
> choose All Tasks and Generate SQL Script
> Click the "show all" button
> put a checkmark in the "all stored procedures" checkbox
> The options tab allows you to create one file or one file per object
> hit preview (or ok)
> VSS is more of a client side tool. We create stored procedures using Query
> Analyzer. We save each stored procedure to a text file of the same name
> (with a .sql extension) and then we check these into VSS. To modify a
> stored procedure we check it our of VSS, open the file within Query
> Analyzer, make the change, test it, save the file, recreate the stored
> procedure (Ctrl-E), and check the file back into VSS.
> --
> Keith
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:2EEEA26E-913E-4313-8EDB-C1842CD918F3@.microsoft.com...
> > Thanks for the response. I have enterprise manager but am not too
> familiar
> > with it, not even sure how to connect to the desired database. Also have
> > visual source safe on my machine, not sure if vss would have to be on the
> > server.
> > Paul.
> > "Keith Kratochvil" wrote:
> >
> > > You could use Enterprise Manager to script all stored procedures to a
> file.
> > > You could then execute that file within your other database.
> > >
> > > If you have your stored procedures stored within a version control
> system
> > > you could simply grab the most recent files and execute them within the
> > > appropriate database. Source control is nice because you can look back
> at
> > > the changes of the stored procedure. You can see what changed, who
> changed
> > > it, and why. You can also roll back to a previous version if necessary.
> If
> > > you don't store your stored procedures within source control I encourage
> you
> > > to do so.
> > >
> > > --
> > > Keith
> > >
> > >
> > > "Paul" <Paul@.discussions.microsoft.com> wrote in message
> > > news:656816D1-61F2-406C-8B0E-32466BD8F812@.microsoft.com...
> > > > Hi just wondering if there is an easy way to copy stored procedures
> from
> > > one
> > > > dbase to another on the same server, possibly using query analyzer, or
> > > vs.net
> > > > server explorer? There are around 100 procedures so hoping do not
> have to
> > > do
> > > > each one individually. Thanks.
> > > > --
> > > > Paul G
> > > > Software engineer.
> > >
> > >
>|||When you generate the script for your stored procedures, you also have the
choice the generate the permissions for each SP (that's if you will have the
same database users on the destination database).
--
Sasan Saidi, MSc in CS
Senior DBA
Brascan Business Services
"I saw it work in a cartoon once so I am pretty sure I can do it."
"Paul" wrote:
> thanks for the additional information, just wondering how to connect to the
> database with EM? paul.
> "Keith Kratochvil" wrote:
> > EM:
> > connect to the server
> > expand the databases tab
> > right-click a database that you want to script the stored procedures for
> > choose All Tasks and Generate SQL Script
> > Click the "show all" button
> > put a checkmark in the "all stored procedures" checkbox
> > The options tab allows you to create one file or one file per object
> > hit preview (or ok)
> >
> > VSS is more of a client side tool. We create stored procedures using Query
> > Analyzer. We save each stored procedure to a text file of the same name
> > (with a .sql extension) and then we check these into VSS. To modify a
> > stored procedure we check it our of VSS, open the file within Query
> > Analyzer, make the change, test it, save the file, recreate the stored
> > procedure (Ctrl-E), and check the file back into VSS.
> >
> > --
> > Keith
> >
> >
> > "Paul" <Paul@.discussions.microsoft.com> wrote in message
> > news:2EEEA26E-913E-4313-8EDB-C1842CD918F3@.microsoft.com...
> > > Thanks for the response. I have enterprise manager but am not too
> > familiar
> > > with it, not even sure how to connect to the desired database. Also have
> > > visual source safe on my machine, not sure if vss would have to be on the
> > > server.
> > > Paul.
> > > "Keith Kratochvil" wrote:
> > >
> > > > You could use Enterprise Manager to script all stored procedures to a
> > file.
> > > > You could then execute that file within your other database.
> > > >
> > > > If you have your stored procedures stored within a version control
> > system
> > > > you could simply grab the most recent files and execute them within the
> > > > appropriate database. Source control is nice because you can look back
> > at
> > > > the changes of the stored procedure. You can see what changed, who
> > changed
> > > > it, and why. You can also roll back to a previous version if necessary.
> > If
> > > > you don't store your stored procedures within source control I encourage
> > you
> > > > to do so.
> > > >
> > > > --
> > > > Keith
> > > >
> > > >
> > > > "Paul" <Paul@.discussions.microsoft.com> wrote in message
> > > > news:656816D1-61F2-406C-8B0E-32466BD8F812@.microsoft.com...
> > > > > Hi just wondering if there is an easy way to copy stored procedures
> > from
> > > > one
> > > > > dbase to another on the same server, possibly using query analyzer, or
> > > > vs.net
> > > > > server explorer? There are around 100 procedures so hoping do not
> > have to
> > > > do
> > > > > each one individually. Thanks.
> > > > > --
> > > > > Paul G
> > > > > Software engineer.
> > > >
> > > >
> >
> >|||ok thanks for the information. Actually my application does authentication,
password and username check and the connection uses a dedicated username and
password for the application so all stored procedures are accessable for this
dedicated username and password.
"Sasan Saidi" wrote:
> When you generate the script for your stored procedures, you also have the
> choice the generate the permissions for each SP (that's if you will have the
> same database users on the destination database).
> --
> Sasan Saidi, MSc in CS
> Senior DBA
> Brascan Business Services
> "I saw it work in a cartoon once so I am pretty sure I can do it."
>
> "Paul" wrote:
> > thanks for the additional information, just wondering how to connect to the
> > database with EM? paul.
> >
> > "Keith Kratochvil" wrote:
> >
> > > EM:
> > > connect to the server
> > > expand the databases tab
> > > right-click a database that you want to script the stored procedures for
> > > choose All Tasks and Generate SQL Script
> > > Click the "show all" button
> > > put a checkmark in the "all stored procedures" checkbox
> > > The options tab allows you to create one file or one file per object
> > > hit preview (or ok)
> > >
> > > VSS is more of a client side tool. We create stored procedures using Query
> > > Analyzer. We save each stored procedure to a text file of the same name
> > > (with a .sql extension) and then we check these into VSS. To modify a
> > > stored procedure we check it our of VSS, open the file within Query
> > > Analyzer, make the change, test it, save the file, recreate the stored
> > > procedure (Ctrl-E), and check the file back into VSS.
> > >
> > > --
> > > Keith
> > >
> > >
> > > "Paul" <Paul@.discussions.microsoft.com> wrote in message
> > > news:2EEEA26E-913E-4313-8EDB-C1842CD918F3@.microsoft.com...
> > > > Thanks for the response. I have enterprise manager but am not too
> > > familiar
> > > > with it, not even sure how to connect to the desired database. Also have
> > > > visual source safe on my machine, not sure if vss would have to be on the
> > > > server.
> > > > Paul.
> > > > "Keith Kratochvil" wrote:
> > > >
> > > > > You could use Enterprise Manager to script all stored procedures to a
> > > file.
> > > > > You could then execute that file within your other database.
> > > > >
> > > > > If you have your stored procedures stored within a version control
> > > system
> > > > > you could simply grab the most recent files and execute them within the
> > > > > appropriate database. Source control is nice because you can look back
> > > at
> > > > > the changes of the stored procedure. You can see what changed, who
> > > changed
> > > > > it, and why. You can also roll back to a previous version if necessary.
> > > If
> > > > > you don't store your stored procedures within source control I encourage
> > > you
> > > > > to do so.
> > > > >
> > > > > --
> > > > > Keith
> > > > >
> > > > >
> > > > > "Paul" <Paul@.discussions.microsoft.com> wrote in message
> > > > > news:656816D1-61F2-406C-8B0E-32466BD8F812@.microsoft.com...
> > > > > > Hi just wondering if there is an easy way to copy stored procedures
> > > from
> > > > > one
> > > > > > dbase to another on the same server, possibly using query analyzer, or
> > > > > vs.net
> > > > > > server explorer? There are around 100 procedures so hoping do not
> > > have to
> > > > > do
> > > > > > each one individually. Thanks.
> > > > > > --
> > > > > > Paul G
> > > > > > Software engineer.
> > > > >
> > > > >
> > >
> > >|||In order to connect to a [database] server you have to register that server
within the Enterprise Manager GUI. I am guessing that you already did this
step and you want to know how to "connect" to a specific database.
Open Enterprise Manager
Connect to a specific server.
Expand the databases folder
Now you will see each of the databases
Right-click on one and choose All Tasks and Generate SQL Script
--
Keith
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:7DF6A163-273B-4314-AFCB-AE46382D3CA3@.microsoft.com...
> thanks for the additional information, just wondering how to connect to
the
> database with EM? paul.
> "Keith Kratochvil" wrote:
> > EM:
> > connect to the server
> > expand the databases tab
> > right-click a database that you want to script the stored procedures for
> > choose All Tasks and Generate SQL Script
> > Click the "show all" button
> > put a checkmark in the "all stored procedures" checkbox
> > The options tab allows you to create one file or one file per object
> > hit preview (or ok)
> >
> > VSS is more of a client side tool. We create stored procedures using
Query
> > Analyzer. We save each stored procedure to a text file of the same name
> > (with a .sql extension) and then we check these into VSS. To modify a
> > stored procedure we check it our of VSS, open the file within Query
> > Analyzer, make the change, test it, save the file, recreate the stored
> > procedure (Ctrl-E), and check the file back into VSS.
> >
> > --
> > Keith
> >
> >
> > "Paul" <Paul@.discussions.microsoft.com> wrote in message
> > news:2EEEA26E-913E-4313-8EDB-C1842CD918F3@.microsoft.com...
> > > Thanks for the response. I have enterprise manager but am not too
> > familiar
> > > with it, not even sure how to connect to the desired database. Also
have
> > > visual source safe on my machine, not sure if vss would have to be on
the
> > > server.
> > > Paul.
> > > "Keith Kratochvil" wrote:
> > >
> > > > You could use Enterprise Manager to script all stored procedures to
a
> > file.
> > > > You could then execute that file within your other database.
> > > >
> > > > If you have your stored procedures stored within a version control
> > system
> > > > you could simply grab the most recent files and execute them within
the
> > > > appropriate database. Source control is nice because you can look
back
> > at
> > > > the changes of the stored procedure. You can see what changed, who
> > changed
> > > > it, and why. You can also roll back to a previous version if
necessary.
> > If
> > > > you don't store your stored procedures within source control I
encourage
> > you
> > > > to do so.
> > > >
> > > > --
> > > > Keith
> > > >
> > > >
> > > > "Paul" <Paul@.discussions.microsoft.com> wrote in message
> > > > news:656816D1-61F2-406C-8B0E-32466BD8F812@.microsoft.com...
> > > > > Hi just wondering if there is an easy way to copy stored
procedures
> > from
> > > > one
> > > > > dbase to another on the same server, possibly using query
analyzer, or
> > > > vs.net
> > > > > server explorer? There are around 100 procedures so hoping do not
> > have to
> > > > do
> > > > > each one individually. Thanks.
> > > > > --
> > > > > Paul G
> > > > > Software engineer.
> > > >
> > > >
> >
> >|||actually have not registered it yet, just getting started. Thanks for the
information.
"Keith Kratochvil" wrote:
> In order to connect to a [database] server you have to register that server
> within the Enterprise Manager GUI. I am guessing that you already did this
> step and you want to know how to "connect" to a specific database.
> Open Enterprise Manager
> Connect to a specific server.
> Expand the databases folder
> Now you will see each of the databases
> Right-click on one and choose All Tasks and Generate SQL Script
> --
> Keith
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:7DF6A163-273B-4314-AFCB-AE46382D3CA3@.microsoft.com...
> > thanks for the additional information, just wondering how to connect to
> the
> > database with EM? paul.
> >
> > "Keith Kratochvil" wrote:
> >
> > > EM:
> > > connect to the server
> > > expand the databases tab
> > > right-click a database that you want to script the stored procedures for
> > > choose All Tasks and Generate SQL Script
> > > Click the "show all" button
> > > put a checkmark in the "all stored procedures" checkbox
> > > The options tab allows you to create one file or one file per object
> > > hit preview (or ok)
> > >
> > > VSS is more of a client side tool. We create stored procedures using
> Query
> > > Analyzer. We save each stored procedure to a text file of the same name
> > > (with a .sql extension) and then we check these into VSS. To modify a
> > > stored procedure we check it our of VSS, open the file within Query
> > > Analyzer, make the change, test it, save the file, recreate the stored
> > > procedure (Ctrl-E), and check the file back into VSS.
> > >
> > > --
> > > Keith
> > >
> > >
> > > "Paul" <Paul@.discussions.microsoft.com> wrote in message
> > > news:2EEEA26E-913E-4313-8EDB-C1842CD918F3@.microsoft.com...
> > > > Thanks for the response. I have enterprise manager but am not too
> > > familiar
> > > > with it, not even sure how to connect to the desired database. Also
> have
> > > > visual source safe on my machine, not sure if vss would have to be on
> the
> > > > server.
> > > > Paul.
> > > > "Keith Kratochvil" wrote:
> > > >
> > > > > You could use Enterprise Manager to script all stored procedures to
> a
> > > file.
> > > > > You could then execute that file within your other database.
> > > > >
> > > > > If you have your stored procedures stored within a version control
> > > system
> > > > > you could simply grab the most recent files and execute them within
> the
> > > > > appropriate database. Source control is nice because you can look
> back
> > > at
> > > > > the changes of the stored procedure. You can see what changed, who
> > > changed
> > > > > it, and why. You can also roll back to a previous version if
> necessary.
> > > If
> > > > > you don't store your stored procedures within source control I
> encourage
> > > you
> > > > > to do so.
> > > > >
> > > > > --
> > > > > Keith
> > > > >
> > > > >
> > > > > "Paul" <Paul@.discussions.microsoft.com> wrote in message
> > > > > news:656816D1-61F2-406C-8B0E-32466BD8F812@.microsoft.com...
> > > > > > Hi just wondering if there is an easy way to copy stored
> procedures
> > > from
> > > > > one
> > > > > > dbase to another on the same server, possibly using query
> analyzer, or
> > > > > vs.net
> > > > > > server explorer? There are around 100 procedures so hoping do not
> > > have to
> > > > > do
> > > > > > each one individually. Thanks.
> > > > > > --
> > > > > > Paul G
> > > > > > Software engineer.
> > > > >
> > > > >
> > >
> > >
>

copy stored procedure from dbase 1 to dbase2

Hi just wondering if there is an easy way to copy stored procedures from one
dbase to another on the same server, possibly using query analyzer, or vs.ne
t
server explorer? There are around 100 procedures so hoping do not have to d
o
each one individually. Thanks.
--
Paul G
Software engineer.You could use Enterprise Manager to script all stored procedures to a file.
You could then execute that file within your other database.
If you have your stored procedures stored within a version control system
you could simply grab the most recent files and execute them within the
appropriate database. Source control is nice because you can look back at
the changes of the stored procedure. You can see what changed, who changed
it, and why. You can also roll back to a previous version if necessary. If
you don't store your stored procedures within source control I encourage you
to do so.
Keith
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:656816D1-61F2-406C-8B0E-32466BD8F812@.microsoft.com...
> Hi just wondering if there is an easy way to copy stored procedures from
one
> dbase to another on the same server, possibly using query analyzer, or
vs.net
> server explorer? There are around 100 procedures so hoping do not have to
do
> each one individually. Thanks.
> --
> Paul G
> Software engineer.|||Thanks for the response. I have enterprise manager but am not too familiar
with it, not even sure how to connect to the desired database. Also have
visual source safe on my machine, not sure if vss would have to be on the
server.
Paul.
"Keith Kratochvil" wrote:

> You could use Enterprise Manager to script all stored procedures to a file
.
> You could then execute that file within your other database.
> If you have your stored procedures stored within a version control system
> you could simply grab the most recent files and execute them within the
> appropriate database. Source control is nice because you can look back at
> the changes of the stored procedure. You can see what changed, who change
d
> it, and why. You can also roll back to a previous version if necessary.
If
> you don't store your stored procedures within source control I encourage y
ou
> to do so.
> --
> Keith
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:656816D1-61F2-406C-8B0E-32466BD8F812@.microsoft.com...
> one
> vs.net
> do
>|||EM:
connect to the server
expand the databases tab
right-click a database that you want to script the stored procedures for
choose All Tasks and Generate SQL Script
Click the "show all" button
put a checkmark in the "all stored procedures" checkbox
The options tab allows you to create one file or one file per object
hit preview (or ok)
VSS is more of a client side tool. We create stored procedures using Query
Analyzer. We save each stored procedure to a text file of the same name
(with a .sql extension) and then we check these into VSS. To modify a
stored procedure we check it our of VSS, open the file within Query
Analyzer, make the change, test it, save the file, recreate the stored
procedure (Ctrl-E), and check the file back into VSS.
Keith
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:2EEEA26E-913E-4313-8EDB-C1842CD918F3@.microsoft.com...
> Thanks for the response. I have enterprise manager but am not too
familiar[vbcol=seagreen]
> with it, not even sure how to connect to the desired database. Also have
> visual source safe on my machine, not sure if vss would have to be on the
> server.
> Paul.
> "Keith Kratochvil" wrote:
>
file.[vbcol=seagreen]
system[vbcol=seagreen]
at[vbcol=seagreen]
changed[vbcol=seagreen]
If[vbcol=seagreen]
you[vbcol=seagreen]
from[vbcol=seagreen]
have to[vbcol=seagreen]|||thanks for the additional information, just wondering how to connect to the
database with EM? paul.
"Keith Kratochvil" wrote:

> EM:
> connect to the server
> expand the databases tab
> right-click a database that you want to script the stored procedures for
> choose All Tasks and Generate SQL Script
> Click the "show all" button
> put a checkmark in the "all stored procedures" checkbox
> The options tab allows you to create one file or one file per object
> hit preview (or ok)
> VSS is more of a client side tool. We create stored procedures using Quer
y
> Analyzer. We save each stored procedure to a text file of the same name
> (with a .sql extension) and then we check these into VSS. To modify a
> stored procedure we check it our of VSS, open the file within Query
> Analyzer, make the change, test it, save the file, recreate the stored
> procedure (Ctrl-E), and check the file back into VSS.
> --
> Keith
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:2EEEA26E-913E-4313-8EDB-C1842CD918F3@.microsoft.com...
> familiar
> file.
> system
> at
> changed
> If
> you
> from
> have to
>|||When you generate the script for your stored procedures, you also have the
choice the generate the permissions for each SP (that's if you will have the
same database users on the destination database).
--
Sasan Saidi, MSc in CS
Senior DBA
Brascan Business Services
"I saw it work in a cartoon once so I am pretty sure I can do it."
"Paul" wrote:
[vbcol=seagreen]
> thanks for the additional information, just wondering how to connect to th
e
> database with EM? paul.
> "Keith Kratochvil" wrote:
>|||ok thanks for the information. Actually my application does authentication,
password and username check and the connection uses a dedicated username and
password for the application so all stored procedures are accessable for thi
s
dedicated username and password.
"Sasan Saidi" wrote:
[vbcol=seagreen]
> When you generate the script for your stored procedures, you also have the
> choice the generate the permissions for each SP (that's if you will have t
he
> same database users on the destination database).
> --
> Sasan Saidi, MSc in CS
> Senior DBA
> Brascan Business Services
> "I saw it work in a cartoon once so I am pretty sure I can do it."
>
> "Paul" wrote:
>|||In order to connect to a [database] server you have to register that ser
ver
within the Enterprise Manager GUI. I am guessing that you already did this
step and you want to know how to "connect" to a specific database.
Open Enterprise Manager
Connect to a specific server.
Expand the databases folder
Now you will see each of the databases
Right-click on one and choose All Tasks and Generate SQL Script
Keith
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:7DF6A163-273B-4314-AFCB-AE46382D3CA3@.microsoft.com...
> thanks for the additional information, just wondering how to connect to
the[vbcol=seagreen]
> database with EM? paul.
> "Keith Kratochvil" wrote:
>
Query[vbcol=seagreen]
have[vbcol=seagreen]
the[vbcol=seagreen]
a[vbcol=seagreen]
the[vbcol=seagreen]
back[vbcol=seagreen]
necessary.[vbcol=seagreen]
encourage[vbcol=seagreen]
procedures[vbcol=seagreen]
analyzer, or[vbcol=seagreen]|||actually have not registered it yet, just getting started. Thanks for the
information.
"Keith Kratochvil" wrote:

> In order to connect to a [database] server you have to register that s
erver
> within the Enterprise Manager GUI. I am guessing that you already did thi
s
> step and you want to know how to "connect" to a specific database.
> Open Enterprise Manager
> Connect to a specific server.
> Expand the databases folder
> Now you will see each of the databases
> Right-click on one and choose All Tasks and Generate SQL Script
> --
> Keith
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:7DF6A163-273B-4314-AFCB-AE46382D3CA3@.microsoft.com...
> the
> Query
> have
> the
> a
> the
> back
> necessary.
> encourage
> procedures
> analyzer, or
>