Executing A Job Using VB
Sep 17, 2007I want to execute a backend SQL job using VB on an MSACCESS frontend.
I know how to run a DTS using VB script. Can anyone give me the syntax for a SQL job? Thanks in advance.
ADVERTISEMENT
Stored Procedure Executing Durations Are Different Between Executing From Application(web) And SQl Server Management Studio - Qu
Jan 24, 2008
Hi,
I have a web application using Stored Procedure (SP). I see that there's a SP taking long time to execute. I try to capture it by Profiler Tool, and find out that with the same SP on the same db with the same parameter. The duration of executing by my web app is far bigger than the duration of executing on SQl server management studio - query window
Please see the image through this url http://kyxao.net/127/ExecutionProblem.png
Any ideas for this issue?
Thanks a lot
Stored Procedure Executing Durations Are Different Between Executing From Application(web) And SQl Server Management Studio - Query Window
Jan 23, 2008Hi,I have a web application using Stored Procedure (SP). I see that there's a SP taking long time to execute. I try to capture it by Profiler Tool, and find out that with the same SP on the same db with the same parameter. The duration of executing by my web app is far bigger than the duration of executing on SQl server management studio - query windowPlease see the image attached http://kyxao.net/127/ExecutionProblem.png Any ideas for this issue?Thanks a lot Jalijack
View 2 Replies View RelatedDifferent Results When Executing From .NET Component Compare To Executing From SQL Management Studio
Oct 10, 2006Hi all,I am facing an unusual issue here. I have a stored procedure, that return different set of result when I execute it from .NET component compare to when I execute it from SQL Management Studio. But as soon as I recompile the stored procedure, both will return the same results.This started to really annoying me, any thoughts or solution? Thanks very much guys
View 2 Replies View RelatedPb Executing An SP
Sep 25, 2006I have an SP that has this part of code located in the line:30
select convert(datetime,CONFIGURATION1) from dbdw..configuration
where CLECONFIG = 'DATE-AJOUT-VENTES'
I get teh error:
Server: Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
Do u know what the problem is pls
Executing A DTS From Another DTS...
Jul 20, 2005Hi there,I am trying to execute a DTS package from another DTSpackage and change some variables in the original DTS. Ihave used the info on the following page but it doesn'twork: http://www.sqldts.com/default.aspx?215. Would youhave any input?Thanks,Andreas*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 2 Replies View RelatedExecuting More Than One Query
Jun 16, 2007Hi,
How to execute more than one query consecutively in SqlDataSource like the picture shown below.
Executing DTS (on Sql Server) From ASP.NET??
Feb 5, 2004Does Any one know how to execute DTS (on sql server) from ASP.NET??
View 2 Replies View RelatedExecuting DTS Package From VB.NET
Nov 16, 2004Hello,
I have a web application that I kick off a DTS package. I tested the package and it works fine. However, when running the package through ASP.NET, I get the following error:
Type mismatch. (mscorlib (80004005): Missing parameter does not have a default value. Parameter name: parameters)
I've traced it to a dynamic task step. I have this as the first step in the workflow, but for some reason, it is executing last. Which is definitely not the place where I want it. Is there anyway I can force it, or why is it doing that?
Any ideas?
Brian
Executing DDL In A Trigger
Nov 15, 2000Dear All,
I would like to execute a DDL statement(create table) in a trigger. The DDL statement is stored as a field in another table. I have read that sql statement into a variable of type varchar. How do I execute that sql statement.
Thanks in advance
Job Sticks On "executing"
Jan 18, 2001Hi, I have a DTS package that runs correctly when executed it manually, but when I schedule a job via the package, the job begins executing, but does not stop. I have to cancel it. The Package runs in less that 1 minute, but the job reports that it's being executed but does not stop until I cancel it.
The job was created by using the DTS package schedule command, so how can I check what's wrong?
Thanks in advance.
Executing SP In Loop
Sep 21, 2000What is way for executing SP in loop? I have to delete some records in table X depends on records from table Y. Simple way is:
DELETE FROM X
WHERE X.ID IN
(
SELECT ID FROM Y WHERE ...
)
But if deleting from X is not so simply (after deleting from X I have to delte it from 5 others tables) ? I've created SP 'SP_DELETE_X' which has input parameter 'ID'. My question is how execute this SP in above loop???
Fly
Job Stuck While Executing
Nov 29, 2001Hi All
I have a job that extracts data from 10 different tables to 10 diffrenet tables of MS-Access (.mdb file).
The job ran okay since last one month but yesterday it ran and did not stop at all .Status is shown as "Executing" ..
I tried to stop it but it is not stopping . I tried to kill associated process (spid) but it allows me to kill spid but it shows there running.
Any idea why it is happening ?
If any body ahs any solution you can call me at 1 860 520 7454.
Sujit
Error 266 After Executing SP
Apr 24, 2003I am facing this error when using a SP called by a DTS package (using ADO connection).
The SP gets three parameter and looks like the following (just major part of it):
SET @STR = 'SET ROWCOUNT 10000' + ' WHILE 1 = 1 ' + 'BEGIN' + ' BEGIN TRAN'
SET @STR = @STR + ' DELETE FROM ' + @TableToBeCleaned + ' WHERE ' + @SELECTEDCOLUMN + ' <
DATEADD (m,' + ' -' + @KeepMonthsInDatabase + ', + GETDATE())'
SET @STR = @STR + 'IF @@ROWCOUNT = 0 ' + 'BREAK ' + 'COMMIT ' + 'END ' + 'SET ROWCOUNT 0'
EXEC (@str)
Following error appears:
Server: Msg 266, Level 16, State 2, Line 1
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing.
Previous count = 4, current count = 5.
Server: Msg 266, Level 16, State 2, Procedure p_DBCleanerDeleteRows, Line 56
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing.
Previous count = 4, current count = 5.
I use the SET ROWCOUNT to execute the delete in batches. When checking with SELECT @@TRANCOUNT it
returns me 5. Somehow it is clear to me that @@TRANCOUNT exited the SP with another value than when
entering it but there is commit in the dynamic SQL statement and I do not know what is wrong.
Is it neccessary to indicate BEGIN TRAN and COMMIT in the statement at all or can I ommit it?
Thanks
mipo
Executing Cursors With ASP
Jan 19, 2006Hi,
I have written a Stored Procedure which opens a Cursor , carries out some functionality and the I De-Allocate and Close it.When I execute the Stored Procedure from Query Analyzer it works fine.However, When I execute it through ASP it does not.
Conn.Execute "Execute Proc_Name"
The Connection Object (Conn) works fine for other pages as it is in an include file.
If I remove the Cursor from the Stored_Proc the other Statements get executed , however the Execution stops when Cursor Code is there.(Though works fine with Query Anaylzer)
No error is thrown, I have checked with Query Profiler and it shows that though Stored Procedure is called but the statements in it are not executed.
There are no Parameters to it, as all it is doing is comparing values of two tables and inserting it into 1st table and deleting from the second.
Pl. Help, As Stored_Proc works fine when executed through
Query analyzer but not thru ASP.
Thanx
Dkashyap
Executing Xp_cmdshell
Oct 14, 2004Hi ,
I want to execute xp_cmdshell from normal sql server account not sysadmin member in SQL Server 2000. I had give execute privileges on xp_cmdshell to normal account... but its not working...
Is it possible to execute xp_cmdshell from non sysadmin member account in SQL Server2000...
Thanks in advance...
-Mohit.
Executing Set Of SPs As JoB Is Hanging..
May 27, 2004I have a job which is set of few Stored procedures,Usually it taked around 3-5 mins to complete the job.But somehow today the job was still executing even after 3:45:24 (yes 3 hrs,45 mins 25 secs)
WHen i tried to run the each procedure indivdually even its taking more time in the query analyzer.But when i try to execute those SPS as individual sql statements(it's step by step) they were working in reasonable time.What should be the reason for these SPs taking that much time?
Thanks.
Executing .sql File
Oct 4, 2006hi
i have a .sql file, which i can run using query analyzer. is there any other way to execute this file, like using a .bat file or .exe file. user should be able to execute this file without using query analyzer or any other database tools. i cannot call this from my main programe.
this should be a self executable file.
thanks
suji
Executing DTS Package
Dec 27, 2006Hi Friends
I am trying to execute the DTS package from the T-Sql
Its basically Exporting the table from Sql Server to tab delimited text file
DECLARE @Command varchar(1000)
declare @partcode varchar(100)
set @command = 'DTSRun /S "servername" /U "username" /P "password" /N "TabFormat" /G "{459784D8-0170-400C-8919-AC25423D7F34}" /W "0" '
EXEC master..xp_cmdshell @Command
It Comes up with the Following Error
output
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
DTSRun: Loading...
DTSRun: Executing...
DTSRun OnStart: DTSStep_DTSDataPumpTask_1
DTSRun OnError: DTSStep_DTSDataPumpTask_1, Error = -2147467259 (80004005)
Error string: Error opening datafile: The system cannot find the path specified.
Error source: Microsoft Data Transformation Services Flat File Rowset Provider
Help file: DTSFFile.hlp
Help context: 0
Error Detail Records:
Error: 3 (3); Provider Error: 3 (3)
Error string: Error opening datafile: The system cannot find the path specified.
Error source: Microsoft Data Transformation Services Flat File Rowset Provider
Help file: DTSFFile.hlp
Help context: 0
DTSRun OnFinish: DTSStep_DTSDataPumpTask_1
DTSRun: Package execution complete.
NULL
(22 row(s) affected)
Vic
Executing DTS - DSN Error...
Jul 23, 2005Perhaps this has already been answered. If so, I apologize. I'm anewbie at this stuff. I have a DTS package to import some VisualFoxpro data. Yes, I said Visual Foxpro. :) The DTS package worksbeautifully when I rightclick on the package and choose "execute." Andit works beautifully when I run dtsrun.... at the DOS prompt. But itdoesn't work when I try to execute it with code in Query Analyzer.I've tried exec master..xp_cmdshell 'dtsrun ...' And I've triedscheduling the package as a Job, disabling the Job and then usingmsdb..sp_start_job to run it. Neither of those work.I get a "DSN not found and no default driver specified."My DSN stuff looks OK to me, but perhaps some of that is wrong. Whatdoes the DSN have to look like in order for the code in Query Analyzerto find it? If the DSN is screwed up, why would it work OK whenexecuted from some places, but not in others? What am I missing?Any help would be much appreciated. :) thanks...-emily
View 3 Replies View RelatedExecuting Sql Statements
Jul 23, 2005When i execute the following in Sql query anlyzerDeclare @dbname varchar(30),@str varchar(500),@emailID varchar(50)set @EmailID='santosh@yahoo.com'set @dbname='DB_kms_prv'set @str='SELECT empid, NTName, officialEmail, PreferredName FROM ' +@dbname + '.dbo.tblEmployee where officialEmail=' + @emailIDexec (@str)I get error messageThe column prefix 'santosh@yahoo' does not match with a table name oralias name used in the query.How to get rid of it..?
View 1 Replies View RelatedExecuting An String On MS SQL From .Net
Jan 26, 2006Hi there,
I have this:
Dim strSQL As String = "SELECT * FROM GrupoRegistro WHERE Fecha BETWEEN '" & fini & "' AND '" & ffin & "' AND (g.IDTipoCliente = 5) OR g.Fecha BETWEEN '" & fini & "' AND '" & ffin & "' AND (g.IDTipoCliente = 3)"
How do I execute on .Net? I am using VWD2005 Express Edition and MS SQL 2000 server on a WinXp pro with MS Framework 2.0
I am using o trying and some variants :
SelectCommand="@strsqlvar">
<SelectParameters>
<asp:QueryStringParameter Name="strsqlvar" DefaultValue="strsql.string" />
</SelectParameters>
And it doesn´t work... any help will e well apreciated. thank u in advance.
Executing Package As Job
May 8, 2006Hi,
I am having trouble executing a package as a job in SQL Server 2005. I can run the package fine manually from sql server but when I create a job for that package and run the job I get an error that says
The package execution failed. The step failed.
I am completely lost at this point. Any help would be greatly appreciated.
Thanks
Brian
Executing Serial Dts
Feb 1, 2007hi,
i am new here and interested in how to :
- execute serial dts in one single action or using vb script.
- change the status (enabled to disabled) and schedule using vb script.
Many dts make me tired when i have to change the status and schedule one by one.
regards.
Executing Packages From ASP.Net
Oct 10, 2005I just want to confirm my thinking here.
View 5 Replies View RelatedExecuting SSIS....
Apr 22, 2008
Hi....
Iam new to this DTS and SSIS...today only iam looking ata ascreen with DTS and SSIS for 1st time(so may be the words iam using may not be correct..pls adjust and help me...)...so please read and help me how to solve this....
I have 100 dataflows in my DTS....now we r following this code to execute the package....
package.Execute();
but it will all execute all the DTS at a time....
but my requirement is i have to execute each dataflow one by one and then if that particular flow is executed without erron then i need to log with something like <dataflowname> suceed ....else <dataflowname> not suceed and error is .....
for this i need to execute dataflow one by one...can any 1 help me pls...waiting for ur valuable reply...
Thx in Advance....
Roopesh Babu V
Part Of SP Not Executing... Maybe BUG!?
May 2, 2006
Hello!
I have a strange problem in one SP. One small part of it wan't execute.
I have pasted whole procedure here and a solution to problem.
So, black on red part of code will not execute if black on blue code is in place.
But, if I supstitute black on blue with black on green, black on red part will execute!
Only thing I want to know is why is that hapening...
Regards
--SNIP--
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_FINKO_Knjizenje_Robnih]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_FINKO_Knjizenje_Robnih]
GO
CREATE procedure sp_FINKO_Knjizenje_Robnih
(
@VrDok varchar(8) = '', --Vrsta robnog dokumenta
@IDs varchar(2200) = '' --IDovi iz HeRPuSPrijenos_Knjizenja
)
AS
BEGIN
set @VrDok = rtrim(ltrim(@VrDok))
set @Ids = rtrim(ltrim(@Ids))
if @VrDok='' OR len(@IDs) = 0
BEGIN
RAISERROR ('Potrebna vrsta dokumenta i IDovi', 16, 1)
return
END
declare @vbCrLf char(2)
declare @SQL_temp nvarchar(4000)
set @vbcrlf = CHAR(13) + CHAR(10)
CREATE TABLE #tmpDokumenti (
[ID] [numeric] (18,0), [Dok_broj] [varchar] (32) COLLATE Croatian_CI_AS NULL, [AK_Skladiste] [varchar] (10) COLLATE Croatian_CI_AS NULL, [Dok_Vrsta] [varchar] (8)COLLATE Croatian_CI_AS NULL
)
set nocount ON
--Racunamo da imamo vrstu dokumenta te ajdiove te cemo sada brojeve dokumenata i skladista prebaciti u temp tablicu
set @SQL_temp= 'SELECT ID,Dok_Broj,AK_Skladiste,Dok_AK FROM HeRPuSPrijenos_Knjizenja' + @vbcrlf
set @SQL_temp= @SQL_Temp + 'WHERE (isnull(Temeljnica_Nastala,0)=0 AND Dok_OA = ''50'' AND Dok_AK=''' + @VrDok + ''')' + @vbcrlf
set @SQL_temp= @SQL_Temp + 'AND ID IN (' + @IDs + ') '
set @SQL_temp= 'SET NOCOUNT ON ' + @vbcrlf + @SQL_temp
INSERT INTO #tmpDokumenti
exec (@SQL_temp) --prebacujemo
SELECT v.oznaka_analitike
,v.Analiticki_konto
,v.Konto
,v.vrsta_dokumenta
,v.broj_dokumenta
,v.Datum_dokumenta
,sum(v.Duguje) as Duguje
,sum(v.Potražuje) as Potražuje
,v.Opis
,v.Vrsta_Temeljnice, v.Broj_Temeljnice, v.Datum_temeljnice
,v.Tecaj, v.Duguje2, v.Potražuje2
,v.Protukonto, v.Protuanalitika, v.ProtuAnliticki_konto
,v.Zatvoreno, v.Mjesec, v.Godina, v.Broj_dnevnika, v.Saldo
,v.Zatvoren_dnevnik, v.Valuta, v.IdNo, IDENTITY(int, 1,1) as rbr
,v.PodOznaka_analitike, v.Podanaliticki_konto
,v.Broj_izvornog_dokumenta, v.Vrsta_dokumentaIzvorni
,v.Broj_dokumentaIzvorni, v.Datum_dokumentaIzvorni
,v.DugujeDEV, v.PotrazujeDEV, v.NovcanaJedinica
,v.ZatvaraDokument
,v.Prijenos_ID, v.Prijenos_DokID
,v.Duguje2DEV, v.Potrazuje2DEV, v.ZatvorenoDEV
,v.Racunalo, v.Korisnik, v.DatumAzuriranja
,v.Prijenos_Dok_Dogadjaj, v.Prijenos_dok_zatvara
,v.Red_ID, v.Tem_ID,v.RVrDok as r_Vrsta_dokumenta
INTO #tmpTemeljnica
FROM vFK_RK as V INNER JOIN #tmpDokumenti as D
ON v.broj_dokumenta=d.dok_Broj AND V.AK_skladiste=D.AK_skladiste AND V.vrsta_dokumenta=@VrDok
GROUP BY v.Oznaka_analitike
,v.Analiticki_konto
,v.Konto
,v.vrsta_dokumenta
,v.broj_dokumenta
,v.Datum_dokumenta
,v.Opis
,v.Vrsta_Temeljnice, v.Broj_Temeljnice, v.Datum_temeljnice
,v.Tecaj, v.Duguje2, v.Potražuje2
,v.Protukonto, v.Protuanalitika, v.ProtuAnliticki_konto
,v.Zatvoreno, v.Mjesec, v.Godina, v.Broj_dnevnika, v.Saldo
,v.Zatvoren_dnevnik, v.Valuta, v.IdNo
,v.PodOznaka_analitike, v.Podanaliticki_konto
,v.Broj_izvornog_dokumenta, v.Vrsta_dokumentaIzvorni
,v.Broj_dokumentaIzvorni, v.Datum_dokumentaIzvorni
,v.DugujeDEV, v.PotrazujeDEV, v.NovcanaJedinica
,v.ZatvaraDokument
,v.Prijenos_ID, v.Prijenos_DokID
,v.Duguje2DEV, v.Potrazuje2DEV, v.ZatvorenoDEV
,v.Racunalo, v.Korisnik, v.DatumAzuriranja
,v.Prijenos_Dok_Dogadjaj, v.Prijenos_dok_zatvara
,v.Red_ID, v.Tem_ID,v.RVrDok--, v.RK_ID
,case --grupiranje po predznaku...
WHEN v.duguje < 0 THEN 'M'
WHEN v.potražuje < 0 THEN 'M'
ELSE 'P'
end
IF @@ERROR <>0
BEGIN
PRINT 'Greska: sp_FINKO_Knjienje_Robnih'
END
declare @Greska as bit
declare @ID_Knjiz as bigint
declare @VrDok1 as varchar(8)
declare @BrDok1 as varchar(8)
declare @Skl as varchar(8)
declare @Anal1 as varchar(8)
declare @Anal2 as varchar(8)
declare @Konto as varchar(8)
declare @Duguje as numeric(18,2)
declare @Potrazuje as numeric(18,2)
declare @SQL_Update as varchar(4000)
SET @Greska = 0 --ovdje pratimo jeli bilo kakvih gresaka a temeljnici
DECLARE rsTmp CURSOR LOCAL FAST_FORWARD READ_ONLY
FOR
SELECT d.ID,d.ak_skladiste,t.r_Vrsta_dokumenta,t.Broj_dokumenta,t.analiticki_konto,t.podanaliticki_konto,t.Konto,t.Duguje,t.Potražuje
FROM #tmpTemeljnica as t INNER JOIN #tmpDokumenti as d
ON t.r_vrsta_dokumenta=d.Dok_Vrsta and t.broj_dokumenta=d.Dok_broj
WHERE
(len(t.oznaka_analitike) = 2 and t.analiticki_konto is null)
OR
(len(t.Podoznaka_analitike) = 2 and t.Podanaliticki_konto is null)
OR
(t.Konto is null)
OR
(t.duguje=0 and t.potražuje=0)
ORDER by d.ID
OPEN rsTmp FETCH NEXT FROM rsTMP into @ID_Knjiz ,@Skl,@VrDok1,@BrDok1,@anal1,@Anal2,@Konto,@Duguje,@Potrazuje
WHILE (@@fetch_status=0)
BEGIN
IF @Anal1 IS NULL
BEGIN
BEGIN TRANSACTION T1
INSERT INTO HeRPuSPrijenos_Greske
(Knjizenje_ID,Modul,Stavka_ID,Stavka_Rbr,Broj_Greske,Opis_Greske1,Opis_Greske2,Opis_Greske3,VrijemeUpisa,Korisnik,Racunalo)
VALUES
(@ID_knjiz,'Robni prijenos',0,0,201,'Ne mogu pronaci analitiku za konto','','',getdate(),'server','server')
COMMIT TRANSACTION T1
SET @Greska=1
END
ELSE IF @Anal2 IS NULL
BEGIN
BEGIN TRANSACTION T1
INSERT INTO HeRPuSPrijenos_Greske
(Knjizenje_ID,Modul,Stavka_ID,Stavka_Rbr,Broj_Greske,Opis_Greske1,Opis_Greske2,Opis_Greske3,VrijemeUpisa,Korisnik,Racunalo)
VALUES
(@ID_knjiz,'Robni prijenos',0,0,202,'Ne mogu pronaci podanalitiku za konto','','',getdate(),'server','server')
COMMIT TRANSACTION T1
SET @Greska=1
END
ELSE IF @Konto IS NULL
BEGIN
BEGIN TRANSACTION T1
INSERT INTO HeRPuSPrijenos_Greske
(Knjizenje_ID,Modul,Stavka_ID,Stavka_Rbr,Broj_Greske,Opis_Greske1,Opis_Greske2,Opis_Greske3,VrijemeUpisa,Korisnik,Racunalo)
VALUES
(@ID_knjiz,'Robni prijenos',0,0,203,'Ne mogu pronaci konto','','',getdate(),'server','server')
COMMIT TRANSACTION T1
SET @Greska=1
END
ELSE IF @Duguje=0 AND @Potrazuje=0
BEGIN
BEGIN TRANSACTION T1
INSERT INTO HeRPuSPrijenos_Greske
(Knjizenje_ID,Modul,Stavka_ID,Stavka_Rbr,Broj_Greske,Opis_Greske1,Opis_Greske2,Opis_Greske3,VrijemeUpisa,Korisnik,Racunalo)
VALUES
(@ID_knjiz,'Robni prijenos',0,0,204,'Stavka za duguje i potrazuje ima 0','','',getdate(),'server','server')
COMMIT TRANSACTION T1
SET @Greska=1
END
FETCH NEXT FROM rsTMP into @ID_Knjiz ,@Skl,@VrDok1,@BrDok1,@anal1,@Anal2
END
CLOSE rsTmp
deallocate rsTmp
if @Greska= 0
BEGIN
declare @PojedinacniPrijenos bit
declare @Tem_vrsta varchar(8) --vrsta tekuce temeljnice
declare @Tem_broj bigint --sljedeci broj iz fin kartica
declare @Tem_Datum smalldatetime --danasnji datum
declare @Tem_ID uniqueidentifier --eto
declare @Knjiz_Mjesec numeric(18,0)
declare @Knjiz_Godina numeric(18,0)
declare @Knjiz_Dnevnik numeric(18,0)
SET @PojedinacniPrijenos=(
select isnull(osiguranje,0) FROM analiticka_konta where oznaka_analitike='50' and analiticki_konto=@VrDok
)
SET @Tem_vrsta =(
select top 1 vrsta_temeljnice from #tmpTemeljnica
)
SET @Tem_broj =(
SELECT isnull(max(dbo.ToNumeric(FK.Broj_temeljnice)),0) +1 --bilo sranje jer je broj_temeljnice varchar pa je sad ToNumeric
FROM Financijske_kartice as FK WHERE FK.Vrsta_temeljnice=@Tem_Vrsta
)
SET @Tem_Datum=CONVERT(CHAR(8),getdate(),112) --treba nam samo datum
SET @Tem_ID=newid()
SET @Knjiz_Mjesec=(
select isnull(podatak,0) from fink where segment='KNJIZIFIN' and naziv= 'Mjesec knjiženja'
)
SET @Knjiz_Godina=(
select isnull(podatak,0) from fink where segment='KNJIZIFIN' and naziv= 'Godina knjiženja'
)
SET @Knjiz_Dnevnik=(
select isnull(podatak,0) from fink where segment='KNJIZIFIN' and naziv= 'Broj dnevnika'
)
UPDATE #tmpTemeljnica SET
Broj_temeljnice =@Tem_broj
,Tem_ID =@Tem_ID
,Datum_temeljnice =@Tem_datum
,Mjesec =@Knjiz_Mjesec
,Godina =@Knjiz_Godina
,Broj_dnevnika =@Knjiz_Dnevnik
--idemo si napravit kopiju temp tablice(treba nam zbog grupnoh odnosno pojedinacnog prijenosa)
SELECT * INTO #tmpTemeljnica1
FROM #tmpTemeljnica WHERE 1=2
--idemo sad grupirat sve sto treba
SET @PojedinacniPrijenos =1 --zasad ostajemo na ovom
IF @PojedinacniPrijenos = 1
BEGIN
INSERT INTO #tmpTemeljnica1
SELECT v.oznaka_analitike
,v.Analiticki_konto
,v.Konto
,v.vrsta_dokumenta
,v.broj_dokumenta
,v.Datum_dokumenta
,sum(v.Duguje) as Duguje
,sum(v.Potražuje) as Potražuje
,v.Opis
,v.Vrsta_Temeljnice, v.Broj_Temeljnice, v.Datum_temeljnice
,v.Tecaj, v.Duguje2, v.Potražuje2
,v.Protukonto, v.Protuanalitika, v.ProtuAnliticki_konto
,v.Zatvoreno, v.Mjesec, v.Godina, v.Broj_dnevnika, v.Saldo
,v.Zatvoren_dnevnik, v.Valuta, v.IdNo
,v.PodOznaka_analitike, v.Podanaliticki_konto
,v.Broj_izvornog_dokumenta, v.Vrsta_dokumentaIzvorni
,v.Broj_dokumentaIzvorni, v.Datum_dokumentaIzvorni
,v.DugujeDEV, v.PotrazujeDEV, v.NovcanaJedinica
,v.ZatvaraDokument
,v.Prijenos_ID, v.Prijenos_DokID
,v.Duguje2DEV, v.Potrazuje2DEV, v.ZatvorenoDEV
,v.Racunalo, v.Korisnik, v.DatumAzuriranja
,v.Prijenos_Dok_Dogadjaj, v.Prijenos_dok_zatvara
,v.Red_ID, v.Tem_ID,''
FROM #tmpTemeljnica as v
GROUP by
v.oznaka_analitike
,v.Analiticki_konto
,v.Konto
,v.vrsta_dokumenta
,v.broj_dokumenta
,v.Datum_dokumenta
,v.Opis
,v.Vrsta_Temeljnice, v.Broj_Temeljnice, v.Datum_temeljnice
,v.Tecaj, v.Duguje2, v.Potražuje2
,v.Protukonto, v.Protuanalitika, v.ProtuAnliticki_konto
,v.Zatvoreno, v.Mjesec, v.Godina, v.Broj_dnevnika, v.Saldo
,v.Zatvoren_dnevnik, v.Valuta, v.IdNo
,v.PodOznaka_analitike, v.Podanaliticki_konto
,v.Broj_izvornog_dokumenta, v.Vrsta_dokumentaIzvorni
,v.Broj_dokumentaIzvorni, v.Datum_dokumentaIzvorni
,v.DugujeDEV, v.PotrazujeDEV, v.NovcanaJedinica
,v.ZatvaraDokument
,v.Prijenos_ID, v.Prijenos_DokID
,v.Duguje2DEV, v.Potrazuje2DEV, v.ZatvorenoDEV
,v.Racunalo, v.Korisnik, v.DatumAzuriranja
,v.Prijenos_Dok_Dogadjaj, v.Prijenos_dok_zatvara
,v.Red_ID, v.Tem_ID,
CASE --grupiranje po predznaku...
WHEN v.duguje < 0 THEN 'M'
WHEN v.potražuje < 0 THEN 'M'
ELSE 'P'
END
END
ELSE
BEGIN
print ''
END
INSERT INTO HeRPuSPrijenos_Temeljnica
SELECT
v.oznaka_analitike,v.Analiticki_konto
,v.Konto
,v.vrsta_dokumenta,v.broj_dokumenta,v.Datum_dokumenta
,v.Duguje,v.Potražuje
,v.Opis
,v.Vrsta_Temeljnice, v.Broj_Temeljnice, v.Datum_temeljnice
,v.Tecaj,v.Duguje2,v.Potražuje2
,v.Protukonto, v.Protuanalitika, v.ProtuAnliticki_konto
,v.Zatvoreno, v.Mjesec, v.Godina, v.Broj_dnevnika, v.Saldo
,v.Zatvoren_dnevnik, v.Valuta,v.rbr
,v.PodOznaka_analitike, v.Podanaliticki_konto
,v.Broj_izvornog_dokumenta, v.Vrsta_dokumentaIzvorni
,v.Broj_dokumentaIzvorni, v.Datum_dokumentaIzvorni
,v.DugujeDEV, v.PotrazujeDEV, v.NovcanaJedinica
,v.ZatvaraDokument
,v.Prijenos_ID, v.Prijenos_DokID
,v.Duguje2DEV, v.Potrazuje2DEV, v.ZatvorenoDEV
,v.Racunalo, v.Korisnik, v.DatumAzuriranja
,v.Prijenos_Dok_Dogadjaj, v.Prijenos_dok_zatvara
,v.Red_ID, v.Tem_ID--,v.RVrDok --, v.RK_ID
FROM #tmpTemeljnica1 as V
IF @@ERROR <> 0
BEGIN
BEGIN TRANSACTION T1
INSERT INTO HeRPuSPrijenos_Greske
(Knjizenje_ID,Modul,Stavka_ID,Stavka_Rbr,Broj_Greske,Opis_Greske1,Opis_Greske2,Opis_Greske3,VrijemeUpisa,Korisnik,Racunalo)
VALUES
(0,'Robni prijenos',0,0,205,'Greska prilikom prebacivanja podataka u HeRPuSPrijenos_Temeljnica','','',getdate(),'server','server')
COMMIT TRANSACTION T1
SET @Greska=1
END
END
-- DROP TABLE #tmpDokumenti --necemo dropat jel ce server dropat nakon zavrsetka procedure
-- DROP TABLE #tmpTemeljnica
-- DROP TABLE #tmpTemeljnica1
IF @Greska=0
BEGIN
set @SQL_Update='update HeRPuSPrijenos_Knjizenja set Temeljnica_Nastala=1, Temeljnica_Greska=0 Where ID IN (' + @vbcrlf
set @SQL_Update=@SQL_Update + @IDs + ') AND Dok_AK=''' + @VrDok + '''' + @vbcrlf
EXEC (@SQL_Update)
UPDATE robne_kartice SET Knjizenje_financijsko=1 WHERE ID IN --updejta robne kartice na knjizeno
(
SELECT v.RK_ID
FROM vfk_rk as v INNER JOIN #tmpDokumenti as D
ON v.broj_dokumenta=d.dok_Broj AND V.AK_skladiste=D.AK_skladiste AND V.vrsta_dokumenta=@VrDok
)
END
ELSE
BEGIN
set @SQL_Update='update HeRPuSPrijenos_Knjizenja set Temeljnica_Nastala=0, Temeljnica_Greska=1 Where ID IN (' + @vbcrlf
set @SQL_Update=@SQL_Update + @IDs + ') AND Dok_AK=''' + @VrDok + '''' + @vbcrlf
EXEC (@SQL_Update)
END
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
--SNIP--
update HeRPuSPrijenos_Knjizenja set Temeljnica_Nastala=1, Temeljnica_Greska=0 Where
ID IN (select ID from #tmpDokumenti)
Query Not Executing.
Mar 13, 2008
hello,
Here is the query.
select Count(*)
from
(select Datepart(yyyy,Activitydate) [Year], Month(ActivityDate) [Month] ,DataCenter, Farm
from Daily
where DATEPART(yyyy, ActivityDate) = DATEPART(yyyy, getdate()) and Month(ActivityDate) = 3
Group By Datepart(yy,Activitydate), Month(ActivityDate), DataCenter, Farm
) A
INNER JOIN
(SELECT *
from Monthly
where Year = DATEPART(yyyy, getdate())
) B
ON
A.Year = B.Year and
A.Month = B.Month and
A.DataCenter = B.DataCenter and
A.Farm = B.Farm
Above query executes fine giving output as 3519; that too in 1sec.
However when i try to apply criteria on Monthly table of Month=3, query just keeps executing for hours..
Following query does not work.
select Count(*)
from
(select Datepart(yyyy,Activitydate) [Year], Month(ActivityDate) [Month] ,DataCenter, Farm
from Daily
where DATEPART(yyyy, ActivityDate) = DATEPART(yyyy, getdate()) and Month(ActivityDate) = 3
Group By Datepart(yy,Activitydate), Month(ActivityDate), DataCenter, Farm
) A
INNER JOIN
(SELECT *
from Monthly
where Year = DATEPART(yyyy, getdate()) and [Month] = 3
) B
ON
A.Year = B.Year and
A.Month = B.Month and
A.DataCenter = B.DataCenter and
A.Farm = B.Farm
I am confused on what could be the problem.
Please suggest.
Thanks
Sandeep.
Executing Trigger
Nov 28, 2007
Hi Everybody,
I am new to ms sql server i had small proublem
i am created Trigger it will work so fine command successfully
how can i executed that Trigger plz help
Regards
subu
Executing DTS From Within SSIS
Sep 13, 2006Hi All,
I have a scenario where I am executing bunch of DTS packages from within an SSIS package. These packages are saved as structured files and everything works fine. However I am concerned about one thing which is whether these SSIS/DTS packges will run fine on my production server where I dont have any SQL SERVER 2000 /2005 installed , it just has SSIS installed on it. Appreciate all help.
Thanks
Job Is Not Executing In Parallel
Oct 22, 2006I just thought I would share this info:
I created the package to download 4 ftp files at once.
I set the MaxConcurrentExecutables for the SSIS package to 4. So in BIDS in downloads 4 files at the same time.
However, when I started the job I noticed that only 3 files were downloaded at the time (looking at temp files in download directory)
Solution:
Sure enough after digging around for awhile - in Step properties for SSIS package - there is execution tab - and "Maximum Concurrent Executables" was -1 (which for some reason defaults to 3 concurrent processes even on our dual CPU server) - so after chanign that value to 4 - tada - all 4 files in parallel
Trigger Not Executing
Aug 13, 2006I will try to keep this as simple as possible and make a long story short. The college I work for has bought a third party package to basically run the operations of the school. There is a component for registration, admissions, billing, etc. It also has a web component so students can access their school information from anywhere. The backend for all of this is SQL Server. To make things easy, the vendor has provided us with the steps to bulk load users, via DTS, into the system and create their accounts for the web portion. The way this works is as follows:
I create a CSV file which I then load via DTS into their "Bulk Load" table. This table has an "on insert" trigger that fires off when the load happens on the "Bulk Load" table. The trigger verifies that the user exists in the system, encrypts the password I provide in the file and inserts the record into an "Accounts" table. This "Accounts" table is where the system checks when a user logs in. Everything works fine on our test server, but in live, the trigger does not execute. The DTS package executes and the "Bulk Load" table gets updated, but the "Accounts" table does not. My file has 382 valid students and again, it worked fine on the test server.
Differences between the servers:
1. The DTS packages are the same, except for the server they connect to.
2. The test server is stand alone, while the production server is clustered.
What I tried:
1. A ran the alter statement to enable the trigger thinking it might be disabled.
2. Dropped the trigger on production and re-created it with the trigger from our test server.
3. Tried to run a trace (I had never done that before). Noticed some sp's running, but none of them were sp's found in the trigger.
Any thoughts or ideas as to what I can check? Do triggers have certain permissions and if so, where can I make sure I have the permissions. I should have full access under my account as should the account that SQL Server Agent is logged in as.
Thanks for any help.
Pete
Executing Procedure
May 13, 2008
Hi,
I have StoredProcedure with 2 Parameters. I want to execute this procedure in SQLSERVER Query Builder.
I know without using parameters to execute. i.e EXEC pro_demo
But now i have with parameters.
How to pass that parameters in a Query Builder?