.rss Methods No Longer Supported

Feb 9, 2006

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?

rss file contents: (the parts that should matter)

-----------------------------------------------------------------------------------------

Report Parameters
Dim branch as string = nothing

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


'Base values
basefilepath = basepath & client & "" & repset & ""
baseerrorpath = basepath & client & "" & "error_" & repset & "_"


'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"

errorlog = 0
filepath = basefilepath & report.name & ".xls"
errorpath = baseerrorpath & report.name & ".txt"
reportpath = basereportpath & "/" & report.name
omitdocmap = omitdocmapall
devinfo = "<DeviceInfo><OmitDocumentMap>" & omitdocmap & "</OmitDocumentMap></DeviceInfo>"

Console.WriteLine (repset.toupper & " " & report.name & " All branches")

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>"

Console.WriteLine (repset.toupper & " " & report.name & " " & "Branch: " & branch)

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

next
end if

end if
next
end if
next

----------------------------------------------------------------------------------------------------

I get this error message from cmd when I try and run my batch:



----------------------------------------------------------------------------------------------------

J:PRA Publisher>rs -i pra_publisher.rss -s http://localhost/reportserver -l 0

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 Settings
etopLocal SettingsTemp1fw7qn9k.exe" /debug-
"C:Documents and Settings
etopLocal SettingsTemp1fw7qn9k.0.vb" "C:Docu
ments and Settings
etopLocal 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 Settings
etopLocal SettingsTemp1fw7qn9k.1.vb(253) : error
BC30002: Type 'SessionHeader' is not defined.

Dim sh As New SessionHeader()
~~~~~~~~~~~~~
C:Documents and Settings
etopLocal SettingsTemp1fw7qn9k.1.vb(254) : error
BC30456: 'SessionHeaderValue' is not a member of 'Microsoft.SqlServer.Reporting
Services2005.ReportingService2005'.

rs.SessionHeaderValue = sh
~~~~~~~~~~~~~~~~~~~~~
C:Documents and Settings
etopLocal 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 Settings
etopLocal 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 Settings
etopLocal 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 Settings
etopLocal SettingsTemp1fw7qn9k.1.vb(498) : error
BC30456: 'SessionHeaderValue' is not a member of 'Microsoft.SqlServer.Reporting
Services2005.ReportingService2005'.

sh.SessionId = rs.SessionHeaderValue.SessionId
~~~~~~~~~~~~~~~~~~~~~



--------------------------------------------------------------------------------------------------------------------

Any advice on how to fix this would be graetly appreciated. Thanks!

View 6 Replies


ADVERTISEMENT

Is These .net Methods Supported To Call From SQL Stored Procedures?

Oct 6, 2006

Is these .net methods supported to call from SQL Stored Procedures?

public static int MultiplyMany(params int[] intArray)
{
    int result = 1;

    foreach(int now in intArray)
    {
        result *= now;
    }

    return result;
}

public static void TestOut(string name, out string firstName, out string lastName)
{
    int spaceIndex = name.IndexOf(' ');
    firstName = name.Substring(0, spaceIndex);
    lastName = name.Substring(++spaceIndex);
}

View 5 Replies View Related

ALLOW_DUP_ROW' Is No Longer Supported

Feb 15, 2007

Any body please give me the details about how to use 'ALLOW_DUP_ROW' in a CREATE CLUSTERED INDEX statement.

I tried executing the below statement but it throws an error "CREATE INDEX option 'ALLOW_DUP_ROW' is no longer supported."(both in SQL Server 2000 and SQL Server 2005)

CREATE CLUSTERED INDEX index121
ON raj(j)
WITH ALLOW_DUP_ROW

raj(j) contains two rows with same value

please hhelp me out

View 2 Replies View Related

Embedded Text Qualifiers No Longer Supported In Yukon ?

Oct 19, 2005

Hello,

View 1 Replies View Related

Adding A New Record Takes Longer And Longer -- Archive? (was Table Help)

Mar 1, 2005

Hi we have a table with about 400000 records in it. It starting to take longer and longer to add a new record. I was thinking of creating another identical table and archiving off most of the records every month (we are now adding about about 4000 records a day) . Is this the best thing to do?
I don't know a lot about sql server so any help or suggestions would be great

View 4 Replies View Related

Restore Taking Longer And Longer

Feb 4, 2008

