I've got this query and it use to work or at least I thought it did. Could someone please help me with it.
Thank you
SELECT CID, CompletionDate, MarkedExport, CustomerName, EditUser, RouteID, WorkOrder FROM RouteCustomer WHERE (CompletionDate IS NOT NULL) AND (ExportDate IS NULL) AND (RouteID LIKE '%' + ISNULL(RouteID,@RouteID) + '%') AND (EditUser IS NULL OR EditUser = '' OR EditUser = @EmployeeID) AND (MONTH(CompletionDate) = ISNULL(MONTH(CompletionDate),@Month))
The problem comes with in the WHERE clause. What I wanted it to do is if the user did want to use a RouteID critera then the user would speified one else it wouldn't, and it was my belief that the ISNULL feature in SQL was the answer for that. same for the Month. I believe the EditUser is fine the way it is written.
I'm using DTS for the first time and am having difficulty understanding some of its usage. In essence, I have 70 tables which are on an ORACLE database. I've created the schema on SQLServer from an ERWin model. I now wish to use DTS to import the data into SQLServer. Of course there are referential integrity constraints. The DTS 'import wizard' seems oblivious of these and carries out the import in alphabetical order! I'm now trying the DTS designer (which I don't fully understand yet). It appears that I have to create a workflow which moves the workflow from table to table in the order dictated by RI - this is not a trivial task! (I want point and click!!). Are there any shortcuts I can take to have DTS automatically arrange the workflow in the correct order? Can I do it programaticaly? I don't want to have to fiddle with 140 'workflow' and 'success' links. Cheers.
for the query i created i need zeros where ever the filed is blank. i have used count(acc) for selecting the count . can any one help me out with sample query. Thanks in advance
I have two tables, tblMTO and tblIMPORT_MTO. If I import an entire MTOinto the import table I want to create a delta from it (i.e. leave onlythe changed items). I have a view (simplified)SELECT dbo.tblIMPORT_MTO.ImportID, dbo.tblIMPORT_MTO.MTONo,dbo.tblIMPORT_MTO.Rev AS New_Rev, dbo.tblMTO.Rev AS Old_RevFROM dbo.tblIMPORT_MTO LEFT OUTER JOINdbo.tblMTO ON dbo.tblIMPORT_MTO.MTONo = dbo.tblMTO.MTONoNow to get all rows where old_rev = new_rev I also want all rows whereboth are null, is the best method to put ISNULL() in the view or toselect from the view using ISNULL in the criteria, e.g.select * from view1 where ISNULL(Old_Rev,0)=ISNULL(new_rev,0)or in the viewCREATE VIEW view1 asSELECT dbo.tblIMPORT_MTO.ImportID, dbo.tblIMPORT_MTO.MTONo,ISNULL(dbo.tblIMPORT_MTO.Rev,0) AS New_Rev, ISNULL(dbo.tblMTO.Rev ASOld_Rev,0)FROM dbo.tblIMPORT_MTO LEFT OUTER JOINdbo.tblMTO ON dbo.tblIMPORT_MTO.MTONo = dbo.tblMTO.MTONo;select * from view1 where Old_Rev=new_rev;
does sql server mobile 2005 support the isnull function? I'm getting an error when I try to use it and I don't know if it is becuase of using the isnull function or not, but when I run the same query on Sql Server 2005 it works fine.
Hello,I am just getting reaquainted with programming and all. I work in GIS mapping and am having diffilculty using a ASP.net internet Map site and adding records to a needed database.I would like to be able to add records to a linked SQLExpress table when the user clicks on a map location. I adapted the following code snippet from an example that does what I would like only using an .mdb file.CODE: // create connection to database var connection; connection = Server.CreateObject("ADODB.Connection"); connection.Open("Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=OutdoorsExp;Data Source=NOLOGOSQLEXPRESS;"); status = "Can't access 'FishSQL' table."; // create recordset var recordset; recordset = Server.CreateObject("ADODB.Recordset"); recordset.CursorType = 1 recordset.LockType = 3; recordset.Open("dbo.FishTable", connection); status = "Can't append new record."; // append record for clicked location recordset.AddNew(); recordset.Fields("LongitudeI").Value = xco; recordset.Fields("LatitudeI").Value = yco; recordset.Update(); recordset.Close(); // release the .mdb file connection.Close();However, this does not add anything to the SQLEx table. What am I missing? I am thinking that it might have something to with the primary key.I had a table with way more columns then just lat/longs (as above) but only coded it to add data to the latitude and longitude fields. Nothing got added of course. Would have to add data to every field that doesnt allow nulls.How do I generate a random, unique ID for a primary key on the fly and for example add it to the appropriate column.Thanks you in adavnce for any insights.
hi i'm trying to write a stored_proc which involves 3 tables. Product, Orders and OrderDetails i want to return products that haven't sold for a particular month. So these products aren't in the OrderDetails table. When i try and run this i get some really random results. when i change the Orders.OrderDate value to different months, i get the same result when i shouldnt. but then when i add the Orders.OrderDate row to the select statement, i get 400 results and i dont have that many products. the extended amounts are just for my datagrid ALTER PROCEDURE proc_Report_NoSales_Septasset nocount onSELECT Product.productID, Product.Title, Product.QtyOnHand, Product.Category, 0 as ExtendedAmount, Product.BuyPrice, 0 as ExtendedAmount2 from Orders, OrderDetails, Productwhere Orders.OrderDate > '2006-09-01' and Orders.OrderDate < '2006-10-01'and Orders.OrderID = OrderDetails.OrderID and Product.ProductID not in (Select distinct OrderDetails.ProductID from OrderDetails)/* Product.ProductID NOT EQUAL to OrderDetails.productID */and Product.productID <> OrderDetails.productIDGROUP BY Title, BuyPrice, Category, Product.ProductID, QtyOnHandORDER by TitleRETURN any ideas cheers!!!
hay friends scene is that i wana read single multiple rows of a single column from a sql database and then want to shows those values in text box,,, so plz tell me ho to do it. By using data set ,,data table or what to use for this and how.... wll be waiting for ur coordination
I have built an application in web developer that has a sql data base in the App_Data folder. It works locally fine. I have uploaded it to my web host client successfully and it all displays. The web host is enabled for asp.net. But the sql data base is not working....no information is getting from the web application into my instance of sql in App_Data. My web host has given me an e:/webs/mywebsitename.com/data/nameofdb.mdf to alter in my web config connection string which I have done, but still not working. I have also tried every version of this in connection string without success Im just confused about the e: part................how is that going to get to my App_Data? Can someone give me a quick overview of what the problem might be richard
Hi All,I'm stumped. I've got a stored procedure with a number of input parameters, and working fine.I added two extra input parameters, FromDate datetime, ToDate datetime. I have not even included these in the SQL yet and just tried to execute the stored proc alone with manually inputting paramters, but I keep getting error: @FromDate: this input parameter cannot be converted.That surely means a formatting issue, so I copy and pasted a value directly from the database into this parameter field, and still get the same error. I've tried various formats, with single and double quotes, and without. But just dont know?And even when I populate these parameters in my code and call the stored proc, it returns no results either, even though I haven't included these new date paramters in the SQL select, so that means it was in error and no doubt a formatting issue on those date fields.format I used was: 17/07/2007 00:00:00 I tried to populate the parameters via the code as follows: Dim dt As DateTime DateFromTB1.Text = DateTime.Now DateTime.TryParse(DateFromTB1.Text, dt) SqlDataSource1.SelectParameters("FromDate").DefaultValue = dt I know there is an extra step for now (DateFromTB1.Text = DateTime.Now), this step will fall away and just parse the textbox.text field as entered by the user.Any help appreciated, thanks.
I've seen lots of entries recommending the use of ISNULL in SQL WHERE clauses, e.g. in a search sproc where users can enter some or all parameters to search a table. Previously I would have used something like:SELECT * FROM MyTableWHERE (FName = @fname OR @fname IS NULL) AND(MName = @mname OR @mname IS NULL) AND(LName = @lname OR @lname IS NULL)So using the neat ISNULL syntax it could be updated to:SELECT * FROM MyTableWHERE (FName = ISNULL(@fname, FName)) AND(MName = ISNULL(@mname, MName)) AND(LName = ISNULL(@lname, LName))Having played around with this I stumbled upon a problem. If one of the fields, e.g. MName, is NULL then that clause will return false since MName = NULL isn't true and you have to use MName IS NULL. Did I miss all the caveats with using ISNULL in this way on fields that can contain NULL or have I missed something else?
When I try to run my application on our server I cannot connect to the SQL Server that's on the same machine (a named instance). I get the SqlException:Login failed for ("null") user. Not a trusted SQL Server connection. I CAN however, connect to this database from the IDE on my laptop--the app works just fine. I have reconfigured the IIS, the default site and the directory for the app: I turned off anonymous access, turned it on, changed users, changed passwords, etc. I have done the same for the database, added users, changed permissions, added permissions, etc., you get the picture...none of it worked. (I changed the connection string to those users/pwds) I saw on an MS site that this error can occur under heavy loads...but that's not the case here. Please help! Thanks!!
I have to display string "not assigined" when a datefield is null in a table. I am using like ISNULL(datefiled, "not assigned"), but I am getting following error Syntax error converting character string to smalldatetime data type. Is there any way, I can acheive desired result. Please help
In Query Analyzer I have 2 connections active. In the first connection I have the following SQL Insert statement:
Begin Tran Update Account Set SomeField = 0 Where CustomerId >= 1000 And CustomerId <= 1101
-----------------
In the second connection I have the following SQL Insert statement
Begin Tran Insert Into Account (Account,AccountName,AccountTypeRowId,CustomerId,U serId) Values('MMM-989', 'Test Account', 1, 1098,207 )
-----------------
The query in the second connection will hang waiting for the query in the first connection to finish. The query in the first connection touches 767 records (out of a total of 9174).
If I change the Where statement in the first connection to "... And CustomerId <= 1100", the query in the second connection will execute immediately. The query in the first connection is now only touching 645 records instead of 767.
Could someone explain to me why this is happening?
We have a testing database we're using to convert large amounts of data from 1 system to another. We might process 5-6 million records, but don't care about being able to recover point-in-time.
I set recovery mode to simple, do a full backup every night. I keep getting large transaction logs. I manually run Shrink Database when I realize the logs are big
What can I do to prevent the logs from getting big in the first place ?? Can I prevent logging from happening ?
I keep reading various books and BOL, but I guess I don't quite "get it" yet ......
Any plain spoken, detailed suggestions would be very appreciated .... thanks in advance.
I can map to a domain to the server where I have a sql Server database from my machine which is in another domin. However, I cannot register the sql server with enterprise manager from the same machine. I am assuming that it is not a permission problem since I could not get the registration to work logging on as SA or with windows authorization. What should be looked at?
Hi, Can any of you folks take a minute to tell me why the results of the attached test are weird. I'm trying to come up with a method of allowing my business layer to interact with the data layer - specifically a nice, easy to maintain, but safe method of passing parameters without adversely impacting performance.
This is only a few of my sketchy ideas so far but I'm stuck because I cannot prove that using OR is slower than a straightforward = because each time I run the test the OR seems to perform better. Even changing the sequence in which the piece work still does alter the OR begin better than =.
And, of course, as that makes no sense whatsoever I cannto really progress.
Any thought would be appreciated. I hope I have altered the script enough that you can just run it straight in and see what I'm talking about. I only introduced the transactions to see if any of that stuff my be interfering.
I am testing out Synchronous Mirroring and have a question about failover. It seems the only way I can failover to the Mirror box is to log onto the Principal box and hit the Failover button? Is the whole point of mirroring to be able to handle the Principal not being available? Is there another way?
To give you a little background, there is a CRM system with SQL server as its back-end. The CRM uses a view in SQL Server to list all the communications a user has had with his client over any given interval of time. Now there is a requirement to add a new column in the view that tells a user if the communication was filed in automatically or if it happened overnight via an automated archive manager process. I have achieved this using an expression field which is based on the comm_url field in the communications table in database.
example:
create view vCommunications as select col1, col2,...,case when comm_url is null then 'Manually filed' else 'Automatically Filed' as Filing from Communications
alternatively, this can also be achieved by the following:
create view vCommunications as select col1, col2,...,isnull(comm_url, 'Manually Filed') as Filing from Communications
Now my question is, given that there are many rows in the communications table, which of the above two expression fields will be more efficient in performance i.e. CASE versus ISNULL. I've checked a lot on google but I haven't been able to come up with a concrete answer.
Hi guys, Is it possible for a column in a table to have a clustered index & the same column to be a part of a non clustered covering index. Please try this one.I am confused Create table test ( id int primary key, name varchar(40), age int ) CREATE NONCLUSTERED INDEX testindex ON test ( id, name, age )
sp_help test
After I do sp_help test it shows two indexes created, one on id & the other one on id,name& age as a part of covering index.
Hey, I'm taking an intro SQL Server class, and I have a pretty simple homework assignment. We were provided with a DB and asked to write several SELECT statements. However, I'm stuck up one of the questions. Here is the question: 12.Create a SELECT statement that displays all employees and their Qualifications. Display that individuals with no Qualifications as having ‘NoQual’. Hint: Use a function to determine this ‘empty’ field using ISNULL.
Here is what I have:
SELECT FNAME + ' ' + LNAME AS 'Employee Name', ISNULL(QUALID, 'NoQual') AS 'Qualifications' FROM EMPLOYEE, QUALIFICATION WHERE EMPLOYEE.QUALID = QUALIFICATION.QUALID;
However, I do not get any results that have a NULL value in the QUALID column. Here is the code for the DB:
INSERT INTO position VALUES (1, 'President'); INSERT INTO position VALUES (2, 'Manager'); INSERT INTO position VALUES (3, 'Programmer'); INSERT INTO position VALUES (4, 'Accountant'); INSERT INTO position VALUES (5, 'Salesman');
INSERT INTO emplevel VALUES (1, 1, 25000); INSERT INTO emplevel VALUES (2, 25001, 50000); INSERT INTO emplevel VALUES (3, 50001, 100000); INSERT INTO emplevel VALUES (4, 100001, 500000);
INSERT INTO qualification VALUES (1, 'Doctorate'); INSERT INTO qualification VALUES (2, 'Masters'); INSERT INTO qualification VALUES (3, 'Bachelors'); INSERT INTO qualification VALUES (4, 'Associates'); INSERT INTO qualification VALUES (5, 'High School');
INSERT INTO dept VALUES (10, 'Finance', 'Charlotte', 123); INSERT INTO dept VALUES (20, 'InfoSys', 'New York', 543); INSERT INTO dept VALUES (30, 'Sales', 'Woodbridge', 135); INSERT INTO dept VALUES (40, 'Marketing', 'Los Angeles', 246);
I'm having a problem creating a isnull statement. I want to do two things I want to list all of the accounts that have a null value in a numeric field. And I want to update those accounts to 0.00. I tried: select Account_Num, isnull(TotalDDMFEE, 0) FROM Addr_20080402 But it returned all records
For the update I tried: update Addr_20080402 CASE when TotalDDMFEE = ' ' then TotalDDMFEE = 0.00 AND update Addr_20080402 CASE when TotalDDMFEE = isnull(TotalDDMFEE, 0) then TotalDDMFEE = 0.00
Any ideas how I should have written these two queries? Thanx, Trudye
I am trying to find a group of clients that were entered last week. I am getting all the dates to go with the Monday of the week they were entered. i.e. If they were entered on 8-2 or 8-3, they go with 8-1. If they were entered, 8-9, they will be under the 8-8 Monday.
No problem with finding the Mondays using either the nice 'weekcommence' function I found here or using numerous t-sql examples.
One thing I don't understand is- how come I can't use an equal sign in my syntax?
For instance,
SELECT dbo.weekcommence(date_added) FROM clients where dbo.weekcommence(date_added) = dateadd(wk, -2, GETDATE() )
won't return any results. If I use a greater than sign, I get this week, 8-8 and last week 8-1. I only want to see the week of 8-1.
Now- look at what I have below: Shouldn't I really be able to see ONLY last week's Mondays with this?
To me- the way this reads is: show me 2 weeks ago, from these dates (first Mondays function). The results are the last two MONTHS, not weeks. What am I misinterpreting?
I can also use this:
SELECT dbo.weekcommence(date_added) FROM clients WHERE (dbo.weekcommence(date_added) >= dateadd("d",-7,DATEADD(wk, DATEDIFF(wk,1,getdate()), 0)))
and get the week's of 8-1 and 8-8. But I can't get JUST 8-1 if I take out the greater than sign.
To me- this one reads: Select all my Mondays from the function. Show me Mondays from my function that are = 7 days from this Monday's date.
It seems like it should be straightforward, but I'm obviously missing something big. Any help?
okay, using isnull function we could replace null value.. but i want to do opposite, i want to replace if it's NOT null.. i tried notisnull also cannot..
Note : this is for select statement SELECT isnull(d.ClientID,'-') FROM blabla
How to replace something if it's not null SELECT isNOTnull(d.ClientID, '-')
SELECT JS_ID = ISNULL(ID.JS_ID,'-') FROM dbo.FM_INVOICE I, dbo.FM_INVOICE_DETAILS ID WHERE I.INVOICE_ID = ID.INVOICE_ID AND WO_ID = '-'--ISNULL(@GetLatest, '-')
is there any mistake i made? because it still return NULL when no data is found. how do i make it return '-' if a null is found
I have a DELETE statement that is taking ages. I was asked to look into it and immediately noticed the use of function on the column. So its doing a scan even though there is an index on the datecolumn.
HEre's the original statement: Delete someTable Where datediff(year, DateColumn, getdate()) > 1
I have rewritten it as
Delete someTable Where DateColumn < dateadd(year, -1, getdate())
When I did a count(*) the counts from previous query didnt match with the new one. Did i miss something? Perhaps a fresh pair of eyes might help me
I have a null in my column IV2.Inventory. So im trying the case statement at the bottom and keeping getting errors. Where am I going Wrong?
Select StoreGroupID, Sum(SnapShotQuantity * SnapShotPrice) as Inventory, Convert(DateTime, Convert(Char, RecordDate, 101)) as [Date] Into #ttIV From Delsol.dbo.ItemSnapshotStore
Where DateDiff(mm,Convert(DateTime, Convert(Char, GetDate(), 101)),Convert(DateTime, Convert(Char, RecordDate, 101))) >= -24 and Day(RecordDate) = 1 and StoreGroupID = 1
Group By Convert(DateTime, Convert(Char, RecordDate, 101)), StoreGroupID Order By Convert(DateTime, Convert(Char, RecordDate, 101)) desc
Select *, IV1.Inventory-IV2.Inventory/IV2.Inventory as Trend, Case When IV2.Inventory (isnull(0,IV1.Inventory-IV2.Inventory/IV2.Inventory)) From #ttIV IV1 Left Join #ttIV IV2 on IV1.[Date] = DateAdd(mm,1,IV2.[Date]) --Drop Table #ttIV
Hi,I am Just beginning to get to grips with SQL Server v 7 security on a standalone win 98 PC.I have created a login which only has access to the pubs database and when Itry and connect to any other database (while using this login) I amcorrectly told that I don't have access. However, Enterprise Manager doesnot request a login and therefore the log in seems to grant full access toevery database.How can I get Enterprise Manager to request a log in?Thanks.
