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
ADVERTISEMENT
Jul 20, 2005
I have these 3 queries - they are the same except each fetches record countsfor one of 3 different record types, nSubsets (type 0), nAssets (type 1) andnImages (type 2). Is there any way I could get all 3 of these (based on theNode.Type integer) with a single query?IF @Error = 0BEGINSELECT @nSubsets = COUNT(*)FROM NodeINNER JOIN AdjacencyON Adjacency.ID_Node = Node.IDWHERE Adjacency.Path LIKE @nodepath + '%'ANDNode.Type = 0SET @Error = @ERRORENDIF @Error = 0BEGINSELECT @nAssets = COUNT(*)FROM NodeINNER JOIN AdjacencyON Adjacency.ID_Node = Node.IDWHERE Adjacency.Path LIKE @nodepath + '%'ANDNode.Type = 1SET @Error = @ERRORENDIF @Error = 0BEGINSELECT @nImages = COUNT(*)FROM NodeINNER JOIN AdjacencyON Adjacency.ID_Node = Node.IDWHERE Adjacency.Path LIKE @nodepath + '%'ANDNode.Type = 2SET @Error = @ERROREND
View 5 Replies
View Related
Dec 24, 2005
Allow me to preface this by saying I'm really excited about writing stored procedures, etc. in C#!! Now...on to my question. When an application needs a simple result set (i.e., SELECT....), why use C# to write this? In the samples I've seen, the developer ends up writing the select statement anyway in the CLR hosted stored procedure. What would be the benefit? You end up writing more code just to write the same query. Now, executing complex logic is another story. I see HUGE benefits to hosting classes on the data server. Anyway, what is Microsoft's answer to this question? Is it recommended that one still write simple statements in T-SQL and leave the complex stuff to CLR code? Thanks in advance for your advice!
View 5 Replies
View Related
Jan 25, 2008
how can I fill a data set with a multi parameter filter:
I want to create in my form few textboxes and create a query that filters the dataset to show only the specific data.
but the problem is if the user doesn't supply any words in some textbox the dataset is not filled.
it would be a good solution for me if I can either:
for example let's say my query is SELECT * FROM table WHERE column = @parameter,
i want to write before the query something like "if @parameter="" then replace the query syntax near WHERE with * so it looks SELECT * FROM table WHERE column = *.
or:
if there is some character or character series that sql reconizes in a parameter as an asterisk.
thank you for your time!
View 37 Replies
View Related
May 5, 2007
Hi everybody!
I have made a simple application with VB6 and SQL 2005 Express.
And after testing it in a network simulated with Virtual-Machine Workstation, the "real world" application was first getting slower and slower, and finally giving timeout errors.
It is even difficult to connect. The application times out always at 30 secs although the ado connection timeout was established longer (45)
I have checked on the machine where the server is installed, just in case the slow down was due to network problems, but it fail locally too.
Any help is wellcome
View 6 Replies
View Related
Jun 8, 2006
I have been trying to export an sql2000 database to sql2005 with no luck. I continuosly get the error
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
I have looked upon all support material and have installed the latest sql2005 service pack, but this has not fixed the problem. a simple query as:
select * from [transaction] ta,transactionentry tr
where tr.transactionnumber=ta.transactionnumber and
glacctid=6 and ta.[time]> '3/01/2006'
would produce this fatal error.
the current version I am using is:
Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86) Oct 14 2005 00:33:37 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
This problem is happening on some of the most important tables in the database.
the error log contains the following:
2006-06-06 11:43:32.59 spid54 ***Stack Dump being sent to C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLLOGSQLDump0001.txt
2006-06-06 11:43:32.59 spid54 SqlDumpExceptionHandler: Process 54 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
2006-06-06 11:43:32.59 spid54 * *******************************************************************************
2006-06-06 11:43:32.59 spid54 *
2006-06-06 11:43:32.59 spid54 * BEGIN STACK DUMP:
2006-06-06 11:43:32.59 spid54 * 06/06/06 11:43:32 spid 54
2006-06-06 11:43:32.59 spid54 *
2006-06-06 11:43:32.59 spid54 *
2006-06-06 11:43:32.59 spid54 * Exception Address = 0136B4F0 Module(sqlservr+0036B4F0)
2006-06-06 11:43:32.59 spid54 * Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
2006-06-06 11:43:32.59 spid54 * Access Violation occurred writing address FFFFFFFC
2006-06-06 11:43:32.59 spid54 * Input Buffer 260 bytes -
2006-06-06 11:43:32.59 spid54 * select * from [transaction] tr,transactionentry te where tr.
2006-06-06 11:43:32.59 spid54 * transactionnumber = te.transactionnumber and glacctid = 6
2006-06-06 11:43:32.59 spid54 *
2006-06-06 11:43:32.59 spid54 *
2006-06-06 11:43:32.59 spid54 * MODULE BASE END SIZE
2006-06-06 11:43:32.59 spid54 * sqlservr 01000000 02BA7FFF 01ba8000
2006-06-06 11:43:32.59 spid54 * ntdll 7C800000 7C8BFFFF 000c0000
2006-06-06 11:43:32.59 spid54 * kernel32 77E40000 77F41FFF 00102000
2006-06-06 11:43:32.59 spid54 * MSVCR80 78130000 781CAFFF 0009b000
2006-06-06 11:43:32.59 spid54 * msvcrt 77BA0000 77BF9FFF 0005a000
I have followed instructions on some support issues posted but none have helped. If anyone has any solution to this problem PLEASE HELP!!!!.
Thanks.
View 7 Replies
View Related
Feb 9, 2007
Hi,
My scenario:
I have a master securities table which has 7 fields. As a part of the daily process I am uploading flat files into database tables. The flat files contains the master(static) security data as well as the analytics(transaction) data. I need to
1) separate the master (static) data from the flat files,
2) check whether that data is present in the master table, if not then insert that data into the master table
3) If data present then move that existing record to an history table and then update the main master table.
All the 7 fields need to be checked to uniquely identify a single record in the master table.
How can this be done? Whether we can us a combination of data flow items or write a sql procedure to do all this.
Thanks in advance for your help.
Regards,
$wapnil
View 4 Replies
View Related
Jun 2, 2006
Hello All,
I have a query that I could use some help with. I would like to see how I could make this query more optimised. I am not hte best at Joins so if someone could help me with this that would be great. Right now its taking about 24-30 secs to run which is a no go. Im wondering if some kind of join would work better?
thank you much
-jes
SELECTrecipes_signed_up_for.user_id,
recipes_signed_up_for.recipe_id,
recipes_signed_up_for.use_in_lesson_plan AS use_in_lesson_plan,
recipes_signed_up_for.attached_lesson_plan AS attached_lesson_plan,
recipes_signed_up_for.participate,
recipes_signed_up_for.authorized AS authorized,
recipes_signed_up_for.date_signed_up_for AS date_signed_up_for,
users.user_id,
users.f_name AS f_name,
users.l_name AS l_name,
users.email_address AS email_address,
users.primary_phone AS primary_phone,
recipes.recipe_name AS recipe_name,
recipes.recipe_id AS recipe_id,
recipes.number_served AS number_served,
recipes.last_day_to_sign_up_for AS last_day_to_sign_up_for,
recipes.recipe_instructions AS recipe_instructions,
recipes.active_recipe,
recipe_ingredients.recipe_ingredient_id,
recipe_ingredients.recipe_id,
recipe_ingredients.ingredient_id,
recipe_ingredients.ingredient_quantity AS ingredient_quantity,
recipe_ingredients.ingredient_unit,
recipe_ingredients.active_ingredient AS active_ingredient,
ingredients.ingredient_id,
ingredients.ingredient AS ingredient_name,
recipes_signed_up_for_ingredients_needed.ingredient_id,
recipes_signed_up_for_ingredients_needed.ingredient_needed AS ingredient_needed
FROMrecipes, recipe_ingredients, ingredients, recipes_signed_up_for_ingredients_needed, recipes_signed_up_for, users, locations, regions
WHERErecipes.recipe_id = recipe_ingredients.recipe_id
AND recipe_ingredients.ingredient_id = ingredients.ingredient_id
AND recipes_signed_up_for_ingredients_needed.user_id = #url.user_id#
AND recipes_signed_up_for_ingredients_needed.ingredient_id = recipe_ingredients.ingredient_id
AND recipes_signed_up_for_ingredients_needed.ingredient_needed = 1
AND recipes.recipe_id = recipes_signed_up_for.recipe_id
AND recipes_signed_up_for.user_id = users.user_id
AND recipes_signed_up_for.user_id = #URL.user_id#
AND recipes_signed_up_for.participate = 1
AND locations.region_id = regions.region_id
AND recipes_signed_up_for.recipe_id IN (SELECTrecipe_id
FROMrecipes
WHEREactive_recipe = 1)
ORDER BY recipes.recipe_name
View 9 Replies
View Related
Oct 11, 2005
Hi guysIs there any way I can run this query faster? Should I take out the ORDER BYclause? This is supposed to return 17,000 rows and takes around 30 minutes orso. Is there no way at all to get this result faster?select r.AttorneyName, r.sitename, r.applicationid, r.clientsurname, r.clientinitials, r.clientidno, r.grantedamount, r.bankname,r.accountnumber, r.status, r.grantdate, r.consultantname, r.propertyaddress,r.erfdescription, r.commenthistory, br.expectedregdatefrom bondtrak..rptdetail rjoin ebondprd..bankresponse bron br.applicationid = r.applicationidwherer.rundate = '20051010'and r.primarybankind = 'Y'and r.status = 'granted'and r.statusdate between '20020101' and '20050930'and r.businessunit in ('bond choice', 'ppl')order by r.sitename, r.consultantname, r.statusdateThanks for any help.Driesen--Message posted via SQLMonster.comhttp://www.sqlmonster.com/Uwe/Forum...eneral/200510/1
View 6 Replies
View Related
May 29, 2008
hi,
I want to know how to optimise this query, the results almost reach 6000 rows, I want to speed this query.
condition2=Select Distinct A,B,B+C+D,E,F,G From a.table where condition1 order by A,B,B+C+D,E,F,G
Select Sum(amount) From b.table where condition2 And date=datetime.
Thanks in advance.
View 1 Replies
View Related
Apr 18, 2008
Hi,
I use this piece of code in a trigger to check if new entered row has unique order number in the given year:
IF EXISTS
(
SELECT COUNT(*)
FROM SPD_Orders
GROUP BY ORD_Year, ORD_Number
HAVING COUNT(*) <> 1
)
RAISERROR('Order with this No exists in given year, 11, 1)
but this code causes deadlocks in my database.
Any suggestions how can I change it to avoid these deadlock?
1. Some constrain? If yes, so how to write it?
2. Index like to enable query optimiser to change execution plan?
CREATE NONCLUSTERED INDEX [_dta_index_SPD_Orders_10_1429580131__K8_K7] ON [dbo].[SPD_Orders]
(
[ORD_Year] ASC,
[ORD_Number] ASC
3. Same index as above but unique and then I will not have to use this trigger, cause database itself with return an error?
4. Any other, the best solution ?
Please help.
Thanks.
Przemo
View 4 Replies
View Related
Apr 11, 2008
Ok I have the following SQL, I have a subquery in the SELECT part but also the same subquery in the WHERE part as well.What I'm trying to do is get all parents that have children OR get all parents with no children OR just get all parents regardless (@HasResponses is a BIT that can be 1, 0 or null). At the same time I want to count the total number of children in the select list, but I'm having to copy the same subquery in the SELECT and WHERE parts which doesn't seem terribly optimal to me (maybe it is, that's why I'm asking). I've tried referencing the column alias in the select list (AS [Responses]) for the where part (@HasResponses = 0 AND [Responses] = 0), but it doesn't seem to work.Is this the most optimal way to do it? Is there a better way? I'm working with SQL Server 2005.SELECTf.FeedbackText AS [Feedback Comment],u.Name AS [Feedback Author],f.CreatedDate AS [Created On],(SELECT COUNT(*)FROM FeedbackResponse frWHERE fr.FeedbackID = f.ID) AS [Responses]FROM Feedback fINNER JOIN [User] u ON f.StaffID = u.StaffIDWHERE f.CreatedDate >= @DateFromAND f.CreatedDate <= @DateToAND(@HasResponses IS NULLOR(@HasResponses = 1 AND(SELECT COUNT(*)FROM FeedbackResponse frWHERE fr.FeedbackID = f.ID) > 0)OR(@HasResponses = 0 AND(SELECT COUNT(*)FROM FeedbackResponse frWHERE fr.FeedbackID = f.ID) = 0))
View 2 Replies
View Related
Mar 11, 2008
Hi,
Please can you help me,
I have created an database with 3 fields
View 3 Replies
View Related
Mar 11, 2008
Hi,
Please can you help me,
I have created an database with 3 indexed (not clustered) fields
key : bytes 100
error : integer
status : bytes 6
I would like to know how to import 3 million or more records quickly.
If I don't index the the fields, the operation takes 3 hours. With indexed fields it takes overnight. Therefore, how can i optomise the import operation from a .txt file.
Thanks and regards.
View 3 Replies
View Related
May 26, 2005
SQL Server 2000, ASP.Net 1.1
I've been writing this stuff for a while, and can't seem to come to the
conclusion of how I should be retrieving data and assigning this data
to variables.
Since i'm using SQL Server, I'm convinced that I should be using the
datareaders GetSqlDouble (or whatever) function, but this would mean i
need my local variables to be one of the SQL types. The problem
with that is, that there will have to be lots of conversions done by me
to be able to use a SQL type in my application.
For instance, I have a class where i'm retrieving dates. In order
to retrieve them correctly (Null values included), I need to retrieve
them with GetSqlDateTime(), then when it comes time to display the date
in a table, i must first check for nulls, then convert to a
string. This seems to be very cumbersome. Would I be better
off just using GetDateTime(), and the .ToString method, and ignoring
Sql Types all together?
so, basically, how are you guys using your sql server data? with
the supplied sql types, and doing all of the post-processing work
manually? I feel like i'm having trouble conveying my
issue...hopefully someone knows what i mean....i'd just like some
direction to save trouble in the long run, since i feel like there's
got to be a better way...
Confused!
Thanks,
JJ
View 1 Replies
View Related
Apr 30, 2008
Hello,
If i want to optimise stored procedure response time.
Which things i need to be considered?
Thanks in advance.
View 3 Replies
View Related
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
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
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
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
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
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
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
Mar 16, 2004
Hello everyone:
Can anyone list the general performance tuning methods? Thanks a lot.
ZYT
View 1 Replies
View Related
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
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
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 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'.
sh.SessionId = rs.SessionHeaderValue.SessionId
~~~~~~~~~~~~~~~~~~~~~
--------------------------------------------------------------------------------------------------------------------
Any advice on how to fix this would be graetly appreciated. Thanks!
View 6 Replies
View Related
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
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
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
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
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
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
