I have created SQL code that is contained in different procedures. I have a procedure that then runs all of the pieces that I wish to run. My last step in my process is to export the data to my c: drive. During this process I created a package to be rerun. How do I include the rerunning of my package automatically as part of the procedural process?
Joe, thank you for the answer to my post.
Can I also use a
SELECT field1 FROM DBName.dbo.TableName with a "VIEW" in this other database (that's on the same server)?
Also, in my sp, I have the following:
SELECT DISTINCT Store.[DemoID#], Progstats.ProgramName, Progstats.[Program#], ZCHAIN.STR_NAME, ZCHAIN.[STR#], ZCHAIN.ADDRESS,
ZCHAIN.City, ZCHAIN.ST, ZCHAIN.ZIP, ZCHAIN.[PHONE#], Store.D1, Store.Status, Store.AgencyCompleted,
Store.Reason, Store.LeadName, Store.DemonstratorName, Store.UpdatedOnline
FROM (Store INNER JOIN Progstats ON Store.[Program#] = Progstats.[Program#]) INNER JOIN ZCHAIN ON Store.[TD#] = ZCHAIN.[ID#]
WHERE (((Store.[DemoID#])=@DemoID)) AND Progstats.Status=1;
GO
ZCHAIN has now become this "VIEWZCHAIN" in this other database. So, could I simply relace "ZCHAIN" with "DB2.dbo.VIEWZCHAIN.STR_NAME" which is actually now a 4-part name?
Thanks,
John Wilson
I am calling a SQL Server 6.5 Stored Procedure from Access 2000 with the following code :-
Public Function CheckDigitCalc()
Dim conn As New ADODB.Connection
Dim cmd As New ADODB.Command
On Error GoTo TryAgain
conn.Open "DSN=WEB;uid=sa;pwd=;DATABASE=WEB;"
Set cmd.ActiveConnection = conn
cmd.CommandText = "SPtest2"
cmd.CommandType = adCmdStoredProc
cmd.Execute
MsgBox "Numbers created OK.", vbOKOnly
Exit Function
TryAgain:
MsgBox "Error occurred, see details below :-" & vbCrLf & vbCrLf & _
Err & vbCrLf & vbCrLf & _
Error & vbCrLf & vbCrLf, 48, "Error"
End Function
The MsgBox pops up indicating that the Stored Procedure has run, and there are no errors produced by either SQL Server or Access. However, when I inspect the results of the Stored Procedure, it has not processed all the records it should have. It appears to stop processing after between 6 and 11 records out of a total of 50. The wierd thing is that if I execute the procedure on the server manually, it works perfectly. HELP ME IF U CAN ! THANKS.
I have writen a nested stored procedure when i am passing a datetime paramter from the first procedure into the internally called procedure it is dropping the time part.
EX;
Declare @pdate datetime
Exec sp1 @pDate<this is datetime> = '2013-10-19 14:00:00'
Create proc sp1
(@pdate datetime)
as
Begin try
Exec sp2 @pdate <this is the same parameter for sp1>
End try
Begin Catch
Select 'error'
end catch
Hi,
I'm converting some direct SQL queries into stored procedures. The
original queries have conditional parts appended depending on certain
parameters like this:
Sql = "First part"
If certain condition then
Sql &= "Second part"
Endif
How could I do this in a stored procedure? So far I have made them like this:
IF @condition > 0
Fist part Second part
ELSE
First part
You can see that I have to maintain 'First part' in two locations. What is a better way of dealing with this?
Thanks!
Noc
I have SSRS 2005 SP2 configured to work in Sharepoint integration. Everything works fine except that I am not able to programmatically change any property of report viewer web part (instance) that I have added on on home page of my sharepoint site.
I can do same thing via sharepoint UI but not through program. When my programs runs it fetches all web parts been added on home page, then I need to iterate through each one and find report viewer web part.
While iterating, as soon as I arrive to report viewer web part it is named as "Error web part" with error message as
"Windows SharePoint Services cannot deserialize the Web Part. Check the format of the properties and try again"
If someone has a solution, please respond at your earlist.
Thanks
Shankar
I have a table with a column named measurement decimal(18,1). If the value is 2.0, I want the stored proc to return 2 but if the value is 2.5 I want the stored proc to return 2.5. So if the value after the decimal point is 0, I only want the stored proc to return the integer portion. Is there a sql function that I can use to determine what the fraction part of the decimal value is? In c#, I can use
dr["measurement "].ToString().Split(".".ToCharArray())[1] to see what the value after the decimal is.
I have made the following Scalar-valued function:
CREATE FUNCTION [dbo].[TimeCalc]
(
@OriginalTime AS INTEGER
, @TenthsOrHundredths AS INTEGER -- code 2: 1/10, code 4: 1/100
)
RETURNS NVARCHAR(8)
[Code] ....
What it does is convert numbers to times
E.g.: 81230 gets divided by 10 (times in seconds: 8123). This 1 1 full minute, and the remainder = 2123 making it 1.21.23 mins)
So far so good (function works perfectly)
My question: sometimes times are in 1/100 (like above sample), sometimes in 1/10.
This means that, e.g. with a time like 3.23.40 the last zero must be deleted.
My thoughts are to use the results from the Return Case part, and as the code = 4: leave it as it is,
is the code 2 the use LEFT(... result Return Case ..., Len(..result Return Case.. - 1))
There are 5 codes: 0 1 2 3 and 4
Hi, hoping someone may be able to shed some light on this.
I have a stored procedure that conditionally executes different DTS Packages. Everything seems to work, there's no errors at all, but the DTS Packages are never executed. Wondering if anyone has any ideas.
Here's the stored procedure....
CREATE PROCEDURE exportLists
AS
DECLARE @script VARCHAR(8000)
DECLARE @id INT
DECLARE @max INT
select regionid,count(distinct homephone) as phone
into #phone
from tbl_template
Where exportid is null
group by regionid
select officeid,listpath
into #office
from tbl_office
WHERE startdate <= GETDATE()
AND (enddate >= GETDATE() OR enddate IS NULL)
and listpath is not null
and listpath <> ''
Select case when phone > 2500 then '"exportLists" /A MDBFile="' + listpath + '" /A OfficeID ="' + convert(varchar(10),officeid) + '" '
Else '"resetTemplate" /A OfficeID="' + convert(varchar(10),officeid) + '" '
end as cmd
,Identity(int,1,1) as rowno
,phone
into #temp
from #office as O
inner Join #phone as p
on o.officeid=p.regionid
Set @max=@@rowcount
set @id = 1
While @id <=@max
Begin
Select @script='"Dtsrun /S "' + @@SERVERNAME + '" /E /N ' + cmd
+ case when phone > 2500
then '/A ExportID="' + convert(varchar(10),(Select coalesce(Max(listid),0) + 1 from tbl_sf3Lists)) + '" '
else ' ' end
, @id=@id+1
from #temp
Where rowno = @id
End
Return
GO
Hi Jonathan Kehayias, Thanks for your valuable response.
I had a hard time to sumbit my reply in that original thread yesterday. So I created this new thread.
Here is my response to the last code/instruction you gave me:
I corrected a small mistake (on Integrated Security-SSPI and executed the last code you gave me.
I got the following debug error message:
1) A Box appeared and said: String or binary data would be truncated.
The statement has been terminated.
|OK|
2) After I clicked on the |OK| button, the following message appeared:
This "SqlException was unhandled
String or binary data would be truncated.
The statement has been terminated."
is pointing to the "Throw" code statement in the middle of
.......................................
Catch ex As Exception
MessageBox.Show(ex.Message)
Throw
Finally
..........
Please help and advise how to correct this problem in my project that is executed in my VB 2005 Express-SQL Server Management Studio Express PC.
Thanks,
Scott Chang
The code of my Form1.vb is listed below:
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Public Class Form1
Public Sub InsertNewFriend()
Dim connectionString As String = "Data Source=.SQLEXPRESS;Initial Catalog=shcDB;Integrated Security=SSPI;"
Dim connection As SqlConnection = New SqlConnection(connectionString)
Try
connection.Open()
Dim command As SqlCommand = New SqlCommand("sp_insertNewRecord", connection)
command.CommandType = CommandType.StoredProcedure
command.Parameters.Add("@procPersonID", SqlDbType.Int).Value = 7
command.Parameters.Add("@procFirstName", SqlDbType.NVarChar).Value = "Craig"
command.Parameters.Add("@procLastName", SqlDbType.NVarChar).Value = "Utley"
command.Parameters.Add("@procAddress", SqlDbType.NVarChar).Value = "5577 Baltimore Ave"
command.Parameters.Add("@procCity", SqlDbType.NVarChar).Value = "Ellicott City"
command.Parameters.Add("@procState", SqlDbType.NVarChar).Value = "MD"
command.Parameters.Add("@procZipCode", SqlDbType.NVarChar).Value = "21045"
command.Parameters.Add("@procEmail", SqlDbType.NVarChar).Value = "CraigUtley@yahoo.com"
Dim resulting As String = command.ExecuteNonQuery
MessageBox.Show("Row inserted: " + resulting)
Catch ex As Exception
MessageBox.Show(ex.Message)
Throw
Finally
connection.Close()
End Try
End Sub
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
InsertNewFriend()
End Sub
End Class
Hi,
I am looking to create a job on our SQL Server 7.0 dB to:
1. Manipulate and move data from table 'a' to table 'b' (via stored proc)
2. Export table 'b' to a comma delimeted, double quote text identifier file (via dts export wizard stored as a DTS )
3. Cleanup table 'b' and end the job.
I am creating a DTS package to execute step 1, then 2 and lastly 3.
How do I execute another DTS package (#2) from within this DTS package?
Also, is there a way I can make the file name for #2 a dynamically generated name (i.e. include date and time in the file name)?
Thank You
Jamie Reis
Hi,
I'm running several SSIS-packages in stored procedure using dtexec. Actually, there is a software that is running the procedure, but that's not important here.
The problem: if some of the packages failes, the whole procedure does not fail and I don't know if all the packages are successfully completed.
Here is a sample list of ssis-packages from procedure:
exec xp_cmdshell 'dtexec /f "E:SSISSourcedataSourcedataaaa.dtsx"'
exec xp_cmdshell 'dtexec /f "E:SSISSourcedataSourcedatabb.dtsx" '
exec xp_cmdshell 'dtexec /f "E:SSISSourcedataSourcedataccc.dtsx"'
exec xp_cmdshell 'dtexec /f "E:SSISSourcedataSourcedataddd.dtsx"'
exec xp_cmdshell 'dtexec /f "E:SSISSourcedataSourcedataeee.dtsx"'
So, if eg bbb.dtsx would fail, is it possible to get the whole procedure failing?
Hi all,
I have a problem while i create a proxy account.The situation is like this...There is a user who has an login in to the server.He has a stored procedure which calls some on the SSIS packages and XP_cmdshell...so this stored procedure basically load some data in to the tables .So for the login in order to execute the stored procedure as he is not a Sys admin I have created a proxy account in my account as Iam an SA and then in the proxies and in principals I selected his login name and this way I have created a credential and a proxy account.
Now the problem is if he logins with his id and password and try to execute the stored procedure it gives an error message
Server: Msg 15153, Level 16, State 1, Procedure xp_cmdshell, Line 1
The xp_cmdshell proxy account information cannot be retrieved or is invalid. Verify that the '##xp_cmdshell_proxy_account##' credential exists and contains valid information.
....so this mean the login is not able to see the proxy account.So what I did is I created a job and then in the job owner tab I have selected his login and then created a step with the type operating system (CmdExec) as I need to just execute the stored procedure and used the proxy account that I have created.
so I gave the command -- exec <stored procedure> --.
But this job fails and gives the error message as
[298] SQLServer Error: 536, Invalid length parameter passed to the SUBSTRING function. [SQLSTATE 42000]....
So now ....first My question is am I doing in a right way....if its right then why Iam not able to execute the stored procedure.
If there is any other way through which I can execute the stored procedure using a proxy account for the logins who are not sys admins....please do let me know.....
Thanks
Raja.V
I've run into a problem with SSIS packages wherein tasks that write or copy files, or create or delete directories, quit execution without any hint of an error nor a failure message, when called from an ASP.NET 2.0 application running on any other machine than the one where the package was created from. By all indications it appeared to be an identity/permissions problem.
Our application involves a separate web server and database server. Both have SQL Server 2005 installed, but the application server originally only had Integration services. The packages are file system-deployed on the application server, and are called using Microsoft.SqlServer.Dts.Runtime methods. For all packages that involve file system tasks, the above problem occurs.
When the above packages are run using the command prompt (either DTEXEC or DTEXECUI) the packages execute just fine. This is expected since we are using an administrative account. However when a ShellExecute of the same command is called from ASP.NET, the same problem occurs.
I've tried giving administrative permissions to the ASPNET worker process user to no avail.
I have likewise attempted to use the SQL Server Agent job approach but that approach might not be acceptable for our clients since it means installing SQL Server 2005 Database services on the application server.
I have read the relevant threads in this forum, namely http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1044739&SiteID=1 and http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=927084&SiteID=1 but failed to find any solution appropriate for our set up.
Anybody got any idea on how to go about this?
We manage some SSIS servers, which has only SSIS and SSIS tools installed on them and not the sql server DB.
SSIS packages and configuration files are deployed on a NAS. We run the SSIS packages through DTEXEC by logging in to the server.
We want to allow developers to run their packages on their own on the server, but at the same time we dont want to give them physical access on the server i.e we do not want to add them into RDP users list on server properties. We want them to allow running their packages remotely on the server.
One way We could think of is by using powershell remoting and we are working on that. But is there any other way or any tool already present for the same.
Hi all
Our data management system currently runs DTS packages using DTSPKG.dll.
I am currently looking at the possibliity of replacing the DTS packages and SQL 2000 with DTSX packages using SSIS in SQL 2005.
Do I need a new dll? or will the current dtspkg.dll handle the new DTSX packages?
Many thanks in advance!
What different are there between connectionString (Part 1) and connectionString (Part 2) in web.config
The CCWW is my PC's name, normally I can connect to the database ASPNETDB.MDF correctly either Part 1 or Part 2 in a web page,After I open Database Explorer panel and browse ASPNETDB.MDF, I can't connect to database using Part 2 when I open a webpage in Microsoft Visual Web Developer 2005 Express Edition,but I can correctly open a webpage using Part 1 after I open Database Explorer panel.
What different are there between connectionString (Part 1) and connectionString (Part 2) in web.config?
I guess while I use Part 1 to connect, maybe it will be cancel exclusive method of the database ASPNETDB.MDF first, but when I connect to database using Part 2, maybe two programms both Part 2 and Database Explorer visit ASPNETDB.MDF at the same time!
--------------------------------------Part 1------------------------------------------------------------------------<add name="MyConnect" connectionString="Data Source=.SQLExpress;Integrated Security=True;User Instance=True;AttachDBFilename=|DataDirectory|ASPNETDB.MDF" providerName="System.Data.SqlClient" /> --------------------------------------Part 1------------------------------------------------------------------------
--------------------------------------Part 2------------------------------------------------------------------------<add name="MyConnect" connectionString="Data Source=CCWWSQLEXPRESS;Initial Catalog=ASPNETDB.MDF;Integrated Security=True;User Instance=True;AttachDBFilename=|DataDirectory|ASPNETDB.MDF" providerName="System.Data.SqlClient" />--------------------------------------Part 2------------------------------------------------------------------------
In a recent post, I've read that Jet Provider is only provided with 32-bit.
I wonder, is there any problem if you run a SSIS package 32-bit which read from Access and then call another SSIS package 64 bit?? We've got quite 2000 dts which pulling data from MDB and within a time it'll disappear in favour 2005 environment .
Another question, what sort of Providers don't offer us 64-bit versions???
Any link or thought will be as usual welcomed
Getting back way too many records with this on - over 4500 and should only be about 350.....
Here's the sql that's not working right:
SELECT c.Name, a.ID, a.Description, s.Status, b.Location, a.scheduleBegin, a.scheduleEnd
from tblEquipments a
inner join tblEquipments_Group U on u.categoryID = a.EquipmentType
inner join tblStatus S on a.status = S.statusID
and u.subCategoryID = @type
inner join tblLocation b on a.status = b.locationID
inner join tblSubCategories c on u.subCategoryID = @type
and c.Parent = @type
The table tblSubCategories has a field Name that I need.
tblSubCategories has a field, Parent, that will = @type
See this post for the other table fields if needed.
http://forums.asp.net/thread/1489880.aspx
If I omit the last inner join, all is ok, but I need that last bit of info - the c.Name.
Suggestions?
Thanks,
Zath
ok, following up on my previous post that I marked as answered a little premature. The query below works fine in sql studio:
SELECT id, CONVERT(NVARCHAR(10), arrdate,101) as formatedDate
FROM guest
but when I try to use it in a c# code behind file:
comm = new SqlCommand("SELECT id, CONVERT(NVARCHAR(10), arrdate,101) as formatedDate FROM guest WHERE id = @id", conn);
it bombs??
hi friends..... i want to get part of the database field value in stored procedure.. ex : select reason from reasontable where rid=1 output : reason-------------------------------telephone is not working properly. but i want the output as : reason--------------------------------telephone is n.............
View 3 Replies View RelatedWhich database do you point it at when you're executing the script?
The Master database?
Hi all,
Is there a difference between the DBA and the SA or are they one in the same. I was reading a previous thead and came across that the SA is the powerful God almighty and the DBA is the just God.
Thanks
Lystra
Need help..
I need to select from a text field (lastname, firstname) the first part which is the last name. The format is exactly like the parenthesis. Any ideas?
Thanx
Thanks everyone for being helpful. I am new to SQL Server and don't have many coworkers so I am relying heavily on this forum's help.
It seems the previous poster on this issue was able to work it out but I am still not able to get it to work.
I have tried using the server URL, the server machine name, the ipaddress with alias, but still cannot connect with the replication over vpn. Any ideas?
Also, how can I set the subscriber from the server? How do I refer to the subscriber machine? Let's say it is my pc that I want to make changes in and have them replicate to the server? When I specify a pull subscription I am not show how to refer to my computer.
I have a tabkle that I want to use for NT authnetication. It was a list of domain lan usernames. I want to use NT authentication in ASP but in order to do so I need to add the domain to each of the lan ds. So my username, kssensalo needs to be DOMAINkssensalo. I need to do this for 5,000+ plus records. What SQL fucntion would I use? I know I can use LTRIM to remove, but I need to add or append.
Any suggesttions?
Hi,
i am trying to write a query the pulls out a table which has date information stored as dd/mm/yyy but all i want is the month and the year.
So i tried
select datepart(mm, dtinsertdate)
from incident
which only gives me month and also used
select datepart(year,dtinsertdate) [year], datepart(month, dtinsertdate)[month],
Which works but i want both in the same column.
But when trying to get both mm, yyyy it looks like it only supports one datepart. Is there any way aroung this?
Many Thanks
John
Hi allPlease read my previous post here if this interests you:http://groups.google.co.uk/group/co...pu+usage&hl=en&I have (or rather a colleague has) found the situation that appears tobe causing this problem but I am still no further to finding asolution.It appears that if we execute a query against one particular table inthe database and that query requests data that is not indexed, or acolumn in the where clause is not indexed then this is when problemsoccur.The table cannot be copied using DTS reporting the error:"Error occurred copying row 3 - unspecified error".However, we can query using:"SELECT TOP n FROM <Table>"where n so far has been between 3 and 100000.However we cannot do SELECT * FROM <Table>.All we can guess at for now is that the problem occurs when a query isperformed to try and fetch data that is not indexed, using an index inthe where clause.i.e. SELECT <Non-indexed column> FROM <Table> WHERE <Indexed Column> =nCould it be possible that we have one 'rogue' row in the table somehowthat is causing the problem?The only thing to dispel this is that we can do the following:SELECT * FROM <Table> WHERE ID < 1which returns 1 row with ID = -1.We cannot do:SELECT * FROM <Table> WHERE ID < 200 even though we know that the onlyrecord matching this criteria is the record with ID = -1.Therefore the exact same data should be returned yet one query failsand one works.The actual problem that we see is that memory usage climbsdramatically, then once all memory is used, the CPU usage climbs to100% and stays there until we have to restart SQL Server.Any suggestions on this would be hugely appreciated.Thanks,Paul
View 4 Replies View RelatedI think I'm having problems because I don't really understand "Windows Authentication".
I've looked on the web and haven't seen any real explinations.
As an example, If I have 3 computers
1.ServerBox (xp pro) with SQL express (using Workgroups not Domains)
2 Laptop1
3 Laptop2
ServerBox and Laptop1 connected with cat5 or Wirless..Works fine.
Laptop2 connected with wirless. I see laptop2 in the serverbox "Network Places-Workgroup" on the serverbox
Laptop 1 has xp pro and uses sql authentication for "instance, db, usernane and password". Logging in is no problem (I use SQL authentication in connection string)
Laptop 2 has xp home edition and dosn't have to log in. I have the machine name, default user with no password and try Windows authentication. Does not work. error show:
Cannot open database "db1" requested by the login. I use Windows authentication connection string.
I've tested both connection strings in Laptop1 and they both work.
Does Windows Authentication read the clients login to his Windows Home Edition, and if so, does in need a password (I know its a good idea)..
So Laptop2 login to his Windows Home edition is Username=Jim and no password (shows administrator rights in his box.
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)
I created a Tabular report... The data string is
Code Snippet
SELECT dbo.forehist_txt.Item, dbo.forehist_txt.Class1, dbo.forehist_txt.Description, dbo.forehist_txt.Price, dbo.DSItem2_txt.UserDefined1,
dbo.DSItem2_txt.UserDefined3, dbo.forehist_txt.AdjustedHistory15, dbo.forehist_txt.AdjustedHistory14, dbo.forehist_txt.AdjustedHistory13,
dbo.forehist_txt.AdjustedHistory12, dbo.forehist_txt.AdjustedHistory11, dbo.forehist_txt.AdjustedHistory10, dbo.forehist_txt.AdjustedHistory9,
dbo.forehist_txt.AdjustedHistory8, dbo.forehist_txt.AdjustedHistory7, dbo.forehist_txt.AdjustedHistory6, dbo.forehist_txt.AdjustedHistory5,
dbo.forehist_txt.AdjustedHistory4, dbo.forehist_txt.AdjustedHistory3, dbo.forehist_txt.AdjustedHistory2, dbo.forehist_txt.AdjustedHistory1,
dbo.forehist_txt.SystemForecast1, dbo.forehist_txt.SystemForecast2, dbo.forehist_txt.SystemForecast3, dbo.forehist_txt.SystemForecast4,
dbo.forehist_txt.SystemForecast5, dbo.forehist_txt.SystemForecast6, dbo.forehist_txt.SystemForecast7
FROM dbo.forehist_txt INNER JOIN
dbo.DSItem2_txt ON dbo.forehist_txt.Item = dbo.DSItem2_txt.Item AND dbo.forehist_txt.Company = dbo.DSItem2_txt.Company AND
dbo.forehist_txt.Division = dbo.DSItem2_txt.Division AND dbo.forehist_txt.Corporation = dbo.DSItem2_txt.Corporation AND
dbo.forehist_txt.Sold = dbo.DSItem2_txt.Sold AND dbo.forehist_txt.Department = dbo.DSItem2_txt.Department AND
dbo.forehist_txt.ShipTo = dbo.DSItem2_txt.ShipTo
I have created two Parameters... ProductManagers (i.e. John) and Comments (i.e. Top 500).
ProductManagers is Data type: String. Multi-value is checked. I created a new dataset (ProductManagers) so that way the values are only one instance instead of several instances. Available values: From query. Default values: Null.
Comments is Data type: Sting. Multi-value is checked. I created a new dataset so that way the values are only one instance instead of several instances. Available values: From query. Default values: Null.
PROBLEM:
When I click Preview to preview the report and select the desired parameters, my report is not filtered by the parameters. It simply returns every single record.
So two Questions:
1) How do I get the report to return results based off of the parameters?
2) How do I setup the parameters so that I can filter results by John only, Top 500 only, and John OR Top 500?
Hi,
I think this is a simple question but I am just not sure how to do it right, as a newbie.
All I want to do is generate a report of company employees grouped by their date of hire.
SELECT DateOfHire, FirstName, LastName from Empls
ORDER BY DateOfHire
The only problem is that the DateOfHire displays as, e.g., '01/03/2004 12:00:00' which is not what I want. I SIMPLY WANT TO OMIT THE TIME PART but don't know quite how to say this. I tried several variations along this line but it causes scoping errors...
SELECT CONVERT(Varchar(10), DateOfHire, 101) AS DOHDateOnly, FirstName, LastName, State
FROM EMPL
GROUP BY DOHDateOnly
but this gives error
"The value expressions for the textbox ‘DateOfHire’ refers to the field ‘DOHDateOnly’. Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope."
Which makes sense, but I don't know HOW to say the right thing to get what I want.
Any help will be greatly appreciated.
Something strange just happened to my database.Windows Vista UltimateSQL Server 2005 SP2IIS 7.0I just did an insert into a few tables from my ASP.NET application that I'm building and I suddenly cannot query the database tables. The query window just spins and finally times out.So I decided to try a SELECT TOP and tried 100, nothing, then 10, nothing, then 1 and it returned the first row. Then I tried 2 and got that, and I remember this table had 8 rows prior to my last Insert, and once I tried to select TOP 9, it times out again.Does anyone knows why this happened and how to fix it? It's like every row past 8 either doesn't exist or got corrupted somehow. I can't even drop the table and rebuild it. SQL Server won't let me. If someone doesn't have a solution, I'll have to rebuild the entire database and I'd rather not have to do that.Any help would be appreciated.Thanks,Kahanu
View 2 Replies View Related