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?
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 have a sql server problem for which the solution is at,
http://support.microsoft.com/kb/889521
The article talks about a hot fix, but there is no link for download the hot fix.
Can anyone have a look and guide me.
Thanks
------------------------
I think, therefore I am - Rene Descartes
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.
View 6 Replies View RelatedI 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?
thanks
-D
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 ?
Thanks
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
View 3 Replies View RelatedI am trying to install sql server sp3a. I checked the version before and after the sp is applied, and it shows:
8.00.194
Now, according to the sp3a readme:
http://support.microsoft.com/default.aspx?scid=/support/servicepacks/SQL/2000/SP3ReadMe.asp
the following versions should show:
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.
Tony F
What is Cross Apply, when it will be used ?
View 2 Replies View RelatedHello 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.
don't worry be happy
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?
View 6 Replies View RelatedI 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?.
Thanks
Should I apply SP2 even though I have already installed the community preview?
View 1 Replies View RelatedHI,
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)
HI FRIENDS,
IS THERE ANY PERFORMANCE IMPACT WHEN I USE "CHAR" AS A DATA TYPE INSTEAD OF USING "INT" FOR RETRIEVING DATA OR FOR SOME COMPLEX QUERY.
THANKS
WITH BEST REGARDS,
DHIRAJ
Hi,
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);
Thanks,
Victor
I have a question, is there any equivalent for the CROSS APPLY operator in SQL server 2000?
I have the following code in SQL Server 2005 and it works fine, but I need an equivalent code in SQL server 2000.
SELECT *
FROM Customers Cust CROSS APPLY dbo.GetAccountAttributes(Cust.AccountNo) Att
what I need is to join a function and passing it a dynamic parameter.
I need it urgently
Thanks in advance,
Imad Elayyan
how can i apply unique cosntraint in an existing table's column
i want to do it like this as below
sql server enterprise manager ==> DB==>table (R.click)==>design table
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
View 4 Replies View RelatedI 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.
Any Ideas?
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 have a question, is there any equivalent for the CROSS APPLY operator in SQL server 2000?
I have the following code in SQL Server 2005 and it works fine, but I need an equivalent code in SQL server 2000.
SELECT *
FROM Customers Cust CROSS APPLY dbo.GetAccountAttributes(Cust.AccountNo) Att
what I need is to join a function and passing it a dynamic parameter.
I need it urgently
Thanks in advance,
Imad Elayyan
I am trying to add Extended properties to each of the columns in my table('MyTable')to the columns
DECLARE @NewTableName as nvarchar(128);
SET @NewTableName = 'MyTable'
SELECT ColumnName FROM(
SELECT c.name as ColumnName
FROM syscolumns c
INNER JOIN sysobjects o
ON o.id = c.id
WHERE o.name = @NewTableName) as T
OUTER APPLY
sp_addextendedproperty( 'Caption', ColumnName,
'user', 'dbo',
'table', @NewTableName,
'column', ColumnName)
I am getting an invalid object name 'sp_addextendedproperty' error.
Jaime
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
Based on some report parameters value?
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.
Hope I am clear.
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.
View 7 Replies View RelatedGood Afternoon,
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
Hi everyone,
As source on a Sql25k table I've got this (often):
XXXXX ZZZZZZZ YYYYYYYY
And, as a result I'd need this (plain file):
Xxxxx Zzzzzz Yyyyy
Ok guys, with VbScript inside a DTS it's very easy, building a loop and then playing with Ucase, Left functions stuff and so on...
How could I do this using a DTSX package?? Any specialized task?
Thanks indeed for your help,
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?
View 1 Replies View Related