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,
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
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!
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.
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.
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.
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.
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
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
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.
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 ?
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))
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.
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...
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.
