I was migrating from Oracle to SQL Server 2005 using SSMA (SQL Server Migration Assistant) but i`ve found an issue, i can´t find how to fix it. In my stored procedure in PL/SQL exists this lines:
TYPE T_ARRAY_COL IS VARRAY (1000 ) OF VARCHAR2 (50);
A_COLUMNS T_ARRAY_COL := T_ARRAY_COL();
Somebody know how can i simulate this data type ARRAY. I was reading http://msdn.microsoft.com/msdnmag/issues/04/02/TSQLinYukon/ but some things are not clear for me.... please help me, give me one hand.
Is there any array data type in SQL Server 7.0. I am using VB 6.0 with ADO 2.1. I am populating a MSFlexGrid with values that I pass to SQL Server one at a time and insert into the database. What I would like to do is pass the entire contents of the Grid at once to a stored procedure and let SQL do the processing so my routine is not going back and forth to the client. I did not find any documentation on any array data types in SQL. What is my best approach to this problem? Thanks, Dan Collins
We're importing data from a progress db. Some of the columns contain arrays or delimited values which represent accounting periods.
Currently I'm passing the arrays row by row to a stored procedure which parses and inserts each value as a row for the applicable accounting period, it works but is very slow.
I am attempting to create a stored procedure that will launch at report runtime to summarize data in a table into a table that will reflect period data using an array type field. I know how to execute one line but I am not sure how to run the script so that it not only summarizes the data below but also creates and drops the table.
I have one column in SQL Server 2005 of data type VARCHAR(4000).
I have imported sql Server 2005 database data into one mdb file.After importing a data into the mdb file, above column data type converted into the memo type in the Access database.
now when I am trying to import a data from this MS Access File(db1.mdb) into the another SQL Server 2005 database, got the error of Unicode Converting a memo data type conversion in Export/Import data wizard.
Could you please let me know what is the reason?
I know that memo data type does not supported into the SQl Server 2005.
I am with SQL Server 2005 Standard Edition with SP2.
Please help me to understans this issue correctly?
nvarchar(max) I have a nvarchar(MAX) “Description” field in my SQL Server 2005 database. Somewhere I think I read that nvarchar(MAX) is the size of 4000. My question is in my Insert Stored Proc set ANSI_NULLS ONset QUOTED_IDENTIFIER ONGOALTER PROCEDURE [dbo].[syl_CodeScheduleTypeInsert] @ScheduleName nvarchar(50), @ScheduleDescription nvarchar(????????)ASBEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; BEGIN TRY INSERT I What number should replace the ???????? with? nvarchar(4000) is that correct? I tried nvarchar(MAX) and nvarchar but both versions only input one character into my database table. Please help a newbie out, cheers!
Hello, I'm trying to build a integration service package importing data from XML files and directs this data to different MS SQL server 2005 Database tables. Can someone suggest me what is equivalent(mapping) data type of DT_UI8 in Sql server 2005 for Integration Services.
Or how to consume DT_UI8 fields in SQL server 2005.
I have a problem using 3 tables. The first recordset is my own invention (that means a very simple one). It reads product features from a table:
Set Rsx = Server.CreateObject("ADODB.RecordSet") sSQL= "SELECT * from prodfeatures" Rsx.Open sSQL, sDSN, adOpenStatic, adLockReadOnly, adCmdText
'then I read the recordset into arrays like this:
While Not Rsx.eof i=i+1 mte(i)=Rsx("id") ' every id has a corresponding featurename mfnavn(mte(i))=Rsx("featurename") Rsx.movenext Wend
'this part works very well
Now comes the difficult part that the SQLTeam figured out (this combines an order base with an itemorder base):
Set Rs = Server.CreateObject("ADODB.RecordSet") sSQL="select oitems.catalogid,oitems.features, sum(oitems.numitems) as SumOfItems from oitems Inner Join Orders On Orders.orderid = oitems.orderid Where Orders.oshippeddate =" & dDate & " AND Orders.orderid <> 1 group by oitems.catalogid,oitems.features" Rs.Open sSQL, sDSN, adOpenStatic, adLockReadOnly, adCmdText
'Then I write the content of the products with product features - the complexity of the sql is due to a use of many other operations:
While Not Rs.eof response.write Rs("catalogid") & "features:" & Rs("features") & "Name:" & mfnavn(rs("features")) Rs.movenext Wend
The query runs, and starts well, writing 3 lines:
239 features:221 Name:Standard bil max 5m lang og 1,9 m høy 240 features:270 Name:Liggestol (gratis) 240 features:271 Name:C1 Enkeltseng i delt lugar u. bildekk. Kun vask
but it is then aborted with this message:
Microsoft VBScript runtime error '800a000d' Type mismatch: 'Rs(...)' I know that the problematic part is the array mfnavn(rs("features")) ...anyone with experience in this?
I have installed the trial enterprise edition of sql server 2005. I am trying to recreate a dts packages. I am using an iseries OLE DB provider. In the data flow the connection to a physical file that has string data gets translated into binary data. Has any one gotten a download to work with the correct data?
Hello All,I have a scenario in which my stored procedure has to return fewvariables with their value and also the collection. Now in SQL their isno such as array, so the best is to return the table in place of.I am execting the stored procedure by having sql command in place.and created the various parameters(variables) those i need the valuesof and secondly wondering how should i be creating the parameter as atable returntype.Any help on this would be a million worth useful.I can excerpt the code if required.RegardsSandesh Kadam
I am looking for information/suggestions in regards to portioning data across a large SAN. The database is several TB in size, and we are looking to partition the data on a date so that the current data is always the fastest available and history can be set as read only but still be available.
If the server has 8 drive letters how can I ensure that the 4 data drives are stripped across the most physical drives for the most through-put??
Are there any specific questions I need to ask the server technicians?
Hi - I'm using Session variables to store information (sort of webshop).
The sessions are a mix of the usual straightforward strings, wich are no problem - but I also have a DataTable which I store in a session variable, and I also have an array, which I store in a session variable eg. session("day")(x) where x is the item in the array.
I want to give my users the ability to store the items they've selected in my database, but have no idea of what type of field I should use for the datatable and array session variable. Should I use a TEXT field, or is there another more appropriate one'?
Hi, I was wondering if there is a method (other than BULK INSERT) to insert a (C++) application level array into the database, I have a variant type array populated with values that I want to insert, perhaps using ADO objects in quick time!
Hi, I m using sql 2005 as a back end in my application... I am useing Store procedure..for my data in grid..
ALTER PROCEDURE [dbo].[ProductZoneSearct] ( @Productid char(8),@Proname char(8),@radius int,@mode varchar(5) = 'M',@Zone nvarchar(1000),) ASSET NOCOUNT ON;Create Table #Product (ProductID int, TimeEntered datetime, DateAvailable datetime, Productname varchar(80), City varchar(50), State char(4),Miles decimal, Payment varchar(40),UserID int, Phone varchar(15)) Insert #Product Select ProductID , TimeEntered, DateAvailable, Productname ,City,State,miles,Payment ,Miles, UserID, Daily, PhoneFrom [tblproduct] Where city IN (@Zone) Select ProductID TimeEntered, DateAvailable, Productname City,State,miles,Payment ,Miles, U.Phone As phoneNumber, Company, , L.Phone As cmpPhone From #Product As L Left Join (Select UserID, Company, Phone, From [User]) As U On U.UserID = L.UserID Order By DateAvailable
if i pass value in "where city in (@Zone)" and @Zone ='CA','AD','MH' then it can not get any result..but if write where city in ('CA','AD','MH') then it give me perfact result.. I tried to below syntax also but in no any user Where city IN ('+@Zone+') In short if i pass value through varibale (@Zone) then i cant get result...but if i put direct value in query then only getting result..can anybody tell me what is problem ? Please Hel[p me !!! Thank you !!!
Hi all,I have a table called PTRANS with few columns (see create script below).I have created a view on top that this table VwTransaction (See below)I can now run this query without a problem:select * from dbo.VwTransactionwhereAssetNumber = '101001' andTransactionDate <= '7/1/2003'But when I create an index on the PTRANS table using the command below:CREATE INDEX IDX_PTRANS_CHL# ON PTRANS(CHL#)The same query that ran fine before, fails with the error:Server: Msg 242, Level 16, State 3, Line 1The conversion of a char data type to a datetime data type resulted inan out-of-range datetime value.I can run the same query by commeting out the AssetNumber clause and itworks fine. I can also run the query commenting out the TransactionDatecolumn and it works fine. But when I have both the conditions in theWHERE clause, it gives me this error. Dropping the index solves theproblem.Can anyone tell me why an index would cause a query to fail?Thanks a lot in advance,AmirCREATE TABLE [PTRANS] ([CHL#] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[CHCENT] [numeric](2, 0) NOT NULL ,[CHYYMM] [numeric](4, 0) NOT NULL ,[CHDAY] [numeric](2, 0) NOT NULL ,[CHTC] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL) ON [PRIMARY]GOCREATE VIEW dbo.vwTransactionsASSELECT CONVERT(datetime, dbo.udf_AddDashes(REPLICATE('0', 2 -LEN(CHCENT)) + CONVERT(varchar, CHCENT) + REPLICATE('0', 4 -LEN(CHYYMM))+ CONVERT(varchar, CHYYMM) + REPLICATE('0', 2 -LEN(CHDAY)) + CONVERT(varchar, CHDAY)), 20) AS TransactionDate,CHL# AS AssetNumber,CHTC AS TransactionCodeFROM dbo.PTRANSWHERE (CHCENT <> 0) AND (CHTC <> 'RA')*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
hi i am getting an error with my code, it says 'value of type byte canot be converted to 1 dimensional array of byte' do you know why and how i can correct this error, the follwoing is my code. can anyone help me correct the error and let me know ow to solve it thanks for any help givenPublic Sub ProcessRequest(ByVal context As HttpContext) Implements IHttpHandler.ProcessRequestDim myConnection As New Data.SqlClient.SqlConnection("ConnectionString") myConnection.Open() Dim sql As String = "Select Image_Content from ImageGallery where Img_Id=@ImageId"Dim cmd As New Data.SqlClient.SqlCommand(sql, myConnection)cmd.Parameters.Add("@imgID", Data.SqlDbType.Int).Value = context.Request.QueryString("id") cmd.Prepare()Dim dr As Data.SqlClient.SqlDataReader = cmd.ExecuteReader() dr.Read() context.Response.ContentType = dr("imgType").ToString()context.Response.BinaryWrite(CByte(dr("imgData"))) ----- this is the line with the error End Sub
I can populate a dataTable with type double (C#) of say '1055.01' however when I save these to the CE3.5 database using a float(CE3.5) I lose the decimal portion. The 'offending' code is:
We have some columns in a table where the date is stored as 19980101 (YYYYMMDD). The data type for this column is NUMBER(8) in Oracle.
I need to copy rows from Oracle to SQL Server using SSIS. I used the Data Conversion transformation editor to change it to DT_DATE, but the rows are not being inserted to the destination.
On Error, If I fail the component, then the error is :
There was an error with input column "ORDER_DATE_CONV" (1191) on input "OLE DB Destination Input" (29). The column status returned was: "Conversion failed because the data value overflowed the specified type.".
Hi everyone, I have an odd problem. I have a generic upload/download ASP.net page that allows the upload and download of and type of file. I have so far tested the following file types: XLS, MDB, JPG, DLL, EXE, PDF, TXT, SWF, and GIF All of these upload and download fine, EXCEPT PDF's. I have tried 4 different PDF's and all open prior to upload/download, but after uploading and downloading, I get the following (from Adobe Reader) error upon trying to open: "There was an error opening this document. The file is damaged and could not be repaired" Here's my current code: 9 Protected Sub ItemCommand_Click(ByVal sender As System.Object, ByVal e As RepeaterCommandEventArgs)10 11 If e.CommandName = "open" Then12 Dim sqlConn As New SqlConnection(ConfigurationManager.ConnectionStrings("data_partsbranding").ConnectionString)13 Dim sSQL As New StringBuilder14 Dim sqlCmd As SqlCommand15 Dim sqlReader As SqlDataReader16 Dim byteArray(UploadedFile.PostedFile.InputStream.Length) As Byte17 18 sSQL.Append(" SELECT * ")19 sSQL.Append(" FROM [survey_document] ")20 sSQL.Append(" WHERE [sd_document_code] = @sd_document_code ")21 22 sqlCmd = New SqlCommand(sSQL.ToString, sqlConn)23 24 sqlCmd.Parameters.AddWithValue("@sd_document_code", e.CommandArgument)25 26 sqlConn.Open()27 sqlReader = sqlCmd.ExecuteReader28 29 While sqlReader.Read30 31 Response.ContentType = sqlReader("sd_mime_type").ToString()32 Response.BinaryWrite(sqlReader("sd_document"))33 Response.AddHeader("Content-Disposition", "attachment;filename=" & sqlReader("sd_file_name").ToString())34 End While35 36 sqlReader.Close()37 sqlConn.Close()38 39 End If40 41 End Sub42 43 Protected Sub btnInsert_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnInsert.Click44 45 'Make sure a file has been successfully uploaded46 If UploadedFile.HasFile Then47 48 'Connect to the database and insert a new record into Products49 Dim sqlConn As New SqlConnection(ConfigurationManager.ConnectionStrings("data_partsbranding").ConnectionString)50 Dim sSQL As New StringBuilder51 Dim sqlCmd As SqlCommand52 Dim byteArray(UploadedFile.PostedFile.InputStream.Length - 1) As Byte53 54 'Read the files binary data into a Byte array55 UploadedFile.PostedFile.InputStream.Read(byteArray, 0, byteArray.Length)56 57 sSQL.Append(" INSERT INTO [survey_document] ")58 sSQL.Append(" ( sd_document ")59 sSQL.Append(" , sd_title ")60 sSQL.Append(" , sd_file_name ")61 sSQL.Append(" , sd_upload_date ")62 sSQL.Append(" , sd_mime_type ")63 sSQL.Append(" ) VALUES ( @sd_document ")64 sSQL.Append(" , @sd_title ")65 sSQL.Append(" , @sd_file_name ")66 sSQL.Append(" , @sd_upload_date ")67 sSQL.Append(" , @sd_mime_type ")68 sSQL.Append(" ) ")69 70 sqlCmd = New SqlCommand(sSQL.ToString, sqlConn)71 72 sqlCmd.Parameters.AddWithValue("@sd_title", FileTitle.Text.Trim())73 sqlCmd.Parameters.AddWithValue("@sd_mime_type", UploadedFile.PostedFile.ContentType)74 sqlCmd.Parameters.AddWithValue("@sd_file_name", System.IO.Path.GetFileName(UploadedFile.PostedFile.FileName))75 sqlCmd.Parameters.AddWithValue("@sd_upload_date", Now)76 sqlCmd.Parameters.AddWithValue("@sd_document", byteArray)77 78 sqlConn.Open()79 sqlCmd.ExecuteNonQuery()80 sqlConn.Close()81 82 Else83 84 'Either the file upload failed or no file was selected85 86 End If87 88 End SubI have also tried the following code as a result of searching far and wide, trying other peoples methods: 1 Protected Sub ItemCommand_Click(ByVal sender As System.Object, ByVal e As RepeaterCommandEventArgs) 2 3 If e.CommandName = "open" Then 4 Dim sqlConn As New SqlConnection(ConfigurationManager.ConnectionStrings("data_partsbranding").ConnectionString) 5 Dim sSQL As New StringBuilder 6 Dim sqlCmd As SqlCommand 7 Dim sqlReader As SqlDataReader 8 Dim byteArray(UploadedFile.PostedFile.InputStream.Length) As Byte 9 10 sSQL.Append(" SELECT * ") 11 sSQL.Append(" FROM [survey_document] ") 12 sSQL.Append(" WHERE [sd_document_code] = @sd_document_code ") 13 14 sqlCmd = New SqlCommand(sSQL.ToString, sqlConn) 15 16 sqlCmd.Parameters.AddWithValue("@sd_document_code", e.CommandArgument) 17 18 sqlConn.Open() 19 sqlReader = sqlCmd.ExecuteReader 20 21 While sqlReader.Read 22 23 Dim buffer() As Byte = sqlReader("sd_document") 24 Dim blen As Integer = CType(sqlReader("sd_document"), Byte()).Length 25 26 Response.ContentType = sqlReader("sd_mime_type").ToString() 27 Response.OutputStream.Write(buffer, 0, blen,) 28 Response.AddHeader("Content-Disposition", "attachment;filename=" & sqlReader("sd_file_name").ToString()) 29 End While 30 31 sqlReader.Close() 32 sqlConn.Close() 33 34 End If 35 36 End Sub 37 38 Protected Sub btnInsert_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnInsert.Click 39 40 'Make sure a file has been successfully uploaded 41 If UploadedFile.HasFile Then 42 43 'Connect to the database and insert a new record into Products 44 Dim sqlConn As New SqlConnection(ConfigurationManager.ConnectionStrings("data_partsbranding").ConnectionString) 45 Dim sSQL As New StringBuilder 46 Dim sqlCmd As SqlCommand 47 Dim byteArray(UploadedFile.PostedFile.InputStream.Length - 1) As Byte 48 49 'Read the files binary data into a Byte array 50 UploadedFile.PostedFile.InputStream.Read(byteArray, 0, byteArray.Length) 51 52 sSQL.Append(" INSERT INTO [survey_document] ") 53 sSQL.Append(" ( sd_document ") 54 sSQL.Append(" , sd_title ") 55 sSQL.Append(" , sd_file_name ") 56 sSQL.Append(" , sd_upload_date ") 57 sSQL.Append(" , sd_mime_type ") 58 sSQL.Append(" ) VALUES ( @sd_document ") 59 sSQL.Append(" , @sd_title ") 60 sSQL.Append(" , @sd_file_name ") 61 sSQL.Append(" , @sd_upload_date ") 62 sSQL.Append(" , @sd_mime_type ") 63 sSQL.Append(" ) ") 64 65 sqlCmd = New SqlCommand(sSQL.ToString, sqlConn) 66 67 'sqlCmd.Parameters.AddWithValue("@sd_title", PictureTitle.Text.Trim()) 68 'sqlCmd.Parameters.AddWithValue("@sd_mime_type", UploadedFile.PostedFile.ContentType) 69 'sqlCmd.Parameters.AddWithValue("@sd_file_name", System.IO.Path.GetFileName(UploadedFile.PostedFile.FileName)) 70 'sqlCmd.Parameters.AddWithValue("@sd_upload_date", Now) 71 'sqlCmd.Parameters.AddWithValue("@sd_document", byteArray) 72 73 sqlCmd.Parameters.Add(New SqlParameter("@sd_mime_type", SqlDbType.VarChar)) 74 sqlCmd.Parameters.Add(New SqlParameter("@sd_title", SqlDbType.VarChar)) 75 sqlCmd.Parameters.Add(New SqlParameter("@sd_upload_date", SqlDbType.DateTime)) 76 77 sqlCmd.Parameters.Add(New SqlParameter("@sd_file_name", SqlDbType.VarChar)) 78 79 sqlCmd.Parameters.Add(New SqlParameter("@sd_document", SqlDbType.Image)) 80 81 Dim bArray(UploadedFile.PostedFile.ContentLength - 1) As Byte 82 83 84 85 86 87 88 UploadedFile.PostedFile.InputStream.Read(bArray, 0, UploadedFile.PostedFile.ContentLength) 89 90 sqlCmd.Parameters("@sd_mime_type").Value = UploadedFile.PostedFile.ContentType 91 sqlCmd.Parameters("@sd_title").Value = PictureTitle.Text.Trim() 92 sqlCmd.Parameters("@sd_upload_date").Value = Now 93 94 sqlCmd.Parameters("@sd_file_name").Value = System.IO.Path.GetFileName(UploadedFile.PostedFile.FileName).ToLower 95 96 sqlCmd.Parameters("@sd_document").Value = bArray 97 98 99 sqlConn.Open() 100 sqlCmd.ExecuteNonQuery() 101 sqlConn.Close() 102 103 Else 104 105 'Either the file upload failed or no file was selected 106 107 End If 108 109 End Sub 110
This method atleast gives a different error: "Adobe Reader could not open '132-171510.pdf' because it is either not a supported file type or the file has been damaged (for examplc, it was sent as an email attachment and wasn't correctly decoded)." Please help, it would be appreciated. Thanks
