MS SQL server Insert Error [109]

Greetings!

When I run the following SQL statement in Perl, I get an error
stating:
any help/pointers how this can be resolved?

Thanks,
-Murali

SQL note_insert error: [109] [2] [0] "[Microsoft][ODBC SQL Server
Driver][SQL Server]There are more columns in the INSERT statement than
values specified in the VALUES clause. The number of values in the
VALUES clause must match the number of columns specified in the INSERT
statement."

The perl code:

$sql_stmt1 = "select cast(newid () as varbinary(16)) as notes_id
from cc_test";

if($db_ends->Sql($sql_stmt1))
{
$db_ends->Error();
exit(-1);
}

while($db_ends->FetchRow())
{
undef %Data;
%Data = $db_ends->DataHash();
$notes_id = $Data{"notes_id"};
}

# Prepare the columns for insert

my$updated_detail = "$pn . $cc_ver\n";

my $note_columns = "user_note_id, bio_name, related_tbl_name,
related_string_id, related_int_id, note_type_lkp, notes,
internal_flag, revision_number, obsolete_flag";

my $note_values = "cast(" +$notes_Id + "as binary(16)), Null, Null,
Null, $bfn, 0x74942E5A0A04022800415DE63529D4B7, $notes_detail, 0, 0,
0";

$sql_note_insert = "insert into user_note ($note_columns) values
($note_values)";

if($db_ends->Sql($sql_note_insert))Make sure you delimit all strings with single quotes ('). If one of the
current variables (such as $note_details) contains a comma, then you
will have more "values" than "columns".

If this doesn't work, then please post the actual query you are
submitting to SQL-Server.

Hope this helps,
Gert-Jan

Murali Kanaga wrote:
> Greetings!
> When I run the following SQL statement in Perl, I get an error
> stating:
> any help/pointers how this can be resolved?
> Thanks,
> -Murali
> SQL note_insert error: [109] [2] [0] "[Microsoft][ODBC SQL Server
> Driver][SQL Server]There are more columns in the INSERT statement than
> values specified in the VALUES clause. The number of values in the
> VALUES clause must match the number of columns specified in the INSERT
> statement."
> The perl code:
> $sql_stmt1 = "select cast(newid () as varbinary(16)) as notes_id
> from cc_test";
> if($db_ends->Sql($sql_stmt1))
> {
> $db_ends->Error();
> exit(-1);
> }
> while($db_ends->FetchRow())
> {
> undef %Data;
> %Data = $db_ends->DataHash();
> $notes_id = $Data{"notes_id"};
> }
> # Prepare the columns for insert
> my $updated_detail = "$pn . $cc_ver\n";
> my $note_columns = "user_note_id, bio_name, related_tbl_name,
> related_string_id, related_int_id, note_type_lkp, notes,
> internal_flag, revision_number, obsolete_flag";
> my $note_values = "cast(" +$notes_Id + "as binary(16)), Null, Null,
> Null, $bfn, 0x74942E5A0A04022800415DE63529D4B7, $notes_detail, 0, 0,
> 0";
> $sql_note_insert = "insert into user_note ($note_columns) values
> ($note_values)";
> if($db_ends->Sql($sql_note_insert))sql

MS SQL Server 2000 JDBC Driver setDate() bug report

Hi,

Recently, I found a bug in Microsoft's official SQL Server2000 JDBC driver.
It seemed to lie in the Statement's setDate() method. Maybe they simply assumed
all date are represented in 12-hours format.
The test code is below:


public void testMisc() throws Exception {
Connection conn = TransactionManager.getConnection();
String sql = "select count(salescheck0_.ID) as x0_0_ " +
"from T_CHK_OUT_BILL_TEST salescheck0_ " +
"where (salescheck0_.EX_TIME>?)" +
"and(salescheck0_.EX_TIME<?)";
PreparedStatement stmt = conn.prepareCall(sql);
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
Date timefrom = sdf.parse("2004-04-02 00:00:00.000");
Date timeto = sdf.parse("2004-04-02 23:00:00.000");

stmt.setDate(1, new java.sql.Date(timefrom.getTime()));
stmt.setDate(2, new java.sql.Date(timeto.getTime()));
// stmt.setString(1, "2004-04-02 00:00:00.000" );
// stmt.setString(2, "2004-04-02 23:59:59.000" );
ResultSet rs = stmt.executeQuery();
if(rs.next()) {
int count = rs.getInt(1);
System.out.println("count = " + count);
}
}

