I have a scenario in which my stored procedure has to return few
variables with their value and also the collection. Now in SQL their is
no 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 values
of and secondly wondering how should i be creating the parameter as a
table returntype.
I would like to INSERT an array of integer into a table in MSSQL Server, then count the number of rows in the table with c++ using ODBC. Here you find my code to do this task:
In my code, I would like to Insert the array and then count the number of rows:
SQLTCHAR Statement[] = _T("INSERT INTO information1(Wert1,Wert2) VALUES(?,?) select count(*) as a from information1 ") ;
Problem : My expectation is, that first 9 rows are inserted into table then comes 9 as result to user (if the table is empty) but this code returns me 1 if first the table is empty. If the table is not empty, it returns 1+number of  existing rows in the table. If I take a look inside the table, the 9 rows are successfully inserted in it. Only the number of rows in the table is wrong.
Hint : If I monitor the database using SQL Profiler. It looks like this:
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 table "Clients" who have associated records in table "Mailings" I want to populate a gridview using a single query that grabs all the info I need so that I may utilize the gridview's built in sorting. I'm trying to return records containing the next upcoming mailing for each client.
The closest I can get is below: I'm using GROUP BY because it allows me to return a single record for each client and the MIN part allows me to return the associated record in the mailings table for each client that contains the next upcoming 'send_date'
SELECT MIN(dbo.tbl_clients.client_last_name) AS exp_last_name, MIN(dbo.tbl_mailings.send_date) AS exp_send_date, MIN(dbo.tbl_mailings.user_id) AS exp_user_id, dbo.tbl_clients.client_id, MIN(dbo.tbl_mailings.mailing_id) AS exp_mailing_idFROM dbo.tbl_clients INNER JOIN dbo.tbl_mailings ON dbo.tbl_clients.client_id = dbo.tbl_mailings.client_idWHERE (dbo.tbl_mailings.user_id = 1000)GROUP BY dbo.tbl_clients.client_id The user_id set at 1000 part is what makes it rightly pull in all clients for a particular user. Problem is, by using the GROUP BY statement I'm just getting the lowest 'mailing_id' number and NOT the actual entry associated with mailing item I want to return. Same goes for the last_name field. Perhaps I need to have a subquery within my WHERE clause?Or am I barking up the wrong tree entirely..
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?
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'?
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.
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.
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
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 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
Hi,i need an opinion on this,...i am using UNION ALL quite a bit in my queries..most of the time these are like 1 line select query,returning "different" type of stuff ..like say query 1 is returning one EmpName and query 2 is DOB and so on(keeping it simple for example)...i am combining it b/c i find it cumbersome to call 10 dfferent query to populate 1 structure(table etc) ,rather i wud call one and manipulate the result to fill up that one structure..Is that okay to combine different kind of return types in one (here first row of multi-select query result is empName,second row is DOB and so on...)?????thanks
I am calling a stored procedure and have an output parameter that I pass back to my asp page. The datatype of the value is nvarchar. When I run the page I get an error "Syntax error converting the nvarchar value 'OK' to a column of data type int". Can a return parameter be anything else other than an integer? Below is my code. Thanks for the help.
Parem = cmd.parameters.add("@Retval",SqlDbType.nvarchar,50) Parem.Direction = ParameterDirection.Output
Hi everybody,VARCHAR has a higher precedence than CHAR. If you have a querySELECT COALESCE(c1,c2) FROM T1where c1 is CHAR(5) and c2 is VARCHAR(10), I would expect the returntype to be varchar(10) similiar to UNION. However it turns out tobe CHAR(10). Does anyone know why that is so?Thanks,Steffen
Hi, I have another question about code in RS. I have a table cell with FORMAT of "$#,##0,0". If I have any number in the cell I want to display it. If I have 0 (zero) I want the cell to be empty.
I know how to do it with an expression: iif (Fileds!myFiled.Value == 0,"", Fileds!myFiled.Value)
Now I want to do it with a code section: =Code.hideIfZero(Fileds!myFiled.Value)
The problem is that the function should return Integer and if the value is zero I need to return empty string.
It does't say any thing about returning the string but when it trying to use the format on a string it give me a worning and print #ERORR in the cell.
is there a solution for this? Do you know the syntax to format the number in the function before the return? (Sorry for the dumb question but I know C# not VB.net and there is no intelisance in that editor).
I 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
Hi, I am trying to retrive some XML from my SQL server 2005 database. The XML is in a table called "myxml" and it is being stored a native XML. The Field type is "XML". The VB code returns nothing when I select SELECT xml. How ever if i SELECT NAME it displays the correct name from the name field. how do I return the XML to be viewed as XML throught the response.write? Thanks! Here is my Table definition:
Column Name Data Type Allow Nulls
Id Int
xml xml Yes
NAME Nvarchar(50) yes My XML that is stored in the XML field: <ROOT> <CHAPTER> <TITLE>This is a test</TITLE> </CHAPTER> </ROOT> Here is my VB code: Dim myconnection As SqlConnection Dim mycommand As SqlCommand myconnection = New SqlConnection() myconnection.ConnectionString = _ ConfigurationManager.ConnectionStrings("myxmlConnectionString2").ConnectionString
Dim strSQL As String = "SELECT [xml] FROM myxml WHERE id = 76"
' create SQL command instance mycommand = New SqlCommand(strSQL, myconnection)
Try ' open database connection myconnection.Open() 'execute T-SQL command Dim dr = mycommand.ExecuteReader() While dr.read() Response.Write("<p>" & dr(0).ToString() & "</p>") End While Response.Write("GOT IT")
Catch ex As Exception Response.Write(ex.Message) Response.Write(strSQL) Response.End()
I am trying to figure out a way to analyze a stored procedure to deduce its output type.
A stored procedure can return values throught one of the following:
1) Using the Return keyword
2) Returning a recordset
3) Throught its Output parameters
4) A combination of 1, 2 or 3
Using the Information_Schema, you can access the SQL code of the stored procedure from, say, VB.Net. From there, I want to know what output type the stored procedure has.
A) for 1), I guess I can parse the code for the 'Return' keyword, but that wouldn't be efficient, for the 'Return' word could be inserted into string values...
B) for 2), it would be difficult to analyze the stored procedure's code to know FOR SURE whether a recordset is returned. You just can't parse for SELECT keywords in complex code...
C) 3) should be easy enough, getting the return parameters types from the Information_Schema.
So, is there an easy way that I missed for achieving this, some object in the .Net Framework that I could use to analyze SQL queries perhaps, or am I doomed to do it the hard way?
hi to all, i have written SQL-CLR function using C# which will perform some manupulation and will not not return any value(return type is void) i am creating sql function using SQL script
CREATE FUNCTION dbo.Amex ()
RETURNS NULL
AS CALLER
WITH EXECUTE
AS
EXTERNAL
NAME [AMEX].[UserDefinedFunctions].[Function1];
GO
but i am getting error. there is something wrong in my sql syntax..can anybody help me?
when creating funtion having signature
public static void Function1()
i am getting error....................... Error 1 The method "Function1" in class "UserDefinedFunctions" marked as a user defined function must return a scalar value or ISqlReader. SqlServerProject1
I'm using quite odd combination of technology for my project, I'm using PHP and MSSQL 2000, at one certain page, I want to insert to a table where one of the column is TEXT data type, and I want to get the value from the TEXTAREA at the page, of course, with carriage return captured, I manage to get it done in MySQL, where it automatically store the carriage return keyed in by user at the TEXTAREA, while for MSSQL I no luck in finding solution for this, is there any settings I can set or I need to convert the carriage return keystroke to HTML tag at my PHP?
I am using vs2008 with c#.I have table in my database that has email addresses stored in a column. I would like somehow to take each email address from every row and paste them into a textbox that can be copied and pasted with a comma delimiter into outlook so I can send a mass email.I have no idea how I can accomplish this task.
I have an asp.net project that displays a timesheet based on a fortnightly system.
It has a Y and X axis, i.e. Sun-Mon, Sun-Mon across the top and Categories of hour types accross the Y axis, i.e. Holidays, Overtime, Maintenance.
I was using a datagrid to gather the rows, but I need to have the whole grid in edit mode which is something that requires more coding. So I have swapped to a datalist.
It will take me 240 fields to store all the data in their corresponding fields. So I basically need to know if there is an easier way to store two dimensional array type data into an SQL table using a minimum of fields.
I like to define my procedure parameter type to match a referenced table colum type, similar to PL/SQL "table.column%type" notation. That way, when the table column is changes, I would not have to change my stored proc. Any suggestion?
CREATE PROCEDURE fts_insert_service_tasks( @status_no int output, @status_text nvarchar(255) output, @fts_employee char(100) , @fts_SCCode bigint, @fts_TaskDescription ntext) AS
declare @str_err nvarchar(255) declare @err_no int
set @err_no=0
if ( isnumeric(@fts_SCCode) = 0 ) begin set @str_err ='The fts Sccode is not a number' set @status_text = @str_err set @err_no=@err_no+1 return end
if ( @fts_SCCode = '' ) begin set @str_err ='The fts Sccode can not be null ' set @status_text = @str_err set @err_no=@err_no+1 return end
if ( len(@fts_employee) > 100) begin set @str_err ='Maximum Employee length allowed is 100 characters' set @status_text = @str_err set @err_no=@err_no+1 return end
if ( @fts_employee = '' ) begin set @str_err ='The employee fiedl can not be null' set @status_text = @str_err set @err_no=@err_no+1 return end
if (@err_no=0) begin
INSERT INTO fts_ServiceTasks (fts_employee , fts_Sccode, fts_taskdescription) VALUES(@fts_employee, @fts_SCCode, @fts_taskdescription)
set @status_no=0 set @status_text = 'Add Service Task Ok' end
else
begin set @status_no=@err_no set @status_text = @str_err end GO
and I called it from the ASP
<%function Add_Service_Task(fts_employee,fts_sccode, fts_TaskDescription) cm.ActiveConnection = m_conn cm.CommandType = 4 cm.CommandText = "fts_insert_service_tasks" cm.Parameters.refresh cm.Parameters(3).Value = fts_employee cm.Parameters(4).Value = fts_sccode cm.Parameters(5).Value = fts_TaskDescription on error resume next cm.Execute if cm.Parameters(1)=0 then exec_command=cm.Parameters(2).Value else call obj_utils.ErrMsg(cm.Parameters(2).Value,3000) Response.End end if if err.number <> 0 then call obj_utils.ErrMsg("System error at " & err.number & err.Description & ", please contact the administrator", 5000) Response.End end if Add_Service_Task=exec_command end function%>
I test with SQL 2k, Win2k3 OK But with Win2k i got:
Error Type: Microsoft OLE DB Provider for SQL Server (0x80040E30) Type name is invalid. /fmits/classes/cls_servicecall.asp, line 256
I have created a function that will check whether the data is null or not. If its null then it will display that as No data else it will display the original value. Below is the function
GO Object: UserDefinedFunction [dbo].[fnchkNull] Script Date: 3/4/2015 12:01:58 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO
[code]...
The code is working good. However i want the return type to be dynamic. If the data type supplied is integer then i want to return a integer value like 0 if its null. if the data value is varchar then i want to return 'No Data'.
I am trying to have an Excecute SQL Task return a single row result set executed on SQL Server 2005.
The query in the Execute SQL Task is: select 735.234, 2454.123
I get a conversion error when trying to assign to SSIS variables of type Double. I have nothing configured in the "Parameter Mapping" tab. I have the two SSIS Double variables mapped to the Tesult Name 0 and 1 in the "Result Set" tab
I don't want to use a for loop enumerator since there is a single row returned.
I simply want to assign these two values to SSIS Double variables (double is the closest match)
I can't even hack this by converting the decimals as string and then using DirectCast to convert them to Double.
I'm looking for a way to pass an array of values as a parameter to a query in a table adapter. For example I want to run a query something like:SELECT * FROM menu WHERE menu_role IN (@roles)And I could pass something like 'RegisteredUser, SuperUser, OtherUser' to the @roles parameter.For some reason I can't figure out a way to do this. Any help would be greatly appericated.Thanks,Ryan.
how can i insert a carriage return when i update the field?
say i want to put the following inside a field: firstline secondline
how can i update/insert a column to have a return carriage inside it? UPDATE table SET column = 'firstline secondline'
the reason i want this is because when using a program (Solomon, by microsoft, purchasing software) to grab a field out of the database and when it displays that field in the programs textbox, i want it to be displayed on two separate lines
i tried doing UPDATE table SET column = 'firstline' + char(13) 'secondline'
but when in the solomon program, it displays an ascii character between firstline and secondline like: firstline||secondline
