I have an application that is being developed on Visual Studio 2005 (VB) and SQL Express. The application is set up to use the SQL Express on the development computer, and if an Internet or CD install is built, it installs SQL Express as expected. The question is, when it is time to deploy and put the SQL Express database on the 2003 server, how should the install on the server be done and how should we set the database path to the server?
I am in a SQL Class and I am starting to learn about SQL Parameters in conjunction with using them from a C# program. I have a couple of questions.
1. When I include an Output parameter in the list or paramters that I send, SQL always is returning a message indicating that the parameter was missing, when I know in fact that I am passing the paramter (@RtnMsg)
2. When I remove any reference to "@RtnMsg", which it can not find, and execute the stored procedure it always returns a -1 and never inserts the record.
From previous postings I have read it seems that I cannot create afunction that accepts a variable list of parameters. Seems that SQLServer does not support this.A few questions:Is this true?What is a workaround?Then how does the function COALESCE do it?Cut and pasted from online books:SyntaxCOALESCE ( expression [ ,...n ] )ArgumentsexpressionIs an expression of any type.nIs a placeholder indicating that multiple expressions can bespecified. All expressions must be of the same type or must beimplicitly convertible to the same type.
I've got a RS2000 report from Analysis services 2000. This report is working fine but after the upgrade to SQL2K5, i'm having issues with changes to parameters.
Basically i'm passing DateQuarter, CustomersLevel as parameters to the following mdx script. I've tried to change mdx script in RS2005 but no luck yet. I don't much about the parameter level changes in sql2k5. So can anyone suggest what is wrong in the script.
Code Block RS2000
="with " & "member [Measures].[Ex Price] as 'coalesceempty([Measures].[Ex Price After Trade Discount - Detail],0.00)' " & "set [Rolling Quarters] as '" + Parameters!DateQuarter.value +".lag(4):" + Parameters!DateQuarter.value + "'" & "SELECT " & "{[Measures].[Ex Price]} ON COLUMNS , " & "filter({CrossJoin([Items by Class].[Item Class Description].members, [Rolling Quarters])}, [Measures].[Ex Price] > 0) on Rows " & "FROM Sales " & "WHERE (" & Parameters!CustomersLevel.Value & ")"
RS2005
with member [Measures].[Ex Price] as 'coalesceempty([Measures].[Ex Price After Trade Discount - Detail],0.00)' set [Rolling Quarters] as '@DateQuarter' +'.lag(4):' + '@DateQuarter' SELECT {[Measures].[Ex Price]} ON COLUMNS , filter({CrossJoin([Items by Class].[Item Class Description].members, [Rolling Quarters])}, [Measures].[Ex Price] > 0) on Rows FROM Sales WHERE STRTOSET(@CustomersLevel)
We are using MS Reporting Services to generate Xmls for corporate billing statements. Our DBAs found that MS ReportingServices are querying Oracle Catalogue before executing stored procedures in RDLs to get Oracle parameter information. Is there a way to eliminate the trip to database before executing the stored procedures?
Using gridview to display the data and sql server 2000 I have a column in the database say departtime of datetime datatype that cntains the date and time resp(09/19/2007 9:00 PM). I am separating the date and time parts to display in two different textboxes say txt1(09/19/2007) contaons date and txt2(9:00 PM) contains time by using the convert in sqldatasource. Now i need to update the column in the database and i am using Updatecommand with parameters in aspx lke updatecommand = "Update table set departtime = @departtime" . How can i update my column as datetime by getting the data from 2 texboxes as now i have 2 textboxes displaying data for single column means if user edit the data in txt1 as(10/19/2007) then on click of update i need to populate the column daparttime as (10/19/2007 9:00 PM).Please let me know if you have any questions.
I have one table with categoriestblCategoriescat_id | cat_name-----------------1 | cat 12 | cat 23 | cat 34 | cat 45 | cat 56 | cat 6and one table with projects which relates to tblCategoriestblProjectsproj_id | proj_name | cat_id----------------------------1 | proj 1 | 22 | proj 2 | 23 | proj 3 | 34 | proj 4 | 2How would you create stored procedure for searching some string infiled proj_name but within multiple categoriesfor exampleCREATE PROCEDURE [spSearch](@SEARCH_STRING nvarchar(200),@CAT_ID int)ASBEGINSELECT proj_idFROM tblProjectsWHERE (proj_name LIKE '%' + @SEARCH_STRING + '%') AND (cat_id =@CAT_ID)ENDBut that one works only with one categorie and i need to search for oneor more categories at once, does anyone have a solution? Is theresomething like ellipsis (...) in C++ for MSSQL?
Hi Im writing a program that stores data to SQL server 2005. Program writen on VC++ 2003. Code is very simple and it works ok if server runs and parameters are ok.
But! if i give wrong paramterer for Data Source , it may happen if user configures it and misprints it, or if server goes offline for some reason, program just hangs on m_Connection->Open and never gives exception as it should. Any ideas what wrong? Thanks.
In SQL 2000, working with Maintenance plan wizard, what would be best settings and values should i choose in "Update Data Optimization information" window,
SELECT ArticleID, ArticleTitle, ArticleDate, Published, PublishDate, PublishEndDateFROM UserArticlesWHERE (ArticleDate >= PublishDate) AND (ArticleDate <= PublishEndDate) When I use the above on a GridView I get nothing. Using SQL manager it works great. I don't know how to pass the value of the ArticleDate field as a default parameter or I think that's what I don't know how to do. I am trying to create a app that I can set the dates an article will appear on a page and then go away depending on the date. Thanks for any help!
Disaster Recovery Options based on the following criteria.
--Currently running SQL 2012 standard edition --We have 18000 databases (same schema across databases)- majority of databases are less than 2gb-- across 64 instances approximately --Recovery needs to happen within 1 hour (Not sure that this is realistic -- We are building a new data center and building dr from the ground up.
What I have looked into is:
1. Transactional Replication: Too Much Data Not viable 2. AlwaysOn Availability Groups (Need enterprise) Again too many databases and would have to upgrade all instances 3. Log Shipping is a viable option and the only one I can come up with that would work right now. Might be a management nightmare but with this many databases probably all options with be a nightmare.
More often than not, I typically don't touch DTC on clusters anymore; however on a project where the vendor states that it's required. So a couple things here.
1) Do you really need DTC per instance or one for all? 2) Should DTC be in its own resource group or within the instance's group? 2a) If in it's own resource group, how do you tie an instance to an outside resource group? tmMappingSet right?
Hello,I am trying to construct a query across 5 tables but primarily 3tables. Plan, Provider, ProviderLocation are the three primary tablesthe other tables are lookup tables for values the other tables.PlanID is the primary in Plan andPlanProviderProviderLocationLookups---------------------------------------------PlanIDProviderIDProviderIDLookupTypePlanNamePlanIDProviderStatusLookupKeyRegionIDLastName...LookupValue....FirstName...Given a PlanID I want all the Providers with a ProviderStatus = 0I can get the query to work just fine if there are records but what Iwant is if there are no records then I at least want one record withthe Plan information. Here is a sample of the Query:SELECT pln.PlanName, pln.PlanID, l3.LookupValue as Region,p.ProviderID, p.SSNEIN, pl.DisplayLocationOnPCP,pl.NoDisplayDate, pl.ProviderStatus, pl.InvalidDate,l1.LookupValue as ReasonMain, l2.LookupValue as ReasonSub,pl.InvalidDataFROM Plans plnINNER JOIN Lookups l3 ON l3.LookupType = 'REGN'AND pln.RegionID = l3.Lookupkeyleft outer JOIN Provider p ON pln.PlanID = p.PlanIDleft outer JOIN ProviderLocation pl ON p.ProviderID = pl.ProviderIDleft outer JOIN Lookups l1 ON l1.LookupType = 'PLRM'AND pl.ReasonMain = l1.LookupKeyleft outer JOIN Lookups l2 ON l2.LookupType = 'PLX1'AND pl.ReasonSub = l2.LookupkeyWHERE pln.PlanID = '123456789' AND pl.ProviderStatus = 0ORDER BY p.PlanID, p.ProviderID, pl.SiteLocationNumI know the problew the ProviderStatus on the Where clause is keepingany records from being returned but I'm not good enough at this toanother select.Can anybody give me some suggestions?ThanksDavid
I am new to Reporting Services and hope that what I am looking to do is within capabilities :-)
I have many identical schema databases residing on a number of data servers. These support individual clients accessing them via a web interface. What I need to be able to do is run reports across all of the databases. So the layout is:
Dataserver A
Database A1
Database A2
Database A3
Dataserver B
Database B1
Database B2
Dataserver C
Database C1
Database C2
Database C3
I would like to run a report that pulls table data from A1, A2, A3, B1, B2, C1, C2, C3
Now the actual number of servers is 7 and the number of databases is close to 1000. All servers are running SQL2005.
Is this something that Reporting Services is able to handle or do I need to look at some other solution?
Is it possible to install and configure the MSDTC resource in a SQL Server 2000 cluster after SQL is installed and running?
When I recently went through a rebuild of my cluster, I forgot to install the resource before installing SQL Server. Now, if I install and bring online the MSDTC resource the SQL disk groups will not fail over correctly. The SQL Server resource will not come online.
Thanks in advance for any help. I would really like to avoid rebuilding again.
Greetings,We are trying to set up a set of "Leading Practices" for ourdevelopers, as well as ourselves, and hope some gentle reader canrecommend some documentation in favor of what appears to be the rightposition to take.We do not allow third party applications to run on our SQL Servers. Wewant to include DTS Packages under the definition of third partyapplications, insisting instead that the developers save theirpackages as COM Formatted files into their source code control systemsand run them from their app servers. The devlopers would like to hearthis from someone besides ourselves.While strong recomendations to remove guest access to MSDB altogetherabound, I have been unable to find a straight forward discussion ofthe advantages of structured file storage and app server off load ofDTS packages.Can anyone suggest any articles, white papers, rants, etc attemptingto formulate a solution to the benefits of taking msdb away fromguest, with the advantages of running DTS from an App server orworkstation platform, with the packages protected in source codecontrol?Thank youJohn Pollinsjpollins @ eqt . com
Hi all, Im new here so il start with a little introduction of myself, My name is Arjan im 19 years old from Holland, and i work for a company to compleet my ICT Education.
My situation:
My boss gave me a server with server 2003 standard and Sql server 2005 and visual studio 2005 installed already, he asked me if i could figure out how the 'new' reporting services work, Im pretty new to SQL and the reporting service but i figured out i had to install asp.net / frameworks and IIS.
So right now i wanna start the Reporting Services Configuration Manager and i get an error that says 'Invalid namespace' and when im trying to approach by using my browser i get 'page not found' so obviously their is Alot wrong. I asked my boss if i could not reinstall everything and do it in the correct order (IIS / ASP.net / Frameworks before installing SQL server 2k5 but that was not an option because we dont seem to have the cd's anymore.
The server is not connected to any network or the internet.
My Question:
Is their any way to fix this? and if yes could anyone tell me where to start
Thanks in Advance!
ps if their is information or logs that u need in order to help me just say so :-)
Is it possible to create a stored procedure that can be used on multiple dBs??
For instance, I want to create a stored procedure and use it on DB1 and DB2. Right now, I can create a stored procedure in DB1 but only I can run it. I want to run the stored procedures on DB2 that I created in DB1..
I hope this makes sense.. Any insight into the workings of SPs would be most appreciated...
-- declare table declare @Table1 table(ID int, Value int )-- Insert sample data provided into tableinsert into @Table1select 1 , 2 union allselect 2 , 1 union allselect 7 , 2 -- declare table declare @Table2 table(ID int, Value int )-- Insert sample data provided into tableinsert into @Table2select 1 , 2 union allselect 5 , 1 union allselect 3 , 2 -- declare table declare @Table3 table(ID int, Value int )-- Insert sample data provided into tableinsert into @Table3select 1 , 2 union allselect 2 , 1 union allselect 3 , 2 -- declare table declare @Table4 table(ID int, Value int )-- Insert sample data provided into tableinsert into @Table4select 5 , 2 union allselect 2 , 1 union allselect 10 , 2 /*Is there anyway I can write one sql query which will give me the following result:IDs = All unique ID from all tablesSumOfAllValueFromAllTables =for the same ID, value from Table1+for the same ID, value from Table2 + so on ...IDs - SumOfAllValueFromAllTables===============================1 - 62 - 33 - 45 - 37 - 210 - 2*/--I have tried by the following way, but for more tables, it is becoming complicated, because I have 12 tables.--Is there any better way to do it? Thanks for the help.select coalesce(t1.ID,t2.ID) as IDs, coalesce(sum(t1.value),0) +coalesce(sum(t2.value),0) as SumOfAllValueFromAllTablesfrom @Table1 t1full join @Table2 t2 on t1.id=t2.idgroup by t1.ID,t2.IDorder by IDs
I have a search form that takes 5 inputs from textboxes or Drop Down Lists.
I built a query that works just fine in Analyizer, but the second i try to turn it into a sproc that takes parameters I can only get it to work with one parameter so far....
' Inner Join ftr_location On ftr_location.loc_id = ftr_file.loc_id' + ' Inner Join ftr_Status On ftr_status.status_id = ftr_file.status_id' + ' Inner Join ftr_doc_types on ftr_doc_types.file_type_id = ftr_file.file_type_id' + ' WHERE 1=1 '
If @ResultLocation Is Not Null Set @SQLString = @SQLString + 'And ftr_file.loc_id=' + cast @ResultLocation If @ResultLocation Is Not Null Set @SQLString = @SQLString + 'And ftr_file.file_date=' + cast @ResultFileDate
EXEC (@SQLString) GO
Any feedback would be most helpful, as I can get it to work with one parameter but not when I add teh second in the mix.
I have a dataadapter on my asp.net page and am having issues passing values to the sql statement. See below:
SELECT tblTemp.SensorID, tblSensor.SensorNum, tblTemp.TempTime, tblTemp.TempVal FROM tblTemp INNER JOIN tblSensor ON tblTemp.SensorID = tblSensor.SensorID WHERE (tblTemp.TempTime BETWEEN @From AND @To) AND (tblTemp.SensorID IN (@Sensor)) ORDER BY tblTemp.TempTime
@Sensor is populated in vb with the selected values from a checkboxlist control. I attempted to test this in the "Data Adapter Preview" window, but continually get an error. Is there a way to pass multiple values to a parameter? Any help would be appreciated, thanks!
I have an app that is critical to our business. It handles and syncronises several SQL Servers, checks integrety etc. I need to make the app so it can run a few things at once. Does anyone have any experience with this? Currently we use Delphi and ADO. I have been fiddling with DMO to get more performance - I am not sure ADO is very quick for some I tasks I need to do.
I suppose my main question *really* is does ADO/DMO multi-thread and has anyone tried it. If not how do people do it?
I and trying to get some help with Multi-Value parameters in Reporting Services 2005 in VS 2005. I’m new to the product and struggling with the TSQL syntax.
(I have simplified my SQL statement for the purposes of this question) I have a dataset below which has 1 parameter;
="SELECT * " & " FROM dbo.Table" & Iif(Parameters!Sex.Value = "ALL",""," AND dbo.Table.Sex = '" & Parameters!Sex.Value &"'" )
The Sex parameter dataset contains F, M, U and ALL – the above parameter allows me to select on any of the 4 options. I would like to use the multi-value parameter but am struggling to grasp the syntax. Believe me I have tried a few things.
Could someone provide me with an idiots guide on how to make my basic parameter a multi-value parameter?
I have a SQL2k5 table capturing transaction via an online vendor application. I need to join this data with cooresponding data that exist within a 2003 MS Access .MDB application. I've had recent training in SQL Serv 2005 T-SQL and programming. This is what I'm planning but not if this is the best/simple plan.
write After Insert trigger for SQL tbl to invoke a sp write After Insert trigger for SQL tbl to invoke a sp (OK with this) write a sp to select last inserted record from SQL tbl (OK with this) sp will use pk_col to pull data from linked MS Access 2003 using OPENQUERY or four part naming (is one way better than the other?) insert Parent record into MS Access parent tbl (after insert, how can i get Parent Key from this insert to use as Child FK col? will @@identity work?) insert Child record into MS Access child tbl (how can i get Child Key from this insert to use as GrandChild FK col? @@identity?) insert GrandChild record into MS Access GrandChild table
any help greatly appreciated! Thanks for being there....
