is user dba

Is this statement correct :
In SQL Server 2000 the SQL that checks to see "Is User a DBA" is not valid.
One must use suser_sname instead of suser_name.
See example below:
select 1 where suser_sname()='sa'
go
And if so is there a patch available to correct this ?
Don
"don" <don@.discussions.microsoft.com> wrote in message
news:549733A3-F0B5-4CAD-819A-141BCAF05497@.microsoft.com...
> Is this statement correct :
> In SQL Server 2000 the SQL that checks to see "Is User a DBA" is not
> valid.
> One must use suser_sname instead of suser_name.
> See example below:
> select 1 where suser_sname()='sa'
> go
> And if so is there a patch available to correct this ?
>
Um.
From BOL: SUSER_NAME always returns NULL when used in Microsoft SQL
ServerT 2000. This system built-in function is included only for backward
compatibility. Use SUSER_SNAME instead.
But suser_sname() is not right either. Since many different logins may be
system administrators.
Try
select IS_SRVROLEMEMBER('sysadmin')
David

is user dba

Is this statement correct :
In SQL Server 2000 the SQL that checks to see "Is User a DBA" is not valid.
One must use suser_sname instead of suser_name.
See example below:
select 1 where suser_sname()='sa'
go
And if so is there a patch available to correct this ?
Don"don" <don@.discussions.microsoft.com> wrote in message
news:549733A3-F0B5-4CAD-819A-141BCAF05497@.microsoft.com...
> Is this statement correct :
> In SQL Server 2000 the SQL that checks to see "Is User a DBA" is not
> valid.
> One must use suser_sname instead of suser_name.
> See example below:
> select 1 where suser_sname()='sa'
> go
> And if so is there a patch available to correct this ?
>
Um.
From BOL: SUSER_NAME always returns NULL when used in Microsoft SQL
ServerT 2000. This system built-in function is included only for backward
compatibility. Use SUSER_SNAME instead.
But suser_sname() is not right either. Since many different logins may be
system administrators.
Try
select IS_SRVROLEMEMBER('sysadmin')
David

Is this correct?

hi

if row.col1 = nothing then ...

instead of (sql2k) if dtssource("col1") = null then...

TIA

No!

If you type:

If Row.

then an intellisense box will pop up. In there you will see a function called col1_IsNull()

That function returns a boolean indicating whether or not the field is empty.

-Jamie

|||Thanks Jamie.

Is this correct use of INSTEAD OF Triggers?

I am loading data from table A into table B. Certain columns in B have
check constraints. I'd like for any rows from A, which violate
constraints, to be inserted into a third table, C. When the process is
finished, I'll have only good rows in B, and exeption rows in C.

I am investigating INSTEAD OF triggers, however my question to the
group is, is there a better or best practice for this scenario? This
must be common. Any high-level tips or direction will be highly
appreciated.

DAP>> loading data from table A into table B. Certain columns in B have
check constraints. I'd like for any rows from A, which violate
constraints, to be inserted into a third table, C. <<

You might want to use a cursor that attempts to insert each A row into
B and throws the exceptions into C. This would give you better control
and perhaps a chance to fix the bad rows with (ugh!) procedural code.

A moire set-oriented approach woudl be to create a VIEW on A whch has
the B constraints:

CREATE VIEW GoodA
AS SELECT *
FROM A
WHERE << B's constraints as predicates>> ;

You are probably thinking that the next step is to use:

CREATE VIEW BadA
AS SELECT *
FROM A
WHERE NOT (<< B's constraints as predicates>>);

But this does not work. A CHECK() constraint will accept an UNKNOWN
result from its predicate; a WHERE clause will reject them. You will
have to write a little extra code in each predicate to handle NULLs.

example:

CREATE TABLE B
( ..
foo INTEGER CHECK ( foo >= 0), -- works for null
..);

SELECT *
FROM A
WHERE ( foo >= 0 OR foo IS NULL);|||Hi

You can try something like:

INSERT INTO Table C
SELECT col1, col2, col3 FROM TABLE A
WHERE <CLAUSE TO TEST CONSTRAINT FAIL
INSERT INTO Table B
SELECT col1, col2, col3 FROM TABLE A
WHERE <CLAUSE TO TEST CONSTRAINTS PASS
OR
INSERT INTO Table B
SELECT col1, col2, col3 FROM TABLE A
WHERE NOT EXISTS ( SELECT * FROM TABLE C WHERE <CLAUSE TO CHECK NOT IN C>)

John

"Dan" <dpratte@.dpratte.com> wrote in message
news:1115474335.494377.167790@.o13g2000cwo.googlegr oups.com...
>I am loading data from table A into table B. Certain columns in B have
> check constraints. I'd like for any rows from A, which violate
> constraints, to be inserted into a third table, C. When the process is
> finished, I'll have only good rows in B, and exeption rows in C.
> I am investigating INSTEAD OF triggers, however my question to the
> group is, is there a better or best practice for this scenario? This
> must be common. Any high-level tips or direction will be highly
> appreciated.
> DAP|||Dan (dpratte@.dpratte.com) writes:
> I am loading data from table A into table B. Certain columns in B have
> check constraints. I'd like for any rows from A, which violate
> constraints, to be inserted into a third table, C. When the process is
> finished, I'll have only good rows in B, and exeption rows in C.
> I am investigating INSTEAD OF triggers, however my question to the
> group is, is there a better or best practice for this scenario? This
> must be common.

Not really.

I think the only way to do this without duplicating the constraints is
run a cursor one-by-one as suggested by Celko. An improvement could be
to first attempt to insert all, and if there is an error, use the
cursor as a fallback. But you could not do this in an INSTEAD OF
trigger, because an error in a trigger aborts the batch. You see,
the whole idea is that the INSERT statement should be atomic, either
all rows make it, or others not.

An alternative would be move the constraints to the trigger and check
for them there. An INSTEAD OF trigger would then redo the original
INSERT statement for the good rows. An AFTER trigger would delete
the bad rows.

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

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

I missed the title to this! Rather than use a trigger I would put the logic
into a stored procedure.

John

"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:427d0150$0$1878$da0feed9@.news.zen.co.uk...
> Hi
> You can try something like:
> INSERT INTO Table C
> SELECT col1, col2, col3 FROM TABLE A
> WHERE <CLAUSE TO TEST CONSTRAINT FAIL>
> INSERT INTO Table B
> SELECT col1, col2, col3 FROM TABLE A
> WHERE <CLAUSE TO TEST CONSTRAINTS PASS>
> OR
> INSERT INTO Table B
> SELECT col1, col2, col3 FROM TABLE A
> WHERE NOT EXISTS ( SELECT * FROM TABLE C WHERE <CLAUSE TO CHECK NOT IN C>)
> John
> "Dan" <dpratte@.dpratte.com> wrote in message
> news:1115474335.494377.167790@.o13g2000cwo.googlegr oups.com...
>>I am loading data from table A into table B. Certain columns in B have
>> check constraints. I'd like for any rows from A, which violate
>> constraints, to be inserted into a third table, C. When the process is
>> finished, I'll have only good rows in B, and exeption rows in C.
>>
>> I am investigating INSTEAD OF triggers, however my question to the
>> group is, is there a better or best practice for this scenario? This
>> must be common. Any high-level tips or direction will be highly
>> appreciated.
>>
>> DAP
>>

Is there performance penalty for returning resultsets instead of rows

> Does anyone know if there's significant overhead compared to single result
> set? Pages won't be larger than 30-40 rows so maybe that's not important
> at all?
I wasn't sure about the overhead so created the following procs.
CREATE PROC Proc1 AS
SELECT * FROM
(SELECT 1 AS Test
UNION ALL SELECT 2
UNION ALL SELECT 3
<snip>
UNION ALL SELECT 40) As Test
GO
CREATE PROC Proc2 AS
SELECT 1
SELECT 2
SELECT 3
<snip>
SELECT 40
GO
I then ran a test of 10,000 iterations with the code below. The app and SQL
were on the same machine:
private void AdHocTest()
{
_connection.Open();
SqlCommand command = new SqlCommand();
command.CommandType = CommandType.StoredProcedure;
command.Connection = _connection;
command.CommandText = "dbo.Proc1";
RunTest(connection, command, 10000);
command.CommandText = "dbo.Proc2";
RunTest(connection, command, 10000);
_connection.Close();
}
private void RunTest(SqlConnection connection,
SqlCommand command,
int iterations)
{
DateTime startTime = DateTime.Now;
for (int i = 0; i < iterations; ++i)
{
//execute command and consume all results
SqlDataReader reader = command.ExecuteReader();
do
{
while (reader.Read()) ;
}
while (reader.NextResult());
reader.Close();
}
System.Diagnostics.Trace.WriteLine(
string.Format("Test {0} duration is {1}",
command.CommandText,
DateTime.Now.Subtract(startTime).ToString()));
}
The single result method was 1 second for all 10,000 iterations and the
multiple result method was about 2 seconds. I would expect an even more
pronounced difference with SQL on a separate box.
Of course, this test didn't include the overhead of the server cursor or
client processing. In your actual application, the performance difference
probably won't matter unless you have a lot of users.
Note that you might run into issues with a single 'generic' paging solution.
There are many different pagination techniques and no single one is best for
all situations.
Hope this helps.
Dan Guzman
SQL Server MVP
"Dejan Grujic" <dejan.grujic@.REMOVE.cogin.com.NO_SPAM> wrote in message
news:OKkF9y3aGHA.4520@.TK2MSFTNGP03.phx.gbl...
> I'm using server cursor for generic paging.
> Interesting part is this:
> FETCH RELATIVE @.StartRow FROM cur
> WHILE @.PageSize > 1 AND @.@.FETCH_STATUS = 0
> BEGIN
> FETCH NEXT FROM cur
> SET @.PageSize = @.PageSize - 1
> END
> Instead of single result set with N rows, this returns N result sets with
> 1 row. I can handle that in my client, that's not an issue.
> Does anyone know if there's significant overhead compared to single result
> set? Pages won't be larger than 30-40 rows so maybe that's not important
> at all?
> Thanks,
> Dejan> Since I'm making admin utility I also thought to show row count for each
> table in a database but I'm not sure about that now.
If a count of the number of rows is not needed for your paging technique,
you can still provide the user with separate 'get count' button. We used
that technique in one of our apps because we had tables with hundreds of
millions of rows and querying was ad-hoc. Users could still navigate with
'next' and 'prev' button as well as jump to specific pages.
Note that SELECT COUNT(*) will use the narrowest useful index so index
tuning can help performance. Also, @.@.CURSOR_ROWS is an option if you are
using a cursor.
Hope this helps.
Dan Guzman
SQL Server MVP
"Dejan Grujic" <dejan.grujic@.REMOVE.cogin.com.NO_SPAM> wrote in message
news:uZjrlKHbGHA.4972@.TK2MSFTNGP03.phx.gbl...
> Thanks Dan for these numbers. In the mean time I performed some tests of
> my own. To my surprise, I found out that main problem is not with cursors
> and result sets, but with SELECT COUNT(*)!
> For 50k rows and when page is at the beginning of table COUNT takes about
> 10x more time than fetching 30 full rows!
> I need that count, to display exact number of pages to user.
> Since I'm making admin utility I also thought to show row count for each
> table in a database but I'm not sure about that now.
> Dejan|||I forgot to add that you can get an accurate table rowcount in SQL Server
2005 with:
SELECT rowcnt
FROM sysindexes
WHERE id = OBJECT_ID('dbo.MyTable)
AND indid IN(0,1)
The returned value is an approximation in SQL 2000 and might not be
accurate.
Hope this helps.
Dan Guzman
SQL Server MVP
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:%23874LaHbGHA.3812@.TK2MSFTNGP04.phx.gbl...
> If a count of the number of rows is not needed for your paging technique,
> you can still provide the user with separate 'get count' button. We used
> that technique in one of our apps because we had tables with hundreds of
> millions of rows and querying was ad-hoc. Users could still navigate with
> 'next' and 'prev' button as well as jump to specific pages.
> Note that SELECT COUNT(*) will use the narrowest useful index so index
> tuning can help performance. Also, @.@.CURSOR_ROWS is an option if you are
> using a cursor.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Dejan Grujic" <dejan.grujic@.REMOVE.cogin.com.NO_SPAM> wrote in message
> news:uZjrlKHbGHA.4972@.TK2MSFTNGP03.phx.gbl...
>

Is there performance penalty for returning resultsets instead

Thanks Dan for these numbers. In the mean time I performed some tests of
my own. To my surprise, I found out that main problem is not with
cursors and result sets, but with SELECT COUNT(*)!
For 50k rows and when page is at the beginning of table COUNT takes
about 10x more time than fetching 30 full rows!
I need that count, to display exact number of pages to user.
Since I'm making admin utility I also thought to show row count for each
table in a database but I'm not sure about that now.
DejanThanks Dan,
I'll probably use sysindexes for final solution. @.@.CURSOR_ROWS won't
work for me as I'm using DYNAMIC cursor, which always returns -1.
Dejan

Is there any way to blank out certain columns in a single sele

The users doesn't want the data for these 4 columns to print out unless the
formID is one of the ones selected. I can use "" instead. The dataset is
sent to Crystal Report to print out so it's better using "". I think Crysta
l
prints out the word "NULL" if they're set to NULL.
Thanks.
"Raymond D'Anjou" wrote:

> Add a Case for each of these columns.
> Example:
> ...CASE when b.formID in ('2', '16', '11', '12', '1', '13', '10') then NU
LL
> else b.form end as form,...
> "Alpha" <Alpha@.discussions.microsoft.com> wrote in message
> news:9BD3F043-072D-4598-B2BB-BA63529EA142@.microsoft.com...
>
>I don't know anything about Crystal report.
You can use '' for Text datatypes but the numerics and dates may not give
you what you want.
You may have to do a bit of CASTing for these types.
If the users can only choose 1 formID, just build your query differently in
C#.
if formid in ('2', '16', '11', '12', '1', '13', '10') then
select t.tid, t.tdate, t.trip_ticket,t.source,'' AS form, '' AS
formdate, '' AS printed,...
else
select t.tid,t.tdate,t.trip_ticket,t.source,b.form, b.formdate,
b.printed,
"Alpha" <Alpha@.discussions.microsoft.com> wrote in message
news:D27DCE8F-4B22-41C1-BDDF-A344467FCD63@.microsoft.com...
> The users doesn't want the data for these 4 columns to print out unless
> the
> formID is one of the ones selected. I can use "" instead. The dataset is
> sent to Crystal Report to print out so it's better using "". I think
> Crystal
> prints out the word "NULL" if they're set to NULL.
> Thanks.
> "Raymond D'Anjou" wrote:
>|||Hi,Thank you for the reply. I think the system got messed up here. The
"NULL" answer was address to Mesa's question.
My question for you is: Do I just append the "Case..." to the end of my
select
statement?
"Alpha" wrote:
> The users doesn't want the data for these 4 columns to print out unless th
e
> formID is one of the ones selected. I can use "" instead. The dataset is
> sent to Crystal Report to print out so it's better using "". I think Crys
tal
> prints out the word "NULL" if they're set to NULL.
> Thanks.
> "Raymond D'Anjou" wrote:
>|||No, the Case replaces the column name in your Select statement.
So, instead of:
select b.form,...
select CASE when b.formID in ('2', '16', '11', '12', '1', '13', '10') then
NULL else b.form end as form,
"Alpha" <Alpha@.discussions.microsoft.com> wrote in message
news:ED00B94C-E471-4508-9F1C-D8E2803E47EC@.microsoft.com...
> Hi,Thank you for the reply. I think the system got messed up here. The
> "NULL" answer was address to Mesa's question.
> My question for you is: Do I just append the "Case..." to the end of my
> select
> statement?
>
> "Alpha" wrote:
>

Is there any advantages of using views in the same DB instead of tables in a differen

I've two databases. PROD & ARCHIVE
I need to archive data from production to archive database.
Right now I am using stored procedures to move from prod to archive.
If I use views in PROD which will reflect the ARCHIVE database tables, Is
there any advantages on connection?
Is "insert ...from archive.dbo.tableA select ....from prod.dbo.tableA" is
having connection over head over the views?
Please advice...Thanks...ShafNo overhead. The views would be only for typing convenience. If on 2005, syn
onyms would probably be
better if you just don't want to type in the database name.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Shaf.Khalidh" <itsprobablyme@.msn.com> wrote in message
news:uVclgt$GGHA.2440@.TK2MSFTNGP14.phx.gbl...
> I've two databases. PROD & ARCHIVE
> I need to archive data from production to archive database.
> Right now I am using stored procedures to move from prod to archive.
> If I use views in PROD which will reflect the ARCHIVE database tables, Is
there any advantages on
> connection?
> Is "insert ...from archive.dbo.tableA select ....from prod.dbo.tableA" i
s having connection over
> head over the views?
> Please advice...Thanks...Shaf
>|||Thanks Tibor,
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23AvomFAHGHA.240@.TK2MSFTNGP11.phx.gbl...
> No overhead. The views would be only for typing convenience. If on 2005,
> synonyms would probably be better if you just don't want to type in the
> database name.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Shaf.Khalidh" <itsprobablyme@.msn.com> wrote in message
> news:uVclgt$GGHA.2440@.TK2MSFTNGP14.phx.gbl...
>