i using a bound data grid which is using a stored proc. The stored proc needs the ClientID "if logged in" there is no form or control on the page outside of the loginstatus. I am wanting to pass the Membership.GetUser.ProviderUserKey.ToString() to the asp:parameter but I cant get it to work.
So How do I pass a variable to a stored proc parameter using a bound data grid.
I this its very strange that this cant be dont and there are a raft of reason why you wold want to do this with out the need to pass it to a form control.
please help
jim
I will need some examples in assigning and getting values using SQLServer 2005. For eg. How can I store the value that I retrieved in a variable and return that value ? How can I use a function inside a stored procedure ? Do we have any examples or some simple sample code just to take a look ?
For eg I have written the following function which I called from a stored procedure. BEGIN --Declare the return variable here DECLARE @Rows NUMERIC(10) DECLARE @RETURN_ENABLED VARCHAR(1) -- Add the T-SQL statements to compute the return value here
SELECT @Rows = MAX(PROFILE_INDEX) FROM PROFILE_PERMISSION PP INNER JOIN sys_menu_item ON PP.MENU_ITEM=sys_menu_item.menu_item WHERE PP.PROFILE_INDEX in (select up.profile_index from user_profile up where up.user_id= @is_user) and not exists (select up.profile_index from user_profile up where up.user_id= @is_user and up.profile_index=1) and PP.APPLICATION_CODE = @is_appl AND PP.MENU_NAME=@menu_name Group By Profile_INdex
-- Return the result of the function RETURN @RETURN_ENABLED;
END
Is it correct ? The variable @ROWS will be assigned with the values that the sql statement will return ?
From the stored procedure I'm calling the function inside a CTE.
;WITH GetHierarchy (item_text ,orden , read_order, item_parent , menu_item , enabled) AS (--Anchor. select tb1.item_text, tb1.orden, tb1.read_order, tb1.item_parent , tb1.menu_item , dbo.f_sty_print_menu_per_role_per_app2(@menu_name , @is_user , @is_appl) as enabled From sys_menu_item as tb1 where tb1.MENU_ITEM not in ('m_window','m_help','m_toolbar') and tb1.item_parent not in ('m_toolbar','m_window','m_help') And tb1.item_parent= @menu_name --Members UNION ALL select tb2.item_text, tb2.orden, tb2.read_order, tb2.item_parent , tb2.menu_item , dbo.f_sty_print_menu_per_role_per_app2(@menu_name , @is_user , @is_appl) as enabled from sys_menu_item as tb2 , GetHierarchy where tb2.MENU_ITEM not in ('m_window','m_help','m_toolbar') and tb2.item_parent not in ('m_toolbar','m_window','m_help') And tb2.item_parent = GetHierarchy.menu_item and tb2.menu_name = @menu_name ) select Space(5*(orden)) + item_text as menui, orden, read_order, item_parent , menu_item ,enabled From GetHierarchy
I have variables and values stored in a table in this format
process_id | t_variable | t_value ----------------------------------------------------- 1 | Remote_Log_Server | AUSCPSQL01 ... many such rows
how to assign values to variables in SSIS?
basically i'm looking for SQL equivalent of the following query i currently use to assign values to multiple variables (in a single query)
SELECT @varRemoteLogServer=MAX(CASE WHEN [t_variable] = 'Remote_Log_Server' THEN [t_value] END) ,@varVariable2=MAX(CASE WHEN [t_variable] = 'variable2_name' THEN [t_value] END) FROM Ref_Table WHERE process_id=1
what other method can you use to generate primary keys automatically. My boss disagrees with me using identity for the primary keys. He says it is not professional. I am working on SQL Server 2005 platform. Can anybody advice me?
I need some help regarding a conversion in a Script Task.
I am reading my variable values from a database with a sql task, the table has two columns, variable and variableValue. Looping through the recordset and setting the different variables works well, with two links: http://blogs.conchango.com/jamiethomson/archive/2005/02/09/SSIS_3A00_-Writing-to-a-variable-from-a-script-task.aspx http://sqlblog.com/blogs/andy_leonard/archive/2007/10/14/ssis-design-pattern-read-a-dataset-from-variable-in-a-script-task.aspx
setting the variable value only works well if the package variable is defined as string, because the db field is a varchar, trying to assign an integer for example brings up an error.
Therefor I tried something like CType: Dts.Variables("MyVar").Value = CType(MyRecordsetField,String), where the target datatype should be depending on the variable datatype instead of being assigned as a constant.
I have a script that I use after some amount of data massaging (not shown). I would like to be able to change the
1) denominator value (the value 8 in line 32 of my code) based on how many columns are selected by the where clause:
where left(CapNumber,charindex('_', CapNumber)-1) = 1where capNumber is a value like [1_1], [1_4], [1_6]...[1_9] capNumber can be any values from [1_1]...[14_10] depending upon the specialty value (example: Allergy) and the final number after the equal sign is a number from 1 to 14)
2) I'd like to dynamically determine the series depending upon which values correspond to the specialty and run for each where: left(CapNumber,charindex('_', CapNumber)-1) = n. n is a number between 1 and 14.
3) finally I'd like to dynamically determine the columns in line 31 (4th line from the bottom)
If I do it by hand it's 23 * 14 separate runs to get separate results for each CapNumber series within specialty. The capNumber series is like [1_1], [1_2], [1_3],[1_4], [1_5], [1_6], [1_7], [1_8],[1_9] ... [8_4],[8_7] ... [14_1], [14_2],...[14_10] etc.
Again, the series are usually discontinuous and specific to each specialty.
Here's the portion of the script (it's at the end) that I'm talking about:
--change values in square brackets below for each specialty as needed and change the denom number in the very last query.
if object_id('tempdb..#tempAllergy') is not null drop table #tempAllergy select * into #tempAllergy from dbo.#temp2 T
[Code] ....
If I were to do it manually I'd uncomment each series line in turn and comment the one I just ran.
Hi, figured out where I was going wrong in my post just prior, but isthere ANY way I can assign several variables to then use them in anUpdate statement, for example (this does not work):ALTER PROCEDURE dbo.UpdateXmlWF(@varWO varchar(50))ASDECLARE @varCust VARCHAR(50)SELECT @varCust = (SELECT Customer FROM tblWorkOrdersWHERE WorkOrder=@varWO)DECLARE @varAssy VARCHAR(50)SELECT @varAssy=(SELECT Assy FROM tblWorkOrdersWHERE WorkOrder=@varWO)UPDATE statement here using declared variables...I can set one @variable but not multiple. Any clues? kinda new tothis.Thanks,Kathy
I have a subtotal that I want to compare to a value to determine the color property that it will appear on the report in - how can I do this. Essentially, if the department is overbudget the value should be red, if the department is within budget the value should be black.......
what is the correct syntex to do this......
if (Sum(Fields!Value.Value) > (fields!budget.value) then
I have a requirement to load tables in the database from files on a shared server (lets say 50 tables from 50 files). I do not want to hardocde the file path anywhere in the package since this would mean changing 50 packages everytime the path changes (say when moving to to a diff server).
In SQL Server 2000, I used a .ini file to pass the path and used a Dynamic properties task to set run time variables. That way, evertime the path changes, I only had to change 1 ini file and all packages picked up the new path from it. How do I do this in SQL Server 2005 ?
Appreciate any assistance / suggestion in this regard.
I have a requirement to load tables in the database from files on a shared server (lets say 50 tables from 50 files). I do not want to hardocde the file path anywhere in the package since this would mean changing 50 packages everytime the path changes (say when moving to to a diff server).
In SQL Server 2000, I used a .ini file to pass the path and used a Dynamic properties task to set run time variables. That way, evertime the path changes, I only had to change 1 ini file and all packages picked up the new path from it. How do I do this in SQL Server 2005 ?
Appreciate any assistance / suggestion in this regard.
CancelSelectOnNullParameterHi,Suppose I have a SQLDataSource with a SelectCommand="SELECT * FROM STOCKS WHERE USER=@USER ORDER BY STOCK_NAME" and @USER is set as a session parameter. No problem till this part, but in some postbacks, I wish to add a new WHERE criteria to my SQL, so I do:DSSQL.SelectParameters.Add("@STOCK_NAME", "ABC123"); DSSQL.SelectCommand = DSgvStoklar.SelectCommand.Replace(" ORDER BY ", " AND STOCK_NAME LIKE @STOCK_NAME+'%' ORDER BY "); gridViewDS.DataBind(); and wish that my SelectCommand is modified and a new parameter is added. The SQL is modified, however -I guess- new parameter is not added, so I get "Must declare the scalar variable "@STOCK_NAME" error. How can I solve, any ideas?
I am creating the report and setting the datasource(dataset) dynamically to the report. I want to set the parameters also dynamically. am using Report Viewer to process the report.
When i set the parameters locally, i am getting the below error: An error occurred during local report processing
Here is my code: da = new SqlDataAdapter(strqry, conn); ds = new DataSet(); da.Fill(ds);
ReportDataSource ReportDataSourceX = new ReportDataSource(); ReportDataSourceX.Value = ds.Tables[0]; ReportParameter[] parm = new ReportParameter[2];
parm[0] = new ReportParameter("Business_Function", "SQMO"); parm[1] = new ReportParameter("Application", "ETMRS"); parm[0] = new ReportParameter("Owner", "Subbu"); //parm[0] = new ReportParameter("Business_Function", ds.Tables[0].Columns["Business_Function"].ToString()); //parm[1] = new ReportParameter("Application", ds.Tables[0].Columns["Application"].ToString()); //parm[0] = new ReportParameter("Owner", ds.Tables[0].Columns["Owner"].ToString());
All of these dates are based on the update of our data warehouse. This stored procedure runs a 5 step process and produces data for 8 - 10 monthly reports.
I was wondering if these variables can be updated dynamically and if they can how it is done.
I am SSRS user, We have a .net UI from where we want to pass multi select values, but these values are comma separated in the database. how can I write a sql query such that when I select multi values on my UI, the comma separated values are take care of.
Hello all, Ok, I finally got my SqlDataSource working with Oracle once I found out what Oracle was looking for. My next hurdle is to try and set the Update Command and Parameters dynamically from a variable or radiobutton list. What I'm trying to accomplish is creating a hardware database for our computers by querying WMI and sending the info to textboxes for insertion and updating. I got that part all working nicely. Now I want to send the Computer name info to a different table column depending on if it is a laptop or desktop. I have been tossing around 2 ideas. A radiobutton list to select what it is and change the SQL parameters or do it by computer name since we have a unique identifier as the first letter ("W" for workstation, "L" for Laptop). I'm not sure what would be easiest but I'm still stuck on how this can be done. I posted this same question in here a few days ago, but I didn't have my SqlDataSources setup like I do now, I was using Dreamweaver 8, it is now ported to VS 2005. Below is my code, in bold is what I think needs to be changed dynamically, basically i need to change DESKTOP to LAPTOP...Thanks for all the help I've gotten from this forum already, I'm very new to ASP.NET and I couldn't do this without all the help. Thanks again!
Hi all I hope someone can help me on this issue: I am designing dynamical reports, that are abel to run in four different languages - so far everything works just fine for me (I select my headers and labels from a database). Only problem is the prompt text for report parameters. How can I set this text dynamically?
E.g. I have a parameter where the user selects a storage - this prompt should be "Storage: " in the English report and "Lager: " in the Danish report. I have absolutely no idea how to fix this.
A have a multi-valued parameter (B) which is dependent on a single-valued parameter (A) on my report. When a value is selected in A, I want all matching values in B to be selected by default and the "Select All" option checked. To do this I have set the Default Values section in B to point to the same dataset as the "Available Values" section. Both A and B have default values so the report runs automatically.
One of the values in parameter A (say Value1) yields more values in parameter B than the other (say Value2).
If I run the report the first time with Value1 selected as the default for parameter A, all values in B are checked correctly. If I run the report with Value2 selected the first time and then change the selected value to Value2 and run my report, all values in B are displayed but only the values that were previously checked (when Value1 was selected), are now checked, leaving the "Select All" unchecked.
What am I doing wrong? Why are all the values in B not checked? The dataset is the same in "Available Values" section and "Default Values" section.
I have the following ASP code that builds part of the example SQL statement below (it's the same SQL as in my earlier thread here (http://www.dbforums.com/showthread.php?t=1214044) but a very different question):
if sFindTicketEventId > 0 then sSQL = sSQL & " AND [tblEvents].[id]=" & sFindTicketEventId if sFindTicketStandId > 0 then sSQL = sSQL & " AND [tblStands].[id]=" & sFindTicketStandId
SELECT [tblC].[id] AS CombinationID, [tblC].[availability], [tblC].[description], [tblC].[price] AS combinationPrice, [tblC].[combination_open], [tblT].[TicketID] AS TicketID, [tblT].[price] AS ticketPrice, [tblT].[availability], [tblT].[ticket_open], [tblT].[quantity], [tblT].[event_name], [tblT].[event_open], [tblT].[stand_name], [tblT].[stand_open], [tblT].[admission_start_date], [tblT].[admission_end_date], [tblT].[date_open], [tblT]., [tblT]., [tblT2].[description], [tblT2].[admin_description] FROM( SELECT [tblCombinations].[id], [tblTickets].[id] As TicketID, [tblTickets].[price], [tblTickets].[availability], [tblTickets].[ticket_open], [tblCombinations_Tickets].[quantity], [tblEvents].[event_name], [tblEvents].[event_open], [tblStands].[stand_name], [tblStands].[stand_open], [tblAdmissionDates].[admission_start_date], [tblAdmissionDates].[admission_end_date], [tblAdmissionDates].[date_open], [tblBookingDates].[booking_start_date], [tblBookingDates].[booking_end_date] FROM [tblCombinations] LEFT JOIN [tblCombinations_Tickets] ON [tblCombinations_Tickets].[combination_id] = [tblCombinations].[id] LEFT JOIN [tblTickets] ON [tblCombinations_Tickets].[ticket_id] = [tblTickets].[id] LEFT JOIN [tblEvents] ON [tblEvents].[id] = [tblTickets].[event_id] LEFT JOIN [tblStands] ON [tblStands].[id] = [tblTickets].[stand_id] LEFT JOIN [tblAdmissionDates] ON [tblAdmissionDates].[id] = [tblTickets].[admission_date_id] LEFT JOIN [tblBookingDates] ON [tblBookingDates].[id] = [tblTickets].[booking_date_id] LEFT JOIN [tblTicketConcessions] ON [tblTicketConcessions].[id] = [tblTickets].[ticket_concession_id] LEFT JOIN [tblBookingQuantities] AS [tblBookingMinQuantities] ON [tblBookingMinQuantities].[id] = [tblTickets].[booking_min_quantity_id] LEFT JOIN [tblBookingQuantities] AS [tblBookingMaxQuantities] ON [tblBookingMaxQuantities].[id] = [tblTickets].[booking_max_quantity_id] LEFT JOIN [tblMemberships] ON [tblMemberships].[id] = [tblTickets].[membership_id] WHERE 1=1 [B]AND [tblEvents].[id]=2 [B]AND [tblStands].[id]=3 --AND [tblAdmissionDates].[id]=@admissionDateId --AND [tblBookingDates].[id]=@bookingDateId --AND [tblTicketConcessions].[id]=@concessionId --AND [tblBookingMinQuantities].[id]=@bookingMinQuantityId --AND [tblBookingMaxQuantities].[id]=@bookingMaxQuantityId --AND [tblMemberships].[id]=@membershipId GROUP BY [tblCombinations].[id], [tblTickets].[id], [tblTickets].[price], [tblTickets].[availability], [tblTickets].[ticket_open], [tblCombinations_Tickets].[quantity], [tblEvents].[event_name], [tblEvents].[event_open], [tblStands].[stand_name], [tblStands].[stand_open], [tblAdmissionDates].[admission_start_date], [tblAdmissionDates].[admission_end_date], [tblAdmissionDates].[date_open], [tblBookingDates].[booking_start_date], [tblBookingDates].[booking_end_date] ) as [tblT] JOIN [tblCombinations] as [tblC] on [tblT].[id]=[tblC].[id] LEFT JOIN [tblTickets] as [tblT2] on [tblT].[TicketID]=[tblT2].[id]
I want to turn this SQL into a stored proc; there are currently about 8 parameters that I want to pass into it. The field value for each will be either NULL or a positive integer, and the paramater will be passed in as an integer.
If the passed parameter value is a positive integer then it should return all records where the corresponding field value matches that integer. If the passed parameter is 0, it should return all rows regardless of whether the field value is an integer or NULL.
And I can't for the life of me figure out how to do it. Do I need an IF statement in there or something?
I have three databases which have the identical data structure. When the user runs a report, he has to select a database. For example, my report query would look like:
Select * from <theDatabase>.dbo.MyTable
How could I assign the value of <theDatabase> at run time depending on what the user selects from the list? In my datasource, I have only specified the server name and no value for initial catalog.
I am creating simple report in ssrs and pass one parameter only. It will work perfectly (here user enter the parameter value). but i need that i should select the value in drop down box. i had tried many time and did different ways but I am unable to do it.
First i gave the parameter in my sql query in Data set (like WHERE COUNTRY = @COUNTRY) and i checked the Parameters tab in the data set. Here by default comes the Parameter Name: COUNTRY and Parameter value: [@COUNTRY].
Next i select COUNTRY Parameter in the Report Data Pane. and go to properties Here in General Tab: Name COUNTRY Prompt: COUNTRY, Select Get values from query in available values Tab (and also i tried with Select Get values from query in Default Value Tab) and Select Data set: Data Set1, Value field: COUNTRY and Label Field COUNTRY. And Click Ok
And tried to preview the report, it throwing below error
"An error Occured during local report processing. The definition of the report is invalid. The Report Parameter 'COUNTRY' has a DefaultValue or a ValidValue that depends on the report parameter "COUNTRY". Forward dependencies are not valid.
How can I achieve dropdown list.What i missed? Even i unable to do it Multi valued parameters and Cascading parameters.
Actually i am working on SQL Express 2012 version.
Hello all, I was wondering if anyone knew of a way to dynamically delete all of the values for a group of columns. What I mean by this is that lets say a table (TableA) has five fields (Field1, Field2, Field3, Field4, and Field5) with 100 rows of data. I want to delete all of the data in Field1, Field2, and Field4. I do not want to delete any of the data in Field3 and Field5. I would then end up with a table with 5 fields and 100 row, but only 2 fields (Field3 and Field5) have data.
The catch is that I can't hardcode the field names of the fields I want to clear out (Field1, Field2, and Field4) into the SQL. This is because if any new fields are eventually added to the table I want them to be cleared out as well without modifying the SQL.
I can hardcode the field names of the fields that I want to keep values for (Field3 and Field5) in the SQL.
If anyone has any idea how to do this, I would greatly appreciate it.
I have created dynamic sql to declare variables based on columns from the table and i set values to those variable now here is the issue . i want to check the variable values how do i do that dynamically
drop table test create table test ( id varchar(10) not null, col1 varchar(10) , col2 varchar(10)
[Code] .....
Now my next step is verify if the variable is blank or not how do i do that ?
How do i verify all of the columns one after the other .
I am after the statement like this dynamically
-- IF NOT (@col1 = '') THEN set @SQL = @SQL + '[col1] = ' + @col1 + ' ' --IF NOT (@col2 = '') THEN set @SQL = @SQL + '[col2] = ' + @col2 + ' '
I need to check if the columns are blank or not dynamically as i do not want to hard code the column names there.
I have developed an SSIS package which extracts and creates 5 flat files and finally using Process Extraction task zip the folder. On my Dev environment everything is working fine but when I am moving to SIT and UAT, not able to set up jobs dynamically by importing XMLConfig file.I created variables and assigned values but still it doesnt take.Below are varaibles I created for flat file destination, Arguments and Working Directory (for zipping)On UAT when I go to SQLAgentJobs to set, import .dtsx file, XML config file....the new values doesnt appear. why ?DataSource is taking always dev location....why ? How can I set it up to take dynamic values what I mentioned in config file ?
