How To Retrieve Value From Particular Column By Using Parameter
Jan 5, 2008
suppose a table has 4 column,
Name x y z z1
A 1 2 3 3
B 0 2 0 3
C 1 0 3 3
if i pass parameter as @param=1, only column of x should be displayed, if i send parameter as @param=2 only y column should be displayed, if @param=3 is parameter then Z column And Z1 column should be displayed. plz help to create sql query.
Hi, I'm having problems retrieving the output parameter from my stored procedure, i'm getting the following error An SqlParameter with ParameterName '@slideshowid' is not contained by this SqlParameterCollection code as follows: int publicationId = (int) Session["PublicationId"];string title = txtTitle.Text;int categoryid = Convert.ToInt32(dlCategory.SelectedItem.Value);SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);SqlCommand myCommand = new SqlCommand("sp_be_addSlideshow", myConnection);myCommand.CommandType = CommandType.StoredProcedure;myCommand.Parameters.Add(new SqlParameter("@publicationid", SqlDbType.Int));myCommand.Parameters["@publicationid"].Value = publicationId;myCommand.Parameters.Add(new SqlParameter("@title", SqlDbType.NVarChar));myCommand.Parameters["@title"].Value = title;myCommand.Parameters.Add(new SqlParameter("@categoryid", SqlDbType.Int));myCommand.Parameters["@categoryid"].Value = categoryid;myConnection.Open();myCommand.ExecuteNonQuery();string slideshowId = myCommand.Parameters["@slideshowid"].Value.ToString();myConnection.Close(); my stored procedure: CREATE PROCEDURE sp_be_addSlideshow( @publicationid int, @title nvarchar(50), @categoryid int, @slideshowid int = NULL OUTPUT)AS INSERT INTO Slideshow(publicationid,title,slideshowcategoryid,deleted) VALUES (@publicationid,@title,@categoryid,0) SELECT @slideshowid = SCOPE_IDENTITY()GO
Goal: My request is the retrieve the return result from sp_Test as 8, 2, 4, 1 ,3 (take a look at picture 1) based on the chronological list from User-Defined Table Type dbo.tvf_id.
Problem: When I execute the stored procedure I sp_Test I retrive the list that is from 1 to 8. I don't know how to do it?
Information: I'm using SQL server 2012
create table datatable (id int, name varchar(100), email varchar(10), phone varchar(10), cellphone varchar(10), none varchar(10) ); insert into datatable values
Currently, I want to get images of an item in my report for illustration purpose.
Below is my query to get image for item 'GL-10000' in the database. However, I would like to pass a parameter value, '@sItem', from my report so that it would get all related pics of items.
SQL Query:
Select top 1 item_picture_mst.picture from item_picture_mst where item_picture_mst.item in (select item_all.item from item_all where item_all.item = 'GL-10000') ORDER BY item_picture_mst.picture DESC;
i have a table that has 9 columns that belongs to other tables. i need to retrieve one column from this table. In order to do this, do i have to join all the other tables that has similar columns?
Hi, all.. I want to know the query to retrieve Column information that we can see from table Design view of Enterprise manager, such as Column name, Pk or not, FK table, Data Type, Null or not, Description(Specially Descrition).
I have search for and see how to put the columns for the last, first mi together (Name_Last + ', ' + Name_First + ' ' + Name_MI) as Name But how can I test the value of the Name_Type field to determine how the Name column looks if Name_Type = 1 then Name = (Name_Last + ', ' + Name_First + ' ' + Name_MI) else Name = Name_Last
The Name_Type represents Individual versus an Entity 0,ABC Pipeline,, 1,Williams, John,A
I am trying to retrieve all rows with the largest value in a particular column. The largest value could return many rows for a particular users. Here is what I have thus far.
SELECT DISTINCT ID, NAME, FOP, ACCT, CTNUM, ENDDATE, DEBIT, CREDIT, TRANSACTION_DATE, EXPORTED, CALENDAR_YEAR, FISCAL_YEAR, PAYROLL_IDENTIFIER, PAYROLL_NUMBER, [EARN-SEQNO], EVENT_SEQUENCE_NUMBER FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY ID, ACCT, PAYROLL_NUMBER,EVENT_SEQUENCE_NUMBER ORDER BY EVENT_SEQUENCE_NUMBER DESC) AS RN FROM PAYROLLYEAREND ) s WHERE RN = 1 AND ID = '16443' AND PAYROLL_NUMBER ='7'
In the above example, the EVENT_SEQUENCE_NUMBER is populated with values from 0 to 12. Could vary per user and PAYROLL_NUMBER. The query above returns 48 rows. However, all I want are the rows where EVENT_SEQUENCE_NUMBER is equal to the highest, which is in the above example is 12. The result would be 29 rows. The where clause is not part of overall query. Just isolating on one ID.
What would be the most efficient method of constructing a sql statement to retrieve certain record into (this gets tricky) rows of a table with 3 columns date.desc.
I have two tables something like this : Table : Account AccountID UserName Password
Table : Address AddressID AccountID Line1 Line2 State
My NHibernate mapping file looks like this : For Table Account <id name="ID" type="long" column="AccountID" access="property"> <generator class="identity"/> </id>
My Problem is it does entry in the Account table perfectly, but when it comes to do entry in Address Table it says AccountID can't be null, I'm wondering shouldn't it pick it up from the Account table as I'm passing in that object.
For the Workaround I was thinking maybe I can get the identity of the last entry saved and somehow save it to the other table myself.
I have a table with an Identity column. Is there a way to get the automatically generated primary key after I insert something to the table? If so, what is the syntax?
Is there any way to retrieve partial contents of a database column? For example, say a column holds 5,000 characters, but I only wish to retrieve the first 50. Thanks
I want to just retrieve the numbers left of the space in this address column what can I use? The Address column is Varchar.
Address: 6501 Red Hook Place #201 32 Orchard St 1 Hardy Rd ste 210 379 E Center St. 170 Bridge St 399 Interpace Pkwy 1800 Valley Rd 2210 Harding Hwy 41 Maple Ct
Expected Results:
Address 6501 32 1 379 170 399 1800 2210 41
I tried using this:
Left([ADDRESS], Charindex(' ',[ADDRESS] - 1)) as 'Address'
But i received an error:
Conversion failed when converting the nvarchar value '6501 Red Hook Place #201' to data type int.
We have a SSIS project where we load lot of image files using an OLE DB Source component and put it across the database as varbinary into OLEDB Destination component.
Things were fine until now where am getting an error like this. alongside the log also reads like,
There is not enough space on the disk. Long data was retrieved and can't be added to the data flow task buffer.
Is this method of loading the files using an OleDb Souce not the efficient one ? If not is there a way I can get this done. Comments are highly appreciated!!
I have the following query and where I need to use the t_PrevSession.DischargeTime which is in the nested query that is bolded below. How do i bring it up to the main select statement?
Using Visual Studio 2005 Prof and SQL Server everywhere.
How do get the identity column value after insert record.
With SQL Server 2005, its quite easy to get by creating and insert statement on the tabledapter ( Insert statement followed by a select statement where identitycolumn = scope_identity())
I am in process of transfering data from Sybase to Sql Server using SSIS 2005
have taken a Data Flow Task in Control Flow tab
In Data flow tab, I have taken one Ole DB Source and One OLe DB Destination
For the source, I am using Sybase Adaptive Server Anywhere Provider 8.0
For Destination, I am using Sql Server 2005 database
In Ole Db Source Editor , For OLe Db Connection Manager, I choose Sybase Connection For Data access mode, I choose Table or View For Name of the table or the view, I choose a table by name Table1( it lists all the tables from Sybase database)
When i click on preview button or Columns link, I get the following Error
Error at Data Flow Task [OLE DB Source [1]]: An OLE DB error has occurred. Error code: 0x80040E21.
Error at Data Flow Task [OLE DB Source [1]]: Unable to retrieve column information from the data source. Make sure your target table in the database is available.
I am trying to set up a data flow task. The source is "SQL Command" which is a stored procedure. The proc has a few temp tables that it outputs the final resultset from. When I hit preview in the ole db source editor, I see the right output. When I select the "Columns" tab on the right, the "Available External Column List" is empty. Why don't the column names appear? What is the work around to get the column mappings to work b/w source and destination in this scenario.
In DTS previously, you could "fool" the package by first compiling the stored procedure with hardcoded column names and dummy values, creating and saving the package and finally changing the procedure back to the actual output. As long as the columns remained the same, all would work. Thats not working for me in SSIS.
Below is the query in which i want to retrieve another column (exchange rate) from a particular date for the sub query.
Actually PurchaseOrderDet table get records related to purchaseorder but for each row in purchaseorderdet need info from the same table for all rows on a particular date.
how i can achieve this query without using RANK() and other functions.
"SELECT Supplier.Uniid AS SupplierID, Supplier.Name, PurchaseOrder.Uniid AS PurchaseID, PurchaseOrder.OrderNo, PurchaseOrder.FormDate, StockItem.Uniid AS StockID, StockItem.StockCode + N' - ' + StockItem.Description1 AS StockItem, PurchaseOrderDet.ExchangeRate, PurchaseOrderDet.UnitPrice, PurchaseOrderDet.Discount, SUM(PurchaseOrderDet.OrderQty) AS SumOfOrderQty,
Howdy folks! (I feel im creating too many topics...but all my questions are unrelated :-/)
Dev environment: SSCE, Native c++ OLE code, Win CE 5
I have a routine that calculates the size of a set of columns in order to create a byte buffer for retrieving data from an IRowset and inserting it into a custom class. This method works great for all data types except ntext. When I look at the column information from an ntext field, the DBCOLUMNINFO::ulColumnSize member holds 536,870,911 (the max length of an ntext type). Obviously I dont want to allocate 1GB of memory for each ntext column. Is there anyway (natively) to determine the actual number of characters in a field?
I want to retrieve SQL 2000 Encrypted Column Data From SQL 2005 strored proc. My Stored Procedure was on SQL 2000 and it works fine....Then I restore Database From SQL 2000 to SQL 2005. The Following Statement is on my store proce.
select user_id , Encrypt(user_pass) from OpenRowset('SQLOLEDB','myserver';'sa';'mypass',databasename.dbo.users) as a
The Following Error I get When I execute the above statement.
Msg 195, Level 15, State 10, Line 1
'Encrypt' is not a recognized built-in function name.
where the job_type columIn is of the char(1) type, NTBSTRINGBIND is the vartype argument in the dbbind() function.
However, what I have gotten is nothing more than a null string from the job_type column. I have alternatively changed the vartype argument to STRINGBIND, CHARBIND and even INTBIND, but the results are the same.
Who can tell me the tricks to retrieve a char(1) column from SQL server?
Hello, I am getting very frustrated! I have got a Foreach loop container which I am processing files within a folder. I have a flatfile connection manager which I have set up using a test file and have updated the expressions attribute to be the package variable I set up in the collection for the loop container however everytime I run it I get the error: 0xC0202094 cannot retrieve the column information from the flatfile connection manager. I can only guess that it is either the variable being passed to the connection manager or the way I set up the connection manager. When I msgbox the variable in a script component before the dataflow step, the variable for the file seems fine. Any suggestions are REALLY appreciated.
I've developed a package that is working well at development machine from VS 2013 for a sample flat file. Also, over development machine, I've deployed it to SSISDB catalogue and even from there also it is running well for the same file.When the same package is deployed to production server's SSISDB catalogue database, it throws following error while processing the same sample flat file, “Unable to retrieve column information from the flat file connection manager”
Greetings, I am developing a search form that uses a DDL to select a column name and a text box to define a search term. Code follows: 1 cmdSearch = New SqlCommand( "select * from sites where @columnName like @sTerm order by site_name", conSites) 2 3 cmdSearch.Parameters.Add( "@columnName", SqlDbType.Text).Value=ColumnParam 4 cmdSearch.Parameters.Add( "@sTerm", SqlDbType.Text).Value=searchParam The problem I'm running into is that the @variable appears to imply single quotes about the value. This is fine for @sTerm, I just removed the quotes from the concatenation and it reads the value correctly, but it appears to break @columnName and the query returns no values. If I manually substitute an appropriate column for @columnName sans single quotes, it works. How can I eliminate the implied single quotes from @columnName so that the query will read it properly?
I am feeding in @RsCategory varchar (25) into my stored procedure. I was hoping to make the RsCategory = the column name from the table I would like to select data from.
How can I use @RscCategory as the column name value in the select statement? (or can I?)
The problem is with @NUMERY parameterin code behind i setDim dr As GridViewRowDim numery As Stringnumery = "" For Each dr In GridView1.RowsDim numeros As Label = dr.Cells(0).Controls(1)numery += numeros.Text & ", "Next numery = numery.TrimEnd(", ") SqlDataSource1.UpdateParameters("NUMERY").DefaultValue = numery'so numery will look like this 123, 65465, 54616, 56465Update command looks like this :UpdateCommand="UPDATE slon SET mrowka = @MROWKA WHERE (NUMER IN (@NUMERY))" <UpdateParameters><asp:Parameter Name="MROWKA" /><asp:Parameter Name="NUMERY" /></UpdateParameters> And because of @numery i have err: Error converting data type nvarchar to numeric. how should i post "123, 65465, 54616, 56465" as parameter for this query ?
In a SPROC I am creating, is there a way to use a columnName as a parameter and then do a filter on that based on a second parameter such as @columnValue ?
So instead of having to construct the WHERE clause or doing a bunch of IF statements to see what the column name is from the parameter and doing a query based upon that, is there a way to tell it to do a WHERE clause where @columnName = @columnValue ?
I do not want to use dynamic SQL string concatenation...
