This is a multi-part message in MIME format.
--=_NextPart_000_006D_01C654BC.18062AC0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
The first time I posted it landed under someone else's thread & I wasn't = sure anyone would see it to help me out.
I have a database that's being restored on a monthly basis, but I think = this process is overkill.
The users are using a few tables or whatever to run reports using = Crystal Reports, and accessing tables via MS Access.
What do I profile for to 'see' everything that is being used in this = database so that I can restore only these objects as opposed to the = entire db?
I have only used profiler to do some query statement troubleshooting, = 'Stored Procedure Compelted', but other than that am not proficient with = profiler.
All suggestions are greatly appreciated!!!!
--=_NextPart_000_006D_01C654BC.18062AC0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
The first time I posted it landed under = someone else's thread & I wasn't sure anyone would see it to help me = out.
I have a database that's being restored on a = monthly basis, but I think this process is overkill.
The users are using a few tables or whatever = to run reports using Crystal Reports, and accessing tables via MS = Access.
What do I profile for to 'see' everything = that is being used in this database so that I can restore only these objects as = opposed to the entire db?
I have only used profiler to do some query = statement troubleshooting, 'Stored Procedure Compelted', but other than that am = not proficient with profiler.
All suggestions are greatly appreciated!!!!
--=_NextPart_000_006D_01C654BC.18062AC0--How big is your database? If it's small, restoring the database for reporting
may not be a bad practice. Note that you can't just 'restore only these
objects' from the database backup file if you are using SQL Server native
restore.
Linchi
"Jude" wrote:
> The first time I posted it landed under someone else's thread & I wasn't sure anyone would see it to help me out.
> I have a database that's being restored on a monthly basis, but I think this process is overkill.
> The users are using a few tables or whatever to run reports using Crystal Reports, and accessing tables via MS Access.
> What do I profile for to 'see' everything that is being used in this database so that I can restore only these objects as opposed to the entire db?
> I have only used profiler to do some query statement troubleshooting, 'Stored Procedure Compelted', but other than that am not proficient with profiler.
> All suggestions are greatly appreciated!!!!
>|||Actually I am not using Sql Server Native backup software & I do have an
option of restoring single objects.
But to be perfectly honest I didn't want to debate the point, I want to
learn about using profiler to effectively capture this information and
suppose I was looking for a quick answer.
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:FAD6A024-8973-4AFC-8F28-75B5ADC2B618@.microsoft.com...
> How big is your database? If it's small, restoring the database for
> reporting
> may not be a bad practice. Note that you can't just 'restore only these
> objects' from the database backup file if you are using SQL Server native
> restore.
> Linchi
> "Jude" wrote:
>> The first time I posted it landed under someone else's thread & I wasn't
>> sure anyone would see it to help me out.
>> I have a database that's being restored on a monthly basis, but I think
>> this process is overkill.
>> The users are using a few tables or whatever to run reports using Crystal
>> Reports, and accessing tables via MS Access.
>> What do I profile for to 'see' everything that is being used in this
>> database so that I can restore only these objects as opposed to the
>> entire db?
>> I have only used profiler to do some query statement troubleshooting,
>> 'Stored Procedure Compelted', but other than that am not proficient with
>> profiler.
>> All suggestions are greatly appreciated!!!!
>>
Showing posts with label plain. Show all posts
Showing posts with label plain. Show all posts
Sunday, March 11, 2012
Thursday, March 8, 2012
Another issue with Linked Server
http://support.microsoft.com/?id=814398
I read this article. Maybe Im just plain dumb, but I get this error when I
try to connect to a Linked Access database via sql server. Now the article
states to login using the SQL Server start up account. Im assuming that this
account is the one thats stated in the MSSQLSERVER service under the LogOn
tab.
Well, if it is, then the logon account is "system" in my case. And I cant
physically login to the computer as system now can I?
How difficult can it be to create a linked server to a access database thats
password protected?
Girish
Hi Girish,
From your descriptions, I understood you would like to link an secured
Access mdb file as Linked Server in SQL Server. Have I understood you? If
there is anything misunderstood, please feel free to let me know.
First of all, I would like to answer the question what "system" is. If you
see "System" is selected in LogOn tab of MSSQLServer service, it means
MSSQLServer is started by the account you start Windows. For example,
assuming you are login as "Girish" in you Windows 2003 (or other Windows
Operation System) and "Girish" is in Administartor Group of Windows 2003,
your SQL Server is started as "Girish".
Secondly, I am not sure you encounter exactly the scenario KB:814398
described. Based on my scope, I think the following documents will be
helpful for you.
HOWTO: Use a SQL Server 7.0 Distributed Query with a Linked Server to
Secured Access Databases
http://support.microsoft.com/default...;en-us;Q246255
You receive a "Could not find installable ISAM" error message or some file
types are missing when you import files, export files, or link files in
Access 2000
http://support.microsoft.com/default...;EN-US;Q209805
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Microsoft Developer Community Support
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||Thanks for your reply.
What does SQL Server use to logon to if I do not login interactively?
Its a service so must still run all the time right?
Girish
""Mingqing Cheng [MSFT]"" <v-mingqc@.online.microsoft.com> wrote in message
news:VbRBzDZhEHA.2632@.cpmsftngxa10.phx.gbl...
> Hi Girish,
> From your descriptions, I understood you would like to link an secured
> Access mdb file as Linked Server in SQL Server. Have I understood you? If
> there is anything misunderstood, please feel free to let me know.
> First of all, I would like to answer the question what "system" is. If you
> see "System" is selected in LogOn tab of MSSQLServer service, it means
> MSSQLServer is started by the account you start Windows. For example,
> assuming you are login as "Girish" in you Windows 2003 (or other Windows
> Operation System) and "Girish" is in Administartor Group of Windows 2003,
> your SQL Server is started as "Girish".
> Secondly, I am not sure you encounter exactly the scenario KB:814398
> described. Based on my scope, I think the following documents will be
> helpful for you.
> HOWTO: Use a SQL Server 7.0 Distributed Query with a Linked Server to
> Secured Access Databases
> http://support.microsoft.com/default...;en-us;Q246255
> You receive a "Could not find installable ISAM" error message or some file
> types are missing when you import files, export files, or link files in
> Access 2000
> http://support.microsoft.com/default...;EN-US;Q209805
> Thank you for your patience and cooperation. If you have any questions or
> concerns, don't hesitate to let me know. We are here to be of assistance!
>
> Sincerely yours,
> Mingqing Cheng
> Microsoft Developer Community Support
> Introduction to Yukon! - http://www.microsoft.com/sql/yukon
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>
|||Hi Girish,
MSSQLServer is run as a background services, you could also make it started
automatically or manually. In General Tab of MSSQLServer, you could change
"Startup type" between Automatic, Manual and Disable, while you could click
the button under Serivce status to make it start, stop and pause (resume).
If you choose Manual, MSSQLServer will be started only after you click the
"start" button, if Automatic is choosen, MSSQLServer will be started every
time Windows started up.
Additional, we could also use DOS-prompt to start/stop a service like
MSSQLServer. For example, "net start mssqlserver" or "net stop mssqlserver"
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Microsoft Developer Community Support
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||My mistake... I didnt mean "how" does it start up...
I meant.. when it starts up automatically, what account does it use to login
if I choose "system" and Im NOT interactively logged into the computer.
Im getting confused because my MSSQLServer does run in the background... and
Ive choosen it to login as system... and you said in an earlier email that
system means; if I LOGIN as Girish, then system = Girish. If I login as XYZ,
then system = XYZ.
Now my Q is; if I DONT login at all and MSSQLServer is automatic and a
background service and is set to login as system; then system = ?
Girish
""Mingqing Cheng [MSFT]"" <v-mingqc@.online.microsoft.com> wrote in message
news:cagdUwchEHA.2632@.cpmsftngxa10.phx.gbl...
> Hi Girish,
> MSSQLServer is run as a background services, you could also make it
started
> automatically or manually. In General Tab of MSSQLServer, you could change
> "Startup type" between Automatic, Manual and Disable, while you could
click
> the button under Serivce status to make it start, stop and pause (resume).
> If you choose Manual, MSSQLServer will be started only after you click the
> "start" button, if Automatic is choosen, MSSQLServer will be started every
> time Windows started up.
> Additional, we could also use DOS-prompt to start/stop a service like
> MSSQLServer. For example, "net start mssqlserver" or "net stop
mssqlserver"
> Thank you for your patience and cooperation. If you have any questions or
> concerns, don't hesitate to let me know. We are here to be of assistance!
>
> Sincerely yours,
> Mingqing Cheng
> Microsoft Developer Community Support
> Introduction to Yukon! - http://www.microsoft.com/sql/yukon
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>
|||Hi Girish,
I feel sorry for my poor understanding, thanks for your kindest
explaination.
If MSSQLServer is selected to be started as Local System account, it will
be started as NT AUTHORITH\SYSTEM. This account is internal and invisable
no matter what username you choosed to login.
Here are some descriptions fro BOL, which I think will be helpful
explaining it
The local system account does not require a password, does not have network
access rights in Windows NT 4.0, and may restrict your SQL Server
installation from interacting with other servers.
Note In Windows 2000, the local system account does allow network access.
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Microsoft Developer Community Support
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||Hi Girish,
I am just checking on your progress regarding the information that was sent
you! If you encounter any difficulty, please do not hesitate to let me
know. Please post here and let me know the status of your issue. Without
your further information, it's very hard for me to continue with the
troubleshooting.
Looking forward to hearing from you soon. Thank you for your patience and
cooperation.
Sincerely yours,
Mingqing Cheng
Microsoft Developer Community Support
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
I read this article. Maybe Im just plain dumb, but I get this error when I
try to connect to a Linked Access database via sql server. Now the article
states to login using the SQL Server start up account. Im assuming that this
account is the one thats stated in the MSSQLSERVER service under the LogOn
tab.
Well, if it is, then the logon account is "system" in my case. And I cant
physically login to the computer as system now can I?
How difficult can it be to create a linked server to a access database thats
password protected?
Girish
Hi Girish,
From your descriptions, I understood you would like to link an secured
Access mdb file as Linked Server in SQL Server. Have I understood you? If
there is anything misunderstood, please feel free to let me know.
First of all, I would like to answer the question what "system" is. If you
see "System" is selected in LogOn tab of MSSQLServer service, it means
MSSQLServer is started by the account you start Windows. For example,
assuming you are login as "Girish" in you Windows 2003 (or other Windows
Operation System) and "Girish" is in Administartor Group of Windows 2003,
your SQL Server is started as "Girish".
Secondly, I am not sure you encounter exactly the scenario KB:814398
described. Based on my scope, I think the following documents will be
helpful for you.
HOWTO: Use a SQL Server 7.0 Distributed Query with a Linked Server to
Secured Access Databases
http://support.microsoft.com/default...;en-us;Q246255
You receive a "Could not find installable ISAM" error message or some file
types are missing when you import files, export files, or link files in
Access 2000
http://support.microsoft.com/default...;EN-US;Q209805
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Microsoft Developer Community Support
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||Thanks for your reply.
What does SQL Server use to logon to if I do not login interactively?
Its a service so must still run all the time right?
Girish
""Mingqing Cheng [MSFT]"" <v-mingqc@.online.microsoft.com> wrote in message
news:VbRBzDZhEHA.2632@.cpmsftngxa10.phx.gbl...
> Hi Girish,
> From your descriptions, I understood you would like to link an secured
> Access mdb file as Linked Server in SQL Server. Have I understood you? If
> there is anything misunderstood, please feel free to let me know.
> First of all, I would like to answer the question what "system" is. If you
> see "System" is selected in LogOn tab of MSSQLServer service, it means
> MSSQLServer is started by the account you start Windows. For example,
> assuming you are login as "Girish" in you Windows 2003 (or other Windows
> Operation System) and "Girish" is in Administartor Group of Windows 2003,
> your SQL Server is started as "Girish".
> Secondly, I am not sure you encounter exactly the scenario KB:814398
> described. Based on my scope, I think the following documents will be
> helpful for you.
> HOWTO: Use a SQL Server 7.0 Distributed Query with a Linked Server to
> Secured Access Databases
> http://support.microsoft.com/default...;en-us;Q246255
> You receive a "Could not find installable ISAM" error message or some file
> types are missing when you import files, export files, or link files in
> Access 2000
> http://support.microsoft.com/default...;EN-US;Q209805
> Thank you for your patience and cooperation. If you have any questions or
> concerns, don't hesitate to let me know. We are here to be of assistance!
>
> Sincerely yours,
> Mingqing Cheng
> Microsoft Developer Community Support
> Introduction to Yukon! - http://www.microsoft.com/sql/yukon
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>
|||Hi Girish,
MSSQLServer is run as a background services, you could also make it started
automatically or manually. In General Tab of MSSQLServer, you could change
"Startup type" between Automatic, Manual and Disable, while you could click
the button under Serivce status to make it start, stop and pause (resume).
If you choose Manual, MSSQLServer will be started only after you click the
"start" button, if Automatic is choosen, MSSQLServer will be started every
time Windows started up.
Additional, we could also use DOS-prompt to start/stop a service like
MSSQLServer. For example, "net start mssqlserver" or "net stop mssqlserver"
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Microsoft Developer Community Support
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||My mistake... I didnt mean "how" does it start up...
I meant.. when it starts up automatically, what account does it use to login
if I choose "system" and Im NOT interactively logged into the computer.
Im getting confused because my MSSQLServer does run in the background... and
Ive choosen it to login as system... and you said in an earlier email that
system means; if I LOGIN as Girish, then system = Girish. If I login as XYZ,
then system = XYZ.
Now my Q is; if I DONT login at all and MSSQLServer is automatic and a
background service and is set to login as system; then system = ?
Girish
""Mingqing Cheng [MSFT]"" <v-mingqc@.online.microsoft.com> wrote in message
news:cagdUwchEHA.2632@.cpmsftngxa10.phx.gbl...
> Hi Girish,
> MSSQLServer is run as a background services, you could also make it
started
> automatically or manually. In General Tab of MSSQLServer, you could change
> "Startup type" between Automatic, Manual and Disable, while you could
click
> the button under Serivce status to make it start, stop and pause (resume).
> If you choose Manual, MSSQLServer will be started only after you click the
> "start" button, if Automatic is choosen, MSSQLServer will be started every
> time Windows started up.
> Additional, we could also use DOS-prompt to start/stop a service like
> MSSQLServer. For example, "net start mssqlserver" or "net stop
mssqlserver"
> Thank you for your patience and cooperation. If you have any questions or
> concerns, don't hesitate to let me know. We are here to be of assistance!
>
> Sincerely yours,
> Mingqing Cheng
> Microsoft Developer Community Support
> Introduction to Yukon! - http://www.microsoft.com/sql/yukon
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>
|||Hi Girish,
I feel sorry for my poor understanding, thanks for your kindest
explaination.
If MSSQLServer is selected to be started as Local System account, it will
be started as NT AUTHORITH\SYSTEM. This account is internal and invisable
no matter what username you choosed to login.
Here are some descriptions fro BOL, which I think will be helpful
explaining it
The local system account does not require a password, does not have network
access rights in Windows NT 4.0, and may restrict your SQL Server
installation from interacting with other servers.
Note In Windows 2000, the local system account does allow network access.
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Microsoft Developer Community Support
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||Hi Girish,
I am just checking on your progress regarding the information that was sent
you! If you encounter any difficulty, please do not hesitate to let me
know. Please post here and let me know the status of your issue. Without
your further information, it's very hard for me to continue with the
troubleshooting.
Looking forward to hearing from you soon. Thank you for your patience and
cooperation.
Sincerely yours,
Mingqing Cheng
Microsoft Developer Community Support
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
Wednesday, March 7, 2012
Another 101 question
With a plain select if no rows are returned then the row count is 0, but
with a select that assigns to a variable you will get a rowcount of one even
though no actual row was found. I am guessing that is because of assigning
to a variable SQL will return an empty result set, which is a result set of
1, is this correct?
i.e. SET @.mycol =
(SELECT col FROM myTable WHERE id = 1)
Under these circumstances is the best technique to just test the variable
@.mycol for a null value
OR write the select like:
IF EXISTS
(SELECT mycol FROM myTable
WHERE myID = 3)
BEGIN
SET @.mycol =
(SELECT mycol FROM myTable
WHERE myID = 3)
PRINT '@.mycol : ' + CAST(mycol as varchar(15))
END
ELSE
PRINT 'ROW DOES NOT EXIST'
Or is there an even better and/or more professional way to do it?Thank you! I was making my self nuts with the what if's, it seemed that
going beyond validating the current entry could turn into a never ending
task. :)
Except for the news groups I am learning in a vacuum, it is not like
learning in maintenance of a production environment where you get to see
what others have done.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eb6rNDKvFHA.3896@.TK2MSFTNGP15.phx.gbl...
> Thanks Erland, I really should have mentioned that.
> Dazed,
> If you have a Unique constraint you should not have to test for
> duplicates when selecting the values out. The constraint will make sure
> there are no duplicates in the first place.
> --
> Andrew J. Kelly SQL MVP
>
> "Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
> news:Xns96D61468ABF3Yazorman@.127.0.0.1...
>|||What is the desired behavior? DO you simply want to know if one or more
rows exist or not? If so then always use EXISTS.
Andrew J. Kelly SQL MVP
"DazedAndConfused" <AceMagoo61@.yahoo.com> wrote in message
news:eXq$4VHvFHA.2072@.TK2MSFTNGP14.phx.gbl...
> With a plain select if no rows are returned then the row count is 0, but
> with a select that assigns to a variable you will get a rowcount of one
> even though no actual row was found. I am guessing that is because of
> assigning to a variable SQL will return an empty result set, which is a
> result set of 1, is this correct?
> i.e. SET @.mycol =
> (SELECT col FROM myTable WHERE id = 1)
> Under these circumstances is the best technique to just test the variable
> @.mycol for a null value
> OR write the select like:
> IF EXISTS
> (SELECT mycol FROM myTable
> WHERE myID = 3)
> BEGIN
> SET @.mycol =
> (SELECT mycol FROM myTable
> WHERE myID = 3)
> PRINT '@.mycol : ' + CAST(mycol as varchar(15))
> END
> ELSE
> PRINT 'ROW DOES NOT EXIST'
> Or is there an even better and/or more professional way to do it?
>|||When you use a scalar subquery, you can get a one-row, one-column table
that is converted to a scalar; you can get an empty table that is
converted to a NULL; you can get a multi-row, one-column table that
gives a cardinality error when you try to put it into a scalar.|||In this paticular situation I want the field value. I was wondering if it
was better to just do the select and test the variable for a null or do a
EXISTS SELECT and then if it does exist select into the variable.
I'm just learning and trying to find the best way to build a mouse trap,
I've found this news group very helpful. i.e. I had a 130 line procedure
yesterday that was cut down to about 30 from information obtained from the
group. There are a lot of ways to get things to work, some are a lot better
than others. I'm looking for the right ways.
Thank you!!
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:ObA0rlHvFHA.2504@.TK2MSFTNGP15.phx.gbl...
> What is the desired behavior? DO you simply want to know if one or more
> rows exist or not? If so then always use EXISTS.
> --
> Andrew J. Kelly SQL MVP
>
> "DazedAndConfused" <AceMagoo61@.yahoo.com> wrote in message
> news:eXq$4VHvFHA.2072@.TK2MSFTNGP14.phx.gbl...
>|||OK then there is no need for an EXISTS first. You can do this several ways
like:
SET @.mycol = (SELECT col FROM myTable WHERE id = 1)
or
SELECT @.mycol = Col FROM myTable WHERE id = 1
IF @.myCol IS NOT NULL
BEGIN
-- Do your thing here
END
ELSE
...
Also make sure that there will only be at most 1 row returned. As long as
ID is a unique value you should be ok.
Andrew J. Kelly SQL MVP
"DazedAndConfused" <AceMagoo61@.yahoo.com> wrote in message
news:uQq5RzHvFHA.3548@.tk2msftngp13.phx.gbl...
> In this paticular situation I want the field value. I was wondering if it
> was better to just do the select and test the variable for a null or do a
> EXISTS SELECT and then if it does exist select into the variable.
> I'm just learning and trying to find the best way to build a mouse trap,
> I've found this news group very helpful. i.e. I had a 130 line procedure
> yesterday that was cut down to about 30 from information obtained from the
> group. There are a lot of ways to get things to work, some are a lot
> better than others. I'm looking for the right ways.
> Thank you!!
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:ObA0rlHvFHA.2504@.TK2MSFTNGP15.phx.gbl...
>|||Thank you again! id is unique, but I am checking if rowcount is > 1 anyways
and plan on throwing an error if it is. I plan on temporarily removing the
unique constraint and putting in a dupe record to test it.
Is that going too far?
Or is it a good idea to try to handle a situation that in theory should
never happen?
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OMkfpNIvFHA.3256@.TK2MSFTNGP09.phx.gbl...
> OK then there is no need for an EXISTS first. You can do this several
> ways like:
> SET @.mycol = (SELECT col FROM myTable WHERE id = 1)
> or
> SELECT @.mycol = Col FROM myTable WHERE id = 1
>
> IF @.myCol IS NOT NULL
> BEGIN
> -- Do your thing here
> END
> ELSE
> ...
> Also make sure that there will only be at most 1 row returned. As long as
> ID is a unique value you should be ok.
> --
> Andrew J. Kelly SQL MVP
>
> "DazedAndConfused" <AceMagoo61@.yahoo.com> wrote in message
> news:uQq5RzHvFHA.3548@.tk2msftngp13.phx.gbl...
>|||DazedAndConfused (AceMagoo61@.yahoo.com) writes:
> Thank you again! id is unique, but I am checking if rowcount is > 1
> anyways and plan on throwing an error if it is. I plan on temporarily
> removing the unique constraint and putting in a dupe record to test it.
> Is that going too far?
> Or is it a good idea to try to handle a situation that in theory should
> never happen?
OK, now we are in for a real treat! I will show you how to do it, and if
that does not convince you that are going too far, nothing will. :-)
Andy showed you two ways, but they are a little different, which he failed
to tell. Let's look at them again:
0 rows -> @.mycol is assigned NULL, @.@.rowcount = 1
1 rows -> @.mycol is assigned the value, @.@.rowcount = 1
many rows -> You will get an error, "subquery returned more than one value".
0 rows -> @.mycol unchanged(!), @.@.rowcount = 0
1 row -> @.mycol assigned the value, @.@.rowcount = 1
many rows -> @.mycol gets the last value in the result set, which that
is undefined unless you have an ORDER BY. @.@.rowcount is
set to the number of matching rows.
Look at 0 rows again:
SELECT @.mycol = 4711
SELECT @.mycol = Col FROM myTable WHERE id = 1
If there is no row with id = 1, @.mycol will remain 4711, it will not be
set to NULL.
If you really want to check for duplicates, and handle the situation
yourself, it is the SELECT assignment you want to use.
If you keep the constraints, which you should unless you have very good
reasons, the SET method is a little safer. Then again, if you know how
SELECT behaves you can be careful make sure variable is NULL before
you use it. (Yet then again, that is a trap that even season T-SQL
programmers fall into, every now and then!)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks Erland, I really should have mentioned that.
Dazed,
If you have a Unique constraint you should not have to test for
duplicates when selecting the values out. The constraint will make sure
there are no duplicates in the first place.
Andrew J. Kelly SQL MVP
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns96D61468ABF3Yazorman@.127.0.0.1...
> DazedAndConfused (AceMagoo61@.yahoo.com) writes:
> OK, now we are in for a real treat! I will show you how to do it, and if
> that does not convince you that are going too far, nothing will. :-)
> Andy showed you two ways, but they are a little different, which he failed
> to tell. Let's look at them again:
>
> 0 rows -> @.mycol is assigned NULL, @.@.rowcount = 1
> 1 rows -> @.mycol is assigned the value, @.@.rowcount = 1
> many rows -> You will get an error, "subquery returned more than one
> value".
>
> 0 rows -> @.mycol unchanged(!), @.@.rowcount = 0
> 1 row -> @.mycol assigned the value, @.@.rowcount = 1
> many rows -> @.mycol gets the last value in the result set, which that
> is undefined unless you have an ORDER BY. @.@.rowcount is
> set to the number of matching rows.
> Look at 0 rows again:
> SELECT @.mycol = 4711
> SELECT @.mycol = Col FROM myTable WHERE id = 1
> If there is no row with id = 1, @.mycol will remain 4711, it will not be
> set to NULL.
> If you really want to check for duplicates, and handle the situation
> yourself, it is the SELECT assignment you want to use.
> If you keep the constraints, which you should unless you have very good
> reasons, the SET method is a little safer. Then again, if you know how
> SELECT behaves you can be careful make sure variable is NULL before
> you use it. (Yet then again, that is a trap that even season T-SQL
> programmers fall into, every now and then!)
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp
>|||Thank you for the SET/SELECT behavior. Your reply seemed to imply (reading
in between the lines) that since the the id is UNIQUE don't bother to check
for multiple rows, SQL will error anyways in the unlikely event.
If there are duplicates in a unique column, then there is data corruption
anyways, pretty messages aren't really going to help the application faling,
it is time to contact the DBA to see why the data is corrupt.
Is that right?
I'm going nuts creating a procedure that checks for both bad and/or
duplicate data being passed into it and handling for corrupt database
information that should not happen. Seems like opening pandora's box when I
try to code pretty returns to notify the application that the database is
corrupt.
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns96D61468ABF3Yazorman@.127.0.0.1...
> DazedAndConfused (AceMagoo61@.yahoo.com) writes:
> OK, now we are in for a real treat! I will show you how to do it, and if
> that does not convince you that are going too far, nothing will. :-)
> Andy showed you two ways, but they are a little different, which he failed
> to tell. Let's look at them again:
>
> 0 rows -> @.mycol is assigned NULL, @.@.rowcount = 1
> 1 rows -> @.mycol is assigned the value, @.@.rowcount = 1
> many rows -> You will get an error, "subquery returned more than one
> value".
>
> 0 rows -> @.mycol unchanged(!), @.@.rowcount = 0
> 1 row -> @.mycol assigned the value, @.@.rowcount = 1
> many rows -> @.mycol gets the last value in the result set, which that
> is undefined unless you have an ORDER BY. @.@.rowcount is
> set to the number of matching rows.
> Look at 0 rows again:
> SELECT @.mycol = 4711
> SELECT @.mycol = Col FROM myTable WHERE id = 1
> If there is no row with id = 1, @.mycol will remain 4711, it will not be
> set to NULL.
> If you really want to check for duplicates, and handle the situation
> yourself, it is the SELECT assignment you want to use.
> If you keep the constraints, which you should unless you have very good
> reasons, the SET method is a little safer. Then again, if you know how
> SELECT behaves you can be careful make sure variable is NULL before
> you use it. (Yet then again, that is a trap that even season T-SQL
> programmers fall into, every now and then!)
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp
>
with a select that assigns to a variable you will get a rowcount of one even
though no actual row was found. I am guessing that is because of assigning
to a variable SQL will return an empty result set, which is a result set of
1, is this correct?
i.e. SET @.mycol =
(SELECT col FROM myTable WHERE id = 1)
Under these circumstances is the best technique to just test the variable
@.mycol for a null value
OR write the select like:
IF EXISTS
(SELECT mycol FROM myTable
WHERE myID = 3)
BEGIN
SET @.mycol =
(SELECT mycol FROM myTable
WHERE myID = 3)
PRINT '@.mycol : ' + CAST(mycol as varchar(15))
END
ELSE
PRINT 'ROW DOES NOT EXIST'
Or is there an even better and/or more professional way to do it?Thank you! I was making my self nuts with the what if's, it seemed that
going beyond validating the current entry could turn into a never ending
task. :)
Except for the news groups I am learning in a vacuum, it is not like
learning in maintenance of a production environment where you get to see
what others have done.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eb6rNDKvFHA.3896@.TK2MSFTNGP15.phx.gbl...
> Thanks Erland, I really should have mentioned that.
> Dazed,
> If you have a Unique constraint you should not have to test for
> duplicates when selecting the values out. The constraint will make sure
> there are no duplicates in the first place.
> --
> Andrew J. Kelly SQL MVP
>
> "Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
> news:Xns96D61468ABF3Yazorman@.127.0.0.1...
>|||What is the desired behavior? DO you simply want to know if one or more
rows exist or not? If so then always use EXISTS.
Andrew J. Kelly SQL MVP
"DazedAndConfused" <AceMagoo61@.yahoo.com> wrote in message
news:eXq$4VHvFHA.2072@.TK2MSFTNGP14.phx.gbl...
> With a plain select if no rows are returned then the row count is 0, but
> with a select that assigns to a variable you will get a rowcount of one
> even though no actual row was found. I am guessing that is because of
> assigning to a variable SQL will return an empty result set, which is a
> result set of 1, is this correct?
> i.e. SET @.mycol =
> (SELECT col FROM myTable WHERE id = 1)
> Under these circumstances is the best technique to just test the variable
> @.mycol for a null value
> OR write the select like:
> IF EXISTS
> (SELECT mycol FROM myTable
> WHERE myID = 3)
> BEGIN
> SET @.mycol =
> (SELECT mycol FROM myTable
> WHERE myID = 3)
> PRINT '@.mycol : ' + CAST(mycol as varchar(15))
> END
> ELSE
> PRINT 'ROW DOES NOT EXIST'
> Or is there an even better and/or more professional way to do it?
>|||When you use a scalar subquery, you can get a one-row, one-column table
that is converted to a scalar; you can get an empty table that is
converted to a NULL; you can get a multi-row, one-column table that
gives a cardinality error when you try to put it into a scalar.|||In this paticular situation I want the field value. I was wondering if it
was better to just do the select and test the variable for a null or do a
EXISTS SELECT and then if it does exist select into the variable.
I'm just learning and trying to find the best way to build a mouse trap,
I've found this news group very helpful. i.e. I had a 130 line procedure
yesterday that was cut down to about 30 from information obtained from the
group. There are a lot of ways to get things to work, some are a lot better
than others. I'm looking for the right ways.
Thank you!!
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:ObA0rlHvFHA.2504@.TK2MSFTNGP15.phx.gbl...
> What is the desired behavior? DO you simply want to know if one or more
> rows exist or not? If so then always use EXISTS.
> --
> Andrew J. Kelly SQL MVP
>
> "DazedAndConfused" <AceMagoo61@.yahoo.com> wrote in message
> news:eXq$4VHvFHA.2072@.TK2MSFTNGP14.phx.gbl...
>|||OK then there is no need for an EXISTS first. You can do this several ways
like:
SET @.mycol = (SELECT col FROM myTable WHERE id = 1)
or
SELECT @.mycol = Col FROM myTable WHERE id = 1
IF @.myCol IS NOT NULL
BEGIN
-- Do your thing here
END
ELSE
...
Also make sure that there will only be at most 1 row returned. As long as
ID is a unique value you should be ok.
Andrew J. Kelly SQL MVP
"DazedAndConfused" <AceMagoo61@.yahoo.com> wrote in message
news:uQq5RzHvFHA.3548@.tk2msftngp13.phx.gbl...
> In this paticular situation I want the field value. I was wondering if it
> was better to just do the select and test the variable for a null or do a
> EXISTS SELECT and then if it does exist select into the variable.
> I'm just learning and trying to find the best way to build a mouse trap,
> I've found this news group very helpful. i.e. I had a 130 line procedure
> yesterday that was cut down to about 30 from information obtained from the
> group. There are a lot of ways to get things to work, some are a lot
> better than others. I'm looking for the right ways.
> Thank you!!
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:ObA0rlHvFHA.2504@.TK2MSFTNGP15.phx.gbl...
>|||Thank you again! id is unique, but I am checking if rowcount is > 1 anyways
and plan on throwing an error if it is. I plan on temporarily removing the
unique constraint and putting in a dupe record to test it.
Is that going too far?
Or is it a good idea to try to handle a situation that in theory should
never happen?
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OMkfpNIvFHA.3256@.TK2MSFTNGP09.phx.gbl...
> OK then there is no need for an EXISTS first. You can do this several
> ways like:
> SET @.mycol = (SELECT col FROM myTable WHERE id = 1)
> or
> SELECT @.mycol = Col FROM myTable WHERE id = 1
>
> IF @.myCol IS NOT NULL
> BEGIN
> -- Do your thing here
> END
> ELSE
> ...
> Also make sure that there will only be at most 1 row returned. As long as
> ID is a unique value you should be ok.
> --
> Andrew J. Kelly SQL MVP
>
> "DazedAndConfused" <AceMagoo61@.yahoo.com> wrote in message
> news:uQq5RzHvFHA.3548@.tk2msftngp13.phx.gbl...
>|||DazedAndConfused (AceMagoo61@.yahoo.com) writes:
> Thank you again! id is unique, but I am checking if rowcount is > 1
> anyways and plan on throwing an error if it is. I plan on temporarily
> removing the unique constraint and putting in a dupe record to test it.
> Is that going too far?
> Or is it a good idea to try to handle a situation that in theory should
> never happen?
OK, now we are in for a real treat! I will show you how to do it, and if
that does not convince you that are going too far, nothing will. :-)
Andy showed you two ways, but they are a little different, which he failed
to tell. Let's look at them again:
0 rows -> @.mycol is assigned NULL, @.@.rowcount = 1
1 rows -> @.mycol is assigned the value, @.@.rowcount = 1
many rows -> You will get an error, "subquery returned more than one value".
0 rows -> @.mycol unchanged(!), @.@.rowcount = 0
1 row -> @.mycol assigned the value, @.@.rowcount = 1
many rows -> @.mycol gets the last value in the result set, which that
is undefined unless you have an ORDER BY. @.@.rowcount is
set to the number of matching rows.
Look at 0 rows again:
SELECT @.mycol = 4711
SELECT @.mycol = Col FROM myTable WHERE id = 1
If there is no row with id = 1, @.mycol will remain 4711, it will not be
set to NULL.
If you really want to check for duplicates, and handle the situation
yourself, it is the SELECT assignment you want to use.
If you keep the constraints, which you should unless you have very good
reasons, the SET method is a little safer. Then again, if you know how
SELECT behaves you can be careful make sure variable is NULL before
you use it. (Yet then again, that is a trap that even season T-SQL
programmers fall into, every now and then!)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks Erland, I really should have mentioned that.
Dazed,
If you have a Unique constraint you should not have to test for
duplicates when selecting the values out. The constraint will make sure
there are no duplicates in the first place.
Andrew J. Kelly SQL MVP
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns96D61468ABF3Yazorman@.127.0.0.1...
> DazedAndConfused (AceMagoo61@.yahoo.com) writes:
> OK, now we are in for a real treat! I will show you how to do it, and if
> that does not convince you that are going too far, nothing will. :-)
> Andy showed you two ways, but they are a little different, which he failed
> to tell. Let's look at them again:
>
> 0 rows -> @.mycol is assigned NULL, @.@.rowcount = 1
> 1 rows -> @.mycol is assigned the value, @.@.rowcount = 1
> many rows -> You will get an error, "subquery returned more than one
> value".
>
> 0 rows -> @.mycol unchanged(!), @.@.rowcount = 0
> 1 row -> @.mycol assigned the value, @.@.rowcount = 1
> many rows -> @.mycol gets the last value in the result set, which that
> is undefined unless you have an ORDER BY. @.@.rowcount is
> set to the number of matching rows.
> Look at 0 rows again:
> SELECT @.mycol = 4711
> SELECT @.mycol = Col FROM myTable WHERE id = 1
> If there is no row with id = 1, @.mycol will remain 4711, it will not be
> set to NULL.
> If you really want to check for duplicates, and handle the situation
> yourself, it is the SELECT assignment you want to use.
> If you keep the constraints, which you should unless you have very good
> reasons, the SET method is a little safer. Then again, if you know how
> SELECT behaves you can be careful make sure variable is NULL before
> you use it. (Yet then again, that is a trap that even season T-SQL
> programmers fall into, every now and then!)
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp
>|||Thank you for the SET/SELECT behavior. Your reply seemed to imply (reading
in between the lines) that since the the id is UNIQUE don't bother to check
for multiple rows, SQL will error anyways in the unlikely event.
If there are duplicates in a unique column, then there is data corruption
anyways, pretty messages aren't really going to help the application faling,
it is time to contact the DBA to see why the data is corrupt.
Is that right?
I'm going nuts creating a procedure that checks for both bad and/or
duplicate data being passed into it and handling for corrupt database
information that should not happen. Seems like opening pandora's box when I
try to code pretty returns to notify the application that the database is
corrupt.
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns96D61468ABF3Yazorman@.127.0.0.1...
> DazedAndConfused (AceMagoo61@.yahoo.com) writes:
> OK, now we are in for a real treat! I will show you how to do it, and if
> that does not convince you that are going too far, nothing will. :-)
> Andy showed you two ways, but they are a little different, which he failed
> to tell. Let's look at them again:
>
> 0 rows -> @.mycol is assigned NULL, @.@.rowcount = 1
> 1 rows -> @.mycol is assigned the value, @.@.rowcount = 1
> many rows -> You will get an error, "subquery returned more than one
> value".
>
> 0 rows -> @.mycol unchanged(!), @.@.rowcount = 0
> 1 row -> @.mycol assigned the value, @.@.rowcount = 1
> many rows -> @.mycol gets the last value in the result set, which that
> is undefined unless you have an ORDER BY. @.@.rowcount is
> set to the number of matching rows.
> Look at 0 rows again:
> SELECT @.mycol = 4711
> SELECT @.mycol = Col FROM myTable WHERE id = 1
> If there is no row with id = 1, @.mycol will remain 4711, it will not be
> set to NULL.
> If you really want to check for duplicates, and handle the situation
> yourself, it is the SELECT assignment you want to use.
> If you keep the constraints, which you should unless you have very good
> reasons, the SET method is a little safer. Then again, if you know how
> SELECT behaves you can be careful make sure variable is NULL before
> you use it. (Yet then again, that is a trap that even season T-SQL
> programmers fall into, every now and then!)
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp
>
Subscribe to:
Posts (Atom)