Newbie Question on DTS Object Trasnfer

Very new to SQL Server. Was asked to copy some tables from a Prod to a
Test database in the same instance. (This system is not yet in
production.)

I used the Microsoft course book guidance for course 2072 A, the lab
exercise in Module 9, page 33 to give me some guidance. In the exercise
they were copying tables as well as views.

While in the wizard the guidance said to uncheck 'copy primary and
foreign keys' and uncheck 'copy full text indexes'.

While I realize that might be because of the nature of copying views, I
used that guidance to copy the tables. Is this correct or should I do
it over and copy both the keys and full text indexes?

I DID copy base indexes.

Thanks in advance.

GerryDataPro (datapro01@.yahoo.com) writes:

Quote:

Originally Posted by

Very new to SQL Server. Was asked to copy some tables from a Prod to a
Test database in the same instance. (This system is not yet in
production.)
>
I used the Microsoft course book guidance for course 2072 A, the lab
exercise in Module 9, page 33 to give me some guidance. In the exercise
they were copying tables as well as views.
>
While in the wizard the guidance said to uncheck 'copy primary and
foreign keys' and uncheck 'copy full text indexes'.
>
While I realize that might be because of the nature of copying views, I
used that guidance to copy the tables. Is this correct or should I do
it over and copy both the keys and full text indexes?

You should certainly copy the keys. I don't use full-text, so I don't know
if there is any good reason why they are not copied by default. Then
again, with a little luck your database does have full-text indexes.

--
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|||Thanks...ran a few tests looking at the dll before and after trying
different options and kept the keys. Worked out fine...makes you wonder
why Microsoft would use an example of a dts move in its book for
newbies that would only confuse the issue.

Erland Sommarskog wrote:

Quote:

Originally Posted by

DataPro (datapro01@.yahoo.com) writes:

Quote:

Originally Posted by

Very new to SQL Server. Was asked to copy some tables from a Prod to a
Test database in the same instance. (This system is not yet in
production.)

I used the Microsoft course book guidance for course 2072 A, the lab
exercise in Module 9, page 33 to give me some guidance. In the exercise
they were copying tables as well as views.

While in the wizard the guidance said to uncheck 'copy primary and
foreign keys' and uncheck 'copy full text indexes'.

While I realize that might be because of the nature of copying views, I
used that guidance to copy the tables. Is this correct or should I do
it over and copy both the keys and full text indexes?

>
You should certainly copy the keys. I don't use full-text, so I don't know
if there is any good reason why they are not copied by default. Then
again, with a little luck your database does have full-text indexes.
>
--
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

newbie question on DTS

Hi,
I am trying to create a DTS package to transfer tables and views from SQL
Server to MySQL, the tables and views I want to transfer do not exist in
MySQL yet. In DTS designer I created the connections to SQL server and
MySQL server without problems, then tried to use "Data Transform Task" to
transfer, the problem I have, "Data Transform Task" tries to map tables
from SQL Server to the tables in MySQL, that defies my whole purpose.
Please help! I am using SQL Server 2000 Developer Edition.
I may be incorrect on this, but I don't think that you can transfer objects
(i.e., table schema) to other vendors' databases.
That would require that the DTS Import/Export Wizard have intimate knowledge
of each vendors permutation of the SQL DDL.
Of course, as inferred above, I haven't attempted to transfer objects to
MySQL. I would expect the DTS Export Wizard to provide me the option to
transfer the table as well as the data IF it were possible.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Raymond Du" <rdrd@.yahoo.com> wrote in message
news:eTEI4moBHHA.4024@.TK2MSFTNGP04.phx.gbl...
> Hi,
> I am trying to create a DTS package to transfer tables and views from SQL
> Server to MySQL, the tables and views I want to transfer do not exist in
> MySQL yet. In DTS designer I created the connections to SQL server and
> MySQL server without problems, then tried to use "Data Transform Task" to
> transfer, the problem I have, "Data Transform Task" tries to map tables
> from SQL Server to the tables in MySQL, that defies my whole purpose.
> Please help! I am using SQL Server 2000 Developer Edition.
>

newbie question on DTS

Hi,
I am trying to create a DTS package to transfer tables and views from SQL
Server to MySQL, the tables and views I want to transfer do not exist in
MySQL yet. In DTS designer I created the connections to SQL server and
MySQL server without problems, then tried to use "Data Transform Task" to
transfer, the problem I have, "Data Transform Task" tries to map tables
from SQL Server to the tables in MySQL, that defies my whole purpose.
Please help! I am using SQL Server 2000 Developer Edition.I may be incorrect on this, but I don't think that you can transfer objects
(i.e., table schema) to other vendors' databases.
That would require that the DTS Import/Export Wizard have intimate knowledge
of each vendors permutation of the SQL DDL.
Of course, as inferred above, I haven't attempted to transfer objects to
MySQL. I would expect the DTS Export Wizard to provide me the option to
transfer the table as well as the data IF it were possible.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Raymond Du" <rdrd@.yahoo.com> wrote in message
news:eTEI4moBHHA.4024@.TK2MSFTNGP04.phx.gbl...
> Hi,
> I am trying to create a DTS package to transfer tables and views from SQL
> Server to MySQL, the tables and views I want to transfer do not exist in
> MySQL yet. In DTS designer I created the connections to SQL server and
> MySQL server without problems, then tried to use "Data Transform Task" to
> transfer, the problem I have, "Data Transform Task" tries to map tables
> from SQL Server to the tables in MySQL, that defies my whole purpose.
> Please help! I am using SQL Server 2000 Developer Edition.
>

newbie question on DTS

Hi,
I am trying to create a DTS package to transfer tables and views from SQL
Server to MySQL, the tables and views I want to transfer do not exist in
MySQL yet. In DTS designer I created the connections to SQL server and
MySQL server without problems, then tried to use "Data Transform Task" to
transfer, the problem I have, "Data Transform Task" tries to map tables
from SQL Server to the tables in MySQL, that defies my whole purpose.
Please help! I am using SQL Server 2000 Developer Edition.I may be incorrect on this, but I don't think that you can transfer objects
(i.e., table schema) to other vendors' databases.
That would require that the DTS Import/Export Wizard have intimate knowledge
of each vendors permutation of the SQL DDL.
Of course, as inferred above, I haven't attempted to transfer objects to
MySQL. I would expect the DTS Export Wizard to provide me the option to
transfer the table as well as the data IF it were possible.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Raymond Du" <rdrd@.yahoo.com> wrote in message
news:eTEI4moBHHA.4024@.TK2MSFTNGP04.phx.gbl...
> Hi,
> I am trying to create a DTS package to transfer tables and views from SQL
> Server to MySQL, the tables and views I want to transfer do not exist in
> mysql yet. In DTS designer I created the connections to SQL server and
> mysql server without problems, then tried to use "Data Transform Task" to
> transfer, the problem I have, "Data Transform Task" tries to map tables
> from SQL Server to the tables in MySQL, that defies my whole purpose.
> Please help! I am using SQL Server 2000 Developer Edition.
>sql

Newbie question on dbo permission

Hi all,
I have a sql account in a shared hosting server and my account is able
to execute scripts to create [dbo].* tables and [dbo].* store
procedures.
however, after they are created, if I tried to execute them, I get for
example,
permission denied on object 'GetUserDataByEmail', database 'mypm',
owner 'dbo'.
so after googling around, I found out I had to do
GRANT EXECUTE ON [dbo].[GetUserDataByEmail] TO 'myuser'
then i could execute it.
so i have these questions
1. I always thought in order to be able to create [dbo].* tables, you
need to a dbo?
2. If I'm right with #1, then how come my user can't excute
[dbo].[GetUserDataByEmail], instead I had to explicilty grant execute
access to it?
3. I see in another person's code, it had "GRANT EXECUTE on
dbo.GetUserDataByEmail to public, what does public mean? anybody can
execute it? if so, then what's the point of setting [dbo] to it?
Really appreicate your 2 cents on this. Thanks
"Liming" <lmxudotnet@.gmail.com> wrote in message
news:1168466707.519839.206840@.77g2000hsv.googlegro ups.com...
> Hi all,
> I have a sql account in a shared hosting server and my account is able
> to execute scripts to create [dbo].* tables and [dbo].* store
> procedures.
> however, after they are created, if I tried to execute them, I get for
> example,
> permission denied on object 'GetUserDataByEmail', database 'mypm',
> owner 'dbo'.
> so after googling around, I found out I had to do
> GRANT EXECUTE ON [dbo].[GetUserDataByEmail] TO 'myuser'
> then i could execute it.
> so i have these questions
> 1. I always thought in order to be able to create [dbo].* tables, you
> need to a dbo?
> 2. If I'm right with #1, then how come my user can't excute
> [dbo].[GetUserDataByEmail], instead I had to explicilty grant execute
> access to it?
> 3. I see in another person's code, it had "GRANT EXECUTE on
> dbo.GetUserDataByEmail to public, what does public mean? anybody can
> execute it? if so, then what's the point of setting [dbo] to it?
> Really appreicate your 2 cents on this. Thanks
>
Is this SQL 2000 or 2005?
David
|||Hi

> 1. I always thought in order to be able to create [dbo].* tables, you
> need to a dbo?
You will have to be a member of sysadmin server role or db_owner database
role.
DBO is just a privileged user.

> 2. If I'm right with #1, then how come my user can't excute
> [dbo].[GetUserDataByEmail], instead I had to explicilty grant execute
> access to it?
If you memeber of syadmin server role or db_owner database role you don't
need EXECUTE permission

> 3. I see in another person's code, it had "GRANT EXECUTE on
> dbo.GetUserDataByEmail to public, what does public mean? anybody can
> execute it? if so, then what's the point of setting [dbo] to it?
Public means that every user can execute the sp , do you really want it
:-)))?
"Liming" <lmxudotnet@.gmail.com> wrote in message
news:1168466707.519839.206840@.77g2000hsv.googlegro ups.com...
> Hi all,
> I have a sql account in a shared hosting server and my account is able
> to execute scripts to create [dbo].* tables and [dbo].* store
> procedures.
> however, after they are created, if I tried to execute them, I get for
> example,
> permission denied on object 'GetUserDataByEmail', database 'mypm',
> owner 'dbo'.
> so after googling around, I found out I had to do
> GRANT EXECUTE ON [dbo].[GetUserDataByEmail] TO 'myuser'
> then i could execute it.
> so i have these questions
> 1. I always thought in order to be able to create [dbo].* tables, you
> need to a dbo?
> 2. If I'm right with #1, then how come my user can't excute
> [dbo].[GetUserDataByEmail], instead I had to explicilty grant execute
> access to it?
> 3. I see in another person's code, it had "GRANT EXECUTE on
> dbo.GetUserDataByEmail to public, what does public mean? anybody can
> execute it? if so, then what's the point of setting [dbo] to it?
> Really appreicate your 2 cents on this. Thanks
>

Newbie question on dbo permission

Hi all,
I have a sql account in a shared hosting server and my account is able
to execute scripts to create [dbo].* tables and [dbo].* store
procedures.
however, after they are created, if I tried to execute them, I get for
example,
permission denied on object 'GetUserDataByEmail', database 'mypm',
owner 'dbo'.
so after googling around, I found out I had to do
GRANT EXECUTE ON [dbo].[GetUserDataByEmail] TO 'myuser'
then i could execute it.
so i have these questions
1. I always thought in order to be able to create [dbo].* tables, you
need to a dbo?
2. If I'm right with #1, then how come my user can't excute
[dbo].[GetUserDataByEmail], instead I had to explicilty grant execut
e
access to it?
3. I see in another person's code, it had "GRANT EXECUTE on
dbo.GetUserDataByEmail to public, what does public mean? anybody can
execute it? if so, then what's the point of setting [dbo] to it?
Really appreicate your 2 cents on this. Thanks"Liming" <lmxudotnet@.gmail.com> wrote in message
news:1168466707.519839.206840@.77g2000hsv.googlegroups.com...
> Hi all,
> I have a sql account in a shared hosting server and my account is able
> to execute scripts to create [dbo].* tables and [dbo].* store
> procedures.
> however, after they are created, if I tried to execute them, I get for
> example,
> permission denied on object 'GetUserDataByEmail', database 'mypm',
> owner 'dbo'.
> so after googling around, I found out I had to do
> GRANT EXECUTE ON [dbo].[GetUserDataByEmail] TO 'myuser'
> then i could execute it.
> so i have these questions
> 1. I always thought in order to be able to create [dbo].* tables, you
> need to a dbo?
> 2. If I'm right with #1, then how come my user can't excute
> [dbo].[GetUserDataByEmail], instead I had to explicilty grant exec
ute
> access to it?
> 3. I see in another person's code, it had "GRANT EXECUTE on
> dbo.GetUserDataByEmail to public, what does public mean? anybody can
> execute it? if so, then what's the point of setting [dbo] to it?
> Really appreicate your 2 cents on this. Thanks
>
Is this SQL 2000 or 2005?
David|||Hi

> 1. I always thought in order to be able to create [dbo].* tables, you
> need to a dbo?
You will have to be a member of sysadmin server role or db_owner database
role.
DBO is just a privileged user.

> 2. If I'm right with #1, then how come my user can't excute
> [dbo].[GetUserDataByEmail], instead I had to explicilty grant exec
ute
> access to it?
If you memeber of syadmin server role or db_owner database role you don't
need EXECUTE permission

> 3. I see in another person's code, it had "GRANT EXECUTE on
> dbo.GetUserDataByEmail to public, what does public mean? anybody can
> execute it? if so, then what's the point of setting [dbo] to it?
Public means that every user can execute the sp , do you really want it
:-)))?
"Liming" <lmxudotnet@.gmail.com> wrote in message
news:1168466707.519839.206840@.77g2000hsv.googlegroups.com...
> Hi all,
> I have a sql account in a shared hosting server and my account is able
> to execute scripts to create [dbo].* tables and [dbo].* store
> procedures.
> however, after they are created, if I tried to execute them, I get for
> example,
> permission denied on object 'GetUserDataByEmail', database 'mypm',
> owner 'dbo'.
> so after googling around, I found out I had to do
> GRANT EXECUTE ON [dbo].[GetUserDataByEmail] TO 'myuser'
> then i could execute it.
> so i have these questions
> 1. I always thought in order to be able to create [dbo].* tables, you
> need to a dbo?
> 2. If I'm right with #1, then how come my user can't excute
> [dbo].[GetUserDataByEmail], instead I had to explicilty grant exec
ute
> access to it?
> 3. I see in another person's code, it had "GRANT EXECUTE on
> dbo.GetUserDataByEmail to public, what does public mean? anybody can
> execute it? if so, then what's the point of setting [dbo] to it?
> Really appreicate your 2 cents on this. Thanks
>

Newbie question on dbo permission

Hi all,
I have a sql account in a shared hosting server and my account is able
to execute scripts to create [dbo].* tables and [dbo].* store
procedures.
however, after they are created, if I tried to execute them, I get for
example,
permission denied on object 'GetUserDataByEmail', database 'mypm',
owner 'dbo'.
so after googling around, I found out I had to do
GRANT EXECUTE ON [dbo].[GetUserDataByEmail] TO 'myuser'
then i could execute it.
so i have these questions
1. I always thought in order to be able to create [dbo].* tables, you
need to a dbo?
2. If I'm right with #1, then how come my user can't excute
[dbo].[GetUserDataByEmail], instead I had to explicilty grant execute
access to it?
3. I see in another person's code, it had "GRANT EXECUTE on
dbo.GetUserDataByEmail to public, what does public mean? anybody can
execute it? if so, then what's the point of setting [dbo] to it?
Really appreicate your 2 cents on this. Thanks"Liming" <lmxudotnet@.gmail.com> wrote in message
news:1168466707.519839.206840@.77g2000hsv.googlegroups.com...
> Hi all,
> I have a sql account in a shared hosting server and my account is able
> to execute scripts to create [dbo].* tables and [dbo].* store
> procedures.
> however, after they are created, if I tried to execute them, I get for
> example,
> permission denied on object 'GetUserDataByEmail', database 'mypm',
> owner 'dbo'.
> so after googling around, I found out I had to do
> GRANT EXECUTE ON [dbo].[GetUserDataByEmail] TO 'myuser'
> then i could execute it.
> so i have these questions
> 1. I always thought in order to be able to create [dbo].* tables, you
> need to a dbo?
> 2. If I'm right with #1, then how come my user can't excute
> [dbo].[GetUserDataByEmail], instead I had to explicilty grant execute
> access to it?
> 3. I see in another person's code, it had "GRANT EXECUTE on
> dbo.GetUserDataByEmail to public, what does public mean? anybody can
> execute it? if so, then what's the point of setting [dbo] to it?
> Really appreicate your 2 cents on this. Thanks
>
Is this SQL 2000 or 2005?
David|||Hi
> 1. I always thought in order to be able to create [dbo].* tables, you
> need to a dbo?
You will have to be a member of sysadmin server role or db_owner database
role.
DBO is just a privileged user.
> 2. If I'm right with #1, then how come my user can't excute
> [dbo].[GetUserDataByEmail], instead I had to explicilty grant execute
> access to it?
If you memeber of syadmin server role or db_owner database role you don't
need EXECUTE permission
> 3. I see in another person's code, it had "GRANT EXECUTE on
> dbo.GetUserDataByEmail to public, what does public mean? anybody can
> execute it? if so, then what's the point of setting [dbo] to it?
Public means that every user can execute the sp , do you really want it
:-)))?
"Liming" <lmxudotnet@.gmail.com> wrote in message
news:1168466707.519839.206840@.77g2000hsv.googlegroups.com...
> Hi all,
> I have a sql account in a shared hosting server and my account is able
> to execute scripts to create [dbo].* tables and [dbo].* store
> procedures.
> however, after they are created, if I tried to execute them, I get for
> example,
> permission denied on object 'GetUserDataByEmail', database 'mypm',
> owner 'dbo'.
> so after googling around, I found out I had to do
> GRANT EXECUTE ON [dbo].[GetUserDataByEmail] TO 'myuser'
> then i could execute it.
> so i have these questions
> 1. I always thought in order to be able to create [dbo].* tables, you
> need to a dbo?
> 2. If I'm right with #1, then how come my user can't excute
> [dbo].[GetUserDataByEmail], instead I had to explicilty grant execute
> access to it?
> 3. I see in another person's code, it had "GRANT EXECUTE on
> dbo.GetUserDataByEmail to public, what does public mean? anybody can
> execute it? if so, then what's the point of setting [dbo] to it?
> Really appreicate your 2 cents on this. Thanks
>

Newbie question Local Temp Tables vs Global Temp Tables

I'm trying to find the distinction between Local temp tables vs. Global Temp
Tables.
Seem that Global temp tables have greater persistence when using a stored
procedure with a returning select statement.
Question: Are any of the #tempLocalTable or ##tempGlobalTable accessible
from other network users? i.e. can user using the same stored procedures at
the same time overwrite either of these tables?
Thanks for the answers.
Stephen K. MiyasatoLocal temp tables are private to the connection created the tamp table. Glob
al temp tables are not,
they are ... global.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Stephen K. Miyasato" <miyasat@.flex.com> wrote in message
news:u78Xuj2TGHA.1576@.tk2msftngp13.phx.gbl...
> I'm trying to find the distinction between Local temp tables vs. Global Te
mp Tables.
> Seem that Global temp tables have greater persistence when using a stored
procedure with a
> returning select statement.
> Question: Are any of the #tempLocalTable or ##tempGlobalTable accessible f
rom other network users?
> i.e. can user using the same stored procedures at the same time overwrite
either of these tables?
> Thanks for the answers.
> Stephen K. Miyasato
>|||Yes, global temp tables have greater persistence, but I've never found an
actual need to retain a temporary table beyond the context of the procedure
that created it. Have you?
"Stephen K. Miyasato" <miyasat@.flex.com> wrote in message
news:u78Xuj2TGHA.1576@.tk2msftngp13.phx.gbl...
> I'm trying to find the distinction between Local temp tables vs. Global
> Temp Tables.
> Seem that Global temp tables have greater persistence when using a stored
> procedure with a returning select statement.
> Question: Are any of the #tempLocalTable or ##tempGlobalTable accessible
> from other network users? i.e. can user using the same stored procedures
> at the same time overwrite either of these tables?
> Thanks for the answers.
> Stephen K. Miyasato
>|||I guess when I did do a stored procedure, I found that the #tempLocalTable
was not available, so I thought using he global tables would have been a
solution. Perhaps I'm doing it wrong. I ended up using regular tables but
when the stored procedure were used on different stations simultaneously, I
would get results not related to the patient.
Thanks,
Stephen K. Miyasato
"JT" <someone@.microsoft.com> wrote in message
news:ugUTNx3TGHA.1868@.TK2MSFTNGP09.phx.gbl...
> Yes, global temp tables have greater persistence, but I've never found an
> actual need to retain a temporary table beyond the context of the
> procedure that created it. Have you?
> "Stephen K. Miyasato" <miyasat@.flex.com> wrote in message
> news:u78Xuj2TGHA.1576@.tk2msftngp13.phx.gbl...
>|||Global and permanent tables are visible to all connections so you need to
account for multi-user environments when using these for transitory data.
It's best to stick with a local temp table or table variable in those cases.
Hope this helps.
Dan Guzman
SQL Server MVP
"Stephen K. Miyasato" <miyasat@.flex.com> wrote in message
news:OiWhVq4TGHA.1160@.TK2MSFTNGP09.phx.gbl...
>I guess when I did do a stored procedure, I found that the #tempLocalTable
>was not available, so I thought using he global tables would have been a
>solution. Perhaps I'm doing it wrong. I ended up using regular tables but
>when the stored procedure were used on different stations simultaneously, I
>would get results not related to the patient.
> Thanks,
> Stephen K. Miyasato
> "JT" <someone@.microsoft.com> wrote in message
> news:ugUTNx3TGHA.1868@.TK2MSFTNGP09.phx.gbl...
>|||>> BUT, sometimes the CTE doesn't behave as expected, and it does the same l
ookup several times. <<
That is T-SQL; the DB2 implementation is much better and seems to know
when to materalize and when expand a CTE in line.
My real gripe is that T-SQL keeps only one execution plan for a
procedure. Other products keep several plans, look at the parameter
values and pick the plan that is best for that set of values. Thus, if
sex is one parameter for a query againt a Marine personnel data base,
and I pass in "male", I get the tabel scan plan, but if I pass in
"female' I get a plan with an index. I vaguely remember that DB2 can
have 16 plans per proc, but I might be wrong.

newbie question describing tables

How can I print the structure of a table from Enterprise Manager?
Or is there another app that has a print option?"MacKenzieMouse" <no-spamkeyconc@.yahoo.com> wrote in message
news:JrmdnX6w396rZ2XcRVn-1w@.comcast.com...
> How can I print the structure of a table from Enterprise Manager?
> Or is there another app that has a print option?

You can create and print database diagrams in EM (right-click Diagrams, New
Diagram), or if you use a third-party data modelling tool (Erwin,
Embarcadero etc.) then it would be able to do the same thing. If the CREATE
TABLE script is good enough, then you can generate it in Query Analyzer
(right-click a table in the Object Browser), then print the resulting
script.

Simon|||To print the structure of a table from Query Analyzer just print the
ouput of EXEC sp_help 'table_name' or a query from the
information_schema.columns table.
--
David Portas
SQL Server MVP
--|||David Portas wrote:
> To print the structure of a table from Query Analyzer just print the
> ouput of EXEC sp_help 'table_name' or a query from the
> information_schema.columns table.
> --
> David Portas
> SQL Server MVP
> --

How do I print it ?
Is there any way to do that using the Query Analayzer ?|||Select the Text ouput option (CTRL+T), run the query, select the
Results tab then File/Print or CTRL+P.
--
David Portas
SQL Server MVP
--|||Works great! Thanks !|||MacKenzieMouse wrote:
> How can I print the structure of a table from Enterprise Manager?
> Or is there another app that has a print option?
Thanks to all that helped!sql

Newbie question about Object transfer

Hi,

I'm new to Integration Services and I have a problem when I try to make a simple transfer of tables and views between 2 DB.

The idea is to copy tables and views from DB1 to DB2.

If the object exists in the target DB, it must be dropped, then created and the data must be copied.

The problem occurs when a new object is created in the source DB and thus does not exist in the target DB. This throws an error because the system cannot drop an object wich does not exist.

What is the workaround ?

The idea is to execute this package every hour to have a cached DB. In this case, replication is not a solution.

Thanks in advance,

Patrice.

This issue is a bug in SQL Server 2005 RTM. Hopefully it will be fixed in one of the SP releases in the near future.

newbie question :Truncate Table side effect

We have 23 tables in the database, for each table we have a auditlog table
having similar columns and few others like date created and date updated and
AuditLogId.
When the application runs for the first time we are allocating 300MB for MDF
file and 99MB for LDF file.
Audit Logs are eating up all the space, so when we get Primary File Group
Full error, we used BCP command to transfer the content to text files and
then run the truncate table command on each AuditTable.
We are noticing a unusual behaviour after running the BCP followed by
truncate table command. The database free space suddenly is being used up at
less slower pace. We are not having any data loss or audit table data loss.
For example, before running the BCP+truncate table, we can import 30
libraries in our application, but after running the BCP+truncate table we can
import way too many around 90 libraries.
I am not able to solve this mystery because I am not familiar with SQL
Server internals how it behaves. Any help will be greatly appreciated.
Hi
Check the following:
That if you databse is set to "Full Recovery" mode, backup your transaction
log on a regular basis.
You can set the data and logs to grow automatically so that you do not run
out of space.
Regards
Mike
"Help_Me_Please" wrote:

> We have 23 tables in the database, for each table we have a auditlog table
> having similar columns and few others like date created and date updated and
> AuditLogId.
> When the application runs for the first time we are allocating 300MB for MDF
> file and 99MB for LDF file.
> Audit Logs are eating up all the space, so when we get Primary File Group
> Full error, we used BCP command to transfer the content to text files and
> then run the truncate table command on each AuditTable.
> We are noticing a unusual behaviour after running the BCP followed by
> truncate table command. The database free space suddenly is being used up at
> less slower pace. We are not having any data loss or audit table data loss.
> For example, before running the BCP+truncate table, we can import 30
> libraries in our application, but after running the BCP+truncate table we can
> import way too many around 90 libraries.
> I am not able to solve this mystery because I am not familiar with SQL
> Server internals how it behaves. Any help will be greatly appreciated.
|||If you are talking about the database free space in enterprise manager then
that is data and log so it would probably be the logs that are causing the
problem.
If the database is in full recovery mode then transaction logs will fill up
until truncated or baced up. This does not happen until after the first
backup though (log is automatically truncated until then as the backups are
useless without a full backup). Maybe your truncate is causing the same
effect and you would start using more space after the next full backup.
Have a look at
http://www.nigelrivett.net/Transacti...leGrows_1.html
If it's to do with just data then it's probably fragmentation or the way you
are checking the file space.
30 - 90 sounds a lot though.
How is the audit trail taken? Do you have something that depends on the
previous data?
|||The tables I am truncating do not have any foreign key constraints hence
truncation did not have any problem.
After the BCP + truncation, I tried to run the automated test to fill up the
database, the behaviour I noticed is the LDF file is still 101MB, while the
MDF file is 1.2 GB. i.e., now LDF file is not growing as fast. Does that mean
the transaction logs are not being written?
How can I check which recovery mode is it in. I want to check before we run
into the problem and after we run the truncate cmd.
Thank you so much for the explaination.
Regards.
"Nigel Rivett" wrote:

