Hi... I'm reading the MS Press 70-442 Self-Paced Training kit, and I'm having problems with this example. I'd like help getting it to work correctly, or understand why it is isn't working the way I planned.
On page 67, the lab is about the APPLY operator (CROSS APPLY and OUTER APPLY). I first have to input a sample table-valued function into the AdventureWorks database:
Code Block CREATE FUNCTION fnGetAvgCost(@ProdID int) RETURNS @RetTable TABLE (AvgCost money) AS BEGIN WITH Product(stdcost) AS ( SELECT avg(standardcost) as AvgCost FROM Production.ProductCostHistory WHERE ProductID = @ProdID ) INSERT INTO @RetTable SELECT * FROM Product RETURN END
and then run a sample T-SQL statement
Code Block SELECT p.Name, p.ProductNumber, Convert(varchar, cost.AvgCost,1) AS 'Average Cost' FROM Production.Product p CROSS APPLY fnGetAvgCost(p.ProductID) AS cost WHERE cost.AvgCost IS NOT NULL ORDER BY cost.AvgCost desc
My problem is with the WHERE clause... According to page 56, CROSS APPLY returns only rows from the outer table that produces a result set, so why do I need to explicitly filter NULL values?
When I remove the WHERE clause, the query retrieves lots of NULL AvgCost values.
Again, according to page 56, it is the OUTER APPLY that returns all rows that return a result set and will include NULL values in the columns that are returned from the table-valued function.
So, in short, I don't see the difference between CROSS APPLY and OUTER APPLY, using this example, when I remove the WHERE clause?
(Please refrain from introducing another example into this question.)
I am using SETROWCOUNT 10 in my stored procedure. At end of thi sp I use SETROWCOUNT 0. Will all other sp's that are executing at the same time as the above sp, get affected by the above SETROWCOUNT statement?
I have a table called Courses with columns "courseCode" "courseName" and "TeacherName"Im passing in a value @prefixText ...I have the following...Select courseModuleCode from courses Where courseModuleCode like @prefixTextwhat I actually want to do is return one list, i.e. one column that will do the same for "courseName" and "TeacherName" aswell based on what the @prefix tag is...so i will end up with one column that may contains some or all of the following courseModuleCode, courseName, TeacherName. Im basically implementing a search on this table and want the users to search by any of the column nameshope this makes sense.
I am new to DTS. In ExecuteSQLTask: want the original sql statement of <<backup database databaeABC to backup_databaseABC with init>> to be
backup database ? to 'backup_' + ? with init
But it looks the parameter could not place a globalVariable to it and parse it with error. One alternate way probably need to create a proc to pass a parm to it....
Also, how could pass a globalVariable to a FTP task?
We have a Pub, Dist and Sub Server all running under SP2 with worm update and are looking to apply SP3a . Replication is transactional with heavy usage . What is the best sequence to apply SP3
can it be spread out in 3 weekends or has to be done all together ?
I want to apply lock on a row, where by when another application is trying to access the locked row the application will automatically move to the next row instead of waiting for the locked row
SQL Server 2000 Original Release 8.00.194 RTM Database Components SP1 or Desktop Engine SP1 8.00.384 SP1 Database Components SP2 or Desktop Engine SP2 8.00.534 SP2 Database Components SP3 or SP3a, or Desktop Engine SP3 or SP3a 8.00.760 SP3
This would imply that after installing sp3a and restarting, the version reported should now be 8.00.760. However, it still shows 8.00.194. So it appears to have not really installed, althought the sp3a installation ran without incident. So, how can I get the sp3a to not just act like it installed, but actually get it to install? Also, I have shut down all mssql services and rebooted, applied patch, and rebooted again. Doesn't help. Also, if it is really installing, is there another way to tell?
Anyway, I really need to be able to install sp3a to see if it fixes another problem I'm having. Unfortunetly, since I can't even tell if the sp3a is installing, I can't tell if it really doesn't help or not. Thanks.
Hello everyone... i am new to Structured Query Language. I am learning it for the past 6 months form a book by myself. I don't have a teacher available to me. Now when i have finished that with all of the exercises completed. i want to make sure what i've learned is i've really learned it. the problem is that there is no such organization here that i join and have some hand on practical. So now i am asking u guys to help me. Tell me some way so that i make sure of whatever knowledge i have about SQL.
We would like to apply a hotfix to solve the issue that brought our server down. The hotfix we are trying to apply is in Build 3175. But the latest Cumulative Update 3 Package is Build 3186. Should we apply 3175? Or we can go for the latest build 3186? Which is recommended?
I want to show top selling items for each store. Sample SQL Query is "SELECT TOP n ItemNumber FROM Inventory Group By Store" n - is the dynamic value. I am accepting this n parameter from the user. If the user is giving 5 for n then, I want to show top 5 selling items for each store. It will be greatfull, If anybodies giving me an idea of how to do this?.
HI, I am going to make a big web application. so im going to use sql connection with class for example i have 100 web pages and i will make a slq connection in class.vb Class1.vb(see the below code) Imports System.Data Imports System.Data.SqlClient Imports Microsoft.VisualBasicPublic Class Class1 Private conn As New SqlConnection("Data Source=DANISHSQLEXPRESS;Initial Catalog=ARBASHHR.MDF;Integrated Security=True")Private cmd As New SqlCommand() Private da As New SqlDataAdapter(cmd) End Class default.aspx(here is asp.net page i want to use the sql connection from that class.) but this below page saying that Name "cmd" is not declared and connection Try cmd.Connection = conn conn.Open()Dim ds As New DataSet cmd.CommandText = "SELECT * FROM Users where (UserName = N'" & TextBox1.Text & "')" 'And (Password = N '" & TextBox2.Text & "')"da.Fill(ds, "data") conn.Close() For Each dt As DataTable In ds.Tables For Each dr As DataRow In dt.RowsTextBox3.Text = dr.Item("Password") Next drNext dt If TextBox2.Text = TextBox3.Text ThenResponse.Redirect("Welcome.aspx") Else Label3.Text = "Invalid Username/password" End IfCatch ex As Exception ' MsgBox(ex.Message) '("Error Loading Information From Database..", MsgBoxStyle.Critical, "Error") End Try End Sub please let me know where im doing mistake..... Thank in Advance
Is anyone else having trouble with the Apply button under the permissions tab? I can change permissions, click apply, the changes are shown, but when I access the database, the changes arn't made. If I close the permissions windows and re open the changes arn't made.
Will SP1 fix this?(it's not listed in the fix text file)
Why am I getting a different numbers of distinct ids in those queries?
USE AdventureWorks go Declare @myXml as xml set @myXml = ' <lol>omg</lol> <lol>rofl</lol> ';
select locations.*, T.c.value('.','nvarchar(max)') from ( select newid() as Id from Production.ProductModel where ProductModelID in (7, 8) ) as locations cross apply @myXml.nodes('(/lol)') T(c);
select mytable.* , T.c.value('.','nvarchar(max)') from ( select newid() as Id union select newid() ) as mytable cross apply @myXml.nodes('(/lol)') T(c);
Hi all!I want to create a constraint that uses data from other tables,specifically i want to make sure that a varchar has exactly the lengthspecified in an integer-column in a table that I pointed out with aforeign key.I would like this to be solved something like this:create table string_size_limits(row_id INTEGER PRIMARY KEYstring_size INTEGER)create table strings(string_size_limit_row_id INTEGERREFERENCES string_size_limits(row_id)string varcher(50)CONSTRAINT check_string_size CHECK ???)Is it possible to solve this problem without SP using above model?Is it possible to solve this problem with SP using above model?Must the above problem be solved using triggers?Any help appreciated
I try applying the SP 4 on the default instance of the msde 2000 and when it stops after a minute generates the log and says it was successful but no upgrade is done. I use the following command setup /upgradesp sqlrun SECURITYMODE=SQL BLANKSAPWD=1 DISABLENETWORKPROTOCOLS=0 /L*v C:MSDELog.log
On the same machine I has installed also SQLExpress.
I stop the sql services before issuing the command.
I got 4 sql2k5 servers with service patch version as following:
Microsoft SQL Server 2005 - 9.00.2221.00 (Intel X86) Feb 9 2007 17:30:38 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2) Microsoft SQL Server 2005 - 9.00.2153.00 (Intel X86) May 8 2006 22:41:28 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2) Microsoft SQL Server 2005 - 9.00.3054.00 (Intel X86) Mar 23 2007 16:28:52 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
Can anyone please advise me what is the easiest way to upgrade them to the most updated patch version? i.e. update path.
Meanwhile, what is the best practice to apply patches? Do you go to microsoft website and check it out daily and apply it or other tricks? Please advise. Thanks.
I've been trying to figure out why these two return a different amount of distinct ids... Is that a bug in optimization?
Code Snippet
USE AdventureWorks go Declare @myXml as xml set @myXml = ' <lol>omg</lol> <lol>rofl</lol> ';
WITH locations as ( select newid() as Id from Production.ProductModel where ProductModelID in (7, 8) ) select locations.*, T.c.value('.','nvarchar(max)') from locations cross apply @myXml.nodes('(/lol)') T(c);
with mytable as ( select newid() as Id union select newid() ) select mytable.* , T.c.value('.','nvarchar(max)') from mytable cross apply @myXml.nodes('(/lol)') T(c);
Hi, I have data about amount which is Decimal in my Excel file . The data is like this: $100.24. So In my SQLServer, I declared Amount column as Decimal. Before doing the exporting this data into SQLServer table, I need to remove $ symbol from the amounts. I applied like this: Substring([Amount], 1, Length([Amount]-1). It is giving error : Incorrect format or data invalid when I entered this expression in Derived column. How to remove $ sign and store the remaining part from the Amount field? Thanks in advance
My dataset to display is coming from a stored procedure. Now based on a report parameter which user selects, I want to filter the results being displayed but only if user selects certain value in the parameter drop down. I tried using iif statement and I can use it to filter dataset based on parameter but can't get how NOT to apply the filter for a particular value of the user selected parameter.
I have an order with the time 08/03/2015 7:30 08/03/2015 10:30 ..I have availability with the time 08/03/2015 07:00:00 to 08/03/2015 19:30:00...Here the availability falls for 3 hours only..My requirement is that For order if availability< 4hours i don't want to show employee.How to apply the condition.
I'm attempting to leverage SQL's new 'APPLY" operator and I guess I don't fully understand it proper usage.
This is a relatively simple request, first i want to count the models produced within a valid period of time. The first 'Cross Apply' gets the valid starting and ending dates and looks ups the number of models produced for the period of time. This section of code works perfectly fine.
The problem appears to be with the second "Cross Apply". What I'm attempting to accomplish is to count all models produced, regardless of time frame.
When executed the query appears to go into an loop and I end up canceling out the request.
Any ideas where I went wrong?? Any help is greatly appreciated!
select b1.model as Model ,b1.MinDate as Mfg_Str_Date ,b1.MaxDate as Mfg_End_Date ,Count(b2.Model+B2.Serial) as Mfg_Date_Valid ,Count(b3.Model+B3.Serial) as All_Units
from (select b.model, min(b.build_date) as MinDate ,max(b.build_date) as MaxDate from etbl_models_Serial as b group by b.model) as b1
--These are Units produced within Valid Window cross apply (select b2.model,b2.Serial from etbl_Production as b2 where b2.Model = b1.Model and b2.Mfg_Date between b1.MinDate and b1.MaxDate) as b2
--These are all units produced cross apply (select b3.model,b3.Serial from etbl_Production as b3 where b3.Model = b2.Model) as b3
Group by b1.Model, b1.MinDate, b1.MaxDate Order by b1.Model
I have a database that keeps track of pledges, scheduled reminders, and payments. A user sets up a pledge and then sets up a schedule for reminders to be sent for each payment of the pledge. Pledge table - PledgeID,PledgeDate,PledgeAmount,etc... ScheduledReminders table - ScheduleID,PledgeID,DueDate,ReminderDate,ReminderPrintedDate,ReminderPaidDate AppliedPayments table - ScheduleID,PaymentID,PaidAmount,PaidDate (Junction table between ScheduledReminders and PledgePayments PledgePayments table PaymentID,PaymentAmount,PaymentDate What I need to know is, when a payment is being Inserted I need the payment amount applied to the oldest scheduled reminder record that has not been paid. But if the payment is more than the amount shown in the schedule reminder record, I need the balance to be applied to the next oldest schedule reminder record, etc... Can anyone offer some good suggestions or examples on how to do this?
Hello, We have two updateable subscribers and a publisher using transactional replication. The distributor is located on publisher. Currenetly all 3 sites are SQL 2000 SP3. We plan to upgrade all sites to sp4 with minimum downtime (if possible zero). Is it possible to upgrade publisher/distributor first and subscribers after few days? Thanks, np70
I have two SQL 6.5 servers running independently. Both servers have almost identical databases. For example, we create purchase order in SQL server A. And post the same purchase order # for it's PO Receipt and PO Inspection in SQL server B. How can I apply all transactions entered from SQL server A to SQL server B, and vise versa at end of the day. How can I get help or training for writing codes for manipulating transaction log?