2 Dimensional Array
Jan 24, 2008
Hi. Does anyone know how to pass a 2 dimensional array into a stores procedure? I need to loop through it and insert it into a table.
Thanks!
ADVERTISEMENT
Help With Error 'value Of Type Byte Canot Be Converted To 1 Dimensional Array Of Byte'
Mar 26, 2008hi
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
RS2005: Export To Excel Error: Destination Array Was Not Long Enough. Check DestIndex And Length, And The Array's Lower Bounds.
Jan 25, 2007All,
I am using Reporting Services 2005. One of my reports is getting the following error when I try to export to Excel. It will export to .CSV though.
"Destination array was not long enough. Check destIndex and length, and the array's lower bounds."
Any suggestions would be greatly appreciated. Please copy me at machelle.a.chandler@intel.com.
Machelle
How Would I Send A String Array As A Integer Array?
Jun 25, 2007I have a stored procedure that has a paramter that accepts a string of values. At the user interface, I use a StringBuilder to concatenate the values (2,4,34,35,etc.) I would send these value to the stored procedure. The problem is that the stored procedure doesn't allow it to be query with the parameter because the Fieldname, "Officer_UID" is an integer data type, which can't be query against parameter string type.
What would I need to do to convert it to an Integer array?
@OfficerIDs as varchar(200)
Select Officer_UID From Officers Where Officer_UID in (@OfficerIDs)
Thanks
Any Suggestions On How To Make 1 Flow Do Many Dimensional Tables
Mar 4, 2008OK looking for anyone out there that may have already tackled this and what they may have learned
I have multiple flat files providing dimensional data to multiple tables. The source files and destination tables are very similiar in structure, the columns are named differently in each table
ID - surrogate key
Code - match for lookup
Desc - updated column
flow options I have considered or might work are:
source (flat file) - this is easy to make dynamic
lookup against existing Table -
1. I can make this dynamic on the advanced tab by restricting memory. have not really tried this option yet. Seems like it might be a performance issue for large tables but so far it seems all these dimensional tables will be small.
2. Could create a view in the control flow,before execution of the DataFlow with generic column names against a variable table and use this same view for every lookup - again not sure of performance etc
3. could just replace this with a merge/join as long as it does full outer, have not used this option so I'm not quite clear on the dynamic properties
now the last part update or insert
Insert
1. Seems like I could use the OLEDB destination and make the table a variable - have not tried to see if this works.
Update-
1. looking at the OLEDB command but not sure how it would handle a variable table name in an update statement - will need to try this out
2. OLEDB command that calls a proc that executes a dynamically built update statement
3. Write the update items to a generic holding table then run an update in the control flow from a dymanically built sql statement.
Analysis :: Two Sets Specified In UNION Function Have Different Dimensional
May 14, 2015I am using following Query to get data from Cube for some dates and Quarters at single time, but I am getting an error "Two sets Specified in the UNION function have different dimensional".
WITH
MEMBER [Measures].[Data Value1]
AS [Measures].[Average Value]
SELECT
{UNION(
([DimPeriodType].[Period Type Key].&[1],[DimDate].[Date Key].&[20150428],[DimEnergyCalculated Ratio].[Calc Ratio Key].&[1],[Measures].[Data
[code]....
Data Warehousing :: Can Use Dimensional Model For OLTP System?
Jul 9, 2015Question: Is it feasible to use a star schema dimensional model for an OLTP system that incurs few (750 per day)Sales Orders transactions?
Background: My customer wants to replace an existing OLTP system database because it runs on Oracle and their in-house expertise is in SQL Server. The original database developers that designed the Oracle DB have apparently retired. The Oracle database has been over-normalized, to say the least. The number of sales orders being entered daily is small: about 500-750 per day. These entries are done at the five clerks' convenience, from a paper form, and are very unlikely to ever be entered in quick succession. Nothing else gets regularly entered into this database except for the occasional change to a customer, but new customers are very few and far between.
I've designed a star schema for the replacement database with the Sales Order Header and Sales Order detail table combined into a single 'fact' table, and I've introduced some duplication into dimension tables (like customer) in order to eliminate some of the joins (and confusion) that were built into the original database.
I've never tried this before. Is there any reason this would not or should not work?
Dimensional Modeling: Age As A Slowly Changing Dimension Attribute?
Oct 8, 2007
Hi all
Probably not the right forum - pointers would be appreciated - but I'll give it a try anyway:
I'm in the process of designing a relational database to be used in a BI scenario - ie. dimension and fact tables. The data will eventually be used to feed cubes in Analysis services, however end users will probably be allowed to run reports aginst views of the relational database.
I'm currently looking at the employee dimensions and my first try would designate AGE as a SCD Type 2 attribute. As a result every employee gets at least one new record every year as AGE increases. Given that BIRTHDATE is specified should I drop AGE from the tables and recreate it as a computed attribute in database views and/or cubes?
Regards, Steen
Power Pivot :: How To Get Back To Traditional Hierarchy Dimensional View
Jun 17, 2015When ever I create a new Powerpivot from an AS cube, PowerPivot shows the data only in flattened PowerPivot format. How can I get back to traditional hierarchy dimensional view in Pivot table.
View 5 Replies View RelatedAnalysis :: SSAS Tabular And Multi-dimensional Instances Using Same Server Instance?
Aug 27, 2015Is it possible to install 2 SSAS instance (one default and one named) and have them access a single sql server instance (default) all on the same server?
View 3 Replies View RelatedArray
Jul 17, 2007how can i send array from asp.net to sql?
View 3 Replies View RelatedGet Array Value From A Loop?
Jun 19, 2006Hi, I am trying to do a loop while a list of array is assigned ('CHP,CNH,COW') ... I am using comma seperator to get each list value ... but, it donest really do what I am trying to do ... pls help!!! How do I loop through each value and do the rest ...??
=====================================
DECLARE @ABBR AS NVARCHAR(50)SET @ABBR = 'CHP,CNH,COW'
DECLARE @SEP AS NVARCHAR(5)SET @SEP = ','
WHILE patindex('%,' + @ABBR + ',%', @ABBR ) > 0 BEGIN
-- do the rest
END
SQL Query In A VB.net Array
Jan 31, 2007I want to do a query on an SQL Server 2005 db and have the results returned into aarray or collection in vb.net... how do I do this? I know the basicconnection and stuff.. just not how to get the result to an array thanks!
View 1 Replies View RelatedArray Problem
Apr 4, 2007Hi All,
I have this code below:Dim a As Integer = 0 While a <= myArray.Length() Conn.Open() Dim UpdateCmd As New SqlClient.SqlCommand("Update email_addr SET category = 'Deleted' where HP = @hp", Conn) UpdateCmd.Parameters.Add("@hp", SqlDbType.VarChar, 50).Value = myArray(a) UpdateCmd.ExecuteNonQuery() a = a + 1 Conn.Close() End While
BUt, When I run it, I receive error "Prepared statement '(@hp varchar(50))Update email_addr SET category = 'Deleted' wher' expects parameter @hp, which was not supplied. "
DOes anyone have the solution???
Thanks....
Array In WHERE Clause
Aug 28, 2007Hello, how can I use an array in a WHERE clause? If I had an array saystring[] NamesArray = new string[] {"Tom", "***", "Harry"} How would I do this:string myquery = "SELET name, city, country FROM myTable WHERE name in NamesArray" Thanks in advance,Louis
View 3 Replies View RelatedDataRow Array
Jan 16, 2008Hi,
i m pretty new to this forum and c#.net
i m doin a project in c#.net
I have four values in my datarow array
for example
DataRow[] cmb;
cmb=dsResult.Tables[0].Select("Controls Like 'cmb%'");// Here i m getting four Rows
for(i=0;i<cmb.Length;i++)
{
cmb[i]=Session["cmb'+i].ToString().Trim()//Here i m getting error;Cannot implicitly convert type 'string' to 'System.Data.DataRow'
}
How to assign my session values to them.
I want to assign my value stored in the session variable to that array.Is there any way i can do it.Can i convert datarow array to string array! Please can any one help
me.
Parameterized Or Array With This SQL?
Jun 2, 2008I have two CheckBoxList controls. One CheckBoxList is a group of area codes as they apply to our customers, and the second CheckBoxList is a group of categories of those customers. The code below works fine with either CheckBoxList as a standalone (this code applies to the Area Codes selection), but what I need is the VB code to combine the choices a user makes in both CheckBoxLists. Is this where parameterized SQL comes into play? Or can I/should I use an array statement to combine both CheckBoxList choices? Sometimes a user will select nothing in one CBL and a few choices in the other, or vice versa, or a handful of choices in both CBLs, so that they might want only customers in, say two area codes and then only the selected categories of those area codes. Need help on this one, thanks...Protected Sub btn_CustomerSearchCombine_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btn_CustomerSearchCombine.Click Dim ACSelections As Boolean = False Dim ACItem As ListItem For Each ACItem In cbl_CustomerSearchAREA.Items If ACItem.Selected Then ACSelections = True End If Next If ACSelections = True Then Dim ACSqlString As String ACSqlString = "SELECT Customers.CustomerID, Customers.CustomerName, Customers.CategoryID, Customers.EstHours, Customers.Locality, Categories.Category FROM Customers INNER JOIN Categories ON Customers.CategoryID = Categories.CategoryID WHERE " For Each ACItem In cbl_CustomerSearchAREA.Items If ACItem.Selected Then ACSqlString &= "Customers.AreaCodeID = '" & ACItem.Value & "' OR " End If Next ACSqlString = Left(ACSqlString, Len(ACSqlString) - 4) ACSqlString &= "ORDER By Customers.CustomerName" sql_CustomerSearchGrid.SelectCommand = ACSqlString End IfEnd Sub
View 7 Replies View RelatedResult Set Into Array
Aug 22, 2004Hi All,
How can I read a query result set (which are of type VARCHAR) into an array? i.e the result set comprises of just one column and 5 rows and i want to save these into an array to easily extract each row whenever i want to. Hope I have conveyed my idea clearly.
Gayathri
SqlCommand Array Help
Feb 19, 2006I want to do something like the following but I get an error: Object reference not set to an instance of an object. SqlConnection sqlConnection = new SqlConnection("server=xxxxx"); SqlCommand [] cmd = new SqlCommand[3]; Object returnValue; cmd[0].CommandText = "DO QUERY"; cmd[1].CommandText = "DO QUERY"; cmd[2].CommandText = "DO QUERY"; cmd[3].CommandText = "DO QUERY"; } sqlConnection.Open();int i = 0;while(i<4){ cmd[i].CommandType = CommandType.Text; cmd[i].Connection = sqlConnection; cmd[i].ExecuteNonQuery(); returnValue[i] = cmd[i].ExecuteScalar();i++} sqlConnection.Close();How should I do the followingThanks
View 9 Replies View RelatedInline Sql With An Array
Sep 20, 2006I have data which looks like below
actid labname
100 CKS
200 CKS;HDP;LAS
I need the data to be
actid labname
200 CKS
200 HDP
200 LAS
The ; is the seperator
For a reporting product I created a sp which created a temp table and then using my function below built. problem is the product won't allow me to create a temp table. With what I have below anyone have any creative ideas I could use. In-line sql, subquery views?
select enc_id,labcnt,order_name,date_due
reate FUNCTION fn_GET_ARRAY_VALUE(
@DELIMITER VARCHAR(100),
@STRING VARCHAR(1000),
@ARRAY_POSITION INT)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @CURRENT_POSITION INT
DECLARE @VALUE VARCHAR(8000)
SET @CURRENT_POSITION = 0
WHILE @CURRENT_POSITION<@ARRAY_POSITION
AND CHARINDEX(@DELIMITER,@STRING,0)>0
BEGIN
SET @STRING =
SUBSTRING(@STRING,
CHARINDEX(@DELIMITER, @STRING, 0)
+LEN(@DELIMITER),
LEN(@STRING)
-CHARINDEX(@DELIMITER, @STRING, 0)
+ LEN(@DELIMITER)
)
SET @CURRENT_POSITION = @CURRENT_POSITION + 1
END
IF CHARINDEX(@DELIMITER,@STRING,0)=0
SET @VALUE = @STRING
ELSE
SET @VALUE = SUBSTRING(@STRING, 0,
CHARINDEX(@DELIMITER, @STRING, 0)
)
RETURN(LTRIM(RTRIM(@VALUE)))
END
Array List In DB
Feb 14, 2005Hi,
Is it possible to have a column as array in SQL Server 2000- similar to Oracle's varray. If yes, how?
rgds,
Kishore
Array In SQL Server
Jan 27, 2004How would one implement an array in SQL Server ?
Specically, i'm parsing a field into the array, and doing operations on these elements.
Thanks.
Array In A STOC??
Feb 3, 2004Hi all,
My Issue is the next thing:
I have a table filled with contracts
I have Stoc that checks if contract has expired
If I start the Stoc I must give it the contractnumber with it like:
SP_expired 8
This works fine but now I want that the Stoc checks all the contracts by just starting the Stoc.
I don't really know how to do this, I have thinking about using an array.
But don't have an idea where to start....
Thnx in advance
Dynamic Array
Jan 3, 2007Please excuse me if this is the wrong forum. I hope your combined genius will be able to point me in the right direction.
I have an ASP page that accepts any SQL query. I want to create a table based on the result set so the number of columns can grow/shrink. But I don't know how. Can anyone help me?
My ASP Code shows how I create a table with only 8 fields.
<%
Dim objConn
dim SQLstmt
Dim objRS
Dim SQLqry
Dim UID
Dim RecordArray
Dim I
UID = right(Request.ServerVariables("AUTH_USER"),8)
SQLqry = Request.Form("qry")
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.ConnectionString="Trusted_Connection=yes;driver=SQL Server;server=Server;database=db;"
objConn.Open
If SQLqry = "" then
Response.Write "Please Enter a SQL Query"
Else
Set objRS = objConn.Execute(SQLqry)
Response.Write "<H5 align = ""center"">" & sqlqry & "</H5>"
Response.Write "<Table align = ""center"" border = ""1"">"
For I = 0 To objRS.Fields.Count - 1
Response.Write "<TD><B>" & objRS(I).Name & "</B></TD>"
Next
Do while not objRS.EOF
RecordArray = objRS.GetRows(30)
For I = 0 To UBound(RecordArray,2)
Response.Write "<TR>"
Response.Write "<TD>" & RecordArray(0,I) & "</TD>"
Response.Write "<TD>" & RecordArray(1,I) & "</TD>"
Response.Write "<TD>" & RecordArray(2,I) & "</TD>"
Response.Write "<TD>" & RecordArray(3,I) & "</TD>"
Response.Write "<TD>" & RecordArray(4,I) & "</TD>"
Response.Write "<TD>" & RecordArray(5,I) & "</TD>"
Response.Write "<TD>" & RecordArray(6,I) & "</TD>"
Response.Write "<TD>" & RecordArray(7,I) & "</TD>"
Response.Write "</TR>"
Next
Loop
Response.Write "</TABLE>"
objRS.close
objConn.close
set objRS=nothing
set objConn=nothing
End if
%>
Array As Parameter
Jan 28, 2008hi guys im having some problems with arrays as parameter
what i want to do is pass a array of comma separated values i.e 12,24,36 and use the in(@parameter) but it doesnt work
i've found some solutions, like
ALTER PROCEDURE [dbo].[GetLawyers] ( @LawyerTypeIDs VARCHAR(100)
AS
DECLARE @SQL VARCHAR(2000)
SET @SQL = 'SELECT * FROM [dbo].[TB_VALOR_BANCOS]
WHERE [PARCELAS] IN (' + @LawyerTypeIDs + ')'
EXECUTE (@SQL)
but in my case doesnt work.
here is a part of my code
SELECT * FROM (
SELECT *, ROW_NUMBER()
OVER (PARTITION BY cod_banco ORDER BY coeficiente asc) AS RecID FROM tb_valor_bancos
where
(1 = CASE WHEN @TABELA IS NULL THEN 1
ELSE CASE WHEN TABELA = @TABELA THEN 1
ELSE 0
END
END
)
AND (1 = CASE WHEN @PARCELAS IS NULL THEN 1
ELSE CASE WHEN PARCELAS in(@PARCELAS) THEN 1
ELSE 0
END
END
)
...
i know there is a way to pass the array into a table but it doesnt work too
thanks
Split Array
Feb 21, 2008hi,
how can i split text separated by semicolumn in different cells:
text1;text2;text3;
into
1 - text1
2 - text2
3 - text3
thank you
How To Identify An Array
Nov 20, 2006I have seen several examples explaining the fact that a tablecontaining a field for each day of the week is for the most part anarray. An specific example is where data representing worked hours isstored in a table.CREATE TABLE [hoursWorked] ([id] [int] NOT NULL ,[location_id] [tinyint] NOT NULL,[sunday] [int] NULL ,[monday] [int] NULL ,[tuesday] [int] NULL ,[wednesday] [int] NULL ,[thursday] [int] NULL ,[friday] [int] NULL ,[saturday] [int] NULL)I had to work with a table with a similar structure about 7 years agoand I remember that writing code against the table was pretty close toHell on earth.I am now looking at a table that is similar in nature - but different.CREATE TABLE [blah] ([concat_1_id] [int] NOT NULL ,[concat_2_id] [int] NOT NULL ,[code_1] [varchar] (30) NOT NULL ,[code_2] [varchar] (20) NULL ,[code_3] [varchar] (20) NULL ,[some_flg] [char] (1) NOT NULL) ON [PRIMARY]The value for code_2 and code_3 will be dependently null and they willrepresent similar data in both records (i.e. the value "abc" can existin both fields) . For example if code_2 contains data then code_3 willprobably not contain data.I do not think that this is an array. But with so many rows wherecode_2 and code_3 will be NULL something just does not feel right.I will appreciate your input.
View 1 Replies View RelatedArray In TSQL
Sep 19, 2007Hi all,
I have data like this :
POHDR;JAKARTA;St.1DTL;1. ;00248337;8996006855701 ;083;041;002; 7,812.50; 312,500.00; 284,091.00; 8,875.00
first row is the PO header and the second is the detail. This data was collected using Export Import Wizard from flatfile and put it in one column. I want to split the header and the detail into different table and map each column separated by ; to a proper field. For this work I should check every column separated by ; with CHARINDEX function but I have to write the TSQL as much as the count of ;. It would be better if we can use array to simplify the code.
Can we use array in Transact SQL ? since I have no clue referring to SQLBOL. Thanks in advance.
Best regards,
Hery
Create An Array With T-SQL
Oct 10, 2007I'm using SQL Server 2005 and have have a table similar to the following:
Name Week Role
Bob 1 Primary
Bob 2 Backup
Bob 3 Primary
Joe 1 Backup
Joe 2 Backup
Mike 1 Primary
Mike 2 Backup
Is there a t-SQL query that will combine the role and week into an array and return a table like:
Name Role
Bob Primary: 1, 3
Backup: 2
Joe Backup: 1, 2
Mike Primary: 1
Backup: 2
Array Parameter
Jul 20, 2007
Is there any way to make a CLR stored procedure that accepts an array style set of data? I want to make a stored procedure that accepts to parameters of type int, and then one more that is an array of name/value pairs. Is it possible to do something like this?
Populate The Array From Database
Oct 18, 2006Hi, I want to populate the array with a single column values from database(sqlserver 2000)Ex. name ageaaaa 23bbbb 43cccc 18 Now i want to populate the array with name field values. Please anyone guide me how to do this.Thanks in advanceBala
Populate DB Records In Array
Nov 9, 2006Using the SqlDataReader, I am retrieveing records from a SQL Server 2005 DB table. Since I am using the SqlDataReader to retrieve the records from the DB, I have to use the Read method of the SqlDataReader like this:Dim sqlReader As SqlDataReaderWhile(sqlReader.Read) Response.Write(sqlReader.GetValue(0) & "<br>") Response.Write(sqlReader.GetValue(1) & "<br>") Response.Write(sqlReader.GetValue(2) & "<br>")End WhileThe records retrieved can only be accessed inside the While loop. I want to access the records outside the While loop as well. Is there anyway by which I can do this, maybe by populating the recordset in an array variable & then using it outside the While loop?
View 3 Replies View RelatedIndex Was Outside The Bounds Of The Array
Feb 16, 2007Index was outside the bounds of the array
When working with a SQL Server 2005 database and ASP.NET,
I get this error when trying to pull a small amount of data from the database,
Any help would be great, thanks a lot
I work for a large company, so I have to watch the code I release.