MS SQL Server :: From Byte[] To A .rdl File

Writing Byte Stream To Flat File Destination (ebcdic)
Hello all,
I was trying to run a test to write a ebcdic file out with a comp - 3 number (testing this for other people) and have run into a problem writing the string out to the flat file destination. I have the following script component:

Code Block

' Microsoft SQL Server Integration Services user script component
' This is your new script component in Microsoft Visual Basic .NET
' ScriptMain is the entrypoint class for script components
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Public Class ScriptMain
Inherits UserComponent
Public Overrides Sub CreateNewOutputRows()
'
' Add rows by calling AddRow method on member variable called "Buffer"
' E.g., MyOutputBuffer.AddRow() if your output was named "My Output"
'
Output0Buffer.AddRow()
Dim myByteArray() As Byte = {&H12, &H34, &H56, &H7F}
Output0Buffer.myByteStream = myByteArray
Output0Buffer.myString = "ABCD"
Output0Buffer.myString2 = "B123"
myByteArray = Nothing
End Sub
End Class

I have added myByteStream as a DT_BYTES length 4, myString as (DT_STR, 4, 37) and myString2 as (DT_STR, 4, 37) to the output 0 buffer.

I then add a flat file destination with code set 37 (ebcdic us / canda) with the corresponding columns using fixed width.

When i place a dataviewer on the line between the two the output looks as I expect ("0x12 0x34 0x56 0x7F", "ABCD", "B123"). However, when it gets to the flat file destination it errors out with the following:

Code Block
[Flat File Destination [54]] Error: Data conversion failed. The data conversion for column "myByteStream" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".

If i increase the size of the byte stream (say, to 50) the error goes away but I am left with the string "1234567F" instead of the appropriate hex values. Any clues on how to go about this? I obviously don't care if it gets transferred to "readable" text as this is supposed to be a binary stream, thus the no match in target page seems superfulous but is probably what is causing the problems.

NOTE: this is relating to the following thread (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2300539&SiteID=1) in that I am trying to determine why these people are not seeing the "UseBinaryFormat" when importing an EBCDIC file (i see this fine when i use an ftp'd file, but it auto converts to ascii) with comp-3 values. I also see the "UseBinaryFormat" when I am importing a regular EBCDIC file which I create that has no import errors with zoned decimals.

View Replies !

SQL2005 Data Import Error, Unicode Data Is Odd Byte Size For Column &&<n&&>. Should Be Even Byte Size
Hi, I have a problem importing data from SQL Server 2000 'text' columns to SQL Server 2005 nvarchar(max) columns. I get the following error when encountering a transfer of any column that matches the above.
The error is copied below,

Any help on this greatly appreciated...

ERROR : errorCode=-1071636471 description=An OLE DB error has occurred. Error code: 0x80004005.An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Unicode data is odd byte size for column 3. Should be even byte size.". helpFile=dtsmsg.rll helpContext=0 idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-DA807BCDC2AC} (Microsoft.SqlServer.DtsTransferProvider)

Many thanks

View Replies !

Byte[] Value In SQL Query...
Hi people,I have a little problem with this!There are some variables in C# code: int personID = 10;
string personName = "Tom";

System.IO.BinaryReader reader = new System.IO.BinaryReader(FileUploadPersonPhoto.PostedFile.InputStream);

byte[] personPhoto = reader.ReadBytes(FileUploadPersonPhoto.PostedFile.ContentLength); After that, there is a SQL query: string query = "INSERT INTO PersonTable (PersonID, PersonName, PersonPhoto) VALUES ("

+ personID + ", '" + personName + "', " + personPhoto + ")"; In Debug mode, value of the query is "INSERT INTO PersonTable (PersonID, PersonName, PersonPhoto) VALUES (10, 'Tom', System.Byte[])" and it does not work! Is there any prefix or something else that I should put and make it work?Thank you in advance!P.S. Do not want to use sql parameters at this piont!

View Replies !

SQL Binary To C# Byte[]
I have a binary column with length 20 in SQL server table. I store dynamically C# byte[] value range from 0 to 19. So for example, If I store length of 16 and when I try to retrive it back into byte [] in C# it returns whole length 20. When I see in debug it has value from 0 to 15 which I want to use but from 16 to 19 is zero. How can I get just length value which I stored.

I use DataRow to get whole row and from the row object I extract byte [] based on column name.

Thank you in advance....

View Replies !

DATEADD With 8 Byte Int?
I have a bigint column called "MillisecondsSince1970" that I need to convert to a date - SSIS is erroring out when I use DATEADD with the 8 byte int (if I use 4 byte it works but the column is bigger than 4 byte). The error is really lame:

[Derived Column [79]] Error: The "component "Derived Column" (79)" failed because error code 0xC0049067 occurred, and the error row disposition on "output column "Date" (100)" specifies failure on error. An error occurred on the specified object of the specified component.

Anyone have a way around it... a VB.NET equivalent of DATEADD or something else I can do?

View Replies !

CLR UDT Max Byte Size
Hello,

I need some help working with CLR UDTs. I have created two UDTs called trajectory and point. Each trajectory consists of a list of points. Each point consists of three members : lon( type double), lat( type double) and datetime.

I have written my own IBinarySerialize.Write method for the trajectory type which is the following:

Dim maxSize As Integer = 4000
Dim value As String = ""
Dim paddedvalue As String

Dim i As Integer
Dim pt As Point

For i = 0 To point_list.Count - 1

pt = point_list.Item(i)

If i = 0 Then
value = value & pt.X & "|" & pt.Y & "|" & pt.D

Else

value = value & ">" & pt.X & "|" & pt.Y & "|" & pt.D
End If

Next

paddedvalue = value.PadRight(maxSize, ControlChars.NullChar)

For i = 0 To paddedvalue.Length - 1 Step 1
w.Write(paddedvalue(i))
Next

If I try to store 225 points for a trajectory then the following error occurs:

System.Data.SqlTypes.SqlTypeException: The buffer is insufficient. Read or write operation failed.

Why is that happening? The limit size for a UDT is 8000bytes. Each point needs 24Bytes (2*SizeOf(double) + sizeOf(Datetime)).

Please help
ST

View Replies !

Cannot Convet Sql Image To Byte[]
I have followed many examples found on this site, but still get an invalid cast execption when I attempt to run code below. the exception is thrown when I try to convert the sql image to a byte[] in the download section of the code.

//
// sqlUploadImage
//
this.sqlUploadImage.CommandText = "INSERT INTO Image_Table (Description, Type, Lenth, DocName) VALUES (@Description," +
" @Type, @Length, @DocName)";
this.sqlUploadImage.Connection = this.sqlConnection2;
this.sqlUploadImage.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Description", System.Data.SqlDbType.VarChar, 50, "Description"));
this.sqlUploadImage.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Type", System.Data.SqlDbType.VarChar, 50, "Type"));
this.sqlUploadImage.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Length", System.Data.SqlDbType.Int, 4, "Lenth"));
this.sqlUploadImage.Parameters.Add(new System.Data.SqlClient.SqlParameter("@DocName", System.Data.SqlDbType.VarChar, 50, "DocName"));
this.sqlUploadImage.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Image", System.Data.SqlDbType.Image, 16, "ImgField"));
this.Button2.Click += new System.EventHandler(this.Button2_Click);
//
// sqlDownLoadImage
//
this.sqlDownLoadImage.CommandText = "SELECT Image_Table.* FROM Image_Table";
this.sqlDownLoadImage.Connection = this.sqlConnection2;

//upload file
private void Button1_Click(object sender, System.EventArgs e)
{
//Get the filename of the pdf file to be uploaded.
string strFilename = File1.PostedFile.FileName.Substring(File1.PostedFile.FileName.LastIndexOf("\") +1);

//Get the file type
string strFileType = File1.PostedFile.ContentType;

//Get the file size
int intImageSize = File1.PostedFile.ContentLength;

// Reads the Image
Stream ImageStream = File1.PostedFile.InputStream;

byte[] ImageContent = new byte[intImageSize + 1];
int intStatus = 0;
intStatus = ImageStream.Read(ImageContent, 0, intImageSize);

//Response.Write(strFilename.ToString());
//Response.Write("<br>Image Type: " + strFileType.ToString());
//Response.Write("<br>Image Size: "+intImageSize.ToString());

this.sqlUploadImage.Parameters["@Description"].Value = strFilename;
this.sqlUploadImage.Parameters["@Type"].Value = strFileType;
this.sqlUploadImage.Parameters["@Length"].Value = intImageSize;
this.sqlUploadImage.Parameters["@DocName"].Value = "TestPDF";
this.sqlUploadImage.Parameters["@Image"].Value = ImageContent;

try
{
this.sqlConnection2.Open();
this.sqlUploadImage.ExecuteNonQuery();

this.Label2.Text = "File Uploaded Successfully";
this.Button2.Visible = true;
}
catch(SqlException ex)
{
Response.Write(ex.ToString());
}
finally
{
this.sqlConnection2.Close();
}
}

//download file
private void Button2_Click(object sender, System.EventArgs e)
{
try
{
this.sqlConnection2.Open();
SqlDataReader r = this.sqlDownLoadImage.ExecuteReader(CommandBehavior.CloseConnection);

if(r.Read())
{

Response.ContentType = r["Type"].ToString();
byte[] image = (byte[])r["ImgField"];
Response.BinaryWrite(image);
}
}
catch(SqlException ex)
{
Response.Write(ex.ToString());
}
finally
{
this.sqlConnection2.Close();
}
}

View Replies !

Doube Byte Characters
I want to store some double byte characters in a table. Originally I planed to change all data type of fields which will store double bytes from VARCHAR() to NVARCHAR(). But i just found out we have no problem to pull and store foreign content(double byte characters) in fields with VARCHAR type( the content is showing correct after stored in database)

my question is : if VARCHAR can handle double byte character, what's the point of using NVARCHAR to store unicode character? any advice is appreciated.

Michael

ming.shi@factiva.com

View Replies !

Double-byte In MSSQL
Experts,

i have trouble while insert/update a field which contains double-byte characters (Chinese Traditional).

NO PROBLEM if i m using Enterprise Manager to view/edit the data. They are retrieved properly in the following:
(1) Enterprise Manager
(2) Query Analyzer
(3) Visual Basic
(4) Command prompt isql

EACH of the Chinese words are become a qustion mark '?' if the UPDATE SQL or stored procedure executed in the following:
(2) Query Analyzer
(3) Visual Basic

WHILE (4) Command prompt isql does not have the problem for the same UPDATE SQL and stored procedure.

Do you have any idea?

View Replies !

Create Certificate From Byte[]
Hi,

Just wondering if anyone knows if you can create a certificate from a byte[]. For example, you can create an assembly using CREATE ASSEMBLY FROM 0x...; specifying the hex representation of it - can you do the same with a certificate? This means that you don't need to save the file to disk before loading it into the database.

Cheers,
Adam

View Replies !

Storing Byte Array In Sql Server
Hello, I am using .net 1.1 and sql server 2000.

I want to store a byte array, but it seems that only a string is stored there.

Code:

SHA512 sha = new SHA512Managed();

byte[] ReturnedPasswordByte = sha.ComputeHash(ToCryptByte);

MyGenericCommand.Parameters.Add("@Password", ReturnedPasswordByte);

MyConnection.Open();
MyGenericCommand.ExecuteNonQuery();
MyConnection.Close();

If I select the record later, all I get is "System.Byte[]" (as string)

View Replies !

DTS: Handling Zero Byte Input Files
Good Day,

I have a situation where I need to be able to gracefully handle zero byte text files to be processed by DTS (using ActiveX Script transformations).

I have a job set up in SQL Server that executes several DTS packages, then executes a Transact SQL script to process the data imported by DTS. This all works fine, but there are instances where some of files have no data to be imported. This causes the entire job to fail -- I don't want to alter the job to continue on an error because there is a legimate potential for errors. I do, however, want to be to have the job continue if one of the DTS packages has no data to process. Is there a way (DTS object method, property) to check for the absence of data before the DTS package errors out? Any ideas would be greatly appreciated.

Regards,

Robin Sarac

View Replies !

Derived Column Can't Add 8-byte Integers
I'm having trouble adding a 4-byte integer with an 8-byte integer. Here's what I'm doing:

Column Name: BIG_ID

Derived Column: < add as new column >

Expression: (DT_I8)[ID] + 840230000538058

Data Type: eight-byte signed integer  {DT_I8]

The error I get:

The literal 840230000538058 is too large to fit into type DT_I4. The magnitude of the literal overflows the type.

Then I try the expression:

(DT_I8)[ID] + (DT_I8)840230000538058

and

[ID] + 840230000538058

and get the same error.

What am I doing wrong? Is it possible to add 2 8-byte integers in regular expression? Why does it still think the literal is DT_I4?

Thanks,

Michael

View Replies !

Save An Image To A Byte[] Variable From A Database
i'm trying to read an image file from a database(ms-sql, .mdf) with type image. Anyone have any ideas on how to do this? I have a table adapter created but could not assign it to my byte[] variable. Thanks in advance.

View Replies !

Help: 900 Byte Limit On Stored Procedure Parameter?
Hi,I have a .NET application that I want to save the Config.EXE contentsto my SQL database for remote review/testing. This config file is3700+ bytes long. I created a field in one of my tables with a VARCHAR4800 and then created a stored procedure that receives a parameter(also VARCHAR(4800).However it fails to write anything if the length of the value that Ipass is anything greater than 900. If I pass exactly 900 characters orless - the data is written to the field. If I pass 901 characters Iget nothing.I'm suspicious since it is exactly 900. I seriously doubt it's somelimitation of MS-SQL so I need a nudge in the right direction.Thanks

View Replies !

Breaking Data Into 1500 Byte Chunks
Hi,

I have a text file (5 MB). It appears as a single line in a text editor. But actually it has records of 1500 byte length each.

I want to strip it down to 1500 byte records. So 1500*3500 = 5 MB (approx). The record size is always 1500 bytes.

Does anyone have a script that I can run on this file to achieve this break.

Thanks

View Replies !

Why Can Varchar Datatype Variable Only 4000 Byte?
Why can varchar datatype variable only 4000 byte?
For example:
in a storedprocedure
declare @aa varchar(8000)
......
while
select @aa=@aa+@otherinfo
end
when the length is more than 4000 ,the data in the behind will be lost

View Replies !

DTS Adding Byte To Columns During Table Migration
When migrating a table from Informix to SQL Server using DTS services, SQL Server adds an extra byte to the column lengths. Does anyone know why this happens and is there a way to prevent it? I'd like the source and target table to have the same column characteristics.

Thanks.

View Replies !

Trying To Map To Tinyint - Single Byte Unsigned Int Not Working
I have an Excel spreadsheet that I eventually land into my staging table. In between, I'm attempting to get a date code from the Date table. I'm using a Lookup Transformation Editor and mapping the fiscal week of year and fiscal year name. I know the fiscal year name is fine. When I have both the fiscal year name and the fiscal week of year, the package fails on the lookup step. In a data conversion, I convert the fiscal week of year to a single byte unsigned integer. (In the Date table, the fiscal week of year is a tinyint.) I'm not sure what I'm doing wrong?

View Replies !

Connecting To Double Byte Progress (MFG-PRO) Databases From SSIS...
HelloWe use MFG-PRO as our ERP system which in turn uses Progressdatabases. In the old version of SQL 2000, using DTS packages, we usedto set the code page via command prompts and execute DTS packageswithin that command prompt to fetch data from our double byte ERPdatabases. In SSIS, we are able to connect and fecth data from NONDouble Byte databases, however, when we set the code page first andexecute via a command prompt the SSIS package, we get the followingerror. We used a ADO.NET connection to connect to our Source ERPSystem. Appreciate any help.ThanksJagannathan SanthanamMicrosoft (R) SQL Server Execute Package UtilityVersion 9.00.1399.06 for 32-bitCopyright (C) Microsoft Corp 1984-2005. All rights reserved.Started: 8:09:26 AMProgress: 2007-11-28 08:09:26.93Source: Data Flow Task 1Validating: 0% completeEnd ProgressError: 2007-11-28 08:09:27.15Code: 0xC0047062Source: Data Flow Task 1 DataReader Source [1]Description: System.Data.Odbc.OdbcException: ERROR [HYC00][DataDirect][ODBCPROGRESS driver]Optional feature not implemented.ERROR [HY000] [DataDirect][ODBC PROGRESS driver][PROGRESS]-219901ERROR [IM006] [DataDirect][ODBC PROGRESS driver]Driver'sSQLSetConnectAttr failed.ERROR [HYC00] [DataDirect][ODBC PROGRESS driver]Optional feature notimplemented..ERROR [HY000] [DataDirect][ODBC PROGRESS driver][PROGRESS]-219901ERROR [IM006] [DataDirect][ODBC PROGRESS driver]Driver'sSQLSetConnectAttr failed.at System.Data.Odbc.OdbcConnection.HandleError(OdbcHa ndle hrHandle,RetCode retcode)at System.Data.Odbc.OdbcConnectionHandle..ctor(OdbcCo nnectionconnection, OdbcConnectionString constr, OdbcEnvironmentHandle environmentHandle)atSystem.Data.Odbc.OdbcConnectionFactory.CreateConne ction(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool,DbConnection owningObject)atSystem.Data.ProviderBase.DbConnectionFactory.Creat eNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup)atSystem.Data.ProviderBase.DbConnectionFactory.GetCo nnection(DbConnectionowningConnection)atSystem.Data.ProviderBase.DbConnectionClosed.OpenCo nnection(DbConnectionouterConnection, DbConnectionFactory connectionFactory)at System.Data.Odbc.OdbcConnection.Open()atMicrosoft.SqlServer.Dts.Runtime.ManagedHelper.GetM anagedConnection(StringassemblyQualifiedName, String connStr, Object transaction)atMicrosoft.SqlServer.Dts.Runtime.Wrapper.IDTSConnec tionManager90.AcquireConnection(Object pTransaction)atMicrosoft.SqlServer.Dts.Pipeline.DataReaderSourceA dapter.AcquireConnections(Object transaction)atMicrosoft.SqlServer.Dts.Pipeline.ManagedComponentH ost.HostAcquireConnections(IDTSManagedComponentWrapper90 wrapper, Object transaction)End ErrorError: 2007-11-28 08:09:27.16Code: 0xC0047017Source: Data Flow Task 1 DTS.PipelineDescription: component "DataReader Source" (1) failed validationand returnederror code 0x80131937.End ErrorProgress: 2007-11-28 08:09:27.16Source: Data Flow Task 1Validating: 33% completeEnd ProgressError: 2007-11-28 08:09:27.16Code: 0xC004700CSource: Data Flow Task 1 DTS.PipelineDescription: One or more component failed validation.End ErrorError: 2007-11-28 08:09:27.16Code: 0xC0024107Source: Data Flow Task 1Description: There were errors during task validation.End ErrorDTExec: The package execution returned DTSER_FAILURE (1).Started: 8:09:26 AMFinished: 8:09:27 AMElapsed: 0.859 seconds

View Replies !

Connecting To Double Byte Progress (MFG-PRO) Databases From SSIS...

Hello

We use MFG-PRO as our ERP system which in turn uses Progress databases. In the old version of SQL 2000, using DTS packages, we used to set the code page via command prompts and execute DTS packages within that command prompt to fetch data from our double byte ERP databases. In SSIS, we are able to connect and fecth data from NON
Double Byte databases, however, when we set the code page first and execute via a command prompt the SSIS package, we get the following error. We used a ADO.NET connection to connect to our Source ERP System. What shocks me is that this functionality works well in SQL Server 2000 DTS packages but not in the much publicized SSIS packages. The error messages are so cryptic as they used to be in DTS days and do not help a wee bit!

Appreciate any help.

Thanks

Jagannathan Santhanam

Microsoft (R) SQL Server Execute Package Utility
Version 9.00.1399.06 for 32-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.

Started: 8:09:26 AM
Progress: 2007-11-28 08:09:26.93
Source: Data Flow Task 1
Validating: 0% complete
End Progress
Error: 2007-11-28 08:09:27.15
Code: 0xC0047062
Source: Data Flow Task 1 DataReader Source [1]
Description: System.Data.Odbc.OdbcException: ERROR [HYC00]
[DataDirect][ODBC
PROGRESS driver]Optional feature not implemented.
ERROR [HY000] [DataDirect][ODBC PROGRESS driver][PROGRESS]-219901
ERROR [IM006] [DataDirect][ODBC PROGRESS driver]Driver's
SQLSetConnectAttr faile
d.
ERROR [HYC00] [DataDirect][ODBC PROGRESS driver]Optional feature not
implemented
.
ERROR [HY000] [DataDirect][ODBC PROGRESS driver][PROGRESS]-219901
ERROR [IM006] [DataDirect][ODBC PROGRESS driver]Driver's
SQLSetConnectAttr faile
d.
at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle,
RetCode r
etcode)
at System.Data.Odbc.OdbcConnectionHandle..ctor(OdbcConnection
connection, Odb
cConnectionString constr, OdbcEnvironmentHandle environmentHandle)
at
System.Data.Odbc.OdbcConnectionFactory.CreateConnection(DbConnectionOption
s options, Object poolGroupProviderInfo, DbConnectionPool pool,
DbConnection own
ingObject)
at
System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbC
onnection owningConnection, DbConnectionPoolGroup poolGroup)
at
System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection
ow
ningConnection)
at
System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection
ou
terConnection, DbConnectionFactory connectionFactory)
at System.Data.Odbc.OdbcConnection.Open()
at
Microsoft.SqlServer.Dts.Runtime.ManagedHelper.GetManagedConnection(String
assemblyQualifiedName, String connStr, Object transaction)
at
Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSConnectionManager90.AcquireCon
nection(Object pTransaction)
at
Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.AcquireConnection
s(Object transaction)
at
Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostAcquireConnectio
ns(IDTSManagedComponentWrapper90 wrapper, Object transaction)
End Error
Error: 2007-11-28 08:09:27.16
Code: 0xC0047017
Source: Data Flow Task 1 DTS.Pipeline
Description: component "DataReader Source" (1) failed validation
and returned
error code 0x80131937.
End Error
Progress: 2007-11-28 08:09:27.16
Source: Data Flow Task 1
Validating: 33% complete
End Progress
Error: 2007-11-28 08:09:27.16
Code: 0xC004700C
Source: Data Flow Task 1 DTS.Pipeline
Description: One or more component failed validation.
End Error
Error: 2007-11-28 08:09:27.16
Code: 0xC0024107
Source: Data Flow Task 1
Description: There were errors during task validation.
End Error
DTExec: The package execution returned DTSER_FAILURE (1).
Started: 8:09:26 AM
Finished: 8:09:27 AM
Elapsed: 0.859 seconds

View Replies !

Quick Question On Nvarchar And Single Byte Characters.
if single byte characters are stored, does it take up two bytes in the database anyway, or does it only take up one byte?

View Replies !

How To: Store And Retrieve Images In A Database As A Byte Array.
I€™ve inherited a project from one of the guys on our team who will be out sick for a while. He developed two for marshaling data between System.Drawing.Image and System.Byte(). He€™s storing the byte array data in a database image field.

I€™ve retrieved the byte array data from his database image fields and have successfully converted them to images using his ConvertByteArrayToImage method below. I have also converted and image to a byte array with his ConvertImageToByteArray method below and succfully stored the data in a database image field. However, when I retrieve the byte array data that I stored in the database the last line in his ConvertByteArrayToImage method throws an exception (Parameter is not valid). I€™ve not been able to find a working copy of his code that€™s storing the byte array data. Does anyone see anything I€™m overlooking?

Imports System.Drawing
Imports System.IO

Public Sub InsertImage(ByVal pFilename As String)

        Try

            Dim lImage As Image
            Dim lBA() As Byte
            Dim lSQL As String
            Dim lQuery As Alcon.SQLServer.Database.clsQuery
            Dim lParameters As New Alcon.SQLServer.Database.clsParameters

            lImage = Image.FromFile(pFilename)

            ConvertImageToByteArray(lImage, lBA)

            ' Initialization
            lQuery = New Alcon.SQLServer.Database.clsQuery(mConnection)

            lSQL = ""
            lSQL += "INSERT INTO [TBL_PCL_LENS_DATA]("
            lSQL += "[SerialNumber], "
            lSQL += "[ProcessedDate], "
            lSQL += "[CartonLabelImage]) "
            lSQL += "VALUES ("
            lSQL += "@SerialNumber, "
            lSQL += "@ProcessedDate, "
            lSQL += "@CartonLabelImage "
            lSQL += ")"

            lParameters.Add("@SerialNumber", SqlDbType.VarChar, ParameterDirection.Input, mSerialNumber)
            lParameters.Add("@ProcessedDate", SqlDbType.DateTime, ParameterDirection.Input, Now)
            lParameters.Add("@CartonLabelImage", SqlDbType.Image, ParameterDirection.Input, lBA)

            ' Execute query
            lQuery.Execute(lSQL, lParameters)

        Catch ex As Exception
            Throw
        End Try

End Sub

ConvertByteArrayToImage(ByVal pBA() As Byte, ByRef pImage As Image)

Try

            ' Declaration
            Dim lMS As MemoryStream

            ' Initialization
            lMS = New MemoryStream(pBA, 0, pBA.Length)
            lMS.Write(pBA, 0, pBA.Length)
            lMS.Position = 0

            ' Create image
            pImage = Image.FromStream(lMS, True)

        Catch ex As Exception
            Throw
        End Try

End Sub

ConvertImageToByteArray(ByVal pImage As Image, ByRef pBA() As Byte)

        Try

            ' Declaration
            Dim lBM As Bitmap
            Dim lBR As BinaryReader
            Dim lMS As New MemoryStream

            ' Initialization
            lBM = New Bitmap(pImage)
            lBM.Save(lMS, Imaging.ImageFormat.Png)
            lBR = New BinaryReader(lMS)

            ' Create byte array
            pBA = lBR.ReadBytes(lMS.Length)

        Catch ex As Exception
            Throw
        End Try

End Sub

View Replies !

Searching A String With Asian Characters (double Byte)
My problem is that i can't search a field that contains Asian characters (Korean in this case).

The table user_Access_tab have 2 keys:

access_id nVarchar(50)

user_id nVarchar(50)

The sql query below is sent through a oledbcommand to a sql server 2005 database.

"select access_id, access_right from user_Access_tab where user_id ='HQ001kimjo012007-05-07 ì˜¤ì „ 11:50:323401'"

It doesn't show any hits even thogh i know there is a number of matching records.

The question doesn't generate an answer in SQL server manager studio eigher.

If i change the datatype on user_id to Varchar(50) the id is presented (in the database) as:

HQ001kimjo012007-05-07 ?? 11:50:323401

Then the question works, but why doesn't it work with nVarChar(50)?

Regards Martin Jonsson

View Replies !

How To Set Up SQL Server To Do Full Text Search Against Double Byte Character
I had a situation that required me to set up SQL Server to do full text search against both English content and Chinese content. I am not sure if it's achievable in SQL server environment. Any help is appreciated.

View Replies !

Do Stored Procedures Have A Limit On Number Of Parameters Or Byte Size Passed In?
Hi,I'm using c# with a tableadapter to call stored procedures. I'm running into a problem where if I have over a certain byte size or number of parameters being passed into my stored proc I get an exception that reads: "Cannot evaluate expression because a thread is stopped at a point where garbage collection is impossible, possibly because the code is optimized." If I remove one parameter, the problem goes away. Has anyone run into this before? Thanks,Mark

View Replies !

System.IO.FileLoadException: LoadFrom(), LoadFile(), Load(byte[]) And LoadModule() Have Been Disabled By The Host.
Hi all,

I use Assembly.LoadFrom("test.dll"); to dynamically load a dll in a SQL CLR pocedure method, but I get this error:

System.IO.FileLoadException: LoadFrom(), LoadFile(), Load(byte[]) and LoadModule() have been disabled by the host.

Any idea?

Thanks in advance.

View Replies !

Unable To Cast Object Of Type 'System.DBNull' To Type 'System.Byte[]'.
Hi,
I have developed a custom server control for .NET Framework 2.0. The server control has a property named BinaryData of type byte[]. I marked this property to be data bindable. Now, I have varbinary(Max) type of field in my SQL Database and I have used SQLDataSource and bound this varbinary(Max) field with the property BinaryData (byte[]) of my control. It is working fine as long as the data value is not NULL. Now, In my control, I have handled the NULL value so that no Exception is thrown. Still, when I bind this property using the SQLDataSource, I get Error "Unable to cast object of type 'System.DBNull' to type 'System.Byte[]'." I am not sure if I can do anything to stop this erro within my control. If it is not possible from the control, then what is the workaround that I can do in my ASPX page in order to stop this error ?
Thanks a lot in advance.

View Replies !

Change The Path And File Name Of The Data File And Transaction Log File??
Hi all,
I have a database on a computer so data file and transaction log on the same, i want to change the path and file name of the data file and transaction log file on the other computer on network, please show me how to do it??
Best regard,

View Replies !

How .cs File That Have Been Generated With .resx File Communicate With Ordinary .cs File?

How .cs file that have been generated with .resx file communicate with ordinary .cs file?

It is because, I can get the value that I try to retrieve form .cs file that have been automatically generated eith .resx file.
There is no error while I try to build it.

Hope someone can help me to explain this.

Thank you.

View Replies !

File Will Not Attach To SQL Reason File Is Not Primary File
I have a customer they are running raid 5 on a windows 2000 server one of the drives went bad. The customer replaced the drive and raid rebuilt the drive, every thing seamed to be fine but there is one database file that cannot be attached to SQL. The file is 15G so I know there is information the error states that the file is not a Primary file. Any clue on how to fix this?

mdf file size 5,738,944 KB
ldf file size 10,176 KB

View Replies !

An Attempt To Attach An Auto-named Database For File (file Location).../Database.mdf Failed. A Database With The Same Name Exists, Or Specified File Cannot Be Opened, Or It Is Located On UNC Share.
Greetings, I have just arrived back into the country (NZ) and back into ASP.NET.
I am having trouble with the following:An attempt to attach an auto-named database for file (file location).../Database.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.
It has only begun since i decided i wanted to use IIS, I realise VWD comes with its own localhost, but since it is only temporary, i wanted a permanent shortcut on my desktop to link to my intranet page.
Anyone have any ideas why i am getting the above error? have searched many places on the internet and not getting any closer.
Cheers ~ J

View Replies !

Attach Mdf File And Data File Without Log File
how can i attach the mdf file with a corrupted/deleted Log file
my log file is deleted how can i attach the mdf file

View Replies !

Help Please (Check File Exists/ Archive File/ Check If File Empty)

Hello World,

I'm new to SSIS and would like a little assistance getting started, if possible...

Here is what I want to do:

Check if file exist (C:DTS UpgradeFilexxx.txt) --->

Archive file (C:DTS UpgradeArchive) --->

Check if file has data (true or false)

AND/OR

If there are any good website that have good direction, let me know

Thanks in advance for your help!!!

View Replies !

SQL Server 2005 Notification (Text File Not Updating Using File Channel Protocol)
Hi Alls!

I m new to SQL Notification Services. I m trying out an example as mentioned on the following article.

http://www.devx.com/dbzone/Article/28522

I created an Notification Application using nsControl.exe in SQL 2K5 which is based on the File Channel Protocol. The scenerio is. When we add an XML file in the watched directory. A text file in my case "Notification.txt" should be updated.

What i m getting at the end is:

My Notification Table is getting updated, but it seems like the distributor is not storing the information in the text file.

One thing more, I m using the main notification tables rather than views in my Event rule.

The following are my ICF and ADF Files.

Instance Configuration File

<?xml version="1.0" encoding="utf-8" ?>
<NotificationServicesInstance xmlns="http://www.microsoft.com/MicrosoftNotificationServices/ConfigurationFileSchema">
<InstanceName>DM</InstanceName>
<SqlServerSystem>
WFW050
</SqlServerSystem>
<Applications>
<Application>
<ApplicationName>StockPrice</ApplicationName>
<BaseDirectoryPath>C:WorkingNotificationServicesSample</BaseDirectoryPath>
<ApplicationDefinitionFilePath>appADF.xml</ApplicationDefinitionFilePath>
</Application>
</Applications>
<DeliveryChannels>
<DeliveryChannel>
<DeliveryChannelName>
FileChannel
</DeliveryChannelName>
<ProtocolName>
File
</ProtocolName>
<Arguments>
<Argument><Name>FileName</Name>
<Value>C:ResultsStockNotification.txt</Value>
</Argument>
</Arguments>
</DeliveryChannel>
</DeliveryChannels>
</NotificationServicesInstance>

Application Definition File

<?xml version="1.0" encoding="utf-8" ?>
<Application xmlns="http://www.microsoft.com/MicrosoftNotificationServices/ApplicationDefinitionFileSchema">
<EventClasses>
<EventClass>
<EventClassName>StockEvt</EventClassName>
<Schema>
<Field>
<FieldName>
StockCode
</FieldName>
<FieldType>
varchar(10)
</FieldType>
<FieldTypeMods>
not null
</FieldTypeMods>
</Field>
<Field>
<FieldName>
ExchangeCode
</FieldName>
<FieldType>
varchar(15)
</FieldType>
<FieldTypeMods>
not null
</FieldTypeMods>
</Field>
<Field>
<FieldName>
Price
</FieldName>
<FieldType>
decimal(18,5)
</FieldType>
<FieldTypeMods>
not null
</FieldTypeMods>
</Field>
</Schema>
</EventClass>
</EventClasses>

<Providers>
<HostedProvider>
<ProviderName> StockEP </ProviderName>
<ClassName> FileSystemWatcherProvider </ClassName>
<SystemName> WFW050 </SystemName>
<Arguments>
<Argument>
<Name>WatchDirectory</Name>
<Value>C:Events</Value>
</Argument>
<Argument>
<Name>SchemaFile</Name>
<Value>C:WorkingNotificationServicesSampleSchema.xsd</Value>
</Argument>
<Argument>
<Name>EventClassName</Name>
<Value>StockEvt</Value>
</Argument>
</Arguments>
</HostedProvider>
</Providers>

<SubscriptionClasses>
<SubscriptionClass>
<SubscriptionClassName>
StockPriceSubscriptions
</SubscriptionClassName>
<Schema>
<Field>
<FieldName>
DeviceName
</FieldName>
<FieldType>
varchar(255)
</FieldType>
<FieldTypeMods>
not null
</FieldTypeMods>
</Field>
<Field>
<FieldName>
SubLocale
</FieldName>
<FieldType>
varchar(10)
</FieldType>
<FieldTypeMods>
not null
</FieldTypeMods>
</Field>
<Field>
<FieldName>
StockCode
</FieldName>
<FieldType>
varchar(15)
</FieldType>
<FieldTypeMods>
not null
</FieldTypeMods>
</Field>
<Field>
<FieldName>
ExchangeCode
</FieldName>
<FieldType>
varchar(15)
</FieldType>
<FieldTypeMods>
not null
</FieldTypeMods>
</Field>
<Field>
<FieldName>
TriggerVal
</FieldName>
<FieldType>
decimal(18,5)
</FieldType>
<FieldTypeMods>
not null
</FieldTypeMods>
</Field>
</Schema>
<EventRules>
<EventRule>
<RuleName>EvtRule</RuleName>
<Action>
INSERT INTO NSStockPriceNotificationsNotifications(DeliverySta tusCode,NotificationBatchId,SubscriberId, DeviceName, SubscriberLocale, StockCode, ExchangeCode, Price) SELECT 0,1,s.SubscriberId, s.DeviceName, s.SubLocale, e.StockCode, e.ExchangeCode, e.Price FROM NSStockEvtEvents e, NSStockPriceSubscriptionsSubscriptions s WHERE e.ExchangeCode = s.ExchangeCode AND e.StockCode = s.StockCode AND e.Price > s.TriggerVal
</Action>
<EventClassName>StockEvt</EventClassName>
</EventRule>
</EventRules>
</SubscriptionClass>
</SubscriptionClasses>

<NotificationClasses>
<NotificationClass>
<NotificationClassName>
StockPriceNotifications
</NotificationClassName>
<Schema>
<Fields>
<Field>
<FieldName>
StockCode
</FieldName>
<FieldType>
varchar(15)
</FieldType>
</Field>
<Field>
<FieldName>
ExchangeCode
</FieldName>
<FieldType>
varchar(15)
</FieldType>
</Field>
<Field>
<FieldName>
Price
</FieldName>
<FieldType>
decimal(18,5)
</FieldType>
</Field>
</Fields>
</Schema>
<ContentFormatter>
<ClassName> XsltFormatter </ClassName>
<Arguments>
<Argument>
<Name> XsltBaseDirectoryPath </Name>
<Value> C:Stylesheets </Value>
</Argument>
<Argument>
<Name> XsltFileName </Name>
<Value> Tansformation.xslt </Value>
</Argument>
</Arguments>
</ContentFormatter>

<Protocols>
<Protocol>
<ProtocolName>
File
</ProtocolName>
</Protocol>
</Protocols>
<ExpirationAge>
PT2H
</ExpirationAge>
</NotificationClass>
</NotificationClasses>

<Generator>
<SystemName>WFW050</SystemName>
</Generator>

<Distributors>
<Distributor>
<SystemName>WFW050</SystemName>
</Distributor>
</Distributors>
</Application>

Can you ever comeup with such problem. Please do let me know

Thanks

R.A.F

View Replies !

How To Create, Install And Deploy Dts File For Sqlserver 2000 And Dtsx File For 2005 ?
Hi,

I am looking for tutorials about how to create dts et dtsx files.

Thanks for your help.

Arioule.

View Replies !

Script Task To Compare File From FTP With File On Local Archive Behaved Strangely!
In my script task I have the following code. The task I'm trying to accomplish is:
If the filename on FTP can be found in the local archive folder of e: drive then show message "FileAlreadyThere" (I will ultimatley change it to do nothing); if the filename on FTP cannot be found in the local archive folder of e: drive then transfer the file to the local package folder on d: drive.

While the script task is executing I was watching it closely, but the problem i saw is that:
If some files on FTP are already in local archive folder and some are not, then it the files which are already in the archive folder are dumped to the package folder; then after that the files which are not in the archive folder are then dumped to the package folder. But I only want the new files on FTP to be transferred to the package folder for further processing.

Then after this is finished, I saw all the files in the package folder are refreshed one after another, after the first round of refresh the second round starts, after the second round finishes it then stopped. I saw it refreshes itself because the 'Date Modified' of the file changes. And I saw the script task turned green.

I don't see how the code below produced this result. Something is wrong in the logic of the loop? Anyone has any idea why it's behaving the way it is now? And how to change the code to accomplish what I want? Thanks a lot!!

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Imports System
Imports System.IO
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Public Class ScriptMain
    Public Sub Main()
        Dim cm As ConnectionManager = Dts.Connections.Add("FTP")
        cm.Properties("ServerName").SetValue(cm, "ftp2.name.com")
        cm.Properties("ServerUserName").SetValue(cm, "username")
        cm.Properties("ServerPassword").SetValue(cm, "password")
        cm.Properties("ServerPort").SetValue(cm, "21")
        cm.Properties("Timeout").SetValue(cm, "0")
        cm.Properties("ChunkSize").SetValue(cm, "1000") '1000 kb
        cm.Properties("Retries").SetValue(cm, "1")
        Dim ftp As FtpClientConnection = New FtpClientConnection(cm.AcquireConnection(Nothing))
        ftp.Connect()
        ftp.SetWorkingDirectory("/directory")
        Dim fileNames() As String
        Dim folderNames() As String
        ftp.GetListing(folderNames, fileNames)
        If fileNames Is Nothing Then
            MsgBox("NoFileOnFTP")
        Else
            Dim fileName As String
            For Each fileName In fileNames
                If File.Exists("c: emp" + fileName) Then
                    MsgBox("FileAlreadyThere")
                Else
                    ftp.ReceiveFiles(fileNames, "c: emp", True, True)
                End If
            Next
        End If

        ftp.Close()
    End Sub
End Class

View Replies !

XML Format File For Bulk Insert Of Text File With Fixed Length Columns
Hey All,

Similar to a previous post (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=244646&SiteID=1), I am trying to import data into a SQL Table.

I am trying to program a small application that will import product data obtained through suppliers via CD-ROM. One supplier in particular uses Fixed width colums, and data looks like this:

Example of Data

0124015Apple Crate    32.12

0124016Bananna Box    12.56

0124017Mango Carton 15.98

0124018Seedless Watermelon 42.98
My Table would then have:
ProductID as int
Name as text
Cost as money

How would I go about extracting the data with an XML Format file? I am stumbling over how to tell it where to start picking up data for a specific column.
Is there any way that I could trim the Name column (i.e.: "Mango Carton " --> "Mango Carton")?

I don't know if it makes any difference, but I've been calling SQL from my code by doing this:

Code in C# Form

            SqlConnection SqlConnection = new SqlConnection(global::SQLClients.Properties.Settings.Default.ClientPhonebookConnectionString);
            SqlCommand cmd = new SqlCommand();

            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "INSERT INTO PhonebookTable(Name, PhoneNumber) VALUES('" + txtName.Text.ToString() + "', '" + txtPhoneNumber.Text.ToString() + "')";
            cmd.Connection = SqlConnection;

            SqlConnection.Open();
            cmd.ExecuteNonQuery();
            SqlConnection.Close();
            RefreshData();
I am running Visual Studio C# Express 2005 and SQL Server Express 2005.

Thanks for your time,

Hayden.

View Replies !

Is It Possible To Write A Format File That Skips A Few Bytes Of Header In Data File?
Hi,I am trying to use BULK INSERT with format file. All of our data hasfew bytes of header in the data file which I would like to skip beforedoing BULK INSERT.Is it possible to write format file to skip these few bytes ofheader before doing BULK INSERT? For example, I have a 1 GB data filewith 1000 byte header. Except for first 1000 bytes, rest of the data isgood for BULK INSERT.Thanks in advance. Sorry if it is really a dumb question as I am newto BULK INSERT and practicing still.Bob

View Replies !

Shrink File Tool From EM Took All The Empty Space Allocated To Data File
I've production sql server 7 sp3 on windows NT. I had a 8GB data file ofwhich 5GB were used and 3GB were unused. I wanted to take back the unused3GB.So I did the following with EM GUI:1. I tried to "truncate fre space from end of the file". Didn't truncatethe file. I believe there was no empty space at the end of the file.2. Next I chose the option to "shrink file to 5GB". And to my horror thedata file instead of taking just 5GB took the empty spaces also and the sizeof the used data file went to 8GB.Any idea what's going on?TIA,SP

View Replies !

Using Batch File To Download Flat File From Ftp Server So Can Bulkcopy Into Database
I use RUMBA and write FOCUS programs to pull data from our mainframe so I can then import the flat file into our Data Warehouse. I'm trying to eliviate some steps here and want to include the downloading of the flat file from the FTP Server to my server where I can then continue with the rest of the batch file and bulkcopy the data into my Data Warehouse. I can't figure out what code to use in the batch file to do that though. Can anyone help me please? I need help with the batch code to ftp the file to my server.

Thanks!
Jennifer

View Replies !

How To Specify Multiple File Types In SSIS Foreach Loop (File Enumerator)?
I want to enumerate all *.xls and *.csv file. How to fill the Files box? I tried

*.xls, *.csv

*.xls *.csv

*.(xls|cvs)

all doesn't work

View Replies !

File System Task - Move File With Dynamic Destination Path
I am having an issue with the File System Task.

I was wondering if there is a way to 'Move File' with the File System Task inside of a For Each Loop container but to dynamically set the Destination path variable.

Currently, this is what I have:
FileDestinationPath variable - set to C:TestFiles
FileSourcePath variable - set to C:TestFiles
FileNameAndLocation variable - set to blank

For Each Loop Container €“ Iterates through a folder C:TestFiles that has .txt files in it with dates in the file name. Ex: Test_09142006.txt. Sets the file path (fully qualified) to the Variable Mapping FileNameAndLocation.

Script Task (within For Each Loop, first step) €“ Sets the FileDestinationPath to the correct dated folder within C:TestFiles. For example, if the text files I want to move are for the 14th of September, it takes FileDestinationPath and appends the date folder to the end of it. The text files have a date in the file name (test_09142006.txt) and I am picking this apart (from FileNameAndLocation in the For Each Loop) to get the folder date. (dts.Variables(€œUser::FileDestinationPath€?).Value = dts.Variables(€œUser::FileDestinationPath€?).Value & €œ€? Month & €œ_€? & Day & €œ_€? & Year & €œ€?) which gives me €œC:TestFiles9_14_2006€?.

File System Task (within For Each Loop, second step) €“ This is where the action is supposed to occur. I want it to take the FileDestinationPath and move the FileNameAndLocation file (from the For Loop) into this folder for each run.

Now as for my problem. I want this package to run everyday but it has to set the FileDestinationPath variable dynamically according to that day€™s date. Basically, how do I get this to work since I can€™t hard code the destination path variable from the start? I have the DestinationVariable on the File System Task set to the FileDestinationPath variable, after the script task builds it. However, using FileNameAndLocation as the SourceVariable on my File System Task tells me that the €œVariable €œFileNameAndLocation€? is used as a source or destination and is empty.€?

Let me know if I need to clarify further€¦...I may be missing something very simple. Any help would be greatly appreciated!

View Replies !