> If you are talking about the database free space in enterprise manager then
> that is data and log so it would probably be the logs that are causing the
> problem.
> If the database is in full recovery mode then transaction logs will fill up
> until truncated or baced up. This does not happen until after the first
> backup though (log is automatically truncated until then as the backups are
> useless without a full backup). Maybe your truncate is causing the same
> effect and you would start using more space after the next full backup.
> Have a look at
> http://www.nigelrivett.net/Transacti...leGrows_1.html
> If it's to do with just data then it's probably fragmentation or the way you
> are checking the file space.
> 30 - 90 sounds a lot though.
> How is the audit trail taken? Do you have something that depends on the
> previous data?
|||> After the BCP + truncation, I tried to run the automated test to fill up the
> database, the behaviour I noticed is the LDF file is still 101MB, while the
> MDF file is 1.2 GB. i.e., now LDF file is not growing as fast. Does that mean
> the transaction logs are not being written?
No. It just means that the LDF file were large enough to hold the log records produced by your
modifications.

> How can I check which recovery mode is it in.
sp_helpdb
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Help_Me_Please" <HelpMePlease@.discussions.microsoft.com> wrote in message
news:90ADC3D5-3E79-4D6F-B0A7-9A754F876A09@.microsoft.com...[vbcol=seagreen]
> The tables I am truncating do not have any foreign key constraints hence
> truncation did not have any problem.
> After the BCP + truncation, I tried to run the automated test to fill up the
> database, the behaviour I noticed is the LDF file is still 101MB, while the
> MDF file is 1.2 GB. i.e., now LDF file is not growing as fast. Does that mean
> the transaction logs are not being written?
> How can I check which recovery mode is it in. I want to check before we run
> into the problem and after we run the truncate cmd.
> Thank you so much for the explaination.
> Regards.
> "Nigel Rivett" wrote:

newbie question :Truncate Table side effect

We have 23 tables in the database, for each table we have a auditlog table
having similar columns and few others like date created and date updated and
AuditLogId.
When the application runs for the first time we are allocating 300MB for MDF
file and 99MB for LDF file.
Audit Logs are eating up all the space, so when we get Primary File Group
Full error, we used BCP command to transfer the content to text files and
then run the truncate table command on each AuditTable.
We are noticing a unusual behaviour after running the BCP followed by
truncate table command. The database free space suddenly is being used up at
less slower pace. We are not having any data loss or audit table data loss.
For example, before running the BCP+truncate table, we can import 30
libraries in our application, but after running the BCP+truncate table we can
import way too many around 90 libraries.
I am not able to solve this mystery because I am not familiar with SQL
Server internals how it behaves. Any help will be greatly appreciated.Hi
Check the following:
That if you databse is set to "Full Recovery" mode, backup your transaction
log on a regular basis.
You can set the data and logs to grow automatically so that you do not run
out of space.
Regards
Mike
"Help_Me_Please" wrote:
> We have 23 tables in the database, for each table we have a auditlog table
> having similar columns and few others like date created and date updated and
> AuditLogId.
> When the application runs for the first time we are allocating 300MB for MDF
> file and 99MB for LDF file.
> Audit Logs are eating up all the space, so when we get Primary File Group
> Full error, we used BCP command to transfer the content to text files and
> then run the truncate table command on each AuditTable.
> We are noticing a unusual behaviour after running the BCP followed by
> truncate table command. The database free space suddenly is being used up at
> less slower pace. We are not having any data loss or audit table data loss.
> For example, before running the BCP+truncate table, we can import 30
> libraries in our application, but after running the BCP+truncate table we can
> import way too many around 90 libraries.
> I am not able to solve this mystery because I am not familiar with SQL
> Server internals how it behaves. Any help will be greatly appreciated.|||If you are talking about the database free space in enterprise manager then
that is data and log so it would probably be the logs that are causing the
problem.
If the database is in full recovery mode then transaction logs will fill up
until truncated or baced up. This does not happen until after the first
backup though (log is automatically truncated until then as the backups are
useless without a full backup). Maybe your truncate is causing the same
effect and you would start using more space after the next full backup.
Have a look at
http://www.nigelrivett.net/TransactionLogFileGrows_1.html
If it's to do with just data then it's probably fragmentation or the way you
are checking the file space.
30 - 90 sounds a lot though.
How is the audit trail taken? Do you have something that depends on the
previous data?|||The tables I am truncating do not have any foreign key constraints hence
truncation did not have any problem.
After the BCP + truncation, I tried to run the automated test to fill up the
database, the behaviour I noticed is the LDF file is still 101MB, while the
MDF file is 1.2 GB. i.e., now LDF file is not growing as fast. Does that mean
the transaction logs are not being written?
How can I check which recovery mode is it in. I want to check before we run
into the problem and after we run the truncate cmd.
Thank you so much for the explaination.
Regards.
"Nigel Rivett" wrote:
> If you are talking about the database free space in enterprise manager then
> that is data and log so it would probably be the logs that are causing the
> problem.
> If the database is in full recovery mode then transaction logs will fill up
> until truncated or baced up. This does not happen until after the first
> backup though (log is automatically truncated until then as the backups are
> useless without a full backup). Maybe your truncate is causing the same
> effect and you would start using more space after the next full backup.
> Have a look at
> http://www.nigelrivett.net/TransactionLogFileGrows_1.html
> If it's to do with just data then it's probably fragmentation or the way you
> are checking the file space.
> 30 - 90 sounds a lot though.
> How is the audit trail taken? Do you have something that depends on the
> previous data?|||> After the BCP + truncation, I tried to run the automated test to fill up the
> database, the behaviour I noticed is the LDF file is still 101MB, while the
> MDF file is 1.2 GB. i.e., now LDF file is not growing as fast. Does that mean
> the transaction logs are not being written?
No. It just means that the LDF file were large enough to hold the log records produced by your
modifications.
> How can I check which recovery mode is it in.
sp_helpdb
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Help_Me_Please" <HelpMePlease@.discussions.microsoft.com> wrote in message
news:90ADC3D5-3E79-4D6F-B0A7-9A754F876A09@.microsoft.com...
> The tables I am truncating do not have any foreign key constraints hence
> truncation did not have any problem.
> After the BCP + truncation, I tried to run the automated test to fill up the
> database, the behaviour I noticed is the LDF file is still 101MB, while the
> MDF file is 1.2 GB. i.e., now LDF file is not growing as fast. Does that mean
> the transaction logs are not being written?
> How can I check which recovery mode is it in. I want to check before we run
> into the problem and after we run the truncate cmd.
> Thank you so much for the explaination.
> Regards.
> "Nigel Rivett" wrote:
> > If you are talking about the database free space in enterprise manager then
> > that is data and log so it would probably be the logs that are causing the
> > problem.
> > If the database is in full recovery mode then transaction logs will fill up
> > until truncated or baced up. This does not happen until after the first
> > backup though (log is automatically truncated until then as the backups are
> > useless without a full backup). Maybe your truncate is causing the same
> > effect and you would start using more space after the next full backup.
> >
> > Have a look at
> > http://www.nigelrivett.net/TransactionLogFileGrows_1.html
> >
> > If it's to do with just data then it's probably fragmentation or the way you
> > are checking the file space.
> > 30 - 90 sounds a lot though.
> > How is the audit trail taken? Do you have something that depends on the
> > previous data?

Newbie Question

How can you create a relationship between two tables in two different databases? Or can you?
Thanks... Sorry if it is a simple question..You can't create cross database RI...

You'll need to use triggers...

Newbie question

Hi All,
Is it possible to store tables (in general database objects) in SQL clients and access it from the SQL server?
Thanks
Eva
What are you trying to accomplish?
"Eva" <anonymous@.discussions.microsoft.com> wrote in message
news:2C3CCA52-4F57-409A-A331-4BFC9001F773@.microsoft.com...
> Hi All,
> Is it possible to store tables (in general database objects) in SQL
clients and access it from the SQL server?
> Thanks
>
|||I am designing a system in which there are about 50 computers containing region-wise data in tables. The tables in different regions have same structure but the data is region-specific. I need to transfer data from the tables in each region to a server. T
he problem is that I cannot have 50 SQL servers installations. I am looking for a work around. Can you suggest what should be done?
Thanks.
|||Eva
REPLICATION is what you need.
http://www.swynk.com/friends/achigrik/SetupMR.asp -- Setting Up Merge
Replication: A Step by Step Guide
http://www.mssqlcity.com/Articles/Replic/Replic.htm --Setting All
Replica (Step by step)
"Eva" <anonymous@.discussions.microsoft.com> wrote in message
news:F851497F-8B3B-4A3C-9482-97B5F0B75410@.microsoft.com...
> I am designing a system in which there are about 50 computers containing
region-wise data in tables. The tables in different regions have same
structure but the data is region-specific. I need to transfer data from the
tables in each region to a server. The problem is that I cannot have 50 SQL
servers installations. I am looking for a work around. Can you suggest what
should be done?
> Thanks.
|||Thanks Uri.
Yes, I could have the 50 computers set up as publishers and the server as a subscriber. But, what I understand of Publishers is that they have to be SQL servers!
|||Eva
How do they store the data? With text files?
Do they have an application (vb,c) which can connect to the main server?
"Eva" <anonymous@.discussions.microsoft.com> wrote in message
news:A89D4478-7858-493C-BD95-607A710004B3@.microsoft.com...
> Thanks Uri.
> Yes, I could have the 50 computers set up as publishers and the server as
a subscriber. But, what I understand of Publishers is that they have to be
SQL servers!
|||Data is stored in SQL tables. There is an application running on the server which accesses the tables in each of the 50 machines and fetches data from them!
|||Eva
Have you looked at DTS utility?
"Eva" <anonymous@.discussions.microsoft.com> wrote in message
news:4F67F94C-FFC2-4CB9-8ADB-2C66E427EA8C@.microsoft.com...
> Data is stored in SQL tables. There is an application running on the
server which accesses the tables in each of the 50 machines and fetches data
from them!
|||Merge replication would seem to fit the bill here. In merge the publisher/subscriber metaphore doesn't really hold, so in your case you'd have a central publisher and 50 subscribers. The subscribers don't need to be sql server - they could heterogenous eg
Access. I'm not sure from your post if you have the option of setting up the 50 subscriber's repositories. If so, then you could use MSDE.
Regards,
Paul Ibison
|||Eva,
When you say "SQL Tables" do you mean tables in a SQL Server database? Or is
it in another RDBMS?
Andrew C. Madsen
Information Architect
Harley-Davidson Motor Company
"Eva" <anonymous@.discussions.microsoft.com> wrote in message
news:4F67F94C-FFC2-4CB9-8ADB-2C66E427EA8C@.microsoft.com...
> Data is stored in SQL tables. There is an application running on the
server which accesses the tables in each of the 50 machines and fetches data
from them!

Newbie question

Hi All,
Is it possible to store tables (in general database objects) in SQL clients
and access it from the SQL server?
ThanksEva
What are you trying to accomplish?
"Eva" <anonymous@.discussions.microsoft.com> wrote in message
news:2C3CCA52-4F57-409A-A331-4BFC9001F773@.microsoft.com...
> Hi All,
> Is it possible to store tables (in general database objects) in SQL
clients and access it from the SQL server?
> Thanks
>|||I am designing a system in which there are about 50 computers containing reg
ion-wise data in tables. The tables in different regions have same structure
but the data is region-specific. I need to transfer data from the tables in
each region to a server. T
he problem is that I cannot have 50 SQL servers installations. I am looking
for a work around. Can you suggest what should be done?
Thanks.|||Eva
REPLICATION is what you need.
http://www.swynk.com/friends/achigrik/SetupMR.asp -- Setting Up Merge
Replication: A Step by Step Guide
http://www.mssqlcity.com/Articles/Replic/Replic.htm --Setting All
Replica (Step by step)
"Eva" <anonymous@.discussions.microsoft.com> wrote in message
news:F851497F-8B3B-4A3C-9482-97B5F0B75410@.microsoft.com...
> I am designing a system in which there are about 50 computers containing
region-wise data in tables. The tables in different regions have same
structure but the data is region-specific. I need to transfer data from the
tables in each region to a server. The problem is that I cannot have 50 SQL
servers installations. I am looking for a work around. Can you suggest what
should be done?
> Thanks.|||Thanks Uri.
Yes, I could have the 50 computers set up as publishers and the server as a
subscriber. But, what I understand of Publishers is that they have to be SQL
servers!|||Eva
How do they store the data? With text files?
Do they have an application (vb,c) which can connect to the main server?
"Eva" <anonymous@.discussions.microsoft.com> wrote in message
news:A89D4478-7858-493C-BD95-607A710004B3@.microsoft.com...
> Thanks Uri.
> Yes, I could have the 50 computers set up as publishers and the server as
a subscriber. But, what I understand of Publishers is that they have to be
SQL servers!|||Data is stored in SQL tables. There is an application running on the server
which accesses the tables in each of the 50 machines and fetches data from t
hem!|||Eva
Have you looked at DTS utility?
"Eva" <anonymous@.discussions.microsoft.com> wrote in message
news:4F67F94C-FFC2-4CB9-8ADB-2C66E427EA8C@.microsoft.com...
> Data is stored in SQL tables. There is an application running on the
server which accesses the tables in each of the 50 machines and fetches data
from them!|||Merge replication would seem to fit the bill here. In merge the publisher/su
bscriber metaphore doesn't really hold, so in your case you'd have a central
publisher and 50 subscribers. The subscribers don't need to be sql server -
they could heterogenous eg
Access. I'm not sure from your post if you have the option of setting up the
50 subscriber's repositories. If so, then you could use MSDE.
Regards,
Paul Ibison|||Eva,
When you say "SQL Tables" do you mean tables in a SQL Server database? Or is
it in another RDBMS?
Andrew C. Madsen
Information Architect
Harley-Davidson Motor Company
"Eva" <anonymous@.discussions.microsoft.com> wrote in message
news:4F67F94C-FFC2-4CB9-8ADB-2C66E427EA8C@.microsoft.com...
> Data is stored in SQL tables. There is an application running on the
server which accesses the tables in each of the 50 machines and fetches data
from them!

Newbie question

I noticed your reply to Alison - having the same problem. How would I enter
the code you gave
select
table_schema
from
information_schema.tables
where
table_name = 'ascis_acadyear_setup'
into visual studio?I don't really use Visual Studio that much. Use Query Analyzer (SQL 2000)
or SQL Server Management Studio (SQL 2005).
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Joe" <,> wrote in message
news:4401a01f$0$6979$ed2619ec@.ptn-nntp-reader02.plus.net...
I noticed your reply to Alison - having the same problem. How would I enter
the code you gave
select
table_schema
from
information_schema.tables
where
table_name = 'ascis_acadyear_setup'
into visual studio?

Newbie question

Hi All
Is it possible to store tables (in general database objects) in SQL clients and access it from the SQL server
ThankEva
What are you trying to accomplish?
"Eva" <anonymous@.discussions.microsoft.com> wrote in message
news:2C3CCA52-4F57-409A-A331-4BFC9001F773@.microsoft.com...
> Hi All,
> Is it possible to store tables (in general database objects) in SQL
clients and access it from the SQL server?
> Thanks
>|||I am designing a system in which there are about 50 computers containing region-wise data in tables. The tables in different regions have same structure but the data is region-specific. I need to transfer data from the tables in each region to a server. The problem is that I cannot have 50 SQL servers installations. I am looking for a work around. Can you suggest what should be done
Thanks.|||Eva
REPLICATION is what you need.
http://www.swynk.com/friends/achigrik/SetupMR.asp -- Setting Up Merge
Replication: A Step by Step Guide
http://www.mssqlcity.com/Articles/Replic/Replic.htm --Setting All
Replica (Step by step)
"Eva" <anonymous@.discussions.microsoft.com> wrote in message
news:F851497F-8B3B-4A3C-9482-97B5F0B75410@.microsoft.com...
> I am designing a system in which there are about 50 computers containing
region-wise data in tables. The tables in different regions have same
structure but the data is region-specific. I need to transfer data from the
tables in each region to a server. The problem is that I cannot have 50 SQL
servers installations. I am looking for a work around. Can you suggest what
should be done?
> Thanks.|||Thanks Uri
Yes, I could have the 50 computers set up as publishers and the server as a subscriber. But, what I understand of Publishers is that they have to be SQL servers!|||Eva
How do they store the data? With text files?
Do they have an application (vb,c) which can connect to the main server?
"Eva" <anonymous@.discussions.microsoft.com> wrote in message
news:A89D4478-7858-493C-BD95-607A710004B3@.microsoft.com...
> Thanks Uri.
> Yes, I could have the 50 computers set up as publishers and the server as
a subscriber. But, what I understand of Publishers is that they have to be
SQL servers!|||Data is stored in SQL tables. There is an application running on the server which accesses the tables in each of the 50 machines and fetches data from them!|||Eva
Have you looked at DTS utility?
"Eva" <anonymous@.discussions.microsoft.com> wrote in message
news:4F67F94C-FFC2-4CB9-8ADB-2C66E427EA8C@.microsoft.com...
> Data is stored in SQL tables. There is an application running on the
server which accesses the tables in each of the 50 machines and fetches data
from them!|||Eva,
When you say "SQL Tables" do you mean tables in a SQL Server database? Or is
it in another RDBMS?
--
Andrew C. Madsen
Information Architect
Harley-Davidson Motor Company
"Eva" <anonymous@.discussions.microsoft.com> wrote in message
news:4F67F94C-FFC2-4CB9-8ADB-2C66E427EA8C@.microsoft.com...
> Data is stored in SQL tables. There is an application running on the
server which accesses the tables in each of the 50 machines and fetches data
from them!|||Can't you have just one SQL Server and have all clients store their data in this SQL Server?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Eva" <anonymous@.discussions.microsoft.com> wrote in message
news:F851497F-8B3B-4A3C-9482-97B5F0B75410@.microsoft.com...
> I am designing a system in which there are about 50 computers containing region-wise data in tables. The
tables in different regions have same structure but the data is region-specific. I need to transfer data from
the tables in each region to a server. The problem is that I cannot have 50 SQL servers installations. I am
looking for a work around. Can you suggest what should be done?
> Thanks.

Newbie Query Question

I have three tables I'm trying to query.
Table 1 - Employees
Table 2 - Cell Phones
Table 3 - Pagers
What I'm trying to do is query ALL of the employees in Table 1 and show
either their pager number or cell phone number or still list them even if
both those fields are Null.
Can someone help point me in the right direction. Everytime I try to run my
own query I just get the employees that have both a cell and a pager.
Any information would be greatly appreciated.
Thanks, Correyselect * from Employees E
left outer join CellPhones C on
C.EmployeeID = E.EmployeeID
left outer join Pagers P on
E.EmployeeID = P.EmployeeID
Alain Quesnel
alainsansspam@.logiquel.com
www.logiquel.com
"Correy" <cgrist@.comcast.net> wrote in message
news:euChehtqIHA.2256@.TK2MSFTNGP05.phx.gbl...
>I have three tables I'm trying to query.
> Table 1 - Employees
> Table 2 - Cell Phones
> Table 3 - Pagers
> What I'm trying to do is query ALL of the employees in Table 1 and show
> either their pager number or cell phone number or still list them even if
> both those fields are Null.
> Can someone help point me in the right direction. Everytime I try to run
> my own query I just get the employees that have both a cell and a pager.
> Any information would be greatly appreciated.
> Thanks, Correy
>
>

newbie pl/sql outputs select results

Hi,
I want to write a PL/SQL search engine that does some complex checking of various tables for a client's website. I could write it all in a PHP $sql = "SELECT ..." but I want to put it the sql into PL/SQL and am having trouble figuring out how PL/SQL outputs results.

Basically, I want to call the procedure with some keywords and have it return the results. Something like this:

create package jonsearch is

procedure getrecords(kw IN varchar, results OUT ?) is
begin
--complex sql goes here
end;

end jonsearch;

jonsearch.getrecords("keywords") would return the results just like select * from table would return results.

My trouble is that every tutorial I have read relies on dbms_output.put_line to output data. I want to output the results as a set, with an output variable, but I can't find a tutorial that shows how to use output variables.

Any help, even pointing me to a tutorial, would be great.

thanks,

JonIt turns out what I was looking for is called a REF CURSOR. I needed to create a package that defines this reference cursor, and then use that as my output variable.

This is described here, if anyone is interested:

http://www.oracle-base.com/Articles/8i/UsingRefCursorsToReturnRecordsets.asp|||Not understanding...

How are you wanting to output the variables? If you want to simply return the values, you will use the DBMS_OUTPUT package. If you are returning to Apache through the modplsql module, you will use the HTP and HTF packages.

As to REF Cursors, here is a Reader's Digest version:

http://www.dbforums.com/t974133.html

JoeB

Newbie on Triggers

System Sql server 2000

Hi all

I've dipped my toe in stored procedures, I'm now having a look at triggers

If I had 2 tables and I wanted to update table 2 with some data from table
1, every time table 1 has a new entry made in it (e.g. for an audit trail) I
thought I would be able to use a trigger on table 1.

for instance

on insert of new value in table 1 execute a trigger

The trigger gets the value of the newly created primary key in table 1 (the
primary key is generated by Autonumber) and inserts a copy of the primary
key into table 2

I thought the following would work

ALTER TRIGGER update_table2
ON dbo.table1
FOR INSERT AS
begin
declare @.new_key as bigint
set @.new_key = dbo.table1(PK_test_primary_key)

