Read Large Binary Data From Sql Server 2005

Jul 14, 2007

Hi
I've followed a tutorial on how to write and read varbinary(max) data to and from a database. But when i try to read the data i get the error that the data would be truncated, but only when the varbinary(max)  is greater then 8kB. I've used a system stored procedure (sp_tableoption) to set the table that holds the data to store data outside rows. To select the data i'm using a stored procedure:
  
            SELECT imageData , MIMEType FROM Pictures WHERE (imageTitle = @imageTitle)
       
And then using an .aspx page to Response.Write the data:
Using conn As New sql.SqlConnection
            conn.ConnectionString = ConfigurationManager.ConnectionStrings("myConnectionString").ToString


            Dim getLogoCommand As New sql.SqlCommand
            getLogoCommand.CommandType = Data.CommandType.StoredProcedure
            getLogoCommand.CommandText = "GetPicture"
            getLogoCommand.Connection = conn

            Dim imageTitleParameter As New sql.SqlParameter("@imageTitle", Data.SqlDbType.NVarChar, 200)
            imageTitleParameter.Value = Request("imageTitle")
            imageTitleParameter.Direction = Data.ParameterDirection.Input

            getLogoCommand.Parameters.Add(imageTitleParameter)

            conn.Open()

            Using logoReader As sql.SqlDataReader = getLogoCommand.ExecuteReader
                logoReader.Read()
                If logoReader.HasRows = True Then
                    Response.Clear()
                    Response.ContentType = logoReader("MIMEtype").ToString()
                    Response.BinaryWrite(logoReader("imageData"))
                End If

            End Using

            conn.Close()

        End Using

 Can anyone please help me with this?!

View 2 Replies


ADVERTISEMENT

Read Binary Data From SQL Server

Oct 19, 2007

hello.
i have a _CommandPtr that has the type CommandTypeEnum::adCmdTex, and the CommandText a query("Select * from Table_1") and this select returns one row that has a binary data in it.




Code Block

_CommandPtr pCommand;
//Create the C++ ADO Command Object

pCommand.CreateInstance(__uuidof(Command));

pCommand->ActiveConnection = this->pConnection;

//Make the ADO C++ command object to accept stored procedure

pCommand->CommandType = CommandTypeEnum::adCmdText;

//Tell the name of the Stored Procedure to the command object

pCommand->CommandText = _bstr_t("select * from Table_1");

//get recordset
pRecordset = pCommand->Execute(NULL,NULL,CommandTypeEnum::adCmdText);

BYTE* buffer = NULL;
if(pRecordset != NULL)
{

while (!pRecordset->GetEndOfFile())
{

VARIANT var = pRecordset->Fields->GetItem("test")->Value;

void * pData = NULL;
SafeArrayAccessData(var.parray, &pData);
long size = GetArraySize(var, NULL);
buffer = new BYTE[size];
memcpy(buffer, pData, size);
SafeArrayUnaccessData(var.parray);
pRecordset->MoveNext();
}
pRecordset->Close();
}

where:



Code Block
long GetArraySize(const VARIANT& var, long * nElems)
{

if ( !(var.vt & VT_ARRAY) ) return -1;

long size = 0;
long dims = SafeArrayGetDim(var.parray);
long elemSize = SafeArrayGetElemsize(var.parray);
long elems = 1;
for ( long i=1; i <= dims; i++ )
{

long lbound, ubound;
SafeArrayGetLBound(var.parray, i, &lbound);
SafeArrayGetUBound(var.parray, i, &ubound);
elems *= (ubound - lbound + 1);
}
if ( nElems ) *nElems = elems;
size = elems*elemSize;
return size;
}






I enter in VARIANT var = pRecordset..... and if gives me a value... but when I put it in Memory explorer in VS, i only see this data " fe ee fe ee fe ee fe ee fe ee fe ee fe ee ...fe ee" and of course it brakes at
SafeArrayGetLBound(var.parray, i, &lbound);

Can someone tell me where I am doing a very bad thing?

P.S. I was able to read the binary data from the server using C#.NET 2.0.

View 1 Replies View Related

How To Store Large Chunks For Binary Data Into The DB?

Sep 7, 2006

From what I can see, the 'varbinary(max)' data type is not supported, and the 'image' data type is supposed to go away. Is there some other way to store large chunks (10MB to 100MB) of data into an SSEv DB?

