newbie question on SP, Databases,instances

I am sql server newbie who is trying to migrating a DB2 system(MF) to
SQLserver.
I have a few questions, it would be great if someone can help me out :
1. When I am porting the app to sqlserver, is it advisable to make the
queries to SP or keep it as a regular SQL ? Does SP perform any better
than SQL ? I have heard that lot of places write only SP, so the
front-end programmers can just call the SP and get the data.
2. I got multiple databases in DB2 (in mainframe ,databases are just
logical), is it a good idea to create different databases in SQLserver
or have it in one big database . Pro and cons ?
Could anybody please help
TIA
Roger
Comments Inline
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Perl rookie" <anytasks@.gmail.com> wrote in message
news:1107809732.055331.57880@.z14g2000cwz.googlegro ups.com...
> I am sql server newbie who is trying to migrating a DB2 system(MF) to
> SQLserver.
> I have a few questions, it would be great if someone can help me out :
> 1. When I am porting the app to sqlserver, is it advisable to make the
> queries to SP or keep it as a regular SQL ? Does SP perform any better
> than SQL ? I have heard that lot of places write only SP, so the
> front-end programmers can just call the SP and get the data.
Generally speaking, it is a good idea to write a stored procedure interface
to the database. Performance is usually better due to procedure plan
caching, plus it gives you a layer of abstraction so you can make changes
without haveing to make the code changes at the same time.

> 2. I got multiple databases in DB2 (in mainframe ,databases are just
> logical), is it a good idea to create different databases in SQLserver
> or have it in one big database . Pro and cons ?
>
I prefer to keep data together in a single database if it needs to be
transactionally synchronized for backup and restore, needs to have
database-level referential integrity constraints, and makes sense to do so.
There are other considerations such as whether there are data logs or work
queues involved that have specific performance and backup needs, but those
are the major reasons.
> Could anybody please help
> TIA
> Roger
>
|||When a query is processed by SQL Server, it requires miliseconds to compile
the execution plan. Whether this is a performance issue depends on the case
usage of the SP. If this SP is called 10,000 times per day, or several times
per second, then yes it can reduce the compile time and is probably more a
scalability issue. However, if this is a SP used for reporting purposes,
occasional data inserts (few times per hour), etc. calling as a SP will not
impact the runtime performance. In other words, a query that takes 2 minutes
to execute from the application will not be reduced to 2 seconds simply
because it has been re-written as a SP. Your time is better spent optimizing
the structure of the query itself.
However, from an architectural perspective, I believe that queries
(especially insert / update / delete queries) should be implemented as SPs.
This shifts business logic an data modification programming to the server
side where it can be better managed by the DBA. The application tier should
be as lightweight as possible and deal strictly with presentation, user
input, workflow, etc.; espcially if we are talking about a web application.
Whether your tables should be placed in one database or multiple databases
would require knowledge about the nature and relationship of the data. That
said, depending on the volume of data (say 10 GBs or larger), it can be
generally beneficial for performance reasons to archive rarely used
historical data to a seperate database.
"Perl rookie" <anytasks@.gmail.com> wrote in message
news:1107809732.055331.57880@.z14g2000cwz.googlegro ups.com...
> I am sql server newbie who is trying to migrating a DB2 system(MF) to
> SQLserver.
> I have a few questions, it would be great if someone can help me out :
> 1. When I am porting the app to sqlserver, is it advisable to make the
> queries to SP or keep it as a regular SQL ? Does SP perform any better
> than SQL ? I have heard that lot of places write only SP, so the
> front-end programmers can just call the SP and get the data.
> 2. I got multiple databases in DB2 (in mainframe ,databases are just
> logical), is it a good idea to create different databases in SQLserver
> or have it in one big database . Pro and cons ?
>
> Could anybody please help
> TIA
> Roger
>
|||Thanks a lot Geoff and Johnny . Appreciate ur reply
sql

newbie question on SP, Databases,instances

I am sql server newbie who is trying to migrating a DB2 system(MF) to
SQLserver.
I have a few questions, it would be great if someone can help me out :
1. When I am porting the app to sqlserver, is it advisable to make the
queries to SP or keep it as a regular SQL ? Does SP perform any better
than SQL ? I have heard that lot of places write only SP, so the
front-end programmers can just call the SP and get the data.
2. I got multiple databases in DB2 (in mainframe ,databases are just
logical), is it a good idea to create different databases in SQLserver
or have it in one big database . Pro and cons ?
Could anybody please help
TIA
RogerComments Inline
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Perl rookie" <anytasks@.gmail.com> wrote in message
news:1107809732.055331.57880@.z14g2000cwz.googlegroups.com...
> I am sql server newbie who is trying to migrating a DB2 system(MF) to
> SQLserver.
> I have a few questions, it would be great if someone can help me out :
> 1. When I am porting the app to sqlserver, is it advisable to make the
> queries to SP or keep it as a regular SQL ? Does SP perform any better
> than SQL ? I have heard that lot of places write only SP, so the
> front-end programmers can just call the SP and get the data.
Generally speaking, it is a good idea to write a stored procedure interface
to the database. Performance is usually better due to procedure plan
caching, plus it gives you a layer of abstraction so you can make changes
without haveing to make the code changes at the same time.

> 2. I got multiple databases in DB2 (in mainframe ,databases are just
> logical), is it a good idea to create different databases in SQLserver
> or have it in one big database . Pro and cons ?
>
I prefer to keep data together in a single database if it needs to be
transactionally synchronized for backup and restore, needs to have
database-level referential integrity constraints, and makes sense to do so.
There are other considerations such as whether there are data logs or work
queues involved that have specific performance and backup needs, but those
are the major reasons.
> Could anybody please help
> TIA
> Roger
>|||When a query is processed by SQL Server, it requires miliseconds to compile
the execution plan. Whether this is a performance issue depends on the case
usage of the SP. If this SP is called 10,000 times per day, or several times
per second, then yes it can reduce the compile time and is probably more a
scalability issue. However, if this is a SP used for reporting purposes,
occasional data inserts (few times per hour), etc. calling as a SP will not
impact the runtime performance. In other words, a query that takes 2 minutes
to execute from the application will not be reduced to 2 seconds simply
because it has been re-written as a SP. Your time is better spent optimizing
the structure of the query itself.
However, from an architectural perspective, I believe that queries
(especially insert / update / delete queries) should be implemented as SPs.
This shifts business logic an data modification programming to the server
side where it can be better managed by the DBA. The application tier should
be as lightweight as possible and deal strictly with presentation, user
input, workflow, etc.; espcially if we are talking about a web application.
Whether your tables should be placed in one database or multiple databases
would require knowledge about the nature and relationship of the data. That
said, depending on the volume of data (say 10 GBs or larger), it can be
generally beneficial for performance reasons to archive rarely used
historical data to a seperate database.
"Perl rookie" <anytasks@.gmail.com> wrote in message
news:1107809732.055331.57880@.z14g2000cwz.googlegroups.com...
> I am sql server newbie who is trying to migrating a DB2 system(MF) to
> SQLserver.
> I have a few questions, it would be great if someone can help me out :
> 1. When I am porting the app to sqlserver, is it advisable to make the
> queries to SP or keep it as a regular SQL ? Does SP perform any better
> than SQL ? I have heard that lot of places write only SP, so the
> front-end programmers can just call the SP and get the data.
> 2. I got multiple databases in DB2 (in mainframe ,databases are just
> logical), is it a good idea to create different databases in SQLserver
> or have it in one big database . Pro and cons ?
>
> Could anybody please help
> TIA
> Roger
>|||Thanks a lot Geoff and Johnny . Appreciate ur reply

newbie question on SP, Databases,instances

I am sql server newbie who is trying to migrating a DB2 system(MF) to
SQLserver.
I have a few questions, it would be great if someone can help me out :
1. When I am porting the app to sqlserver, is it advisable to make the
queries to SP or keep it as a regular SQL ? Does SP perform any better
than SQL ? I have heard that lot of places write only SP, so the
front-end programmers can just call the SP and get the data.
2. I got multiple databases in DB2 (in mainframe ,databases are just
logical), is it a good idea to create different databases in SQLserver
or have it in one big database . Pro and cons ?
Could anybody please help
TIA
RogerComments Inline
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Perl rookie" <anytasks@.gmail.com> wrote in message
news:1107809732.055331.57880@.z14g2000cwz.googlegroups.com...
> I am sql server newbie who is trying to migrating a DB2 system(MF) to
> SQLserver.
> I have a few questions, it would be great if someone can help me out :
> 1. When I am porting the app to sqlserver, is it advisable to make the
> queries to SP or keep it as a regular SQL ? Does SP perform any better
> than SQL ? I have heard that lot of places write only SP, so the
> front-end programmers can just call the SP and get the data.
Generally speaking, it is a good idea to write a stored procedure interface
to the database. Performance is usually better due to procedure plan
caching, plus it gives you a layer of abstraction so you can make changes
without haveing to make the code changes at the same time.

> 2. I got multiple databases in DB2 (in mainframe ,databases are just
> logical), is it a good idea to create different databases in SQLserver
> or have it in one big database . Pro and cons ?
>
I prefer to keep data together in a single database if it needs to be
transactionally synchronized for backup and restore, needs to have
database-level referential integrity constraints, and makes sense to do so.
There are other considerations such as whether there are data logs or work
queues involved that have specific performance and backup needs, but those
are the major reasons.
> Could anybody please help
> TIA
> Roger
>|||When a query is processed by SQL Server, it requires miliseconds to compile
the execution plan. Whether this is a performance issue depends on the case
usage of the SP. If this SP is called 10,000 times per day, or several times
per second, then yes it can reduce the compile time and is probably more a
scalability issue. However, if this is a SP used for reporting purposes,
occasional data inserts (few times per hour), etc. calling as a SP will not
impact the runtime performance. In other words, a query that takes 2 minutes
to execute from the application will not be reduced to 2 seconds simply
because it has been re-written as a SP. Your time is better spent optimizing
the structure of the query itself.
However, from an architectural perspective, I believe that queries
(especially insert / update / delete queries) should be implemented as SPs.
This shifts business logic an data modification programming to the server
side where it can be better managed by the DBA. The application tier should
be as lightweight as possible and deal strictly with presentation, user
input, workflow, etc.; espcially if we are talking about a web application.
Whether your tables should be placed in one database or multiple databases
would require knowledge about the nature and relationship of the data. That
said, depending on the volume of data (say 10 GBs or larger), it can be
generally beneficial for performance reasons to archive rarely used
historical data to a seperate database.
"Perl rookie" <anytasks@.gmail.com> wrote in message
news:1107809732.055331.57880@.z14g2000cwz.googlegroups.com...
> I am sql server newbie who is trying to migrating a DB2 system(MF) to
> SQLserver.
> I have a few questions, it would be great if someone can help me out :
> 1. When I am porting the app to sqlserver, is it advisable to make the
> queries to SP or keep it as a regular SQL ? Does SP perform any better
> than SQL ? I have heard that lot of places write only SP, so the
> front-end programmers can just call the SP and get the data.
> 2. I got multiple databases in DB2 (in mainframe ,databases are just
> logical), is it a good idea to create different databases in SQLserver
> or have it in one big database . Pro and cons ?
>
> Could anybody please help
> TIA
> Roger
>|||Thanks a lot Geoff and Johnny . Appreciate ur reply

newbie question on SP, Databases,instances

I am sql server newbie who is trying to migrating a DB2 system(MF) to
SQLserver.
I have a few questions, it would be great if someone can help me out :
1. When I am porting the app to sqlserver, is it advisable to make the
queries to SP or keep it as a regular SQL ? Does SP perform any better
than SQL ? I have heard that lot of places write only SP, so the
front-end programmers can just call the SP and get the data.
2. I got multiple databases in DB2 (in mainframe ,databases are just
logical), is it a good idea to create different databases in SQLserver
or have it in one big database . Pro and cons ?
Could anybody please help
TIA
RogerComments Inline
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Perl rookie" <anytasks@.gmail.com> wrote in message
news:1107809732.055331.57880@.z14g2000cwz.googlegroups.com...
> I am sql server newbie who is trying to migrating a DB2 system(MF) to
> SQLserver.
> I have a few questions, it would be great if someone can help me out :
> 1. When I am porting the app to sqlserver, is it advisable to make the
> queries to SP or keep it as a regular SQL ? Does SP perform any better
> than SQL ? I have heard that lot of places write only SP, so the
> front-end programmers can just call the SP and get the data.
Generally speaking, it is a good idea to write a stored procedure interface
to the database. Performance is usually better due to procedure plan
caching, plus it gives you a layer of abstraction so you can make changes
without haveing to make the code changes at the same time.
> 2. I got multiple databases in DB2 (in mainframe ,databases are just
> logical), is it a good idea to create different databases in SQLserver
> or have it in one big database . Pro and cons ?
>
I prefer to keep data together in a single database if it needs to be
transactionally synchronized for backup and restore, needs to have
database-level referential integrity constraints, and makes sense to do so.
There are other considerations such as whether there are data logs or work
queues involved that have specific performance and backup needs, but those
are the major reasons.
> Could anybody please help
> TIA
> Roger
>|||When a query is processed by SQL Server, it requires miliseconds to compile
the execution plan. Whether this is a performance issue depends on the case
usage of the SP. If this SP is called 10,000 times per day, or several times
per second, then yes it can reduce the compile time and is probably more a
scalability issue. However, if this is a SP used for reporting purposes,
occasional data inserts (few times per hour), etc. calling as a SP will not
impact the runtime performance. In other words, a query that takes 2 minutes
to execute from the application will not be reduced to 2 seconds simply
because it has been re-written as a SP. Your time is better spent optimizing
the structure of the query itself.
However, from an architectural perspective, I believe that queries
(especially insert / update / delete queries) should be implemented as SPs.
This shifts business logic an data modification programming to the server
side where it can be better managed by the DBA. The application tier should
be as lightweight as possible and deal strictly with presentation, user
input, workflow, etc.; espcially if we are talking about a web application.
Whether your tables should be placed in one database or multiple databases
would require knowledge about the nature and relationship of the data. That
said, depending on the volume of data (say 10 GBs or larger), it can be
generally beneficial for performance reasons to archive rarely used
historical data to a seperate database.
"Perl rookie" <anytasks@.gmail.com> wrote in message
news:1107809732.055331.57880@.z14g2000cwz.googlegroups.com...
> I am sql server newbie who is trying to migrating a DB2 system(MF) to
> SQLserver.
> I have a few questions, it would be great if someone can help me out :
> 1. When I am porting the app to sqlserver, is it advisable to make the
> queries to SP or keep it as a regular SQL ? Does SP perform any better
> than SQL ? I have heard that lot of places write only SP, so the
> front-end programmers can just call the SP and get the data.
> 2. I got multiple databases in DB2 (in mainframe ,databases are just
> logical), is it a good idea to create different databases in SQLserver
> or have it in one big database . Pro and cons ?
>
> Could anybody please help
> TIA
> Roger
>|||Thanks a lot Geoff and Johnny . Appreciate ur reply

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 =Today()

I need to pull some reports from an older ERP system in the US via ODBC
I have the report in play, but want to limit the criteria to the items
shipped today
I have a TODAY_FIELD as a calculated Field with the parameter =TODAY()
The Format of the Oracle view is mm/dd/yyyy hh:mm:ss AM and the
TODAY_FIELD
matches that. (ie 8/30/2006 12:00:00 AM)
So I am looking for the criteria for WHERE (REAL_SHIP_DATE >
TODAY()) which will not generate and ORA-00904 error.
Can anyone please help?getdate() is the system date to use. Or am I understanding what you want
wrong?
"VB" <vodkablokey@.gmail.com> wrote in message
news:1156941597.512536.214380@.b28g2000cwb.googlegroups.com...
>I need to pull some reports from an older ERP system in the US via ODBC
> I have the report in play, but want to limit the criteria to the items
> shipped today
> I have a TODAY_FIELD as a calculated Field with the parameter =TODAY()
> The Format of the Oracle view is mm/dd/yyyy hh:mm:ss AM and the
> TODAY_FIELD
> matches that. (ie 8/30/2006 12:00:00 AM)
> So I am looking for the criteria for WHERE (REAL_SHIP_DATE >
> TODAY()) which will not generate and ORA-00904 error.
> Can anyone please help?
>|||Ben Watts wrote:
> getdate() is the system date to use. Or am I understanding what you want
> wrong?
> "VB" <vodkablokey@.gmail.com> wrote in message
> news:1156941597.512536.214380@.b28g2000cwb.googlegroups.com...
> >I need to pull some reports from an older ERP system in the US via ODBC
> >
> > I have the report in play, but want to limit the criteria to the items
> > shipped today
> >
> > I have a TODAY_FIELD as a calculated Field with the parameter =TODAY()
> >
> > The Format of the Oracle view is mm/dd/yyyy hh:mm:ss AM and the
> > TODAY_FIELD
> > matches that. (ie 8/30/2006 12:00:00 AM)
> >
> > So I am looking for the criteria for WHERE (REAL_SHIP_DATE >
> > TODAY()) which will not generate and ORA-00904 error.
> >
> > Can anyone please help?
> >
I am looking to report all shipping transactions (TRANSACTION_CODE ='OESHIP') which have happened since Midnight.
I know = TODAY() will give you 12:00am today, how do I pass this as a
criteria to a ODBC database call?
I had tried =TODAY() but this is rejected, it seems to suggest I need
to use TO_DATE, but I don't know how to do this either.
Thanks for getting back though :)|||Try:
WHERE REAL_SHIP_DATE > CAST( CONVERT( VARCHAR(8), GETDATE(), 112) AS DATETIME)
This takes the the curent system time, as suggested by Ben, chops of the
time and returns a DateTime value at the very start of the day, similar to
the vb Today() function.
HTH,
Magendo_man
"VB" wrote:
> Ben Watts wrote:
> > getdate() is the system date to use. Or am I understanding what you want
> > wrong?
> > "VB" <vodkablokey@.gmail.com> wrote in message
> > news:1156941597.512536.214380@.b28g2000cwb.googlegroups.com...
> > >I need to pull some reports from an older ERP system in the US via ODBC
> > >
> > > I have the report in play, but want to limit the criteria to the items
> > > shipped today
> > >
> > > I have a TODAY_FIELD as a calculated Field with the parameter =TODAY()
> > >
> > > The Format of the Oracle view is mm/dd/yyyy hh:mm:ss AM and the
> > > TODAY_FIELD
> > > matches that. (ie 8/30/2006 12:00:00 AM)
> > >
> > > So I am looking for the criteria for WHERE (REAL_SHIP_DATE >
> > > TODAY()) which will not generate and ORA-00904 error.
> > >
> > > Can anyone please help?
> > >
> I am looking to report all shipping transactions (TRANSACTION_CODE => 'OESHIP') which have happened since Midnight.
> I know = TODAY() will give you 12:00am today, how do I pass this as a
> criteria to a ODBC database call?
> I had tried =TODAY() but this is rejected, it seems to suggest I need
> to use TO_DATE, but I don't know how to do this either.
> Thanks for getting back though :)
>|||magendo_man wrote:
> Try:
> WHERE REAL_SHIP_DATE > CAST( CONVERT( VARCHAR(8), GETDATE(), 112) AS DATETIME)
> This takes the the curent system time, as suggested by Ben, chops of the
> time and returns a DateTime value at the very start of the day, similar to
> the vb Today() function.
> HTH,
> Magendo_man
> "VB" wrote:
> >
> > Ben Watts wrote:
> > > getdate() is the system date to use. Or am I understanding what you want
> > > wrong?
> > > "VB" <vodkablokey@.gmail.com> wrote in message
> > > news:1156941597.512536.214380@.b28g2000cwb.googlegroups.com...
> > > >I need to pull some reports from an older ERP system in the US via ODBC
> > > >
> > > > I have the report in play, but want to limit the criteria to the items
> > > > shipped today
> > > >
> > > > I have a TODAY_FIELD as a calculated Field with the parameter =TODAY()
> > > >
> > > > The Format of the Oracle view is mm/dd/yyyy hh:mm:ss AM and the
> > > > TODAY_FIELD
> > > > matches that. (ie 8/30/2006 12:00:00 AM)
> > > >
> > > > So I am looking for the criteria for WHERE (REAL_SHIP_DATE >
> > > > TODAY()) which will not generate and ORA-00904 error.
> > > >
> > > > Can anyone please help?
> > > >
> >
> > I am looking to report all shipping transactions (TRANSACTION_CODE => > 'OESHIP') which have happened since Midnight.
> >
> > I know = TODAY() will give you 12:00am today, how do I pass this as a
> > criteria to a ODBC database call?
> >
> > I had tried =TODAY() but this is rejected, it seems to suggest I need
> > to use TO_DATE, but I don't know how to do this either.
> >
> > Thanks for getting back though :)
> >
> >
THanks, that would do the trick.
When I try it though I get a new error, INVALID or MISSING EXPRESSION
Any ideas?|||VB wrote:
> magendo_man wrote:
> > Try:
> >
> > WHERE REAL_SHIP_DATE > CAST( CONVERT( VARCHAR(8), GETDATE(), 112) AS DATETIME)
> >
> > This takes the the curent system time, as suggested by Ben, chops of the
> > time and returns a DateTime value at the very start of the day, similar to
> > the vb Today() function.
> >
> > HTH,
> > Magendo_man
> >
> > "VB" wrote:
> >
> > >
> > > Ben Watts wrote:
> > > > getdate() is the system date to use. Or am I understanding what you want
> > > > wrong?
> > > > "VB" <vodkablokey@.gmail.com> wrote in message
> > > > news:1156941597.512536.214380@.b28g2000cwb.googlegroups.com...
> > > > >I need to pull some reports from an older ERP system in the US via ODBC
> > > > >
> > > > > I have the report in play, but want to limit the criteria to the items
> > > > > shipped today
> > > > >
> > > > > I have a TODAY_FIELD as a calculated Field with the parameter =TODAY()
> > > > >
> > > > > The Format of the Oracle view is mm/dd/yyyy hh:mm:ss AM and the
> > > > > TODAY_FIELD
> > > > > matches that. (ie 8/30/2006 12:00:00 AM)
> > > > >
> > > > > So I am looking for the criteria for WHERE (REAL_SHIP_DATE >
> > > > > TODAY()) which will not generate and ORA-00904 error.
> > > > >
> > > > > Can anyone please help?
> > > > >
> > >
> > > I am looking to report all shipping transactions (TRANSACTION_CODE => > > 'OESHIP') which have happened since Midnight.
> > >
> > > I know = TODAY() will give you 12:00am today, how do I pass this as a
> > > criteria to a ODBC database call?
> > >
> > > I had tried =TODAY() but this is rejected, it seems to suggest I need
> > > to use TO_DATE, but I don't know how to do this either.
> > >
> > > Thanks for getting back though :)
> > >
> > >
> THanks, that would do the trick.
> When I try it though I get a new error, INVALID or MISSING EXPRESSION
> Any ideas?
SOrry Folks, I realised (eventually) instead of trying to re-invent the
wheel I should just create a view in the database with the sysdate -1
call and just report the view.

Newbie question for SQL Server System Integration Services Flat File Import

I am new to SSIS...

I have a very simple package that has a flat file source object and an ole db destination object in the data flow. All works fine.

If I change a row in the flat file to make it fail how do I make the program continue and go to the next row?

The ole db destination does not have a Error Output properties like the flat file source does.

Thanks

Check the Properties of the Data Flow Task (or other task you're using for dealing with the flat file) in the Control Flow tab, there is an attribute MaximumErrorCount, which is default to 1.

newbie question -- error when connecting via System DSN on any network but the SQL Servers

I'm very new to using SQL Server. All I'm trying to do at the moment, is
create a System DSN on a Windows 2000 Professional desktop machine to
connect to a SQL Server 2000 server which is running on a different
network. When I try the DSN while connected to the SAME network that the
SQL Server machine is on, it works fine. But when I connect from any
other network, I receive the following error:
Connection failed:
SQLState: '01000'
SQL Server Error: 10061
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]ConnectionOpent
(Connect()).
Connection failed:
SQLState: '08001'
SQL Server Error: 17
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]SQL Server does not
exist or access denied.

Here are the settings that I'm using for my System DSN:
Name: test
Which SQL Server do you want to connect to? 129.22.180.203
* With SQL Server Authentication using a login ID and password entered by
the user
* Connect to SQL Server to obtain default settings for the additional
configuration options
Login ID: SA
Password: <correct password entered
And in the Client Configuration:
Server Alias: 129.22.180.203
Network libraries: TCP/IP
Server Name: 129.22.180.203
* Dynamically determine port

Also, the driver is:
Microsoft SQL Server ODBC Driver version 03.85.1025

Please, if anyone has any ideas on what I am doing wrong, I can use all
the help I can get!!
--
Using Opera's revolutionary e-mail client: http://www.opera.com/m2/sheree <sah18@.case.edu> wrote in message news:<opsb5642gy6l2js5@.uh007637>...
> I'm very new to using SQL Server. All I'm trying to do at the moment, is
> create a System DSN on a Windows 2000 Professional desktop machine to
> connect to a SQL Server 2000 server which is running on a different
> network. When I try the DSN while connected to the SAME network that the
> SQL Server machine is on, it works fine. But when I connect from any
> other network, I receive the following error:
> Connection failed:
> SQLState: '01000'
> SQL Server Error: 10061
> [Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]ConnectionOpent
> (Connect()).
> Connection failed:
> SQLState: '08001'
> SQL Server Error: 17
> [Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]SQL Server does not
> exist or access denied.
> Here are the settings that I'm using for my System DSN:
> Name: test
> Which SQL Server do you want to connect to? 129.22.180.203
> * With SQL Server Authentication using a login ID and password entered by
> the user
> * Connect to SQL Server to obtain default settings for the additional
> configuration options
> Login ID: SA
> Password: <correct password entered>
> And in the Client Configuration:
> Server Alias: 129.22.180.203
> Network libraries: TCP/IP
> Server Name: 129.22.180.203
> * Dynamically determine port
> Also, the driver is:
> Microsoft SQL Server ODBC Driver version 03.85.1025
> Please, if anyone has any ideas on what I am doing wrong, I can use all
> the help I can get!!

I'm not sure what you mean by "a different network". If you mean that
the networks are physically separated by a firewall, or if you're
connecting over a VPN, then you might need to open the ports for
MSSQL:

http://support.microsoft.com/defaul...2&Product=sql2k

Simon|||Simon,
By "different network", I mean the following:
The SQL Server is running on a Windows 2000 Server machine that is located
on a University network. If I am on any machine that is also connected to
the University network, OR if I VPN into the University network, I can
successfully connect to the SQL Server in my DSN connection with the
parameters I gave in my original posting. However, if I try from my
office computer which is on a hospital network (and IS behind a firewall)
or if I try from any computer where I'm dialed up to my ISP (no firewall
would block anything here) I still can't connect to the SQL Server in my
DSN connection.
So I really don't think it's the port that is being blocked.

Are there any connection settings in SQL Server itself that I may have
configured incorrectly? Since I know so little about the software, I'm
not sure where even to begin to look.

Thanks for helping out!
-sheree

On 4 Aug 2004 01:16:19 -0700, Simon Hayes <sql@.hayes.ch> wrote:

> sheree <sah18@.case.edu> wrote in message
> news:<opsb5642gy6l2js5@.uh007637>...
>> I'm very new to using SQL Server. All I'm trying to do at the moment,
>> is
>> create a System DSN on a Windows 2000 Professional desktop machine to
>> connect to a SQL Server 2000 server which is running on a different
>> network. When I try the DSN while connected to the SAME network that
>> the
>> SQL Server machine is on, it works fine. But when I connect from any
>> other network, I receive the following error:
>> Connection failed:
>> SQLState: '01000'
>> SQL Server Error: 10061
>> [Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]ConnectionOpent
>> (Connect()).
>> Connection failed:
>> SQLState: '08001'
>> SQL Server Error: 17
>> [Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]SQL Server does not
>> exist or access denied.
>>
>> Here are the settings that I'm using for my System DSN:
>> Name: test
>> Which SQL Server do you want to connect to? 129.22.180.203
>> * With SQL Server Authentication using a login ID and password entered
>> by
>> the user
>> * Connect to SQL Server to obtain default settings for the additional
>> configuration options
>> Login ID: SA
>> Password: <correct password entered>
>>
>> And in the Client Configuration:
>> Server Alias: 129.22.180.203
>> Network libraries: TCP/IP
>> Server Name: 129.22.180.203
>> * Dynamically determine port
>>
>> Also, the driver is:
>> Microsoft SQL Server ODBC Driver version 03.85.1025
>>
>> Please, if anyone has any ideas on what I am doing wrong, I can use all
>> the help I can get!!
> I'm not sure what you mean by "a different network". If you mean that
> the networks are physically separated by a firewall, or if you're
> connecting over a VPN, then you might need to open the ports for
> MSSQL:
> http://support.microsoft.com/defaul...2&Product=sql2k
> Simon

--
Using Opera's revolutionary e-mail client: http://www.opera.com/m2/|||> However, if I try from my
> office computer which is on a hospital network (and IS behind a firewall)
> or if I try from any computer where I'm dialed up to my ISP (no firewall
> would block anything here) I still can't connect to the SQL Server in my
> DSN connection.
> So I really don't think it's the port that is being blocked.

From your description, it seems you are trying to connect over the public
internet to the SQL Server at your university. Port 1433 needs to be open
to allow this and it is likely that the firewall at the university has the
port closed for security reasons. It works when you are connected to the
university LAN or VPN because the firewall doesn't block 1433 in those
cases.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"sheree" <sah18@.case.edu> wrote in message
news:opsb7ky5r56l2js5@.v129-22-124-109.vclient.cwru.edu...
> Simon,
> By "different network", I mean the following:
> The SQL Server is running on a Windows 2000 Server machine that is located
> on a University network. If I am on any machine that is also connected to
> the University network, OR if I VPN into the University network, I can
> successfully connect to the SQL Server in my DSN connection with the
> parameters I gave in my original posting. However, if I try from my
> office computer which is on a hospital network (and IS behind a firewall)
> or if I try from any computer where I'm dialed up to my ISP (no firewall
> would block anything here) I still can't connect to the SQL Server in my
> DSN connection.
> So I really don't think it's the port that is being blocked.
> Are there any connection settings in SQL Server itself that I may have
> configured incorrectly? Since I know so little about the software, I'm
> not sure where even to begin to look.
> Thanks for helping out!
> -sheree
>
> On 4 Aug 2004 01:16:19 -0700, Simon Hayes <sql@.hayes.ch> wrote:
> > sheree <sah18@.case.edu> wrote in message
> > news:<opsb5642gy6l2js5@.uh007637>...
> >> I'm very new to using SQL Server. All I'm trying to do at the moment,
> >> is
> >> create a System DSN on a Windows 2000 Professional desktop machine to
> >> connect to a SQL Server 2000 server which is running on a different
> >> network. When I try the DSN while connected to the SAME network that
> >> the
> >> SQL Server machine is on, it works fine. But when I connect from any
> >> other network, I receive the following error:
> >> Connection failed:
> >> SQLState: '01000'
> >> SQL Server Error: 10061
> >> [Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]ConnectionOpent
> >> (Connect()).
> >> Connection failed:
> >> SQLState: '08001'
> >> SQL Server Error: 17
> >> [Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]SQL Server does not
> >> exist or access denied.
> >>
> >> Here are the settings that I'm using for my System DSN:
> >> Name: test
> >> Which SQL Server do you want to connect to? 129.22.180.203
> >> * With SQL Server Authentication using a login ID and password entered
> >> by
> >> the user
> >> * Connect to SQL Server to obtain default settings for the additional
> >> configuration options
> >> Login ID: SA
> >> Password: <correct password entered>
> >>
> >> And in the Client Configuration:
> >> Server Alias: 129.22.180.203
> >> Network libraries: TCP/IP
> >> Server Name: 129.22.180.203
> >> * Dynamically determine port
> >>
> >> Also, the driver is:
> >> Microsoft SQL Server ODBC Driver version 03.85.1025
> >>
> >> Please, if anyone has any ideas on what I am doing wrong, I can use all
> >> the help I can get!!
> > I'm not sure what you mean by "a different network". If you mean that
> > the networks are physically separated by a firewall, or if you're
> > connecting over a VPN, then you might need to open the ports for
> > MSSQL:
http://support.microsoft.com/defaul...2&Product=sql2k
> > Simon
>
> --
> Using Opera's revolutionary e-mail client: http://www.opera.com/m2/|||Dan,
Can another port be used for SQL Server to run on (rather than using the
default port 1433, which is what it is currently running on)? It might be
easier for me to use a different port that is already open at the
university, rather than attempt to request that they open port 1433
(unlikely they will do this).
Thanks so much for your help!
-sheree

On Wed, 04 Aug 2004 13:13:12 GMT, Dan Guzman
<danguzman@.nospam-earthlink.net> wrote:

> From your description, it seems you are trying to connect over the public
> internet to the SQL Server at your university. Port 1433 needs to be
> open
> to allow this and it is likely that the firewall at the university has
> the
> port closed for security reasons. It works when you are connected to the
> university LAN or VPN because the firewall doesn't block 1433 in those
> cases.|||sheree (sah18@.case.edu) writes:
> Can another port be used for SQL Server to run on (rather than using the
> default port 1433, which is what it is currently running on)? It might be
> easier for me to use a different port that is already open at the
> university, rather than attempt to request that they open port 1433

Yes, it is possible to use another port. (Which would affect everyone
using that server, as SQL Server uses only one port at a time.) No,
I don't think your university is likely to make SQL Server available on the
Internet on any port, least of all if you insist on using SQL Server
authentication.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Although it is certainly possible to configure SQL Server to listen on an
alternate port, it appears the SQL Server is configured to use 1433 because
your working local/VPN connections use the default port.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"sheree" <sah18@.case.edu> wrote in message
news:opsb7mufwi6l2js5@.v129-22-124-109.vclient.cwru.edu...
> Dan,
> Can another port be used for SQL Server to run on (rather than using the
> default port 1433, which is what it is currently running on)? It might be
> easier for me to use a different port that is already open at the
> university, rather than attempt to request that they open port 1433
> (unlikely they will do this).
> Thanks so much for your help!
> -sheree
>
> On Wed, 04 Aug 2004 13:13:12 GMT, Dan Guzman
> <danguzman@.nospam-earthlink.net> wrote:
> > From your description, it seems you are trying to connect over the
public
> > internet to the SQL Server at your university. Port 1433 needs to be
> > open
> > to allow this and it is likely that the firewall at the university has
> > the
> > port closed for security reasons. It works when you are connected to
the
> > university LAN or VPN because the firewall doesn't block 1433 in those
> > cases.

Newbie Q: System DSN from DMZ?

Hello,
I have a webserver running in a DMZ off my network. It needs access to a
SQL2000 server running inside my network. How can I create a system DSN
that points to my SQL2000 server when there is no direct path?
When I create the DSN, it asks, "which SQL Server to you want to connect
to?" But, the drop down box is empty. What should I be typing in here, and
what holes do I need to create in my firewall to make it work?
-Trevori'm not a firewall expert here, but as far as i'm aware you need to open up the port for SQL server on the firewall - you ca fid this port number from the SQL Cliet manager tool|||simply type in the name of your db server when creating the dsn.
assuming your sqlserver is listening on tcp port 1433, you'll need to open up
port 1433 in the firewall, but only open the hole for your web server. check
your sql server log to see what port it's listening on.
Trevor Clark wrote:
> Hello,
> I have a webserver running in a DMZ off my network. It needs access to a
> SQL2000 server running inside my network. How can I create a system DSN
> that points to my SQL2000 server when there is no direct path?
> When I create the DSN, it asks, "which SQL Server to you want to connect
> to?" But, the drop down box is empty. What should I be typing in here, and
> what holes do I need to create in my firewall to make it work?
> -Trevor

Newbie Q: System DSN from DMZ?

Hello,
I have a webserver running in a DMZ off my network. It needs access to a
SQL2000 server running inside my network. How can I create a system DSN
that points to my SQL2000 server when there is no direct path?
When I create the DSN, it asks, "which SQL Server to you want to connect
to?" But, the drop down box is empty. What should I be typing in here, and
what holes do I need to create in my firewall to make it work?
-Trevori'm not a firewall expert here, but as far as i'm aware you need to open up
the port for SQL server on the firewall - you ca fid this port number from t
he SQL Cliet manager tool|||simply type in the name of your db server when creating the dsn.
assuming your sqlserver is listening on tcp port 1433, you'll need to open u
p
port 1433 in the firewall, but only open the hole for your web server. chec
k
your sql server log to see what port it's listening on.
Trevor Clark wrote:

quote:

---

> Hello,
> I have a webserver running in a DMZ off my network. It needs access to a
> SQL2000 server running inside my network. How can I create a system DSN
> that points to my SQL2000 server when there is no direct path?
> When I create the DSN, it asks, "which SQL Server to you want to connect
> to?" But, the drop down box is empty. What should I be typing in here, a
nd
> what holes do I need to create in my firewall to make it work?
> -Trevor

---

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

Newbee password question

Is there a way to retreive passords for SQL Server authenticated users from
a system table? I'd like to retreive the passwords for a handfull of SQL
Server users and place them in a database table (for application purposes).
I've looked for functions and stored procedures in the Books On Line, but
came up empty.
Thanks,
JoeSQL Server stores passwords using a one-way hash. Although you could
retrieve the hashed data from sysligns, the value is meaningless to your
application.
Hope this helps.
Dan Guzman
SQL Server MVP
"JRE" <nomail@.all> wrote in message
news:%232kk9yiLEHA.268@.TK2MSFTNGP10.phx.gbl...
> Is there a way to retreive passords for SQL Server authenticated users
from
> a system table? I'd like to retreive the passwords for a handfull of SQL
> Server users and place them in a database table (for application
purposes).
> I've looked for functions and stored procedures in the Books On Line, but
> came up empty.
> Thanks,
> Joe
>|||Thanks Dan,
I saw several articles describing adding or changing passwords...but not
fetching them. Oh well...I guess I'll have to think of an alternative
solution to what Im trying to do
Regards,
Joe
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:ePv63IlLEHA.1032@.tk2msftngp13.phx.gbl...
> SQL Server stores passwords using a one-way hash. Although you could
> retrieve the hashed data from sysligns, the value is meaningless to your
> application.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "JRE" <nomail@.all> wrote in message
> news:%232kk9yiLEHA.268@.TK2MSFTNGP10.phx.gbl...
> from
> purposes).
but[vbcol=seagreen]
>

newbe question: need a way to read in large query with .net

Could someone please point me in the right direction on how to read in
a large query with .net.

I am trying to emulate a legacy database system so I don't know the
upper bounds of the sql query. An example query would be something
like:

Select * from invoices where year 1995

the query must be updatable and only return say 10 to 100 rows at a
time.
It should also be forward only and discard rows no longer in use to
save memory.

And if at all possible I would like to lock one row at a time as the
row is read in.(troy@.makaro.com) writes:

Quote:

Originally Posted by

Could someone please point me in the right direction on how to read in
a large query with .net.
>
I am trying to emulate a legacy database system so I don't know the
upper bounds of the sql query. An example query would be something
like:
>
Select * from invoices where year 1995
>
the query must be updatable and only return say 10 to 100 rows at a
time.
It should also be forward only and discard rows no longer in use to
save memory.

It sounds like you should use ExecuteReader and loop through the rows.
That is, do not use DataAdapter.Fill. Furthermore, to make it possible
to update the rows as you have read them in, you need to enable MARS,
Multiple Active Result Sets, which I believe you do in the connection
string.

However, if your plan is to read one row at a time and update back,
I wonder from where you get the information to update. It's much much
efficient to perform the update in the database on all rows in one
go.

--
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|||Sounds like you need to take a look at this URL:
http://msdn2.microsoft.com/en-us/li...594(VS.80).aspx though
I doubt a newb will understand it much. This is just in case there are
other "MVP's" like myself who are in need of a good reference for a
task like large query input.

HTH,

Carl Tegeder
Master MS-SQL Administrator
MS-SQL MVP

On Feb 26, 12:36 pm, t...@.makaro.com wrote:

Quote:

Originally Posted by

Could someone please point me in the right direction on how to read in
a large query with .net.
>
I am trying to emulate a legacy database system so I don't know the
upper bounds of the sql query. An example query would be something
like:
>
Select * from invoices where year 1995
>
the query must be updatable and only return say 10 to 100 rows at a
time.
It should also be forward only and discard rows no longer in use to
save memory.
>
And if at all possible I would like to lock one row at a time as the
row is read in.

|||Great information on MARS and the ExecuteReader. What I am trying to
do is to emulate a legacy product's data access method. The reason I
am doing this is there is just way too much code to convert into
proper sql. I'm talking at least 1 million lines of code. I have
already written a conversion program to convert the code to VB.NET and
now I must right a dll assembly to emulate the legacy data access.

Here is one an example of what I have to emulate:

get #3, key #1 GE "20060101"
while invoiceDate < "20070101"
! The current row is now locked!
! make changes
update #3 ! updates the currently locked row and now unlocked.
get #3 ! read the next row in
next

The converted code looks something like:

' note: 3 = the registered table
SQL.getGreaterEqual(3, "20060101") ' notice no upper bounds
while invoiceDate < "20070101"
' The current row is now locked!
' make changes
SQL.update(3) ' updates the currently locked row and now unlocked.
SQL.getNext(3) ! read the next row in
next

My question now is:
How do I lock one row at a time?

One thought I had was to make all connections go through my own
service. That service could keep track of locks. The problem with that
is that future products will want to use sql properly which would
bypass the locking.|||(troy@.makaro.com) writes:

Quote:

Originally Posted by

Great information on MARS and the ExecuteReader. What I am trying to
do is to emulate a legacy product's data access method. The reason I
am doing this is there is just way too much code to convert into
proper sql. I'm talking at least 1 million lines of code. I have
already written a conversion program to convert the code to VB.NET and
now I must right a dll assembly to emulate the legacy data access.

I can't escape asking what's the point? You get the legacy product
converted to .Net, but it will still have the architecutre of the
old product, and risk is that you get a compromise with the worst from
both.

Quote:

Originally Posted by

Here is one an example of what I have to emulate:
>
get #3, key #1 GE "20060101"
while invoiceDate < "20070101"
! The current row is now locked!
! make changes
update #3 ! updates the currently locked row and now unlocked.
get #3 ! read the next row in
next
>
The converted code looks something like:
>
' note: 3 = the registered table
SQL.getGreaterEqual(3, "20060101") ' notice no upper bounds
while invoiceDate < "20070101"
' The current row is now locked!
' make changes
SQL.update(3) ' updates the currently locked row and now unlocked.
SQL.getNext(3) ! read the next row in
next
>
My question now is:
How do I lock one row at a time?

I take it that the other product was using another data store than
SQL Server?

There are a couple of ways to do this, but it is important to understand
that locking a row is nothing you don't really do actively in SQL Server.
This is left to the lock manager.

And it's even less possible in ADO .Net, since ADO .Net uses client-
side cursors only. That is data is read from SQL Server and buffered.
Something like ExecuteReader may not read all million rows at once,
but it will not fetch one row at a time.

One way is to wrap the entire reader in a transaction with the isolation
level REPEATABLE READ. But then rows will remained locked until you
commit.

However, the only reasonable approach is optimistic locking. That is,
don't lock, but check for concurrent updates when you update. This
can be done in two ways:

1) Add a timestamp column: a timestamp column is automatically updated when
the row is updated. If you include the timestamp column in the WHERE
clause, and you see that @.@.rowcount is 0, then you know that the row
was changed since you last read it. 2) Without a timestamp column just
add all columns to the WHERE clause. I believe that the Update commands
that comes with the CommandBuilder includes this.

I can think of a third way: first read all keys into local array. Then
iterate over the array, and read one row at a time as 1) Start transaction
with REPEATABLE READ, 2) read row 3) update and 4) commit. But this
will be slow as I don't know what.

All and all, I think you are fighting an uphiil battle.

--
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 for the response. see inline comments:

Quote:

Originally Posted by

>
I take it that the other product was using another data store than
SQL Server?

Yes, unfortunately :(

Quote:

Originally Posted by

However, the only reasonable approach is optimistic locking.

ya, this is the best method for sure, its just that I need to emulate
the old system as accurately as possible. The legacy software does not
expect concurrency errors on the updates.

Quote:

Originally Posted by

I can think of a third way: first read all keys into local array. Then
iterate over the array, and read one row at a time as 1) Start transaction
with REPEATABLE READ, 2) read row 3) update and 4) commit. But this
will be slow as I don't know what.

Fortunately the resultsets that require a lock on each row will
probably be fairly small. Larger resultsets are typically reports
which don't require locks. I can see I am going to have to write a
performance test to see the actual speed of reading say 500 rows one
at a time with a lock on them.