Viewing 15 posts - 5,596 through 5,610 (of 7,631 total)
If I add "ORDER BY name" the error goes aways but it only lists one routine.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 3, 2008 at 2:04 pm
Christian Buettner (9/3/2008)
DECLARE @A nvarchar(max)
SELECT @A = ISNULL(@A,'') + OBJECT_DEFINITION(object_id) + '
GO
' FROM sys.procedures
SELECT CAST(@A AS XML)
Explanation:
1. Use OBJECT_DEFINITION to get the full script of the object...
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 3, 2008 at 1:57 pm
Glad I could help.
Just remember in the future though: If you want to be able to retrieve rows in the order that they were inserted, you will need some...
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 3, 2008 at 1:52 pm
If you want to solve the "text output limit" problem then you will first need a specialized split function like this:
ALTER function [dbo].[fnSplit3](
...
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 3, 2008 at 1:47 pm
Glad I could help (hmm, it does seem like part of my post is missing...).
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 3, 2008 at 1:27 pm
Glad we could help. 🙂
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 3, 2008 at 1:25 pm
This is easy enough to do without a cursor:
--======
SET NOCOUNT ON
select N'
----------------------------------------------------------------------------
-- ' + p.name + N'
----------------------------------------------------------------------------
' + m.definition + N'
GO
----------------------------------------------------------------------------
'
from sys.sql_modules m
join sys.procedures p
...
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 3, 2008 at 1:23 pm
This should work then:
Update c1 set descr = n1.descr
From cit c1
Join nit n1 ON c1.cno=n1.cno
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 3, 2008 at 12:53 pm
You do not really need sp_xml_preparedocument except for OPENXML, which you do not really need either in SQL 2005.
What you should look at are the XML datatypes and the XML...
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 3, 2008 at 12:25 pm
psangeetha (9/3/2008)
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 3, 2008 at 12:10 pm
You want the DENSE_RANK() function:
Select Id, DENSE_RANK() OVER(order by Id) as [Seq_Num]
From MyTable
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 3, 2008 at 11:55 am
And how can you tell which one was the first inserted? SQL Server does not necessarily either store them or return them in the same order that you inserted...
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 3, 2008 at 11:51 am
OK, this will get the entire length:
select o.name, m.definition
from master.sys.system_sql_modules m
join master.sys.system_objects o
ON o.object_id = m.object_id
Of course, getting the entire definition out of...
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 3, 2008 at 11:47 am
"Select [text] from master.sys.syscomments" works but is limited to 4000 characters.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 3, 2008 at 11:41 am
In what context are you trying to do this? A SELECT? A View? An INSERT Trigger? A stored procedure that is writing to the table?
It is...
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 3, 2008 at 11:29 am
Viewing 15 posts - 5,596 through 5,610 (of 7,631 total)