Asynchronous Outputs Technet Example Wrong Or Incomplete
Oct 22, 2007
On URL: http://technet.microsoft.com/en-us/library/ms135931.aspx among other things there is an VB example for "Creating and Configuring Output Columns" of Custom Transformation Component with Asynchronous Outputs:
Public Overrides Sub OnInputPathAttached(ByVal inputID As Integer)
Dim input As IDTSInput90 = ComponentMetaData.InputCollection.GetObjectByID(inputID)
Dim output As IDTSOutput90 = ComponentMetaData.OutputCollection(0)
Dim vInput As IDTSVirtualInput90 = input.GetVirtualInput()
For Each vCol As IDTSVirtualInputColumn90 In vInput.VirtualInputColumnCollection
Dim outCol As IDTSOutputColumn90 = output.OutputColumnCollection.New()
outCol.Name = vCol.Name
outCol.SetDataTypeProperties(vCol.DataType, vCol.Length, vCol.Precision, vCol.Scale, vCol.CodePage)
Next
End Sub
When I copy-paste given code into ScriptMain of Ascynchronous Script Transformation Component I receive error message "sub 'OnInputPathAttached' cannot be declared 'Overrides' because it does not override a sub in a base class."
View 1 Replies
ADVERTISEMENT
Jan 3, 2008
Can someone please clarify:
If you have a data file, and you only want CERTAIN rows to pass to the destination, ie) a table
and you are using a script task to accomplish this,
is this a synchronous or asynchronous transformation?
Q. And how do you assign the values to the output? Do you have to create output columns, or not?
I am very very confused right now. I can't seem to find a decent answer to what is a very basic question either in my SSIS book or in the documenation. Perhaps it is so basic, that the question doesn't seem valid? I don't know. But I just don't understand this at all.
Thank you
View 9 Replies
View Related
Mar 14, 2006
If you have an output that is not synchronous with the input what is the best way of processing the data.
I am currently using a generic queue, and a custom class. I am creating an instance of the class in the ProcessINputRow and then adding it to the Queue.
The CreateNewOutputRows Dequeues the class instances and creates buffer rows.
Is there a better solution?
View 2 Replies
View Related
May 4, 2006
Hi,
Im new to this list and after many days of trying to figure this out-here we go
Can you please tell me where I€™m going wrong in my asynchronous script component?
I€™m almost there but by using to variable iReadingCount to add the row is not quite correct. There has to be a better way !!!
Thanks in advance
Dave
I have to process a data from a staging table this which has imported a data in a structure like this, each line has a tag which is a fieldname <MyName > followed by the value
<Advice Note Number> is the Tag that tells me it is the start of the next record with the only gotca is there may be up to six <Contractor Billing> Tags in one record.
Tag Val1 Val2
<Advice Note Number> 1374239
<Customer Names> My Name
<Customer Address> My Address
<Completion Date Time> 2005/11/25 16:30:00
<Service Order Number> 123456
<Phone Number> 999535431
<Telephone Exchange> MNG
<Contractor ID> Fabrikan
<Service Order Type> F3
<Contract ID> 41
<Comments> 1 2
<Contractor Billing> 165 1
<Contractor Billing> 167 1
<Customer Signature> NO
<Advice Note Number> 1374240
<Customer Names> My Name
<Customer Address> My Address
<Completion Date Time> 2005/11/25 16:30:00
<Service Order Number> 123456
<Phone Number> 999535431
<Telephone Exchange> MNG
<Contractor ID> Fabrikan
<Service Order Type> F3
<Contract ID> 41
<Comments> 1 2
<Contractor Billing> 165 1
<Customer Signature> NO
So I need a asynchronous script component
(
Setting SynchronousInputID=0 turns your component into an asynchronous component - thus giving you access to the output buffer.)
Because I need to map this data structure like this
Input Table
CREATE TABLE [S_CAT] (
[Tag] [varchar] (8000) COLLATE Latin1_General_CI_AS NULL ,
[Val1] [varchar] (8000) COLLATE Latin1_General_CI_AS NULL ,
[Val2] [varchar] (8000) COLLATE Latin1_General_CI_AS NULL ,
[Val3] [varchar] (8000) COLLATE Latin1_General_CI_AS NULL )
GO
Desired Output Table
CREATE TABLE [S_CATM] (
[CATID] [int] IDENTITY (1, 1) NOT NULL ,
[AdviceNoteNumber] [int] NOT NULL ,
[CustomerNames] [varchar] (75) COLLATE Latin1_General_CI_AS NULL ,
[CustomerAddress] [varchar] (120) COLLATE Latin1_General_CI_AS NULL ,
[ArrivalDateTime] [smalldatetime] NULL ,
[CompletionDateTime] [smalldatetime] NULL ,
[ServiceOrderNumber] [varchar] (20) COLLATE Latin1_General_CI_AS NULL ,
[PhoneNumber] [varchar] (20) COLLATE Latin1_General_CI_AS NULL ,
[TelephoneExchange] [varchar] (20) COLLATE Latin1_General_CI_AS NULL ,
[ContractorID] [varchar] (10) COLLATE Latin1_General_CI_AS NULL ,
[ServiceOrderType] [varchar] (6) COLLATE Latin1_General_CI_AS NULL ,
[ContractID] [varchar] (20) COLLATE Latin1_General_CI_AS NULL ,
[Comments] [varchar] (160) COLLATE Latin1_General_CI_AS NULL ,
[ContractorBilling] [varchar] (10) COLLATE Latin1_General_CI_AS NULL ,
[ContractorBillingQuantity] [tinyint] NULL ,
[ContractorBilling2] [varchar] (10) COLLATE Latin1_General_CI_AS NULL ,
[ContractorBillingQuantity2] [tinyint] NULL ,
[ContractorBilling3] [varchar] (10) COLLATE Latin1_General_CI_AS NULL ,
[ContractorBillingQuantity3] [tinyint] NULL ,
[ContractorBilling4] [varchar] (10) COLLATE Latin1_General_CI_AS NULL ,
[ContractorBillingQuantity4] [tinyint] NULL ,
[ContractorBilling5] [varchar] (10) COLLATE Latin1_General_CI_AS NULL ,
[ContractorBillingQuantity5] [tinyint] NULL ,
[ContractorBilling6] [varchar] (10) COLLATE Latin1_General_CI_AS NULL ,
[ContractorBillingQuantity6] [tinyint] NULL ,
[ApprovalCode] [varchar] (20) COLLATE Latin1_General_CI_AS NULL ,
[TelecomRejectReason] [varchar] (132) COLLATE Latin1_General_CI_AS NULL ,
[ContractorRejectResponse] [varchar] (132) COLLATE Latin1_General_CI_AS NULL ,
[CustomerSignature] [char] (1) COLLATE Latin1_General_CI_AS NULL ,
[ReceivedOnTime] [varchar] (3) COLLATE Latin1_General_CI_AS NULL ,
[DateAdded] [smalldatetime] NOT NULL CONSTRAINT [DF_CATRecords_DateAdded] DEFAULT (getdate()),
CONSTRAINT [PK_CATRecords] PRIMARY KEY CLUSTERED
(
[CATID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Public Class ScriptMain
Inherits UserComponent
Dim iReadingCount As Integer = 0
Dim Comments1 As String
Dim Comments2 As String
Dim Comments3 As String
Dim AdviceNoteNumber As Integer
Dim CustomerNames As String
Dim CustomerAddress As String
Dim ArrivalDateTime As Date
Dim CompletionDateTime As Date
Dim ServiceOrderNumber As String
Dim PhoneNumber As String
Dim TelephoneExchange As String
Dim ContractorID As String
Dim ServiceOrderType As String
Dim ContractID As String
Dim Comments As String
Dim ContractorBilling As String
Dim ContractorBillingQuantity As Integer
Dim ContractorBilling2 As String
Dim ContractorBillingQuantity2 As Integer
Dim ContractorBilling3 As String
Dim ContractorBillingQuantity3 As Integer
Dim ContractorBilling4 As String
Dim ContractorBillingQuantity4 As Integer
Dim ContractorBilling5 As String
Dim ContractorBillingQuantity5 As Integer
Dim ContractorBilling6 As String
Dim ContractorBillingQuantity6 As Integer
Dim ApprovalCode As String
Dim TelecomRejectReason As String
Dim ContractorRejectResponse As String
Dim CustomerSignature As String
Dim ReceivedOnTime As String
'Public Overrides Sub CreateNewOutputRows()
'End Sub
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
Try
If StrConv(Row.Tag, VbStrConv.ProperCase) = "<Advice Note Number>" Then
AdviceNoteNumber = CInt(Trim(Row.Val1))
'Increase the reading count by 1
iReadingCount += 1
ElseIf StrConv(Row.Tag, VbStrConv.ProperCase) = "<Customer Names>" Then
CustomerNames = Left(Trim(Row.Val1 & Row.Val2), 75)
ElseIf StrConv(Row.Tag, VbStrConv.ProperCase) = "<Customer Address>" Then
CustomerAddress = Left(Trim(Row.Val1 & Row.Val2), 120)
'ElseIf Row.Tag = "<ARRIVAL Date Time>" Then
' 'ArrivalDateTime = CDate(Trim(Row.Val1))
' ArrivalDateTime = CDate(Trim(Row.Val1) & " " & Trim(Row.Val2))
'ElseIf Row.Tag = "<Completion Date Time>" Then
' 'CompletionDateTime = CDate(Trim(Row.Val1))
' CompletionDateTime = CDate(Trim(Row.Val1) & " " & Trim(Row.Val2))
ElseIf StrConv(Row.Tag, VbStrConv.ProperCase) = "<Service Order Number>" Then
ServiceOrderNumber = Left(Trim(Row.Val1), 20)
ElseIf StrConv(Row.Tag, VbStrConv.ProperCase) = "<Phone Number>" Then
PhoneNumber = Left(Trim(Row.Val1), 20)
ElseIf StrConv(Row.Tag, VbStrConv.ProperCase) = "<Telephone Exchange>" Then
TelephoneExchange = Left(Trim(Row.Val1), 20)
ElseIf StrConv(Row.Tag, VbStrConv.ProperCase) = "<Contractor Id>" Then '"<Contractor ID>"
ContractorID = Left(Trim(Row.Val1), 10)
ElseIf StrConv(Row.Tag, VbStrConv.ProperCase) = "<Service Order Type>" Then
ServiceOrderType = Left(Trim(Row.Val1), 6)
ElseIf StrConv(Row.Tag, VbStrConv.ProperCase) = "<Contract Id>" Then '"<Contract Id>"
ContractID = Left(Trim(Row.Val1), 20)
ElseIf StrConv(Row.Tag, VbStrConv.ProperCase) = "<Comments>" Then
Comments1 = Left(Trim(Row.Val1), 160)
Comments2 = Left(Trim(Row.Val2), 160)
Comments3 = Left(Trim(Row.Val3), 160)
'One Line
If Len(Comments1) > 1 And Len(Comments2) = 1 And Len(Comments3) = 1 Then
Comments = Comments1
End If
'Two Lines
If Len(Comments1) > 1 And Len(Comments2) > 1 And Len(Comments3) = 1 Then
Comments = Comments1 & " " & Comments2
End If
'Three Lines
If Len(Comments1) > 1 And Len(Comments2) > 1 And Len(Comments3) > 1 Then
Comments = Comments1 & " " & Comments2 & " " & Comments3
End If
ElseIf StrConv(Row.Tag, VbStrConv.ProperCase) = "<Contractor Billing>" Then
ContractorBilling = Left(Trim(Row.Val1), 10)
ContractorBillingQuantity = 0 'CInt(Val(Trim(Row.Val2))) 'CInt(Val(Trim(Row.Val2)))
ElseIf StrConv(Row.Tag, VbStrConv.ProperCase) = "<Contractor Billing>" Then
ContractorBilling2 = Left(Trim(Row.Val1), 10)
ContractorBillingQuantity2 = 0 'CInt(Val(Trim(Row.Val2))) 'CInt(Trim(Row.Val2))
ElseIf StrConv(Row.Tag, VbStrConv.ProperCase) = "<Contractor Billing>" Then
ContractorBilling3 = Left(Trim(Row.Val1), 10)
ContractorBillingQuantity3 = 0 'CInt(Val(Trim(Row.Val2))) 'CInt(Trim(Row.Val2))
ElseIf StrConv(Row.Tag, VbStrConv.ProperCase) = "<Contractor Billing>" Then
ContractorBilling4 = Left(Trim(Row.Val1), 10)
ContractorBillingQuantity4 = 0 'CInt(Val(Trim(Row.Val2))) 'CInt(Trim(Row.Val2))
ElseIf StrConv(Row.Tag, VbStrConv.ProperCase) = "<Contractor Billing>" Then
ContractorBilling5 = Left(Trim(Row.Val1), 10)
ContractorBillingQuantity5 = 0 'CInt(Val(Trim(Row.Val2))) 'CInt(Trim(Row.Val2))
ElseIf StrConv(Row.Tag, VbStrConv.ProperCase) = "<Contractor Billing>" Then
ContractorBilling6 = Left(Trim(Row.Val1), 10)
ContractorBillingQuantity6 = 0 'CInt(Val(Trim(Row.Val2))) 'CInt(Trim(Row.Val2))
ElseIf StrConv(Row.Tag, VbStrConv.ProperCase) = "<Approval Code>" Then
ApprovalCode = Left(Trim(Row.Val1), 20)
ElseIf StrConv(Row.Tag, VbStrConv.ProperCase) = "<Telecom Reject Reason>" Then
TelecomRejectReason = Left(Trim(Row.Val1), 132)
ElseIf StrConv(Row.Tag, VbStrConv.ProperCase) = "<Contractor Reject Response>" Then
ContractorRejectResponse = Left(Trim(Row.Val1), 132)
ElseIf StrConv(Row.Tag, VbStrConv.ProperCase) = "<Customer Signature>" Then
CustomerSignature = Left(Trim(Row.Val1), 1)
ElseIf StrConv(Row.Tag, VbStrConv.ProperCase) = "<Received On Time>" Then
ReceivedOnTime = Left(Trim(Row.Val1), 3)
End If
If iReadingCount = 1 Then
'Finally add the row
With Output0Buffer
'add a row to the output buffer
.AddRow()
'Set the values of each of our output buffer columns
.AdviceNoteNumber = AdviceNoteNumber
.CustomerNames = CustomerNames
.CustomerAddress = CustomerAddress
'.ArrivalDateTime = ArrivalDateTime
'.CompletionDateTime = CompletionDateTime
.ServiceOrderNumber = ServiceOrderNumber
.PhoneNumber = PhoneNumber
.TelephoneExchange = TelephoneExchange
.ContractorID = ContractorID
.ServiceOrderType = ServiceOrderType
.ContractID = ContractID
.Comments = Comments
.ContractorBilling = ContractorBilling
.ContractorBillingQuantity = ContractorBillingQuantity
.ContractorBilling2 = ContractorBilling2
.ContractorBillingQuantity2 = ContractorBillingQuantity2
.ContractorBilling3 = ContractorBilling3
.ContractorBillingQuantity3 = ContractorBillingQuantity3
.ContractorBilling4 = ContractorBilling4
.ContractorBillingQuantity4 = ContractorBillingQuantity4
.ContractorBilling5 = ContractorBilling5
.ContractorBillingQuantity5 = ContractorBillingQuantity5
.ContractorBilling6 = ContractorBilling6
.ContractorBillingQuantity6 = ContractorBillingQuantity6
.ApprovalCode = ApprovalCode
.TelecomRejectReason = TelecomRejectReason
.ContractorRejectResponse = ContractorRejectResponse
.CustomerSignature = CustomerSignature
.ReceivedOnTime = ReceivedOnTime
End With
iReadingCount = 0 'Reset
End If
Catch e As Exception
Me.ComponentMetaData.FireError(1, "script source", e.Message, "", 0, True)
'Finally
End Try
End Sub
View 5 Replies
View Related
Aug 23, 2000
HI ALL,
I am executing a stored procedure (SQL7) which works fine in Query Analyser, but returns only a partial result set to my ASP application:
Query:
SELECT <some fields> FROM <table> WHERE <field> IN ('<text1>','<text2>','<text3>')
My ASP renders the returned recordset, but only results for the <text1> match are displayed!
I had an idea that I may be receiving multiple recordsets from the sp, so I added:
<% Do Until rS Is Nothing
Do While Not rS.Eof %>
----render rows to table here
<% rS.MoveNext
Loop
set rS = rS.NextRecordset
Loop %>
... to no avail.
I even tried changing my SELECT to use <field>='<text1>' OR <field>='<text2>' OR <field>='<text3>'
... same problem
Anyone know where my data is 'hiding'???
Cheers,
Graeme
View 1 Replies
View Related
Jul 20, 2005
Hi SQL Server Experts,I really need some help ASAP.I thank you in advance.We have an ASP/MS SQL Server Shopping cart application,developed by anindependent developer.We really have a problem where the Order_detailsare not getting stored completely.The concerned Tables are:Table1 Order_InfoStores Order_ID,customer data,Total_Amount,Shipping details etc.(One Record per Order.)Table2 Order_detailsStores Order_ID,Product_Id,Price and Quantity.(Multiple Records per Order.Basically stores the shopping cart items,one by one.)Table3 Payment_InfoStores Order_ID and Transaction Details(One Record per Order.)Now after a customer checks out and the payment transaction has beenapproved the order details are stored this way.Step 1: First a new record is added to Table1 (Order_Info).Step 2: A 'For' loop adds new records - one for each item in the cart-to Table2(Order_details).Step 3: A new record is added to the Table3 (Payment_Info).Recently, we have been having problems with Step 2, where only apartial list of the item details are stored in the Database and Step 3is not executed at all.And we don't have the problem all the time.What could be the problem with Step 2 where a series of record areadded to a table continuosly? does this make SQL Server "too busy" sothat subsequent Add operations are not done?Can someone see an apparent problem?Any solution is greatly appeciated.Please HELP ME!Thanks.Sriram
View 6 Replies
View Related
Dec 23, 2004
I found out that I could use SQLDMO to enumerate objects in a SQL server. However, I can't connect to my server, even though I have both "windows and SQL server" authentication on "(local) Windows NT".
Here is the code (it does not contain the validation in this snippet):
Imports System.Data.SqlClient
Imports SQLDMO
Public Class ADO_2
Inherits System.Web.UI.Page
Protected WithEvents txtQuery As System.Web.UI.WebControls.TextBox
Protected WithEvents txtresults As System.Web.UI.WebControls.TextBox
Protected WithEvents lstDB As System.Web.UI.WebControls.DropDownList
Protected WithEvents lblResult As System.Web.UI.WebControls.Label
Protected WithEvents cmdExecute As System.Web.UI.WebControls.Button
Protected WithEvents cmdNext As System.Web.UI.WebControls.Button
Private m_objConn As New SqlConnection()
Private m_objSQLServer As SQLDMO.SQLServer
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
EnumerateDatabases()
'TEMP
lstDB.Items.Add("Northwind")
lstDB.Items.Add("Pubs")
'END TEMP
End Sub
Private Sub Page_Unload(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.Unload
m_objConn.Close() 'Just in case.....
End Sub
Private Sub cmdExecute_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdExecute.Click
Dim objCommand As SqlCommand
Dim objDataReader As SqlDataReader
Dim intField As Integer
txtresults.Text = String.Empty
m_objConn.ConnectionString = "server=radu;database=" & lstDB.SelectedItem.Text & ";integrated security=SSPI;"
m_objConn.Open()
objCommand = New SqlCommand(txtQuery.Text, m_objConn)
objDataReader = objCommand.ExecuteReader
While objDataReader.Read
txtresults.Text &= vbNewLine
For intField = 0 To objDataReader.FieldCount - 1
txtresults.Text &= objDataReader(intField).ToString.PadRight(15)
Next
End While
objDataReader.Close()
objDataReader = Nothing
objCommand = Nothing
m_objConn.Close()
End Sub
Private Sub EnumerateDatabases()
On Error GoTo EnumerateDatabasesError
Dim objSQLDatabase As SQLDMO.Database
m_objSQLServer = New SQLDMO.SQLServer()
m_objSQLServer.Connect("radu")
'Iterate through all databases on the target server:
For Each objSQLDatabase In m_objSQLServer.Databases
lstDB.Items.Add(objSQLDatabase.Name)
Next objSQLDatabase
EnumerateDatabasesExit:
objSQLDatabase = Nothing
m_objSQLServer.Close()
m_objSQLServer = Nothing
Exit Sub
EnumerateDatabasesError:
lblResult.Text = Err.Number.ToString & ", " & Err.Description
Resume EnumerateDatabasesExit
End Sub
End Class
Why can't I connect with m_objSQLServer.Connect("radu") ? I also tried m_objSQLServer.Connect("radu", "", ""). Otherwise, I can connect just fine with m_objConn.Open() (after I select a db from the dropdownlist, which is for now populated by hand)....
Thanks a lot for your time.
Alex.
View 1 Replies
View Related
May 11, 2007
First off, forgive the ignorance that I'm sure to display in this post. I'm a total newbie with SRSS and have been using it to follow along with the tutorials offered via the SQL Express website. I'm using it strictly for learning on my local machine for the time being.
All had been working well with Reporting Services until I upgraded Vista Ultimate 64-bit (which was running in free 30-day trial unactivated Evaluation mode) sideways to Vista Ultimate Upgrade Edition 64-bit Edition. (Sounds confusing, I know.) I also subsequently installed a SQL Server 2k5 critical update which was recommended by Windows Update.
Now when I attempt to access Report Manager via localhost the "Home page" is incomplete, showing only the SSRS "Home" page header. There are no properites, data sources, config options, etc. outside of links for "Home|Help". I am running IE as an Admin and all the Reporting Services Configuration settings are green.
Also, when I attempt to deploy from VS 2005 I now receive the error: "Permissions granted to user 'NT AuthorityNetwork Service' are insufficient ..."
Some of the settings inside the Report Sever Config are as follows:
Windows Service Identity:
Service Acct: NT AuthorityNetwork Service; Built-in Account: Network Service
Web Service Identity:
Asp.net Service Account; NT AuthorityNetwork Service; Report Server and Manager are both using Classic.Net AppPool
I've attempted for hours to weed through Google and this forum but have repeatedly wound up back where I started and more confused than when I started. I would deeply appreciate any help in resolving this matter.
View 4 Replies
View Related
Dec 29, 2006
We are experiencing a problem where a server report is rendered in the
ReportViewer control successfully but when the user chooses to print the
content, the print output is incomplete (ex. only 18 of 23 pages are printed,
etc.).
This is happening consistently and has been reproduced for multiple
different reports. It does not happen if the same report is printed via the
Report Manager web interface.
I have also found that if I switch to Print Layout mode and then initiate
the print from the ReportViewer that the problem does not occur. This is our
current workaround, but it would be nice to know what is causing this and
have it fixed.
Has anybody else seen this? Is this a known issue with my current version
(SQLRS 2005 with SP1 applied).
Thanx in advance.
View 3 Replies
View Related
Apr 13, 2000
I need to send the output of a report generated, automatically through email as an attachment to some customer.Is it possible?Can anyone help?
View 1 Replies
View Related
Oct 14, 2007
We have a legacy app where some of our web page urls were hardcoded into the stored procs (SQL 2000 SP4 database). We have changed the system and so changed the hardcoded strings with a value stored in a config table. We used the following query to identify the hardcoded urls (say LegacyPage.asp) €“
select routine_name
from information_schema.routines
where routine_definition like '%LegacyPage.asp?%'
However, even after this we keep getting issues with LegacyPage.asp being referenced. Tracing the code, I found that there is at least one SP (say spHardCoded) which does not turn up in the query, but does have the string LegacyPage.asp? in the routine definition. When I run the following query €“
select routine_name
from information_schema.routines
where
routine_name = 'spHardCoded'
and routine_definition like '%LegacyPage.asp?%'
0 rows are returned!
Am I missing something obvious here or is INFORMATION_SCHEMA.Routines metadata not always updated? Is there any way to force the metadata to be updated, before we query it? Is there a better system catalog view which lets me do the same thing? Any help would be really appreciated.
View 3 Replies
View Related
Dec 10, 2005
I have a report that I am trying to export to excel. The export seems to work but the data in the spread sheet is incomplete. The document map is completely exported but the work sheets are not. The links in the document map stop being links after the first occurrence of the lowest level detail. All the data is visible in Report services and all the data exports to PDF. There are 5 levels and 124 pages in the report in the report. I have other reports on the same server that are larger (7 levels and 512 pages) and they work just fine. They export to excel with no problem. Any ideas or input would be very appreciated. Ayla
View 8 Replies
View Related
Nov 2, 2004
Considering email is setup, how would you send emails by scanning errorlog for any issues. Also, how to send program outputs using email.
Thanks
View 4 Replies
View Related
Sep 24, 2007
Hi There,
I have been struggling day and night with the creation of a store procedure due to not being able to retieve the rows I need for SUM and AVG functions.
I have two tables ('actions' & 'incident_types') which both have a score value for each record. In my database, I have reports that contain both action codes and incident_type codes based on a personnel_code.
In simple terms I'm trying to do the following:-
For all reports, find each personnel member and thier attached incident_types and action codes and then, for each score from the actions and incident_types tables, create the SUM of the 'combined' scores.
I have successfully retrieved the output for the scores individually but I need the TOP(10) of the combined output.....
I'm currently using to seperate queries as follows :--
--This gives me the incident type output...... Creating the 'Volume' column which is the total Score for incidetn_types
SELECT Top (@Number) Personnel_details.personnel_code, Personnel_details.personnel_forename, Personnel_details.personnel_Surname, sum(incident_types.type_score) as 'Volume', avg(incident_types.type_score) as 'Average' INTO 'Incident_Scores' from Report_header
JOIN incident_types on incident_types.type_code = report_header.report_incident_Code
JOIN report_basedon on report_basedon.report_code = report_header.report_code
JOIN personnel_details on personnel_details.personnel_code = report_basedon.personnel_code
WHERE (report_header.report_date >= @fromDate and report_header.report_date <= @toDate)
AND (report_header.report_time >= @fromTime and report_header.report_time <= @toTime)
AND report_basedon.personnel_code <> 0
AND report_header.record_status=@recordStatus
group by Personnel_details.personnel_code, Personnel_details.personnel_forename, Personnel_details.personnel_Surname
-- I then use the following to get the total of all action scores, again in the 'Volume' column
SELECT Top (@Number) Personnel_details.personnel_code, Personnel_details.personnel_forename, Personnel_details.personnel_Surname, sum(actions.action_score) as 'Volume', AVG(actions.action_score) as 'Average' from profile
JOIN actions on actions.action_code = profile.action_code
JOIN report_header on report_header.report_code = profile.incident_ID
JOIN personnel_details on personnel_details.personnel_code = profile.personnel_code
WHERE (report_header.report_date >= @fromDate and report_header.report_date <= @toDate)
AND (report_header.report_time >= @fromTime and report_header.report_time <= @toTime)
AND personnel_details.personnel_code <> 0
AND report_header.record_status=@recordStatus
AND profile.record_status=@recordStatus
group by Personnel_details.personnel_code, Personnel_details.personnel_forename, Personnel_details.personnel_Surname
So my question is - How can I get the sum(incident_types.type_score) + sum(actions.action_score) and then get the TOP(10) rows?
Is it possible to output these 2 result to a new table and then join the new tables?
Thanks for any assistance, this is really draining me at the moment..... :-/
View 8 Replies
View Related
Mar 19, 2004
I have a stored procedure that I just need to return the output to my program.It is a Select All type statement.I will post my vb code that works when I use both inputs and outputs but not for all output procedure...I dont get it.
Here is the Stored Procedure.....
CREATE procedure dbo.IDXAppt_Settings_NET
(
@SQLADD nvarchar(15)Output,
@SQLDatabase nvarchar(20)Output,
@SQLLogin nvarchar(20)Output,
@SQLPass nvarchar(20)Output
)
as
select
@SQLADD=SQLAddress,
@SQLDatabase=SQLDatabase,
@SQLLogin=SQLLogin,
@SQLPass=SQLPassword
from
Clinic_Settings
GO
Here is the Vb.Net Code........
To retrieve the elements that does not give me an error just gives me no data....
Private Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim consql As New SqlConnection("server=myserver,database=APPOINTMENTS;uid=webtest;pwd=webtest")
Dim cmdsql As New SqlCommand
Dim parmSQLAddress As SqlParameter
Dim parmDatabase As SqlParameter
Dim parmLogin As SqlParameter
Dim parmSqlPass As SqlParameter
Dim strtest As String
Dim db As String
Dim login As String
Dim pass As String
cmdsql = New SqlCommand("Appt_Settings_NET", consql)
cmdsql.CommandType = CommandType.StoredProcedure
parmDatabase = cmdsql.Parameters.Add("@SQLData", SqlDbType.NVarChar)
parmDatabase.Size = 20
parmDatabase.Direction = ParameterDirection.Output
db = cmdsql.Parameters("@SQLData").Value
parmLogin = cmdsql.Parameters.Add("@SQLLogin", SqlDbType.NVarChar)
parmLogin.Size = 20
parmLogin.Direction = ParameterDirection.Output
login = cmdsql.Parameters("@SQLLogin").Value
parmSqlPass = cmdsql.Parameters.Add("@SQLPass", SqlDbType.NVarChar)
parmSqlPass.Size = 20
parmSqlPass.Direction = ParameterDirection.Output
pass = cmdsql.Parameters("@SQLPass").Value
parmSQLAddress = cmdsql.Parameters.Add("@SQLADD", SqlDbType.NVarChar)
parmSQLAddress.Size = 15
parmSQLAddress.Direction = ParameterDirection.Output
strtest = cmdsql.Parameters("@SQLADD").Value
consql.Open()
cmdsql.ExecuteNonQuery()
Label1.Text = strtest
End Sub
View 2 Replies
View Related
Oct 12, 2004
Hi everyone!
I have an stordprocedure that returns a resultset , an output value and an return value.
I invoked this procedure using an sqlcommand object ( by ExecudeReader method ) and I filled a SqlDataReader object by the resultset.
dr = cmd.ExecudeReader();
but I cant reach its output variable and its return value (cmd.parameters["@ret"].value).
an exception raises : object refrence error!
what is wrong in this approach ?
please help me.
View 3 Replies
View Related
Nov 30, 2014
Obviously Excel is the tool of choice for most people but with it's limited ability to leverage RAM (32 bit) and it's limitation with rows at just over 1 million what other choices do we have for viewing data?
My bosses boss created several OLAP universes and they seems to fly a lot fast than regular relational database. This still doesn't work with the fact the data can't be worked with unless you have a strong front end that can handle processing all those rows.
View 9 Replies
View Related
Jul 22, 2015
I have a table with email addresses and CC_Flag.
Email    | CC_Flag
xxxx          0
yyyy          1
zzzz          1
Using Task SQL, I am trying to pass these email addresses to two separate variables - To_field, Cc_field on basis of the CC_Flag. Is it possible to fill two variables from a single SQl Task.
View 8 Replies
View Related
Apr 11, 2007
Hello, I am using a multicast with 5 outputs, I attempting to pass about four thousand rows to different destinations. However, upon execution only two of the outputs send the rows to the destination. After deleting the multicast and reinserting it, different destinations received the data. The number of destinations is also not consistant, sometimes 3 work and sometimes only 1 works.
Thank you.
View 14 Replies
View Related
Mar 13, 2007
Hi,
SQL Server 2005, sp2
What I am trying to do is very simple. I just want to run 2 stored procedures, then have the output from both sp's written to a single file.
I created a Data Flow task, then put 2 OLE DB Source adapters in the Data Flow task, one for each stored procedure.
Next, I dropped a Flat File destination object onto the page. However, I can only connect ONE arrow from one of the OLE DB Source adapters to the Flat File destination. It won't let me connect both.
What am I doing wrong here? How can I accomplish what I am trying to do here?
Thanks much
View 3 Replies
View Related
Sep 25, 2006
Hi,
I wonder if someone might be able to help me with scripting a script component. I'd like to include error redirection of rows within my script.
If the conversion of any of my inputs fail i'd like to catch the error and then just output all values to another output path. The output path will just take the input values without converting them from string data types and output them to an error table.
In the script i imagine i would use try catch statements and if it fails then set the output. I am not entirely sue as to how to go about switching between outputs though.
Any help on this matter would be greatfully recieved.
Cheers,
Grant
View 4 Replies
View Related
Apr 4, 2007
I have a Sales report with 3 lists each being nested inside of the other and they also each contain a Matrix. Top list is for Region, followed by District and then State. I have page breaks setup so that each region appears on it's own page, followed by each District summary appearing at the top of the page with a breakdown on that page for that Districts states. In total the report exported to PDF or printed runs around 19 pages. This report when exported to PDF or printed works fine.
When I export it to excel I get a full document map on the first worksheet. I then get 10 worksheets of information breaking off the last 9. The document map links reflect the absence of this data by not actually being links from that point on. If I remove page breaks so that the number of pages created are less than 10 excel worksheets the export works fine and my document map links are created.
So does the export to Excel have a limitation on the number of Worksheets that can be created? In my case it appears to happen at 10 each time. Oh and by the way I've attempted this in VS.2003 and 2005 and SQL Reporting Services 2000 and 2005 for the same reports.
Thanks for your assistance.
View 1 Replies
View Related
Aug 21, 2007
Can someone please point me to one or more examples of a Source component that has two or more outputs?
I wrote a source, which works with a single output, but after adding a second output, I see no rows there. I've single-stepped the code in the debugger, and it looks like it should be adding rows to the second output, but the downstream component never sees any.
Thanks.
View 7 Replies
View Related
Feb 19, 2006
Im trying to insert a record in my sql server 2005 express database.The following function tries that and without an error returns true.However, no data is inserted into the database...Im not sure whether my insert statement is correct: I saw other example with syntax: insert into table values(@value1,@value2)....so not sure about thatAlso, I havent defined the parameter type (eg varchar) but I reckoned that could not make the difference....Here's my code: Function CreateNewUser(ByVal UserName As String, ByVal Password As String, _ ByVal Email As String, ByVal Gender As Integer, _ ByVal FirstName As String, ByVal LastName As String, _ ByVal CellPhone As String, ByVal Street As String, _ ByVal StreetNumber As String, ByVal StreetAddon As String, _ ByVal Zipcode As String, ByVal City As String, _ ByVal Organization As String _ ) As Boolean 'returns true with success, false with failure Dim MyConnection As SqlConnection = GetConnection() Dim bResult As Boolean Dim MyCommand As New SqlCommand("INSERT INTO tblUsers(UserName,Password,Email,Gender,FirstName,LastName,CellPhone,Street,StreetNumber,StreetAddon,Zipcode,City,Organization) VALUES(@UserName,@Password,@Email,@Gender,@FirstName,@LastName,@CellPhone,@Street,@StreetNumber,@StreetAddon,@Zipcode,@City,@Organization)", MyConnection) MyCommand.Parameters.Add(New SqlParameter("@UserName", SqlDbType.NChar, UserName)) MyCommand.Parameters.Add(New SqlParameter("@Password", Password)) MyCommand.Parameters.Add(New SqlParameter("@Email", Email)) MyCommand.Parameters.Add(New SqlParameter("@Gender", Gender)) MyCommand.Parameters.Add(New SqlParameter("@FirstName", FirstName)) MyCommand.Parameters.Add(New SqlParameter("@LastName", LastName)) MyCommand.Parameters.Add(New SqlParameter("@CellPhone", CellPhone)) MyCommand.Parameters.Add(New SqlParameter("@Street", Street)) MyCommand.Parameters.Add(New SqlParameter("@StreetNumber", StreetNumber)) MyCommand.Parameters.Add(New SqlParameter("@StreetAddon", StreetAddon)) MyCommand.Parameters.Add(New SqlParameter("@Zipcode", Zipcode)) MyCommand.Parameters.Add(New SqlParameter("@City", City)) MyCommand.Parameters.Add(New SqlParameter("@Organization", Organization)) Try MyConnection.Open() MyCommand.ExecuteNonQuery() bResult = True Catch ex As Exception bResult = False Finally MyConnection.Close() End Try Return bResult End FunctionThanks!
View 1 Replies
View Related
Mar 27, 2007
Hello :
I have a Web application with reports which lasts for a long time, for it I reflected to make call Asynchronous for reports
(that is I execute the report and warned the user when the report is ready).
I found an example which supplied with SQL Server, but as I am novice C# I understands not everything in the example
( AsynchronousRenderCS).
Please indicate me a simpler example.
Thank you.
View 1 Replies
View Related
Apr 8, 1999
Hi all,
Can anybody tell me if the have had any luck on creating and processing asychronous cursors. According to Microsoft SQL 7.0 books online after you create your Async cursor and then display the variable @@cursor_rows it should display either a negative number like -1245 meaning that it is still loading or a whole number meaning that it finish loading, but every time I display this variable I get -1 and according to MSSQL documentation this means I'm creating a Synchronous cursor, I have modified the cursor threshold settings, declared my cursor INSENSITIVE, and still can't get a cursor to be Async.
Thanks
View 1 Replies
View Related
Mar 21, 2007
What I am looking to do is have a stored procedure begin a dialog with my request service.
With that dialog established my stored procedure sends 50 request messages, one for each of the 50 of the United States. I want these to be processed asynchronously by a procedure that is called on activation for the request queue. In that activation procedure the request is processed against the respective state and a response message is sent to the response service (to the response queue). I want to be able to tie these request messages and response messages together with some type of shared identifier. These requests don't need to be processed in any specific order and don't need any fancy locking mechanism via conversation group since these requests require to be processed asynchronously. What is the best approach? Do I need to create 50 seperate queues and open dialogs with each? If this is the route to take, would this be a performance hit?
My goal is to have all 50 states process all at once, each finishing with a response message sent to the response queue. The initiating procedure, after sending these 50 requests, would then spin and wait for all 50 to complete, be it a response, error, or timeout. When all 50 have returned, the procedure would then merge the results and return. So as you can see in my scenario, I dont care when a state is complete as they do not affect the outcome, nor do they access any of the same resources when being processed.
View 3 Replies
View Related
Apr 27, 2005
Rather than the real code, here's a sample we came up with.
Here's the C# Code:
public class sptest : System.Web.UI.Page
{
protected System.Web.UI.WebControls.Label Label1;
private DataSet dtsData;
private void Page_Load(object sender, System.EventArgs e)
{
// Put user code to initialize the page here
string strSP = "sp_testOutput";
SqlParameter[] Params = new SqlParameter[2];
Params[0] = new SqlParameter("@Input", "Pudding");
Params[1] = new SqlParameter("@Error_Text", "");
Params[1].Direction = ParameterDirection.Output;
try
{
this.dtsData = SqlHelper.ExecuteDataset(ConfigurationSettings.AppSettings["SIM_DSN"], CommandType.StoredProcedure, strSP, Params);
Label1.Text = Params[0].Value.ToString() + "--Returned Val is" + Params[1].Value.ToString();
}
//catch (System.Data.SqlClient.SqlException ex)
catch (Exception ex)
{
Label1.Text = ex.ToString();
}
}
Here is the stored procedure:
CREATE PROCEDURE [user1122500].[sp_testOutput](@Input nvarchar(76),@Error_Text nvarchar(10) OUTPUT)AS
SET @Error_Text = 'Test'GO
When I run this, it prints up the input variable, but not the output variable.
View 2 Replies
View Related
Jul 13, 1999
We're looking for a solution to an audit trail issue. Our business people are looking to track each column value that changes(before and after images) for every table on our database as well as the userid that changed the data and when it was changed. Are there any methods that have been employed by other sites to track this level of detailed changes without resorting to triggers for each table and has anyone worked out a way for this audit trail writing to be handled asynchronously within SQL Server?
View 1 Replies
View Related
Oct 21, 2014
i tried to use xp_cmdshell in order to execute a vbscript from a trigger and it works, but i notice that trigger wait until the vbscript was terminated. i do some things into vbscript, so i can't wait until the end. There is a way to don't wait until the end, in practice, run vbscript in asynchronous mode??
View 6 Replies
View Related
Apr 19, 2007
Hi--done some searching, but I am not finding exactly what I need. I am using an asynchronous script component as a lookup since my table I am looking up on requires an ODBC connection. Here is what my data looks like:
From an Excel connection:
Order Number
123
234
345
The table I want to do a lookup on has multiple rows for each order number, as well as a lot of rows that aren't in my first table:
Order Number Description
123 Upgrade to System
123 Freight
123 Spare Parts
234 Upgrade to System
234 Freight
234 Spare Parts
778 Another thing
889 Yet more stuff
etc. My desired result would be to pull all the items from table two that match on Order Number from table one. My actual results from the script I have is a single (random) row from table two for each item in table one.....So my current results look like:
Order Number Description
123 Freight
234 Freight
345 Null
And I want:
Order Number Description
123 Upgrade to System
123 Freight
123 Spare Parts
234 Upgrade to System
234 Freight
234 Spare Parts
345 Null
etc.... Here is my code, courtesy of half a dozen samples found here and elsewhere...
Code Snippet
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Imports System.Data.Odbc
Public Class ScriptMain
Inherits UserComponent
Dim connMgr As IDTSConnectionManager90
Dim odbcConn As OdbcConnection
Dim odbcCmd As OdbcCommand
Dim odbcParam As OdbcParameter
Public Overrides Sub AcquireConnections(ByVal Transaction As Object)
connMgr = Me.Connections.JDEConnection
odbcConn = CType(connMgr.AcquireConnection(Nothing), OdbcConnection)
End Sub
Public Overrides Sub PreExecute()
odbcCmd = New OdbcCommand("SELECT F4211.SDDSC1, F4211.SDDOCO FROM DB.F4211 F4211 Where F4201.SHDOCO = ?", odbcConn)
odbcParam = New OdbcParameter("1", OdbcType.Int)
odbcCmd.Parameters.Add(odbcParam)
End Sub
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
Dim reader As Odbc.OdbcDataReader
odbcCmd.Parameters("1").Value = Row.SO
odbcCmd.ExecuteNonQuery()
reader = odbcCmd.ExecuteReader()
If reader.Read() Then
With Output0Buffer
.AddRow()
.SDDSC1 = reader("SDDSC1").ToString
.SONumb = Row.SO
.SOJDE = CDec(reader("SDDOCO"))
End With
End If
reader.Close()
End Sub
Public Overrides Sub ReleaseConnections()
connMgr.ReleaseConnection(odbcConn)
End Sub
End Class
I just don't know what I need to do to get every row from F4211 where SDDOCO matches Row.SO instead of a single row...... Any ideas or help? Oh, the reason I am starting with my Excel connection is that sheet lists the Orders I need detailed data for, and is only a few hundred rows....F4211 is really really really big.
I have also worked out an alternate way to do this using merge join tasks...but then my datareader source goes off and fetches 300,000 rows from F4211 before my final result set of about 1200 rows. That just feels like a bad approach to me...or am I being over-cautious? I'm a newb (if you couldn't already tell)...so guidence is appreciated.
Thank you....
View 12 Replies
View Related
Jan 3, 2007
I noticed that the current SLQCe driver does not offer support for the APM(Asynchronous Programming Model). Are there any plans to do this in the future? In light of the lack of APM functionality doe anyone have any ideas or thoughts on how async operations could be done, or if they are even needed in the context of applications that use SQL Ce
View 4 Replies
View Related
Mar 20, 2007
Hi All,
Any help regarding this very appreciated.
Problem:
I have a tough situation of trying to execute multiple instance of same package, to reduce the process load times.
Introduction:
We have src system which get 7000 tiny files of 72 rows each, and the SSIS package uses For Each Loop task and iterates through each file and loads data. We have a Process table that keeps track of the status of the SRC Process & ETL Load Process.
When the src process starts, For each row in the process table, it assigns a row status 'Assigned' brings in the flat file of 72 rows & updates the status as 'Complete'. When the ETL starts, for each file in the shared directory, it assigns status 'Running' and loads the data and updates status 'Complete'. Then the file is moved to different processes folder. Its like the bridge table between the 2 processes.
Bride Table Format: Table_PK(identity col), (DATE, City) is natural key, it is a cross join of date & City, so the process is getting 1 file every day for 1 city. Initial status are both 'Queued'
-----------------------------------------------------------------------------------------------------------------
Table_PK DATE CITY SrcProcStatus ETLStatus
-----------------------------------------------------------------------------------------------------------------
1 03/17/2007 Abingdon Queued Queued
2 03/17/2007 Albion Queued Queued
3 03/17/2007 Aledo Queued Queued
4 03/17/2007 Altamont Queued Queued
5 03/17/2007 Alton Queued Queued
6 03/17/2007 Amboy Queued Queued
7 03/17/2007 Anna Queued Queued
8 03/17/2007 Antioch Queued Queued
9 03/17/2007 Arcola Queued Queued
10 03/17/2007 Arlington Heights Queued Queued
11 03/17/2007 Ashley Queued Queued
.... ....
11 03/17/2007 Zeigler Queued Queued
11 03/17/2007 Zion Queued Queued
----------------------------------------------------------------------------------------------------------------
Since the bridge table is prepopulated, the src process(which is on Unix) starts multiple threads and gets files with in 30 minutes. But the SSIS is serial process & takes 2 -3 hrs to load the files, most of the time is taken by file operations and SSIS can only start only 1 thread.
Future Plan:
So to bring down the processing times, we wanted to start the SSIS packages in the Bridge table instead of starting in the share folder. i.e. for each row in the bridge where SRCProcess is Complete & ETLProcess Queued, start the SSIS process for this src file. Since our SRC files are names as "CityName_Date.csv" it will not be difficult. So we wanted to start multiple threads, that way the load process will be fast.
Implementation:
In the T-SQL loop we wanted to use 'xp_cmdshell' and call DTEXEC utility with the src file name as variable. But the DTEXEC is a synchronous process, but I am looking for a way to implement this asyncronously. Just like using "nohup executionscript &" in unix.
So any ideas on how to implement this, I looked on the web, and there is some thing about service broker, but all it says is about sending messages & queuing. Any light on how to implement this on windows server is going to be a life saver.
Thanks a lot,
Venkat
View 2 Replies
View Related