The test data is following:
ID, SO_ID,WH_ID,CLIENT_ID,CODE,OPERATOR,CHK_OUT_TIME, STATUS,CREATE_TIME,DELIVER_TYPE
86179,67521,1,661,SLCCK200404020016,xuwei,2004-4-2 12:15,20,2004-4-2 9:40,10
86229,67566,1,118,SLCCK200404020066,xuwei,2004-4-2 12:23,20,2004-4-2 9:41,10
86231,67568,1,130,SLCCK200404020068,xuwei,2004-4-2 12:23,20,2004-4-2 9:41,10
86232,67569,1,134,SLCCK200404020069,xuyili,2004-4-2 11:08,20,2004-4-2 9:41,10
86233,67571,1,151,SLCCK200404020070,xuwei,2004-4-2 12:23,20,2004-4-2 9:41,10
86234,67572,1,55,SLCCK200404020071,xuwei,2004-4-2 12:23,20,2004-4-2 9:41,10
86235,67573,1,71,SLCCK200404020072,xuwei,2004-4-2 12:23,20,2004-4-2 9:41,10
86252,67590,1,298,SLCCK200404020089,xuwei,2004-4-2 12:33,20,2004-4-2 9:41,10
86268,67576,1,25,SLCCK200404020105,xuwei,2004-4-2 12:23,20,2004-4-2 10:18,10
86269,67588,1,271,SLCCK200404020106,xuwei,2004-4-2 12:33,20,2004-4-2 10:20,10
86270,67506,1,42,SLCCK200404020107,xuwei,2004-4-2 12:15,20,2004-4-2 10:21,10
86271,67531,1,751,SLCCK200404020108,xuyili,2004-4-2 11:28,20,2004-4-2 10:22,10
86272,67570,1,149,SLCCK200404020109,xuwei,2004-4-2 12:23,20,2004-4-2 10:29,10
86273,67600,1,6,SLCCK200404020110,xuwei,2004-4-2 12:15,20,2004-4-2 10:40,10
86373,67665,3,686,SLCCK200404020210,root,2004-4-2 17:18,20,2004-4-2 16:32,10
86374,67657,3,883,SLCCK200404020211,root,2004-4-2 17:18,20,2004-4-2 16:32,10
86375,67666,3,686,SLCCK200404020212,root,2004-4-2 17:18,20,2004-4-2 16:32,10
86376,54799,1,395,SLCCK200404020213,zhouliyi,2004-4-2 19:52,20,2004-4-2 19:51,10
86377,55383,1,341,SLCCK200404020214,zhoulingyi,200 4-4-2 19:52,20,2004-4-2 19:51,10

Given the code and data, the expected output ought to be "count = 19"
But it's "count = 19" instead.
However, if you set date by using alternative method like the commented statements,
you can get the correct result.


Does anybody know if there exists any patch?
Could we expect Mircosoft to fix this bug? If so, where could I submit this bug report?

Regards,
JustinWhich version of the Microsoft JDBC Driver are you using?|||Originally posted by peterlemonjello
Which version of the Microsoft JDBC Driver are you using?

Don't know very clearly.
It may be rather old.
We have used it for about 2 years.

I notice there is a SP2 of MS SQL Server 2000 JDBC driver.
I'll try it immediately, and feedback ASAP.

Regards,
Justin|||Given the code and data, the expected output ought to be "count = 19"
But it's "count = 19" instead.
So what's wrong with that?sql

MS SQL Query statement.

Hi All,
Problem statement:
Group 2 tables.
B(B_ID,B_DES)
C(C_ID,B_ID,C_BY)
select B.*,C.*
from b bx inner join cx on bx.b_id=cx.b_id
group by B column,,C column
Output:
B_ID column | C_BY
1 we
1 xy
2 DF
Above result is not what i desired, instead, i needs:
1 we
xy
2 DF
How to solve it'
thank you in advance..Help will be appreciated.
Best regards,
Gin Lye KhorRepresent the results in a report engine using Group & Detail bands?
"Daniel" <Daniel@.discussions.microsoft.com> wrote in message
news:9B5D30B5-ADFB-404D-97D1-5CC5DB252BC4@.microsoft.com...
> Hi All,
> Problem statement:
> Group 2 tables.
> B(B_ID,B_DES)
> C(C_ID,B_ID,C_BY)
> select B.*,C.*
> from b bx inner join cx on bx.b_id=cx.b_id
> group by B column,,C column
> Output:
> B_ID column | C_BY
> 1 we
> 1 xy
> 2 DF
>
> Above result is not what i desired, instead, i needs:
> 1 we
> xy
> 2 DF
> How to solve it'
> thank you in advance..Help will be appreciated.
> Best regards,
> Gin Lye Khor
>
>

MS SQL Print Statement

i would like to enquire about the Print statement. If I do not want to use the Raiseerror to raise an error as its not an error, but want to just print out in a dialog box, how do i do it?

I tried using the Print statement but nothing happened. I am using this inside my trigger, can someone help?

Thank You

Hello my friend,

You can have a table called tblError with one field called ErrorDescription and then instead of using the PRINT statement, do INSERT INTO tblError (ErrorDescription) VALUES ('Part 2 has failed').

Kind regards

Scotty

MS SQL Joins

Hi Folks,
I writing a store procedure, the first three parts work pretty well. The last select statement has about 8 outer joins in it. every time I run the store procedure, I get an error message for the last part. Below are the error message and the store procedure:

Store Procedure:

--Create Procedure dbo.IMS_Donation

--AS

Select Distinct D_VST_ID as 'DRWLOC_ID', D_VST_INSTID as 'DRWLOC_INSTID'
Into Donor_Visit1
From DNR_VST_DB_REC
Where D_VST_DATE Between 20010101 AND 20040512
AND D_VST_DONTYP in ('AP', 'WB', 'RP', 'E2', 'E1')
AND D_VST_STATUS = 'DN'
ORDER BY D_VST_ID
GO