If I have to use the 'image' data type to so this, does anyone have a code sample that would let me push an array() of numbers into an 'image' field, and unload an 'image' field into an array()?

TIA

Pat

View 7 Replies View Related

Read Binary Data From Table And Save On Hard Drive

Oct 18, 2007

Hi all,
I have one table with a column of type 'image'. There are manytypes of files saved in that column (i.e. .Doc,Xls,Pdf,jpg,gif etc.). What I want is, read that files from database and save it in temp folder on d drive of server. Can anyone help me in my problem?
Thanx in advance

View 1 Replies View Related

Sending Large Binary To SQL Server Via ADO.NET

Feb 27, 2007

Hi,I am having some trouble with my ASP page sending a file to a SQL Server 2005 database running on another machine. An exception is generated as follows:A transport-level error has occurred when sending the request to the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)  It seems to work ok with smaller files, but when I attempt to upload a 11MB file, it gives me the above exception. I upped the httpRuntime maxRequestLength in the web.config to 2048. Any Ideas? Here is my code:1 if (FileUploader.HasFile)
2 {
3
4 // call the stored proc
5 SqlConnection conn = new SqlConnection();
6 conn.ConnectionString = "Password=password;Persist Security Info=True;User ID=MyUser;Initial Catalog=MyDb;Data Source=MySqlServerMachine;Connect Timeout=300";
7 conn.Open();
8
9 SqlCommand cmd = new SqlCommand();
10 cmd.Connection = conn;
11 cmd.CommandType = CommandType.StoredProcedure;
12 cmd.CommandText = "spAddTestBinary";
13
14 SqlParameter param = new SqlParameter();
15 param.ParameterName = "@binaryParam";
16 param.SqlDbType = SqlDbType.VarBinary;
17 param.Direction = ParameterDirection.Input;
18 param.Value = FileUploader.FileBytes;
19 cmd.Parameters.Add(param);
20
21
22 cmd.ExecuteNonQuery();
23
24
25 conn.Close();
26 }
  Here is my table:1 BinaryTable(
2 [id] [int] IDENTITY(1,1) NOT NULL,
3 [myBinary] [varbinary](max) NULL,
4 CONSTRAINT [PK_BinaryTable] PRIMARY KEY CLUSTERED
5 (
6 [id] ASC
7 )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
8 ) ON [PRIMARY]
My stored proc:1 ALTER PROCEDURE [dbo].[spAddTestBinary]
2 -- Add the parameters for the stored procedure here
3 @binaryParam varbinary(MAX)
4 AS
5 BEGIN
6 -- SET NOCOUNT ON added to prevent extra result sets from
7 -- interfering with SELECT statements.
8 SET NOCOUNT ON;
9
10 -- Insert statements for procedure here
11 Insert into BinaryTable
12 (myBinary)
13 Values
14 (
15 @binaryParam
16 )
17 END
 

View 3 Replies View Related

Using DAO To Access Binary Data In Sql Server 2005?

Aug 29, 2006

(Appologies if this group isn't the best place for this post)Is it possible to use DAO 3.6 to access binary data (varbinary(max)) inSql Server 2005? I have images and sound in a Sql 2005 DB that I needto retrieve (and write) with DAO (ADO and ADO.Net are not options asthis is legacy code that can't be changed).Thx,Marcus

View 2 Replies View Related

SQL Server 2008 :: Large Binary Dataset - Database Or File System?

Jun 2, 2015

I have a well-structured but also very large binary data-set that is generated by a C++ application every five minutes. The data needs to be accessed by SQL applications. Since data is generated every five minutes, performance is key, both for write and read. The data set is about 500MB.If data is written to the file system, the write performance doesn't involve SQL server. For reading it, I have a CLR to read the portions of the data that I need based on offset and length. That works and is very fast. The problem is that data is stored in the file system, so it is not self-contained within the database.

A second option that I haven't explored yet, is to write the data into a table as VARBINARY(MAX). I would read the data using SUBSTRING with appropriate offset and length. Performance of SQL write/read of binary data of this size, and whether there is a third option I haven't thought off. I'm using SQL Server 2014.

View 5 Replies View Related

How To Read And Store A Large File From SQL Server 2000?

Feb 23, 2006

hi..
   I want to store a RMVB file to SQL SERVER 2000 ,and read from it,iwant to play the RMVB file in web,the size of the RMVB file is more than 300MB less 1G.the  SQL Field Image can include it.
Now My Quesstion is How can i Store and Read the RMVB file from SQL Server2000?
I used SqlInsertCommand.ExecuteNoquery()  in my program,but it Too slow,ao make a unknown error.
Thank you for your help.

View 6 Replies View Related

Best Way To Insert Large Amounts Of Data From A Webform To SQL Server 2005

Oct 21, 2007

HiI have a VB.net web page which generates a datatable of values (3 columns and on average about 1000-3000 rows).What is the best way to get this data table into an SQL Server? I can create a table on SQL Server no problem but I've found simply looping through the datatable and doing 1000-3000 insert statements is slow (a few seconds). I'd like to make this as streamlined as possible so was wondering is there is a native way to insert all records in a batch via ADO.net or something.Any ideas?ThanksEd

View 1 Replies View Related

SQL Server 2005 Unable To Read Image Data

Nov 27, 2006

This just happened today and I dont know why.  I have a database on a SQL server 2005 database, that has a table called photos.  It contains image data, but not the column says <Unable to read data>.  But before that I get an error when I try to execute the table to retreive the data.  The error is below. SQL Execution Error:Executed SQL statement: SELECT ID, AlbumID, Caption, BytesOriginal, BytesFull, BytesThumb FROM PhotosError Source: System.DataError Message: Invalid attempt to Read when reader is closed. This happens with SQL Server Management Studio, and Visual Studio 2005 when I try to access it.  I tried two computers so its not that.  Any help or insight would be appreciated. 

View 2 Replies View Related

SqlServer 2005 String Or Binary Data Would Be Truncated When Data Is OK

Feb 21, 2006

When using AquaData or JDBC (inet tds driver), when doing an insert using SqlServer 2005, I get error "String or binary data would be truncated" when the data is actually OK. There are no triggers, etc. that would confuse the situation. It works fine in SqlServer 2000.

The scenario is as follows:

Create table:
create table test3 (
name varchar (18) ,
tbname varchar (18)
)

Create and populate table:
create table maxtable (
tablename varchar (18) not null,
[...]
)

Try to insert into test3:
insert into test3 (name, tbname)
select i.name, o.name
from dbo.sysindexes i, sysobjects o, maxtable m
where i.indid > 0 and i.indid < 255
and i.id = o.id and i.indid = 1
and o.name = lower(m.tablename)

And I get the error "String or binary data would be truncated." The values being selected for i.name and o.name have maximum length of 18. There are other rows in sysindexes and sysobjects with longer values, but they are not being selected.

The error does not occur with SQL Server Management Studio, and does not occur using SqlServer 2000.

View 6 Replies View Related

How To Read Binary File

Jun 6, 2007

Need help reading a binary file see below for details...
 
 I have uploaded a csv file into a sql table.
Now i want to extract the data and insert the data in the csv file into another sql table.
 What commands can i use in sql to extract/ read  the data ? 
 
 
 

View 7 Replies View Related

Large Binary Writes Slower Than Expected

Jul 23, 2005

Hello, we are investigating the use of SQL Server as a backend to ourscientific imaging application. We have found that when we write alarge image (60 Megabytes) the performance is quite a bit slower thanwriting 60 single megabyte images. The tests were performed runningSQL Server 2000 on Windows 2003 Enterprise on a single machine toeliminate the network's contribution. Perhaps there is a configurationoption that will allow us to tune SQL Server to better handle largewrites?TIA

View 1 Replies View Related

VERY Large Binary Import/export Headache

Oct 13, 2006

Hi,

I am currently importing (and exporting) binary flat files to and from Db fields using the TEXTPTR and UPDATETEXT (or READTEXT for export) functions. This allows me to fetch/send the data in manageable packet sizes without the need to load complete files into RAM first.

Given that some files can be up to 1Gb in size I am keen to find out a new way of doing this since the announcement that TEXTPTR, READTEXT and UPDATETEXT are going to be removed from T-SQL.

I had a quick foray into SSIS but couldn't find anything suitable which brings me back to T-SQL. If anyone knows a nice elegant way of doing this and is prepared to share, that would be grand.

Thanks for your time,
Paul

View 9 Replies View Related

How To View Binary Data In MSSQL 2005

Sep 5, 2007

Hi all,

I have a problem reading binary data in MSSQL using the Server Mgmt Studio. All it shows in the column is "<Binary data>". Is there a way to view this data at least the SIZE?

