I have an SSIS package, run by the DTExec utility, each night. When I run my package, I assign a value to a variable (package scope) (/SET "Package.Variables[User:: psRunNo]";0154). (Note: all the variables here are strings).
Value for variable psRunNo = 0154
In the package, I have another package variable called S_SOURCE_FILE_NAME, which is an expression and that uses the variable psRunNo.
Expression for S_SOURCE_FILE_NAME = @[User:: psRunNo] + "_{TABLENAME}.txt"
So, at the beginning, the value of variable S_SOURCE_FILE_NAME is 0154_{TABLENAME}.txt with that example.
Then, I use the variable S_SOURCE_FILE_NAME in the expression used to assign the ConnectionString property of a Connection (a source file), like this:
So here for example, the final value for ConnectionString would be 0154_STADDRES.txt
Everything works nice, most of the time. Sometimes (intermittent problem), the value of the ConnectionString for one of the many Connections I have in the package is not assigned with the right value of psRunNo. The ConnectionString gets the value of psRunNo which is saved into the package (when it was deployed) instead of the value of psRunNo passed with the DTExec.
It is like if the ConnectionString value was computed before the variable psRunNo (and S_SOURCE_FILE_NAME expression) was assigned to the new value, but only for one of the connections (all my connections use the same kind of expression for their ConnectionString property).
Does somebody had similar precedence problem? Is there some settings I could use to indicate a precedence in assignation of variables ? Where does the "natural" precedence in assignation comes from in SSIS ??
I'm using a cursor in SQL Server 2000 to assist me in calculating for each store, the Sales Rank of a zip code. There are about 1500 stores, and 125,000 store/sales/zip code records.
I am finding that this works for about 95% of the stores, but about 5% are getting fouled up, where the store's records are getting split in the sequencing, and so the store ends up with two zips ranked 1, two ranked 2, two ranked 3, etc.
In the DB structure, there is a constraint restricting one record per store (org_id) per zip code (postalcode).
Here's my code. Basically what I'm trying to have the cursor do is go through the table, ordered by org_id (store) asc, org_criteria_value (sales) desc, and rank the zip codes. When a new store is encountered, reset the counter to 1 and start ranking again. Do this until all the records are processed.
insert into Sales_Table select 0 as count_id, omd.org_id, omd.postalcode, omd.org_criteria_value, 0 as cum_rank, org_criteria_input_date fromorg_model_data omd join org o on o.org_id = omd.org_id where o.client_id = @ClientID and model_Criteria_id = 27 and org_criteria_value <> 0 order by omd.org_id asc, omd.org_criteria_value desc
-- DECLARE CURSOR for Sales_Table
declare SalesRankCursor CURSOR SCROLL dynamic FOR select org_id, sales, sales_rank from Sales_Table for update of sales_rank
-- CREATE LOOP TO UPDATE SALES RANK in Sales_Table with valid values
OPEN SalesRankCursor
while exists (Select * from Sales_Table where sales_rank = 0) Begin
FETCH NEXT FROM SalesRankCursor
set @StoreNext = @StoreCurrent set @SalesRank = (@SalesRank + 1)
update Sales_Table set@StoreCurrent = org_id where current of SalesRankCursor
if @StoreCurrent <> @StoreNext begin set @SalesRank = 1 end
update Sales_Table setsales_rank = @SalesRank where current of SalesRankCursor
I have a report that displays its date dropdown badly: 10AM,10PM, 11AM, 11PM etc when it should be 10AM, 11AM, etc. The used measure is Hour Name which looks like "01AM" "02AM" etc. And here is the current query
WITH MEMBER [Measures].[ParameterCaption] AS [Report Verified Time].[Hour Name].CURRENTMEMBER.MEMBER_CAPTION MEMBER [Measures].[ParameterValue] AS [Report Verified Time].[Hour Name].CURRENTMEMBER.UNIQUENAME MEMBER [Measures].[ParameterLevel] AS [Report Verified Time].[Hour Name].CURRENTMEMBER.LEVEL.ORDINAL SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue],
I have 13 parameters, ordered correctly within the Report Parameter screen.
When displayed in the Preview tab they are all ordered correctly, but when viewed in the application the first 4 are at the top but ordered incorrectly. The remainder are ordered correctly.
I have tried reordering, saving, deploying, viewing and then doing the same but in the correct order without any success.
Please can someone suggest how I can get the parameters to appear in the correct order within the application?
We are presently testing various upgrade scripts to our current application which is scheduled to shortly be upgraded to mssql 7.0. We are testing under mssql 7.0,sp 2.
As it works now, I receive some existing scripts that have been modified and some stored proceures that are new. For the existing stored procedures, I usually take my best guess as to what the order of creation will be, test it in my script for recompile(actually all are dropped first and then the guesswork on the creates). This is usually trial and error as I run the script, see any sysdepends errors such as:
"CREATE PROCEDURE: ep_invoiceheaderformat_spv0101 Cannot add rows to sysdepends for the current stored procedure because it depends on the missing object 'ep_assumepay"
And then move the order of the create procedures around in the script and try again until I get a clean run in a test database I use just to syntactically test the scripts.
I looked at the sysdepends table for the database and pretty much decided that the object numbers and stuff was pretty much incomprehensible to me.
Alternatively I could compile each one separately but I would have the same problem subsequently trying to generate a script of the al of the create procedures... in the right order which would not
My question is:
1) Is there a way I can read and understand what the data means in sysdepends?
2) Figure out a way to utilize the data there to create or generate the create stored procedure text in the correct clean compile order?
3) Any other suggestions?
Any information which can be proveded will be greatly appreciated. Thanks.
My Problem ========= I have a problem with a parameter drop down that is on my report. On one report server (i.e. a SQL Server Reporting Server instance), the drop down data is ordered correctly. On another, it appears to be random.
The report is created using Report Builder v9.0.2047.0.
I have used SQL Server Profiler and I have found that an "order by" clause is appended on one of the report servers, while it is excluded on another.
About my Set-up ============ I have configured multiple report servers (i.e. SQL Server Reporting Server instances) on a single SQL Server 2005 instance. The SQL Server 2005 details are: productversion = 9.00.3054.00 productlevel = SP2 edition = Standard Edition (64-bit)
Another post (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1379591&SiteID=1) puts this problem down to SP2. However, both my report servers are on the same SQL server instance.
I also see that Bob has a blog on a possible solution for this: http://blogs.msdn.com/bobmeyers/archive/2007/10/11/sorting-the-values-in-parameter-dropdowns-in-report-builder.aspx. However, I would like to understand why the report is behaving differently.
I want to have this query insert a bunch of XML but i get this error...
Msg 128, Level 15, State 1, Procedure InsertTimeCard, Line 117
The name "ExpenseRptID" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.
Msg 128, Level 15, State 1, Procedure InsertTimeCard, Line 151
The name "DateWorked" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.
What am i doing wrong...Can anyone help me out!! Thanks!!
p.s I know this query looks crazy...
Code Block
IF EXISTS (SELECT NAME FROM sysobjects WHERE NAME = 'InsertTimeCard' AND type = 'P' AND uid=(Select uid from sysusers where name=current_user)) BEGIN DROP PROCEDURE InsertTimeCard END go /********************************************************************************************************* ** PROC NAME : InsertTimeCardHoursWorked ** ** AUTHOR : Demetrius Powers ** ** TODO/ISSUES ** ------------------------------------------------------------------------------------ ** ** ** MODIFICATIONS ** ------------------------------------------------------------------------------------ ** Name Date Comment ** ------------------------------------------------------------------------------------ ** Powers 12/11/2007 -Initial Creation *********************************************************************************************************/ CREATE PROCEDURE InsertTimeCard @DateCreated DateTime, @EmployeeID int, @DateEntered DateTime, @SerializedXML text, @Result int output as declare @NewTimeCardID int select @NewTimeCardID = max(TimeCardID) from OPS_TimeCards -- proc settings SET NOCOUNT ON
-- local variables DECLARE @intDoc int DECLARE @bolOpen bit SET @bolOpen = 0 --Prepare the XML document to be loaded EXEC sp_xml_preparedocument @intDoc OUTPUT, @SerializedXML -- check for error IF @@ERROR <> 0 GOTO ErrorHandler --The document was prepared so set the boolean indicator so we know to close it if an error occurs. SET @bolOpen = 1
--Create temp variable to store values inthe XML document DECLARE @tempXMLTimeCardExpense TABLE ( TimeCardExpenseID int not null identity(1,1), TimeCardID int, ExpenseRptID int, ExpenseDate datetime, ProjectID int, ExpenseDescription nvarchar(510), ExpenseAmount money, ExpenseCodeID int, AttachedRct bit, SubmittoExpRep bit ) DECLARE @tempXMLTimeCardWorked TABLE ( TimeCardDetailID int not null identity(1,1), TimeCardID int, DateWorked DateTime, ProjectID int, WorkDescription nvarchar(510), BillableHours float, BillingRate money, WorkCodeID int, Location nvarchar(50) ) -- begin trans BEGIN TRANSACTION insert OPS_TimeCards(NewTimeCardID, DateCreated, EmployeeID, DateEntered, Paid) values (@NewTimeCardID, @DateCreated, @EmployeeID, @DateEntered, 0) -- check for error IF @@ERROR <> 0 GOTO ErrorHandler
--Now use @intDoc with XPATH style queries on the XML INSERT @tempXMLTimeCardExpense (TimeCardID, ExpenseRptID, ExpenseDate, ProjectID, ExpenseDescription, ExpenseAmount, ExpenseCodeID, AttachedRct, SubmittoExpRep) SELECT @NewTimeCardID, ExpenseRptID, ExpenseDate, ProjectID, ExpenseDescription, ExpenseAmount, ExpenseCodeID, AttachedRct, SubmittoExpRep FROM OPENXML(@intDoc, '/ArrayOfTimeCardExpense/TimeCardExpense', 2) WITH ( ExpenseRptID int 'ExpenseRptID', ExpenseDate datetime 'ExpenseDate', ProjectID int 'ProjectID', ExpenseDescription nvarchar(510) 'ExpenseDescription', ExpenseAmount money 'ExpenseAmount', ExpenseCodeID int 'ExpenseCodeID', AttachedRct bit 'AttachedRct', SubmittoExpRep bit 'SubmittoExpRep') -- check for error IF @@ERROR <> 0 GOTO ErrorHandler
-- remove XML doc from memory EXEC sp_xml_removedocument @intDoc SET @bolOpen = 0
-- commit transaction, and exit COMMIT TRANSACTION set @Result = @NewTimeCardID RETURN 0
-- Error Handler ErrorHandler: -- see if transaction is open IF @@TRANCOUNT > 0 BEGIN -- rollback tran ROLLBACK TRANSACTION END -- set failure values SET @Result = -1 RETURN -1
We have some SQL CLR code that was written to go get information from a web service...all works great and the CLR code does what it is supposed to. The problem is, it returns its success or failure status as a string text value (equivalent of a print statement I imagine) instead of as a result set or output parameter that i can check. In the proc I wrote to call the CLR stored proc I do some cleanup that i would like to rollback if the call to the web service that the CLR proc makes fails. Problem is, how can i get there. I have tried every way that I know and I can't seem to get that text into an object (temp table, variable whatever) that i can evaluate. Anyone have any suggestions?
I have a report which includes two fields of type date pFromDate and pEndDate. My pFromDate Available values is 'No-queried', and Default values is 'Null'. My pEndDate values is 'No-queried', and Default values is the expression '=DateValue(Today)'. After I upload my report to the report server and run it pEndDate is disabled until I choose value to pFromDate and a postBack occurs. I want pEndDate display the evaluated expression automatically when I run the report. How can I do it? Thanks in advance.
They both drop and use a static table, this will be changed for production to use a temp table. there is a slight difference in how the create the table and when it comes to executing them if the other has been run first it errors because of the column definition or names.
Now as I said I will change these to use a temp table rather than a static staging table for production but as one of the first parts of my proc uses the IF OBJECT_ID ('dbo.tab', 'U') IS NOT NULL DROP TABLE dbo.Tab;
Then I would expect the table difference to not matter as the table is being dropped...
Error 3 Error loading MLS_AZ_PHX.dtsx: The result of the expression ""C:\sql_working_directory\MLS\AZ\Phoenix\Docs\Armls_Schema Updated 020107.xls"" on property "ConnectionString" cannot be written to the property. The expression was evaluated, but cannot be set on the property. c:documents and settingsviewmastermy documentsvisual studio 2005projectsm l sMLS_AZ_PHX.dtsx 1 1
Directly using C:sql_working_directoryMLSAZPhoenixDocsArmls_Schema Updated 020107.xls as connectionString works
However - I'm trying to deploy the package - and trying to use expression: @[User::DIR_WORKING] + "\Docs\Armls_Schema Updated 020107.xls" which causes the same error to occur
(Same error with other Excel source also: Error 5 Error loading MLS_AZ_PHX.dtsx: The result of the expression "@[User::DIR_WORKING] + "\Docs\Armls_SchoolCodesJuly06.xls"" on property "ConnectionString" cannot be written to the property. The expression was evaluated, but cannot be set on the property. c:documents and settingsviewmastermy documentsvisual studio 2005projectsm l sMLS_AZ_PHX.dtsx 1 1 )
Untill recently I had a smooth running SSIS package,but suddenly it throws error syaing "OnError,,,,,,,The result of the expression
"@[User:trTextFileImpDirectory] +"SomeTextStringHere"+ @[User:trANTTextFileName] +(DT_STR,30,1252) @[User:taging_Date_Key]+ "SomeTextStringHere" " on property "ConnectionString" cannot be written to the property. The expression was evaluated, but cannot be set on the property."
I have child SSIS package running under a parent package (through execute package task)
I have few flat file connection managers in child package for text file import , in which I am building text file path dynamically at run time by assigning an expression in connection string property of connection manager. The Expression is as follows
Where @[User:trTextFileImpDirectory] is a variable which contains path of directory containg text files.Value in this variable is assigned at runtime from parent package's variable,which in turns fetch value from a configuration file on local server.
With my current configuration this path has been configured to some other server's directory over network ( I.e my package picks text files from some other servers folder over network)
While "Some string here"+ @[User:trANTTextFileName]" part of file name string.
(DT_STR,30,1252) @[User:taging_Date_Key] Contain the date of processing ,value in this variable is also picked up at run time from parent package variable.
1) So can someone give me some insight into possible reason of failures. 2) Is it possible that problem arises if directory (from which I m picking text files) is assigned password or is there exist some problem in accessing forlders over network ? 3) Or there can be some problem in package configuration at design time( I.e where I m assigning value in variable from parent package vriables)?
Finding the "pieces of information" I need to successfully install the SQL Server Express edition is so complex. Uninstalls do "not" really uninstall completely, leading to failure of SQL install. Can you suggest a thorough, one-stop site for directions for the order of app uninstalls and then the order for app installs for the following...
SQL Server Express edition
Visual Studios 2005
Jet 4.0 newest upgrade
.Net Framework 2.0 (or should I use 3.0)
VS2005 Security upgrade
Anything else I need for just creating a database for my VS2005 Visual Basic project?
I was trying to use MS Access as my backend db but would like to try SQL Express
In SQL sERVER 2008, I have two fields - Depatment and Employees. I need to sort the result set by employee number ascending order, with following exception
1)when department number = 50 - the preferred order is Employee # - 573 followed by 551-572 (employee # belong to Dept 50 = 551-573)
2)When Department number = 20 – the preferred sort order is Employee # 213-220, followed by Employee # 201-213 (employee # belong to Dept 20 = 201-220)
I never paid much attention to this before but I noticed this today in a new table I was creating.
For tables defined in the tabular model the table properties have something like SELECT Blah FROM TableName ORDER BY Blah Then in the tabular model the table's data is in the same order it was ordered by in the data source for the table.
I have a date table I setup and I noticed it is NOT respecting the sort order.
I have it sorted by DateID which sorts with the oldest date first and newest date as last row.However, the table that is imported and stored in the data model is not in that order.
I can of course manually sort the rows in BIDS/DataTools, but I find this discrepancy odd.
Would this have negative impacts on the EARLIER function for example if the data rows are not in the order specified?
INSERT INTO PurchaseOrder (PurchaseOrderDate, SupplierID) VALUES(@date, @SupplierID)
END
SET @POno = @@IDENTITY
RETURN
However, how do i make it that it will automatically adds item under the POno being gernerated? can i use a trigger so that whenever a Insert for PO is success, it automaticallys proceed to adding the items into the table PurcahseOrderItem?
hi basically what i have is 3 text boxes. one for start date, one for end date and one for order id, i also have this bit of SQL SelectCommand="SELECT [Order_ID], [Customer_Id], [Date_ordered], [status] FROM [tbl_order]WHERE (([Date_ordered] >= @Date_ordered OR @Date_ordered IS NULL) AND ([Date_ordered] <= @Date_ordered2 OR @Date_ordered2 IS NULL OR (Order_ID=ISNULL(@OrderID_ID,Order_ID) OR @Order_ID IS NULL))"> but the problem is it does not seem to work! i am not an SQL guru but i cant figure it out, someone help me please! Thanks Jez
Hi, We got a problem. supposing we have a table like this:
CREATE TABLE a ( aId int IDENTITY(1,1) NOT NULL, aName string2 NOT NULL ) go ALTER TABLE a ADD CONSTRAINT PK_a PRIMARY KEY CLUSTERED (aId) go
insert into a values ('bank of abcde'); insert into a values ('bank of abcde'); ... ... (20 times)
select top 5 * from a order by aName Result is: 6Bank of abcde 5Bank of abcde 4Bank of abcde 3Bank of abcde 2Bank of abcde
select top 10 * from a order by aName Result is: 11Bank of abcde 10Bank of abcde 9Bank of abcde 8Bank of abcde 7Bank of abcde 6Bank of abcde 5Bank of abcde 4Bank of abcde 3Bank of abcde 2Bank of abcde
According to this result, user see the first 5 records with id 6, 5, 4, 3, 2 in page 1, but when he tries to view page 2, he still see the records with id 6, 5, 4, 3, 2. This is not correct for users. :eek:
Of course we can add order by aid also, but there are tons of sqls like this, we can't update our application in one shot.
So I ask for your advice here, is there any settings can tell the db use default sort order when the order by column value are the same? Or is there any other solution to resolve this problem in one shot?
Hi,guys!I have a table below:CREATE TABLE rsccategory(categoryid NUMERIC(2) IDENTITY(1,1),categoryname VARCHAR(20) NOT NULL,PRIMARY KEY(categoryid))Then I do:INSERT rsccategory(categoryname) VALUES('url')INSERT rsccategory(categoryname) VALUES('document')INSERT rsccategory(categoryname) VALUES('book')INSERT rsccategory(categoryname) VALUES('software')INSERT rsccategory(categoryname) VALUES('casus')INSERT rsccategory(categoryname) VALUES('project')INSERT rsccategory(categoryname) VALUES('disert')Then SELECT * FROM rsccategory in ,I can get a recordeset with the'categoryid' in order(1,2,3,4,5,6,7)But If I change the table definition this way:categoryname VARCHAR(20) NOT NULL UNIQUE,The select result is in this order (3,5,7,2,6,4,1),and 'categoryname 'in alphabetic.Q:why the recordset's order is not the same as the first time since'categoryid' is clustered indexed.If I change the table definition again:categoryname VARCHAR(20) NOT NULL UNIQUE CLUSTEREDthe result is the same as the first time.Q:'categoryname' is clustered indexed this time,why isn't in alphabeticorder?I am a newbie in ms-sqlserver,or actually in database,and I do havesought for the answer for some time,but more confused,Thanks for yourkind help in advance!
Hi, We got a problem. supposing we have a table like this:
CREATE TABLE a ( aId int IDENTITY(1,1) NOT NULL, aName string2 NOT NULL ) go ALTER TABLE a ADD CONSTRAINT PK_a PRIMARY KEY CLUSTERED (aId) go
insert into a values ('bank of abcde'); insert into a values ('bank of abcde'); ... ... (20 times)
select top 5 * from a order by aName Result is: 6 Bank of abcde 5 Bank of abcde 4 Bank of abcde 3 Bank of abcde 2 Bank of abcde
select top 10 * from a order by aName Result is: 11 Bank of abcde 10 Bank of abcde 9 Bank of abcde 8 Bank of abcde 7 Bank of abcde 6 Bank of abcde 5 Bank of abcde 4 Bank of abcde 3 Bank of abcde 2 Bank of abcde
According to this result, user see the first 5 records with id 6, 5, 4, 3, 2 in page 1, but when he tries to view page 2, he still see the records with id 6, 5, 4, 3, 2. This is not correct for users. Of course we can add order by aid also, but there are tons of sqls like this, we can't update our application in one shot. So I ask for your advice here, is there any settings can tell the db use default sort order when the order by column value are the same? Or is there any other solution to resolve this problem in one shot?
I have created view by jaoining two table and have order by clause.
The sql generated is as follows
SELECT TOP (100) PERCENT dbo.UWYearDetail.*, dbo.UWYearGroup.* FROM dbo.UWYearDetail INNER JOIN dbo.UWYearGroup ON dbo.UWYearDetail.UWYearGroupId = dbo.UWYearGroup.UWYearGroupId ORDER BY dbo.UWYearDetail.PlanVersionId, dbo.UWYearGroup.UWFinancialPlanSegmentId, dbo.UWYearGroup.UWYear, dbo.UWYearGroup.MandDFlag, dbo.UWYearGroup.EarningsMethod, dbo.UWYearGroup.EffectiveMonth
If I run sql the results are displayed in proper order but the view only order by first item in order by clause.
Has somebody experience same thing? How to fix this issue?
insert into test_sort values('Non-A'); insert into test_sort values('Non-O'); insert into test_sort values('Noni'); insert into test_sort values('Nons');
then execute the following selects: select * from test_sort order by cast( 1 as nvarchar(75));
select * from test_sort order by cast( description as nvarchar(75));
When creating expressions we have access to a list of functions. It is my understanding that while these functions seem to have the same names and parameters as SQL functions, they are not so. They are implemented in the package libraries themselves. It is also my understanding that this function library cannot be extended to add new ones.
Am I correct? If so... why not?
Leaving alone the fact that they follow the same screwy names as SQL (instead of .NET on which SSIS is built on) and what seems to be a limited library (i.e. You have YEAR(), MONTH(), DAY() functions but no HOUR(), MINUTE(), or SEC() functions -- instead you have to use DATEPART())
I mean honestly... a common expression for most people is using date and times for folder and filenames... So instead of a simple .NET type expression of DateTime.ToString("yyyyMMdd") or Format(DateTime.Now, "yyyyMMdd_hhmmss") I end up with the very complex:
Personally I find myself using Script Tasks and Variables to "build" my expressions and just use Expressions to set the property to the variable. (Which I think may defeat the full purpose of expressions.)
I have a DB with items which can have lengths from 0 to 400 meter.In my resultset I want to show the items with length 1-400 meter and then the results with length 0 meterHow to build my SQL?
Lets say I have a table named [Leadership] and I want to select the field 'leadershipName' from the [Leadership] Table.
My query would look something like this:
Select leadershipName From Leadership
Now, I would like to order the results of this query... but I don't want to simply order them by ASC or DESC. Instead, I need to order them as follows:
Executive Board Members, Delegates, Grievance Chairs, and Negotiators
My question: Can this be done through MS SQL or do I need to add a field to my [Leadership] table named 'leadershipImportance' or something as an integer to denote the level of importance of the position so that I can order on that value ASC or DESC?
I have some hierarchical data in a table. Say for example:
Parent Child ------------------------ NULL 1
1 2
1 3
2 4
2 5
3 6
3 7
5 8
5 9
7 10
7 11
11 12
11 13
Now I want to be able to use CTE's to be able to traverse this tree in 1) level by level order 1,2,3,4,5,6,7,8,9,10.... 2) in order 1,2,4,5,8,9,3,6,7,10,11,12,13...
What would be the aueries for this. Using the following i get: 1,2,3,6,7,10,11,12,13,4,5,8,9 (interesting and potentially useful) but I would like to be able to experiment with the aforementioned orders as well.
Hi! For the Orders table (let's assume for the Northwind database), I'm trying to get the order id of the latest order for every customer. That means that the result should be one record per customer and that would display CustomerID and OrderID.
I have a table CREATE TABLE [dbo].[CmnLanguage]( [Id] [char](2) NOT NULL CONSTRAINT PkCmnLanguage_Id PRIMARY KEY, [EnglishName] [varchar](26) NOT NULL, [NativeName] [nvarchar](26) NOT NULL, [DirectionType] [smallint] NOT NULL, [IsVisible] [bit] NOT NULL, [CreatedDateTime] [datetime] NOT NULL DEFAULT GETDATE(), [ModifiedDateTime] [datetime] NULL) We will use these 3 queries select * from CmnLanguage where IsVisible = 0select * from CmnLanguage where IsVisible = 1select * from CmnLanguage I want to make a method which handles these queries. But at the back end on Stored Procedures We have to write 3 queries Which I don't want to do. I want to minimize the queries and conditions and want to just write one for these 3 Can any one do it?
Hello all, I have come across a road block once again and it is probably something fairly easy to do. I have 3 queries that give counts with different criteria. They are: SELECT building, COUNT(asmt_number) AS Cnt FROM dbo.mr_gb_asmt WHERE (school_year = 2008) AND (due_date <= CONVERT (DATETIME, '2007-10-26 00:00:00', 102)) AND (publish_scores = 'N') GROUP BY building ORDER BY building SELECT building, COUNT(asmt_number) AS Cnt FROM dbo.mr_gb_asmt WHERE (school_year = 2008) AND (due_date <= CONVERT (DATETIME, '2007-10-26 00:00:00', 102)) GROUP BY building ORDER BY building SELECT building, COUNT(asmt_number) AS Cnt FROM dbo.mr_gb_asmt WHERE (school_year = 2008) AND (due_date <= CONVERT (DATETIME, '2007-10-26 00:00:00', 102)) AND (publish_asmt = 'N') GROUP BY building ORDER BY building My question is how can I combine these three queries into one so that I only get one record set? Thanks for all the help!
