insert into #t(branchnumber) values (005) insert into #t(branchnumber) values (090) insert into #t(branchnumber) values (115) insert into #t(branchnumber) values (210) insert into #t(branchnumber) values (216)
[code]....
I have a parameter which should take multiple values into it and pass that to the code that i use. For, this i created a parameter and temporarily for testing i am passing some values into it.Using a dynamic SQL i am converting multiple values into multiple records as rows into another variable (called @QUERY). My question is, how to insert the values from variable into a table (table variable or temp table or CTE).OR Is there any way to parse the multiple values into a table. like if we pass multiple values into a parameter. those should go into a table as rows.
I have 2 identical tables one contains current settings, the other contains all historical settings.I could create a union view to display the current values from table A and all historical values from table B, butthat would also require a Variable to hold the tblid for both select statements.
Q. Can this be done with one joined or conditional select statement?
DECLARE @tblid int = 501 SELECT 1,2,3,4,'CurrentSetting' FROM TableA ta WHERE tblid = @tblid UNION SELECT 1,2,3,4,'PreviosSetting' FROM Tableb tb WHERE tblid = @tblid
I have a page that is displaying different movie covers. They each have their respective genres stored in several keys (genre0, genre1, genre2). Now I can search within the movies by changing the url variable movies.aspx?genre=action but if I simply go to movies.aspx, I'm wanting all genres to appear. But for me, the page is blank if I don't specify the genre. So I've had to start filling in the genre All for all movies so I can go to movies.aspx?genre=all but I know this can't be necessary. Is it my SQL statement for the datasource causing the problem? Code is in VB. <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="Data Source=xxx;Initial Catalog=xxx;User ID=xxx;Password=xxx" ProviderName="System.Data.SqlClient" SelectCommand="SELECT * FROM [Movies] WHERE [genre0] = @strGenre OR [genre1] = @strGenre OR [genre2] = @strGenre"> <SelectParameters> <asp:QueryStringParameter Name="strGenre" QueryStringField="genre" /> </SelectParameters> </asp:SqlDataSource>
I have the below data with two columns (Posting Date and Pond Crop, construct an SQL where i will pick and display only the TOP 3 Posting date of EACH Pond Crop. I only attached less data but in reality there are lots of Ponds and posting date in this table.
Raw Data Posting Date PondCrop 2011-11-21 00:00:00.00001PA01-15 2011-11-28 00:00:00.00001PA01-15 2011-12-02 00:00:00.00001PA01-15 2011-12-05 00:00:00.00001PA01-15
In Table Contains 6 rows with 2 order_numbers and different PSON Number.I want Order Number,PSON1,PSON2,PSON3,PSON4 and its Values
Ex:--Order_number   PSON NUmber        1           ONT10        1           ONT11        1          ONT12        2          ONT13         2          ONT14
I want  data :-    Order_number   PSON1      PSON2     PSON3                  1           ONT10    ONT11    ONT12                   2            ONT13   ONT14    Null
I have created different tables in my database with descriptions in from other tables (example : a table named errors with errorid and errordescription as fields in it)
In a report i want to display the total nr of errors during a certain period for a certain department.
i have created that report with a list (by department). in this list i have a table where i group my errors.
until here all ok. when i display my report it shows all the errors.
but what it doesn't show is all the errors who have value 0 (or errors that didn't occur during that period i defined)
how can i display all my errors, even if they did not occur (0 times)
Date                             ID            Message 2015-05-29 7:00:00     AOOze           abc 2015-05-29 7:05:00     AOOze           start 2015-05-29 7:10:00     AOOze           pqy 2015-05-29 7:15:00     AOOze           stop 2015-05-29 7:20:00     AOOze           lmn    Â
[code]....
and so on following the series for every set of different ID with 5 entries.I need to Find Maximum interval time for each ID and for condition in given message (between Start and Stop)For example, in above table
-For ID AOOze, in message "start" is logged at 7:05 and stop and 7:15, so interval is 10 mins -For ID LaOze, in message "start" is logged at 7:30 and stop and 7:45, so interval is 15 mins
I am looking for a sql query that will return in below format
ok, I am on Day 2 of being brain dead.I have a database with a table with 2 varchar(25) columns I have a btton click event that gets the value of the userName, and a text box.I NEED to insert a new row in a sql database, with the 2 variables.Ive used a sqldatasource object, and tried to midify the insert parameters, tried to set it at the button click event, and NOTHING is working. Anyone have a good source for sql 101/ASP.Net/Braindead where I can find this out, or better yet, give me an example. this is what I got <%@ Page Language="C#" %><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><script runat="server"> protected void runit_Click(object sender, EventArgs e) { //SqlDataSource ID = "InsertExtraInfo".Insert(); //SqlDataSource1.Insert(); } protected void Button1_Click1(object sender, EventArgs e) { SqlDataSource newsql; newsql.InsertParameters.Add("@name", "Dan"); newsql.InsertParameters.Add("@color", "rose"); String t_c = "purple"; string tempname = Page.User.Identity.Name; Label1.Text = tempname; Label2.Text = t_c; newsql.Insert(); }</script><html xmlns="http://www.w3.org/1999/xhtml" ><head runat="server"> <title>mini update</title></head><body> <form id="form1" runat="server"> name<asp:TextBox ID="name" runat="server" OnTextChanged="TextBox2_TextChanged"></asp:TextBox><br /> color <asp:TextBox ID="color" runat="server"></asp:TextBox><br /> <br /> <asp:Button ID="Button1" runat="server" OnClick="Button1_Click1" Text="Button" /> <br /> set lable =><asp:Label ID="Label1" runat="server" Text="Label" Width="135px" Visible="False"></asp:Label><br /> Lable 2 => <asp:Label ID="Label2" runat="server" Text="Label"></asp:Label><br /> Usernmae=><asp:LoginName ID="LoginName1" runat="server" /> <br /> <br /> <br /> <br /> <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConflictDetection="CompareAllValues" ConnectionString="<%$ ConnectionStrings:newstring %>" DeleteCommand="DELETE FROM [favcolor] WHERE [name] = @original_name AND [color] = @original_color" InsertCommand="INSERT INTO [favcolor] ([name], [color]) VALUES (@name, @color)" OldValuesParameterFormatString="original_{0}" SelectCommand="SELECT [name], [color] FROM [favcolor]" UpdateCommand="UPDATE [favcolor] SET [color] = @color WHERE [name] = @original_name AND [color] = @original_color"> <DeleteParameters> <asp:Parameter Name="original_name" Type="String" /> <asp:Parameter Name="original_color" Type="String" /> </DeleteParameters> <UpdateParameters> <asp:Parameter Name="color" Type="String" /> <asp:Parameter Name="original_name" Type="String" /> <asp:Parameter Name="original_color" Type="String" /> </UpdateParameters> <InsertParameters> <asp:InsertParameter("@name", "Dan", Type="String" /> <asp:InsertParameter("@color", "rose") Type="String"/> </InsertParameters> </asp:SqlDataSource> <asp:GridView ID="GridView1" runat="server" AllowPaging="True" AllowSorting="True" AutoGenerateColumns="False" DataKeyNames="name" DataSourceID="SqlDataSource1"> <Columns> <asp:CommandField ShowDeleteButton="True" ShowEditButton="True" ShowSelectButton="True" /> <asp:BoundField DataField="color" HeaderText="color" SortExpression="color" /> <asp:BoundField DataField="name" HeaderText="name" ReadOnly="True" SortExpression="name" /> </Columns> </asp:GridView> </form></body></html>
Does somebody know how do you get a column in Reporting Services to display the values from two different columns? I don't mean a concat, but the result of the two columns.
I have a report I'm writing and have got a problem that has stumped me.
The data the report is based in is in this format:
Date Type
1/1/08 A
1/3/08 B
3/1/08 C
3/5/08 B
3/10/08 A
3/12/08 C
3/20/08 A
Management wants a report showing a line chart that summarizes the data by Month and by Type. So, there are 2 series depending on the Type and data values are based on the count of each Type. So, in the example above the x-axis group would be month, the values would be count(Type) and there would be 2 series €“ Series 1. A, B and Series 2. C
I€™ve defined the x-axis to be Month(Fields!DateOccured.Value). However, they want the x-axis to show every month for the entire year not just what is in the database. So, I defined the x-axis to have a scale of 1 to 12 and the major interval is set to 1. This displays 1 through 12 on the x-axis.
The problem is when there is no data (null) for a particular month. Instead of showing 0, the line chart does not plot anything and the line is drawn to the next displayed point. So, for example on the data above the line chart would plot:
January
Series 1 €“ Qty. 2 Series 2 €“ Qty. 0
February
Null
March
Series 1 €“ Qty. 3 Series 2 €“ Qty. 2
The line chart would draw a line from January to March skipping February. I need to display the null values as 0 and not null indicating no occurrences for the month rather than no data present.
I need to display the 'Flag' column in the result set as mentioned above to identify the cycle values without breakdown indicator values. Here is the logic for this,
1. Need to set the flag 'Yes' for the cycle values PM04 to PM04 in the OrderType and if there is any breakdownindicator value 'X' then we can not consider that cycle for Flag.
hi friends i am using visual studio 2005. i need one combo box which will display two values. one is "Designation" i.e.("clerk,accountant,manager,officer,shopkeeper) and another "Designation ID" i.e.(1,2,3,4,5 and respectively) i made table of "Designation Master" in sql server 2000 including field of "Designation ID, Designation". manually i am inserting id and designation in that table. what i want exactly i have one customer form which contain a "combobox" for "Designation" column i achieved "Designation ID" and "Designation" in that combo box also but when i will go for save i want that i will store only "Designation ID" i don't want to store "Designation" in my "Designation" column which is in "Customer table" i am attaching code which store "Designation ID" and "Designation" in customer table but i want only "Designation ID" only bold line which is difficult for me to inserting only "designationId" in customer table.
Inventory_Master.cntData.Open()
cmdstr = "Select DesigID,Designation from Designation_Master"
cmd = New SqlCommand(cmdstr, Inventory_Master.cntData)
I am stuck on a query where I need to display all the month year values even if the corresponding count_booking values are NULL. The requirement is to display the 13 month year period from current date.
For e.g. if the date exists in the current month then starting from May 15 go all the way back to Apr 14.
My current query works in terms of displaying all the 13 months if the count_booking values exist for all the months. However, if some months are missing the values then those months don't show up.how to display all the months even if the values are NULL ? Query below:
SELECT COUNT(m.BOOKING_ID) AS count_booking, LEFT(DATENAME(MONTH, m.CREATE_DT), 3) + ' ' + RIGHT('00' + CAST(YEAR(m.CREATE_DT) AS VARCHAR), 2) AS month_name FROM MG_BOOKING AS m WHERE (m.CREATE_DT >= DATEADD(m, - 13, GETDATE()))
I have a table with a column AttributeNumber and a column AttributeValue. The data is like this:
OrderNo. AttributeNumber AttributeValue 1.-Order_1 2001 A 2.-Order_1 2002 B 3.-Order_1 2003 C 4.-Order_2 2001 A 5.-Order_2 2002 B 6.-Order_2 2003 C
So the logic is as follows:
I need to display in my query the values are coming from Order_1, means AttributreValues coming from AttibuteNumbers: 2001,2002,2003...and Order_2 the same thing.
Not sure how to create my Select here since the values are in the same table
I am new to SSRS. I created a reporting services with 3 groups. I would like to know how to create different colors for each group so that all my values displayed by color group ?.
In my report, I need to display column names (and its values) from my dataset, which uses the stored procedure and within it use the PIVOT statement. I cannot use the matrix control in report since I need to add another static column to the right of the matrix, but since that cannot be done ni SQL SSRS 2005, I need to PIVOT it through stored procedure. The parameter passed to stored procedure is the year and it is used for pivot. The statement looks like this:
@sql = 'SELECT * FROM (SELECT YearId, SchoolId, ReportText, OutputValue_Text from AggregateTable) AS AGAM PIVOT (MIN(OutputValue_Text) FOR YearId IN ([' + @YearId + '])) PVT'
EXEC(@sql)
So, since the years passed as parameters can change, I cannot hard-code it as column in my report, so I need to programatically display column names and values from my dataset, something like dataSet.Tables[0].Columns[].ColumnName; I have tried with writing the code, but I do not have much experience with it and I have not been very successful. Can someone help me with the code?
I have a table that has product name and submissionID and some other columns. I am really concerned about these two columns. My task is to get all the submissionIDs for a particular product name and display SubmissionIDs seperated by commas against each product name . The tables below might give a better idea current scenario: Product Name SubmissionID columnC Column D AAA 123 AAA 456 BBB 111 ccc 121 AAA 789 Expected result: Product Name SubmissionID columnC Column D AAA 123,456,789 BBB 111 CCC 121 Any suggestions/pointers would be highly appreciated.
I have a power pivot with 2 multi valued report filters  student_branch & blood_group. These report filters are used to fetch the data set that contain below result set
student_branch        blood_group      count
Everything works fine. But, what i am looking for , is there any way to show the what are all the report filters that are selected currently by , separated in a separate cell ? below is the image for output reference.
Is it possible to save variable values to the log file? I have a for each loop that loops through customer orders and I would like to know the order number when the package fails.
I have a package which contains a variable with the value of a UNC sharename. The hostname in the share contains dashes.
I am trying to create a SQL Agent entry to execute said package. Because the package is now in production I want to pass in a new value for the sharename variable. I use the SQL @@SERVERNAME variable to build the package command. (Notice the BlotterUploadFolder variable at the end.)
If I print the @command variable right after setting it, I get what I expect.
/FILE "\PRD-NY-DB-01PackagesMiddleOfficeReconcilliationImportManualBlotter_AccountTransfers.dtsx" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EW /CONNECTION MiddleOfficeDb;"Data Source=PRD-NY-DB-01;Initial Catalog=MiddleOffice;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;" /SET package.variables[BlotterUploadFolder].Value;\PRD-NY-DB-01ManualBlottersUploads
If I cut-paste this into cmd window and execute it with DTExec.exe the package works fine. But when I run the schedule task it causes an error. When I go into the Job Properties dialog to look at the step, I see the value (in the "Set values" table) set to \PRD instead of \PRD-NY-DB-01.
If I modify the SELECT command above to put double-quotes around the value, the value is correct in the Job Properties dialog but causes an error when I exec it with DTExec.
/FILE "\PRD-NY-DB-01PackagesMiddleOfficeReconcilliationImportManualBlotter_AccountTransfers.dtsx" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EW /CONNECTION MiddleOfficeDb;"Data Source=PRD-NY-DB-01;Initial Catalog=MiddleOffice;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;" /SET package.variables[BlotterUploadFolder].Value;"\PRD-NY-DB-01ManualBlottersUploads"
causes the error
Option "-NY" is not valid.
Hopefully somebody has an idea of what is going on.
How can I populate values for package variables inside a data flow task? I could do this inside a script task in control tab, but how inside a data flow task?
As there is no support of check points at data flow component level, we are trying to implement this on our own. In the process of implementing we have to save values of certain user defined variables in to an xml file. To do this, we added a data flow task in the event handler OnTaskFailure. Now we want to save values of the variables in to an xml file in the data flow. Can you please suggest me how can we save values of variables in an event handler?