INSERT INTO dbo.table2
(FK_from_table1)
VALUES (@.new_key)
end

unfortunatly I cant read the value of the newly entered primary key from
table 1. I get a message saying PK_test_primary_key doesn't exist in
dbo.table1

does anyone know if what I am trying to achieve is possible, and maybe a
clue as a good way to go about doing it?

many thanks

AndyHi just a bit more...

after some more googling I have found @.@.identity and it seems to give the
identity of the last added record in the table calling the trigger

is this correct, and is it safe to do this i.e.

ALTER TRIGGER update_table2
ON dbo.table1
FOR INSERT AS
begin

INSERT INTO dbo.table2
(FK_from_table1)
VALUES (@.@.identity)
end

I've also been playing with objConn.BeginTrans in my vb6 code and I write
lots of records to table 1 before confirming the transaction. I believe that
the autonumbers are only generated on completion of the transaction, so how
many triggers will be executed (one per transaction or 1 per new record) and
at what time (as the records are written or on compltion of the
transaction)... Will it reliably update table 2 with the correct info from
table 1 if say dozens of additions are made to table 1 from different
sources at the same time...?

I think I worry too much and it seems to work when I test it, but I like to
know what happens when 8-)

thanks again

Andy

"aaj" <a.b@.c.com> wrote in message
news:4028f77b$0$29799$afc38c87@.news.easynet.co.uk. ..
> System Sql server 2000
> Hi all
> I've dipped my toe in stored procedures, I'm now having a look at triggers
> If I had 2 tables and I wanted to update table 2 with some data from table
> 1, every time table 1 has a new entry made in it (e.g. for an audit trail)
I
> thought I would be able to use a trigger on table 1.
> for instance
> on insert of new value in table 1 execute a trigger
> The trigger gets the value of the newly created primary key in table 1
(the
> primary key is generated by Autonumber) and inserts a copy of the primary
> key into table 2
> I thought the following would work
> ALTER TRIGGER update_table2
> ON dbo.table1
> FOR INSERT AS
> begin
> declare @.new_key as bigint
> set @.new_key = dbo.table1(PK_test_primary_key)
> INSERT INTO dbo.table2
> (FK_from_table1)
> VALUES (@.new_key)
> end
> unfortunatly I cant read the value of the newly entered primary key from
> table 1. I get a message saying PK_test_primary_key doesn't exist in
> dbo.table1
> does anyone know if what I am trying to achieve is possible, and maybe a
> clue as a good way to go about doing it?
> many thanks
> Andy|||aaj (a.b@.c.com) writes:
> after some more googling I have found @.@.identity and it seems to give the
> identity of the last added record in the table calling the trigger
> is this correct, and is it safe to do this i.e.
> ALTER TRIGGER update_table2
> ON dbo.table1
> FOR INSERT AS
> begin
> INSERT INTO dbo.table2
> (FK_from_table1)
> VALUES (@.@.identity)
> end

No, this is not the way to go. In a trigger you have access to two
virtual tables, inserted and deleted. inserted holds the rows that
were inserted, and in case of an UPDATE statment, the values after
the update. And deleted holds the values that were removed by a DELETE
or an UPDATE statement.

Beware that trigger fires once by statement, so many rows can be affected
at once.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||> ALTER TRIGGER update_table2
> ON dbo.table1
> FOR INSERT AS
> begin
> declare @.new_key as bigint
> set @.new_key = dbo.table1(PK_test_primary_key)
> INSERT INTO dbo.table2
> (FK_from_table1)
> VALUES (@.new_key)
> end
Triggers utilize the virtual "inserted" and "deleted" tables. Check
out this article:http://www.sqlteam.com/item.asp?ItemID=3850.|||You should also not that the @.@.identity is not just the last identity
created for that table, it is the last identity created. That makes the
value of @.@.identity sort of unpredictable. The other guys are right that in
the trigger you should use INSERTED and DELETED tables, but if you need to
access the latest identity created within a given stored procedure you can
use SCOPE_IDENTITY().

"aaj" <a.b@.c.com> wrote in message
news:4028fa81$0$29827$afc38c87@.news.easynet.co.uk. ..
> Hi just a bit more...
> after some more googling I have found @.@.identity and it seems to give the
> identity of the last added record in the table calling the trigger
> is this correct, and is it safe to do this i.e.
> ALTER TRIGGER update_table2
> ON dbo.table1
> FOR INSERT AS
> begin
> INSERT INTO dbo.table2
> (FK_from_table1)
> VALUES (@.@.identity)
> end
>
> I've also been playing with objConn.BeginTrans in my vb6 code and I write
> lots of records to table 1 before confirming the transaction. I believe
that
> the autonumbers are only generated on completion of the transaction, so
how
> many triggers will be executed (one per transaction or 1 per new record)
and
> at what time (as the records are written or on compltion of the
> transaction)... Will it reliably update table 2 with the correct info
from
> table 1 if say dozens of additions are made to table 1 from different
> sources at the same time...?
> I think I worry too much and it seems to work when I test it, but I like
to
> know what happens when 8-)
> thanks again
> Andy
>
> "aaj" <a.b@.c.com> wrote in message
> news:4028f77b$0$29799$afc38c87@.news.easynet.co.uk. ..
> > System Sql server 2000
> > Hi all
> > I've dipped my toe in stored procedures, I'm now having a look at
triggers
> > If I had 2 tables and I wanted to update table 2 with some data from
table
> > 1, every time table 1 has a new entry made in it (e.g. for an audit
trail)
> I
> > thought I would be able to use a trigger on table 1.
> > for instance
> > on insert of new value in table 1 execute a trigger
> > The trigger gets the value of the newly created primary key in table 1
> (the
> > primary key is generated by Autonumber) and inserts a copy of the
primary
> > key into table 2
> > I thought the following would work
> > ALTER TRIGGER update_table2
> > ON dbo.table1
> > FOR INSERT AS
> > begin
> > declare @.new_key as bigint
> > set @.new_key = dbo.table1(PK_test_primary_key)
> > INSERT INTO dbo.table2
> > (FK_from_table1)
> > VALUES (@.new_key)
> > end
> > unfortunatly I cant read the value of the newly entered primary key from
> > table 1. I get a message saying PK_test_primary_key doesn't exist in
> > dbo.table1
> > does anyone know if what I am trying to achieve is possible, and maybe a
> > clue as a good way to go about doing it?
> > many thanks
> > Andy|||Jason Sauer (jason.sauer@.fuse.net) writes:
> You should also not that the @.@.identity is not just the last identity
> created for that table, it is the last identity created. That makes the
> value of @.@.identity sort of unpredictable. The other guys are right that
> in the trigger you should use INSERTED and DELETED tables, but if you
> need to access the latest identity created within a given stored
> procedure you can use SCOPE_IDENTITY().

In this particular case @.@.identity is the only choice. scope_identity()
will return NULL, because there have been no inserts into a table
with an identity column in the current scope, that is the trigger.

The only time @.@.identity will not return the right value, is when there
are more than one trigger on the table, and the other trigger executes
first and that trigger too inserts into a table with a identity column.
Maybe not the most likely scenario.

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

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