Thanks.

View 2 Replies View Related

Storing Zip Files Read As Binary In A Field

Feb 16, 2004

i need to store a bmp or zipped file in a field of a ms sql db. i read the file using vb6 o.net, and in my mind i think sto store it in binary mode. the files could be more of 12 mega. whitch kind of field a could use?

View 1 Replies View Related

T-SQL (SS2K8) :: Store Binary Data Rather Than Int Or Binary?

May 7, 2015

I'm using a bit-wise comparison to effectively store multiple values in one column. However once the number of values increases it starts to become too big for a int data type.you also cannot perform a bitwise & on two binary datatypes. Is there a better way to store the binary data rather than int or binary?

View 9 Replies View Related

String Or Binary Data Would Be Truncated. I Get This Error When Entering Data Using Sql Server Management Studio Express.

May 21, 2008

String or binary data would be truncated. I get this error when entering data using sql server management studio express.

I am not running a sql to insert or update the table. This is through the EDI.

The data type is varchar(100). I enter one character and it errors on me. So this isn't a string being too long problem.

Any ideas?



View 2 Replies View Related

Passing A Binary Value To Sql Server 2005

Jun 27, 2007

I have been pulling my hair out on this.



I am trying to pass a binary value from an ASP.NET app to a VERY simple stored proc, and I can NOT get it to work!



Here is some of my code:






Code Snippet

ALTER PROCEDURE [dbo].[sp_SelectAppointments] @PID VarChar(50)



AS

BEGIN

--set @PID = 0x00000000000000B1

--PLEASE NOTE HERE THAT THE ABOVE VALUE(WHEN UNCOMMENTED) RETURNS RECORDS

--WHEN THE VALUE IS IN QUOTES (AS IT WOULD BE) IT DOES NOT WORK

SET NOCOUNT ON;

SELECT Appt_Date, Appt_Description, Rn_Appointments_Id

FROM Rn_Appointments

WHERE project = @PID --Project is a binary field.

END



I created a Dataset in VS2005, and here is the code that passes the value to the Dataset:




Code Snippet

Dim PID As String = Request.QueryString("pid")

PID = "0x00000000000000B1"

Dim da2 As New sp_SelectAppointmentsTableAdapter

GridView1.DataSource = da2.GetData2(PID)

GridView1.DataBind()



What am I missing?? Can anybody please help me out here?



Thank you.



Steve

View 1 Replies View Related

Data Access To Large Tables In Sql 2005

Mar 1, 2007

hi all,

i have a large table in sql server 2005 (it has about 6 columns and 10 million records).

i need to work in a linear way on all the records (i know it sounds dumb but i need to work on all records).

now, obviously when trying to work on this table sql server get stuck for timeout or something like that...



i've noticed that a simple function like "select top 100 * from ExportTable" still works.

is there any way to have sql send me the data when it access it so that i'll still be able to proccess it on the same time, i basically work using dataset so that fixing the timeout wont be helpfull since windows probably wont allow me to load this amount of data into memory.



can any1 help?

Z

View 1 Replies View Related

Binary Data And Sql Server

Sep 20, 2007

Here is my task  I am storing pdf's in sql server. I would like to retrieve the binary data from sql server and write the pdf content into an existing aspx page to the appropriate pageview section.  What is the best way to handle this.  The code works below but it loads a new browser with the content.  I need it to appear in it's tabbed section in the original aspx file.  Any assistance you can give me would be greatly appreciated.
 Thanks Jerry
oSQLConn.Open()Dim myreader As SqlDataReader
myreader = myCommand.ExecuteReader
Response.Expires = 0
Response.Buffer = True
Response.Clear()
Do While (myreader.Read())
Response.ContentType = ("application/pdf")Response.BinaryWrite(myreader.Item("img_content"))
Loop

View 2 Replies View Related

Newbe Question: Need A Way To Read In Large Query With .net

Feb 26, 2007

Could someone please point me in the right direction on how to read ina large query with .net.I am trying to emulate a legacy database system so I don't know theupper bounds of the sql query. An example query would be somethinglike:Select * from invoices where year 1995the query must be updatable and only return say 10 to 100 rows at atime.It should also be forward only and discard rows no longer in use tosave memory.And if at all possible I would like to lock one row at a time as therow is read in.

View 5 Replies View Related

Example Of Binary Data Type In SQL Server

Jan 29, 2006

