Prev: How to use Request.Form() while using BinaryRead
Next: Run CACLS from an ASP page?
From: Joe on 9 Dec 2008 13:37
I am building a small helper application to create a table, stored
procedures and triggers. I need to output the SQL formatted instead
of all together. For instance, here is an Update Trigger that I have
generated,

CREATE TRIGGER updTrips ON dbo.Trips AFTER UPDATE AS IF @@ROWCOUNT = 0
RETURN INSERT INTO [dbo].[Audit_Changes] ([PrimaryID],
[ColumnChanged], [TableChanged], [OldValue], [NewValue], [Username],
[DTChanged], [ActionType]) SELECT i.TripID, CASE col# WHEN 2 THEN
'TripDate' WHEN 3 THEN 'StartTime' WHEN 4 THEN 'EndTime' ELSE '?' END,
'Trips', CASE col# WHEN 2 THEN CAST(d.TripDate AS VARCHAR(25)) WHEN 3
THEN d.StartTime WHEN 4 THEN d.EndTime ELSE '?' END, CASE col# WHEN 2
THEN CAST(i.TripDate AS VARCHAR(25)) WHEN 3 THEN i.StartTime WHEN 4
THEN i.EndTime ELSE '?' END, SUSER_SNAME(), GETDATE(), 'U' FROM
inserted i INNER JOIN deleted d ON i.TripID = d.TripID CROSS JOIN
( SELECT 2 AS col# UNION ALL SELECT 3 AS col# UNION ALL SELECT 4 AS
col# ) AS col#s WHERE ISNULL(CASE col# WHEN 2 THEN CAST(i.TripDate AS
VARCHAR(25)) WHEN 3 THEN i.StartTime WHEN 4 THEN i.EndTime ELSE '?'
END, '') <> ISNULL(CASE col# WHEN 2 THEN CAST(d.TripDate AS VARCHAR
(25)) WHEN 3 THEN d.StartTime WHEN 4 THEN d.EndTime ELSE '?' END, '')

Which is very, very messy... I would like to figure out how to format
the SQL so it looks something like this,

