da dovoljno je prosljediti samo putanju do upita (ja radim tocno tako kako si gore napisao), jer druga dva parametra imaju def.vrijednosti pa ako se ne prosljede zadrze defaultnu, sad sam ja sve ispocetka napravio eto ovako:
kreirao sam novi query, u njega stavio kod za kreiranje SP, koji sam izmjenio da fajl pise recimo na C:\, izmjenio sam na dva mjesta gdje god je prije bilo C:\Program Files\... ,i opet mi kriera lijepo proceduru al tu datoteku nikako, probao sam vise lokacija al nikad ju uopće ne kreira, nego zamjetio sam da kad se procedura kreira pa idem na MOdyfy onda mi u proceduri nedostaje kod sa kraja (u ovom listingu je oznaceno - pri dnu)! probao sam taj dio posebno izvršiti al ni onda ne kreira tu datoteku! imaš koju ideju? (inaće ovo sam probao na dva računala, oba naravno u SQL SERVER Surface Area Configuration imaju xp_cmdshell omogucen), no na nijednom ne kreira taj file, pa zato počinjem sumnjati da ja nešto dobro ne napravim ili da tvoj kod za kreiranje procedure nije drugačiji, ne pada mi više ništa na pamet jer ne znam šta da več probam :)
Code:
USE master
GO
CREATE PROCEDURE dbo.sp_ExecuteSQLFromFile
@PCFetch varchar(1000),
@PCAdmin varchar(1000) = NULL,
@PCUltra bit = 0
AS
SET NOCOUNT ON
DECLARE @Return int
DECLARE @Retain int
DECLARE @Status int
SET @Status = 0
DECLARE @Task varchar(2000)
DECLARE @Work varchar(2000)
DECLARE @Line varchar(8000)
DECLARE @SQL1 varchar(8000)
DECLARE @SQL2 varchar(8000)
DECLARE @SQL3 varchar(8000)
DECLARE @SQL4 varchar(8000)
DECLARE @SQL5 varchar(8000)
DECLARE @SQL6 varchar(8000)
DECLARE @SQL7 varchar(8000)
DECLARE @SQL8 varchar(8000)
DECLARE @SQL9 varchar(8000)
DECLARE @CRLF char(2)
DECLARE @Save tinyint
DECLARE @Have int
DECLARE @SQLA int
DECLARE @SQLZ int
DECLARE @SQLN int
DECLARE @BOLA datetime
DECLARE @BOLZ datetime
DECLARE @BOLN datetime
CREATE TABLE #DBAT (Line varchar(8000), Work int IDENTITY(1,1))
CREATE TABLE #DBAZ (Batch int, SQLA int, SQLZ int, SQLN int, BOLA datetime, BOLZ datetime, BOLN datetime, Status int)
SET @CRLF = CHAR(13) + CHAR(10)
SET @SQL1 = ''
SET @SQL2 = ''
SET @SQL3 = ''
SET @SQL4 = ''
SET @SQL5 = ''
SET @SQL6 = ''
SET @SQL7 = ''
SET @SQL8 = ''
SET @SQL9 = ''
SET @Save = 1
SET @Have = 0
SET @SQLA = 1
SET @PCAdmin = ISNULL(@PCAdmin,'C:\File.fmt')
SET @Task = 'BULK INSERT #DBAT FROM ' + CHAR(39) + @PCFetch + CHAR(39) + ' WITH (FORMATFILE = ' + CHAR(39) + @PCAdmin + CHAR(39) + ')'
EXECUTE (@Task)
SET @Return = @@ERROR IF @Status = 0 SET @Status = @Return
DECLARE Lines CURSOR FAST_FORWARD FOR SELECT ISNULL(Line,''), Work FROM #DBAT ORDER BY Work
OPEN Lines
FETCH NEXT FROM Lines INTO @Line, @SQLZ
WHILE @@FETCH_STATUS = 0 AND @Status = 0
BEGIN
IF UPPER(LTRIM(RTRIM(@Line))) = 'GO'
BEGIN
SET @BOLA = GETDATE()
SET @Have = @Have + 1
EXECUTE (@SQL1+@SQL2+@SQL3+@SQL4+@SQL5+@SQL6+@SQL7+@SQL8+@SQL9)
SET @Return = @@ERROR -- IF @Status = 0 SET @Status = @Return
SET @BOLZ = GETDATE()
SET @SQLN = @SQLZ - @SQLA
SET @BOLN = @BOLZ - @BOLA
INSERT #DBAZ VALUES (@Have, @SQLA, @SQLZ, @SQLN, @BOLA, @BOLZ, @BOLN, @Return)
SET @SQL1 = ''
SET @SQL2 = ''
SET @SQL3 = ''
SET @SQL4 = ''
SET @SQL5 = ''
SET @SQL6 = ''
SET @SQL7 = ''
SET @SQL8 = ''
SET @SQL9 = ''
SET @Save = 1
SET @BOLA = GETDATE()
SET @SQLA = @SQLZ + 1
END
ELSE
BEGIN
IF @Save = 1 IF DATALENGTH(@SQL1) + DATALENGTH(@Line) < 7998 SET @SQL1 = @SQL1 + @Line + @CRLF ELSE SET @Save = 2
IF @Save = 2 IF DATALENGTH(@SQL2) + DATALENGTH(@Line) < 7998 SET @SQL2 = @SQL2 + @Line + @CRLF ELSE SET @Save = 3
IF @Save = 3 IF DATALENGTH(@SQL3) + DATALENGTH(@Line) < 7998 SET @SQL3 = @SQL3 + @Line + @CRLF ELSE SET @Save = 4
IF @Save = 4 IF DATALENGTH(@SQL4) + DATALENGTH(@Line) < 7998 SET @SQL4 = @SQL4 + @Line + @CRLF ELSE SET @Save = 5
IF @Save = 5 IF DATALENGTH(@SQL5) + DATALENGTH(@Line) < 7998 SET @SQL5 = @SQL5 + @Line + @CRLF ELSE SET @Save = 6
IF @Save = 6 IF DATALENGTH(@SQL6) + DATALENGTH(@Line) < 7998 SET @SQL6 = @SQL6 + @Line + @CRLF ELSE SET @Save = 7
IF @Save = 7 IF DATALENGTH(@SQL7) + DATALENGTH(@Line) < 7998 SET @SQL7 = @SQL7 + @Line + @CRLF ELSE SET @Save = 8
IF @Save = 8 IF DATALENGTH(@SQL8) + DATALENGTH(@Line) < 7998 SET @SQL8 = @SQL8 + @Line + @CRLF ELSE SET @Save = 9
IF @Save = 9 IF DATALENGTH(@SQL9) + DATALENGTH(@Line) < 7998 SET @SQL9 = @SQL9 + @Line + @CRLF ELSE SET @Save = 0
END
FETCH NEXT FROM Lines INTO @Line, @SQLZ
END
CLOSE Lines DEALLOCATE Lines
IF DATALENGTH(@SQL1) > 0 AND @Status = 0
BEGIN
SET @BOLA = GETDATE()
SET @Have = @Have + 1
EXECUTE (@SQL1+@SQL2+@SQL3+@SQL4+@SQL5+@SQL6+@SQL7+@SQL8+@SQL9)
SET @Return = @@ERROR -- IF @Status = 0 SET @Status = @Return
SET @BOLZ = GETDATE()
SET @SQLN = @SQLZ - @SQLA + 1
SET @BOLN = @BOLZ - @BOLA
INSERT #DBAZ VALUES (@Have, @SQLA, @SQLZ, @SQLN, @BOLA, @BOLZ, @BOLN, @Return)
END
IF @PCUltra <> 0
BEGIN
SELECT Batch
, SQLA AS LineFrom
, SQLZ AS LineThru
, SQLN AS CodeSize
, CONVERT(char(12),BOLA,14) AS TimeFrom
, CONVERT(char(12),BOLZ,14) AS TimeThru
, CONVERT(char(12),BOLN,14) AS Duration
, Status
FROM #DBAZ
ORDER BY Batch
END
DROP TABLE #DBAT
DROP TABLE #DBAZ
SET NOCOUNT OFF
RETURN (@Status)
----------kad kasnije idem na Modify odavde do kraja nedostaje----------------------
GO
DECLARE @Task varchar(1000)
DECLARE @Work varchar(2000)
SET @Task = ' PRINT ' + CHAR(39) + '7.0' + CHAR(39)
+ ' PRINT ' + CHAR(39) + '1' + CHAR(39)
+ ' PRINT ' + CHAR(39) + '1 SQLCHAR 0 8000 ' + CHAR(39) + ' + CHAR(34) + ' + CHAR(39) + '\r\n' + CHAR(39) + ' + CHAR(34) + ' + CHAR(39) + ' 1 Line SQL_Latin1_General_CP1_CI_AS' + CHAR(39)
SET @Work = 'osql -E -Q "' + @Task + '" -o "C:\File.fmt" -s "" -w 8000'
EXECUTE master.dbo.xp_cmdshell @Work, NO_OUTPUT
GO
----Roberto----
izvršavanje SQLQUERY-a
