I am looking for a list of either generally used or a full list of overridable methods/functions for a script transformation?
I tried looking in the Visual Studio behind the script component but it only gives one method namely, Input0_ProcessInputRow, or if you make it asynchronous then it gives you another method CreateNewOutputRows.
I am basically looking for all the available overridable methods behind a script component in various situations.
Hi,I alredy tried to search this problem in last posts, but I couldn'tfind the answer.I try to access via Extended SP the method in a dll.I registered the dll as a ExSP, with a name of method. But aftercalling it in T-SQL, I became such a error message:[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot find thefunction SendGeneralNotify_FromA in the library [LibraryName.dll].Reason: 127(error not found).In this dll I have only one class an it has events, properities andmethods.I will to call one of these methods.aha ... very importand. Please don't say that this dll should bewritten in c++ because it is made like that (no VB).Maybe somebody of you have an example how I should call it, to becamean access on this dll?Sorry for my not well english.With best regards, looking forward for reply-----------------------------MatikJoin Bytes!
I need to know how to use my private function - created as a scalar-valued-function in SQL Server 2005 - in script component (here a transformation is used) in a data flow task to transform a two-digit-month into a tree-sign-month:
I have a package that has a data lfow task. this task imports data from a db2 database (using the IBM Ole DB provider fro db2) and adds it to sql server database table. This package was created on the server. then though version control (using TFS source control) I check out the package on my local machine. and when I open the package I get the foll 3 errors.
Error 1 Validation error. Import Account Num from BMGP_BDR: DTS.Pipeline: The component metadata for "component "DataReader Source" (1113)" could not be upgraded to the newer version of the component. The PerformUpgrade method failed.
Error 2 Error loading BMAG Download Xref Tables - bmag.dtsx: Microsoft.SqlServer.Dts.Pipeline.ComponentVersionMismatchException: The version of component "DataReader Source" (1113) is not compatible with this version of the DataFlow. [[The version or pipeline version or both for the specified component is higher than the current version. This package was probably created on a new version of DTS or the component than is installed on the current PC.]] at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostCheckAndPerformUpgrade(IDTSManagedComponentWrapper90 wrapper, Int32 lPipelineVersion)
Error 3 Error loading BMAG Download Xref Tables - bmag.dtsx: The component metadata for "component "DataReader Source" (1113)" could not be upgraded to the newer version of the component. The PerformUpgrade method failed.
I have a package which reads an Access file from a folder. My connection manager to this file is .NET providers for OledbMicrosoft Jet 4.0 OLE DB Provider.
Package works from my computer. But when I execute it on the server as a SQL Agent job, I get
The component metadata for "component "DataReader Source" (1) could not be upgraded to the newer version of the component. The PerformUpgrade method failed.
I copied the mdb file to a folder on the server which my packages have no problem reading data from.
My packages run under the same domain account as defined in proxies.
I was playing around with the new SQL 2005 CLR functionality andremembered this discussion that I had with Erland Sommarskog concerningperformance of scalar UDFs some time ago (See "Calling sp_oa* infunction" in this newsgroup). In that discussion, Erland made thefollowing comment about UDFs in SQL 2005:[color=blue][color=green]>>The good news is that in SQL 2005, Microsoft has addressed several of[/color][/color]these issues, and the cost of a UDF is not as severe there. In fact fora complex expression, a UDF in written a CLR language may be fasterthanthe corresponding expression using built-in T-SQL functions.<<I thought the I would put this to the test using some of the same SQLas before, but adding a simple scalar CLR UDF into the mix. The testinvolved querying a simple table with about 300,000 rows. Thescenarios are as follows:(A) Use a simple CASE function to calculate a column(B) Use a simple CASE function to calculate a column and as a criterionin the WHERE clause(C) Use a scalar UDF to calculate a column(D) Use a scalar UDF to calculate a column and as a criterion in theWHERE clause(E) Use a scalar CLR UDF to calculate a column(F) Use a scalar CLR UDF to calculate a column and as a criterion inthe WHERE clauseA sample of the results is as follows (time in milliseconds):(295310 row(s) affected)A: 1563(150003 row(s) affected)B: 906(295310 row(s) affected)C: 2703(150003 row(s) affected)D: 2533(295310 row(s) affected)E: 2060(150003 row(s) affected)F: 2190The scalar CLR UDF function was significantly faster than the classicscalar UDF, even for this very simple function. Perhaps a more complexfunction would have shown even a greater difference. Based on this, Imust conclude that Erland was right. Of course, it's still faster tostick with basic built-in functions like CASE.In another test, I decided to run some queries to compare built-inaggregates vs. a couple of simple CLR aggregates as follows:(G) Calculate averages by group using the built-in AVG aggregate(H) Calculate averages by group using a CLR aggregate that similatesthe built-in AVG aggregate(I) Calculate a "trimmed" average by group (average excluding highestand lowest values) using built-in aggregates(J) Calculate a "trimmed" average by group using a CLR aggregatespecially designed for this purposeA sample of the results is as follows (time in milliseconds):(59 row(s) affected)G: 313(59 row(s) affected)H: 890(59 row(s) affected)I: 216(59 row(s) affected)J: 846It seems that the CLR aggregates came with a significant performancepenalty over the built-in aggregates. Perhaps they would pay off if Iwere attempting a very complex type of aggregation. However, at thispoint I'm going to shy away from using these unless I can't find a wayto do the calculation with standard SQL.In a way, I'm happy that basic SQL still seems to be the fastest way toget things done. With the addition of the new CLR functionality, Isuspect that MS may be giving us developers enough rope to comfortablyhang ourselves if we're not careful.Bill E.Hollywood, FL------------------------------------------------------------------------- table TestAssignment, about 300,000 rowsCREATE TABLE [dbo].[TestAssignment]([TestAssignmentID] [int] NOT NULL,[ProductID] [int] NULL,[PercentPassed] [int] NULL,CONSTRAINT [PK_TestAssignment] PRIMARY KEY CLUSTERED([TestAssignmentID] ASC)--Scalar UDF in SQLCREATE FUNCTION [dbo].[fnIsEven](@intValue int)RETURNS bitASBEGINDeclare @bitReturnValue bitIf @intValue % 2 = 0Set @bitReturnValue=1ElseSet @bitReturnValue=0RETURN @bitReturnValueEND--Scalar CLR UDF/*using System;using System.Data;using System.Data.SqlClient;using System.Data.SqlTypes;using Microsoft.SqlServer.Server;public partial class UserDefinedFunctions{[Microsoft.SqlServer.Server.SqlFunction(IsDetermini stic=true,IsPrecise=true)]public static SqlBoolean IsEven(SqlInt32 value){if(value % 2 == 0){return true;}else{return false;}}};*/--Test #1--Scenario A - Query with calculated column--SELECT TestAssignmentID,CASE WHEN TestAssignmentID % 2=0 THEN 1 ELSE 0 END ASCalcColumnFROM TestAssignment--Scenario B - Query with calculated column as criterion--SELECT TestAssignmentID,CASE WHEN TestAssignmentID % 2=0 THEN 1 ELSE 0 END ASCalcColumnFROM TestAssignmentWHERE CASE WHEN TestAssignmentID % 2=0 THEN 1 ELSE 0 END=1--Scenario C - Query using scalar UDF--SELECT TestAssignmentID,dbo.fnIsEven(TestAssignmentID) AS CalcColumnFROM TestAssignment--Scenario D - Query using scalar UDF as crierion--SELECT TestAssignmentID,dbo.fnIsEven(TestAssignmentID) AS CalcColumnFROM TestAssignmentWHERE dbo.fnIsEven(TestAssignmentID)=1--Scenario E - Query using CLR scalar UDF--SELECT TestAssignmentID,dbo.fnIsEven_CLR(TestAssignmentID) AS CalcColumnFROM TestAssignment--Scenario F - Query using CLR scalar UDF as crierion--SELECT TestAssignmentID,dbo.fnIsEven_CLR(TestAssignmentID) AS CalcColumnFROM TestAssignmentWHERE dbo.fnIsEven(TestAssignmentID)=1--CLR Aggregate functions/*using System;using System.Data;using System.Data.SqlClient;using System.Data.SqlTypes;using Microsoft.SqlServer.Server;[Serializable][Microsoft.SqlServer.Server.SqlUserDefinedAggregate (Format.Native)]public struct Avg{public void Init(){this.numValues = 0;this.totalValue = 0;}public void Accumulate(SqlDouble Value){if (!Value.IsNull){this.numValues++;this.totalValue += Value;}}public void Merge(Avg Group){if (Group.numValues > 0){this.numValues += Group.numValues;this.totalValue += Group.totalValue;}}public SqlDouble Terminate(){if (numValues == 0){return SqlDouble.Null;}else{return (this.totalValue / this.numValues);}}// private accumulatorsprivate int numValues;private SqlDouble totalValue;}[Serializable][Microsoft.SqlServer.Server.SqlUserDefinedAggregate (Format.Native)]public struct TrimmedAvg{public void Init(){this.numValues = 0;this.totalValue = 0;this.minValue = SqlDouble.MaxValue;this.maxValue = SqlDouble.MinValue;}public void Accumulate(SqlDouble Value){if (!Value.IsNull){this.numValues++;this.totalValue += Value;if (Value < this.minValue)this.minValue = Value;if (Value > this.maxValue)this.maxValue = Value;}}public void Merge(TrimmedAvg Group){if (Group.numValues > 0){this.numValues += Group.numValues;this.totalValue += Group.totalValue;if (Group.minValue < this.minValue)this.minValue = Group.minValue;if (Group.maxValue > this.maxValue)this.maxValue = Group.maxValue;}}public SqlDouble Terminate(){if (this.numValues < 3)return SqlDouble.Null;else{this.numValues -= 2;this.totalValue -= this.minValue;this.totalValue -= this.maxValue;return (this.totalValue / this.numValues);}}// private accumulatorsprivate int numValues;private SqlDouble totalValue;private SqlDouble minValue;private SqlDouble maxValue;}*/--Test #2--Scenario G - Average Query using built-in aggregate--SELECT ProductID, Avg(Cast(PercentPassed AS float))FROM TestAssignmentGROUP BY ProductIDORDER BY ProductID--Scenario H - Average Query using CLR aggregate--SELECT ProductID, dbo.Avg_CLR(Cast(PercentPassed AS float)) AS AverageFROM TestAssignmentGROUP BY ProductIDORDER BY ProductID--Scenario I - Trimmed Average Query using built in aggregates/setoperations--SELECT A.ProductID,CaseWhen B.CountValues<3 Then NullElse Cast(A.Total-B.MaxValue-B.MinValue ASfloat)/Cast(B.CountValues-2 As float)End AS AverageFROM(SELECT ProductID, Sum(PercentPassed) AS TotalFROM TestAssignmentGROUP BY ProductID) ALEFT JOIN(SELECT ProductID,Max(PercentPassed) AS MaxValue,Min(PercentPassed) AS MinValue,Count(*) AS CountValuesFROM TestAssignmentWHERE PercentPassed Is Not NullGROUP BY ProductID) BON A.ProductID=B.ProductIDORDER BY A.ProductID--Scenario J - Trimmed Average Query using CLR aggregate--SELECT ProductID, dbo.TrimmedAvg_CLR(Cast(PercentPassed AS real)) ASAverageFROM TestAssignmentGROUP BY ProductIDORDER BY ProductID
In a Data Flow, I have the necessity to use a SSIS variable of type €œObject€? inside Script Component and assign to it the content of 'n' variables of string type. On exiting from the script the variable of type object should contain something like in the following lines: AAAAAAAAAAAAAAAAAAAAAAAAAAAAA BBBBBBBBBBBBBBBBBBBBBBBBBBBBB CCCCCCCCCCCCCCCCCCCCCCCCCCCCC DDDDDDDDDDDDDDDDDDDDDDDDDDDDD €¦€¦€¦€¦€¦€¦€¦. €¦€¦€¦€¦€¦€¦€¦. On exiting from the data flow I will use the variable of type Object in a Script Task, by reading each element in a cyclic fashion. Is there anyone who have experienced something like this? Could anyone provide any example of that? Thanks in advance!
Hi all I'm into a project which uses a lot of views for joining 2 or more tables. Using the MERGE component in SSIS will be a huge effort coz it only has 2 inputs and I gotta SORT the input too. Isnt it possible to have a VIEW like component that joins more than 2 tables and DOESNT need sorting?? (I've thought about creating views in database engine but it breaks my data floe in SSIS and is'nt a practical solution)
I am writing a custom dataflow transformation component and I need to get the name of the preceeding component.
I have been trying to find a way to get a reference to the Package object, MainPipe object or IDTSPath90 object (connecting to the IDTSInput90 of my component) from my component because I think from there I can get to the information I want.
No idea where this bug crept in from. Have been using SSIS for 1.5 years now without hitting this problem.
I had a script component opening an XML document and parsing it using XPATH. I added some code that uses StreamReader / Streamwriter (closing one stream before starting the other). The code works without issue in my C# app.
And it ran without issue 2-3 times in SSIS. Then suddenly after running my package again, the script component says it completes successfully, yet nothing happens. I set a breakpoint on the first line of code - it never hits it. I add a msgbox as the first line of code - and it never displays.
I then close my package / exit out of ssis ... and then re-open it. When i open my script component, all of my code is GONE. All references that I added are gone.
I tried adding the streamreader/writer process to a dll I created from my c# app ... and added the DLL to the package -- same result.
I can reproduce this on 2 different computers.
Anyone experience this problem ? Any idea how to stop it ? Or debug it ?
Here is a slimmed down code sample of what causes the error :
Public Class ScriptMain Public Sub Main() Try Dim xmlDoc As New XmlDocument xmlDoc.Load("c:ulkasync_86281519_20070628045850225_4.xml") MsgBox("xmlLoaded") --this doesn't display once the package starts "acting up" Catch ex As Exception MsgBox(ex.Message) UpdateXML("c:ulkasync_86281519_20070628045850225_4.xml", ex.Message) End Try Dts.TaskResult = Dts.Results.Success End Sub Private Sub UpdateXML(ByVal fileName As String, ByVal message As String) Try Dim invalidChar As String = message.Trim().Substring(message.Trim().IndexOf("0x"), 4) Dim rd As StreamReader = New StreamReader(fileName) Dim xml As String = rd.ReadToEnd() Xml = Xml.Replace(invalidChar, String.Empty) xml = xml.Replace("", String.Empty) xml = xml.Replace("<![CDATA[<![CDATA[", "<![CDATA[") xml = xml.Replace("]]>]]>", "]]>") MsgBox("replaced") rd.Close() Dim wr As StreamWriter = New StreamWriter(fileName) wr.Write(xml) wr.Close() Dim xdoc As XmlDocument = New XmlDocument() xdoc.Load(fileName) Catch ex As Exception UpdateXML(fileName, ex.Message) End Try End Sub End Class
I'm a newbee to replication and wanted to know about the whole mechanism of replication and how is two way replication implemnted on 2 diffrent servers located at 2 different remote locations????
I am looking for a good method to do table lookups in a 2003 SQL database. I am retrieving products from an item table and then want to do value lookups in related tables in the same database like category name, category type, brand name etc. These values are referenced in the item master file by guid links to related tables.I am using a datareader to loop through the Select records and then I am writing results to a tab delimited data file for a data conversion. I am limited to net 1.1 and so can't use the 2.0 executescalar for single lookups.What would be the more efficienct method to use?'Dim myItemData As New DataReader Dim myItemReader As SqlClient.SqlDataReaderDim myItemCommand As New SqlClient.SqlCommand Dim myConnection = New SqlClient.SqlConnection("server=sql01.xxx") myItemCommand.Connection = myConnectionmyItemCommand.CommandText = "SELECT ""mf_items"".""item_guid"", ""mf_items"".""item_description"", ""mf_items"".""item_wholesale_price"", ""mf_items"".""item_description_title"",""mf_items"".""item_cd"",""mf_items"".""category_guid"" FROM ""CDB006"".""dbo"".""mf_items"" WHERE ""mf_items"".""item_closed"" = 0 And ""mf_items"".""item_visible"" = 1 AND ""mf_items"".""item_available"" = 1"myItemCommand.Connection.Open()myItemReader = myItemCommand.ExecuteReader()Dim myfileout As StreamWritermyfileout = File.CreateText(Server.MapPath("out.txt"))myfileout.WriteLine("link" & vbTab & "title" & vbTab & "description" & vbTab & "price" & vbTab & "image_link" & vbTab & "category" & vbTab & "id")While myItemReader.ReadIf myItemReader.Item(4).ToString() > "" And Val(myItemReader.Item(2).ToString) > 0 Then'what method to to look up for example the category name using the category_quid referenced in the item master'Write out record for parts file
I'm fiddling around with my sql server in my home computer. I would like to store a different image in each row of the product table (for example). All the pics are now in my hard disk. I gather from various discussion boards that there are several ways of saving images -
1. INTERNALLY
a)Use VB. Run in ISQL/w b) Use insert statement. INSERT INTO EMP(empno,empname ,Picture_Id) VALUES('E1235','Anthony','c:PhotosAnthony.jpg') c) Use isql/w & TSQL statement d) Use sqlgetdata and sqlputdatn e) Use textcopy in binn directory. f) Use VB & ADO
2. EXTERNALLY
a)store the image externally and simply store the path to it in a plain varchar variable.
My questions are :- (i) Can I save images simply using 1b)? (ii)Is 2a) correct ? Do I just type the name of the image file in my harddisk as the value in the table ? (iii) If I have the database linked to the internet and have it hosted by a commercial server in the future, will I have a nightmare reentering all the path names when I upload the database into the commercial server? (iv) What are the pros and cons of the various methods listed in 1.
Everytime I attempt to restore the master database, it corrupts the entire instance of SQL server 2000. I have been using the method out on Microsoft's Knowledgebase:
http://support.microsoft.com/kb/822852/en-us
using sqlservr.exe and the switches -c -m -T3608 -T4022
I would like to know if anyone has other methods of restoring the master database from a backup device.
I have developed the SSIS project, but I have a problem with deployment. My users are statistic people and SSIS packges they use are cleaning the raw data for them. Since they would like to run this SSIS package by themselves whenever they want, I need some deployment method that is easy for them to run (they are really not IT oriented people). The problem I encountered is that there is no visual studio (integration service client) installed on there computer, but they are allowed to access several dbs and server on the net.
Can someone tell me what is the best solution for this type of deployment?
I am looking to get some guidance and feed back from the community as to the prevalence and usage of the following technologies:
ODBC Descriptor support OLE DB support External Linked Server support Data Link support for Data Transformation Services
Specifically I am hoping to find out when each technology should be used and the types of applications that use them. Additionally, I am wondering if there are alternatives to these technologies and why they may have been used instead.
I am having a problem running a sql2k report service script on my new sql2005 server. It seems that the methods SessionHeader() and rs.render are no longer suport. can someone help me figure out what I need to do to continue automating my report running?
Dim skipreport as string = nothing dim skip as integer = 0
dim allbranchreport as string = nothing dim allbranch as integer = 0
dim report as CatalogItem dim repname as string = nothing dim specificreport as string = nothing dim filepath as string = nothing dim errorpath as string = nothing dim basefilepath as string = nothing dim baseerrorpath as string = nothing dim errorlog as integer = 0 Dim parameters() As ParameterValue = nothing Dim paramcount as integer = 0
' Render arguments Dim result As Byte() = Nothing Dim reportPath As String = nothing 'Dim format As String = "PDF" Dim format as string = "EXCEL" Dim historyID As String = Nothing Dim devInfo as string = Nothing Dim credentials As DataSourceCredentials() = Nothing Dim showHideToggle As String = Nothing Dim encoding As String Dim mimeType As String Dim warnings As Warning() = Nothing Dim reportHistoryParameters As ParameterValue() = Nothing Dim streamIDs As String() = Nothing Dim sh As New SessionHeader() rs.SessionHeaderValue = sh Dim omitdocmap as string = "True" '**************************************
'************************************** 'Repset run Dim repset as string = "" for each repset in repsetlist specificreports = specificreportsstart if specificreports = 0 then select case repset.tolower case "all" specificreports = 0 case "brkctr" specificreports = 1 reportcount = reportset_brkctr.getupperbound(0) redim specificreportslist(reportcount) for reportloop = 0 to reportcount specificreportslist(reportloop) = reportset_brkctr(reportloop) next case "cashmgmt" specificreports = 1 reportcount = reportset_cashmgmt.getupperbound(0) redim specificreportslist(reportcount) for reportloop = 0 to reportcount specificreportslist(reportloop) = reportset_cashmgmt(reportloop) next case "com" specificreports = 1 reportcount = reportset_com.getupperbound(0) redim specificreportslist(reportcount) for reportloop = 0 to reportcount specificreportslist(reportloop) = reportset_com(reportloop) next case "com_vw" specificreports = 1 reportcount = reportset_com_vw.getupperbound(0) redim specificreportslist(reportcount) for reportloop = 0 to reportcount specificreportslist(reportloop) = reportset_com_vw(reportloop) next repset = "com" case "comcons" specificreports = 1 reportcount = reportset_comcons.getupperbound(0) redim specificreportslist(reportcount) for reportloop = 0 to reportcount specificreportslist(reportloop) = reportset_comcons(reportloop) next case "cons" specificreports = 1 reportcount = reportset_cons.getupperbound(0) redim specificreportslist(reportcount) for reportloop = 0 to reportcount specificreportslist(reportloop) = reportset_cons(reportloop) next case "cons_vw" specificreports = 1 reportcount = reportset_cons_vw.getupperbound(0) redim specificreportslist(reportcount) for reportloop = 0 to reportcount specificreportslist(reportloop) = reportset_cons_vw(reportloop) next repset = "cons" case "dep" specificreports = 1 reportcount = reportset_dep.getupperbound(0) redim specificreportslist(reportcount) for reportloop = 0 to reportcount specificreportslist(reportloop) = reportset_dep(reportloop) next case "staff" specificreports = 1 reportcount = reportset_staff.getupperbound(0) redim specificreportslist(reportcount) for reportloop = 0 to reportcount specificreportslist(reportloop) = reportset_staff(reportloop) next omitdocmapbranch = "False" case "staff2" specificreports = 1 reportcount = reportset_staff2.getupperbound(0) redim specificreportslist(reportcount) for reportloop = 0 to reportcount specificreportslist(reportloop) = reportset_staff2(reportloop) next omitdocmapbranch = "False" case else specificreports = 1 redim specificreportslist(0) specificreportslist(0) = "Undefined" end select
if repset.tolower = "staff2" then repset = "staff" End if
'Reports List Dim reports() as CatalogItem reports = rs.ListChildren(basereportpath, False)
'Execution if datasourceok = 1 then for each report in reports
skip = 0 allbranch = allbranchrun
for each skipreport in skipreports if report.name.tolower = skipreport.tolower then skip = 1 next
if skip = 0 then if specificreports = 1 then skip = 1 for each specificreport in specificreportslist if report.name.tolower = specificreport.tolower then skip = 0 next end if end if
if skip = 0 then for each allbranchreport in allbranchreports if report.name.tolower = allbranchreport.tolower then allbranch = 1 next
Redim parameters(1) parameters(0) = New ParameterValue() parameters(0).name = "branchall" parameters(1) = New ParameterValue() parameters(1).Name = "branch"
'5 parameters reports repname = report.name if (repname.substring(0,5).tolower = "loans" or repname.substring(0,4).tolower = "locs") then paramcount = 4 if (repname.length >= 13) then if repname.substring(0,13).tolower = "loans_beacons" then paramcount = 1 end if
if paramcount = 4 then paramcount = 0 redim preserve parameters(4)
parameters(2) = New ParameterValue() parameters(2).name = "comconsall" if repset.tolower = "cons" or repset.tolower = "com" then parameters(2).value = 0 else parameters(2).value = 1
parameters(3) = New ParameterValue() parameters(3).Name = "comcons" if repset.tolower = "com" then parameters(3).value = "commercial" else parameters(3).value = "consumer"
parameters(4) = New ParameterValue() parameters(4).Name = "brokered" parameters(4).value = 0 if repset.substring(0,3).tolower = "brk" then parameters(4).value = 1 end if
'All Branches Reports if allbranch = 1 then
parameters(0).value = "1" parameters(1).value = "admin" if client = "CSCU" then parameters(1).value = "newwst" if clienttype = "vw" then parameters(1).value = "1"
Try result = rs.Render(reportPath, format, historyID, devInfo, parameters, _ credentials, showHideToggle, encoding, mimeType, reportHistoryParameters, warnings, streamIDs) sh.SessionId = rs.SessionHeaderValue.SessionId Catch e As SoapException errorlog = 1 Console.WriteLine(e.Detail.OuterXml) Catch f as Exception errorlog = 1 Console.writeline(f.message) End Try
' Create an error log If errorlog <> 0 then Try Dim stream As FileStream = File.Create(errorpath, 1024) Console.WriteLine("Errorlog created: " & errorpath) stream.Close() Catch g As Exception Console.WriteLine(g.Message) End Try End if
' Write the contents of the report to a file. If errorlog <> 1 then Try Dim stream As FileStream = File.Create(filepath, result.Length) stream.Write(result, 0, result.Length) Console.WriteLine("Result written to file: " & filepath) stream.Close()
Catch e As Exception Console.WriteLine(e.Message) End Try end if
'Other Reports else parameters(0).value = "0"
for each branch in branches parameters(1).value = branch
errorlog = 0 if branch <> "h/o" then filepath = basefilepath & branch & "" & report.name & ".xls" else filepath = basefilepath & "ho" & report.name & ".xls" end if if branch <> "h/o" then errorpath = baseerrorpath & branch & "_" & report.name & ".txt" else errorpath = baseerrorpath & "ho_" & report.name & ".txt" end if reportpath = basereportpath & "/" & report.name omitdocmap = omitdocmapbranch devinfo = "<DeviceInfo><OmitDocumentMap>" & omitdocmap & "</OmitDocumentMap></DeviceInfo>"
Try result = rs.Render(reportPath, format, historyID, devInfo, parameters, _ credentials, showHideToggle, encoding, mimeType, reportHistoryParameters, warnings, streamIDs) sh.SessionId = rs.SessionHeaderValue.SessionId Catch e As SoapException errorlog = 1 Console.WriteLine(e.Detail.OuterXml) Catch f as Exception errorlog = 1 Console.writeline(f.message) End Try
' Create an error log If errorlog <> 0 then Try Dim stream As FileStream = File.Create(errorpath, 1024) Console.WriteLine("Errorlog created: " & errorpath) stream.Close() Catch g As Exception Console.WriteLine(g.Message) End Try End if
' Write the contents of the report to a file. If errorlog <> 1 then Try Dim stream As FileStream = File.Create(filepath, result.Length) stream.Write(result, 0, result.Length) Console.WriteLine("Result written to file: " & filepath) stream.Close()
Catch e As Exception Console.WriteLine(e.Message) End Try end if
The specified script failed to compile with the following errors: J:PRA Publisher> "C:WINDOWSMicrosoft.NETFrameworkv2.0.50727vbc.exe" /t:exe /main:MainModule /utf8output /R:"System.dll" /R:"System.Xml.dll" /R:"System.Web .Services.dll" /R:"C:Program FilesMicrosoft SQL Server90Toolsinn s.exe" / out:"C:Documents and SettingsetopLocal SettingsTemp1fw7qn9k.exe" /debug- "C:Documents and SettingsetopLocal SettingsTemp1fw7qn9k.0.vb" "C:Docu ments and SettingsetopLocal SettingsTemp1fw7qn9k.1.vb"
Microsoft (R) Visual Basic Compiler version 8.0.50727.42 for Microsoft (R) .NET Framework version 2.0.50727.42 Copyright (c) Microsoft Corporation. All rights reserved.
C:Documents and SettingsetopLocal SettingsTemp1fw7qn9k.1.vb(253) : error BC30002: Type 'SessionHeader' is not defined.
Dim sh As New SessionHeader() ~~~~~~~~~~~~~ C:Documents and SettingsetopLocal SettingsTemp1fw7qn9k.1.vb(254) : error BC30456: 'SessionHeaderValue' is not a member of 'Microsoft.SqlServer.Reporting Services2005.ReportingService2005'.
rs.SessionHeaderValue = sh ~~~~~~~~~~~~~~~~~~~~~ C:Documents and SettingsetopLocal SettingsTemp1fw7qn9k.1.vb(434) : error BC30456: 'Render' is not a member of 'Microsoft.SqlServer.ReportingServices2005 .ReportingService2005'.
result = rs.Render(reportPath, format, historyID, de vInfo, parameters, _ ~~~~~~~~~
C:Documents and SettingsetopLocal SettingsTemp1fw7qn9k.1.vb(436) : error BC30456: 'SessionHeaderValue' is not a member of 'Microsoft.SqlServer.Reporting Services2005.ReportingService2005'.
sh.SessionId = rs.SessionHeaderValue.SessionId ~~~~~~~~~~~~~~~~~~~~~ C:Documents and SettingsetopLocal SettingsTemp1fw7qn9k.1.vb(496) : error BC30456: 'Render' is not a member of 'Microsoft.SqlServer.ReportingServices2005 .ReportingService2005'.
result = rs.Render(reportPath, format, historyID , devInfo, parameters, _ ~~~~~~~~~
C:Documents and SettingsetopLocal SettingsTemp1fw7qn9k.1.vb(498) : error BC30456: 'SessionHeaderValue' is not a member of 'Microsoft.SqlServer.Reporting Services2005.ReportingService2005'.
i had read information about sce and i had not unsderstood a little thing:
is it possible or not to use a dataset with that version of sql server? i have a class that access the database with some functions and one of then return a dataset. the dataset is not to be used because it does not make sense with sql server compact edition or it cannot by use at all cause sql server compact edition does not support it?
thkx and by the way i think that it's a great product!
I have a dll which is developed in C#. I have made a reference to it from Reporting Services under Report Properties.
My problem is, that the methods in this dll are not static. Therefore under the Classes in Report Properties I have written the class-name and the instance-names of the instances that I need to use from this class (as I understand it, this is what you have to do if using non-static methods). In a text-box, I call one of the instances in the class, but I get this error:
[rsInvalidName] 'MyMethod()' is not a valid code class name. Names of objects must be CLS-compliant identifiers.
Lets say you want to offer users access to a table that has over 1,000,000 records in it. You dont want to fetch the entire table into a dataset. Even with paging, it will still cause a performance lag, will it not ?Lets say there was a column that held last names. You could then divide up all the data in to alphabetical groups by letter. That will at least let you split up the data 26 different ways (letters in teh alphabet). Thats still roughly 38,000 possible records in the dataset at any given time. I suppose since the database and web server are very close to each other, physically, the latency shouldnt be too bad. However, Im worried about having several dozen or more users, each requesting datasets with tens of thousands of records. That would cause the memory use on the web server to spike most likely. Right now, its just one user do this sort of processing, but that might grow to include several dozens.ideas ?
In order to update a MSSQL table, named [Table_1]. The procedure is: (@id int,@Field0 nvarchar(1000) = NULL,@Field1 nvarchar(1000) = NULL,... ...@Field9 nvarchar(1000) = NULL)Method 1: UPDATE Table_1SET Field0 = CASE WHEN @Field0 IS NULL THEN Field0 ELSE @Field0 END, Field1 = CASE WHEN @Field1 IS NULL THEN Field1 ELSE @Field1 END, ... ... Field9 = CASE WHEN @Field9 IS NULL THEN Field9 ELSE @Field9 ENDWHERE id = @id Method 2 IF @Field0 IS NOT NULL UPDATE Table_1 SET Field0 = @Field0 WHERE id = @id ... ... UPDATE Table_1 SET Field9 = @Field9 WHERE id = @id Method 3: DECLARE @SQL nvarchar(max)DECLARE @NeedComma bitSET @SQL = 'UPDATE Table_1 SET 'SET @NeedComma = 0IF @Field0 IS NOT NULL BEGIN IF @NeedComma = 1 SET @SQL = @SQL + ', ' SET @SQL = @SQL + 'Field0 = @Field0' SET @NeedComma = 1 END... ...IF @Field9 IS NOT NULL BEGIN IF @NeedComma = 1 SET @SQL = @SQL + ', ' SET @SQL = @SQL + 'Field9 = @Field9' SET @NeedComma = 1 ENDSET @SQL = @SQL + ' WHERE id = @id'EXEC sp_executeSQL @SQL, N'@id int,@Field0 nvarchar(1000), ... ... , @Field9 nvarchar(1000)', @id = @id, @Field0 = @Field0, ... ... , @Field9 = @Field9
Let me see if I can explain what I'm trying to do (actually am doing now but with methods that I know can improve), and hopefully someone can give me some thoughts on the best way to do this (for maximum speed).
I've got data that comes from a couple of different sources that I'm updating and compiling nightly. On each file there can be different fields of data (some memo fields, some numbers, some text, etc. but all a subset of a master list of fields in my table). I then need to take that data and basically update my existing records (or insert new records if they don't exist based on an id which is in the incoming files). The current program does this all by looping through the data and creating sql statements for the inserts and updates. I think using SSIS or bulk inserts after writing my data to a file, i should be able to speed this up.
To me the "gotchas" are that each file may have different columns of data and I can't write different imports for each one. So I either have to have it be able to determine the columns for each piece of data and match when loading or updating or I could recode my program to basically insert the missing columns (should be doable).
Any thoughts on the best way to do it? Writing out to a file for SSIS to use but having it only write out new records and then doing updates through another method or ? I know SQL Server 2005 fairly well for basics on SQL but haven't used SSIS so will have to learn or outsource so any good starter points or tips would help. Seems the updates are where I'm a bit more confused on the very best way to do it.
I'm also stripping out html in one of the fields and creating a secondary field that doesn't have the html so I'm assuming the best is probably to do that while writing out the file I may use for whatever system but if there is a way to do it inside SQL or SSIS and make it happen quickly (so it doesn't lock the database) that would be great.
I am retrieving data from Web Services by using Web Service Task. In the General pane, I successfuly download the wsdl file. There are 3 methods included in this file. However, I cannot see these methods in the list box in Input pane. I have installed SP2. Could anyone tell me why?
I have created a report in reporting service 2005 and i have succesfully integrated the report in asp.net webpage. But i want to know whether it is possible to access the methods and properties of (report)rdl file in the asp.net webpage. If it is possible then please tell me how to implement it
We have roughly 5000 methods generated into C# that need to get assembled into DLLs and then put out as assmeblies in a SQL Server database. We then intend on creating user-defined functions to call many of these directly.
We can break these 5000 down into bundles and make about 10 DLLs, OR we can make 5000 DLLs. What are the concerns from a run-time perspective ??
If we go with 5000 DLLs, will an end user performing a few ad-hoc queries that use these user-defined functions experience poorer performance than if we had 10 large DLLs ?
Any input is appreciated. We'll be trying to assess the performance ourselves, but I'm looking for any stories about using large numbers of DLLs.