CREATE TRIGGER updTrips
ON dbo.Trips
AFTER UPDATE
AS
IF @@ROWCOUNT = 0
RETURN
INSERT INTO [dbo].[Audit_Changes] ([PrimaryID], [ColumnChanged],
[TableChanged], [OldValue], [NewValue], [Username], [DTChanged],
[ActionType])
SELECT i.TripID,
CASE col#
WHEN 2 THEN 'TripDate'
WHEN 3 THEN 'StartTime'
WHEN 4 THEN 'EndTime'
WHEN 5 THEN 'Duration'
WHEN 6 THEN 'RLU'
WHEN 7 THEN 'TripPlace'
WHEN 8 THEN 'TripPurpose'
ELSE '?' END,
'Trips',
CASE col#
WHEN 2 THEN CAST(d.TripDate AS VARCHAR(25))
WHEN 3 THEN d.StartTime
WHEN 4 THEN d.EndTime
WHEN 5 THEN d.Duration
WHEN 6 THEN d.RLU
WHEN 7 THEN d.TripPlace
WHEN 8 THEN d.TripPurpose
ELSE '?' END,
CASE col#
WHEN 2 THEN CAST(i.TripDate AS VARCHAR(25))
WHEN 3 THEN i.StartTime
WHEN 4 THEN i.EndTime
WHEN 5 THEN i.Duration
WHEN 6 THEN i.RLU
WHEN 7 THEN i.TripPlace
WHEN 8 THEN i.TripPurpose
ELSE '?' END,
SUSER_SNAME(),
GETDATE(),
'U'
FROM inserted i
INNER JOIN deleted d ON i.TripID = d.TripID
CROSS JOIN (
SELECT 2 AS col# UNION ALL
SELECT 3 AS col# UNION ALL
SELECT 4 AS col# UNION ALL
SELECT 5 AS col# UNION ALL
SELECT 6 AS col# UNION ALL
SELECT 7 AS col# UNION ALL
SELECT 8 AS col# )
AS col#s
WHERE ISNULL(CASE col#
WHEN 2 THEN CAST(i.TripDate AS VARCHAR(25))
WHEN 3 THEN i.StartTime
WHEN 4 THEN i.EndTime
WHEN 5 THEN i.Duration
WHEN 6 THEN i.RLU
WHEN 7 THEN i.TripPlace
WHEN 8 THEN i.TripPurpose
ELSE '?' END, '') <>
ISNULL(
CASE col#
WHEN 2 THEN CAST(d.TripDate AS VARCHAR(25))
WHEN 3 THEN d.StartTime
WHEN 4 THEN d.EndTime
WHEN 5 THEN d.Duration
WHEN 6 THEN d.RLU
WHEN 7 THEN d.TripPlace
WHEN 8 THEN d.TripPurpose
ELSE '?' END, '')

I have tried vbCrLf, vbNewLine, etc... and nothing seems to work. Any
ideas?

Thanks,
Drew
From: Bob Barrows on 9 Dec 2008 14:00
Joe wrote:
> I am building a small helper application to create a table, stored
> procedures and triggers. I need to output the SQL formatted instead
> of all together.

"Output" it where? In your HTML? If so, you either need to use the <PRE>
tag, or use <br> for your line breaks.
--
HTH,
Bob Barrows


From: Joe on 9 Dec 2008 14:35
On Dec 9, 2:00 pm, "Bob Barrows" <reb01...(a)NOyahoo.SPAMcom> wrote:
> Joe wrote:
> > I am building a small helper application to create a table, stored
> > procedures and triggers.  I need to output the SQL formatted instead
> > of all together.
>
> "Output" it where? In your HTML? If so, you either need to use the <PRE>
> tag, or use <br> for your line breaks.
> --
> HTH,
> Bob Barrows

No, the problem seems to be that when I use,

select name as 'Trigger', object_name(parent_obj) as 'Table'
from sysobjects
where xtype = 'TR'

to view the trigger in the database, the trigger is really unreadable,
which is hard to troubleshoot. Is there anyway to do this?

Thanks,
Drew
From: Bob Barrows on 9 Dec 2008 14:56
Joe wrote:
> On Dec 9, 2:00 pm, "Bob Barrows" <reb01...(a)NOyahoo.SPAMcom> wrote:
>> Joe wrote:
>>> I am building a small helper application to create a table, stored
>>> procedures and triggers. I need to output the SQL formatted instead
>>> of all together.
>>
>> "Output" it where? In your HTML? If so, you either need to use the
>> <PRE> tag, or use <br> for your line breaks.
>> --
>> HTH,
>> Bob Barrows
>
> No, the problem seems to be that when I use,
>
> select name as 'Trigger', object_name(parent_obj) as 'Table'
> from sysobjects
> where xtype = 'TR'
>
> to view the trigger in the database, the trigger is really unreadable,
> which is hard to troubleshoot. Is there anyway to do this?
>
View it where? in SSMS? or, if it's pre-2005 SS, Query Analyzer?

How did the text get into the sysobjects table? Was it input into your
html page? if so, what kind of element was used? Whatever you are doing
to receive the input from the user and pass it to the database is
causing the whitespace to be stripped
--
HTH,
Bob Barrows


From: Joe on 9 Dec 2008 15:21
On Dec 9, 2:56 pm, "Bob Barrows" <reb01...(a)NOyahoo.SPAMcom> wrote:
> Joe wrote:
> > On Dec 9, 2:00 pm, "Bob Barrows" <reb01...(a)NOyahoo.SPAMcom> wrote:
> >> Joe wrote:
> >>> I am building a small helper application to create a table, stored
> >>> procedures and triggers. I need to output the SQL formatted instead
> >>> of all together.
>
> >> "Output" it where? In your HTML? If so, you either need to use the
> >> <PRE> tag, or use <br> for your line breaks.
> >> --
> >> HTH,
> >> Bob Barrows
>
> > No, the problem seems to be that when I use,
>
> > select name as 'Trigger', object_name(parent_obj) as 'Table'
> > from sysobjects
> > where xtype = 'TR'
>
> > to view the trigger in the database, the trigger is really unreadable,
> > which is hard to troubleshoot.  Is there anyway to do this?
>
> View it where? in SSMS? or, if it's pre-2005 SS, Query Analyzer?
>
> How did the text get into the sysobjects table? Was it input into your
> html page? if so, what kind of element was used? Whatever you are doing
> to receive the input from the user and pass it to the database is
> causing the whitespace to be stripped
> --
> HTH,
> Bob Barrows

In Query Analyzer. I am using SQL Server 2000 (should've mentioned
that earlier).

I am dynamically creating the triggers (so I don't have to manually
write them) using ASP... the ASP page is connecting to the DB and then
executing the CREATE TRIGGER statement (as well as creating a table
and a couple stored procedures) that has been generated by ASP. I
just figured that there would be some way to format these statements
before they were created on the SQL Server.

Thanks,
Drew
 |  Next  |  Last
Pages: 1 2
Prev: How to use Request.Form() while using BinaryRead
Next: Run CACLS from an ASP page?