Soring file >60MB in sql server 2008 R2 with FILESTREAM

[andlas63]

Hello everybody,

I’ve to save files in a MS SQL Server database with a TD 6.0 SP7 program, because I want to use the FILESTREAM-Property. Everything works fine, except files larger then 60MB??? For this files, I get a network error from Microsoft ODBC Driver (see at end).

Here is my configuration:

Windows 7
MS SQL Server 2008 R2 Express (local installation)
TD 6.0 SP7
ODBC Connection with SQL Server ( not native 10.0, doesn’t work !!! )
LONGBUFFER = 83886080 ( =80MB increasing doesn’t help, for testing in SQL.INI, should be set in program by DBP )

Code: Select all

CREATE TABLE [dbo].[FILE0001](
	[ID] [int] NOT NULL,
	[TEST0001_ID] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
	[ERSTELLT] [datetime] NOT NULL,
	[DATALEN] [int] NOT NULL,
	[STATUS] [char](1) NULL,
	[APPNAME] [varchar](20) NOT NULL,
	[PFADNAME] [varchar](254) NOT NULL,
	[FILEDATA] [varbinary](max) FILESTREAM  NULL,
PRIMARY KEY CLUSTERED ….

…
Long String: mFILEDATA
…

Set nLen = VisFileGetSize( sFile ) 
If SalFileOpen( hFile, sFile, OF_Binary|OF_Read )
	Set nRead = SalFileRead( hFile, mFILEDATA, nLen )
	Call SalFileClose( hFile )
	If nRead = nLen
		….
		Set mDATALEN = SalGetBufferLength( mFILEDATA )
		Set bOk = bOk AND SqlPrepare( hSql, "INSERT INTO FILE0001
							( ID, APPNAME, PFADNAME, ERSTELLT, STATUS, DATALEN, FILEDATA )
						VALUES
							( :mID, :mAPPNAME, :mPFADNAME, :mERSTELLT, :mSTATUS, :mDATALEN, CAST( :mFILEDATA as varbinary(max) ) )" )
		Set bOk = bOk AND SqlSetLongBindDatatype( 7, 23 )
		Set bOk = bOk AND SqlExecute( hSql )
		….

I compared and opened the files (binary and textfiles), which SQLServer stored in the FILESTREAM directory. The files are ok.

I tried a lot ( example: Binary: mFILESTREAM and VisFileReadBinary and without cast in the statement ). The solution above is the only one I found, to make it work.

But I don’t understand the restriction of the filesize (ODBC Error)???

By the way, I wrote a program, which stores and reads the same files in SQL Base 11.0.2. But on this way, I’ve to implement something like FILESTREAM-ing by myself. Otherwise the database gets too large.


Best regards,
Andreas Laschet

Code: Select all

ODBC Error:

 

Microsoft SQL Server:233[Microsoft][ODBC SQL Server Driver][DBMSLPCN]ConnectionWrite (WrapperWrite()).Microsoft SQL Server:11[Microsoft][ODBC SQL Server Driver][DBMSLPCN]Allgemeiner Netzwerk


Extract of the ODBC-Tracefile:
fileread        2ec4-6d0	EXIT  SQLPutData  with return code -1 (SQL_ERROR)
		HSTMT               0x0546DF50
		PTR                0x0ABDF824
		SQLLEN                 65534

		DIAG [01000] [Microsoft][ODBC SQL Server Driver][DBMSLPCN]ConnectionWrite (WrapperWrite()). (233) 

		DIAG [08S01] [Microsoft][ODBC SQL Server Driver][DBMSLPCN]Allgemeiner Netzwerkfehler. Weitere Informationen finden Sie in der Dokumentation über Netzwerke. (11) 

fileread        2ec4-6d0	ENTER SQLErrorW 
		HENV                0x01DC3810
		HDBC                0x0546DA58
		HSTMT               0x0546DF50
		WCHAR *             0x0012DDF4
		SDWORD *            0x0012DE3C
		WCHAR *             0x0012D9F4 
		SWORD                      168 
		SWORD *             0x0012DE50

fileread        2ec4-6d0	EXIT  SQLErrorW  with return code 0 (SQL_SUCCESS)
		HENV                0x01DC3810
		HDBC                0x0546DA58
		HSTMT               0x0546DF50
		WCHAR *             0x0012DDF4 [       5] "01000"
		SDWORD *            0x0012DE3C (233)
		WCHAR *             0x0012D9F4 [      78] "[Microsoft][ODBC SQL Server Driver][DBMSLPCN]ConnectionWrite (WrapperWrite())."
		SWORD                      168 
		SWORD *             0x0012DE50 (78)

fileread        2ec4-6d0	ENTER SQLErrorW 
		HENV                0x01DC3810
		HDBC                0x0546DA58
		HSTMT               0x0546DF50
		WCHAR *             0x0012DDF4
		SDWORD *            0x0012DE3C
		WCHAR *             0x0012D9F4 
		SWORD                       66 
		SWORD *             0x0012DE50

fileread        2ec4-6d0	EXIT  SQLErrorW  with return code 1 (SQL_SUCCESS_WITH_INFO)
		HENV                0x01DC3810
		HDBC                0x0546DA58
		HSTMT               0x0546DF50
		WCHAR *             0x0012DDF4 [       5] "08S01"
		SDWORD *            0x0012DE3C (11)
		WCHAR *             0x0012D9F4 [      66] "[Microsoft][ODBC SQL Server Driver][DBMSLPCN]Allgemeiner Netzwerk\ 0"
		SWORD                       66 
		SWORD *             0x0012DE50 (142)