The following code is taking longer and longer to run. I am not talking about the gradualy increase in size. this job has been taking 30-40 mins normaly and in the last few days it has gone 1hr to 2 hr to 3 hr... ANy ideas why this is happening? I can not see and other jobs running at this time.

declare @filename varchar(255)

set @filename =
(select top 1 physical_device_name
from ****.msdb.dbo.backupset bs, ****.msdb.dbo.backupmediafamily bf
where bs.media_set_id=bf.media_set_id
and database_name = 'Live_PRD'
and backup_start_date>getdate()-1
and type = 'D'
order by backup_start_date desc)

restore database REPORTS_REP
from disk=@filename
with
move 'LIVE_PRD_Data' to 'T:SOUTHREPORTS_REP_Data.mdf',
move 'LIVE_PRD_Log' to 'U:SOUTHREPORTS_REP_Log.ldf',
move 'LIVE_PRD_Log2' to 'U:SOUTHREPORTS_REP_Log2.ldf',
replace, stats=2, recovery

View 5 Replies View Related

Why Does It Take Longer And Longer For The Same Code To Run

Feb 22, 2008

Why does it take longer and longer for the same code to run very simply I have 8,0000,000 records I want to delete from a table . I have tried a few options

Option 1 a while loop which deletes 10,000 rows per loop starting from the earliest until it hits the cut of number I have set. THIS TOOK 5 HOURS

Option 2 created an SP which found the oldest 100,000 records then deleted them. If I run this SP manually it takes 30 €“ 60 secs. Which I thought was much better than above. So I put this SP in a while loop to run 80 odd times thinking the time it would take would be 80 mins a huge improvement.


But every time this SP is called it takes longer and longer (36,30,32,39,37,37,123,163,155,182€¦and so on(In seconds)).

All the sp is doing is as follows(8860000 is just to insure I don€™t delete to much). this sp is then called from in a while loop.


set @recnumber = (select top 1 recnumber from
(select top 100000 recnumber from TabletodeleteFROM where recnumber < 8860000
order by recnumber asc ) TabletodeleteFROM
order recnumber desc)

delete TabletodeleteFROM where recnumber < @recnumber


please help why is it taking longer and longer

View 8 Replies View Related

SQL Connection Methods

Jun 5, 2007

Set myConn1 = Server.CreateObject("ADODB.Connection")
 Set myRs1 = Server.CreateObject("ADODB.Recordset")
 
Can anybody tell me what the ADODB in these two statements is telling me?

View 3 Replies View Related

UDT For Division Methods

Sep 4, 2007

Hi,

I plan to implement UDT for division methods.


The following fragment TSQL with zero check:


DECLARE @a int, @b int


SELECT



CASE WHEN @b = 0 THEN NULL

ELSE @a / @b
END



Clr UDT may look like this and script will be shorter.


DECLARE @a int, @b int


SELECT @a Type :: divide(@b)







Is that better to use UDT ?

Anyone have try this before?

View 8 Replies View Related

Methods Within An Assembly

Apr 4, 2007

Is there a way to retrieve the methods within an assembly that are attributed with SqlTrigger or SqlProcedure using T-SQL?

View 1 Replies View Related

Replication Methods

Aug 31, 2007

Hi,

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????

View 2 Replies View Related

SQL Table Lookup Methods

Aug 29, 2006

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 

View 2 Replies View Related

Various Methods Of Storing Images

Jun 26, 2000

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.

View 1 Replies View Related

Methods For Restoring The Master Db

Nov 16, 2006

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.

View 2 Replies View Related

Performance Tuning Methods

Mar 16, 2004

Hello everyone:

Can anyone list the general performance tuning methods? Thanks a lot.

ZYT

View 1 Replies View Related

SSIS Deployment Methods

Oct 12, 2007

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?

View 4 Replies View Related

Questions About Various DB Connection Methods.

Dec 15, 2006

Hello All,

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.

Any guidance/feed back is appreciated.

Thanks,

View 4 Replies View Related

Sql Compact Edition And Methods

Feb 7, 2007

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!

View 1 Replies View Related

Calling Non-static Methods In Dll

May 31, 2007

Hello,

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.

What exactly have I done wrong here?


Thanks
/Peter

View 3 Replies View Related

Methods For Handling Large Datasets

Apr 7, 2008

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 ? 

View 3 Replies View Related

The Three Methods Of Update MSSQL Table, Which Is The Best?

Mar 8, 2006

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
 
Which is the best? Thanks!

View 3 Replies View Related

Data Importing And Updating Methods

Dec 10, 2007

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.

View 1 Replies View Related

Methods To Move Existing DTS Packages

Mar 19, 2008

I need to move total of 50 DTS packages from SQL 2000 to SQL 2005.Could someone give me the best way to do this ?

Do I need to just save the package onto the new server OR do I use the DTS package migration wizard.

What are the problems that can arise ?

View 4 Replies View Related

Accessing Static Methods Of A .net Dll, But From Sql2000?

Jan 15, 2007

Is this somehow possible?

View 1 Replies View Related

Web Service Task: Cannot See The List Of Methods

Oct 24, 2007

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?

View 1 Replies View Related

How To Access The Properties And Methods Of Rdl From Webpage.

Aug 14, 2007



Hi,


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



Thanks and Regards
Altaf Hussain N

View 1 Replies View Related

What Methods Can Be Used To Improve The Reliability Of SSIS?

Sep 11, 2006

Hi ,all here,

I am having a question about what other methods are avaliable in SSIS besides error outputs and checkpoints to improve the reliability of SSIS?

Thanks a lot in advance for any guidance and advices for that.

With best regards,

Yours sincerely,

View 4 Replies View Related

Grouping Methods In A DLL, Where Is The Break Point?

May 19, 2006

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.



Thanks

Scott

View 1 Replies View Related

Methods To Generate ER Diagrams Programmatically

Sep 4, 2007

Hi All,

Can anyone tell me if its possible to generate ER diagrams programmatically using SQL Server.
Any help would be highly appreciated.

Thanks
-Akash

View 1 Replies View Related

Simple Methods To Optimise SQL Queries

Sep 18, 2006

Hi, please excuse me if this seems like a daft or badly formed question.
A colleage mentioned that when constructing the where clause in a query that it was important what order the criteria were entered. They weren't sure themsleves but the idea was that starting from the bottom of the query you should add the criteria that will have the effect of reducing the data the most and then work 'up' the query back to the from clause.
As a dodgy example, if you have a staff table and you want to run a specific query on all staff over 75 that are male, then:

where gender = "male"
and age >- 75
The reasoning being that the age criteria would reduce the size of the data being worked on more than the gender criteria.
It's been a while since I've had to look at intermediate tables etc but i'm pretty sure this suggestion will not make any difference to the performance of a query.
Thanks,

Matt

View 2 Replies View Related

Cannot Use Anonymous Methods Inside SQLCLR...

Sep 15, 2006

Hi all,

The problem is: when you're trying to call in method MyMethod anonymous method that doesn't use local variables, deployment of the assembly will fail referring that MyMethod tries to store smth. in the static variable. Indeed, looking at the compiled CLR code, you can see that anonymous delegate is cached in the private static delegate and the call looks like:

If(ClassName.privateStaticDelegate == null)
ClassName.privateStaticDelegate = new MyDelegate(HiddenMethodName);
CallAnonymousMethod(ClassName.privateStaticDelegate);

Is there any workaround to fix this problem.

P.S. I googled about this problem and found only one article on it:
http://www.ayende.com/Blog/default,date,2005-12-26.aspx

View 4 Replies View Related

Full Text Search For Find Methods

Nov 10, 2006

Hello,I have to  implement a method FindProduct, which takes a parameter search query and returns product based on the query.I think i can use full text search here but don't know how. I tried to read a various way but could not figure out. 1
2
3 [DataObjectMethodAttribute(DataObjectMethodType.Select,true)]
4 public DataLayer.shopDBDataSet.ProductsDataTable GetProducts()
5 {
6 return Adapter.GetProducts();
7 }
8
9 [DataObjectMethodAttribute(DataObjectMethodType.Insert, true)]
10 public bool AddProduct(string UPCCode, string Description, string Size)
11 {
12 shopDBDataSet.ProductsDataTable products = new shopDBDataSet.ProductsDataTable();
13 shopDBDataSet.ProductsRow productRow = products.NewProductsRow();
14
15 productRow.UPCCode = UPCCode;
16 productRow.Description = Description;
17 productRow.Size = Size;
18
19 products.AddProductsRow(productRow);
20 int rowsAffected = Adapter.Update(products);
21 return rowsAffected == 1;
22 }
23
24 [DataObjectMethodAttribute(DataObjectMethodType.Delete, true)]
25 public bool DeleteProduct(string UPCCode)
26 {
27 int rowsAffected = Adapter.Delete(UPCCode);
28 return rowsAffected == 1;
29 }
30
31 [DataObjectMethodAttribute(DataObjectMethodType.Select, false)]
32 public DataLayer.shopDBDataSet.ProductsDataTable FindProducts(string searchQuery)
33 {
34
35 throw new System.NotImplementedException();
36
37 }
38
  You can see the method at line number 32.  I could not figure out the way to implement it although i know full text search is a good choice.I have following table adapterGetProducts(); GetProductsByManufacturerName(); GetProductsByManufacturerID(), and GetProductsByUPCCode();Product table contains  UPCCode, Description, Size columnsBut i need to get the product which returns the description containing search query. I know some of you have done something similar to this. Could you please guide me....Thanks in advance....  

View 8 Replies View Related

Need To Know Why Two Calculation Methods Result In Different Rounding Effect

Feb 23, 2004

Hi All,

I have a view that does some calculations on data in the database. Note in the two code snippets, that the columns being used in the calculations are all type FLOAT(53).

My confusion stems from the fact that both code snippets should be functionally the same in my "view" (*snicker* I kill me...), but they return different results which I think are related to rounding issues.

The first snippet:

CREATE VIEW dbo.VIEW_Calculate_PortfolioIndex
AS
SELECT PP.PortfolioID AS PortfolioID,
PP.CreateDate AS CreateDate,
(ROUND((PPI.CloseIndex + (PPI.CloseIndex * PP.DailyPerChg / 100.00)), 2) * PP.AvgHighPriceRatio) AS HighIndex,
CASE WHEN PPI.CloseIndex IS NULL
THEN 100.00
ELSE ROUND((PPI.CloseIndex + (PPI.CloseIndex * PP.DailyPerChg / 100.00)), 2)
END AS CloseIndex,
(ROUND((PPI.CloseIndex + (PPI.CloseIndex * PP.DailyPerChg / 100.00)), 2) * PP.AvgLowPriceRatio) AS LowIndex,
PP.Volume as Volume
FROM dbo.PortfolioIndex PPI INNER JOIN
dbo.PortfolioPerformance PP ON
PPI.PortfolioID = PP.PortfolioID AND
PPI.CreateDate = PP.PrevDate
GO

and it's result set:
PortfolioIDCreateDateHighIndexCloseIndexLowIndexVolume
102/20/2004781.11774.17769.53527896
112/20/2004757.97750.36742.93605740
122/20/2004509.92501.72494.854180516
132/20/2004988.23980.65973.58632337
142/20/20041283.261269.571259.37416145


And the second snippet:

CREATE VIEW dbo.VIEW_Calculate_PortfolioIndex
AS
SELECT PP.PortfolioID AS PortfolioID,
PP.CreateDate AS CreateDate,
(CloseIndex * PP.AvgHighPriceRatio) AS HighIndex,
CASE WHEN PPI.CloseIndex IS NULL
THEN 100.00
ELSE ROUND((PPI.CloseIndex + (PPI.CloseIndex * PP.DailyPerChg / 100.00)), 2)
END AS CloseIndex,
(CloseIndex * PP.AvgLowPriceRatio) AS LowIndex,
PP.Volume as Volume
FROM dbo.PortfolioIndex PPI INNER JOIN
dbo.PortfolioPerformance PP ON
PPI.PortfolioID = PP.PortfolioID AND
PPI.CreateDate = PP.PrevDate

which returns a different result set:
PortfolioIDCreateDateHighIndexCloseIndexLowIndexVolume
102/20/2004784.52774.17772.89527896
112/20/2004755.64750.36740.64605740
122/20/2004512.43501.72497.294180516
132/20/2004989.77980.65975.1632337
142/20/20041285.991269.571262.05416145


Specifically, I am concerned with the HighIndex and LowIndex values...since the only modification between the two code snippets is that in the second one, the HighIndex and LowIndex calculations use the column name of CloseIndex (as calculated in the select) in the calcs for those two columns, rather than repeating the code used to calculate the CloseIndex column's value.

I am confused as to why the results of the HighIndex and LowIndex caculations are different in the two selects, when the only change (in my view/expectations) is that one references the CloseIndex column, and the other one just reproduces the calculation itself

*scratching head*

any thoughts? Thanks,
Paul

View 14 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved