May 16, 2012 at 5:18 am
Is there any special way to receive a variable from a macro and put it into my sql-procedure...
I'm a newbie, I know...
/*
EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE
GO
*/
DECLARE @myDate varchar(30),
@myTime VARCHAR(23),
@receivedValue int -- Value received when a button is pushed and a its macro calls on this SQL-procedure...
DECLARE @value_1 VARCHAR(20),
@value_2 VARCHAR(30),
@value_3 NVARCHAR(MAX),
@value_4 int,
@value_5 int,
@value_6 int,
@value_7 VARCHAR(30),
@value_8 VARCHAR(23),
@strValue VARCHAR(8000)
SELECT @myDate = GETDATE()
set @myDate = convert(varchar(10),cast(@myDate AS datetime),121)
BEGIN
SET @myTime = convert(varchar(10), @myDate, 121)
DECLARE C1 CURSOR READ_ONLY
FOR
SELECT
tbl_1.value_1
,tbl_1.value_2
,tbl_1.value_3
,tbl_1.value_4
,tbl_1.value_5
,tbl_1.value_6
,tbl_1.value_7
,tbl_1.value_8
FROM tbl_1
INNER JOIN tbl_2 ON tbl_1.value_2 = tbl_2.value_2
WHERE (tbl_1.value_8 = @myTime) AND (tbl_1.value_6 = @receivedValue) AND (tbl_1.column_x = 1 OR tbl_1.column_y = 0)
ORDER BY tbl_1.value_7
END
OPEN C1
FETCH NEXT FROM C1 INTO
@value_1, @value_2, @value_3, @value_4, @value_5, @value_6, @value_7, @value_8
WHILE @@FETCH_STATUS = 0
BEGIN -- For each row in recordset, create a new line in the textfile...
BEGIN
SET @strValue = 'echo '+ @value_1 + CHAR(9) + @value_2 + CHAR(9) + @value_3 + CHAR(9) + convert(varchar,@value_4) + CHAR(9) + convert(varchar,@value_5) + CHAR(9) + convert(varchar,@value_6) + CHAR(9) + @value_7 +' > C:\Directory\filename_'+ convert(varchar,@value_6) +'.txt'
exec master..xp_cmdshell @strValue
END
FETCH NEXT FROM C1 INTO
@value_1, @value_2, @value_3, @value_4, @value_5, @value_6, @value_7, @value_8
END
CLOSE C1
DEALLOCATE C1
May 16, 2012 at 5:25 am
I don't quite understand what you mean, but from what I see, this appears to be a job for SQL Server Integration Services (SSIS). I would strongly suggest that xp_cmdshell should stay disabled (which is default in all newer versions of SQL Server).
May 16, 2012 at 5:30 am
The variable @receivedValue is sent from a macro and i need to collect it to use in the select-statement...
May 16, 2012 at 5:39 am
What sort of macro?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
May 16, 2012 at 5:49 am
jonas.kasper (5/16/2012)
The variable @receivedValue is sent from a macro and i need to collect it to use in the select-statement...
The phase "sent from a macro" is extremely unclear. Thousands of different products and facilities have things called "macros", all very different. Since SQL Server is not one of those, it is not at all clear which one you mean, nor how it is being "sent".
Also, since you are a "newbie", I strongly encourage you NOT to continue relying on the crutch of Cursors and While loops. Here[/url]'s an article that explains why and how....
[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]
May 16, 2012 at 5:52 am
And here's [/url]a stored procedure that automates doing almost anything over a SELECT statement without using Cursors or Loops.
[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]
May 18, 2012 at 1:59 am
Thanks RBarryYoung!
I certainly will improve, and yes I am one of those who "don't know any better"... Yet! :hehe:
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply