Running VB Function In SQL?
Jul 23, 2005Hello,
I have a VB Function that I'd like to run in SQL Server. Is that possible?
Thanks!
Hello,
I have a VB Function that I'd like to run in SQL Server. Is that possible?
Thanks!
Hello,
I see the following problem regarding a SQL function that returns a value.
We have the following query
SELECT c.Id_Customer, c.Name
FROM t_Customers c
WHERE c.Id_Status = fn_GetParameter('ID_Status.Active')
The idea is not hardcoding the status and other values on each query, and, since SQL Server does not support the definition of constants, we have a table with many parameters and we search them.
We defined the function "WITH SCHEMABINDING" and the SQL Server recognizes it as DETERMINISTIC, so I do not understand why it executes the function as many times as records in the t_Customers table, since every time it is executed it returns the same value.
We could define a variable, assign the value returned by this function to this variable and then use it on the SELECT, but this approach is useless if we use SQL instead of stored procedures (for example, in reports from reporting / BI tools).
Any explanation about why SQL chooses to execute the function many times, and any hint regarding how to make SQL Server execute only once the function will be very appreciated.
Thanks in advance,
Lisandro.
Hi, when the following function is used within the where statement of a select statement it runs very slowly, anyone know why?
ALTER FUNCTION [dbo].[fn_GetLastDayOfLVPeriod] ( @pInputDate DATETIME )
RETURNS DATETIME
BEGIN
DECLARE @vLVDPeriod varchar(4)
DECLARE @vLVDYear varchar(4)
DECLARE @vOutputDate DATETIME
SELECT @vLVDPeriod = LVDPeriod
FROM dbo.tblMIDates
WHERE CONVERT(varchar(8), Date, 112) = CONVERT(varchar(8), @pInputDate, 112)
SELECT @vLVDyear = LVDYear
FROM dbo.tblMIDates
WHERE CONVERT(varchar(8), Date, 112) = CONVERT(varchar(8), @pInputDate, 112)
SELECT @vOutputDate = MIN(Date)
FROM dbo.tblMIDates
WHERE LVDPeriod = @vLVDPeriod
AND LVDYear = @vLVDyear
RETURN @vOutputDate
END
I am using SQL Server 2012 to improve the running time on a user defined function named "udf_GetPackagesHirearchyofNetwork_NEW". This function takes a little bit long time to run. It is called in .NET application using Microsoft Entity Framework version 5.
CREATE Function [dbo].[udf_GetPackagesHirearchyofNetwork_NEW]
(
@networkItemid int
)
RETURNS @PackageTable TABLE
(
Id int identity (1,1),
ContentItemTargetMapID int,
ContentItemMapID int,
[code]....
When I run the Microsoft tutorial for data mining I get this error when I get to the decision tree part.
I get a similar error for clustering in the same tutorial.
However, The Naive Bayes demo seems fine.
The messages said the project was built and deployed without errors.
Does anyone know how to fix the error:
TITLE: Microsoft Visual Studio
------------------------------
The tree graph cannot be created because of the following error:
'Query (1, 6) The '[System].[Microsoft].[AnalysisServices].[System].[DataMining].[DecisionTrees].[GetTreeScores]' function does not exist.'.
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%u00ae+Visual+Studio%u00ae+2005&ProdVer=8.0.50727.762&EvtSrc=Microsoft.AnalysisServices.Viewers.SR&EvtID=ErrorCreateGraphFailed&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
Query (1, 6) The '[System].[Microsoft].[AnalysisServices].[System].[DataMining].[DecisionTrees].[GetTreeScores]' function does not exist. (Microsoft OLE DB Provider for Analysis Services 2005)
------------------------------
BUTTONS:
OK
------------------------------
In my environment, there is maintenance plan configured on one of the server and while running DBCC checkdb on a database of size around 200GB, log file usage of tempdb is increasing and causing the maintenance job to fail.
What can I do to make the maintenance job run successfully, size of the tempdb database is only 50GB and recovery model is set to simple. It cannot be increased as the mount point on which it is residing is 50GB.
If I start a long running query running on a background thread is there a way to abort the query so that it does not continue running on SQL server?
The query would be running on SQL Server 2005 from a Windows form application using the Background worker component. So the query would have been started from the background workers DoWork event using ado.net. If the user clicks an abort button in the UI I would want the query to die so that it does not continue to use sql server resources.
Is there a way to do this?
Thanks
One of my stored procs, taking one parameter, is running about 2+ minutes. But if I run the same script in the stored proc with the same parameter hardcoded, the query only runs in a couple of seconds. The execution plans are different as well. Any reason why this could happen? TIA.
View 6 Replies View RelatedI have this function in access I need to be able to use in ms sql. Having problems trying to get it to work. The function gets rid of the leading zeros if the field being past dosn't have any non number characters.For example:TrimZero("000000001023") > "1023"TrimZero("E1025") > "E1025"TrimZero("000000021021") > "21021"TrimZero("R5545") > "R5545"Here is the function that works in access:Public Function TrimZero(strField As Variant) As String Dim strReturn As String If IsNull(strField) = True Then strReturn = "" Else strReturn = strField Do While Left(strReturn, 1) = "0" strReturn = Mid(strReturn, 2) Loop End If TrimZero = strReturnEnd Function
View 3 Replies View RelatedHi all,
I executed the following sql script successfuuly:
shcInLineTableFN.sql:
USE pubs
GO
CREATE FUNCTION dbo.AuthorsForState(@cState char(2))
RETURNS TABLE
AS
RETURN (SELECT * FROM Authors WHERE state = @cState)
GO
And the "dbo.AuthorsForState" is in the Table-valued Functions, Programmabilty, pubs Database.
I tried to get the result out of the "dbo.AuthorsForState" by executing the following sql script:
shcInlineTableFNresult.sql:
USE pubs
GO
SELECT * FROM shcInLineTableFN
GO
I got the following error message:
Msg 208, Level 16, State 1, Line 1
Invalid object name 'shcInLineTableFN'.
Please help and advise me how to fix the syntax
"SELECT * FROM shcInLineTableFN"
and get the right table shown in the output.
Thanks in advance,
Scott Chang
I need to know how can i incoporate the functionality of DECODE function like the one in ORACLE in mSSQL..
please if anyone can help me out...
ali
Got some errors on this one...
Is Rand function cannot be used in the User Defined function?
Thanks.
I need to be able to pass the output of a function to another function as input, where all functions involved are user-defined in-line table-valued functions. I already posted this on Stack Exchange, so here is a link to the relevant code: [URL] ...
I am fairly certain OUTER APPLY is the core answer here; there's *clearly* some way in which does *not* do what I need, or I would not get the null output you see in the link, but it seems clear that there should be a way to fool it into working.
Hi,
I wonder if there a function that i can use in the expression builder that return a value (e.g o) if the input value is null ( Like ifnull(colum1,0) )
i hope to have the answer because i need it so much.
Maylo
Can anybody know ,how can we add builtin functions(ROW_NUMBER()) of Sql Server 2005 into database library.
I get this error when i used into storeprocedure :
ROW_NUMBER() function is not recognized in store procedure.
i used MS SQL SERVER 2005 , so i think "ROW_FUNCTION()" is not in MS SQL SERVER 2005 database library.
I need to add that function into MS SQL SERVER 2005 database library.
Can anbody know how we can add that function into MS SQL SERVER 2005 database library?
I want to write function to call another function which name isparameter to first function. Other parameters should be passed tocalled function.If I call it function('f1',10) it should call f1(10). If I call itfunction('f2',5) it should call f2(5).So far i tried something likeCREATE FUNCTION [dbo].[func] (@f varchar(50),@m money)RETURNS varchar(50) ASBEGINreturn(select 'dbo.'+@f+'('+convert(varchar(50),@m)+')')ENDWhen I call it select dbo.formuła('f_test',1000) it returns'select f_test(1000)', but not value of f_test(1000).What's wrong?Mariusz
View 3 Replies View Related
Hi,
I am trying to create a inline function which is listed below.
USE [Northwind]
SET ANSI_NULLS ON
GO
CREATE FUNCTION newIdentity()
RETURNS TABLE
AS
RETURN
(SELECT ident_current('orders'))
GO
while executing this function in sql server 2005 my get this error
CREATE FUNCTION failed because a column name is not specified for column 1.
Pleae help me to fix this error
thanks
Purnima
Ok, I'm pretty knowledgable about T-SQL, but I've hit something that seems should work, but just doesn't...
I'm writing a stored procedure that needs to use the primary key fields of a table that is being passed to me so that I can generate what will most likely be a dynamically generated SQL statement and then execute it.
So the first thing I do, is I need to grab the primary key fields of the table. I'd rather not go down to the base system tables since we may (hopefully) upgrade this one SQL 2000 machine to 2005 fairly soon, so I poke around, and find sp_pkeys in the master table. Great. I pass in the table name, and sure enough, it comes back with a record set, 1 row per column. That's exactly what I need.
Umm... This is the part where I'm at a loss. The stored procedure outputs the resultset as a resultset (Not as an output param). Now I want to use that list in my stored procedure, thinking that if the base tables change, Microsoft will change the stored procedure accordingly, so even after a version upgrade my stuff SHOULD still work. But... How do I use the resultset from the stored procedure? You can't reference it like a table-valued function, nor can you 'capture' the resultset for use using the syntax like:
DECLARE @table table@table=EXEC sp_pkeys MyTable
That of course just returns you the RETURN_VALUE instead of the resultset it output. Ugh. Ok, so I finally decide to just bite the bullet, and I grab the code from sp_pkeys and make my own little function called fn_pkeys. Since I might also want to be able to 'force' the primary keys (Maybe the table doesn't really have one, but logically it does), I decide it'll pass back a comma-delimited varchar of columns that make up the primary key. Ok, I test it and it works great.
Now, I'm happily going along and building my routine, and realize, hey, I don't really want that in a comma-delimited varchar, I want to use it in one of my queries, and I have this nice little table-valued function I call split, that takes a comma-delimited varchar, and returns a table... So I preceed to try it out...
SELECT *FROM Split(fn_pkeys('MyTable'),DEFAULT)
Syntax Error. Ugh. Eventually, I even try:
SELECT *FROM Split(substring('abc,def',2,6),DEFAULT)
Syntax Error.
Hmm...What am I doing wrong here, or can't you use a scalar-valued function as a parameter into a table-valued function?
SELECT *FROM Split('bc,def',DEFAULT) works just fine.
So my questions are:
Is there any way to programmatically capture a resultset that is being output from a stored procedure for use in the stored procedure that called it?
Is there any way to pass a scalar-valued function as a parameter into a table-valued function?
Oh, this works as well as a work around, but I'm more interested in if there is a way without having to workaround:
DECLARE @tmp varchar(8000)
SET @tmp=(SELECT dbo.fn_pkeys('MyTable'))
SELECT *
FROM Split(@tmp,DEFAULT)
Hi All
Yesterday Peso was gracious enough to help me with creating function/views/sp's
I took those examples and extended what had from excel into function in SQL
however I see myself repeating certain parts of the query and i'm wondering if there is a way to call a function (in part or in whole) from another function?
Here are excerpts two functions I have:
We'll call this function UserUsage()
------------------------------------
RETURN(
SELECT ut.LastName, ut.FirstName,
CEILING(Sum(hu.session_time)/ 60000) AS [Time Spent(MIN)],
Max(hu.time_stamp) AS [Last Log Date],
pct.Title, cat.topic_name
FROM ZSRIVENDEL.dbo.UserTable ut,
ZSRIVENDEL.dbo.history_usage hu,
ZSRIVENDEL.dbo.pc_CourseTitles pct,
ZSRIVENDEL.dbo.cam_topics cat
WHERE ut.student_id = hu.student_id
AND hu.course_id = pct.CourseID
AND hu.topic_id = cat.topic_id
AND ((ut.ClientID=@ClientID)
AND (pct.ClientID=@ClientID)
AND (ut.GroupID=3400)
AND (hu.time_stamp>= @StartDate
And hu.time_stamp< @EndDate)
AND (hu.session_time<21600000))
GROUP BY ut.LastName, ut.FirstName, pct.Title, cat.topic_name
)
and will call this function UserSummary():
-----------------------------------------
RETURN (
SELECTut.LastName, ut.FirstName,
CEILING(SUM(hu.Session_Time) / 60000.0) AS [Time Spent(MIN)]
FROM ZSRIVENDEL.dbo.UserTable AS ut
INNER JOIN ZSRIVENDEL.dbo.History_Usage AS hu
ON hu.Student_ID = ut.Student_ID
WHERE ut.ClientID = @ClientID
AND ut.GroupID = 3400
AND hu.Time_Stamp >= @StartDate
AND hu.Time_Stamp < @EndDate
AND hu.Session_Time < 21600000
GROUP BY ut.LastName, ut.FirstName
)
As you can see the first part of the both query are simlar. In particular the:
SELECTut.LastName, ut.FirstName,
CEILING(SUM(hu.Session_Time) / 60000.0) AS [Time Spent(MIN)]
and also the variables @StartDate and @EndDate.
In C# it would create a method and just call that method as well as the variables. However i'm not sure how to do that with sql functions. Could someone shed some light please?
Thank you!
Hi,I have written a stored proc with some temporary tables and also useda getdate() in my stored proc. When i try to call the sproc the erroris that we can only use extended sprocs or function inside a sproc.Now if try to write the stored proc directly inside a fuction ie copypaste after changing my temp tables to tables the problem is , i get aerror invalid use of getdate in sproc.What do i do to get somethingfor my results inside a table.Thanks in advance.RVG
View 5 Replies View RelatedHello Reporting Services 2005 users or Reporting Services Team,
I have done everything to get pass this error but no luck.
My setup is :- WIndows 2003 Standared Edition SP1
Sql Server :-
Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86) Oct 14 2005 00:33:37 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
Reporting Services starting SKU: Standard.
From the Reporting Services Configuration Manager:
I can see all other things configured except Encryption Keys(blue sign)
Initialization(Grey sign) and Execution Acct(Yellow sign)
My windows Service Identity is using :
NT AuthorityNetworkService
Builtin Acct :NetworkService
Web Service Identity :-
ASP.NET Service Acct :- NT AuthorityNetworkService
DataBase Setup :- Credentials Type :- Service Credentials
I have also done everything from here thanks to Göran
http://stevenharman.net/blog/archive/2007/03/21/Avoiding-the-401-Unauthorized-Error-when-Using-the-ReportViewer-in.aspx
and
http://support.microsoft.com/default.aspx?scid=kb;en-us;896861
and
http://forum.k2workflow.com/viewtopic.php?t=619&
If you guys any solution for this please let me know.
I was wondering could this be a scale-out deployment issue ?
I also get the error when setting up the DataBase Setup :
Although saving the database connection info succeeded the The report server cannot access internal info about this deployment to determine whetherthe current con fig is valid for this editionThis could be a scale-out deployment and that the feature is not supported by this editionTo continue use a diff report server database or remove the encription keys
My Error log file is below:-
w3wp!webserver!5!16/05/2007-14:52:46:: i INFO: Reporting Web Server started
w3wp!library!5!16/05/2007-14:52:46:: i INFO: Initializing ConnectionType to '0' as specified in Configuration file.
w3wp!library!5!16/05/2007-14:52:46:: i INFO: Initializing IsSchedulingService to 'True' as specified in Configuration file.
w3wp!library!5!16/05/2007-14:52:46:: i INFO: Initializing IsNotificationService to 'True' as specified in Configuration file.
w3wp!library!5!16/05/2007-14:52:46:: i INFO: Initializing IsEventService to 'True' as specified in Configuration file.
w3wp!library!5!16/05/2007-14:52:46:: i INFO: Initializing PollingInterval to '10' second(s) as specified in Configuration file.
w3wp!library!5!16/05/2007-14:52:46:: i INFO: Initializing WindowsServiceUseFileShareStorage to 'False' as specified in Configuration file.
w3wp!library!5!16/05/2007-14:52:46:: i INFO: Initializing MemoryLimit to '60' percent as specified in Configuration file.
w3wp!library!5!16/05/2007-14:52:46:: i INFO: Initializing RecycleTime to '720' minute(s) as specified in Configuration file.
w3wp!library!5!16/05/2007-14:52:46:: i INFO: Initializing MaximumMemoryLimit to '80' percent as specified in Configuration file.
w3wp!library!5!16/05/2007-14:52:46:: i INFO: Initializing MaxAppDomainUnloadTime to '30' minute(s) as specified in Configuration file.
w3wp!library!5!16/05/2007-14:52:46:: i INFO: Initializing MaxQueueThreads to '0' thread(s) as specified in Configuration file.
w3wp!library!5!16/05/2007-14:52:46:: i INFO: Initializing IsWebServiceEnabled to 'True' as specified in Configuration file.
w3wp!library!5!16/05/2007-14:52:46:: i INFO: Initializing MaxActiveReqForOneUser to '20' requests(s) as specified in Configuration file.
w3wp!library!5!16/05/2007-14:52:46:: i INFO: Initializing MaxScheduleWait to '5' second(s) as specified in Configuration file.
w3wp!library!5!16/05/2007-14:52:46:: i INFO: Initializing DatabaseQueryTimeout to '120' second(s) as specified in Configuration file.
w3wp!library!5!16/05/2007-14:52:46:: i INFO: Initializing ProcessRecycleOptions to '0' as specified in Configuration file.
w3wp!library!5!16/05/2007-14:52:46:: i INFO: Initializing RunningRequestsScavengerCycle to '60' second(s) as specified in Configuration file.
w3wp!library!5!16/05/2007-14:52:46:: i INFO: Initializing RunningRequestsDbCycle to '60' second(s) as specified in Configuration file.
w3wp!library!5!16/05/2007-14:52:46:: i INFO: Initializing RunningRequestsAge to '30' second(s) as specified in Configuration file.
w3wp!library!5!16/05/2007-14:52:46:: i INFO: Initializing CleanupCycleMinutes to '10' minute(s) as specified in Configuration file.
w3wp!library!5!16/05/2007-14:52:46:: i INFO: Initializing DailyCleanupMinuteOfDay to default value of '120' minutes since midnight because it was not specified in Configuration file.
w3wp!library!5!16/05/2007-14:52:46:: i INFO: Initializing WatsonFlags to '1064' as specified in Configuration file.
w3wp!library!5!16/05/2007-14:52:46:: i INFO: Initializing WatsonDumpOnExceptions to 'Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException,Microsoft.ReportingServices.Modeling.InternalModelingException' as specified in Configuration file.
w3wp!library!5!16/05/2007-14:52:46:: i INFO: Initializing WatsonDumpExcludeIfContainsExceptions to 'System.Data.SqlClient.SqlException,System.Threading.ThreadAbortException' as specified in Configuration file.
w3wp!library!5!16/05/2007-14:52:46:: i INFO: Initializing SecureConnectionLevel to '0' as specified in Configuration file.
w3wp!library!5!16/05/2007-14:52:46:: i INFO: Initializing DisplayErrorLink to 'True' as specified in Configuration file.
w3wp!library!5!16/05/2007-14:52:46:: i INFO: Initializing WebServiceUseFileShareStorage to 'False' as specified in Configuration file.
w3wp!resourceutilities!5!16/05/2007-14:52:46:: i INFO: Reporting Services starting SKU: Standard
w3wp!resourceutilities!5!16/05/2007-14:52:46:: i INFO: Evaluation copy: 0 days left
w3wp!resourceutilities!5!16/05/2007-14:52:46:: i INFO: Running on 1 physical processors, 2 logical processors
w3wp!runningjobs!5!16/05/2007-14:52:46:: i INFO: Database Cleanup (Web Service) timer enabled: Next Event: 600 seconds. Cycle: 600 seconds
w3wp!runningjobs!5!16/05/2007-14:52:46:: i INFO: Running Requests Scavenger timer enabled: Next Event: 60 seconds. Cycle: 60 seconds
w3wp!runningjobs!5!16/05/2007-14:52:46:: i INFO: Running Requests DB timer enabled: Next Event: 60 seconds. Cycle: 60 seconds
w3wp!runningjobs!5!16/05/2007-14:52:46:: i INFO: Memory stats update timer enabled: Next Event: 60 seconds. Cycle: 60 seconds
w3wp!library!5!05/16/2007-14:52:48:: i INFO: Call to GetPermissions:/
w3wp!library!5!05/16/2007-14:52:48:: i INFO: Catalog SQL Server Edition = Standard
w3wp!library!5!05/16/2007-14:52:48:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerServiceUnavailableException: The Report Server Windows service 'ReportServer' is not running. The service must be running to use Report Server., ;
Info: Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerServiceUnavailableException: The Report Server Windows service 'ReportServer' is not running. The service must be running to use Report Server.
w3wp!library!5!05/16/2007-14:52:48:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerServiceUnavailableException: The Report Server Windows service 'ReportServer' is not running. The service must be running to use Report Server., ;
Info: Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerServiceUnavailableException: The Report Server Windows service 'ReportServer' is not running. The service must be running to use Report Server.
w3wp!library!5!05/16/2007-14:52:49:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerServiceUnavailableException: The Report Server Windows service 'ReportServer' is not running. The service must be running to use Report Server., ;
Info: Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerServiceUnavailableException: The Report Server Windows service 'ReportServer' is not running. The service must be running to use Report Server.
w3wp!library!5!05/16/2007-14:52:49:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerServiceUnavailableException: The Report Server Windows service 'ReportServer' is not running. The service must be running to use Report Server., ;
Info: Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerServiceUnavailableException: The Report Server Windows service 'ReportServer' is not running. The service must be running to use Report Server.
I have a post similar to this is the Getting Started thread but haven't had any responses so I'm going to ask an updated version of my question. What I need to do is run a job after a user clicks a button in my ASP page. I know I can use oDBConnection.pStoredProc to run a stored procedure, but is there a similar command I can use to run the job? Any help will be appreciated. ThanksTim ConlanIntern
View 4 Replies View RelatedI want to be able to run a DTS package when a user clicks a button on a web page. The way I thought this could be done is by having a stored procedure linked to a web button which will run the DTS. This now does not appear possible (either that or I dont know how to set up an sp).
Does anyone know how to do this or another way of running DTS from a web page.
Many Thanks
I am running a set of steps from within a SQL Server Job. The very last step is to execute a Visual FoxPro EXE. I am using something like:
'D:My DirectorySubDirmyapplication.exe'
as the last step of the job (replace single-quotes with double-quotes in the line above). The job, upon reaching this step, does not continue any further. It does not report a failure, but the EXE does not execute. I have to manually stop the job. I am writing to a status.txt from within the VFP EXE to see how far it has progressed, but the EXE does not even reach line 1.
I replaced the exe with a system command (like dir > status.txt) and that works. Is there some sort of problem with VFP apps and SQL Server?
Thank you.
Aristotle
I have a DTS package that executes 4 stored procedures. The first procedure runs fine, then on success, DTS blows through the next 3 SPs. It shows completion and success, but they are not running. I have tried breaking them out into separate SQL tasks, same result. I have run profiler and the SP is being sent, but obviously is not running. I tried to insert the SQL statements instead of the EXECUTE SP_NAME with the same results. Help me OBI-WON-KENOBEEs your my only hope.
Peter Cwik
I am running a set of steps from within a SQL Server Job. The very last step is to execute a VFP EXE. I am using something like:
'D:My DirectorySubDirmyapplication.exe'
as the last step of the job (replace single-quotes with double-quotes in the line above). The job, upon reaching this step, does not continue any further. It does not report a failure, but the EXE does not execute. I have to manually stop the job. I am writing to a status.txt from within the VFP EXE to see how far it has progressed, but the EXE does not even reach line 1.
I replaced the exe with a system command (like dir > status.txt) and that works. Is there some sort of problem with VFP apps and SQL Server?
Thank you.
Aristotle
I have the following code which successfully runs a DTS package in SQLServer v7. However when it is run against a DTS package in SQLServer2000 nothing happens. It appears to run & there are no error message but it has done nothing. The DTS package works fine when executed on its own.
Set dts_pkg = Server.CreateObject("DTS.Package")
dts_pkg.LoadFromSQLServer strServer, strUser, strPass, , , , , strDTSName
dts_pkg.Execute
Set dts_pkg = Nothing
Help much appreciated.
Thanks
Adrian
Dear Experts,
I have 3 tables:
"Dim_Date"
Date_ID
MonthNumber (Month nr... ex. jan=1)
Year (ex. Year=2007)
Year&Month (Contains both year and name of month... ex. "2007 - July")
Lets say it looks like this:
Date_ID YearAndMonth Year Month
1 "2007 - Jan" 2007 1
2 "2007 - Feb" 2007 2
3 "2007 - Feb" 2007 2
4 "2007 - Mar" 2007 3
5 "2007 - Apr" 2007 4
6 "2007 - Jan" 2007 1
7 "2007 - Jan" 2007 1
"Customer"
Customer_ID
CustomerNumber (Contains a customernr... ex. "08243")
CustomerName (Contains the Customers name... ex. "Barneys Carwash")
Could be like this:
Customer_ID CustomerNumber CustomerName
33 08243 Barneys Carwash
65 14952 Henrys Hats
"Customer_Entry"
Entry_Date_ID
Customer_ID
OriginalAmount (Contains the amount)
What I am looking for is to get the running sum (OriginalAmount) for each month...
lets say the "Customer_Entry" tables contains the following:
Entry_Date_ID Customer_ID OriginalAmount
1 33 150
2 33 100
2 33 50
3 33 200
4 33 300
4 33 200
6 65 120
6 65 140
7 65 170
Then my goal is to get the following output, for a customer with a certain CustomerNumber (not ID):
CustomerName Year&Month RunningSum
Barneys Carwash 2007 - Jan 150
Barneys Carwash 2007 - Feb 300 (150 + 100 + 50)
Barneys Carwash 2007 - Mar 500 (150 + 100 + 50 + 200)
Barneys Carwash 2007 - Apr 1000 (150 + 100 + 50 + 200 + 300 + 200)
When I did my own attemps I was using a subquery. But I keep getting dobble posts because there are more than 1 amount for 1 Entry_Date_ID.
I hope one of you can come up with a solution?
Regards Mzane
I'm trying to run a VB.NET exe from a dts.
This works when I run the DTS manually but just hangs when I schedule the DTS.
Has anyone seen this before ?
Hi.
How to check if the dts currently running or not
please help
How can i fix a job to run with particular account
Thanks,
ServerTeam
Hi
In my report I have the total column,under the total i have two sub fields:no , Row%and i have another column Cumulative total sub fields are no,***%
For the Row % under total i write like this:
=Round((Fields!Male.Value+Fields!Female.Value+Fields!Unknown.Value+Fields!Invalid.Value)/Sum(Fields!Male.Value+Fields!Female.Value+Fields!Unknown.Value+Fields!Invalid.Value)*100,2)
For the *** % under cumulative total the expression is:
=RunningValue((Fields!Male.Value+Fields!Female.Value+Fields!Unknown.Value+Fields!Invalid.Value)/Sum(Fields!Male.Value+Fields!Female.Value+Fields!Unknown.Value+Fields!Invalid.Value)*100,sum,"AgeByGender")
But i am getting this error:
The Value expression for the textbox '*** %' contains an aggregate function (or RunningValue or RowNumber functions) in the argument to another aggregate function (or RunningValue). Aggregate functions cannot be nested inside other aggregate functions.
How to get the cm % for the Cumulative total
Please help me
Thanks in advance
Is anyone doing this successfully? Were there any problems getting up andrunning? Any software patches needing to be downloaded to make this happen?We have been using Access. I know, it's cheesy, but for the simplistic datamodel we've used up until now it worked. However, we've outgrown the simpledata model and now need more full-fledged queries and stored procs).I have an old copy of SQL Server 7.0 laying around but wanted to get somefeedback from folks who have been using it with XP Pro before installing andgetting into a bunch of potential headaches. Any feedback concerning thisboth welcomed and appeciated in advance.Cheers!
View 1 Replies View Related