[Shared Memory] ConnectionRead (recv()) ... error

Hi I've got error below when i ran a stored procedure:
----
[Microsoft][ODBC SQL Server Driver][Shared Memory]
ConnectionRead (recv())
General network error.
----
The same error occured in different places in each run.
The SP was running locally in Query Analyzer and there is
no network connection.
Anyone came across this before?
Cheers
FrankWhat operating system are you running?
Vikram
"frk" <frk_wang@.hotmail.com> wrote in message
news:1a80001c44e83$e5ea8210$a101280a@.phx
.gbl...
> Hi I've got error below when i ran a stored procedure:
> ----
> [Microsoft][ODBC SQL Server Driver][Shared Memory]
> ConnectionRead (recv())
> General network error.
> ----
> The same error occured in different places in each run.
> The SP was running locally in Query Analyzer and there is
> no network connection.
> Anyone came across this before?
> Cheers
> Frank
>|||A couple of ideas.
1. Check the version of MDAC that you're running. Make sure it's patched
as well.
2. Disable Shared Memory using the SQL Client Network Utility. Then retest.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.

[Shared Memory] ConnectionRead (recv()) ... error

Hi I've got error below when i ran a stored procedure:
[Microsoft][ODBC SQL Server Driver][Shared Memory]
ConnectionRead (recv())
General network error.
The same error occured in different places in each run.
The SP was running locally in Query Analyzer and there is
no network connection.
Anyone came across this before?
Cheers
Frank
What operating system are you running?
Vikram
"frk" <frk_wang@.hotmail.com> wrote in message
news:1a80001c44e83$e5ea8210$a101280a@.phx.gbl...
> Hi I've got error below when i ran a stored procedure:
> ----
> [Microsoft][ODBC SQL Server Driver][Shared Memory]
> ConnectionRead (recv())
> General network error.
> ----
> The same error occured in different places in each run.
> The SP was running locally in Query Analyzer and there is
> no network connection.
> Anyone came across this before?
> Cheers
> Frank
>
|||A couple of ideas.
1. Check the version of MDAC that you're running. Make sure it's patched
as well.
2. Disable Shared Memory using the SQL Client Network Utility. Then retest.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.

Multiple Records Subquery..

Hi everybody,

I like to display the records for AccountNo = 221 from table records shown below, how would I do this? I am display this results in a crystal report. What is sql statement to do this? Thanks.

Sql Statement: (this statement is not allowed)
Select AccountNo, RecordID, (Select Description From Table1 Where RecordID In (Select RecordID From Table2 Where Date < '04/05/2006')) As Description, Amount From Table2 Where Date < '04/05/2006'

Desired Result:

AccountNo RecordID Description Amount
221 20 Whew 290.00
221 21 Hi There Good Morning 728.00

Table 1

RecordID Description
20 Whew
21 Hi
21 There
21 Good Morning

Table 2
Date AccountNo RecordID Amount
04/02/2006 220 19 80.0
04/03/2006 221 20 290.0
04/04/2006 221 21 728.0
04/06/2006 223 23 200.3
04/07/2006 225 25 2893.20

den2005

select t2.AccountNo, t1.RecordId, t2.Description, t1.Amount

from Table1 t1 inner join Table2 t2 on t1.RecordId = t2.RecordId

where t2.AccountNo = 221 and t2.Date < '4/5/2006'

|||

Hi,

Thanks for replying, I f I do that teh results would be,

AccountNo RecordID Description Amount
221 20 Whew 290.00
221 21 Hi 728.00

221 21 There 728.00

221 21 Good Morning 728.00

But I want to display shown below,

AccountNo RecordID Description Amount
221 20 Whew 290.00
221 21 Hi There Good Morning 728.00

Besides that is my first statement using inner join...

den2005

|||How do you determine that it is "Hi There Good Morning" and not "Good Morning Hi There" and not "There Hi Good Morning" etc.

Is there another column that will determine the required order ?

Do take not that, records are stored in database in no particular order. When returning the records, you have to determine the sequence by using ORDER BY.

[Microsoft][SQLServer 2000 Driver for JDBC]Row update failed

I am getting an error "[Microsoft][SQLServer 2000 Driver for JDBC]Row update
failed" while updating an int value on result set. Below is the code snippet
that i use in java. The error is occurring occasionally. Not able to
replicate.
strSQL = "SELECT intCol FROM Table1 WHERE intCol = 1";
PreparedStatement objPS = null;
...get the connection object, prepare object...using msjdbc driver with SQL
server 2000
ResultSet objRS = objPS.executeQuery();
objRS.updateInt("intCol", 2);
objRS.updateRow();
any help is appreciated.
Thanks in advance.
| Thread-Topic: [Microsoft][SQLServer 2000 Driver for JDBC]Row update failed
| thread-index: AcUbT0uzY+NiEZNfSCeDqAYi6cibWQ==
| X-WBNR-Posting-Host: 152.160.15.58
| From: "=?Utf-8?B?U2FyYW4=?=" <Saran@.discussions.microsoft.com>
| Subject: [Microsoft][SQLServer 2000 Driver for JDBC]Row update failed
| Date: Fri, 25 Feb 2005 07:33:03 -0800
| Lines: 15
| Message-ID: <05BEC007-4670-4D11-9C3F-E46F19A60BDC@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.jdbcdriver
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
| Path: TK2MSFTNGXA02.phx.gbl!cpmsftngxa10.phx.gbl!TK2MSFT NGXA03.phx.gbl
| Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.jdbcdriver:6702
| X-Tomcat-NG: microsoft.public.sqlserver.jdbcdriver
|
| I am getting an error "[Microsoft][SQLServer 2000 Driver for JDBC]Row
update
| failed" while updating an int value on result set. Below is the code
snippet
| that i use in java. The error is occurring occasionally. Not able to
| replicate.
|
| strSQL = "SELECT intCol FROM Table1 WHERE intCol = 1";
| PreparedStatement objPS = null;
| ...get the connection object, prepare object...using msjdbc driver with
SQL
| server 2000
| ResultSet objRS = objPS.executeQuery();
| objRS.updateInt("intCol", 2);
| objRS.updateRow();
|
| any help is appreciated.
| Thanks in advance.
|
What is the frequency of this problem? Did the problem occur while other
activity the same database was occurring? Do you have a primary key
defined on the table? Which options did you use when preparing the
statement? Are you using the most recent build of the JDBC driver?
Carb Simien, MCSE MCDBA MCAD
Microsoft Developer Support - Web Data
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection
Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.
|||Hi Carb, thanks for your reply, here is my answers for your questions. Hope
that will help us to resolve the issue.
1. What is the frequency of this problem? - not able to determine,
occurring occasinoaly.
2. Did the problem occur while other activity the same database was
occurring? - I couldn't make sure it 100% though, i am possitive that there
could be some other activity on the database.
3. Do you have a primary key defined on the table? - Yes e.g. table script
tbl1(col1 varchar(100) NOT NULL, col2 int NULL)
here: col1 is primary key - but i am trying to update col2
4. Which options did you use when preparing the statement? - here is e.g.
objCon.prepareStatement(strSQL, ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY)
5. Are you using the most recent build of the JDBC driver? MS-JDBC SP2
thanks
-Saran
""Carb Simien [MSFT]"" wrote:

> --
> | Thread-Topic: [Microsoft][SQLServer 2000 Driver for JDBC]Row update failed
> | thread-index: AcUbT0uzY+NiEZNfSCeDqAYi6cibWQ==
> | X-WBNR-Posting-Host: 152.160.15.58
> | From: "=?Utf-8?B?U2FyYW4=?=" <Saran@.discussions.microsoft.com>
> | Subject: [Microsoft][SQLServer 2000 Driver for JDBC]Row update failed
> | Date: Fri, 25 Feb 2005 07:33:03 -0800
> | Lines: 15
> | Message-ID: <05BEC007-4670-4D11-9C3F-E46F19A60BDC@.microsoft.com>
> | MIME-Version: 1.0
> | Content-Type: text/plain;
> | charset="Utf-8"
> | Content-Transfer-Encoding: 7bit
> | X-Newsreader: Microsoft CDO for Windows 2000
> | Content-Class: urn:content-classes:message
> | Importance: normal
> | Priority: normal
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
> | Newsgroups: microsoft.public.sqlserver.jdbcdriver
> | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
> | Path: TK2MSFTNGXA02.phx.gbl!cpmsftngxa10.phx.gbl!TK2MSFT NGXA03.phx.gbl
> | Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.jdbcdriver:6702
> | X-Tomcat-NG: microsoft.public.sqlserver.jdbcdriver
> |
> | I am getting an error "[Microsoft][SQLServer 2000 Driver for JDBC]Row
> update
> | failed" while updating an int value on result set. Below is the code
> snippet
> | that i use in java. The error is occurring occasionally. Not able to
> | replicate.
> |
> | strSQL = "SELECT intCol FROM Table1 WHERE intCol = 1";
> | PreparedStatement objPS = null;
> | ...get the connection object, prepare object...using msjdbc driver with
> SQL
> | server 2000
> | ResultSet objRS = objPS.executeQuery();
> | objRS.updateInt("intCol", 2);
> | objRS.updateRow();
> |
> | any help is appreciated.
> | Thanks in advance.
> |
> What is the frequency of this problem? Did the problem occur while other
> activity the same database was occurring? Do you have a primary key
> defined on the table? Which options did you use when preparing the
> statement? Are you using the most recent build of the JDBC driver?
> Carb Simien, MCSE MCDBA MCAD
> Microsoft Developer Support - Web Data
> Please reply only to the newsgroups.
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Are you secure? For information about the Strategic Technology Protection
> Program and to order your FREE Security Tool Kit, please visit
> http://www.microsoft.com/security.
>
|||saran wrote:
> 4. Which options did you use when preparing the statement? - here is
e.g.
> objCon.prepareStatement(strSQL, ResultSet.TYPE_SCROLL_INSENSITIVE,
> ResultSet.CONCUR_READ_ONLY)
And if the ResultSet was created as read only how do you expect
updateRow() to work? Speaking of which, if the error message would have
been "General error" or simply "Error", it would have been just as easy
to track down the cause.
Alin,
The jTDS Project.
|||| Thread-Topic: [Microsoft][SQLServer 2000 Driver for JDBC]Row update failed
| thread-index: AcUpfTtxzBvD5vNlRPaQXIKezzsWHg==
| X-WBNR-Posting-Host: 152.160.15.58
| From: "=?Utf-8?B?c2FyYW4=?=" <saran@.discussions.microsoft.com>
| References: <05BEC007-4670-4D11-9C3F-E46F19A60BDC@.microsoft.com>
<zQR4wCMKFHA.1136@.TK2MSFTNGXA02.phx.gbl>
| Subject: RE: [Microsoft][SQLServer 2000 Driver for JDBC]Row update failed
| Date: Tue, 15 Mar 2005 08:37:09 -0800
| Lines: 86
| Message-ID: <4F9F2476-D4EE-4EA1-8288-B9C2EB8D5A30@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.jdbcdriver
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
| Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGXA01.phx.gbl!TK2MSF TNGXA03.phx.gbl
| Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.jdbcdriver:6756
| X-Tomcat-NG: microsoft.public.sqlserver.jdbcdriver
|
| Hi Carb, thanks for your reply, here is my answers for your questions.
Hope
| that will help us to resolve the issue.
|
| 1. What is the frequency of this problem? - not able to determine,
| occurring occasinoaly.
|
| 2. Did the problem occur while other activity the same database was
| occurring? - I couldn't make sure it 100% though, i am possitive that
there
| could be some other activity on the database.
|
| 3. Do you have a primary key defined on the table? - Yes e.g. table script
| tbl1(col1 varchar(100) NOT NULL, col2 int NULL)
| here: col1 is primary key - but i am trying to update col2
|
| 4. Which options did you use when preparing the statement? - here is e.g.
| objCon.prepareStatement(strSQL, ResultSet.TYPE_SCROLL_INSENSITIVE,
| ResultSet.CONCUR_READ_ONLY)
|
| 5. Are you using the most recent build of the JDBC driver? MS-JDBC SP2
|
| thanks
| -Saran
|
| ""Carb Simien [MSFT]"" wrote:
|
| >
| > --
| > | Thread-Topic: [Microsoft][SQLServer 2000 Driver for JDBC]Row update
failed
| > | thread-index: AcUbT0uzY+NiEZNfSCeDqAYi6cibWQ==
| > | X-WBNR-Posting-Host: 152.160.15.58
| > | From: "=?Utf-8?B?U2FyYW4=?=" <Saran@.discussions.microsoft.com>
| > | Subject: [Microsoft][SQLServer 2000 Driver for JDBC]Row update failed
| > | Date: Fri, 25 Feb 2005 07:33:03 -0800
| > | Lines: 15
| > | Message-ID: <05BEC007-4670-4D11-9C3F-E46F19A60BDC@.microsoft.com>
| > | MIME-Version: 1.0
| > | Content-Type: text/plain;
| > | charset="Utf-8"
| > | Content-Transfer-Encoding: 7bit
| > | X-Newsreader: Microsoft CDO for Windows 2000
| > | Content-Class: urn:content-classes:message
| > | Importance: normal
| > | Priority: normal
| > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| > | Newsgroups: microsoft.public.sqlserver.jdbcdriver
| > | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
| > | Path: TK2MSFTNGXA02.phx.gbl!cpmsftngxa10.phx.gbl!TK2MSFT NGXA03.phx.gbl
| > | Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.jdbcdriver:6702
| > | X-Tomcat-NG: microsoft.public.sqlserver.jdbcdriver
| > |
| > | I am getting an error "[Microsoft][SQLServer 2000 Driver for JDBC]Row
| > update
| > | failed" while updating an int value on result set. Below is the code
| > snippet
| > | that i use in java. The error is occurring occasionally. Not able to
| > | replicate.
| > |
| > | strSQL = "SELECT intCol FROM Table1 WHERE intCol = 1";
| > | PreparedStatement objPS = null;
| > | ...get the connection object, prepare object...using msjdbc driver
with
| > SQL
| > | server 2000
| > | ResultSet objRS = objPS.executeQuery();
| > | objRS.updateInt("intCol", 2);
| > | objRS.updateRow();
| > |
| > | any help is appreciated.
| > | Thanks in advance.
| > |
| >
| > What is the frequency of this problem? Did the problem occur while
other
| > activity the same database was occurring? Do you have a primary key
| > defined on the table? Which options did you use when preparing the
| > statement? Are you using the most recent build of the JDBC driver?
| >
| > Carb Simien, MCSE MCDBA MCAD
| > Microsoft Developer Support - Web Data
| >
| > Please reply only to the newsgroups.
| > This posting is provided "AS IS" with no warranties, and confers no
rights.
| >
| > Are you secure? For information about the Strategic Technology
Protection
| > Program and to order your FREE Security Tool Kit, please visit
| > http://www.microsoft.com/security.
| >
| >
|
Yes, you will need to use Resultset.CONCUR_UPDATABLE to allow the updates..
Also, the most recent build of the Microsoft JDBC driver is JDBC SP3
(build 2.2.0040):
SQL Server 2000 Driver for JDBC Service Pack 3
http://www.microsoft.com/downloads/d...B11-0502-461A-
B138-2AA54BFDC03A&displaylang=en
Carb Simien, MCSE MCDBA MCAD
Microsoft Developer Support - Web Data
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection
Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.