SELECT DRWLOC_ID as 'COUNT_ID', DRWLOC_INSTID as 'COUNT_INSTID',
count(*) as 'COUNT_VISITS'
INTO Donor_Visit2
FROM DNR_VST_DB_REC, Donor_Visit1
Where D_VST_ID = DRWLOC_ID
AND NOT EXISTS (Select R_DCC_ID
From REC_DCC_DB_REC
Where R_DCC_ID = DRWLOC_ID
AND R_DCC_INSTID = DRWLOC_INSTID
AND R_DCC_CALLCD = 'DC')
GROUP BY DRWLOC_ID, DRWLOC_INSTID
GO

SELECT DVT1.DRWLOC_ID as'COMP_ID', CMP.l_CMP_UNITNO as 'COMP_UNITID',
CMP.L_CMP_INSTID as 'COMP_INSTID', count(*) as 'COMP_COMPTOT'
INTO Donor_Visit3
FROM LAB_CMP_DB_REC CMP, Donor_Visit1 DVT1, DNR_VST_DB_REC VST, CMP_VST_Jct CVT
WHERE CMP.L_CMP_INSTID = DVT1.DRWLOC_INSTID
AND VST.D_VST_ID = DVT1.DRWLOC_ID
AND VST.D_VST_UNITNO = CVT.L_CMP_UNITNO
AND CMP.L_CMP_UNITNO = CVT.L_CMP_UNITNO
AND CMP.L_CMP_STATCD != 'MOD'
AND CMP.L_CMP_CMPCD NOT IN ('INC', 'EMTY')
AND VST.D_VST_DATE BETWEEN 20010101 AND 20040512
AND VST.D_VST_STATUS = 'DN'
GROUP BY DVT1.DRWLOC_ID, CMP.L_CMP_UNITNO, CMP.L_CMP_INSTID

GO

SELECT DISTINCT
NAM.N_NAM_ID AS 'ID1',
NAM.N_NAM_INSTID AS 'INSTID1',
NAM.N_NAM_FNAME AS 'FNAME1',
NAM.N_NAM_MINITIAL AS 'MINITIAL1',
NAM.N_NAM_LNAME AS 'LNAME1',
PER.N_PER_BIRTH AS 'BIRTH1',
ADR.N_ADR_ADDR1 AS 'ADDR1',
ADR.N_ADR_ADDR2 AS 'ADDR2',
ADR.N_ADR_CITY AS 'CITY1',
ADR.N_ADR_STATE AS 'STATE1',
SUBSTRING(ADR.N_ADR_ZIP, 1,5) AS 'ZIP1',
PER.N_PER_EMAIL AS 'EMAIL1',
PER.N_PER_GENDER AS 'GENDER1',
PHNA.N_PHN_AREACD AS 'AREAD1',
PHNA.N_PHN_PREFIX AS 'PREFIXD1',
PHNA.N_PHN_NUMBER AS 'NBRD1',
PHNA.N_PHN_EXTENTN AS 'EXTD1',
PHNB.N_PHN_AREACD AS 'AREAD2',
PHNB.N_PHN_PREFIX AS 'PREFIXD2',
PHNB.N_PHN_NUMBER AS 'NBRE2',
PHNB.N_PHN_EXTENTN AS 'EXTD2',
BTY.D_BTY_ABO AS 'ABO1',
BTY.D_BTY_RHESUS AS 'RHI',
VST.D_VST_DATE AS 'FIRST1',
DV2.COUNT_VISITS AS 'COUNT',
SUM(DTS.D_DTS_DONSUM) AS 'AWARD',
ELG.D_ELG_RWBDTE AS 'ELIG1'
--INTO Donor_Visit4
From Donor_Visit2 DV2
RIGHT OUTER JOIN DNR_DTS_DB_REC DTS
ON DV2.COUNT_INSTID = DTS.D_DTS_INSTID
RIGHT OUTER JOIN NAT_PER_DB_REC PER
ON DV2.COUNT_INSTID = PER.N_PER_INSTID
RIGHT OUTER JOIN DNR_BTY_DB_REC BTY
ON DV2.COUNT_INSTID = BTY.D_BTY_INSTID
RIGHT OUTER JOIN NAT_PHN_DB_REC PHNA
ON DV2.COUNT_INSTID = PHNA.N_PHN_INSTID
RIGHT OUTER JOIN NAT_PHN_DB_REC PHNB
ON DV2.COUNT_INSTID = PHNB.N_PHN_INSTID
RIGHT OUTER JOIN DNR_DTS_DB_REC DNT
ON DV2.COUNT_ID = DNT.D_DTS_ID
RIGHT OUTER JOIN NAT_PER_DB_REC PER1
ON DV2.COUNT_ID = PER1.N_PER_ID
RIGHT OUTER JOIN DNR_BTY_DB_REC BTY1
ON DV2.COUNT_ID = BTY1.D_BTY_ID
LEFT OUTER JOIN NAT_PHN_DB_REC PHNA1
ON DV2.COUNT_ID = PHNA1.N_PHN_ID
RIGHT OUTER JOIN NAT_PHN_DB_REC PHNB1
ON DV2.COUNT_ID = PHNB1.N_PHN_ID
LEFT OUTER JOIN NAT_PHN_DB_REC PHNA2
ON PHNA2.N_PHN_PHTYP = 'D'
LEFT OUTER JOIN NAT_PHN_DB_REC PHNB2
ON PHNB2.N_PHN_PHTYP = 'E',
--LEFT OUTER JOIN DNR_DTS_DB_REC DTS1
--DTS1.D_DTS_CNTTYP <> 'N',
DNR_ELG_DB_REC ELG, NAT_NAM_DB_REC NAM, NAT_ADR_DB_REC ADR, DNR_VST_DB_REC VST
WHERE DV2.COUNT_INSTID = VST.D_VST_INSTID
AND DV2.COUNT_INSTID = ELG.D_ELG_INSTID
AND DV2.COUNT_INSTID = N_NAM_INSTID
AND DV2.COUNT_INSTID = N_ADR_INSTID
AND DV2.COUNT_INSTID = VST.D_VST_INSTID
--AND DV2.COUNT_INSTID = ELG.D_ELG_ID
AND NAM.N_NAM_SEQNO = 0
AND VST.D_VST_DATE = (SELECT MIN(VSTB.D_VST_DATE)
FROM DNR_VST_DB_REC VSTB
WHERE VST.D_VST_INSTID = VSTB.D_VST_INSTID
AND VSTB.D_VST_STATUS = 'DN'
AND VST.D_VST_ID = VSTB.D_VST_ID)
AND NOT EXISTS (SELECT R_DRC_ID
FROM REC_DRC_DB_REC
WHERE R_DRC_ID = COUNT_ID
AND R_DRC_INSTID = COUNT_INSTID
AND R_DRC_RESPCD = '15')
GROUP BY
NAM.N_NAM_ID,
NAM.N_NAM_INSTID,
NAM.N_NAM_FNAME,
NAM.N_NAM_MINITIAL,
NAM.N_NAM_LNAME,
PER.N_PER_BIRTH,
ADR.N_ADR_ADDR1,
ADR.N_ADR_ADDR2,
ADR.N_ADR_CITY,
ADR.N_ADR_STATE,
ADR.N_ADR_ZIP,
PER.N_PER_EMAIL,
PER.N_PER_GENDER,
PHNA.N_PHN_AREACD,
PHNA.N_PHN_PREFIX,
PHNA.N_PHN_NUMBER,
PHNA.N_PHN_EXTENTN,
PHNB.N_PHN_AREACD,
PHNB.N_PHN_PREFIX,
PHNB.N_PHN_NUMBER,
PHNB.N_PHN_EXTENTN,
BTY.D_BTY_ABO,
BTY.D_BTY_RHESUS,
VST.D_VST_DATE,
DV2.COUNT_VISITS,
DTS.D_DTS_DONSUM,
ELG.D_ELG_RWBDTE

Error Message:

(845 row(s) affected)

(844 row(s) affected)

(396 row(s) affected)

Server: Msg 9002, Level 17, State 6, Line 2
The log file for database 'tempdb' is full. Back up the transaction log for the database to free up some log space.
Server: Msg 1105, Level 17, State 1, Line 2
Could not allocate space for object '(SYSTEM table id: -109901351)' in database 'TEMPDB' because the 'DEFAULT' filegroup is full.Ok, its a hog.

First, see if you can "blow out" tempdb using DBCC SHRINKDATABASE (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_dbcc_3pd1.asp).

If that doesn't help enough, see if you can create an index that the GROUP BY expression can use... It is often enough to get the first three or four columns covered, since that can buy you an enormous reduction in staging space.

If that doesn't help, buy more disk!

-PatP|||My God, I mean Oh Codd, you have 27 GROUP BY's!!!!... You realize that your tempdb would be the bottleneck throughout the life of your app! Are you sure you need all 27?.. Click on estimated execution plan icon in QA and see what you get there.

MS SQL for returning no values

Hello,

im having a problem integrating an SQL statement with my program,
it works fine when theres a result but throws an error when there is
no result,
is there anyway in SQL to make up a row if there isnt one that matches
the query for example in this i could put indicator values in the
returned fields then a simple check to see if its a proper field of my
indicator field

I know ISNULL works with single values but that only works if there is
a row returned

Does anyone know of any SQL syntax for MS SQL that when no row is
returned it makes on up and parses it back

Thank you in advance for your help!!

BenAm Tue, 20 Nov 2007 02:57:06 -0800 (PST) schrieb Ben:

Quote:

Originally Posted by

Hello,
>
im having a problem integrating an SQL statement with my program,
it works fine when theres a result but throws an error when there is
no result,

... and what is the error message ??

Quote:

Originally Posted by

Does anyone know of any SQL syntax for MS SQL that when no row is
returned it makes on up and parses it back

I don't see any sense in this, but anyway, you can do something like:

if (select count(*) from persontable where id < 17) 0
select id, lastname, street1, city from persontable where id < 17
else select 0 as id, 'testperson' as lastname, 'noStreet' as street1,
'noCity' as city

bye,
Helmut|||What language is your "Program" in? Check the recordcount on the
dataset returned and go from there, don't return the dummy record.

Cheers,
Jason Lepack

On Nov 20, 5:57 am, Ben <bor...@.gmail.comwrote:

Quote:

Originally Posted by

Hello,
>
im having a problem integrating an SQL statement with my program,
it works fine when theres a result but throws an error when there is
no result,
is there anyway in SQL to make up a row if there isnt one that matches
the query for example in this i could put indicator values in the
returned fields then a simple check to see if its a proper field of my
indicator field
>
I know ISNULL works with single values but that only works if there is
a row returned
>
Does anyone know of any SQL syntax for MS SQL that when no row is
returned it makes on up and parses it back
>
Thank you in advance for your help!!
>
Ben

|||On 20 Nov, 13:33, Jason Lepack <jlep...@.gmail.comwrote:

Quote:

Originally Posted by

What language is your "Program" in? Check the recordcount on the
dataset returned and go from there, don't return the dummy record.
>
Cheers,
Jason Lepack
>
On Nov 20, 5:57 am, Ben <bor...@.gmail.comwrote:
>
>
>

Quote:

Originally Posted by

Hello,

>

Quote:

Originally Posted by

im having a problem integrating an SQL statement with my program,
it works fine when theres a result but throws an error when there is
no result,
is there anyway in SQL to make up a row if there isnt one that matches
the query for example in this i could put indicator values in the
returned fields then a simple check to see if its a proper field of my
indicator field

>

Quote:

Originally Posted by

I know ISNULL works with single values but that only works if there is
a row returned

>

Quote:

Originally Posted by

Does anyone know of any SQL syntax for MS SQL that when no row is
returned it makes on up and parses it back

>

Quote:

Originally Posted by

Thank you in advance for your help!!

>

Quote:

Originally Posted by

Ben- Hide quoted text -

>
- Show quoted text -

The Error Message is: exception : System.NullReferenceException:
Object reference not set to an instance of an object.

and its not really programming its a prebuilt assembly which has to be
used i can only alter the code around it, but this assembly throws an
error if there is nothing there so waht i need is, if there are no
values returned that it creats a dummy value, im not sure if you can
do that using a MS SQL statement

All that this assembly returns is either one record or nothing, pretty
useless i know :/ doesnt even return a recordset

Cheers

Ben|||On 20 Nov, 14:34, Ben <bor...@.gmail.comwrote:

Quote:

Originally Posted by

On 20 Nov, 13:33, Jason Lepack <jlep...@.gmail.comwrote:
>
>
>
>
>

Quote:

Originally Posted by

What language is your "Program" in? Check the recordcount on the
dataset returned and go from there, don't return the dummy record.

>

Quote:

Originally Posted by

Cheers,
Jason Lepack

>

Quote:

Originally Posted by

On Nov 20, 5:57 am, Ben <bor...@.gmail.comwrote:

>

Quote:

Originally Posted by

Quote:

Originally Posted by

Hello,

>

Quote:

Originally Posted by

Quote:

Originally Posted by

im having a problem integrating an SQL statement with my program,
it works fine when theres a result but throws an error when there is
no result,
is there anyway in SQL to make up a row if there isnt one that matches
the query for example in this i could put indicator values in the
returned fields then a simple check to see if its a proper field of my
indicator field

>

Quote:

Originally Posted by

Quote:

Originally Posted by

I know ISNULL works with single values but that only works if there is
a row returned

>

Quote:

Originally Posted by

Quote:

Originally Posted by

Does anyone know of any SQL syntax for MS SQL that when no row is
returned it makes on up and parses it back

>

Quote:

Originally Posted by

Quote:

Originally Posted by

Thank you in advance for your help!!

>

Quote:

Originally Posted by

Quote:

Originally Posted by

Ben- Hide quoted text -

>

Quote:

Originally Posted by

- Show quoted text -

>
The Error Message is: exception : System.NullReferenceException:
Object reference not set to an instance of an object.
>
and its not really programming its a prebuilt assembly which has to be
used i can only alter the code around it, but this assembly throws an
error if there is nothing there so waht i need is, if there are no
values returned that it creats a dummy value, im not sure if you can
do that using a MS SQL statement
>
All that this assembly returns is either one record or nothing, pretty
useless i know :/ doesnt even return a recordset
>
Cheers
>
Ben- Hide quoted text -
>
- Show quoted text -

Hey!

nevermind, my stupid brain thought of another idea, appologies for the
post it seems really stupid now >.< why didint i think of count...
Sigh

Thanks for your help anyway! :D

Regards

Ben

ms sql equivalent of this oracle

Hi.
I'm a casual sql user. I have found a situation where I need to convert an
oracle statement to tsql, one I can just fire off in any sql tool against an
ms sql server database.

I studied the exists statement and I think I understand it somewhat, however I
was not sure how to get it quite right. If you have an idea and a minute or
two I'd appreciate any insight or tutorial.

insert into authorization (program, optiontitle, usergroup, authorizationid)
select 'EVERYWHERE','NAVIGATOR',usergroup, authorizationseq.nextval
from allgroups where exists (select * from authorization
where authorization.USERGROUP = allgroups.USERGROUP and
authorization.optiontitle = 'READ' and authorization.program = 'EVERYWHERE')

I believe that because in my data, three values of usergroup from allgroups
return true from the exists, that this is supposed to insert three rows into
authorization.

But I can't figure out what to do about the authorization.nextval.. I tried
various max(authorization)+1
etc but nothing seemed to compile/work

thanks
Jeff KishHi

You can make the column an identity, this will not guarantee contiguous
number but it will be increasing/decreasing and unique. You can then miss it
out from the statement altogether.

