Hello again, hope this post is not offensive to the newsgroup.
I am just wondering what is an efficient method of invoking external code from SQL server. I do not want to load assemblies in SQL, I want to run something outside. So far options considered:
1. Windows Service - loop and check for a specified condition in database.
2. Web Service Listener - use SQL CLR to call the web service.
3. Queueing mechanism. Use MSMQ and widows service (similar 1) [probably reliant on CLR to send messages].
4. Service Broker - similar to 3. but message handling is SQL internal and in transaction.
All of the above are listeners in a way, could there be something that relies on event handlers - a stab in the dark.
It runs fine on my machine; however, on anyone elses machine "result" comes back as "failure". We have figured out that it is loading the package and variables fine but failing before the first step of the package. Does anyone know why this would be? Or how to fix it? I am totally stumped considering it works fine on my machine.
I need to run stored procs based on a list in a code table. In other words, it reads the name of a stored proc from a table and runs it and does this for all the rows in the table. I think the ForEach loop container will do what I need and there is an ADO enumerator option but the documentation does not tell you how to use this. From what I can tell, you need to get a dataset into an SSIS variable first and then you plug the variable name into the ForEach ADO enumerator. Is that correct? If so, can someone tell me how to get a dataset into a variable?
When I run my report from within visual studio 2005 it generates just fine.
However, when I run the report from the reporting services local web site I get the following error. What do I need to do to fix this (temporarily turning off .net security uusing caspol didn't work).
An error occurred while executing OnInit: Request for the permission of type 'System.Security.Permissions.EnvironmentPermission, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed
This is the code which I have written in code window.
Public Shared Function CalcLocalFactor(ByVal CalcLifeCode As Integer, ByVal CalcFiscalAge As Integer, ByVal CalcLifeYearsUsed As Double, ByVal CalcLocConvention As String, ByVal CalcSRate As Integer) As Double Dim locCalcFiscalAge As Integer = 0 Dim locFactor As Double= 1.0 Dim locFactor1 As Double = 1.0 Dim REM1 As Integer = 1 Dim DEP As Double = 0 Dim YR As Integer Dim HALF_YEAR As Double Dim LINEAR As Double Dim MACR As Double If CalcFiscalAge > CalcLifeCode + 1 Then locCalcFiscalAge = 0 locFactor = 1.0 End If If (CalcLocConvention <> "HALF-YEAR" And CalcLifeYearsUsed < CalcLifeCode) Then locFactor = Math.Round((CalcLifeYearsUsed / CalcLifeCode), 4) End If If (CalcLocConvention = "HALF-YEAR") Then for YR = 1 to CalcFiscalAge step 1 If YR = CalcLifeCode + 1 Then locFactor1 = 1 Exit For End If If (YR = 1 Or YR = CalcFiscalAge) Then HALF_YEAR = 2 Else HALF_YEAR = 1 End If LINEAR = Math.Round(REM1 / (CalcLifeCode - YR + 1.5) / HALF_YEAR, 4) MACR = Math.Round(REM1 / CalcLifeCode * CalcSRate / HALF_YEAR, 4) If MACR >= LINEAR Then DEP = MACR Else DEP = LINEAR End If locFactor1 = locFactor1 + DEP REM1 = 1 - locFactor1 locFactor = locFactor1 Next End If Return locFactor End Function
I'm calling this code in a Report Parameter like below:
It is working fine for the first record where as for other records, the value is not getting changed. i.e. the first records value is coming repeatedly for all other records also.
How can I dynamically change the parameter values of the function?
Parameter is not accepting directly the field names, hence I used other parameter to initialize the field and used that parameter for this.
Ex. Parameter Name ; FieldPurchDate (internal) FieldName : PURCHDATE Other parameter: FieldInDate (internal) FieldName : InDate
While initializing the new parameter CalcPurchDate,, I used an expression for this: Parameters!CalcFiscalAge.Value
=iif(Parameters!FieldPurchDate.Value is nothing, Parameters!FieldInDate.Value,Parameters!FieldPurchDate.Value)
and using this CalcPurchDate for processing of the parameter:
Hi all--I'm trying to convert a function which I inherited from a SQL Server 2000 DTS package to something usable in an SSIS package in SQL Server 2005. Given the original code here: Function Main() on error resume next dim cn, i, rs, sSQL Set cn = CreateObject("ADODB.Connection") cn.Open "Provider=sqloledb;Server=<server_name>;Database=<db_name>;User ID=<sysadmin_user>;Password=<password>" set rs = CreateObject("ADODB.Recordset") set rs = DTSGlobalVariables("SQLstring").value
for i = 1 to rs.RecordCount sSQL = rs.Fields(0).value cn.Execute sSQL, , 128 'adExecuteNoRecords option for faster execution rs.MoveNext Next
Main = DTSTaskExecResult_Success
End Function
This code was originally programmed in the SQL Server ActiveX Task type in a DTS package designed to take an open-ended number of SQL statements generated by another task as input, then execute each SQL statement sequentially. Upon this code's success, move on to the next step. (Of course, there was no additional documentation with this code. :-)
Based on other postings, I attempted to push this code into a Visual Studio BI 2005 Script Task with the following change:
public Sub Main()
...
Dts.TaskResult = Dts.Results.Success
End Class
I get the following error when I attempt to compile this:
Error 30209: Option Strict On requires all variable declarations to have an 'As' clause.
I am new to Visual Basic, so I'm on a learning curve here. From what I know of this script: - The variables here violate the new Option Strict On requirement in VS 2005 to declare what type of object your variable is supposed to use.
- I need to explicitly declare each object, unless I turn off the Option Strict On (which didn't seem recommended, based on what I read).
Given this statement:
dim cn, i, rs, sSQL
I'm looking at "i" as type Integer; rs and sSQL are open-ended arrays, but can't quite figure out how to read the code here:
This code seems to create an instance of a COM component, then pass provider information and create the recordset being passed in by the previous task, but am not sure whether this syntax is correct for VS 2005 or what data type declaration to make here. Any ideas/help on how to rewrite this code would be greatly appreciated!
Table 1: AddressBook Fields --> User Name, Address, CountryCode
Table 2: Country Fields --> Country Code, Country Name
Step 1 : I have created a Cube with these two tables using SSAS.
Step 2 : I have created a report in SSRS showing Address list.
The Column in the report are User Name, Address, Country Name
But I have no idea, how to convert this Country Code to Country name.
I am generating the report using the Layout tab. ( Data | Layout | Preview ) Report1.rdl [Design]
Anyone help me to solve this issue. Because, in our project most of the transaction tables have Code and Code description in master table. I need to convert all code into corresponding description in all my reports.
Hello, I'm using ASP.Net to update a table which include a lot of fields may be around 30 fields, I used stored procedure to update these fields. Unfortunatily I had to use a FormView to handle some TextBoxes and RadioButtonLists which are about 30 web controls. I 've built and tested my stored procedure, and it worked successfully thru the SQL Builder.The problem I faced that I have to define the variable in the stored procedure and define it again the code behind againALTER PROCEDURE dbo.UpdateItems ( @eName nvarchar, @ePRN nvarchar, @cID nvarchar, @eCC nvarchar,@sDate nvarchar,@eLOC nvarchar, @eTEL nvarchar, @ePhone nvarchar, @eMobile nvarchar, @q1 bit, @inMDDmn nvarchar, @inMDDyr nvarchar, @inMDDRetIns nvarchar, @outMDDmn nvarchar, @outMDDyr nvarchar, @outMDDRetIns nvarchar, @insNo nvarchar,@q2 bit, @qper2 nvarchar, @qplc2 nvarchar, @q3 bit, @qper3 nvarchar, @qplc3 nvarchar, @q4 bit, @qper4 nvarchar, @pic1 nvarchar, @pic2 nvarchar, @pic3 nvarchar, @esigdt nvarchar, @CCHName nvarchar, @CCHTitle nvarchar, @CCHsigdt nvarchar, @username nvarchar, @levent nvarchar, @eventdate nvarchar, @eventtime nvarchar ) AS UPDATE iTrnsSET eName = @eName, cID = @cID, eCC = @eCC, sDate = @sDate, eLOC = @eLOC, eTel = @eTEL, ePhone = @ePhone, eMobile = @eMobile, q1 = @q1, inMDDmn = @inMDDmn, inMDDyr = @inMDDyr, inMDDRetIns = @inMDDRetIns, outMDDmn = @outMDDmn, outMDDyr = @outMDDyr, outMDDRetIns = @outMDDRetIns, insNo = @insNo, q2 = @q2, qper2 = @qper2, qplc2 = @qplc2, q3 = @q3, qper3 = @qper3, qplc3 = @qplc3, q4 = @q4, qper4 = @qper4, pic1 = @pic1, pic2 = @pic2, pic3 = @pic3, esigdt = @esigdt, CCHName = @CCHName, CCHTitle = @CCHTitle, CCHsigdt = @CCHsigdt, username = @username, levent = @levent, eventdate = @eventdate, eventtime = @eventtime WHERE (ePRN = @ePRN) and the code behind which i have to write will be something like thiscmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@eName", ((TextBox)FormView1.FindControl("TextBox1")).Text);cmd.Parameters.AddWithValue("@ePRN", ((TextBox)FormView1.FindControl("TextBox2")).Text); cmd.Parameters.AddWithValue("@cID", ((TextBox)FormView1.FindControl("TextBox3")).Text);cmd.Parameters.AddWithValue("@eCC", ((TextBox)FormView1.FindControl("TextBox4")).Text); ((TextBox)FormView1.FindControl("TextBox7")).Text = ((TextBox)FormView1.FindControl("TextBox7")).Text + ((TextBox)FormView1.FindControl("TextBox6")).Text + ((TextBox)FormView1.FindControl("TextBox5")).Text;cmd.Parameters.AddWithValue("@sDate", ((TextBox)FormView1.FindControl("TextBox7")).Text); cmd.Parameters.AddWithValue("@eLOC", ((TextBox)FormView1.FindControl("TextBox8")).Text);cmd.Parameters.AddWithValue("@eTel", ((TextBox)FormView1.FindControl("TextBox9")).Text); cmd.Parameters.AddWithValue("@ePhone", ((TextBox)FormView1.FindControl("TextBox10")).Text); cmd.Parameters.AddWithValue("@eMobile", ((TextBox)FormView1.FindControl("TextBox11")).Text); So is there any way to do it better than this way ?? Thank you
Hi,I need some help here. I have a SELECT sql statement that will query the table. How do I get the return value from the sql statement to be assigned to a label. Any article talk about this? Thanks geniuses.
My service broker is working with 2 different instances in local server.But could not able to get working on 2 different servers because of Conversation ID cannot be associated with an active conversation error which I have posted.
After I receive the message successfully...in the end I get this message sent...
Recently in an SSIS package I am getting the following error for a particular Data flow task.
Error: 2008-01-25 12:01:48.58
Code: 0xC0202009
Source: Import Datasynapse Data User Events Source [3017]
Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x8000FFFF.
End Error
Error: 2008-01-25 12:01:48.73
Code: 0xC004701A
Source: Import Datasynapse Data DTS.Pipeline
Description: component "User Events Source" (3017) failed the pre-execute phase and returned error code 0xC0202009.
End Error
Our guess is when the data size of User Events table is more it throws this error. If we try to transfer small subset of data it succeeds. What could be reason for this error?
Since this is very urgent, immediate response would be very much appreciated.
Hi all, i have a DTS package that i call from a C# app, i had it working great until i decided to use an activeX script to do the data transformations instead of the row copy. I need to use ActiveX to add a standard name to the last column in the destination table. the problem is the task is executing without errors (from c#) but nothing is happening, its failing silently. If i modify the Data Transformation back to a standard column mapping (with separate DTSTransformations for each column) it works fine, but as soon as i use activeX to handle the transformations it doesn't work. Can anyone tell me what i may be doing wrong. heres the calling code from C# if(f.Name.Substring(13,7).ToLower() == "product") { try { activity.Log("Starting Product DTS Package..."); DTS.Package2Class package = new DTS.Package2Class(); object pVarPersistStgOfHost = null; package.LoadFromSQLServer( "192.168.8.8", "username", "thepassword", Microsoft.SqlServer.DTSPkg80.DTSSQLServerStorageFlags.DTSSQLStgFlag_Default, null, null, null, (string)ConfigurationSettings.AppSettings["productDTSPackage"], ref pVarPersistStgOfHost); package.GlobalVariables.Item(1).Value = f.FullName.ToString(); package.Execute(); package.UnInitialize(); //force release of COM object System.Runtime.InteropServices.Marshal.ReleaseComObject(package); package = null; } catch(Exception e) { activity.Log(string.Format("Failureprocessing {0}",WorkingPath + f.Name) +" - "+ e.Message); } and the activex i tried to use for the transformations is: '**********************************************************************' Visual Basic Transformation Script'************************************************************************' Copy each source column to the destination columnFunction Main() DTSDestination("Yesmail Id") = DTSSource("product series") DTSDestination("Customer CKM CustId") = DTSSource("product family") DTSDestination("Product SKU") = DTSSource("transaction date") DTSDestination("product model name") = DTSSource("serial number") DTSDestination("serial number") = DTSSource("product model name") DTSDestination("transaction date") = DTSSource("Product SKU") DTSDestination("product family") = DTSSource("Customer CKM CustId") DTSDestination("product series") = DTSSource("Yesmail Id") DTSDestination("fileName") = DTSGlobalVariables("sourceFile").Value <-- this is why im using the activex for this field to get a Global Variable. Main = DTSTransformStat_OKEnd Functionthe weird thing is the PACKAGE runs fine from ENTERPRISE MANAGER with this activex, it just doesn't do it from my Calling app, perhaps i have missed something i need to change in the package constructor? BTW: i do have my assembly signed in C# for the COM wrapper.thanks in advance, mcm
Hello, I have done extensive work with Classic ASP for 9 years now. Working with Stored Proceedures etc However now am working in c# am adviced by a friend that its best to use the 1. The "Object data source" to call SP 2. Are there any documentations pointers on best practices, how its done ? I want to use the "on click event" in my class file. thanks Ehi
Hello, are there any sample codes that show you how to execute a SP in .net 1. Using a class 2. Calling the class in the onlick_button function ? thanks Ehi
I'm developing a new Stored Proc that will be taking information in to enter customer and order info. Looking at the current Stored Proc its using alot of If statements and I'm thinking of breaking the sp up into different Stored Procs and calling them all from One main stored proc. I know how to do this but I was wondering how it would effect performance. So should I simply keep all the stuff in one proc or module it out into multiple ones to make it easier to follow and read?
I'm working in SQL Server 2005. I have an existing SP that does exactly what I need (it's the aspnet_UsersInRoles_IsUserInRole SP). I want to reuse this SP and use it's return value as a field in the SP I'm writing. How do I go about doing this? I could take the logic out of the called SP and wrap it in a function; but, I would really like to reuse the SP aspnet_UsersInRoles_IsUserInRole. Thanks in advance for any assistance.
hi, i'm trying to call a dts package from vb.net. i got 2 examples which both don't work. first one gives me a [DBNETLIB][ConnectionOpen(Connect()).]SQL Server does not exist or access denied error. source code:Dim serverName As String = "SERVERNAME"Dim oPackage As New DTS.Package()Dim oStep As DTS.StepDim pVarPersistStgOfHost As Object = Nothing oPackage.LoadFromSQLServer(serverName, "USERID", "PASSWORD", DTSSQLServerStorageFlags.DTSSQLStgFlag_Default, _ "DTSPASSWORD", Nothing, Nothing, "DTSPACKAGENAME", pVarPersistStgOfHost) For Each oStep In oPackage.Steps oStep.ExecuteInMainThread = TrueNext oPackage.Execute() Dim err As LongDim source, description, message As StringFor Each oStep In oPackage.StepsIf oStep.ExecutionResult = DTSStepExecResult.DTSStepExecResult_Failure Then oStep.GetExecutionErrorInfo(err, source, description) message = String.Format("ErrorCode: {0}, Source: {1}, Description: {2}", err.ToString(), source, description)Else message = "Success"End IfNext oPackage.UnInitialize()oPackage = Nothing second example tries to create a dts package dynamically. this time i get the error that the CustomTask can not be casted, somehting about the QueryInterface. source code:Dim oPackage As New Package2()Dim oConnection As Connection2Dim oStep As Step2Dim oTask As TaskDim oCustomTask As BulkInsertTask oConnection = oPackage.Connections.[New]("SQLOLEDB")oStep = oPackage.Steps.[New]()oTask = oPackage.Tasks.[New]("DTSBulkInsertTask")oCustomTask = CType(oTask.CustomTask, BulkInsertTask) <-- error With oConnection .Catalog = "CATALOG" .DataSource = "SERVERNAME" .ID = 1 .UseTrustedConnection = True .UserID = "USERID" .Password = "PASSWORD"End With oPackage.Connections.Add(oConnection)oConnection = Nothing With oStep .Name = "InsertGemal" .ExecuteInMainThread = TrueEnd With With oCustomTask .Name = "InsertGemal" .DataFile = "D:ImportGemal.dat" .ConnectionID = 1 .DestinationTableName = "Gemal" .FieldTerminator = ";" .RowTerminator = " "End With oStep.TaskName = oCustomTask.Name With oPackage .Steps.Add(oStep) .Tasks.Add(oTask) .FailOnError = TrueEnd With oPackage.Execute() oPackage.UnInitialize()oPackage = Nothing any help highly appreciated! t.i.a.,ratjetoes.
Hello, I have the class below. And trying to execute it on a button click event. What am i doing wrong ? Thanks Here is the button click event1 protected void Button1_Click(object sender, EventArgs e) 2 { 3 signup_data_entry signup = new signup_data_entry(); 4 signup.signup_data_entry(); 5 6 }
Here is my class file. please advice 1 public class signup_data_entry 2 { 3 public signup_data_entry() 4 { 5 //SqlConnection con = new SqlConnection("cellulant_ConnectionString"); 6 SqlConnection con = new SqlConnection(ConfigurationManager.AppSettings["cellulant_ConnectionString"]); 7 8 SqlCommand command = new SqlCommand("Cellulant_Users_registration", con); 9 command.CommandType = CommandType.StoredProcedure; 10 11 con.Open(); 12 13 //string IP = new string(); 14 15 16 command.Parameters.Add(new SqlParameter("@RegionID", SqlDbType.Int, 0, "RegionID")); 17 command.Parameters.Add(new SqlParameter("@RegionDescription", SqlDbType.NChar, 50, "RegionDescription")); 18 19 command.Parameters[0].Value = 4; 20 command.Parameters[1].Value = "SouthEast"; 21 22 int i = command.ExecuteNonQuery(); 23 24 25 } 26 }
I have problem in calling the SP from ASP.NET application @DefApp nvarchar(255)= '' ,@DefBusFunction nvarchar(255)= '' ,@DefImpact nvarchar(255) = '',AS Begin declare @sql nvarchar(4000)declare @whereClause nvarchar(4000)DECLARE @return_value intdeclare @sqlWhere nvarchar(4000) select @sql = 'SELECT DefApp, DefBusFunction, DefImpact FROM Def LEFT JOIN ZFunction ON (def.DefApp = ZFunction.App) AND (Def.DefBusFunction = ZFunction.BusFunction)' if @DefImpact <> '' Set @whereClause = ' where DefImpact ' SET @whereClause = @whereClause + ' = ''' + @DefImpact + '''' set @sqlWhere=@sql + @WhereClauseEXEC @sqlWhereEnd and i am calling this SP from my ASP.net application to fill the SSRS report. I have written code in ASP.NET like: sqlCmd = new SqlCommand("subbusample", conn); sqlCmd.CommandType = CommandType.StoredProcedure;sqlCmd.Parameters.Add(new SqlParameter("@DefImpact", SqlDbType.NVarChar, 255, txtValue3.Text.ToString())); sqlCmd.Parameters.Add(new SqlParameter("@DefBusFunction", SqlDbType.NVarChar, 255, txtValue1.Text.ToString())); sqlCmd.Parameters.Add(new SqlParameter("@DefApp", SqlDbType.NVarChar, 255, txtValue2.Text.ToString())); RptViewer.ProcessingMode = Microsoft.Reporting.WebForms.ProcessingMode.Remote; RptViewer.ServerReport.ReportServerUrl = new System.Uri("http://servername/ReportServer");RptViewer.ShowParameterPrompts = false; RptViewer.ServerReport.ReportPath = "/folder name/Subbu_Sample";
when i execute it, i am not able to fill the report with data.
In my shared hosting environment where I am making large volumes of database calls, I am very anxious to preserve my heavily throttled threads-I presume when I make a database call, that it is going to a separate server and any threads that server may be using are in addition to my " hosting plan ". My question (s) is, is it therefore a no-brainer to always make calls to:Dim oResult As IAsyncResult = iDBCmd.BeginExecuteReader()arWaitHandles(0) = oResult.AsyncWaitHandleiIndex = WaitHandle.WaitAny(arWaitHandles, 60000, False) i.e. run all calls to the database asynchronously? Is it really releasing a thread back to my asp.net App while it is waiting? (I.e. Is it worth doing)
I'm new to using DTS packages and I'm running into a problem. Hopefully someone can help me out. I have an ASP.NET page that needs to call a DTS package. Everything seems to be working right up until I call opkg.Execute. The package seems to run but nothing happens. After further investigation I have found what looks like a permissions issue. this is the message I'm getting" Error Number: -2147467259 {Integer}
Error Description: "The Microsoft Jet database engine cannot open the file ''. It is already opened exclusively by another user, or you need permission to view its data." {String}
This is obviously a permissions issue. I'm just not sure who the DTS package is running as and where to set the permissions.
If you have a begin tran in a sp that calls another sp and that called sp fails. can you execute a roll back from the called sp, or should it return and error which will roll back the begin tran in the original sp
Hi all, I want to call dts package from visual basic. The dts package will do a backup of the database. I am not sure how this can be achieved. Any help will be great Thanks
