OpenXML Identity Insert: February 2012

Saturday, February 25, 2012

Operating System Memory. How much?

I currently am looking for a document from microsoft (or close to it)
that tells me that I should have X amount of memory set aside for the
OS for 16 and/or 32 gig memory configurations.
This is also one of those "my boss is asking me for a docuemnt to
support what I'm telling him. things"
this is in ref to SQL/AWE.
On Oct 5, 9:00 am, shawncr...@.yahoo.com wrote:
> I currently am looking for a document from microsoft (or close to it)
> that tells me that I should have X amount of memory set aside for the
> OS for 16 and/or 32 gig memory configurations.
> This is also one of those "my boss is asking me for a docuemnt to
> support what I'm telling him. things"

Operating System Memory. How much?

I currently am looking for a document from microsoft (or close to it)
that tells me that I should have X amount of memory set aside for the
OS for 16 and/or 32 gig memory configurations.
This is also one of those "my boss is asking me for a docuemnt to
support what I'm telling him. things"this is in ref to SQL/AWE.
On Oct 5, 9:00 am, shawncr...@.yahoo.com wrote:
> I currently am looking for a document from microsoft (or close to it)
> that tells me that I should have X amount of memory set aside for the
> OS for 16 and/or 32 gig memory configurations.
> This is also one of those "my boss is asking me for a docuemnt to
> support what I'm telling him. things"

Operating System Memory. How much?

I currently am looking for a document from microsoft (or close to it)
that tells me that I should have X amount of memory set aside for the
OS for 16 and/or 32 gig memory configurations.
This is also one of those "my boss is asking me for a docuemnt to
support what I'm telling him. things"this is in ref to SQL/AWE.
On Oct 5, 9:00 am, shawncr...@.yahoo.com wrote:
> I currently am looking for a document from microsoft (or close to it)
> that tells me that I should have X amount of memory set aside for the
> OS for 16 and/or 32 gig memory configurations.
> This is also one of those "my boss is asking me for a docuemnt to
> support what I'm telling him. things"

Operating system error code 3(The system cannot find the path specified.).

Hi All,

I use Bulk insert to put data to myTable.
When the SQL server is in local machin, it works well. But when I put
the data in a sql server situated not locally, then I get a error
message like this:

Could not bulk insert because file 'C:\Data\2003
txtfiles\abif_20031130.txt' could not be opened. Operating system
error code 3(The system cannot find the path specified.).

BULK INSERT myTable
FROM 'C:\Data\2003 txtfiles\abif_20031130.txt'
with (
-- codepage = ' + char(39) + 'ACP' + char(39) + ',
fieldterminator = ';',
rowterminator = '\n',
keepnulls,
maxerrors=0)

Someone can explan me what the error shows up

Thanks in advance
- Loi -The location of the file is relative to the SQL Server on which the BULK
INSERT command runs. To import a file from another machine, specify an UNC
path rather than use drive letters. For example:

BULK INSERT myTable
FROM '\\MyOtherServer\MyShare\Data\2003 txtfiles\abif_20031130.txt'

Note that the SQL Server service account needs permissions to the share.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Loi" <votanloi@.hotmail.com> wrote in message
news:af9512d5.0406030313.73ed644b@.posting.google.c om...
> Hi All,
> I use Bulk insert to put data to myTable.
> When the SQL server is in local machin, it works well. But when I put
> the data in a sql server situated not locally, then I get a error
> message like this:
> Could not bulk insert because file 'C:\Data\2003
> txtfiles\abif_20031130.txt' could not be opened. Operating system
> error code 3(The system cannot find the path specified.).
> BULK INSERT myTable
> FROM 'C:\Data\2003 txtfiles\abif_20031130.txt'
> with (
> -- codepage = ' + char(39) + 'ACP' + char(39) + ',
> fieldterminator = ';',
> rowterminator = '\n',
> keepnulls,
> maxerrors=0)
> Someone can explan me what the error shows up
> Thanks in advance
> - Loi -|||Hi Dan,
Thanks for your reply.
It does not work yet.
A next question:
SQL Server service account needs permissions to the share.
In my case SQL server is setup following:
In the security tab: Authentication - SQL server and windows.
Startup sevice account - system account.
Can somebody give me any hints

Regards
- Loi -

"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message news:<laEvc.20183$Tn6.12968@.newsread1.news.pas.earthlink .net>...
> The location of the file is relative to the SQL Server on which the BULK
> INSERT command runs. To import a file from another machine, specify an UNC
> path rather than use drive letters. For example:
> BULK INSERT myTable
> FROM '\\MyOtherServer\MyShare\Data\2003 txtfiles\abif_20031130.txt'
> Note that the SQL Server service account needs permissions to the share.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Loi" <votanloi@.hotmail.com> wrote in message
> news:af9512d5.0406030313.73ed644b@.posting.google.c om...
> > Hi All,
> > I use Bulk insert to put data to myTable.
> > When the SQL server is in local machin, it works well. But when I put
> > the data in a sql server situated not locally, then I get a error
> > message like this:
> > Could not bulk insert because file 'C:\Data\2003
> > txtfiles\abif_20031130.txt' could not be opened. Operating system
> > error code 3(The system cannot find the path specified.).
> > BULK INSERT myTable
> > FROM 'C:\Data\2003 txtfiles\abif_20031130.txt'
> > with (
> > -- codepage = ' + char(39) + 'ACP' + char(39) + ',
> > fieldterminator = ';',
> > rowterminator = '\n',
> > keepnulls,
> > maxerrors=0)
> > Someone can explan me what the error shows up
> > Thanks in advance
> > - Loi -|||> Startup sevice account - system account.

Try using a domain account rather than the local system account and ensure
the account has permissions to the share.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Loi Tan Vo" <loi.tan.vo@.fnh.no> wrote in message
news:73f04281.0406040358.5a342f93@.posting.google.c om...
> Hi Dan,
> Thanks for your reply.
> It does not work yet.
> A next question:
> SQL Server service account needs permissions to the share.
> In my case SQL server is setup following:
> In the security tab: Authentication - SQL server and windows.
> Startup sevice account - system account.
> Can somebody give me any hints
> Regards
> - Loi -
> "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:<laEvc.20183$Tn6.12968@.newsread1.news.pas.earthlink .net>...
> > The location of the file is relative to the SQL Server on which the BULK
> > INSERT command runs. To import a file from another machine, specify an
UNC
> > path rather than use drive letters. For example:
> > BULK INSERT myTable
> > FROM '\\MyOtherServer\MyShare\Data\2003 txtfiles\abif_20031130.txt'
> > Note that the SQL Server service account needs permissions to the share.
> > --
> > Hope this helps.
> > Dan Guzman
> > SQL Server MVP
> > "Loi" <votanloi@.hotmail.com> wrote in message
> > news:af9512d5.0406030313.73ed644b@.posting.google.c om...
> > > Hi All,
> > > > I use Bulk insert to put data to myTable.
> > > When the SQL server is in local machin, it works well. But when I put
> > > the data in a sql server situated not locally, then I get a error
> > > message like this:
> > > > Could not bulk insert because file 'C:\Data\2003
> > > txtfiles\abif_20031130.txt' could not be opened. Operating system
> > > error code 3(The system cannot find the path specified.).
> > > > BULK INSERT myTable
> > > FROM 'C:\Data\2003 txtfiles\abif_20031130.txt'
> > > with (
> > > -- codepage = ' + char(39) + 'ACP' + char(39) + ',
> > > fieldterminator = ';',
> > > rowterminator = '\n',
> > > keepnulls,
> > > maxerrors=0)
> > > > Someone can explan me what the error shows up
> > > > Thanks in advance
> > > - Loi -

Operating system error 87 on log file

I am getting an error on the Eventlog that says :17053:
\\server\mssql7\data\app_log.ldf: Operating system error 87
(The paremeter is incorrect) encountered.
This error used to be immediately followed by an
error "The log file for database is full". Then I created
a maintenance plan that backs up the Transaction log every
hour. Now I am getting a "fcb::close-flush:Operating
system error 87".
To me it seems like that SqlServer is having problem
writing to this file. The files are on a RAID 5 disk.
Could it be a problem with RAID? Should I create a new Log
file and delete the old one?
Any help will be greatly appreciated.
Thanks.Did you setup Auto-Growth for the transaction log file?
>--Original Message--
>I am getting an error on the Eventlog that says :17053:
>\\server\mssql7\data\app_log.ldf: Operating system error
87
>(The paremeter is incorrect) encountered.
>This error used to be immediately followed by an
>error "The log file for database is full". Then I created
>a maintenance plan that backs up the Transaction log
every
>hour. Now I am getting a "fcb::close-flush:Operating
>system error 87".
>To me it seems like that SqlServer is having problem
>writing to this file. The files are on a RAID 5 disk.
>Could it be a problem with RAID? Should I create a new
Log
>file and delete the old one?
>Any help will be greatly appreciated.
>Thanks.
>.
>|||Yes, Auto-Growth is set up. Initially it was set up to
grow by 10%.
The file size is only 1.24 MB, and it seems like it
consistently has 0.7 mb free in it. The application
developer insists that it does not need to be any bigger
than 2 MB, and does not need to grow any more than 10%.
I have recently set it up to grow by 1 MB. But that did
not help.
Also, the hourly backup is not creating any backup
file .TRN extension. Is it because it is so small?
>--Original Message--
>Did you setup Auto-Growth for the transaction log file?
>
>>--Original Message--
>>I am getting an error on the Eventlog that says :17053:
>>\\server\mssql7\data\app_log.ldf: Operating system error
>87
>>(The paremeter is incorrect) encountered.
>>This error used to be immediately followed by an
>>error "The log file for database is full". Then I
created
>>a maintenance plan that backs up the Transaction log
>every
>>hour. Now I am getting a "fcb::close-flush:Operating
>>system error 87".
>>To me it seems like that SqlServer is having problem
>>writing to this file. The files are on a RAID 5 disk.
>>Could it be a problem with RAID? Should I create a new
>Log
>>file and delete the old one?
>>Any help will be greatly appreciated.
>>Thanks.
>>.
>.
>

Operating system error 38(Reached the end of the file.) on file "C:\Data\myfile_log.LDF&quo

Hi,

I am facing a problem on a server which has raid 5 solution (3 disks), the raid controller went down 2 of the disks were off in the Bios.

We added the 3 disks to a different server identical in brand and architecture, the raid controller was able to reconfigure the virtual drive H:.

All files were there, we installed sql server 2005 on the new server, but when we tried to attach the database we got the error below:

TITLE: Microsoft SQL Server Management Studio

Attach database failed for Server 'myserver'. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Attach+database+Server&LinkId=20476

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

The operating system returned error 38(Reached the end of the file.) to SQL Server during a read at offset 0x00000000af0000 in file 'C:\Data\mylog_log.LDF'. Additional messages in the SQL Server error log and system event log may provide more detail. This is a severe system-level error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
Operating system error 38(Reached the end of the file.) on file "C:\Data\mylog_log.LDF" during ReadFileHdr.
Could not open new database 'mydb'. CREATE DATABASE is aborted. (Microsoft SQL Server, Error: 823)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=823&LinkId=20476

BUTTONS:

I tried the following steps but it always failed:

- create a new db with the same name of the lost db;

- put the db in emergency mode;

- stop sql service and replace the mdf file;

- start sql service;

- Run Dbcc checkdb('mydb')

we got the error below:

Msg 945, Level 14, State 2, Line 1

Database 'ism0506' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.

Any HELP please ?

Thanks,

Tarek Ghazali

Sql Server MVP

Well, the obvious question is where is your most recent backup of the database?

This situation doesn't look good. Losing 2/3 of a RAID 5 set means that you have lost a significant amount of data. Just because the file metada is there doesn't mean that the contents are there or intact. At a minimum, your log file is corrupt.

You can try CREATE DATABASE FOR ATTACH_REBUILD_LOG and see if you can get it going that way.

You WILL lose data, and most probably have inconsistencies due not being able to read the log file to do recovery.

Seriously restore from backup is your best option at this point.

|||

Hi,

The issue is that the client did not backup since 3 weeks (his mistake) and the only solution is to restore from the corrupted files that they have. I tried your solution but it did not work.

This is the error that i got when i ran (CREATE DATABASE FOR ATTACH_REBUILD_LOG ):

The log cannot be rebuilt because the database was not cleanly shut down.

Thanks for your reply,

Tarek Ghazali

SQL Server MVP

|||Try this undocumented stuff provided by Kevin [MS].

==========
1. Back up the .mdf/.ndf files at first!!!

2. Change the database context to Master and allow updates to system tables:

Use Master
Go
sp_configure 'allow updates', 1
reconfigure with override
Go

3. Set the database in Emergency (bypass recovery) mode:

select * from sysdatabases where name = '<db_name>'
-- note the value of the status column for later use in # 6
begin tran
update sysdatabases set status = 32768 where name = '<db_name>'
-- Verify one row is updated before committing
commit tran

4. Stop and restart SQL server.

5. Call DBCC REBUILD_LOG command to rebuild a "blank" log file based on the
suspected db.
The syntax for DBCC REBUILD_LOG is as follows:

DBCC rebuild_log('<db_name>','<log_filename>')

where <db_name> is the name of the database and <log_filename> is
the physical path to the new log file, not a logical file name. If you
do not
specify the full path, the new log is created in the Windows NT system
root
directory (by default, this is the Winnt\System32 directory).

6. Set the database in single-user mode and run DBCC CHECKDB to validate
physical consistency:

sp_dboption '<db_name>', 'single user', 'true'
DBCC checkdb('<db_name>')
Go
begin tran
update sysdatabases set status = <prior value> where name = '<db_name>'
-- verify one row is updated before committing
commit tran
Go

7. Turn off the updates to system tables by using:

sp_configure 'allow updates', 0
reconfigure with override
Go
============|||

Satya - none of that works in SQL Server 2005 (e.g. I removed the DBCC REBUILD_LOG command).

Tarek - you should call Product Support to help with recovering this.

Thanks

Operating system error 38(Reached the end of the file.) on file "C:\Data\myfile_log.LDF

Hi,

I am facing a problem on a server which has raid 5 solution (3 disks), the raid controller went down 2 of the disks were off in the Bios.

We added the 3 disks to a different server identical in brand and architecture, the raid controller was able to reconfigure the virtual drive H:.

All files were there, we installed sql server 2005 on the new server, but when we tried to attach the database we got the error below:

TITLE: Microsoft SQL Server Management Studio

Attach database failed for Server 'myserver'. (Microsoft.SqlServer.Smo)

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=823&LinkId=20476

BUTTONS:

I tried the following steps but it always failed:

- create a new db with the same name of the lost db;

- put the db in emergency mode;

- stop sql service and replace the mdf file;

- start sql service;

- Run Dbcc checkdb('mydb')

we got the error below:

Msg 945, Level 14, State 2, Line 1

Database 'ism0506' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.

Any HELP please ?

Thanks,

Tarek Ghazali

Sql Server MVP

Well, the obvious question is where is your most recent backup of the database?

You can try CREATE DATABASE FOR ATTACH_REBUILD_LOG and see if you can get it going that way.

You WILL lose data, and most probably have inconsistencies due not being able to read the log file to do recovery.

Seriously restore from backup is your best option at this point.

|||

Hi,

The issue is that the client did not backup since 3 weeks (his mistake) and the only solution is to restore from the corrupted files that they have. I tried your solution but it did not work.

This is the error that i got when i ran (CREATE DATABASE FOR ATTACH_REBUILD_LOG ):

The log cannot be rebuilt because the database was not cleanly shut down.

Thanks for your reply,

Tarek Ghazali

SQL Server MVP

|||Try this undocumented stuff provided by Kevin [MS].

==========
1. Back up the .mdf/.ndf files at first!!!

2. Change the database context to Master and allow updates to system tables:

Use Master
Go
sp_configure 'allow updates', 1
reconfigure with override
Go

3. Set the database in Emergency (bypass recovery) mode:

4. Stop and restart SQL server.

5. Call DBCC REBUILD_LOG command to rebuild a "blank" log file based on the
suspected db.
The syntax for DBCC REBUILD_LOG is as follows:

DBCC rebuild_log('<db_name>','<log_filename>')

6. Set the database in single-user mode and run DBCC CHECKDB to validate
physical consistency:

7. Turn off the updates to system tables by using:

sp_configure 'allow updates', 0
reconfigure with override
Go
============|||

Satya - none of that works in SQL Server 2005 (e.g. I removed the DBCC REBUILD_LOG command).

Tarek - you should call Product Support to help with recovering this.

Thanks

Operating system error 38(Reached the end of the file.) on file "C:\Data\myfile_log.LDF

Hi,

I am facing a problem on a server which has raid 5 solution (3 disks), the raid controller went down 2 of the disks were off in the Bios.

We added the 3 disks to a different server identical in brand and architecture, the raid controller was able to reconfigure the virtual drive H:.

All files were there, we installed sql server 2005 on the new server, but when we tried to attach the database we got the error below:

TITLE: Microsoft SQL Server Management Studio

Attach database failed for Server 'myserver'. (Microsoft.SqlServer.Smo)

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=823&LinkId=20476

BUTTONS:

I tried the following steps but it always failed:

- create a new db with the same name of the lost db;

- put the db in emergency mode;

- stop sql service and replace the mdf file;

- start sql service;

- Run Dbcc checkdb('mydb')

we got the error below:

Msg 945, Level 14, State 2, Line 1

Database 'ism0506' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.

Any HELP please ?

Thanks,

Tarek Ghazali

Sql Server MVP

Well, the obvious question is where is your most recent backup of the database?

You can try CREATE DATABASE FOR ATTACH_REBUILD_LOG and see if you can get it going that way.

You WILL lose data, and most probably have inconsistencies due not being able to read the log file to do recovery.

Seriously restore from backup is your best option at this point.

|||

Hi,

The issue is that the client did not backup since 3 weeks (his mistake) and the only solution is to restore from the corrupted files that they have. I tried your solution but it did not work.

This is the error that i got when i ran (CREATE DATABASE FOR ATTACH_REBUILD_LOG ):

The log cannot be rebuilt because the database was not cleanly shut down.

Thanks for your reply,

Tarek Ghazali

SQL Server MVP

|||Try this undocumented stuff provided by Kevin [MS].

==========
1. Back up the .mdf/.ndf files at first!!!

2. Change the database context to Master and allow updates to system tables:

Use Master
Go
sp_configure 'allow updates', 1
reconfigure with override
Go

3. Set the database in Emergency (bypass recovery) mode:

4. Stop and restart SQL server.

5. Call DBCC REBUILD_LOG command to rebuild a "blank" log file based on the
suspected db.
The syntax for DBCC REBUILD_LOG is as follows:

DBCC rebuild_log('<db_name>','<log_filename>')

6. Set the database in single-user mode and run DBCC CHECKDB to validate
physical consistency:

7. Turn off the updates to system tables by using:

sp_configure 'allow updates', 0
reconfigure with override
Go
============|||

Satya - none of that works in SQL Server 2005 (e.g. I removed the DBCC REBUILD_LOG command).

Tarek - you should call Product Support to help with recovering this.

Thanks

Operating System Error 2

What backup tool is being reference in the KB article below?
http://support.microsoft.com/kb/937683
I am getting this exact scenario trying to restore a backup and I am trying
to determine which backup tool is being reference.
SQL Writer Service
http://technet.microsoft.com/en-us/library/ms175536.aspx
hth
"billinny" wrote:

> What backup tool is being reference in the KB article below?
> http://support.microsoft.com/kb/937683
> I am getting this exact scenario trying to restore a backup and I am trying
> to determine which backup tool is being reference.

Operating System Error 2

What backup tool is being reference in the KB article below?
http://support.microsoft.com/kb/937683
I am getting this exact scenario trying to restore a backup and I am trying
to determine which backup tool is being reference.SQL Writer Service
http://technet.microsoft.com/en-us/library/ms175536.aspx
hth
"billinny" wrote:
> What backup tool is being reference in the KB article below?
> http://support.microsoft.com/kb/937683
> I am getting this exact scenario trying to restore a backup and I am trying
> to determine which backup tool is being reference.

Operating system error 1450(Insufficient system resources exist to complete the requested se

Hello!

Hopefully someone can help me.

I have scripts to refresh database as SQL daily jobs. (O.S is Win2K3 and SQL server 2000 and SP4) It was worked and I got the following message this morning from SQL error log.

Internal I/O request 0x5FDA3C50: Op: Read, pBuffer: 0x0D860000, Size: 65536, Position: 25534864896, RetryCount: 10, UMS: Internal: 0x483099C8, InternalHigh: 0x0, Offset: 0xF1FF1E00, OffsetHigh: 0x5, m_buf: 0x0D860000, m_len: 65536, m_actualBytes: 0, m_errcode: 1450, BackupFile: \\XAPROD12MASTER\XAPROD\XAPROD_db_200701290000.BAK

BackupMedium::ReportIoError: read failure on backup device '\\XAPROD12MASTER\XAPROD\XAPROD_db_200701290000.BAK'. Operating system error 1450(Insufficient system resources exist to complete the requested service.).

This sounds exactly like an OS bug that we saw a long time ago where the Windows Remote File cache was behaving poorly and running out of its own buffers.

The best approach is to take this to Windows and see if there's a patch.

Operating system error 112(There is not enough space on the disk.).

Hi!

While backing a SQL database onto another system after mapping the drive I get this error saying "Operating system error 112(There is not enough space on the disk.)..". There's a lot of space on the Destination server.

But when I back it up onto the same machine the backup's successfull.

I'm not even able to copy and paste the backed up file onto another machine. can someone help me out on this

The size of the DB is 5GB. Is it the problem with the size??

Thankyou For answering Satya and Loach...

I'm logged in with Sa user on the server and also logged on in administrative mode on both the machines (source and destination servers)

I think I've not communicated clearly I'll try and put in my query more eloborately.

1. We have a server which hosts the SQL server and the Database X installed on it. The size of the database when backedup is around 5 GB.

2. We have a backup server which has all the backed up files.

3. I've scheduled the backup using the SQL server agent (Scheduled in Sa user mode).

4. I've created a folder by name DB backup on the backup server and I've assigned access rights to it for administrator user.

5. I've mapped the DB backup folder as a network drive to the Server.

6. Now since DB backup folder acts as a drive in the actual server I scheduled the backup into that folder.

This Process worked successfully earlier, but then the size of the database was relatively smaller actually very small. That's one of the reason why I asked you earlier whether it was a problem with the size.

One thing is for sure In this process which I'm following I've logged on everywhere with administrative privilages.

Thanks for your co-operation once again.What account is used for SQL Server services (MSSQLSErver & SQLAgent)?
Use Domain account with ADMIN Privileges and try backup.|||Satya is correct.

Make sure the account has admin privelges on your SQL Server and the permissions necessary to write to the destination folder. It is not necessary, however, for the account to be a domain admin.|||You may also want to make sure there is no Quotas set up across your network or on the machine you are trying to access. Your Network Admin may be blocking your transfer just because of the size. If Windows is set yo stop the transfer over a certain size or bandwidth then SQL Server will report out of space even when you aren't

Operating system error 10038., An operation was attempted on something that is not a socke

Hi there
I have a SQL Server 7.0 error log full of the following events
Source OD
Operating system error 10038., An operation was attempted on something that is not a socket
an
Error: 17825, Severity: 17, State:
and
Could not close ListenOn connection.
My system is Windows 2000 Server with SP
Microsoft SQL Server 7.00 - 7.00.1063 (Intel X86)
Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4
Any help would be greatly appreciated
Regards
MattMatt,
Check if the article at
http://support.microsoft.com/default.aspx?scid=kb;en-us;109787 is helpful.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Matt" <anonymous@.discussions.microsoft.com> wrote in message
news:C1657807-D180-4A83-B5D2-603EDD6FB12A@.microsoft.com...
> Hi there,
> I have a SQL Server 7.0 error log full of the following events;
> Source ODS
> Operating system error 10038., An operation was attempted on something
that is not a socket.
> and
> Error: 17825, Severity: 17, State: 3
> and
> Could not close ListenOn connection..
> My system is Windows 2000 Server with SP4
> Microsoft SQL Server 7.00 - 7.00.1063 (Intel X86)
> Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
> Any help would be greatly appreciated.
> Regards,
> Matt

Operating system error 10038., An operation was attempted on something that is not a s

Hi there,
I have a SQL Server 7.0 error log full of the following events;
Source ODS
Operating system error 10038., An operation was attempted on something that
is not a socket.
and
Error: 17825, Severity: 17, State: 3
and
Could not close ListenOn connection..
My system is Windows 2000 Server with SP4
Microsoft SQL Server 7.00 - 7.00.1063 (Intel X86)
Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
Any help would be greatly appreciated.
Regards,
MattMatt,
Check if the article at
http://support.microsoft.com/defaul...kb;en-us;109787 is helpful.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Matt" <anonymous@.discussions.microsoft.com> wrote in message
news:C1657807-D180-4A83-B5D2-603EDD6FB12A@.microsoft.com...
> Hi there,
> I have a SQL Server 7.0 error log full of the following events;
> Source ODS
> Operating system error 10038., An operation was attempted on something
that is not a socket.
> and
> Error: 17825, Severity: 17, State: 3
> and
> Could not close ListenOn connection..
> My system is Windows 2000 Server with SP4
> Microsoft SQL Server 7.00 - 7.00.1063 (Intel X86)
> Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
> Any help would be greatly appreciated.
> Regards,
> Matt

Operating system error = 32(error not found).

Dear all,
i try to backup abc.bak(21G) TO remote Server every night. But the system
gave me the following msg:
BackupDiskFile::CreateMedia: Backup
device '\\bkupsvr\dump$\abc.bak' failed to
create. Operating system error = 32(error not found).
SVR OS & SQL VERSION: win2003 STD SVR R2 with SQL SVR 2005 STDHi
Your message indicates that the file is being used by another process. At a
guess this might be either be a problem with disc caching or Anti-virus/ITD
type of system.
John
"Jet Lai" wrote:
> Dear all,
> i try to backup abc.bak(21G) TO remote Server every night. But the system
> gave me the following msg:
> BackupDiskFile::CreateMedia: Backup
> device '\\bkupsvr\dump$\abc.bak' failed to
> create. Operating system error = 32(error not found).
> SVR OS & SQL VERSION: win2003 STD SVR R2 with SQL SVR 2005 STD
>|||thank john for your answer.
Actually i can backup small bak file(said around 1G), but bak file with 21G
will be failed!
I've excluded this bak file from Anti-Virus System, but how do i clear "
disc caching "? , please tell me!
thanks again!
"John Bell" wrote:
> Hi
> Your message indicates that the file is being used by another process. At a
> guess this might be either be a problem with disc caching or Anti-virus/ITD
> type of system.
> John
> "Jet Lai" wrote:
> > Dear all,
> > i try to backup abc.bak(21G) TO remote Server every night. But the system
> > gave me the following msg:
> > BackupDiskFile::CreateMedia: Backup
> > device '\\bkupsvr\dump$\abc.bak' failed to
> > create. Operating system error = 32(error not found).
> >
> > SVR OS & SQL VERSION: win2003 STD SVR R2 with SQL SVR 2005 STD
> >
> >|||Hi
You would need to know how the disc cache has been set up on the remote
server and (possibly) allow write through caching using the software supplied
by the manufacturer. Have you checked that the problem still occurs after
turning off the AV?
You may also want to check things like BIOS versions for the disc cache to
see if it is up-to-date.
Another thought, is that there seems quite a few posts relating to remote
backup issues which are due to a faulty network card/network. Make sure that
you don't have problems with the any cards and that you have sufficient
bandwidth between the two machines to cope with the load.
John
"Jet Lai" wrote:
> thank john for your answer.
> Actually i can backup small bak file(said around 1G), but bak file with 21G
> will be failed!
> I've excluded this bak file from Anti-Virus System, but how do i clear "
> disc caching "? , please tell me!
> thanks again!
> "John Bell" wrote:
> > Hi
> >
> > Your message indicates that the file is being used by another process. At a
> > guess this might be either be a problem with disc caching or Anti-virus/ITD
> > type of system.
> >
> > John
> >
> > "Jet Lai" wrote:
> >
> > > Dear all,
> > > i try to backup abc.bak(21G) TO remote Server every night. But the system
> > > gave me the following msg:
> > > BackupDiskFile::CreateMedia: Backup
> > > device '\\bkupsvr\dump$\abc.bak' failed to
> > > create. Operating system error = 32(error not found).
> > >
> > > SVR OS & SQL VERSION: win2003 STD SVR R2 with SQL SVR 2005 STD
> > >
> > >|||32
The process cannot access the file because it is being used by another
process.
ERROR_SHARING_VIOLATION
"Jet Lai" <JetLai@.discussions.microsoft.com> wrote in message
news:8F4CD19E-72C2-4A89-87BB-B6A3C40D45EB@.microsoft.com...
> Dear all,
> i try to backup abc.bak(21G) TO remote Server every night. But the system
> gave me the following msg:
> BackupDiskFile::CreateMedia: Backup
> device '\\bkupsvr\dump$\abc.bak' failed to
> create. Operating system error = 32(error not found).
> SVR OS & SQL VERSION: win2003 STD SVR R2 with SQL SVR 2005 STD
>

Operating system error = 32(error not found).

Dear all,
i try to backup abc.bak(21G) TO remote Server every night. But the system
gave me the following msg:
BackupDiskFile::CreateMedia: Backup
device '\\bkupsvr\dump$\abc.bak' failed to
create. Operating system error = 32(error not found).
SVR OS & SQL VERSION: win2003 STD SVR R2 with SQL SVR 2005 STDHi
Your message indicates that the file is being used by another process. At a
guess this might be either be a problem with disc caching or Anti-virus/ITD
type of system.
John
"Jet Lai" wrote:

> Dear all,
> i try to backup abc.bak(21G) TO remote Server every night. But the system
> gave me the following msg:
> BackupDiskFile::CreateMedia: Backup
> device '\\bkupsvr\dump$\abc.bak' failed to
> create. Operating system error = 32(error not found).
> SVR OS & SQL VERSION: win2003 STD SVR R2 with SQL SVR 2005 STD
>|||thank john for your answer.
Actually i can backup small bak file(said around 1G), but bak file with 21G
will be failed!
I've excluded this bak file from Anti-Virus System, but how do i clear "
disc caching "? , please tell me!
thanks again!
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> Your message indicates that the file is being used by another process. At
a
> guess this might be either be a problem with disc caching or Anti-virus/IT
D
> type of system.
> John
> "Jet Lai" wrote:
>|||Hi
You would need to know how the disc cache has been set up on the remote
server and (possibly) allow write through caching using the software supplie
d
by the manufacturer. Have you checked that the problem still occurs after
turning off the AV?
You may also want to check things like BIOS versions for the disc cache to
see if it is up-to-date.
Another thought, is that there seems quite a few posts relating to remote
backup issues which are due to a faulty network card/network. Make sure that
you don't have problems with the any cards and that you have sufficient
bandwidth between the two machines to cope with the load.
John
"Jet Lai" wrote:
[vbcol=seagreen]
> thank john for your answer.
> Actually i can backup small bak file(said around 1G), but bak file with 2
1G
> will be failed!
> I've excluded this bak file from Anti-Virus System, but how do i clear "
> disc caching "? , please tell me!
> thanks again!
> "John Bell" wrote:
>|||32
The process cannot access the file because it is being used by another
process.
ERROR_SHARING_VIOLATION
"Jet Lai" <JetLai@.discussions.microsoft.com> wrote in message
news:8F4CD19E-72C2-4A89-87BB-B6A3C40D45EB@.microsoft.com...
> Dear all,
> i try to backup abc.bak(21G) TO remote Server every night. But the system
> gave me the following msg:
> BackupDiskFile::CreateMedia: Backup
> device '\\bkupsvr\dump$\abc.bak' failed to
> create. Operating system error = 32(error not found).
> SVR OS & SQL VERSION: win2003 STD SVR R2 with SQL SVR 2005 STD
>

operating system does not support encryption

Hi All,

Iam using HP iPAq hw6500 series PDA. My application in installed in the PDA and when i try to open the application by taping on the application icon, Iam getting the following error.

Error: 0x80004005 E_FAIL
Native Error: (25080)
Description: The operating system does not support encryption. [,,,,,]
Interface defining error: IID_IDBInitialize
Param. 0: 0
Param. 1: 0
Param. 2: 0
Param. 3:
Param. 4:
Param. 5:

The application is builded on embedded VC++4.0

Can anyone help to figure out the possible reason for this error?

Thanks & Regards

Rajeev

It appears this is coming from SQL Server CE, moving to respective forum.

Please post some code in which this failure happens.

|||

Hi,

Thanks for replying.

Below mentioned is the code snippet (in red) where we are encountering the error:

void CStartUp::CreateDatabase(){
CString path = Application.GetDatabasePath();
path = path.Left(path.ReverseFind('\\'));
CreateDirectory(path, NULL);
CWaitCursor cur;

TRY{
// Create database
DeleteFile(Application.GetDatabasePath());
{
CComVariant vtConn;
CComQIPtr<_Catalog> catalog;
ThrowADOException(catalog.CoCreateInstance(__uuidof(Catalog)), constSourceFile);
CString str;
#if defined(_WIN32_WCE_EMULATION) || defined(_WIN32_WCE_CEPC)
str.Format(L"provider=microsoft.sqlserver.oledb.ce.2.0;data source=%s", Application.GetDatabasePath());
#else
str.Format(L"provider=microsoft.sqlserver.oledb.ce.2.0;data source=%s;SSCE:Database Password='test';SSCE:Encrypt Database=TRUE", Application.GetDatabasePath());
#endif
ThrowADOException(catalog->Create((WCHAR*)(LPCTSTR) str, &vtConn));
}
m_Database->Open(Application.GetDatabasePath());

.....

......

Regards

Rajeev

|||

OK, so it is SQL CE. It looks like your device does not support encryption you've requested in the connection string. Normally PPC 2003 should have required 128 bit encryption pack, but perhaps this one does not (e.g. due to export restrictions).

Please try it without encryption to make sure everything else works. If it does, you probably would need another device to run encryption as I'm not aware of separate encryption pack availability for PPC 03. You also may try PPC 2000/2002 one – search Microsoft’s downloads to find it.

|||

Hi All,

I am working on a PPC 2003 application.

The problem that I am encountering is that,when I install my application the root certificates on the PDA are getting deleted.

In my application, I am using an encrypted database(.sdf),So the application is not working,instead the database is getting deleted.

My application istallation package consists of :

The application,adoce 3.1 and ssce 2.0.

But When I uninstall my application,the root certificates are re-appearing on the PDA.

Please let me know what could be the possible reason for this scenario.

Any suggestion to resolve this will be greatly helpful.

Thanks & Regards

Rajeev

Operating system and SQL server application on same disk set?

Dear all,
Can anyone please tell me what the advantages are when placing the operating
system and SQL server application on different disk sets? Or are the
differences minimal when doing this?
Thanks in advance,
--
Best Regards,
DennisI don't think there are any advantages in doing this. If the box is only
running SQL Server there should not be much I/O going taking place on the OS
volume.
There are advantages in separating tempdb, the data files, and the log files
onto different volumes.
Keith Kratochvil
"Dennis" <Dennis@.discussions.microsoft.com> wrote in message
news:BE15F7F7-A507-4C86-9352-B7B6FE7BE956@.microsoft.com...
> Dear all,
> Can anyone please tell me what the advantages are when placing the
> operating
> system and SQL server application on different disk sets? Or are the
> differences minimal when doing this?
> Thanks in advance,
> --
> Best Regards,
> Dennis|||Dennis wrote:
> Dear all,
> Can anyone please tell me what the advantages are when placing the operati
ng
> system and SQL server application on different disk sets? Or are the
> differences minimal when doing this?
> Thanks in advance,
There really is no benefit to placing the executables on different
volumes. Database files and transaction log files are a different
matter entirely.
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Operating system and SQL server application on same disk set?

Dear all,
Can anyone please tell me what the advantages are when placing the operating
system and SQL server application on different disk sets? Or are the
differences minimal when doing this?
Thanks in advance,
--
Best Regards,
DennisI don't think there are any advantages in doing this. If the box is only
running SQL Server there should not be much I/O going taking place on the OS
volume.
There are advantages in separating tempdb, the data files, and the log files
onto different volumes.
--
Keith Kratochvil
"Dennis" <Dennis@.discussions.microsoft.com> wrote in message
news:BE15F7F7-A507-4C86-9352-B7B6FE7BE956@.microsoft.com...
> Dear all,
> Can anyone please tell me what the advantages are when placing the
> operating
> system and SQL server application on different disk sets? Or are the
> differences minimal when doing this?
> Thanks in advance,
> --
> Best Regards,
> Dennis|||Dennis wrote:
> Dear all,
> Can anyone please tell me what the advantages are when placing the operating
> system and SQL server application on different disk sets? Or are the
> differences minimal when doing this?
> Thanks in advance,
There really is no benefit to placing the executables on different
volumes. Database files and transaction log files are a different
matter entirely.
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Operating conditions after mirroring.

Are there any restrictions in the restored database after database mirroring?

I mean, Is it full mirror of my primary database?

What I want to do:

If my machine A(with primary database) will down I will redirect my clients to a real-time copied database - mirror server. I don't want to create cluster, but I want to use database mirroring function in ms sql 2005.

Can I use DB Mirroring to solve my problem?

p.s. sorry for my english

Yes that is exactly what mirroring is for. The mirror database is an exact copy of the principal.

Operand type clash: nvarchar is incompatible with image

I uploading an image from a web page into Sql Server 2000 database. I call a stored procedure with the parameter @.Flag of type Image. When I tried to pass a null value to this parameter I got the error:
"Operand type clash: nvarchar is incompatible with image".

I was adding the parameter like this:
sqlCmd.Parameters.AddWithValue("@.Flag", DBNull.Value);

After trying few different things I found a workaround using the following:
sqlCmd.Parameters.Add("@.Flag", SqlDbType.Image);
sqlCmd.Parameters["@.Flag"].Value = DBNull.Value;

Possibly a bug in the SqlCommand.Parameters.AddWithValue method ?

Not a bug. It's a limititation. If you don't specify a type (like with AddWithValue), then it makes a best guess. In this case, it guesses wrong.

Operand type clash: datetime is incompatible with text

I am getting this error on my insert statement, do I need to do something
for my datetime fields?
Here is the statement
INSERT INTO tblCalendar (
adjName, DispDueDate, ClaimNumber, Juris, HearingDate, HearingType,
ClaimantName, Location, HearingTime, HearingPart, WCB#, Counsel,
DateRecd)
VALUES (
@.Adjuster, @.newDispositionDueDate, @.ClaimNumber, @.Juris, @.newHearingDate,
@.HearingType, @.ClaimantName, @.HearingLocation, @.newHearingTime,
@.HearingPartRoom, @.WCBClaimNumber, @.AssignedCounsel, @.newNoticeReceived)You might want to check the datatypes of each column in tblCalendar table
and make sure they are compatible with the variables in the VALUES() clause.
If you have difficulty, post the exact CREATE TABLE statements and the
declared types and assigned values for each variables in the INSERT
statement.
Anith

Operand type clash error

Hello!

I am trying to update a DetailsView but I keep getting the error of Operand type clash: int is incompatible with text. I have double checked the stored procedure but I don't see any obvious offending parameters. ID is the only parameter that is an interger. I am not trying to update the ID at all, just for the filter purposes to update the records.

ALTER PROCEDURE [dbo].[proc_update]
/*
(
@.parameter1 int = 5,
@.parameter2 datatype OUTPUT
)
*/
@.BUILDING_SV float,
@.BUILDING_ADDS float,
@.BUILDING_DEL float,
@.BUILDING_TOTAL float,
@.ME_SV float,
@.ME_ADDS float,
@.ME_DEL float,
@.ME_TOTAL float,
@.PATTERNS_SV float,
@.PATTERNS_ADDS float,
@.PATTERNS_DEL float,
@.PATTERNS_TOTAL float,
@.SS_VALUE float,
@.SS_TOT float,
@.COMMENTS text,
@.OCCUPANCY_TYPE varchar(255),
@.REVISED_BY varchar(255),
@.DRAWINGS float,
@.INVENTORY float,
@.TOTAL float,
@.DIVISION varchar(255),
@.LOCATION varchar(255),
@.LOCATIONCODE varchar(255),
@.ADDRESS1 varchar(255),
@.ADDRESS2 varchar(255),
@.ADDRESS3 varchar(255),
@.ADDRESS4 varchar(255),
@.PROP_ID float,
@.POST_DATE smalldatetime,
@.ID int
AS
/* SET NOCOUNT ON */
UPDATE dbo.PROPERTY_VALUES
SET BUILDING_SV = @.BUILDING_SV, BUILDING_ADDS = @.BUILDING_ADDS, BUILDING_DEL = @.BUILDING_DEL, BUILDING_TOTAL = @.BUILDING_TOTAL, ME_SV = @.ME_SV, ME_ADDS = @.ME_ADDS, ME_DEL = @.ME_DEL, ME_TOTAL = @.ME_TOTAL, PATTERNS_SV = @.PATTERNS_SV, PATTERNS_ADDS = @.PATTERNS_ADDS, PATTERNS_DEL = @.PATTERNS_DEL, PATTERNS_TOTAL = @.PATTERNS_TOTAL, SS_VALUE = @.SS_VALUE, SS_TOT = @.SS_TOT, COMMENTS = @.COMMENTS,OCCUPANCY_TYPE = @.OCCUPANCY_TYPE, REVISED_BY = @.REVISED_BY, DRAWINGS = @.DRAWINGS, INVENTORY = @.INVENTORY, TOTAL = @.TOTAL, DIVISION = @.DIVISION, LOCATION = @.LOCATION, LOCATIONCODE = @.LOCATIONCODE, ADDRESS1 = @.ADDRESS1, ADDRESS2 = @.ADDRESS2, ADDRESS3 = @.ADDRESS3, ADDRESS4 = @.ADDRESS4, PROP_ID = @.PROP_ID, POST_DATE = @.POST_DATE
WHERE [ID] = @.ID
RETURN

Help!

Thanks.

I did more debugging. I discovered that the null values in the DetailsView are passing as "1" to the database. Anyone know how to debug this? Thanks.

|||

You probably have some default values set to 1, and "ConvertNullToDefaultValue" set to true (I forget the exact property name, but it's close).

|||

Thanks.

I don't recall setting the default values to 1. I am stumped why it is doing this. Is CONVERTNULLTODEFAULTVALUE a part of the codebehind or in the source page?

Thanks!

|||Please post the sqldatasource source (Everything between <asp:SqlDataSource and </asp:SqlDataSource>).|||

Here is it.

<asp:SqlDataSourceID="Update"runat="server"

SelectCommand="proc_update_select"UpdateCommand="proc_update"SelectCommandType="StoredProcedure"UpdateCommandType="StoredProcedure">

<asp:ControlParameterControlID="DetailsView1"Name="BUILDING_SV"PropertyName="SelectedValue"/>

<asp:ControlParameterControlID="DetailsView1"Name="BUILDING_ADDS"PropertyName="SelectedValue"/>

<asp:ControlParameterControlID="DetailsView1"Name="BUILDING_DEL"PropertyName="SelectedValue"/>

<asp:ControlParameterControlID="DetailsView1"Name="BUILDING_TOTAL"PropertyName="SelectedValue"/>

<asp:ControlParameterControlID="DetailsView1"Name="ME_SV"PropertyName="SelectedValue"/>

<asp:ControlParameterControlID="DetailsView1"Name="ME_ADDS"PropertyName="SelectedValue"/>

<asp:ControlParameterControlID="DetailsView1"Name="ME_DEL"PropertyName="SelectedValue"/>

<asp:ControlParameterControlID="DetailsView1"Name="ME_TOTAL"PropertyName="SelectedValue"/>

<asp:ControlParameterControlID="DetailsView1"Name="PATTERNS_SV"PropertyName="SelectedValue"/>

<asp:ControlParameterControlID="DetailsView1"Name="PATTERNS_ADDS"PropertyName="SelectedValue"/>

<asp:ControlParameterControlID="DetailsView1"Name="PATTERNS_DEL"PropertyName="SelectedValue"/>

<asp:ControlParameterControlID="DetailsView1"Name="PATTERNS_TOTAL"PropertyName="SelectedValue"/>

<asp:ControlParameterControlID="DetailsView1"Name="SS_VALUE"PropertyName="SelectedValue"/>

<asp:ControlParameterControlID="DetailsView1"Name="SS_TOT"PropertyName="SelectedValue"/>

<asp:ControlParameterControlID="DetailsView1"Name="COMMENTS"PropertyName="SelectedValue"/>

<asp:ControlParameterControlID="DetailsView1"Name="OCCUPANCY_TYPE"PropertyName="SelectedValue"/>

<asp:ControlParameterControlID="DetailsView1"Name="POST_DATE"PropertyName="SelectedValue"/>

<asp:ControlParameterControlID="DetailsView1"Name="REVISED_BY"PropertyName="SelectedValue"/>

<asp:ControlParameterControlID="DetailsView1"Name="DRAWINGS"PropertyName="SelectedValue"/>

<asp:ControlParameterControlID="DetailsView1"Name="INVENTORY"PropertyName="SelectedValue"/>

<asp:ControlParameterControlID="DetailsView1"Name="TOTAL"PropertyName="SelectedValue"/>

<asp:ControlParameterControlID="DetailsView1"Name="DIVISION"PropertyName="SelectedValue"/>

<asp:ControlParameterControlID="DetailsView1"Name="LOCATION"PropertyName="SelectedValue"/>

<asp:ControlParameterControlID="DetailsView1"Name="LOCATIONCODE"PropertyName="SelectedValue"/>

<asp:ControlParameterControlID="DetailsView1"Name="ADDRESS1"PropertyName="SelectedValue"/>

<asp:ControlParameterControlID="DetailsView1"Name="ADDRESS2"PropertyName="SelectedValue"/>

<asp:ControlParameterControlID="DetailsView1"Name="ADDRESS4"PropertyName="SelectedValue"/>

<asp:ControlParameterControlID="DetailsView1"Name="ADDRESS3"PropertyName="SelectedValue"/>

<asp:ControlParameterControlID="DetailsView1"Name="PROP_ID"PropertyName="SelectedValue"/>

<asp:ControlParameterControlID="DetailsView1"Name="ID"PropertyName="SelectedValue"/>

</UpdateParameters>

</asp:SqlDataSource>

|||

Why is every update parameter a ControlParameter? The parameters that are getting their values from being databound should be a plain "Parameter". Also what is the selectedvalue of detailsview1 in your test? 1?

Please change the parameters from type ControlParameter to type Parameter. If the problem still persists, then launch your app, go to the detailsview, enter into edit mode (So the dropdowns show), then post the HTML that one of the dropdowns that is bound to a field that is submitting a 1 by clicksing Tools->View Source in IE, and copying everything between the <SELECT and </SELECT> tags.

|||

I did what you said. It now no longer passing the null values as 1! Thank you!

However, it is not updating to the database even though I see the parameters updating. I am using the trace feature and the code below to trace the updating/updated parameters.

ProtectedSub Update_Updating(ByVal senderAsObject,ByVal eAs System.Web.UI.WebControls.SqlDataSourceCommandEventArgs)Handles Update.Updating

For xAsInteger = 0To e.Command.Parameters.Count - 1

Trace.Write(e.Command.Parameters(x).ParameterName)

Trace.Write(CStr(e.Command.Parameters(x).Value))

Any Ideas?

EndSub

Protected Sub Update_Updated(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceStatusEventArgs) Handles Update.Updated

For x As Integer = 0 To e.Command.Parameters.Count - 1

Trace.Write(e.Command.Parameters(x).ParameterName)

Trace.Write(CStr(e.Command.Parameters(x).Value))

End Sub

Conversion from type 'DBNull' to type 'String' is not valid.

Here is the code for the updated parameters

ProtectedSub Update_Updated(ByVal senderAsObject,ByVal eAs System.Web.UI.WebControls.SqlDataSourceStatusEventArgs)Handles Update.Updated

For xAsInteger = 0To e.Command.Parameters.Count - 1

Trace.Write(e.Command.Parameters(x).ParameterName)

Trace.Write(CStr(e.Command.Parameters(x).Value))

EndSub

|||

oops sorry for the last post...let me repost again.

I did what you said. It now no longer passing the null values as 1! Thank you!

However, it is not updating to the database even though I see the parameters updating. I am using the trace feature and the code below to trace the updating/updated parameters.

Here's the code for updating parameters

Protected Sub Update_Updating(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceCommandEventArgs) Handles Update.Updating

For x As Integer = 0 To e.Command.Parameters.Count - 1

Trace.Write(e.Command.Parameters(x).ParameterName)

Trace.Write(CStr(e.Command.Parameters(x).Value))

End Sub

Protected Sub Update_Updated(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceStatusEventArgs) Handles Update.Updated

For x As Integer = 0 To e.Command.Parameters.Count - 1

Trace.Write(e.Command.Parameters(x).ParameterName)

Trace.Write(CStr(e.Command.Parameters(x).Value))

End Sub

Here is the code for the updated parameters

Protected Sub Update_Updated(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceStatusEventArgs) Handles Update.Updated

For x As Integer = 0 To e.Command.Parameters.Count - 1

Trace.Write(e.Command.Parameters(x).ParameterName)

Trace.Write(CStr(e.Command.Parameters(x).Value))

End Sub

I got an error for the updated parameters code

Conversion from type 'DBNull' to type 'String' is not valid.

Any Ideas?

|||

Help?

It won't update the fields that currently have null values...

Help again? :)

|||Are you getting an error? Is it updating the fields that don't have null values?|||

Yes to both.

I am still having the error that I posted above and the update is also updating one of the columns that didn't have a null value with a null value.

Strangely, enough the update works if there are null values but it will still catch the exception of this error Conversion from type 'DBNull' to type 'String' is not valid when I step into the updated parameters. I would like to fix that so there won't be any problems in the future even though it still works. The another problem is that the update function will update this column named BLDG_SV with a null value even though it had a value, previously.

Thanks!!

|||Is the column named BLDG_SV or BUILDING_SV?|||yes sorry it is BUILDING_SV, forgive me.

openxml/'for xml' image data problem

I'm having problems with image fields when trying to insert XML into a table
using 'for xml' and openxml.
The code below demonstrates my problem. I have a table, tblAttachment that
contains a field named 'file' that contains image data.
prsXML is a simple proc that takes an xml document, extracts rows using
openxml and updates the row.
--prsXML
create proc dbo.prsXML2 @.XMLDoc text as begin
declare @.iDoc int
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.XMLDoc
select * into #tblAttachment
from openxml (@.idoc, '/NewDataSet/tblAttachment', 2) with tblAttachment
update tblAttachment set [ID] = t.[ID], [FileName] = t.[FileName],
[File] = t.[File]
from #tblAttachment t inner join tblAttachment f on f.ID = t.ID
drop table #tblAttachment
exec sp_xml_removedocument @.idoc
end
If I call prsXML passing it a simple xml document as shown below it updates
the row in the database.
prsXML '<?xml version="1.0" ?>
<NewDataSet>
<tblAttachment>
<ID>49AA5490-DD38-4A7D-87E7-0525E07930AF</ID>
<FileName>test.jpg</FileName>
<File>QUJDREVGRw==</File>
</tblAttachment>
</NewDataSet>'
However, when I then select this updated row using:
select [file] from tblAttachment where ID =
'49AA5490-DD38-4A7D-87E7-0525E07930AF'
for xml auto, elements, binary base64
it returns:
<tblAttachment>
<file>UQBVAEoARABSAEUAVgBHAFIAdwA9AD0A</file>
</tblAttachment>
The element <File>QUJDREVGRw==</File>
does not match the element: <file>UQBVAEoARABSAEUAVgBHAFIAdwA9AD0A</file>
returned by the last query.
It appears that encoding/decoding is messing up the image data. What is
going on and how can I fix it?
Thanks for your help.
Jay
The issue is that FOR XML binary base64 does encode the content of your
image column using base64 encoding, but the OpenXML with clause does not
perform a decoding (let's say it is a design quirk :-)).
So you have the following workarounds:
1. Extract the encoded image and run a base64-decoder on it before doing the
insertion. The algorithm is well-described in the literature and can be
implemented using T-SQL or an extended stored proc.
2. You are waiting for SQL Server 2005, where the nodes() method will
provide you automatic decoding in the same scenario.
Sorry and best regards
Michael
"jamccormick" <jamccormick@.discussions.microsoft.com> wrote in message
news:17A4CDE2-90C8-46B0-A17E-493E2A6AF23E@.microsoft.com...
> I'm having problems with image fields when trying to insert XML into a
> table
> using 'for xml' and openxml.
> The code below demonstrates my problem. I have a table, tblAttachment
> that
> contains a field named 'file' that contains image data.
> prsXML is a simple proc that takes an xml document, extracts rows using
> openxml and updates the row.
> --prsXML
> create proc dbo.prsXML2 @.XMLDoc text as begin
> declare @.iDoc int
> EXEC sp_xml_preparedocument @.idoc OUTPUT, @.XMLDoc
> select * into #tblAttachment
> from openxml (@.idoc, '/NewDataSet/tblAttachment', 2) with tblAttachment
> update tblAttachment set [ID] = t.[ID], [FileName] = t.[FileName],
> [File] = t.[File]
> from #tblAttachment t inner join tblAttachment f on f.ID = t.ID
> drop table #tblAttachment
> exec sp_xml_removedocument @.idoc
> end
> If I call prsXML passing it a simple xml document as shown below it
> updates
> the row in the database.
> prsXML '<?xml version="1.0" ?>
> <NewDataSet>
> <tblAttachment>
> <ID>49AA5490-DD38-4A7D-87E7-0525E07930AF</ID>
> <FileName>test.jpg</FileName>
> <File>QUJDREVGRw==</File>
> </tblAttachment>
> </NewDataSet>'
> However, when I then select this updated row using:
> select [file] from tblAttachment where ID =
> '49AA5490-DD38-4A7D-87E7-0525E07930AF'
> for xml auto, elements, binary base64
> it returns:
> <tblAttachment>
> <file>UQBVAEoARABSAEUAVgBHAFIAdwA9AD0A</file>
> </tblAttachment>
> The element <File>QUJDREVGRw==</File>
> does not match the element: <file>UQBVAEoARABSAEUAVgBHAFIAdwA9AD0A</file>
> returned by the last query.
> It appears that encoding/decoding is messing up the image data. What is
> going on and how can I fix it?
> Thanks for your help.
> --
> Jay

OPENXML, UPDATES, and SET Criteria

I have been using OPENXML with great success to create INSERT
statments.
But with updates I am having an issue. Because the tables I am
updating use triggers on many of the fields, I am only allowed to
update fields that have actual changed values due to the fact the
triggers fire based on UPDATE, not an actual change. I do not have
authority to change the triggers.
When I try using OPENXML, sending only the fields that have changed,
the result is that the updated fields are changed, but all other fields
are emptied because they were not sent through in the XML file.
Because I do not know which fields will be changed from one execution
to the next, I need a way for the statement to be written which would
only update the changed fields.
I understand why it is happening, I am curious if there is a way around
it without having to go back to using plain old sql statements. I
cannot use Updategrams or Diffgrams as it is not available within our
application. Currently my only thought is to create all of the SQL
statments in an XSL and dynamically create normal, non-OPENXL
statements to be sent to the stored procedure.
This is not what I would consider the best solution as it makes coding
and debugging the SQL statements much more difficult.
Any insight would be greatly appreciated.
Thanks!
Here's an example:
Prior to Update:
Row 1: fielda = 1, fieldb = 2, fieldc = 3
Row 2: fielda = 1, fieldb = 2, fieldc = 3
Updated Fields:
Row 1: fieldb = 2a
Row 2: fielda= 2, fieldb=3
Update Statement using XML with only the updated fields:
UPDATE
table_with_triggers
SET
fielda = xmlDoc.fielda,
fieldb = xmlDoc.fieldb,
fieldc = xmlDoc.fieldc
FROM
table_with_triggers,
OPENXML (@.hDoc,'DatabaseTables/table_with_triggers', 2)
WITH table_with_triggers xmlDoc
WHERE table_with_triggers.id = @.int_id
After Update:
Row 1: fielda is empty, field b = 2b, fieldc is empty
Row 2: fielda = 2, fieldb = 3, fieldc is empty
Dynamically generating your statements is probably best in this case. If you
do not allow updates to NULL, you could also do conditionals on whether
OpenXML extracts a value or returns NULL.
Best regards
Michael
"box464" <box464@.gmail.com> wrote in message
news:1131306557.832830.295700@.z14g2000cwz.googlegr oups.com...
>I have been using OPENXML with great success to create INSERT
> statments.
> But with updates I am having an issue. Because the tables I am
> updating use triggers on many of the fields, I am only allowed to
> update fields that have actual changed values due to the fact the
> triggers fire based on UPDATE, not an actual change. I do not have
> authority to change the triggers.
> When I try using OPENXML, sending only the fields that have changed,
> the result is that the updated fields are changed, but all other fields
> are emptied because they were not sent through in the XML file.
> Because I do not know which fields will be changed from one execution
> to the next, I need a way for the statement to be written which would
> only update the changed fields.
> I understand why it is happening, I am curious if there is a way around
> it without having to go back to using plain old sql statements. I
> cannot use Updategrams or Diffgrams as it is not available within our
> application. Currently my only thought is to create all of the SQL
> statments in an XSL and dynamically create normal, non-OPENXL
> statements to be sent to the stored procedure.
> This is not what I would consider the best solution as it makes coding
> and debugging the SQL statements much more difficult.
> Any insight would be greatly appreciated.
> Thanks!
> Here's an example:
> Prior to Update:
> Row 1: fielda = 1, fieldb = 2, fieldc = 3
> Row 2: fielda = 1, fieldb = 2, fieldc = 3
> Updated Fields:
> Row 1: fieldb = 2a
> Row 2: fielda= 2, fieldb=3
> Update Statement using XML with only the updated fields:
> UPDATE
> table_with_triggers
> SET
> fielda = xmlDoc.fielda,
> fieldb = xmlDoc.fieldb,
> fieldc = xmlDoc.fieldc
> FROM
> table_with_triggers,
> OPENXML (@.hDoc,'DatabaseTables/table_with_triggers', 2)
> WITH table_with_triggers xmlDoc
> WHERE table_with_triggers.id = @.int_id
> After Update:
> Row 1: fielda is empty, field b = 2b, fieldc is empty
> Row 2: fielda = 2, fieldb = 3, fieldc is empty
>

OPENXML, UPDATES, and SET Criteria

I have been using OPENXML with great success to create INSERT
statments.
But with updates I am having an issue. Because the tables I am
updating use triggers on many of the fields, I am only allowed to
update fields that have actual changed values due to the fact the
triggers fire based on UPDATE, not an actual change. I do not have
authority to change the triggers.
When I try using OPENXML, sending only the fields that have changed,
the result is that the updated fields are changed, but all other fields
are emptied because they were not sent through in the XML file.
Because I do not know which fields will be changed from one execution
to the next, I need a way for the statement to be written which would
only update the changed fields.
I understand why it is happening, I am curious if there is a way around
it without having to go back to using plain old sql statements. I
cannot use Updategrams or Diffgrams as it is not available within our
application. Currently my only thought is to create all of the SQL
statments in an XSL and dynamically create normal, non-OPENXL
statements to be sent to the stored procedure.
This is not what I would consider the best solution as it makes coding
and debugging the SQL statements much more difficult.
Any insight would be greatly appreciated.
Thanks!
Here's an example:
Prior to Update:
Row 1: fielda = 1, fieldb = 2, fieldc = 3
Row 2: fielda = 1, fieldb = 2, fieldc = 3
Updated Fields:
Row 1: fieldb = 2a
Row 2: fielda= 2, fieldb=3
Update Statement using XML with only the updated fields:
UPDATE
table_with_triggers
SET
fielda = xmlDoc.fielda,
fieldb = xmlDoc.fieldb,
fieldc = xmlDoc.fieldc
FROM
table_with_triggers,
OPENXML (@.hDoc,'DatabaseTables/table_with_triggers', 2)
WITH table_with_triggers xmlDoc
WHERE table_with_triggers.id = @.int_id
After Update:
Row 1: fielda is empty, field b = 2b, fieldc is empty
Row 2: fielda = 2, fieldb = 3, fieldc is emptyDynamically generating your statements is probably best in this case. If you
do not allow updates to NULL, you could also do conditionals on whether
OpenXML extracts a value or returns NULL.
Best regards
Michael
"box464" <box464@.gmail.com> wrote in message
news:1131306557.832830.295700@.z14g2000cwz.googlegroups.com...
>I have been using OPENXML with great success to create INSERT
> statments.
> But with updates I am having an issue. Because the tables I am
> updating use triggers on many of the fields, I am only allowed to
> update fields that have actual changed values due to the fact the
> triggers fire based on UPDATE, not an actual change. I do not have
> authority to change the triggers.
> When I try using OPENXML, sending only the fields that have changed,
> the result is that the updated fields are changed, but all other fields
> are emptied because they were not sent through in the XML file.
> Because I do not know which fields will be changed from one execution
> to the next, I need a way for the statement to be written which would
> only update the changed fields.
> I understand why it is happening, I am curious if there is a way around
> it without having to go back to using plain old sql statements. I
> cannot use Updategrams or Diffgrams as it is not available within our
> application. Currently my only thought is to create all of the SQL
> statments in an XSL and dynamically create normal, non-OPENXL
> statements to be sent to the stored procedure.
> This is not what I would consider the best solution as it makes coding
> and debugging the SQL statements much more difficult.
> Any insight would be greatly appreciated.
> Thanks!
> Here's an example:
> Prior to Update:
> Row 1: fielda = 1, fieldb = 2, fieldc = 3
> Row 2: fielda = 1, fieldb = 2, fieldc = 3
> Updated Fields:
> Row 1: fieldb = 2a
> Row 2: fielda= 2, fieldb=3
> Update Statement using XML with only the updated fields:
> UPDATE
> table_with_triggers
> SET
> fielda = xmlDoc.fielda,
> fieldb = xmlDoc.fieldb,
> fieldc = xmlDoc.fieldc
> FROM
> table_with_triggers,
> OPENXML (@.hDoc,'DatabaseTables/table_with_triggers', 2)
> WITH table_with_triggers xmlDoc
> WHERE table_with_triggers.id = @.int_id
> After Update:
> Row 1: fielda is empty, field b = 2b, fieldc is empty
> Row 2: fielda = 2, fieldb = 3, fieldc is empty
>

OpenXML XPath question

I've included the xml and my OpenXML statement below. Using XPath in the
WITH portion of the OpenXML statement I can retrieve most values I need, but
I am trying to retrieve the values 'Last Name of Client' and 'First Name of
Client' from the xml and am not having any luck. I tried using '..' as the
xpath query but that returns text in the Question node and all child nodes.
<Section Value="AA"> Name and Identification Numbers
<Question Value="1.A"> Last Name of Client
<Question_Text>Last</Question_Text>
<Answer_Text>ROSSI1</Answer_Text>
<Answer_Value>ROSSI2</Answer_Value>
</Question>
<Question Value="2.A"> First Name of Client
<Question_Text>First Name</Question_Text>
<Answer_Text>TestA1</Answer_Text>
<Answer_Value>TestA2</Answer_Value>
</Question>
</Section>
OpenXML(@.idoc, '/ns0:GoldCare_Assessment/Section/Question')
WITH ([SectionValue] varchar(50) '../@.Value',
[QuestionValue] varchar(50) '@.Value',
[Question_Text] varchar(50) './Question_Text',
[Answer_Text] varchar(50) './Answer_Text',
[Answer_Value] varchar(50) './Answer_Value')Look at the bottom of this article where the stored procedure is shown:
http://www.eggheadcafe.com/articles...er_bulkload.asp
2005 Microsoft MVP C#
Robbe Morris
http://www.robbemorris.com
http://www.mastervb.net/home/ng/for...st10017013.aspx
http://www.eggheadcafe.com/articles...e_generator.asp
"Jeremy Chapman" <NoSpam@.Please.com> wrote in message
news:e1us7sSGFHA.2756@.TK2MSFTNGP15.phx.gbl...
> I've included the xml and my OpenXML statement below. Using XPath in the
> WITH portion of the OpenXML statement I can retrieve most values I need,
> but
> I am trying to retrieve the values 'Last Name of Client' and 'First Name
> of
> Client' from the xml and am not having any luck. I tried using '..' as
> the
> xpath query but that returns text in the Question node and all child
> nodes.
> <Section Value="AA"> Name and Identification Numbers
> <Question Value="1.A"> Last Name of Client
> <Question_Text>Last</Question_Text>
> <Answer_Text>ROSSI1</Answer_Text>
> <Answer_Value>ROSSI2</Answer_Value>
> </Question>
> <Question Value="2.A"> First Name of Client
> <Question_Text>First Name</Question_Text>
> <Answer_Text>TestA1</Answer_Text>
> <Answer_Value>TestA2</Answer_Value>
> </Question>
> </Section>
> OpenXML(@.idoc, '/ns0:GoldCare_Assessment/Section/Question')
> WITH ([SectionValue] varchar(50) '../@.Value',
> [QuestionValue] varchar(50) '@.Value',
> [Question_Text] varchar(50) './Question_Text',
> [Answer_Text] varchar(50) './Answer_Text',
> [Answer_Value] varchar(50) './Answer_Value')
>
>

OPENXML won't return rows

I am trying to insert a row from an XML document into a table. Here is
the table structure:
CREATE TABLE dbo.Customer(
CustomerGUID uniqueidentifier NOT NULL,
CustomerName varchar(25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
Version int NULL,
ActivatedDate smalldatetime NULL,
ActivatedByWS uniqueidentifier NULL,
DeactivatedDate smalldatetime NULL,
DeactivatedByWS varchar(10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
UpdatedByWS uniqueidentifier NULL,
CustomerType char(2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
SalesOfficeName varchar(25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL,
PRIMARY KEY CLUSTERED
(
CustomerGUID ASC
)WITH (IGNORE_DUP_KEY = OFF) ON PRIMARY,
UNIQUE NONCLUSTERED
(
CustomerName ASC
)WITH (IGNORE_DUP_KEY = OFF) ON PRIMARY
) ON PRIMARY
GO
Here is the code to get the data from the document and insert it:
DECLARE @.docHandle int
declare @.xmlDocument xml
set @.xmlDocument = N'<ROOT>
<Customer>
<CustomerGUID>05062E6D-453B-4CAE-9EA4-BAF7D00E4235</CustomerGUID>
<CustomerType>CU</CustomerType>
<SalesOfficeName>Roadway Main</SalesOfficeName>
<UpdatedByWS>D7DA4883-656D-4923-9C5C-FD83EBE6F1BE</UpdatedByWS>
<ActivatedDate>2005-11-20T00:00:00</ActivatedDate>
</Customer>
</ROOT>'
EXEC sp_xml_preparedocument @.docHandle OUTPUT, @.xmlDocument
-- comment out the actual INSERT
--INSERT INTO Customer (CustomerGUID, CustomerType, SalesOfficeName,
UpdatedByWS, ActivatedDate)
SELECT CustomerGUID, CustomerType, SalesOfficeName, UpdatedByWS,
ActivatedDate
FROM OPENXML(@.docHandle, N'/ROOT/Customer')
WITH Customer
EXEC sp_xml_removedocument @.docHandle
GO
I get this result:
CustomerGUID CustomerType SalesOfficeName UpdatedByWS ActivatedDate
-- -- -- -- --
NULL NULL NULL NULL NULL
I think that I am not describing the XML correctly to the XML variable,
but I don't know what to do next. Any ideas? Thanks.
You need to specify the flags parameter to tell OPENXML to look for elements
instead of attributes. The default Flags value is 1, which is attributes -
use 2 for elements, or 3 for both (or better yet use colpatterns in a table
def in the WITH clause).
DECLARE @.docHandle int
declare @.xmlDocument xml
set @.xmlDocument = N'<ROOT>
<Customer>
<CustomerGUID>05062E6D-453B-4CAE-9EA4-BAF7D00E4235</CustomerGUID>
<CustomerType>CU</CustomerType>
<SalesOfficeName>Roadway Main</SalesOfficeName>
<UpdatedByWS>D7DA4883-656D-4923-9C5C-FD83EBE6F1BE</UpdatedByWS>
<ActivatedDate>2005-11-20T00:00:00</ActivatedDate>
</Customer>
</ROOT>'
EXEC sp_xml_preparedocument @.docHandle OUTPUT, @.xmlDocument
-- comment out the actual INSERT
--INSERT INTO Customer (CustomerGUID, CustomerType, SalesOfficeName,
UpdatedByWS, ActivatedDate)
SELECT CustomerGUID, CustomerType, SalesOfficeName,
UpdatedByWS,ActivatedDate
FROM OPENXML(@.docHandle, N'/ROOT/Customer', 2)
WITH Customer
Cheers,
Graeme
_____________________
Graeme Malcolm
Principal Technologist
Content Master
- a member of CM Group
www.contentmaster.com
<googleThis@.nadolna.net> wrote in message
news:1132684803.666917.108890@.g47g2000cwa.googlegr oups.com...
>I am trying to insert a row from an XML document into a table. Here is
> the table structure:
> CREATE TABLE dbo.Customer(
> CustomerGUID uniqueidentifier NOT NULL,
> CustomerName varchar(25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> Version int NULL,
> ActivatedDate smalldatetime NULL,
> ActivatedByWS uniqueidentifier NULL,
> DeactivatedDate smalldatetime NULL,
> DeactivatedByWS varchar(10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> UpdatedByWS uniqueidentifier NULL,
> CustomerType char(2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
> SalesOfficeName varchar(25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL,
> PRIMARY KEY CLUSTERED
> (
> CustomerGUID ASC
> )WITH (IGNORE_DUP_KEY = OFF) ON PRIMARY,
> UNIQUE NONCLUSTERED
> (
> CustomerName ASC
> )WITH (IGNORE_DUP_KEY = OFF) ON PRIMARY
> ) ON PRIMARY
> GO
>
> Here is the code to get the data from the document and insert it:
> DECLARE @.docHandle int
> declare @.xmlDocument xml
> set @.xmlDocument = N'<ROOT>
> <Customer>
> <CustomerGUID>05062E6D-453B-4CAE-9EA4-BAF7D00E4235</CustomerGUID>
> <CustomerType>CU</CustomerType>
> <SalesOfficeName>Roadway Main</SalesOfficeName>
> <UpdatedByWS>D7DA4883-656D-4923-9C5C-FD83EBE6F1BE</UpdatedByWS>
> <ActivatedDate>2005-11-20T00:00:00</ActivatedDate>
> </Customer>
> </ROOT>'
> EXEC sp_xml_preparedocument @.docHandle OUTPUT, @.xmlDocument
> -- comment out the actual INSERT
> --INSERT INTO Customer (CustomerGUID, CustomerType, SalesOfficeName,
> UpdatedByWS, ActivatedDate)
> SELECT CustomerGUID, CustomerType, SalesOfficeName, UpdatedByWS,
> ActivatedDate
> FROM OPENXML(@.docHandle, N'/ROOT/Customer')
> WITH Customer
> EXEC sp_xml_removedocument @.docHandle
> GO
> I get this result:
> CustomerGUID CustomerType SalesOfficeName UpdatedByWS ActivatedDate
> -- -- -- -- --
> NULL NULL NULL NULL NULL
> I think that I am not describing the XML correctly to the XML variable,
> but I don't know what to do next. Any ideas? Thanks.
>
|||That was it! Thanks very much, Graeme.

OPENXML won't return rows

I am trying to insert a row from an XML document into a table. Here is
the table structure:
CREATE TABLE dbo.Customer(
CustomerGUID uniqueidentifier NOT NULL,
CustomerName varchar(25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
Version int NULL,
ActivatedDate smalldatetime NULL,
ActivatedByWS uniqueidentifier NULL,
DeactivatedDate smalldatetime NULL,
DeactivatedByWS varchar(10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
UpdatedByWS uniqueidentifier NULL,
CustomerType char(2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
SalesOfficeName varchar(25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL,
PRIMARY KEY CLUSTERED
(
CustomerGUID ASC
)WITH (IGNORE_DUP_KEY = OFF) ON PRIMARY,
UNIQUE NONCLUSTERED
(
CustomerName ASC
)WITH (IGNORE_DUP_KEY = OFF) ON PRIMARY
) ON PRIMARY
GO
---
Here is the code to get the data from the document and insert it:
---
DECLARE @.docHandle int
declare @.xmlDocument xml
set @.xmlDocument = N'<ROOT>
<Customer>
<CustomerGUID>05062E6D-453B-4CAE-9EA4-BAF7D00E4235</CustomerGUID>
<CustomerType>CU</CustomerType>
<SalesOfficeName>Roadway Main</SalesOfficeName>
<UpdatedByWS>D7DA4883-656D-4923-9C5C-FD83EBE6F1BE</UpdatedByWS>
<ActivatedDate>2005-11-20T00:00:00</ActivatedDate>
</Customer>
</ROOT>'
EXEC sp_xml_preparedocument @.docHandle OUTPUT, @.xmlDocument
-- comment out the actual INSERT
--INSERT INTO Customer (CustomerGUID, CustomerType, SalesOfficeName,
UpdatedByWS, ActivatedDate)
SELECT CustomerGUID, CustomerType, SalesOfficeName, UpdatedByWS,
ActivatedDate
FROM OPENXML(@.docHandle, N'/ROOT/Customer')
WITH Customer
EXEC sp_xml_removedocument @.docHandle
GO
I get this result:
CustomerGUID CustomerType SalesOfficeName UpdatedByWS ActivatedDate
-- -- -- -- --
NULL NULL NULL NULL NULL
I think that I am not describing the XML correctly to the XML variable,
but I don't know what to do next. Any ideas? Thanks.You need to specify the flags parameter to tell OPENXML to look for elements
instead of attributes. The default Flags value is 1, which is attributes -
use 2 for elements, or 3 for both (or better yet use colpatterns in a table
def in the WITH clause).
DECLARE @.docHandle int
declare @.xmlDocument xml
set @.xmlDocument = N'<ROOT>
<Customer>
<CustomerGUID>05062E6D-453B-4CAE-9EA4-BAF7D00E4235</CustomerGUID>
<CustomerType>CU</CustomerType>
<SalesOfficeName>Roadway Main</SalesOfficeName>
<UpdatedByWS>D7DA4883-656D-4923-9C5C-FD83EBE6F1BE</UpdatedByWS>
<ActivatedDate>2005-11-20T00:00:00</ActivatedDate>
</Customer>
</ROOT>'
EXEC sp_xml_preparedocument @.docHandle OUTPUT, @.xmlDocument
-- comment out the actual INSERT
--INSERT INTO Customer (CustomerGUID, CustomerType, SalesOfficeName,
UpdatedByWS, ActivatedDate)
SELECT CustomerGUID, CustomerType, SalesOfficeName,
UpdatedByWS,ActivatedDate
FROM OPENXML(@.docHandle, N'/ROOT/Customer', 2)
WITH Customer
Cheers,
Graeme
_____________________
Graeme Malcolm
Principal Technologist
Content Master
- a member of CM Group
www.contentmaster.com
<googleThis@.nadolna.net> wrote in message
news:1132684803.666917.108890@.g47g2000cwa.googlegroups.com...
>I am trying to insert a row from an XML document into a table. Here is
> the table structure:
> CREATE TABLE dbo.Customer(
> CustomerGUID uniqueidentifier NOT NULL,
> CustomerName varchar(25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> Version int NULL,
> ActivatedDate smalldatetime NULL,
> ActivatedByWS uniqueidentifier NULL,
> DeactivatedDate smalldatetime NULL,
> DeactivatedByWS varchar(10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> UpdatedByWS uniqueidentifier NULL,
> CustomerType char(2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
> SalesOfficeName varchar(25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL,
> PRIMARY KEY CLUSTERED
> (
> CustomerGUID ASC
> )WITH (IGNORE_DUP_KEY = OFF) ON PRIMARY,
> UNIQUE NONCLUSTERED
> (
> CustomerName ASC
> )WITH (IGNORE_DUP_KEY = OFF) ON PRIMARY
> ) ON PRIMARY
> GO
>
> ---
> Here is the code to get the data from the document and insert it:
> ---
> DECLARE @.docHandle int
> declare @.xmlDocument xml
> set @.xmlDocument = N'<ROOT>
> <Customer>
> <CustomerGUID>05062E6D-453B-4CAE-9EA4-BAF7D00E4235</CustomerGUID>
> <CustomerType>CU</CustomerType>
> <SalesOfficeName>Roadway Main</SalesOfficeName>
> <UpdatedByWS>D7DA4883-656D-4923-9C5C-FD83EBE6F1BE</UpdatedByWS>
> <ActivatedDate>2005-11-20T00:00:00</ActivatedDate>
> </Customer>
> </ROOT>'
> EXEC sp_xml_preparedocument @.docHandle OUTPUT, @.xmlDocument
> -- comment out the actual INSERT
> --INSERT INTO Customer (CustomerGUID, CustomerType, SalesOfficeName,
> UpdatedByWS, ActivatedDate)
> SELECT CustomerGUID, CustomerType, SalesOfficeName, UpdatedByWS,
> ActivatedDate
> FROM OPENXML(@.docHandle, N'/ROOT/Customer')
> WITH Customer
> EXEC sp_xml_removedocument @.docHandle
> GO
> I get this result:
> CustomerGUID CustomerType SalesOfficeName UpdatedByWS ActivatedDate
> -- -- -- -- --
> NULL NULL NULL NULL NULL
> I think that I am not describing the XML correctly to the XML variable,
> but I don't know what to do next. Any ideas? Thanks.
>|||That was it! Thanks very much, Graeme.

OpenXML Woes - 2Qs

Hi everybody.

I don't know if anyone can help me but I have two issues with SQL Server 2000 SP4 (version 8.00.2039 - Desktop Engine) running on W2K and W2K3. I'm also running SQLXML 3.0 (msxml2.dll version is 8.30.9530.0).

Is it me or is sp_xml_preparedocument a crippled fat dog that is blind?...not that I have anything against crippled fat dogs that are blind :)

In all the stored procs I have developed, I pass a text var as an input parameter and return an IStream to ADO (using an sqlxml provider) in COM+. All has been very well and fine...until the passed text parameter resembles a data object of any decent size.

The first error I was noticing was a "XML Parsing Error: not enough storage is available to complete this operation". Well, I thought I would debug logically in a step fashion and just prepare the doc first and then do a return and then do a return on the next segment of code to find out where the issue is. I was amazed to find that sp_xml_preparedocument is taking 7 seconds to load a simple 1MByte text input var and around three minutes to load a 7 MByte file.

I believe these long load times are causing issues with transaction timouts etc so I thought I would try to solve the speed issue with sp_xml_preparedocument and then see if the "XML Parsing Error" continues.

So, my first question is:

Should sp_xml_preparedocument take 7 seconds to load a 1MByte text variable and nearly three odd minutes to load a 7 MByte file? Surely there is something wrong somewhere?

I'm also running these tests on two machines - one is 2 GHz and the other is 2.4 GHz P4's.

Cheers and thanks for any info.

ErronHaven't got much experience with it, but did notice the non performance of this while running some test a few monhs ago. Should be much better in SQL 2005, but I haven't tested that at all, so can't share any experiences on that with you ... yet.

Gr,
Yveau|||Thanks Yveau for the response.

If anyone else has any info or optimization tips, it would be muchly appreciated.

Cheers

Erron

OpenXML with namespace and no prefix

I'm trying to run a query on some xml using openxml. The xml has a
namespace withough a prefix, and I'm having trouble. Below is a sample of
the xml and my openxml query. It runs but doesn't give back the expected
resultset. Any hints?
SELECT @.strIDXml = '<?xml version="1.0"?><IDs
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns="http://www.interiorhealth.ca/DevSupport"><ID>3</ID><ID>2</ID></IDs>'
DECLARE @.idoc int
EXECUTE sp_xml_preparedocument @.idoc OUTPUT, @.strIDXml, '<IDs
xmlns="http://www.interiorhealth.ca/DevSupport"/>'
SELECT
2,[ID]
FROM
OpenXML(@.idoc, '/IDs/ID')
WITH (
[ID] int '.'
)
EXECUTE sp_xml_removedocument @.iDocJeremy wrote:
> I'm trying to run a query on some xml using openxml. The xml has a
> namespace withough a prefix, and I'm having trouble. Below is a sample of
> the xml and my openxml query. It runs but doesn't give back the expected
> resultset. Any hints?
For your query you need to bind a prefix to the default namespace and
use that prefix as in the following example:
EXECUTE sp_xml_preparedocument @.idoc OUTPUT, @.strIDXml, '<IDs
xmlns:pf="http://www.interiorhealth.ca/DevSupport"/>'
SELECT
2,[ID]
FROM
OpenXML(@.idoc, '/pf:IDs/pf:ID')
WITH (
[ID] int '.'
)
You are free to choose any valid prefix you like, just make sure it is
bound to the namespace URI defined in the original XML.
Martin Honnen -- MVP XML
http://JavaScript.FAQTs.com/|||thanks!
"Martin Honnen" <mahotrash@.yahoo.de> wrote in message
news:ux0UArHxHHA.736@.TK2MSFTNGP06.phx.gbl...
> Jeremy wrote:
> For your query you need to bind a prefix to the default namespace and use
> that prefix as in the following example:
> EXECUTE sp_xml_preparedocument @.idoc OUTPUT, @.strIDXml, '<IDs
> xmlns:pf="http://www.interiorhealth.ca/DevSupport"/>'
> SELECT
> 2,[ID]
> FROM
> OpenXML(@.idoc, '/pf:IDs/pf:ID')
> WITH (
> [ID] int '.'
> )
> You are free to choose any valid prefix you like, just make sure it is
> bound to the namespace URI defined in the original XML.
> --
> Martin Honnen -- MVP XML
> http://JavaScript.FAQTs.com/

OpenXML with namespace and no prefix

I'm trying to run a query on some xml using openxml. The xml has a
namespace withough a prefix, and I'm having trouble. Below is a sample of
the xml and my openxml query. It runs but doesn't give back the expected
resultset. Any hints?
SELECT @.strIDXml = '<?xml version="1.0"?><IDs
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns="http://www.interiorhealth.ca/DevSupport"><ID>3</ID><ID>2</ID></IDs>'
DECLARE @.idoc int
EXECUTE sp_xml_preparedocument @.idoc OUTPUT, @.strIDXml, '<IDs
xmlns="http://www.interiorhealth.ca/DevSupport"/>'
SELECT
2,[ID]
FROM
OpenXML(@.idoc, '/IDs/ID')
WITH (
[ID] int '.'
)
EXECUTE sp_xml_removedocument @.iDoc
Jeremy wrote:
> I'm trying to run a query on some xml using openxml. The xml has a
> namespace withough a prefix, and I'm having trouble. Below is a sample of
> the xml and my openxml query. It runs but doesn't give back the expected
> resultset. Any hints?
For your query you need to bind a prefix to the default namespace and
use that prefix as in the following example:
EXECUTE sp_xml_preparedocument @.idoc OUTPUT, @.strIDXml, '<IDs
xmlns:pf="http://www.interiorhealth.ca/DevSupport"/>'
SELECT
2,[ID]
FROM
OpenXML(@.idoc, '/pf:IDs/pf:ID')
WITH (
[ID] int '.'
)
You are free to choose any valid prefix you like, just make sure it is
bound to the namespace URI defined in the original XML.
Martin Honnen -- MVP XML
http://JavaScript.FAQTs.com/
|||thanks!
"Martin Honnen" <mahotrash@.yahoo.de> wrote in message
news:ux0UArHxHHA.736@.TK2MSFTNGP06.phx.gbl...
> Jeremy wrote:
> For your query you need to bind a prefix to the default namespace and use
> that prefix as in the following example:
> EXECUTE sp_xml_preparedocument @.idoc OUTPUT, @.strIDXml, '<IDs
> xmlns:pf="http://www.interiorhealth.ca/DevSupport"/>'
> SELECT
> 2,[ID]
> FROM
> OpenXML(@.idoc, '/pf:IDs/pf:ID')
> WITH (
> [ID] int '.'
> )
> You are free to choose any valid prefix you like, just make sure it is
> bound to the namespace URI defined in the original XML.
> --
> Martin Honnen -- MVP XML
> http://JavaScript.FAQTs.com/

Saturday, February 25, 2012

Operating System Memory. How much?

Operating System Memory. How much?

Operating System Memory. How much?

Operating system error code 3(The system cannot find the path specified.).

Operating system error 87 on log file

Operating system error 38(Reached the end of the file.) on file "C:\Data\myfile_log.LDF&quo

Operating system error 38(Reached the end of the file.) on file "C:\Data\myfile_log.LDF

Operating system error 38(Reached the end of the file.) on file "C:\Data\myfile_log.LDF

Operating System Error 2

Operating System Error 2

Operating system error 1450(Insufficient system resources exist to complete the requested se

Operating system error 112(There is not enough space on the disk.).

Operating system error 10038., An operation was attempted on something that is not a socke

Operating system error 10038., An operation was attempted on something that is not a s

Operating system error = 32(error not found).

Operating system error = 32(error not found).

operating system does not support encryption

Operating system and SQL server application on same disk set?

Operating system and SQL server application on same disk set?

Operating conditions after mirroring.

Operand type clash: nvarchar is incompatible with image

Operand type clash: datetime is incompatible with text

Operand type clash error

openxml/'for xml' image data problem

OPENXML, UPDATES, and SET Criteria

OPENXML, UPDATES, and SET Criteria

OpenXML XPath question

OPENXML won't return rows

OPENXML won't return rows

OpenXML Woes - 2Qs

OpenXML with namespace and no prefix

OpenXML with namespace and no prefix

OpenXML Identity Insert

Blog Archive

About Me