These may help:
http://vyaskn.tripod.com/oracle_sql...equivalents.htm
http://www.microsoft.com/technet/pr...art2/c0761.mspx

John

"Jeff Kish" <jeff.kish@.mro.com> wrote in message
news:b5a7u1lod76n3at35csf2b6f8nvpj35jn8@.4ax.com...
> Hi.
> I'm a casual sql user. I have found a situation where I need to convert an
> oracle statement to tsql, one I can just fire off in any sql tool against
> an
> ms sql server database.
> I studied the exists statement and I think I understand it somewhat,
> however I
> was not sure how to get it quite right. If you have an idea and a minute
> or
> two I'd appreciate any insight or tutorial.
> insert into authorization (program, optiontitle, usergroup,
> authorizationid)
> select 'EVERYWHERE','NAVIGATOR',usergroup, authorizationseq.nextval
> from allgroups where exists (select * from authorization
> where authorization.USERGROUP = allgroups.USERGROUP and
> authorization.optiontitle = 'READ' and authorization.program =
> 'EVERYWHERE')
>
> I believe that because in my data, three values of usergroup from
> allgroups
> return true from the exists, that this is supposed to insert three rows
> into
> authorization.
> But I can't figure out what to do about the authorization.nextval.. I
> tried
> various max(authorization)+1
> etc but nothing seemed to compile/work
> thanks
> Jeff Kish|||Jeff,

authorizationseq seems to be a sequence. Sequences just don't exist in
MS SQL Server 2000/7/6.5. You might want to have an identity column.|||On Sat, 4 Feb 2006 16:17:55 -0000, "John Bell" <jbellnewsposts@.hotmail.com>
wrote:

>Hi
>You can make the column an identity, this will not guarantee contiguous
>number but it will be increasing/decreasing and unique. You can then miss it
>out from the statement altogether.
>These may help:
>http://vyaskn.tripod.com/oracle_sql...equivalents.htm
>http://www.microsoft.com/technet/pr...art2/c0761.mspx
thanks. I'm still not sure of how to do something here, though.

This is directly related to the problem but re-worded because I need to
get the next value using max(authorizationid)+1 ...

Given two tables:
allgroups(usergroup, otherdata) =
{'group1',otherdata1,
'group2',otherdata2,
'group3',otherdata3,
:
:
'groupn',otherdatan}

and
authorization(program,optiontitle,
usergroup,authorizationid) =
{'pro1','title1','ug1',3,
'pro2','title2','ug2',4,
:
'pron','titlen','ugn',m}

How can I insert multiple
lines (one for each usergroup
in allgroups) using one sql statement
into authorization if this is correct for a
single insert:
insert into authorization(program,
optiontitle,usergroup,
authorizationid)
select 'proq','titleq','ug1',
max(authorizationid)+1
from authorization

bascially I'd like each usergroup
from allgroups to be used to create a
new line in authorization, having
the authorizationid increment one from
the current max.

Yes, I have no control over the design/use of
an identity column.

Is it possible?
Thanks
Jeff Kish|||Jeff Kish wrote:

> thanks. I'm still not sure of how to do something here, though.
> This is directly related to the problem but re-worded because I need to
> get the next value using max(authorizationid)+1 ...
> Given two tables:
> allgroups(usergroup, otherdata) =
> {'group1',otherdata1,
> 'group2',otherdata2,
> 'group3',otherdata3,
> :
> :
> 'groupn',otherdatan}
> and
> authorization(program,optiontitle,
> usergroup,authorizationid) =
> {'pro1','title1','ug1',3,
> 'pro2','title2','ug2',4,
> :
> 'pron','titlen','ugn',m}
> How can I insert multiple
> lines (one for each usergroup
> in allgroups) using one sql statement
> into authorization if this is correct for a
> single insert:
> insert into authorization(program,
> optiontitle,usergroup,
> authorizationid)
> select 'proq','titleq','ug1',
> max(authorizationid)+1
> from authorization
> bascially I'd like each usergroup
> from allgroups to be used to create a
> new line in authorization, having
> the authorizationid increment one from
> the current max.
> Yes, I have no control over the design/use of
> an identity column.
> Is it possible?
> Thanks
> Jeff Kish

Assuming SQL Server 2005 (you didn't specify otherwise), use the
ROW_NUMBER function. For example:

INSERT INTO [authorization]
(program, optiontitle, usergroup, authorizationid)
SELECT program, optiontitle, usergroup,
authorizationid+
(SELECT MAX(authorizationid)
FROM [authorization])
FROM
(SELECT 'proq','titleq', usergroup, ROW_NUMBER()
OVER (ORDER BY usergroup)
FROM allgroups)
AS T(program, optiontitle, usergroup, authorizationid) ;

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--|||<snip>
>Assuming SQL Server 2005 (you didn't specify otherwise), use the
Sql Server 2000. Really sorry I did not say it up front. Do you know
of a way using that?
Regards,
Jeff

>ROW_NUMBER function. For example:
>INSERT INTO [authorization]
> (program, optiontitle, usergroup, authorizationid)
> SELECT program, optiontitle, usergroup,
> authorizationid+
> (SELECT MAX(authorizationid)
> FROM [authorization])
> FROM
> (SELECT 'proq','titleq', usergroup, ROW_NUMBER()
> OVER (ORDER BY usergroup)
> FROM allgroups)
> AS T(program, optiontitle, usergroup, authorizationid) ;
>--
>David Portas, SQL Server MVP
>Whenever possible please post enough code to reproduce your problem.
>Including CREATE TABLE and INSERT statements usually helps.
>State what version of SQL Server you are using and specify the content
>of any error messages.
>SQL Server Books Online:
>http://msdn2.microsoft.com/library/...US,SQL.90).aspx|||Jeff Kish wrote:
> <snip>
> >Assuming SQL Server 2005 (you didn't specify otherwise), use the
> Sql Server 2000. Really sorry I did not say it up front. Do you know
> of a way using that?
> Regards,
> Jeff

INSERT INTO [authorization]
(program, optiontitle, usergroup, authorizationid)
SELECT program, optiontitle, usergroup,
authorizationid+
(SELECT COALESCE(MAX(authorizationid),0)
FROM [authorization])
FROM
(SELECT 'proq','titleq', A1.usergroup, COUNT(*)
FROM allgroups AS A1
JOIN allgroups AS A2
ON A1.usergroup >= A2.usergroup
GROUP BY A1.usergroup)
AS T(program, optiontitle, usergroup, authorizationid) ;

This assumes that usergroup is unique in Allgroups. If I'm wrong then
use the key of that table in the join. You need a key in order to
generate the sequence.

Read my signature. It may help you get faster answers in future.

Hope this helps.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--

MS SQL 2005: Where clause and optimizer

Way back when, and at least in version 7 IIRC, the query optimizer gave
up when the where clause in a statement contained more than 4 search
conditions.

Does anyone know if such a limitation still exist in MS SQL 2005? The
BOL seems to be silent on the issue.

Boaboa sema (boasema@.gmail.com) writes:

Quote:

Originally Posted by

Way back when, and at least in version 7 IIRC, the query optimizer gave
up when the where clause in a statement contained more than 4 search
conditions.

This is a complete misunderstanding. What you might be thinking of is
that in SQL 6.5 and earlier version, the optimizer would only consider
four tables at a time, so if you had a query like:

SELECT ...
FROM a, b, c, d, e
WHERE ...

the optimizer would first look at (a, b, c, d) as a group, and then
(b, c, d, e) as a group. This mean that if the best plan was to start
with e and then take a next, the optimizer would most likely not find
plan.

Quote:

Originally Posted by

Does anyone know if such a limitation still exist in MS SQL 2005? The
BOL seems to be silent on the issue.

The behaviour I describe above was abolished in SQL 7. The optimizer does
still not consider all possible plans - because for 12-table query it could
spend all day optimising. Instead it considers it task finished when the
estimated cost is good enough.

--
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|||Erland Sommarskog wrote:

Quote:

Originally Posted by

boa sema (boasema@.gmail.com) writes:
>

Quote:

Originally Posted by

>>Way back when, and at least in version 7 IIRC, the query optimizer gave
>>up when the where clause in a statement contained more than 4 search
>>conditions.

>
>
This is a complete misunderstanding. What you might be thinking of is
that in SQL 6.5 and earlier version, the optimizer would only consider
four tables at a time, so if you had a query like:
>
SELECT ...
FROM a, b, c, d, e
WHERE ...
>
the optimizer would first look at (a, b, c, d) as a group, and then
(b, c, d, e) as a group. This mean that if the best plan was to start
with e and then take a next, the optimizer would most likely not find
plan.

That's what I was thinking of. It was a very, very long time ago,
impressing that you still remember the details as well as you do.

Now that you've clarified it so well, this is not an issue anymore. I
remembered incorrectly.

Thanks.
Boa

[snip]

MS SQL 2000 LEFT OUTER JOIN and RIGHT OUTER JOIN problem?

I'm a little with a JOIN that should be working but isn't.
If I run the same SQL statement in MS Access (same data) I get the join
results I expect. If I run the SQL statement in Enterprise Manager I get a
different result (again same data).
I thought both are supposed to be ANSI compliant?
What I'm trying to accomplish is to force return results even if one table
(FS) doesn't return any records:
SELECT FS.CR, S.SN, S.SID
FROM FS RIGHT OUTER JOIN
S ON FS.SID = S.SID
WHERE (FS.DDate = '4/30/2004')
I'm expecting to see one record with SN and SID values populated and CR
null -- the same query in MS Access produces the expected result, but the
same view in MS SQL 2000 returns NO records.
(true field name and table names have been removed to protect the innocent)
;)
Any suggestions?
Rob.>> I thought both are supposed to be ANSI compliant? <<
ACCESS ain't even close to standards and the underlying model is a file
system. That is why they use the words "record" and "field" instead of
rows and columns.
Since the WHERE clause is applied last, that is probably the culprit.
Did you consider using ISO-8601 dates, as required by Standard SQL? Is
ddate a temporal data type?
WHERE FS.ddate = '2004-04-30';|||> WHERE FS.ddate = '2004-04-30';
Actually Joe, in SQL Server, '20040430' is much safer than the (admittedly,
more sensible and legible) yyyy-mm-dd format.
A|||You've referenced the unpreserved table in the WHERE clause, which
effectively turns your outer join into an inner join. Also, your date
format isn't necessarily reliable in SQL Server. Try:
SELECT FS.cr, S.sn, S.sid
FROM FS
RIGHT OUTER JOIN S
ON FS.sid = S.sid
AND FS.ddate = '20040430'
Personally, I would prefer to turn this around as a LEFT join:
SELECT FS.cr, S.sn, S.sid
FROM S
LEFT OUTER JOIN FS
ON FS.sid = S.sid
AND FS.ddate = '20040430'
I think it's fair to say that LEFT outer joins are used much more often
than RIGHT and perhaps one intuitively expects to see the preserved
table specified first. Maybe that confusion is what caused you to
reference FS in the WHERE clause?
Access's basic syntax differs from ANSI SQL92 in several ways. Don't
expect Access and SQL Server results to correspond every time.
David Portas
SQL Server MVP
--|||> Actually Joe, in SQL Server, '20040430' is much safer than the
(admittedly,
> more sensible and legible) yyyy-mm-dd format.
Indeed. On my (German) server, this is interpreted as yyyy-dd-mm. Therefore,
I prefer to use the also safe (and legible) ODBC version: {d 'yyyy-mm-dd'}
Axel|||> Indeed. On my (German) server, this is interpreted as yyyy-dd-mm.
> Therefore,
> I prefer to use the also safe (and legible) ODBC version: {d 'yyyy-mm-dd'}
But I don't know if you will always be able to rely on SQL Server internally
processing that syntax correctly.
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.|||If I'm not mistaken, in SQL Server (per a Kalen Delaney article from some ti
me
ago), if you use the following format: yyyymmdd SQL will always interpret th
at
correctly regardless of the collation or regional settings. Note that this
format has no dashes.
Thomas
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:Ozy7TI4PFHA.2788@.TK2MSFTNGP09.phx.gbl...
> But I don't know if you will always be able to rely on SQL Server internal
ly
> processing that syntax correctly.
> --
> This is my signature. It is a general reminder.
> Please post DDL, sample data and desired results.
> See http://www.aspfaq.com/5006 for info.
>|||Yes, that's the format I recommended. What I was commenting on was {d
'yyyy-mm-dd'}
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Thomas" <replyingroup@.anywhere.com> wrote in message
news:OKO2Jw4PFHA.2252@.TK2MSFTNGP15.phx.gbl...
> If I'm not mistaken, in SQL Server (per a Kalen Delaney article from some
> time ago), if you use the following format: yyyymmdd SQL will always
> interpret that correctly regardless of the collation or regional settings.
> Note that this format has no dashes.|||Some additional information, in case anyone is interested...
That format (ODBC escape clause format) is still in BOL for SQL Server 2005.
My guess is that it
will be around for a while. Personally, I'm not too found of it. It was intr
oduced in ODBC to allow
ODBC to catch the string and convert into a format that the DBMS will unders
tand. As of 7.0, it was
added to the DBMS. Try passing it using ISQL against 6.5 and 7.0 to see the
difference. AFAIK, both
ODBC and OLEDB are supposed to convert these escape clauses, I have no idea
whether ADO.NET catches
ODBC-style escape clauses.
I prefer unseparated (as most do). I wish that MS would make the ANSI SQL fo
rmat unambiguous, but I
doubt that will happen in the upcoming 2.4 versions (if at all).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%230pyPH5PFHA.1932@.tk2msftngp13.phx.gbl...
> Yes, that's the format I recommended. What I was commenting on was {d 'yy
yy-mm-dd'}
> --
> This is my signature. It is a general reminder.
> Please post DDL, sample data and desired results.
> See http://www.aspfaq.com/5006 for info.
>
> "Thomas" <replyingroup@.anywhere.com> wrote in message
> news:OKO2Jw4PFHA.2252@.TK2MSFTNGP15.phx.gbl...
>|||> I wish that MS would make the ANSI SQL format unambiguous
Me too. To me it's rather irritating that it is so hard to find an
internationally constant DATETIME literal format in SQL Server enabling me
to write software compiling INSERT statements writing a date value into a
DATETIME column.

> I prefer unseparated (as most do).
From my perspective I dislike the unseparated format. It's hard to read and
it's even harder to find typing errors in it giving me likely a hard time to
find errors that don't even exist.
Axel Dahmen

MS SQL 2000 - SQL Select with XPATH Where Clause

In MS SQL Server 2000, is there an easy way to use an XPATH statement in a
WHERE clause? I have a table with a TEXT column that contains XML. I would
like to query this table and select any rows where the XML data field meets
my XPATH criteria.
I'm attempting to avoid using cursors and sp_xml_preparedocument on each row
of data in the table, but with SQL Server 2000, that may be my only option.
Any help would be appreciated!Bryan (Bryan@.discussions.microsoft.com) writes:
> In MS SQL Server 2000, is there an easy way to use an XPATH statement in
> a WHERE clause?
Yeah, upgrade the instance to SQL 2005. :-)

> I have a table with a TEXT column that contains XML. I
> would like to query this table and select any rows where the XML data
> field meets my XPATH criteria.
> I'm attempting to avoid using cursors and sp_xml_preparedocument on each
> row of data in the table, but with SQL Server 2000, that may be my only
> option.
OPENXML is on the only XML support that SQL 2000 offers. I agree that a
cursor and preparedocument for each is not appealing. The other alternative
would be to bring the data to the client, but is probably even less
appealing.
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