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
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 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.
Su writes "I'm trying to use a stored procedure to dynamically update a table whenever other staff in other departments update their do any changes to their databaseds. and thanks for your web site taught me how to pass table names as parameters. But I still have problems withe sql command. You have an example in your article ('dynamic sql 2'), showing how to do a sql SELECTION using a table name and a local variable. But the sql command only use a local variable of varchar type. I'm trying to do INSERT with local variables with different data types. For example:
CREATE PROCEDURE KPISU_F_TotalByF @inputT_From varchar(10), @inputT_To varchar(10), @TableName varchar(1000) AS ----------------------------------------------------- --------input variable------------------------------- DECLARE @inputTerm_From varchar(10), @inputTerm_To varchar(10), @sql_empty varchar(2000), @sql_refresh varchar(2000) ---------------------------------------------------- IF EXISTS (select * from tempdb.dbo.sysobjects where id LIKE object_id('tempdb..#tmpOTLTotalByF')) DROP TABLE #tmpOTLTotalByF
Server: Msg 245, Level 16, State 1, Procedure KPISU_F_TotalByF, Line 256 Syntax error converting the varchar value 'INSERT KPISU_F_OTLTotalByF06 VALUES (14-19 Academy, 2005, ' to a column of data type int. -----------------------------------------------------------
I guess I could change all the columns in the table to data type of varchar. But are there any other way to solve this problem?
As far as i can tell, you cannot pass an array (or structured) parameter to a stored procedure...
Ok, this means when you have to store data for an item and its sub-items (e.g. a product and its - say- version specific infos) you cannot code all the logic into a single procedure. You need to code it into your DAL, where you first insert then loop to sub-insert...
Is this correct? Is there any other way to approach the problem?
============================== CREATE procedure dbo.AddTb2FromTb1 @Tb1No nvarchar(1000) as insert into Tb2 (*) select * from Tb1 where Tb1 IN (@Tb1No) /* How to Passing an Array to a Stored Procedures ??? */ ==============================
dbo.AddTb2FromTb1 'No001' is Work ! dbo.AddTb2FromTb1 'No001,No002,Bo003' is not Work !
I thought that I had read somewhere that SQL 2000 allowed you to pass an array into a stored procedure. I've tried to look up some additional information pertaining to this but I haven't found anything. Did I dream this or can you pass an array to a stored procedure with SQL 2000? If you can please give me some information on this or where I can find some.
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 have two database(MYDB1 , MYDB2) on two different server's(SERVER1 , SERVER2) . I want to create an store procedure in MYDB1 on SERVER1 and get some data from a table of MYDB2 on SERVER2. How can i do this?
I want to know the differences between SQL Server 2000 storedprocedures and oracle stored procedures? Do they have differentsyntax? The concept should be the same that the stored proceduresexecute in the database server with better performance?Please advise good references for Oracle stored procedures also.thanks!!
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?
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
public Set DoSomthing(Set toBeProcessed, Set measuresToWorkWith)The set measurseToWorkWith is passed as {[Measures].[Measure1], [Measures].[Measure2] ...}
with the measures being real or query-scoped calculated members.
To get the value of the measure for each tuple in the set toBeProcessed, I create an Expression for each tuple (measure) in the set measuresToWorkWith then for each tuple in toBeProcessed call expression.Calculate(tuple) which returns a MDXValue.
My problem is that in order to make the code generic I need to get the real (.NET) data type of the MDXValue. The class only has explicit conversion methods ToInt16() etc which implies that the data type is known at design time.
However, if one of the measures is a query-scoped calculation then it could return a .NET double, int, bool or string.
If the measure is real then I can look up its metadata. However, it appears that if it is a formula (scoped member) then are all bets are off?
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 - 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'?
How does one pass into a Stored Procedure an array of numbers to be used in an IN clause? If I pass "1,2" in a VARCHAR, the stored procedure sees only the first number (1 in this case). I'm using VB and ADO.NET, but I don't know how to set up the stored procedure for an array. Is there a parsing function to do this? CREATE PROCEDURE TestInClause( @TeamList VARCHAR)ASSELECT Name FROM Teams WHERE TeamID IN (@TeamList); /* sees only 1st number */GO
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,This is driving me nuts, I have a table that stores notes regarding anoperation in an IMAGE data type field in MS SQL Server 2000.I can read and write no problem using Access using the StrConv function andI can Update the field correctly in T-SQL using:DECLARE @ptrval varbinary(16)SELECT @ptrval = TEXTPTR(BITS_data)FROM mytable_BINARY WHERE ID = 'RB215'WRITETEXT OPERATION_BINARY.BITS @ptrval 'My notes for this operation'However, I just can not seem to be able to convert back to text theinformation once it is stored using T-SQL.My selects keep returning bin data.How to do this! Thanks for your help.SD
Can anyone tell me how to access data from Stored Procedures using data adapters? My task is to select a row which is valid with data particular value. Suppose i had to get all values of particular user after validating username and password. Can anyone give me some hint regarding store procedure and retriving data from stored procedure using data adapters ? How can i bind data to dropdownbox of one field in the table using datasets and data adapters? How can i insert data in database using data adapters?Can any one solve this?
Hi, am new to sql server. Please some one send me some introduction abt stored procedures and some coding exammples to update and fetch the data from datasourece. thanks.
Hi, I'm developing a fresh SQL DB which is result of a deep analysis of an old Access DB. THe thing is, this old one had very complex consultations to the Access tables, and some consultations were using another consultations as way to select some specific data. THe ideia in SQL is to avoid that too, however, there are some data that may serve exactly the same to some bigger stored procedures. This way, I have three options I guess: 1. Create every stored procedure making select queries directly to the table and it's done!2. Create auxiliary stored procedures which will select the redundant data, and when it is needed, another stored procedures call this one and use its returned data. (is this possible anyway?).3. I create a view to this redundant data, and the greater depth stored procedures access this data of the view when needed. I've heard, however, that a select to a view is slower than directly to the table, once the view adds an extra query to the process.. What is your guess on this issue of mine?I'm almost sure that option 1 is the best, however, I'd love to hear from you guys your opinion on this. The greater issue above this all is just one - performance. Thanks a lot!
How do you run a stored procedure on PDW via SSIS? I've tried Execute SQL Task and Execute T-SQL Task but in both cases the task will run and complete almost immediately. Task shows success, no errors, but nothing happens in PDW.  PDW admin console does not even register the query. Procedures run fine manually from SQL Server Object Explorer connection.
I have a stored procedure a portion of which looks like this:
IF (CAST (@intMyDate AS datetime)) IN ( SELECT DISTINCT SHOW_END_DATE FROM PayPerView PP WHERE PP_Pay_Indicator = 'N' ) BEGIN --ToDo Here END
where: @intMyDate is of type int and is of the form 19991013 SHOW_END_DATE is of type datetime and is of the form 13/10/1999
however when I run the procedure in sql query analyzer as:
EXEC sp_mystoredproc param1, param2
i get the error:
Server: Msg 242, Level 16, State 3, Procedure usp_Summary_Incap, Line 106 The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
what is the proper way of doing the conversion from int to datetime in stored procedure?
I have my database: "RequestTrack" My table (with its columns): "Request"RequestKey (automatically generated)..and the Primary KeyEntryDate (datetime)Summary (nvarchar)RequestStatusCodeKey (bigint)EntryUserID (nvarchar)EntryUserEmail (nvarchar)I am wanting to create a basic web form where my user interface has 3 text boxes and a Submit button: txtUserID.TexttxtEmailAddress.TexttxtRequestSummary.Text **After I hit the submit button the information will then be inserted into the database. Also the RequestStatusCodeKey will be MANUALLY typed in so that will not require the user to add that. Please please please help ! I've been searching online for days and looking at various websites and still havent found anything. I've found somethings but they went into too much depth with too much information. I am just wanting to stay basic but w/o using SQLDataSource Controls. I would like to be able to store a lot of data. Thanks for your help!!!
I am in the position where I have to transfer data from an old database schema to a new database schema. During the transfer process alot of logic has to be performed so that the old data gets inserted into the new tables and are efficiently done so that all foreign keys are remained and newly created keys (as the new schema is Normalised alot more) are correct.
Is it best if I perform all this logic in a Stored Procedure or in C# code (where the queries will also be run)?
I have defined a user defined data type. When I try to create a stored procedure specifying the column and user define data tpye I receive message
Server: Msg 2715, Level 16, State 3, Procedure spStoredproc, Line 0 Column or parameter #1: Cannot find data type udtcol1. Server: Msg 2715, Level 16, State 1, Procedure spStoredproc, Line 0 Column or parameter #2: Cannot find data type udtcol2. Server: Msg 2715, Level 16, State 1, Procedure spStoredproc, Line 0 Column or parameter #3: Cannot find data type udtcol3
Can you have user defined data types in stored procedures.
Store Procedure creation text
CREATE PROCEDURE spStoredproc @col1 udtcol1, @col2 udtcol2, @col3 udtcol3 AS INSERT INTO tblTempEmployee (col1 , col2 , Col3) VALUES (@col1 , @col2, @col3) GO SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS OFF GO