[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

trying to install sql server2005 on a windows 2003 server box.

getting msg below at the sql server . i looked at other posts on trying to uninstall SQL Native Access Client and norton antivirus. i could never find the snac on the add - remove programs and this server does not have a virus protection program yet.

here's the history of the installs on the server:

wanted to test a 2005 upgrade so:

1) installed sql server 2000 then sp4 then restored some databases to it - all OK

2) tried to upgrade to sql 2005 but ran into problems and left it at that.

had a disk drive crash on the d drive so lost the installs but not the operating system

when the drive was replaced, left alone for a while

then wanted to test a straight 2005 install

1) removed the broken 2005 attempt

2) removed the 2000

3) installed 2005 and got the error on the subject line:

TITLE: Microsoft SQL Server 2005 Setup

SQL Server Setup could not connect to the database service for server configuration. The error was: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified Refer to server error logs and setup logs for more information. For details on how to view setup logs, see "How to View Setup Log Files" in SQL Server Books Online.

i've gone through as many of the forums that i can and have tried several things - like uninstalling 2005 and installing pieces and parts but but nothing seems to work.

Thanks!

Dan <><

It sounds like the SNAC version you have might be corrupt or incompatible.

Go to Add or Remove Programs and find the listing for "Microsoft SQL Native Client". Remove this component.

Then try the install again.

Michelle

|||

Thank you so much for replying. last attempt was to install just the sql server commonent - no others like analysis services or integration services.

i looked at the add / remove programs both before, during and after the sql server database services failed attempt and the add/remove has never shown a program called MS sql native access client (SNAC).

when i start the install, the installing prerequisites window shows the two components that will be installed: snac and the sql server 2005 setup support files. the snac goes to a green arrow almost immediately while the 2005 setup files takes a few seconds. i dont know if that helps or not.

the add remove programs just has:

microsoft .net framwork 2.0|||

It definatel looks like the problems is the "Microsoft SQL Server Native Client" layer. At this point your machine thinks that it is already installed but the install is broken.

Follow the directions in the following article to clean up the machine and then try again.

http://support.microsoft.com/kb/909967

Michelle

|||

uninstall manually - did the following (shows issues with some of the directions)

it said to delete the local security groups. but i never could figure out how to do. the article it mensions didnt (at least to me) show how to delete. so i just went on|||

i went out to the website and downloaded the .msi file for snac. it was slightly different from the .msi that came on the DVD (3511k vs 3516k).

the dvd version when i clicked on the msi file just returned me back to windows explorer.

the snac from microsoft's website executed and finally installed the snac.

the install worked perfectly after that.

thanks for your help

|||

Actually that was a good work around as downloading a newer version would have forced the install to run again where as in the previous case it was failing quickly becuase it thought it was laready installed.

Glad you got you machine back.

Michelle

|||

Hi,

I've been handling SQL Server upgrades for many years. The most dangerous path for an upgrade is to directly upgrade 2000 to 2005 (or 7.0 to 2000). That's because you create the risk of destroying the databases on the existing server and causing data loss.

The best approach embraces the fact that we typically upgrade a windows server when the mfg warranty on the hardware has expired. So, you have a new box and windows server. If you don't, the store the backups on another resource (disk, tape, flash drive, etc...) and copy from or restore from that resource. Rebuild the Windows server or uninstall SQL Server 2000. (I would have fresh windows to insure the best chance of success.)

Install SQL 2005 & configure on the new W03 SP1 server. migrate the users. (this worked easily with 2K but MS has hosed that functionality in 2005 from what I can see; you must use a script they provided that is flaky. You will need to manually create the users it fails to migrate.) Backup the dbs on 2K, shut down 2K on the old machine and restore the dbs to 2005. associate the users with the logins.

Now you have a fullly upgraded database server and your databases are unhosed. This may not be elegant but it guarantees the MS upgrade process won't destroy your databases or bring down your business for days.

If you have a completely hosed system, then rebuild the system (and the drives) and follow the steps above to a successful conclusion.

z.

[database_name . [schema_name ] . | schema_name . ] table_name

Hi,
I try to create 2 schema as the syntax display's as below, but I get a error
of only use 1. Do I have to change any parameters in SQL 2005?
And will I get any other problems in the future to use 2 schema?
Syntax
CREATE TABLE
[ database_name . [ schema_name ] . | schema_name . ] table_name> Hi,
> I try to create 2 schema as the syntax display's as below, but I get a
> error
> of only use 1.
Can you show the ACTUAL syntax you tried, and the EXACT error message?
The syntax you show demonstrates two options:
CREATE TABLE master.schema1.table
CREATE TABLE schema1.table
It sounds like you maybe misinterpreted it as:
CREATE TABLE master.schema1.schema2.table
?

> Do I have to change any parameters in SQL 2005?
> And will I get any other problems in the future to use 2 schema?
> Syntax
> CREATE TABLE
> [ database_name . [ schema_name ] . | schema_name . ] table_name|||The schema concept is not hierarchical. An object is contained in a schema,
not a level of schemas.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"RBC" <RBC@.discussions.microsoft.com> wrote in message
news:DD3B310F-B41C-45E5-AEC9-C5F2DD1A12C4@.microsoft.com...
> Hi,
> I try to create 2 schema as the syntax display's as below, but I get a err
or
> of only use 1. Do I have to change any parameters in SQL 2005?
> And will I get any other problems in the future to use 2 schema?
> Syntax
> CREATE TABLE
> [ database_name . [ schema_name ] . | schema_name . ] table_name

@xml.value against XML data with namespaces

i have some XML, that i am using .value to get the values from, when the
namespa e is included in the XML(as below ) it doesn't work (everything come
s
as NULL), however if i manually take out toe xmlns part of the xml, it works
fine. i'm just wondering how i can change my .value code to work with the
namespace..
declare @.myxml XML
set @.myxml =
'<CodingNoticesP6P6B
xmlns="http://www.govtalk.gov.uk/taxation/CodingNoticesP6P6B/2"
xmlns:gt="http://www.govtalk.gov.uk/CM/core"
xmlns:gms="http://www.govtalk.gov.uk/CM/gms-xs" IssueDate="2005-09-01"
TaxYearEnd="2005" SequenceNumber="1" FormType="P6B">
<EmployerRef>961/1791574</EmployerRef>
<Name>
<Title>AA</Title>
<Forename>AA</Forename>
<Surname>AA</Surname>
</Name>
<NINO>AA000000A</NINO>
<WorksNumber>WN001</WorksNumber>
<EffectiveDate>2005-09-01Z</EffectiveDate>
<CodingUpdate>
<TaxCode>10A</TaxCode>
<TotalPreviousPay Currency="GBP">5000.00</TotalPreviousPay>
<TotalPreviousTax Currency="GBP">1000.00</TotalPreviousTax>
</CodingUpdate>
</CodingNoticesP6P6B>'
--doesn't work when namespace is in, need to make the query work with the
namespace somehow
select @.myxml.value('(/CodingNoticesP6P6B/@.IssueDate)[1]' , 'datetime' ) as
P6_IssueDate,
@.myxml.value('(/CodingNoticesP6P6B/@.TaxYearEnd)[1]', 'char(4)' ) as
P6_TaxYearEnd,
@.myxml.value('(CodingNoticesP6P6B/@.SequenceNumber)[1]' , 'int' ) as
P6_SequenceNumber,
@.myxml.value('(CodingNoticesP6P6B/EmployerRef)[1]' , 'varchar(14)' ) AS
P6_EmployerRef,
@.myxml.value('(CodingNoticesP6P6B/Name/Title)[1]' , 'varchar(4)' ) as
P6_Title,
@.myxml.value('(CodingNoticesP6P6B/Name/Forename)[1]' , 'varchar(35)' )
as P6_ForeName,
@.myxml.value('(CodingNoticesP6P6B/Name/Forename)[2]' , 'varchar(35)' )
as P6_ForeName_2,
@.myxml.value('(CodingNoticesP6P6B/Name/Surname)[1]' , 'varchar(35)' ) as
P6_Surname,
@.myxml.value('(CodingNoticesP6P6B/NINO)[1]' , 'char(9)' ) as P6_NINO,
@.myxml.value('(CodingNoticesP6P6B/WorksNumber)[1]' , 'varchar(35)' )
as P6_WorksNumber,
@.myxml.value('(CodingNoticesP6P6B/EffectiveDate)[1]' , 'datetime' ) as
P6_EffectiveDate,
@.myxml.value('(CodingNoticesP6P6B/@.FormType)[1]' , 'varchar(3)' ) as
P6_FormType,
@.myxml.value('(CodingNoticesP6P6B/CodingUpdate/TaxCode)[1]' ,
'varchar(5)' ) as P6_TaxCode,
@.myxml.value('(CodingNoticesP6P6B/CodingUpdate/TaxCode/@.W1Month1Indicator
)[1]' , 'char(1)' ) as P6_W1Month1Indicator,
@.myxml.value('(CodingNoticesP6P6B/CodingUpdate/TotalPreviousPay)[1]'
, 'numeric(9,2)' ) as P6_TotalPreviousPay,
@.myxml.value('(CodingNoticesP6P6B/CodingUpdate/TotalPreviousPay/@.Currency)[1
]' , 'char(3)' ) as P6_TotalPreviousPay_Currency,
@.myxml.value('(CodingNoticesP6P6B/CodingUpdate/TotalPreviousTax)[1]' ,
'numeric(9,2)' ) as P6_TotalPreviousTax,
@.myxml.value('(CodingNoticesP6P6B/CodingUpdate/TotalPreviousTax/@.Currency)[1
]' , 'char(3)' ) as P6_TotalPreviousTax_Currency
thanksKarl,
The query processor only has several builtin namespace/prefix mappings
builtin. You need to supply these additional mappings since each xml instanc
e
that the query runs against may have their own namespace/prefix mappings.
There are two way to create this mapping. One is to use the "declare" clause
in a value or query method. For example:
select @.myxml.value('declare namespace ns1 =
"http://www.govtalk.gov.uk/taxation/CodingNoticesP6P6B/2";
(/ns1:CodingNoticesP6P6B/@.IssueDate)[1]' , 'datetime' ) as P6_IssueDate
But, since you have many value methods, you can also use the WITH
XMLNAMESPACES clause before the SELECT statement. This defines
namespace/prefix mappings for all value and query methods within a single
select statement. See BOL for me details.
WITH XMLNAMESPACES
('http://www.govtalk.gov.uk/taxation/CodingNoticesP6P6B/2' as ns1)
select @.myxml.value('(/ns1:CodingNoticesP6P6B/@.IssueDate)[1]' , 'datetime' )
as P6_IssueDate
You will need to place the correct prefix in front of each element name
within your xpath expressions. In the example you gave, all the elements use
the default namespace.
Note: You should place a semicolon after the statement that precedes the
WITH clause. In your case, the set statement should have ; at the end.
Regards,
Galex Yen
"Karl Prosser" wrote:

> i have some XML, that i am using .value to get the values from, when the
> namespa e is included in the XML(as below ) it doesn't work (everything co
mes
> as NULL), however if i manually take out toe xmlns part of the xml, it wor
ks
> fine. i'm just wondering how i can change my .value code to work with the
> namespace..
> declare @.myxml XML
> set @.myxml =
> '<CodingNoticesP6P6B
> xmlns="http://www.govtalk.gov.uk/taxation/CodingNoticesP6P6B/2"
> xmlns:gt="http://www.govtalk.gov.uk/CM/core"
> xmlns:gms="http://www.govtalk.gov.uk/CM/gms-xs" IssueDate="2005-09-01"
> TaxYearEnd="2005" SequenceNumber="1" FormType="P6B">
> <EmployerRef>961/1791574</EmployerRef>
> <Name>
> <Title>AA</Title>
> <Forename>AA</Forename>
> <Surname>AA</Surname>
> </Name>
> <NINO>AA000000A</NINO>
> <WorksNumber>WN001</WorksNumber>
> <EffectiveDate>2005-09-01Z</EffectiveDate>
> <CodingUpdate>
> <TaxCode>10A</TaxCode>
> <TotalPreviousPay Currency="GBP">5000.00</TotalPreviousPay>
> <TotalPreviousTax Currency="GBP">1000.00</TotalPreviousTax>
> </CodingUpdate>
> </CodingNoticesP6P6B>'
> --doesn't work when namespace is in, need to make the query work with the
> namespace somehow
> select @.myxml.value('(/CodingNoticesP6P6B/@.IssueDate)[1]' , 'datetime' ) a
s
> P6_IssueDate,
> @.myxml.value('(/CodingNoticesP6P6B/@.TaxYearEnd)[1]', 'char(4)' ) as
> P6_TaxYearEnd,
> @.myxml.value('(CodingNoticesP6P6B/@.SequenceNumber)[1]' , 'int' ) as
> P6_SequenceNumber,
> @.myxml.value('(CodingNoticesP6P6B/EmployerRef)[1]' , 'varchar(14)' ) A
S
> P6_EmployerRef,
> @.myxml.value('(CodingNoticesP6P6B/Name/Title)[1]' , 'varchar(4)' ) as
> P6_Title,
> @.myxml.value('(CodingNoticesP6P6B/Name/Forename)[1]' , 'varchar(35)' )
> as P6_ForeName,
> @.myxml.value('(CodingNoticesP6P6B/Name/Forename)[2]' , 'varchar(35)' )
> as P6_ForeName_2,
> @.myxml.value('(CodingNoticesP6P6B/Name/Surname)[1]' , 'varchar(35)' )
as
> P6_Surname,
> @.myxml.value('(CodingNoticesP6P6B/NINO)[1]' , 'char(9)' ) as P6_NINO,
> @.myxml.value('(CodingNoticesP6P6B/WorksNumber)[1]' , 'varchar(35)' )
> as P6_WorksNumber,
> @.myxml.value('(CodingNoticesP6P6B/EffectiveDate)[1]' , 'datetime' ) as
> P6_EffectiveDate,
> @.myxml.value('(CodingNoticesP6P6B/@.FormType)[1]' , 'varchar(3)' ) as
> P6_FormType,
> @.myxml.value('(CodingNoticesP6P6B/CodingUpdate/TaxCode)[1]' ,
> 'varchar(5)' ) as P6_TaxCode,
> @.myxml.value('(CodingNoticesP6P6B/CodingUpdate/TaxCode/@.W1Month1Indicat
or)[1]' , 'char(1)' ) as P6_W1Month1Indicator,
> @.myxml.value('(CodingNoticesP6P6B/CodingUpdate/TotalPreviousPay)[1]
'
> , 'numeric(9,2)' ) as P6_TotalPreviousPay,
> @.myxml.value('(CodingNoticesP6P6B/CodingUpdate/TotalPreviousPay/@.Currency)
[1]' , 'char(3)' ) as P6_TotalPreviousPay_Currency,
> @.myxml.value('(CodingNoticesP6P6B/CodingUpdate/TotalPreviousTax)[1]' ,
> 'numeric(9,2)' ) as P6_TotalPreviousTax,
> @.myxml.value('(CodingNoticesP6P6B/CodingUpdate/TotalPreviousTax/@.Currency)
[1]' , 'char(3)' ) as P6_TotalPreviousTax_Currency
> thanks|||thank you very much, i appreciate your help and feedback
here is an example of what i am doing now.
WITH XMLNAMESPACES
('http://www.govtalk.gov.uk/taxation/CodingNoticesP6P6B/2' as p6)
select @.myxml.value('(/p6:CodingNoticesP6P6B/@.IssueDate)[1]' , 'datetime' )
as P6_IssueDate,
@.myxml.value('(/p6:CodingNoticesP6P6B/@.TaxYearEnd)[1]', 'char(4)' ) as
P6_TaxYearEnd,
@.myxml.value('(/p6:CodingNoticesP6P6B/@.SequenceNumber)[1]' , 'int' ) as
P6_SequenceNumber,
@.myxml.value('(/p6:CodingNoticesP6P6B/p6:EmployerRef)[1]' ,
'varchar(14)' ) AS P6_EmployerRef,
@.myxml.value('(/p6:CodingNoticesP6P6B/p6:Name/p6:Title)[1]' ,
'varchar(4)' ) as P6_Title,
i noticed that in multilevel parts i have to put p6 at every level (i.e
codingbase, name and title, as shown above for it to work.. Is this Normal?
shouldn't it be able to know from the first one? Of course i can get it
working with the p6 and that makes me happy. jUst curious about the details
thanks again|||Karl,
The reason the prefix must be specified over and over again is simply an
artifact of xml+namespaces. An xml document may have elements/attributes fro
m
multiple namespaces and within those namespace there could be name
collisions. An element name is not really just the name part, it is also the
namespace it is defined in.
That being said, in your case, since only one namespace is being used, you
can define the default namespace (no prefix) throughout your query. This can
be done like this:
WITH XMLNAMESPACES (DEFAULT 'http://your.uri')
SELECT xmlcol.value('/foo[1]', 'int')
In this case '/foo' really means {http://your.uri}:foo. Note: this isn't
really xml syntax, just an illustration.
Regards,
Galex Yen
"Karl Prosser" wrote:

> thank you very much, i appreciate your help and feedback
> here is an example of what i am doing now.
> WITH XMLNAMESPACES
> ('http://www.govtalk.gov.uk/taxation/CodingNoticesP6P6B/2' as p6)
> select @.myxml.value('(/p6:CodingNoticesP6P6B/@.IssueDate)[1]' , 'datetime'
)
> as P6_IssueDate,
> @.myxml.value('(/p6:CodingNoticesP6P6B/@.TaxYearEnd)[1]', 'char(4)' ) as
> P6_TaxYearEnd,
> @.myxml.value('(/p6:CodingNoticesP6P6B/@.SequenceNumber)[1]' , 'int' ) a
s
> P6_SequenceNumber,
> @.myxml.value('(/p6:CodingNoticesP6P6B/p6:EmployerRef)[1]' ,
> 'varchar(14)' ) AS P6_EmployerRef,
> @.myxml.value('(/p6:CodingNoticesP6P6B/p6:Name/p6:Title)[1]' ,
> 'varchar(4)' ) as P6_Title,
> i noticed that in multilevel parts i have to put p6 at every level (i.e
> codingbase, name and title, as shown above for it to work.. Is this Normal
?
> shouldn't it be able to know from the first one? Of course i can get it
> working with the p6 and that makes me happy. jUst curious about the detail
s
> thanks again

@xml.value against XML data with namespaces

i have some XML, that i am using .value to get the values from, when the
namespa e is included in the XML(as below ) it doesn't work (everything comes
as NULL), however if i manually take out toe xmlns part of the xml, it works
fine. i'm just wondering how i can change my .value code to work with the
namespace..
declare @.myxml XML
set @.myxml =
'<CodingNoticesP6P6B
xmlns="http://www.govtalk.gov.uk/taxation/CodingNoticesP6P6B/2"
xmlns:gt="http://www.govtalk.gov.uk/CM/core"
xmlns:gms="http://www.govtalk.gov.uk/CM/gms-xs" IssueDate="2005-09-01"
TaxYearEnd="2005" SequenceNumber="1" FormType="P6B">
<EmployerRef>961/1791574</EmployerRef>
<Name>
<Title>AA</Title>
<Forename>AA</Forename>
<Surname>AA</Surname>
</Name>
<NINO>AA000000A</NINO>
<WorksNumber>WN001</WorksNumber>
<EffectiveDate>2005-09-01Z</EffectiveDate>
<CodingUpdate>
<TaxCode>10A</TaxCode>
<TotalPreviousPay Currency="GBP">5000.00</TotalPreviousPay>
<TotalPreviousTax Currency="GBP">1000.00</TotalPreviousTax>
</CodingUpdate>
</CodingNoticesP6P6B>'
--doesn't work when namespace is in, need to make the query work with the
namespace somehow
select @.myxml.value('(/CodingNoticesP6P6B/@.IssueDate)[1]' , 'datetime' ) as
P6_IssueDate,
@.myxml.value('(/CodingNoticesP6P6B/@.TaxYearEnd)[1]', 'char(4)' ) as
P6_TaxYearEnd,
@.myxml.value('(CodingNoticesP6P6B/@.SequenceNumber)[1]' , 'int' ) as
P6_SequenceNumber,
@.myxml.value('(CodingNoticesP6P6B/EmployerRef)[1]' , 'varchar(14)' ) AS
P6_EmployerRef,
@.myxml.value('(CodingNoticesP6P6B/Name/Title)[1]' , 'varchar(4)' ) as
P6_Title,
@.myxml.value('(CodingNoticesP6P6B/Name/Forename)[1]' , 'varchar(35)' )
as P6_ForeName,
@.myxml.value('(CodingNoticesP6P6B/Name/Forename)[2]' , 'varchar(35)' )
as P6_ForeName_2,
@.myxml.value('(CodingNoticesP6P6B/Name/Surname)[1]' , 'varchar(35)' ) as
P6_Surname,
@.myxml.value('(CodingNoticesP6P6B/NINO)[1]' , 'char(9)' ) as P6_NINO,
@.myxml.value('(CodingNoticesP6P6B/WorksNumber)[1]' , 'varchar(35)' )
as P6_WorksNumber,
@.myxml.value('(CodingNoticesP6P6B/EffectiveDate)[1]' , 'datetime' ) as
P6_EffectiveDate,
@.myxml.value('(CodingNoticesP6P6B/@.FormType)[1]' , 'varchar(3)' ) as
P6_FormType,
@.myxml.value('(CodingNoticesP6P6B/CodingUpdate/TaxCode)[1]' ,
'varchar(5)' ) as P6_TaxCode,
@.myxml.value('(CodingNoticesP6P6B/CodingUpdate/TaxCode/@.Week1Month1Indicator)[1]' , 'char(1)' ) as P6_Week1Month1Indicator,
@.myxml.value('(CodingNoticesP6P6B/CodingUpdate/TotalPreviousPay)[1]'
, 'numeric(9,2)' ) as P6_TotalPreviousPay,
@.myxml.value('(CodingNoticesP6P6B/CodingUpdate/TotalPreviousPay/@.Currency)[1]' , 'char(3)' ) as P6_TotalPreviousPay_Currency,
@.myxml.value('(CodingNoticesP6P6B/CodingUpdate/TotalPreviousTax)[1]' ,
'numeric(9,2)' ) as P6_TotalPreviousTax,
@.myxml.value('(CodingNoticesP6P6B/CodingUpdate/TotalPreviousTax/@.Currency)[1]' , 'char(3)' ) as P6_TotalPreviousTax_Currency
thanks
Karl,
The query processor only has several builtin namespace/prefix mappings
builtin. You need to supply these additional mappings since each xml instance
that the query runs against may have their own namespace/prefix mappings.
There are two way to create this mapping. One is to use the "declare" clause
in a value or query method. For example:
select @.myxml.value('declare namespace ns1 =
"http://www.govtalk.gov.uk/taxation/CodingNoticesP6P6B/2";
(/ns1:CodingNoticesP6P6B/@.IssueDate)[1]' , 'datetime' ) as P6_IssueDate
But, since you have many value methods, you can also use the WITH
XMLNAMESPACES clause before the SELECT statement. This defines
namespace/prefix mappings for all value and query methods within a single
select statement. See BOL for me details.
WITH XMLNAMESPACES
('http://www.govtalk.gov.uk/taxation/CodingNoticesP6P6B/2' as ns1)
select @.myxml.value('(/ns1:CodingNoticesP6P6B/@.IssueDate)[1]' , 'datetime' )
as P6_IssueDate
You will need to place the correct prefix in front of each element name
within your xpath expressions. In the example you gave, all the elements use
the default namespace.
Note: You should place a semicolon after the statement that precedes the
WITH clause. In your case, the set statement should have ; at the end.
Regards,
Galex Yen
"Karl Prosser" wrote:

> i have some XML, that i am using .value to get the values from, when the
> namespa e is included in the XML(as below ) it doesn't work (everything comes
> as NULL), however if i manually take out toe xmlns part of the xml, it works
> fine. i'm just wondering how i can change my .value code to work with the
> namespace..
> declare @.myxml XML
> set @.myxml =
> '<CodingNoticesP6P6B
> xmlns="http://www.govtalk.gov.uk/taxation/CodingNoticesP6P6B/2"
> xmlns:gt="http://www.govtalk.gov.uk/CM/core"
> xmlns:gms="http://www.govtalk.gov.uk/CM/gms-xs" IssueDate="2005-09-01"
> TaxYearEnd="2005" SequenceNumber="1" FormType="P6B">
> <EmployerRef>961/1791574</EmployerRef>
> <Name>
> <Title>AA</Title>
> <Forename>AA</Forename>
> <Surname>AA</Surname>
> </Name>
> <NINO>AA000000A</NINO>
> <WorksNumber>WN001</WorksNumber>
> <EffectiveDate>2005-09-01Z</EffectiveDate>
> <CodingUpdate>
> <TaxCode>10A</TaxCode>
> <TotalPreviousPay Currency="GBP">5000.00</TotalPreviousPay>
> <TotalPreviousTax Currency="GBP">1000.00</TotalPreviousTax>
> </CodingUpdate>
> </CodingNoticesP6P6B>'
> --doesn't work when namespace is in, need to make the query work with the
> namespace somehow
> select @.myxml.value('(/CodingNoticesP6P6B/@.IssueDate)[1]' , 'datetime' ) as
> P6_IssueDate,
> @.myxml.value('(/CodingNoticesP6P6B/@.TaxYearEnd)[1]', 'char(4)' ) as
> P6_TaxYearEnd,
> @.myxml.value('(CodingNoticesP6P6B/@.SequenceNumber)[1]' , 'int' ) as
> P6_SequenceNumber,
> @.myxml.value('(CodingNoticesP6P6B/EmployerRef)[1]' , 'varchar(14)' ) AS
> P6_EmployerRef,
> @.myxml.value('(CodingNoticesP6P6B/Name/Title)[1]' , 'varchar(4)' ) as
> P6_Title,
> @.myxml.value('(CodingNoticesP6P6B/Name/Forename)[1]' , 'varchar(35)' )
> as P6_ForeName,
> @.myxml.value('(CodingNoticesP6P6B/Name/Forename)[2]' , 'varchar(35)' )
> as P6_ForeName_2,
> @.myxml.value('(CodingNoticesP6P6B/Name/Surname)[1]' , 'varchar(35)' ) as
> P6_Surname,
> @.myxml.value('(CodingNoticesP6P6B/NINO)[1]' , 'char(9)' ) as P6_NINO,
> @.myxml.value('(CodingNoticesP6P6B/WorksNumber)[1]' , 'varchar(35)' )
> as P6_WorksNumber,
> @.myxml.value('(CodingNoticesP6P6B/EffectiveDate)[1]' , 'datetime' ) as
> P6_EffectiveDate,
> @.myxml.value('(CodingNoticesP6P6B/@.FormType)[1]' , 'varchar(3)' ) as
> P6_FormType,
> @.myxml.value('(CodingNoticesP6P6B/CodingUpdate/TaxCode)[1]' ,
> 'varchar(5)' ) as P6_TaxCode,
> @.myxml.value('(CodingNoticesP6P6B/CodingUpdate/TaxCode/@.Week1Month1Indicator)[1]' , 'char(1)' ) as P6_Week1Month1Indicator,
> @.myxml.value('(CodingNoticesP6P6B/CodingUpdate/TotalPreviousPay)[1]'
> , 'numeric(9,2)' ) as P6_TotalPreviousPay,
> @.myxml.value('(CodingNoticesP6P6B/CodingUpdate/TotalPreviousPay/@.Currency)[1]' , 'char(3)' ) as P6_TotalPreviousPay_Currency,
> @.myxml.value('(CodingNoticesP6P6B/CodingUpdate/TotalPreviousTax)[1]' ,
> 'numeric(9,2)' ) as P6_TotalPreviousTax,
> @.myxml.value('(CodingNoticesP6P6B/CodingUpdate/TotalPreviousTax/@.Currency)[1]' , 'char(3)' ) as P6_TotalPreviousTax_Currency
> thanks
|||thank you very much, i appreciate your help and feedback
here is an example of what i am doing now.
WITH XMLNAMESPACES
('http://www.govtalk.gov.uk/taxation/CodingNoticesP6P6B/2' as p6)
select @.myxml.value('(/p6:CodingNoticesP6P6B/@.IssueDate)[1]' , 'datetime' )
as P6_IssueDate,
@.myxml.value('(/p6:CodingNoticesP6P6B/@.TaxYearEnd)[1]', 'char(4)' ) as
P6_TaxYearEnd,
@.myxml.value('(/p6:CodingNoticesP6P6B/@.SequenceNumber)[1]' , 'int' ) as
P6_SequenceNumber,
@.myxml.value('(/p6:CodingNoticesP6P6B/p6:EmployerRef)[1]' ,
'varchar(14)' ) AS P6_EmployerRef,
@.myxml.value('(/p6:CodingNoticesP6P6B/p6:Name/p6:Title)[1]' ,
'varchar(4)' ) as P6_Title,
i noticed that in multilevel parts i have to put p6 at every level (i.e
codingbase, name and title, as shown above for it to work.. Is this Normal?
shouldn't it be able to know from the first one? Of course i can get it
working with the p6 and that makes me happy. jUst curious about the details
thanks again
|||Karl,
The reason the prefix must be specified over and over again is simply an
artifact of xml+namespaces. An xml document may have elements/attributes from
multiple namespaces and within those namespace there could be name
collisions. An element name is not really just the name part, it is also the
namespace it is defined in.
That being said, in your case, since only one namespace is being used, you
can define the default namespace (no prefix) throughout your query. This can
be done like this:
WITH XMLNAMESPACES (DEFAULT 'http://your.uri')
SELECT xmlcol.value('/foo[1]', 'int')
In this case '/foo' really means {http://your.uri}:foo. Note: this isn't
really xml syntax, just an illustration.
Regards,
Galex Yen
"Karl Prosser" wrote:

> thank you very much, i appreciate your help and feedback
> here is an example of what i am doing now.
> WITH XMLNAMESPACES
> ('http://www.govtalk.gov.uk/taxation/CodingNoticesP6P6B/2' as p6)
> select @.myxml.value('(/p6:CodingNoticesP6P6B/@.IssueDate)[1]' , 'datetime' )
> as P6_IssueDate,
> @.myxml.value('(/p6:CodingNoticesP6P6B/@.TaxYearEnd)[1]', 'char(4)' ) as
> P6_TaxYearEnd,
> @.myxml.value('(/p6:CodingNoticesP6P6B/@.SequenceNumber)[1]' , 'int' ) as
> P6_SequenceNumber,
> @.myxml.value('(/p6:CodingNoticesP6P6B/p6:EmployerRef)[1]' ,
> 'varchar(14)' ) AS P6_EmployerRef,
> @.myxml.value('(/p6:CodingNoticesP6P6B/p6:Name/p6:Title)[1]' ,
> 'varchar(4)' ) as P6_Title,
> i noticed that in multilevel parts i have to put p6 at every level (i.e
> codingbase, name and title, as shown above for it to work.. Is this Normal?
> shouldn't it be able to know from the first one? Of course i can get it
> working with the p6 and that makes me happy. jUst curious about the details
> thanks again

@@Trancount Error

Take a look a the sql code below. Can anyone explain why @.@.trancount increased to 2 after the 2nd print statement?

SETANSI_DEFAULTSON

GO

PRINT@.@.TRANCOUNT

GO

BEGINTRANSACTION

PRINT@.@.TRANCOUNT

GO

Here is the output I got

0

2

With the ANSI_DEFAULTS you also enable IMPLICIT_TRANSACTIONS. So executing a BEGIN TRAN actually starts a second transaction because your implicit transaction is 'open' although it only returns 1 for @.@.TRANCOUNT when you actually issue a query.

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

@@server_name

Should it concern me that the code below returns Null? I simply was hoping
to get the name of the server as a string ...
select @.@.SERVERNAME
Thanks in advance.
Mark
Hi
Run sp_dropserver and then sp_addserver against the server.
Master DB does not have a valid value for the server. This can occur on
servers where Master DB was restored, or upgraded or the server name was
changed.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Mark" <Mark@.nowhere.com> wrote in message
news:eJZOBVWDFHA.3256@.tk2msftngp13.phx.gbl...
> Should it concern me that the code below returns Null? I simply was
hoping
> to get the name of the server as a string ...
> select @.@.SERVERNAME
> Thanks in advance.
> Mark
>

@@server_name

Should it concern me that the code below returns Null' I simply was hoping
to get the name of the server as a string ...
select @.@.SERVERNAME
Thanks in advance.
MarkHi
Run sp_dropserver and then sp_addserver against the server.
Master DB does not have a valid value for the server. This can occur on
servers where Master DB was restored, or upgraded or the server name was
changed.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Mark" <Mark@.nowhere.com> wrote in message
news:eJZOBVWDFHA.3256@.tk2msftngp13.phx.gbl...
> Should it concern me that the code below returns Null' I simply was
hoping
> to get the name of the server as a string ...
> select @.@.SERVERNAME
> Thanks in advance.
> Mark
>

@@server_name

Should it concern me that the code below returns Null' I simply was hoping
to get the name of the server as a string ...
select @.@.SERVERNAME
Thanks in advance.
MarkHi
Run sp_dropserver and then sp_addserver against the server.
Master DB does not have a valid value for the server. This can occur on
servers where Master DB was restored, or upgraded or the server name was
changed.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Mark" <Mark@.nowhere.com> wrote in message
news:eJZOBVWDFHA.3256@.tk2msftngp13.phx.gbl...
> Should it concern me that the code below returns Null' I simply was
hoping
> to get the name of the server as a string ...
> select @.@.SERVERNAME
> Thanks in advance.
> Mark
>

@@ROWCOUNT Support?

I am trying to work a statement like the below - but am getting an error - I assume that @.@.ROWCOUNT is not supported?

UPDATE MobileInvoiceImport SET OrderDetailID = '123', QuantityReceived = '66' WHERE OrderDetailID = '123'

IF @.@.ROWCOUNT = 0

INSERT INTO MobileInvoiceImport (OrderDetailID, QuantityReceived) VALUES('123','1')

there is no support for @.@.ROWCOUNT but when you ExecuteNonQuery() on your insert command, the return value is the number of rows affected. you cannot batch together successive statements with SQL Mobile, so using this value in managed code is going to have to be your conditional value anyway.

Darren