There are two datatypes for storing binary data type in the SQL Server:1. binary - for fixed length binary data2. varbinary - for variable length dataMy question is: how is data inserted into them? Do they have anydelimiters that go into the insert statement like strings and datetimeshave? What format (hex/decimal?) do they accept data in? Can you pleasegive me an insert statement example?

View 9 Replies View Related

Storing Image In Sql Server 2005 In Binary Format?

Nov 1, 2006

please mail me at lavkesh_kumar@yaho.com

View 2 Replies View Related

Extracting Binary Data From SQL DB To A Location On The Web Server

Mar 15, 2007

Hey all, WE have a document management system where by Adminstrators can upload documents, once the document is uploaded the binary data is stored on in a folder on the web server.  We used to stored the documents in the actaul db table, but we found that there were to many documents and it was using alot of space on db server. So my boss has decided we are now going to upload the binary data onto the web server.  Currently we are donig this with new documents which have been added or documents which are gettinguploaded when reloading, but there are many documents in the db table which have not been updated and are still embedded in the db table.  So i need to figure out how to go about copying the data storewd in the db table and storing it in web servers folder location. I've tried various things for a enitre day but im going round in circiles.                             MemoryStream mStream = new MemoryStream((Byte[])dtrResults["file"]);                            BinaryReader bReader = new BinaryReader(mStream);                            int intFileSize = (int)mStream.Length;                            Byte[] byteFile = (Byte[])dtrResults["file"]; i can get to this state but then how do i create a folder on the BinaryREader to then store the binary data of the file to the location.                             BinaryReader bReader2 = new BinaryReader(File.Open(strDocFolder + strSavedFileName, FileMode.Create));                            int count2 = bReader2.Read(byteFile, 0, intFileSize);                            bReader2.Close();i've also tried this but when the file gets created in the folder there is no content.  i do know that the file does contain content as ive tried this and downlaoding the file from that page acctually works                            string strContentTpe = WValue.WStr(dtrResults["contenttype"]);                            int intFileSize = VValue.VInt(dtrResults["filesize"]);                       /    Byte[] byteFile = ((Byte[])dtrResults["file"]);                            //Downloads the data correctly                           Response.ClearContent();                          Response.ClearHeaders();                           Response.AddHeader("Content-Disposition", "attachment; filename="" + WValue.WStr(dtrResults["docfilename"]) + """);                           Response.AddHeader("Content-Length", WValue.WStr(intFileSize));                          Response.ContentType = strContentTpe;                           Response.BinaryWrite(byteFile); I hope ive made some snese andthat someone can hlep me. Have a nice dayZal     

View 2 Replies View Related

SQL 2012 :: Save Binary Data Into Server

Oct 15, 2014

I was assigned a project to read binary data file (2G) and select data from OrderID, OrderDate and Price three columns (there are about 50 columns) into SQL table.

Where to start? Do I need to convert entire binary data file into text file?

View 0 Replies View Related

Sql 2005 Updating Table Definition With 'large' Amounts Of Data - Timeout

Feb 4, 2006

I'm trying to move my current use of an sql 2000 db to sql 2005.



I need to update a table definition (to change a field to an Identity)



I'm getting a dialog box (in SQL server management studio) on save saying :



'xxxx' table

- Saving Definition Changes to tables with large amounts of data could
take a considerable amount of time. While changes are being
saved, table data will not be accessible.



I press 'Yes' to the dialog box.



After 35 seconds, I get another dialog box saying:



'xxxx' table

- Unable to modify table.

Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.



Well, the server is responding and I can query that talbe and other, I
can add/delete rows to other columns. I can modify other
(smaller) tables.



Any ideas where I can change this timeout?



Daniel

View 10 Replies View Related

Problem In Converting MS Access OLE Object[Image] Column To BLOB (binary Large Object Bitmap)

May 27, 2008

Hi All,
i have a table in MS Access with CandidateId and Image column. Image column is in OLE object  format. i need to move this to SQL server 2005 with CandidateId column with integer and candidate Image column to Image datatype.
its very udgent, i need any tool to move this to SQL server 2005 or i need a code to move this table from MS Access to SQL server 2005 in C#.
please do the needfull ASAP. waiting for your reply
with regards
 
 
 

View 1 Replies View Related

SQL Server 2008 :: Storing Images As Binary Data In Table

Feb 25, 2015

I want to store Images as binary data in SQL table and compare it each time with a image file I am getting. I've tried below approach but getting error:

DROP TABLE #BLOBTest
CREATE TABLE #BLOBTest
(
TestID int IDENTITY(1,1),
BLOBName varChar(50),
BLOBData varBinary(MAX)
);

[Code] ....

Error: Msg 4861, Level 16, State 1, Line 10
Cannot bulk load because the file "C:Files12656.jpg" could not be opened. Operating system error code 3(failed to retrieve text for this error. Reason: 15105).

View 4 Replies View Related

Report Builder - Read Data From SQL Server With Ntext Data Type

Apr 16, 2008

Hi all,

I have a column in SQL server which is of type ntext. Selecting the specific column to view it in report builder, an error message appears with the following description:

- Cannot run this report. The grouping expression 'nameofcolumn' returns the datatype binary. The Grouping Expression cannot return binary data.

Report Builder recognises this as if it was an image...

Thanks in advance!

View 2 Replies View Related

Storing Large Files In The SQL Server 2005

Feb 9, 2006

I have a table that I'm inserting a file into and using the Image data type to store the binary object.  Now the code below works fine for files around 1.5 MB, but anything larger and it's like the code won't even execute and I get a Page Not found error.
I'm in the process of running some traces to find out what's going on in the backend, but I'm assuming there's something amiss with my code.  The Image data type should handle files that size with no problem but for some reason it isn't.
Does anyone see anything wrong?
Thanks
Dim iLength As Integer = CType(File1.PostedFile.InputStream.Length, Integer)
If iLength = 0 Then Exit Sub 'not a valid file
Dim sContentType As String = File1.PostedFile.ContentType
Dim sFileName As String, i As Integer
Dim bytContent As Byte()
ReDim bytContent(iLength) 'byte array, set to file size

'strip the path off the filename
i = InStrRev(File1.PostedFile.FileName.Trim, "")
If i = 0 Then
sFileName = File1.PostedFile.FileName.Trim
Else
sFileName = Right(File1.PostedFile.FileName.Trim, Len(File1.PostedFile.FileName.Trim) - i)
End If
conn = New SqlConnection(eco)
conn.Open()
cmd = New SqlCommand("INSERT INTO ECO_Attachments (ECOID, FromType, DocName,OldRev,NewRev,NtLogin,DisplayName, FileName, FileSize, FileData, ContentType) VALUES (@ECOID, @FromType,@DocName,@OldRev,@NewRev,@NtLogin,@DisplayName, @FileName, @FileSize, @FileData, @ContentType) ")
cmd.Connection = conn
Try
File1.PostedFile.InputStream.Read(bytContent, 0, iLength)
With cmd
.Parameters.Add("@ECOID", SqlDbType.Int)
.Parameters.Add("@FromType", SqlDbType.NVarChar, 50)
.Parameters.Add("@DocName", SqlDbType.NVarChar, 250)
.Parameters.Add("@OldRev", SqlDbType.NVarChar, 50)
.Parameters.Add("@NewRev", SqlDbType.NVarChar, 50)
.Parameters.Add("@NTLogin", SqlDbType.NVarChar, 100)
.Parameters.Add("@DisplayName", SqlDbType.NVarChar, 200)
.Parameters.Add("@FileName", SqlDbType.NVarChar, 255)
.Parameters.Add("@FileSize", SqlDbType.Real)
.Parameters.Add("@FileData", SqlDbType.Image)
.Parameters.Add("@ContentType", SqlDbType.NVarChar, 50)
.Parameters("@ECOID").Value = ECOID
.Parameters("@FromType").Value = From
.Parameters("@DocName").Value = DocName
.Parameters("@OldRev").Value = OldRev
.Parameters("@NewRev").Value = NewRev
.Parameters("@NTLogin").Value = NTLogon
.Parameters("@DisplayName").Value = DisplayName
.Parameters("@FileName").Value = sFileName
.Parameters("@FileSize").Value = iLength
.Parameters("@FileData").Value = bytContent
.Parameters("@ContentType").Value = sContentType
.ExecuteNonQuery()
'.ExecuteScalar()
End With
Catch ex As Exception
Response.Write(ex)
'Handle your database error here
conn.Close()
End Try

View 1 Replies View Related

Restoring An SQL 2005 Server Express DB With A LOG That Is To Large

Jul 31, 2006

SQL 2005 Express - Database Restore size problem
Was this post helpful ?














Hi,

I'm trying to restore a SQL Server DB Backup from a SQL Server DB Server on to my Laptop (SQL 2005 Express)

When I execute a restore filelistonly command on the backup file, It seems that the Database included is 1GB, but the Log file is 91 GB in size, which exceeds my diskspace.

I can restore the Data on its own without the log file, but the the Database stays in "restoring" mode. I've tried to switch the restore flag off (update sys.databases set state = 0 where name = 'G001'), but I can`t seem to be able to do it, even if I try to allow updates via:

sp_configure 'allow updates', 1

GO

RECONFIGURE WITH OVERRIDE

GO



Any ideas how I can restore the database without restoring the enormous logfile?

Thanks in advance...



View 3 Replies View Related

SQL Server 2005 Large IO And Logical Reads

May 22, 2008

A table in one of my databases is running very slowly. The IO is very high and below is a printout from the SET STATISTICS IO ON command run on a common query used on the table:


(4162 row(s) affected)

Table 'WebProxyLog'. Scan count 3, logical reads 873660, physical reads 3493, read-ahead reads 505939, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

I have a clustered unique index and a nonclustered index on the table. I have ran SQL Profiler and opened the trace in Database Tuning Advisor, DTA displays 0% improvement suggestions. I have a number of statistics on the table and index which are all up to date and fragmentation is less than 1%. I've tried a number of variations on indexes to improve performance but to no avail. There is only one query which runs on the table, and the nonclustered index created on the table did significantly improve performance, however the query still runs at around 23 seconds. The query does bring back a large amount of data however i'm sure there is a way to bring down the IO and logical reads to improve performance.

The table and index scripts are below:




Code Snippet
-- =================== Table and Clustered index ===========================
CREATE TABLE [dbo].[WebProxyLog](
[ClientIP] [bigint] NULL,
[ClientUserName] [nvarchar](514) NULL,
[ClientAgent] [varchar](128) NULL,
[ClientAuthenticate] [smallint] NULL,
[logTime] [datetime] NULL,
[servername] [nvarchar](32) NULL,
[DestHost] [varchar](255) NULL,
[DestHostIP] [bigint] NULL,
[DestHostPort] [int] NULL,
[bytesrecvd] [bigint] NULL,
[bytessent] [bigint] NULL,
[protocol] [varchar](12) NULL,
[transport] [varchar](8) NULL,
[operation] [varchar](24) NULL,
[uri] [varchar](2048) NULL,
[mimetype] [varchar](32) NULL,
[objectsource] [smallint] NULL,
[rule] [nvarchar](128) NULL,
[SrcNetwork] [nvarchar](128) NULL,
[DstNetwork] [nvarchar](128) NULL,
[Action] [smallint] NULL,
[WebProxyLogid] [int] IDENTITY(1,1) NOT NULL,
CONSTRAINT [pk_webproxylog_webproxylogid] PRIMARY KEY CLUSTERED
(
[WebProxyLogid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

-- =================== Nonclustered Index ===========================

CREATE NONCLUSTERED INDEX [dta_ix_WebProxyLog_Kaction_clientusername_logtime_uri_mimetype_webproxylogid] ON [dbo].[WebProxyLog]
(
[Action] ASC
)
INCLUDE ( [ClientUserName],
[logTime],
[uri],
[mimetype],
[WebProxyLogid]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

-- =================== Query which is called regularly on the table ===========================

SELECT [User] = CASE
WHEN LEFT(clientusername,3) = domain' THEN RIGHT(clientusername,LEN(clientusername) - 3)
ELSE clientusername
END,
logtime AS [Date],
desthost AS [Site],
uri AS [Actual Site]
FROM webproxylog
WHERE CONVERT(Datetime,CONVERT(VarChar(25),logtime,106),106) BETWEEN '20 apr 2008' AND '14 may 2008'
AND(RIGHT(uri,4) NOT IN('.css','.jpg','.gif','.png','.bmp','.vbs'))
AND (RIGHT(uri,3) NOT IN('.js'))
AND LEFT(mimetype,6) = 'text/h'
AND (uri NOT LIKE '%sometext.local%')
AND (uri NOT LIKE '%sometext.co.uk%')
AND [action] = 9
AND (clientusername IN ('USERNAME'))
ORDER BY logtime ASC;





PS There are 60,078,605 rows in the table

Please help!

Many Thanks

View 6 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved