October 2007 - Posts

chunking binary or xml into sql

this is a little unique of a setup. i'm having to update a varbin field, but the data that is coming in from the other sources is being cast from xml. so i'm sort of replicating the stupid thing we are doing on the other side, but it seems like a good code sample for this particular method. if you wanted to chunk up xml, you could use this method.

 

using (SqlConnection cn = new SqlConnection(cs))

{

string staging_sql = @"

create table ##t (t varchar(max));

insert ##t select '';

";

string load_sql = @"update ##t set t .write(@a,null,0)";

string update_and_cleanup_sql = @"

update

tablewithvarbincolumn

set

varbincolumn = cast(cast( (select top 1 t from ##t) as xml) as varbinary(max))

where

id1= @a

and id2= @b;

drop table ##t;

";

//open our connection

cn.Open();

//first let's create the staging tbl to chunk to

using (SqlCommand cmd = new SqlCommand(staging_sql, cn))

cmd.ExecuteNonQuery();

//now let's chunk to the staging table

//reference this metholodogy justification at

// http://www.lacoude.com/Docs/public/public.aspx?doc=SQL90XML.PDF

using (FileStream fs = File.Open(filepath, FileMode.Open, FileAccess.Read))

using (BinaryReader br = new BinaryReader(fs))

using (SqlCommand cmd = new SqlCommand(load_sql, cn))

{

cmd.Parameters.Add("@a", SqlDbType.VarBinary, bufferSize);

bufferSize = 8040;

buffer = br.ReadBytes(bufferSize); //this is set as a byte[] above, we are merely redefining the size.

while (buffer.Length > 0)

{

cmd.Parameters[0].Value = buffer;

cmd.ExecuteNonQuery();

buffer = br.ReadBytes(bufferSize);

}

}

//now let's update all of the data in sql and cleanup

using (SqlCommand cmd = new SqlCommand(update_and_cleanup_sql, cn))

{

cmd.Parameters.AddWithValue("@a", v1);cmd.Parameters.AddWithValue("@b", v1);

cmd.ExecuteNonQuery();

}

//close our connection

cn.Close();

}

Posted Monday, October 29, 2007 10:28 AM by royashbrook | with no comments
Filed under: ,

chunking varbinary into sql2005

//reference this metholodogy justification at

// http://www.lacoude.com/Docs/public/public.aspx?doc=SQL90XML.PDF

int bufferSize = 0;

byte[] buffer = new byte[8040];

string update_sql = @"update tablewithblobs set blobcolumn .write(@a,null,0) where id = @b";

using (FileStream fs = File.Open(abinaryfilepath, FileMode.Open, FileAccess.Read))

using (BinaryReader br = new BinaryReader(fs))

using (SqlConnection cn = new SqlConnection(cs))

using (SqlCommand cmd = new SqlCommand(update_sql, cn))

{

cmd.CommandTimeout = 0;

cmd.Parameters.Add("@a", SqlDbType.VarBinary, bufferSize);

cmd.Parameters.AddWithValue("@b", v1);

cn.Open();

while ((bufferSize = br.Read(buffer, 0, buffer.Length)) != 0)

{

cmd.Parameters[0].Value = buffer;

cmd.ExecuteNonQuery();

}

cn.Close();

}

 

 

note that you will have to zero out the field if it's not empty by setting it to 0x0 first.

Posted Friday, October 26, 2007 6:56 PM by royashbrook | with no comments
Filed under: ,

HTTP Status and SubStatus codes

HTTP Status HTTP SubStatus Definition
100   Continue
101   Switching pools
200   OK.  The clinet request has succeeded
201   Created
202   Accepted
203   Non-authoritative information
204   No content
205   Reset content
206   Partial content
301    Permanent Redirect.
302    Object Moved
304    Not Modified.
307    Temporary redirect.
400    Cannot resolve the request.
401.x    Unauthorized.
401 1  Access is denied due to invalid credentials.
401 2  Access is denied due to server configuration favoring an alternate authentication method.
401 3  Access is denied due to an ACL set on the requested resource.
401 4  Authorization failed by a filter installed on the Web server.
401 5  Authorization failed by an ISAPI/CGI application.
401 7  Access denied by URL authorization policy on the Web server.
403.x None  Access is denied.
403 1  Execute access is denied.
403 2  Read access is denied.
403 3  Write access is denied.
403 4  SSL is required to view this resource.
403 5  SSL 128 is required to view this resource.
403 6  IP address of the client has been rejected.
403 7  SSL client certificate is required.
403 8  DNS name of the client is rejected.
403 9  Too many clients are trying to connect to the Web server.
403 10  Web server is configured to deny Execute access.
403 11  Password has been changed.
403 12  Client certificate is denied access by the server certificate mapper.
403 13  Client certificate has been revoked on the Web server.
403 14  Directory listing is denied on the Web server.
403 15  Client access licenses have exceeded limits on the Web server.
403 16  Client certificate is ill-formed or is not trusted by the Web server.
403 17  Client certificate has expired or is not yet valid.
403 18  Cannot execute requested URL in the current application pool.
403 19  Cannot execute CGIs for the client in this application pool.
403 20  Passport logon failed.
404.x    File or directory not found.
404 1  Web site not accessible on the requested port.
404 2  Web service extension lockdown policy prevents this request.
404 3  MIME map policy prevents this request.
405    HTTP verb used to access this page is not allowed.
406    Client browser does not accept the MIME type of the requested page.
407    Initial proxy authentication required by the Web server.
412    Precondition set by the client failed when evaluated on the Web server.
413    Request entity too large.
414    Request URL is too large and therefore unacceptable on the Web server.
415    Unsupported media type.
416    Requested range not satisfiable.
417    Expectation failed.
423    Locked error.
500.x    Internal server error.
500 11  Application is shutting down on the Web server.
500 12  Application is busy restarting on the Web server.
500 13  Web server is too busy.
500 14  Invalid application configuration on the server.
500 15  Direct requests for Global.asa are not allowed.
500 16  UNC authorization credentials are incorrect.
500 17  URL authorization store cannot be found.
500 18  URL authorization store cannot be opened.
500 100  Internal ASP error.
501    Header values specify a configuration that is not implemented.
502    Bad gateway.
503    Service unavailable.
504    Gateway timeout.
505    HTTP version not supported.

Posted Thursday, October 04, 2007 11:17 AM by royashbrook | with no comments
Filed under: