Hi,
I installed Reporting Services and forgot to name the directories. So when the installation was complete I made the virtual directories myself. But now when I try to view them It says "You are not Authorized to see this page".
Whats the deal with that ?
Hello,
I am trying to migrate my reports from SQL server 2000 reporting services 32bit to 2005 64bit. I am following the migration steps that MS specified.
Restored my Reportserver and ReportserverTempDB databases
Then I was using the configure Report services to upgrade these databases but I always end up getting the follwoing exception when I run the upgrade on the "Database Setup" configuration for 'ReportServerTempDB' database
System.Data.SqlClient.SqlException: Could not locate entry in sysdatabases for database 'ReportServerTempDBTempDB'. No entry found with that name. Make sure that the name is entered correctly.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at Microsoft.ReportingServices.Common.DBUtils.ApplyScript(String connectionString, String script)
at ReportServicesConfigUI.SqlClientTools.SqlTools.ApplyScript(String connectionString, String script)
It's driving me crazy, why is it looking for 'ReportServerTempDBTempDB' in the catalog instead of 'ReportServerTempDB'?
Is it possible to migrate from 32bit to 64bit?
Any help is appreciated
I'm attempting to obtain a cost effective solution for my existing customers to develop reports on their SQL 2000 Server installations using their Reporting Services 2000. With products like Visual Basic.NET 2003 becoming almost impossible to obtain, I have at least one customer who is running into a dead end.
One option possibly is the SQL Express with Advanced Services download, which has Reporting Services. My questions are as follows:
Can the report designer component of SQL Express Reporting Services be configured to connect to an external database (which would happen to be a SQL 2000 database) to establish its datasets?
Does the resultant designed report end up in an RDL file? If the customer published this report via the Reporting Services 2000 Report Manager, would the report be able to run?
Sorry for asking a question like this that I could probably answer on my own, but my customer needed this answer yesterday. I have scoured the web and microsoft sites - and posted a question on the official SQL Reporting Services cateogy ... in an attempt to answer the basic question of how to design reports for Reporting Services 2000 in the absence of Visual Basic.NET 2003 (or other .NET 2003 tools) with no success.
Thanks to anyone who can help.
-- Mark
I work in a big project and we will begin in using reporting services as the base technique for reports and I will be responsible for this part. but I have a problem I will discuss in the following:
Currently: We use currently devexpress reports and we have 2 languages(Arabic and English). the data in tables saved in two ways (Arabic and English). when the end user change the language of the web site the report data language changed when run it.
Example:
we have table with (ID, NAME_AR, NAME_EN, JobTitle_AR, JobTitle_EN). designed report will display(ID, NAME_EN, JobTitle_EN) . but the end user change the language of the system the report will
dispaly(ID, NAME_AR, JobTitle_AR)
Hint:this done pragmatically
how to do this in reporting services.
Hi There,
Our DBA has installed reporting services on a server and now in order to access the report manager, one has to be an Admin on that Server. I am guessing that there is a mistake in the configuration of Reporting Services. Usually it should allow anybody who was added to the roles in the properties section of the Report Manager, right? I have also added the users to the DB..
Also I am using Windows Authentication to access Report Catalog items (Reporting Services is installed on Server2) from a web Application(deployed on Server1) and displaying the report using report viewer. For some reason, server1 has to be in an Admin role on Server2 to access the report catalog/report. This is kinda strange for me as I don't want everybody to be an Admin on Server2. Can anybody please point in the right direction?
Thanks.
We have installed SQL Server 2005 with reporting services.When tried to open getting errors as below: how to avoid such errors See the end of this message for details on invoking just-in-time (JIT) debugging instead of this dialog box.
ReportServicesConfigUI.WMIProvider.WMIProviderException: A WMI error has occurred and no additional error information is available. ---> System.Runtime.InteropServices.COMException (0x8000000A)
at System.Runtime.InteropServices.Marshal.ThrowExceptionForHRInternal(Int32 errorCode, IntPtr errorInfo)
[code]...
Hi all,
I have some problem about reporting service add-in.
After I install reporting service add-in for SharePoint, reporting service menu does not appear in Application Management Tab in SharePoint Central Administration.
I try to uninstall and re-install again, it remain not work.
How can I solve this problem?
Thank you very much.
Is there any way to get the report toolbar using SOAP Api in SSRS reporting.
View 6 Replies View RelatedWe've got a requirement to build the real time report. user can browse report at any point of time and need to see the latest data(stock market) in the report.
I've few options down...
1. Directly point to OLTP database as source and write stored procedure to show result set.
2. Replicate the database and write the SP's to reports. To avoid pointing directly to OLTP db.
3. To build the datawarehouse with dim & facts to show it in reports. I prefer this as a standard method, but this would have some latency depending on trasaction load which will differ from the requirement.
HI There
We are required to gather certain information regarding reporting services.
Which reports were requested, by whom , how long did they run for, what parameters were passed etc etc.
I see that the system tables do provide some of this information, is there a 3rd party tool or something we can use to gather all this type of information for us?
This is for reporting services 2000 and 2005.
Thanx
I hate access, I wish we could get away from it. I cant view employee pictures anymore, and every time theres and upgrade to a different version that means different problems. We are going to upgrade from 2003 to 2007. Good grief SQL doesnt need access, its too good for it anyways. Theres gotta be some different software out there that I can change too. Its just that I'm not a programmer. I keep hearing about Visual Fox Pro, that its easy but I have no idea. Anyone got any suggestions??
View 9 Replies View RelatedHi,
I have setup SQL Reporitng Services on Windows 2003 server. Environment is like this on server:
- Framework 2.0
- SQL Server 2005 Enterprise Edition (with SQL Reporting Services)
- IIS 6.0
- Internet Explorer 7
- Microsoft Ajax 1.0
- Active Direcotry
I have configured SRS and it was done successfully, I got two Virtual Directories (ReportServer and Reports).
My problems are like this:
- When I type http://localhost/Reports in browser, It display ReportBuilder page with "Home" hyperlink. Nothing else is displayed on this page!!! (I am able to execute http://localhost/ReportServer/ I have login as Administrator on my server.)
- I created SQL Report Project in Visual Studio 2005 and created a Shared Data Source and a Report, I am able to preview the report in IDE. Then I tried to build and deploy it on above server, For it I modified project properties and changed TargetServerURL to above server (I tried following combinations for this property without any success http://192.168.115.17/ReportServer / http://ss6/ReportServer/ / http://localhost/ReportServer).
Exact error message is:
A connection could not be made to the report server http://192.168.115.17/ReportServer/.
Additional Information:
The attempt to connect to the server failed. Check your connection information and that the report server is a compatible version. (Microsoft.ReportingServices.Designer)
What could be the problem and solution? SRS is already running.
thanks inadvance,
Regards,
ND
Hello everybody ,
i have a severe problem here regarding production DBs
the system heavily utilizes UDFs in queries in bad behaviour
being used in select and where clauses , the queries can take considerable time
i have managed to enhance issues regarding indexes and select clauses , but UDf in where clauses still a problem bcz they contain some logic i cann't make it inline
here is an example :
select col1,
col2,
col3,
dbo.fn_ConvertUnit(TotalArea,AreaUnitID,@SearchOtherAreaUnit) , dbo.fnCurrencyConvert(SaleCurrency,@SearchCurrencyUnit,0,furnishedSalePrice) ,from listing inner join ResidentialSeller on listing.id=ResidentialSeller.idwhere (@TotalAreaFrom is null or dbo.fn_ConvertUnit(TotalArea,AreaUnitID,@SearchOtherAreaUnit) >= @TotalAreaFrom ) and (@TotalAreaTo is null or dbo.fn_ConvertUnit(TotalArea,AreaUnitID,@SearchOtherAreaUnit) <= @TotalAreaTo ) and (@SalePriceFurnishedFrom=0 or (furnishedSalePrice>0 and dbo.fnCurrencyConvert(SaleCurrency,@SearchCurrencyUnit,0,furnishedSalePrice)>=@SalePriceFrom)) and (@SalePriceFurnishedTo=1000000000 or (furnishedSalePrice>0 and dbo.fnCurrencyConvert(SaleCurrency,@SearchCurrencyUnit,0,furnishedSalePrice)<=@SalePriceTo))
the two UDFs fn_ConvertUnit is used to convert from measure unit to another (like from Meter to feet)
and fnCurrencyConvert is from currency to another (like from $ to Euro) based on predefined rate
CREATE FUNCTION [dbo].[fn_ConvertUnit] (@Uvalue float,@CfromID tinyint,@CToID tinyint)RETURNS float AS BEGIN if ((@Uvalue is null) or (@CfromID is null) or (@CToID is null))begin return nullend declare @fac intdeclare @temp floatif (@CfromID=@CToID)or (@Uvalue=0)beginreturn @Uvalueendif exists(select * from LookupUnitConversion where [LookupUnitConversion_UnitFrom]=@CfromID and [LookupUnitConversion_UnitTo]=@CToID )begin select @fac=LookupUnitConversion_Factor from LookupERAUnitConversion where [LookupUnitConversion_UnitFrom]=@CfromID and [LookupUnitConversion_UnitTo]=@CToID if (@fac>1) begin set @temp=1.79E308 /@fac end else begin set @temp=1.79E308 *@fac-- set @temp=@fac end if (@temp<@Uvalue) begin-- return 1.79E10return 1000000000-- A Big number enough to be compared by any number user can type end else begin RETURN @Uvalue*@fac endendif exists(select * from LookupUnitConversion where [LookupUnitConversion_UnitFrom]=@CToID and [LookupUnitConversion_UnitTo]=@CfromID )begin select @fac=LookupUnitConversion_Factor from LookupERAUnitConversion where [LookupUnitConversion_UnitFrom]=@CToID and [LookupUnitConversion_UnitTo]=@CfromID if (@fac>1) begin set @temp=1.79E308 /@fac end else begin set @temp=1.79E308 *@fac end if (@temp<@Uvalue) begin return 0 end else begin RETURN 1/(@Uvalue*@fac) endendreturn -1END
-------------------------------------------------
-------------------------------------------------
CREATE FUNCTION fnCurrencyConvert (@FromCurrency int, @ToCurrency int, @Country int, -- This prameter is useless @AmountOfMoney float)RETURNS floatASBEGINif( (@FromCurrency is null ) or (@ToCurrency is null) or (@AmountOfMoney is null))begin return nullenddeclare @temp float -- will carry the max value without exceptionif @FromCurrency=@ToCurrency or @AmountOfMoney=0beginreturn @AmountOfMoneyenddeclare @fac floatif exists(select * from CurrencyConvertion where cFromID=@FromCurrency and cToID=@ToCurrency )begin select @fac=Factor from CurrencyConvertion where cFromID=@FromCurrency and cToID=@ToCurrency order by ConvertionDate desc if (@fac>1) begin set @temp=1.79E308 /@fac end else begin set @temp=1.79E308 *@fac end if (@temp<@AmountOfMoney) begin return 1.79E308 end else begin RETURN @AmountOfMoney*@fac endendif exists(select * from CurrencyConvertion where cFromID=@ToCurrency and cToID=@FromCurrency )begin select @fac=Factor from CurrencyConvertion where cFromID=@ToCurrency and cToID=@FromCurrency order by ConvertionDate desc if (@fac>1) begin set @temp=1.79E308 /@fac end else begin set @temp=1.79E308 *@fac end if (@temp<@AmountOfMoney) begin return 0 end else begin RETURN 1/(@AmountOfMoney*@fac) endendreturn -1END
i will appreciate any help regarding these two Udfs as they r the most problematic ones , other suggesions r highly appreciated
thanks for ur time
regards
Hi,
I have just install SQL 2005 SP2 and trying to get Window SharePoint Services V3 integrated with SQL 2005 SP2 reporting services.
In SharePoint Central Administration, I select the Reporting Services Integration page and have setup the Report Server Web Service URL and Authentication Mode. I then goto Grant database access, specify the SQL server name, get promted for a username and password that has access SQL Reportserver and get the following error "The group name could not be found"
Does anyone have any ideas?
Thanks
We built our prod server [vm] with SQL Server 2008 R2 on Server 2008 R2. It works nicely. Then we made a copy of the VM and renamed it (so our test environment would be IDENTICAL to production). After that, SSRS was broken: I get "HTTP Error 503. The service is unavailable." I can't connect via http, or SSMS.
We have tried:
* Running SSRS config tool (several times)
* Running with/without the IIS Server Role
* Dropping & recreating the SSRS keys
* Recreating the ReportServer database, etc
* Checking all of the accounts, permissions, etc
* Running SQL Repair
* Going through the registry to fix any references to the machine's old name
* Uninstalling, reinstalling SSRS
* Completely uninstalling ALL of the parts of SQL Server 2008 R2, deleting all directories, removing references to SQL Server from the registry, rebooting, reinstalling everything.
None of this has worked. SSRS [R2] is still 503 on our test box.
the SSRS [NT] service seems to run, without error. The Event Viewer doesn't seem to be recording any errors. The SSRS logs say that the default URL is wrong, but we get the same error in Prod, and Prod works fine. The other SQL Logs say something about not being able to contact the service. However, as I said, the [NT] service seems to be working fine.
I think I've seen a similar post on a blog or on the forums - but it seems like this should be possible -
I have an MDX query - that works fine in SQL Enterprise Manager, and has my dimension members on columns, and my measures on the rows. When I try the same query in Reporting Services, I get the error:
"The query cannot be prepared: The query must have at least one axis. The first axis of the query should not have multiple hierarchies, nor should it reference any dimension other than the Measures dimension..
Parameter name: mdx (MDXQueryGenerator)"
Although it works when you pivot the view, I really need my data presented with the members on the columns and the measures on the rows. Another forum post mentioned using the SQL 9.0 driver, but I can't see this listed anywhere (the only one I see is the .NET framework Data Provider for Microsoft Analysis Services).
Here's what my query looks like -
SELECT
{ [Time].[Month].&[2006-09-01T00:00:00] ,
[Time].[Month].&[2006-10-01T00:00:00],
[Time].[Month].&[2006-11-01T00:00:00],
[Time].[Month].&[2006-12-01T00:00:00]
} on COLUMNS,
{
[Measures].[Unique Users],
[Measures].[UU Pct 1],
[Measures].[UU Pct 2],
} ON ROWS
FROM [Cube]
Any ideas?
Thanks,
Arjun
Hi All,
I'm trying to create reports in RS2005 using AS2000 as my data source. I understand that if I use RS2005 on AS2000, I wont be able to enjoy the OLAP based parameters as in using AS2005. Does anyone know an easy way to easily use Parameters in RS2005 while still using AS2000?
Regards,
Joseph
I'm using Reporting Services 2005 SP1 and wrote some code to render reports server-side.
My sample report has few parameters that must be passed so I pass these parameters with code.
<My code>
ReportServiceExecution.ReportExecutionService rs = new ReportServiceExecution.ReportExecutionService();
rs.Credentials = System.Net.CredentialCache.DefaultCredentials;
string extension, mimeType, encoding;
ReportServiceExecution.Warning[] warnings;
string[] streamID;
ParameterValue[] parameters = new ParameterValue[2];
parameters[0] = new ParameterValue();
parameters[0].Name = "pID";
parameters[0].Value = "6548747";
parameters[1] = new ParameterValue();
parameters[1].Name = "pClass";
parameters[1].Value = "8";
ExecutionHeader header = new ExecutionHeader();
ExecutionInfo executionInfo = rs.LoadReport(reportPayslip.Name, null);
executionInfo = rs.SetExecutionParameters(parameters, "en-us");
rs.ExecutionHeaderValue = header;
rs.ExecutionHeaderValue.ExecutionID = executionInfo.ExecutionID;
byte[] report = rs.Render("PDF", "<DeviceInfo><Toolbar>False</Toolbar></DeviceInfo>", out extension, out mimeType, out encoding, out warnings, out streamID);
</My code>
Actually, I have several more parameters but I've checked the collection and all the parameters I want are there and have values. Their names match those found in executionInfo.Parameters. But when I run my code I get the following error on Render():
<Error>
"This report requires a default or user-defined value for the report parameter 'pClass'. To run or subscribe to this report, you must provide a parameter value. ---> This report requires a default or user-defined value for the report parameter 'pClass'. To run or subscribe to this report, you must provide a parameter value. ---> This report requires a default or user-defined value for the report parameter 'pClass'. To run or subscribe to this report, you must provide a parameter value."
</Error>
I've double-checked and my parameter array does have the problematic parameter, I passed it a value but when I check executionInfo.Parameters I see that it's the only parameter that hasn't been give a default value after calling SetExecutionParameters(). So my parameters do seem to be passed but one seems to refuse getting a value. If I comment out the SetExecutionParameters() line I get, as expected a similar error but on another parameter (first one in the executionInfo.Parameters collection). So I'm left to beleive that the SetExecutionParameters() has some kind of bug as it works for all but one parameter. I've checked casing, spelling, tried passing phony parameters and from what I've seen I should be getting an error when I pass the parameters if I got something wrong.
Any help would be much appreciated.
Hi,
I am invoking RS web services to render reports, using Apache Axis to generate stub classes from Reporting Service WSDL.
Please let me know if I can integrate Report Viewer control in the jsp where I am writing the report output. Else do I have to create my own custom tags simulating ReportViewer functionality.
My requirement for the parameter is multivalue parameter with a text box. for example when user enters aa15 it need to include product aa15. when the user enters aa15, aa16, zz15 than it needs to include all the three products. the last case is when the user enters AA** than i need to inclued all the products start with AA. when i use default multivalue parameter with data source analytical services than i am getting a drop down box. I dont want that. I need a text box where user can enter the value.
1. In sql we have a like key word to query . for example select * from product where product like "AA%".
what is equavalent mdx query to get such results ?
2.How to impliment the multivalue parameters without using dropdown box?
I am facing sever Refresh issue in PPS Reports. I have Two Dashboard
Dashboard_One and Dashboard_Two
I have few Filters on both the Dashboard .
In Dashboard_One I have 2 Filters
1)Year filter where Year 2012 is my Default value
2)City Filter where "CityOne" is Default Filter Value
If I select Year"2010" in Period Filter and "CityTwo" In City Filter.I see Related reports . Now I navigate to Dashboard_Two to See Other Reports where I have few Other Filter Like "Country" where I select "CountryThree" . When I navigate Back to Dashboard_one I do not see Dashboard with Default value given to them
I still see Filter value Year=2010 and CountryFilter="CountryTwo" in Dashboard Dashboard_One .. where as I should have seen it based on the Default value given to the Filter.. How should I resolve this refresh issue which I am facing in PPS Dashboard. I do not see Default value in the Filter ,It always give the filter value which was selected later when Navigated back.
Hi everyone. I'm new to all this, and having a nightmare accessing my database.
I'm using IIS 5.1 a desktop sql server (MSDE) and Visual Studio.NET Enterprise Architect 2003.
If I try to access a sql database, I get an error "Login failed for user (ServerName)ASPNET". If I try to use an Access database, it tells me that the file is already opened exclusively by another user or that I need permission to view it.
From looking through the forums, it appears to be a security thing (an account for ASPNET?) but how do I fix it. In very plain English...
Hi There,
I've created a couple of search pages which look at sql server. whenever words or values like "?@~:&£^" etc, or words like for, the and so forth, the page the nasrty error page:
Execution of a full-text operation failed. A clause of the query contained only ignored words
Exception Details: System.Data.SqlClient.SqlException: Execution of a full-text operation failed. A clause of the query contained only ignored words.
In short: is there a way I can stop it doing this. It looks rather horrible. I've looked at form validation but cant find anything that seems to fit. I would imagine there is a simple solution, but I haven't been able to find it so far.
Many thanks
Stuart
Dear All
I have three tables
tblStudent
-----------------------------------
|anStudentId|txtFullName|txtMobile|
-----------------------------------
tblClass
------------------------
|anClassID|txtClassCode|
------------------------
tblStudentClass
--------------------------------------------------------------------------------
|id(int)|lngStudent(int)|lngClass(int)|dtStartDate(datetime)|dtEndDate(datetime)|
--------------------------------------------------------------------------------
Table tblStudentClass stores all information about classes that student was in, and will be in.
So there are plenty of records with the same lndStudent field.
What I need is to pull out information about all students from table tblStudent, and the last classes
the were/are in, or will be in if they are future students.
So the needed output should in this form with no duplicates
------------------------------------------------
|anStudentId|txtFullName|txtMobile|txtClassCode|
------------------------------------------------
What I have so far is this query:
SELECT tblStudent.anStudentId,tblStudent.txtFullName,tblStudent.txtMobile,tblClass.txtClassCode
FROM
(tblStudent
INNER JOIN
(
SELECT tblStudentClass.lngStudent, MAX(tblStudentClass.lngClass) AS LastOflngClass
FROM tblStudentClass
GROUP BY tblStudentClass.lngStudent
) AS s3
ON tblStudent.anStudentID = s3.lngStudent)
INNER JOIN tblClass
ON s3.LastOflngClass = tblClass.anClassID
WHERE
tblStudent.txtMobile LIKE '447_________'
ORDER BY tblStudent.txtMobile;
Which is not exactly what I want because it aggregates by greatest classid, not
by latest date, and it is slow on very large dataset. Maybe there are ways to optimize
it (temporary tables,stored procs,views)?
Many thanks in advance
added subreport to main tbl (by putting in detail row). Linked to tbl by 3 params. customer wants subrpt to be side-by-side to main tbl.
Unwanted symptoms: subrpt adds extra space in main tbl if subrpt contains more than 1 row; also, subrpt repeats if main table has more than 1 row. Got all of that? How do I resolve? THANK YOU
I am trying to configure the reporting for TFS using SQL Server. But I get following error when viewing any report:
So I try to manually process the cube to check if it works. I am following this article: [URL] ....
When I click on GetProcessingStatus and invoke it (with last field set as TRUE) I get following error:
How to resolve this issue and be able to see the reports.
I started my Reporting Server and if I use the Internet Explorer as a Administrator everything works fine.But if I don't open it as an admin it calls "rsAccess Denied The user [...] does not have the permission...".Problem is that I want to run it in an SAP Program with an HTML Viewer. how I can get those Permissions ?By the way somehow I'm not able to create or change roles in the Microsoft SQL Server Management Studio.
View 5 Replies View Related
Hello,
i have a small problem with the data sources in the reporting services, maybe you can clarify the situation... I have a MS CRM 3 solution with the reporting services 2005 installed. From an other vendor we have a arcplan solution on the 2000 analysis services. I would like to get access from the 2005 reporting services to the 2000 cube. Is this possible? Ive read that the SSMS cant connect to the 2000 cubes. Can i connect from the reporting services??
thank you
regards
Andreas
Hi all,
Till now all the reports for our application was created in Reporting Services 2005 with SQL Server Database(OLTP) stored procedures.
Most of the reports take 10 to 15 report Parameters and all are multi valued.
Now because of the slow response time,we have decided to use OLAP cubes data source. We have created the OLAP cube in Microsoft Analysis Services.
Can someone tell me how to use the report parameters to query the Cube.
Any links,books for reference would be of great help..
Thanks,
Looking forward to your replies
Here is my nightmare!! : I have 3 database tables. ( START, STOP, TICK )START ( Start_ID, Start_Time )STOP ( Stop_ID, Stop_Time )TICK ( Tick_ID, Tick_Time ) I have 3 records in each tables with the time fields being in datetime format. What I want to accomplish: I want to compare Tick_Time to see if it is between Start_Time and Stop_Time. ie. IF Tick_Time > Start_Time AND Tick_Time < Stop_Time THEN that Tick_Time.colour = Green elseTick_Time.colour = Red Can datetime fields be easily compared like I have shown above? Else can anyone give me an idea of howwwww Thanks and any help is appreciated
View 6 Replies View RelatedI'm hoping that someone here can help me.
I've written an app in C# with ASP.NET in VS. I can connect from my development computer (laptop - XP) to MY server (Win 2003) running MS SQL just fine.
However, when I try to deploy it on the server that it is supposed to run on, I can't connect. I can connect through the network to the SQL server, but I just can't do it with the app. This tells me that my connection string must be wrong... Of course, suggestions would be great...
The SQL server (7.0) is on Windows NT. The app is on a windows 2000 server, and yes, .NET Framework is installed. The Windows NT server is the web server, and the 2000 has been set up as www2. I can see the app from the net, but when I try to log in (requiring connection to the SQL server to verify user and pword), no go.
These are my connection strings, please correct me if I'm wrong...
(username and password are declared)
protected string myConnectionString = "Data Source=192.168.1.98;Initial Catalog=PIM;User ID=" + userName + ";Password=" + password + ";trusted_connection=false;";
protected string myAdminConnectionString = "Data Source=192.168.1.98;Initial Catalog=PIM;User ID=actualUsernameHere;Password=actualPasswordHere;trusted_connection=false;";
I've also tried them without the trusted connection, and with the server name... Same result (or lack thereof).
There's two connection strings, because one is used for the select statements for the public, and the other has all permissions for the back-end. I know it's redundant, but it's just one more safety feature I built in for the paranoid 'client'.
I have also tried to run the SQL server off of the 2000 box, but there's an error with the MMC... Does anyone know how to fix that, if it's a better solution???
Thanks very much for any and all help...
rob
Greetings,I have a couple of stored procedures
that interact to deliver a resultset of data. The first one is
"spStoreList", it receives a few parameters and returns a listing of
stores in a given company or division. The source is below:CREATE PROCEDURE [DBO].[spStoreList](
@p_companyX varchar(20) = NULL,
@p_divisionX char(1) = NULL,
@p_storeX char(4) = NULL
)
AS
CREATE TABLE #Output(
Company char(3),
Division char(1),
Store char(4),
Nickname varchar(40)
)
IF @p_companyX IS NULL
BEGIN
insert into #Output
SELECT CompanyNo,Division,StoreNo,StoreNickname FROM [TBL Store]
ORDER BY CompanyNo,Division,StoreNo
END
IF @p_companyX IS NOT NULL AND @p_divisionX IS NULL
BEGIN
insert into #Output
SELECT CompanyNo,Division,StoreNo,StoreNickname FROM [TBL Store]
WHERE
CompanyNo IN (SELECT DISTINCT CompanyNo FROM [TBL Company] WHERE [TBL Company].[CompanyGroup]=@p_companyX)
ORDER BY CompanyNo,Division,StoreNo
END
IF @p_companyX IS NOT NULL AND @p_divisionX IS NOT NULL AND @p_storeX IS NULL
BEGIN
insert into #Output
SELECT CompanyNo,Division,StoreNo,StoreNickname FROM [TBL Store]
WHERE
CompanyNo IN (SELECT DISTINCT CompanyNo FROM [TBL Company] WHERE [TBL Company].[CompanyGroup]=@p_companyX) AND
Division IN (SELECT DISTINCT DivNo FROM [TBL Division] WHERE DivNo=@p_divisionX AND [tbl Division].[CompanyNo]=[tbl Store].[CompanyNo])
ORDER BY CompanyNo,Division,StoreNo
END
IF @p_companyX IS NOT NULL AND @p_divisionX IS NOT NULL AND @p_storeX IS NOT NULL
BEGIN
insert into #Output
SELECT CompanyNo,Division,StoreNo,StoreNickname FROM [TBL Store]
WHERE
StoreNo = @p_storeX
ORDER BY CompanyNo,Division,StoreNo
END
SELECT * FROM #Output ORDER BY Company,Division,Store
DROP TABLE #Output
GO
The second procedure, "spEmployeeService" uses this
procedure to return a list of how many years the employees have worked
for stores that get returned in the list. THe source is here:CREATE PROCEDURE [dbo].[spEmployeeService](
@p_company char(20) = NULL,
@p_division char(1) = NULL,
@p_store char(4) = NULL
) AS
DECLARE @target int
DECLARE @newLabel varchar(30)
set @target=0
CREATE TABLE #Output(
SortCode int,
Label varchar(30),
Years int
)
CREATE TABLE #Service(
Cnt int
)
CREATE TABLE #Dock(
SortCode int,
Label varchar(30),
Years int
)
create table #Stores(
Company char(3),
Division char(1),
Store char(4),
Nickname char(40)
)
insert into #Stores
exec spStoreList @p_companyX=@p_company,@p_divisionX=@p_division,@p_storeX=@p_store
insert into #Service
select
DateDiff(yyyy,HireDate,GETDATE()) AS YearsOfService
from [tblPyoNet]
where
terminatedate is null and
company <> '' and
[tblPyoNet].StoreNo IN (Select Store FROM #Stores)
insert into #Dock (SortCode,Label,Years)
select
CASE
WHEN Cnt <= 1 THEN 1
WHEN Cnt = 2 THEN 2
WHEN Cnt = 3 THEN 3
WHEN Cnt = 4 THEN 4
WHEN Cnt = 5 THEN 5
WHEN Cnt = 6 THEN 6
WHEN Cnt = 7 THEN 7
WHEN Cnt = 8 THEN 8
WHEN Cnt = 9 THEN 9
WHEN Cnt = 10 THEN 10
WHEN Cnt <= 15 AND Cnt >10 THEN 11
WHEN Cnt >15 THEN 12
END,
CASE
WHEN Cnt <= 1 THEN '1 Year'
WHEN Cnt = 2 THEN '2 Years'
WHEN Cnt = 3 THEN '3 Years'
WHEN Cnt = 4 THEN '4 Years'
WHEN Cnt = 5 THEN '5 Years'
WHEN Cnt = 6 THEN '6 Years'
WHEN Cnt = 7 THEN '7 Years'
WHEN Cnt = 8 THEN '8 Years'
WHEN Cnt = 9 THEN '9 Years'
WHEN Cnt = 10 THEN '10 Years'
WHEN Cnt <= 15 AND Cnt >10 THEN '11-15 Years'
WHEN Cnt >15 THEN '> 15 Years'
END,
1
FROM #Service
insert into #Output
select SortCode,Label,SUM(Years) FROM #Dock GROUP BY Label,SortCode
while @target<12
begin
set @target=@target+1
if (select count(*) from #Output where SortCode=@target) = 0
begin
if @target = 11
begin
set @newLabel = '11-15 Years'
end
if @target = 12
begin
set @newLabel = '> 15 Years'
end
else
begin
set @newLabel = STR(@target)+' Years'
end
insert into #Output (SortCode,Label,Years) VALUES(@target,@newLabel,0)
end
end
SELECT * FROM #Output ORDER BY SortCode
DROP TABLE #Stores
DROP TABLE #Dock
DROP TABLE #Service
DROP TABLE #Output
GO
I can get these two procedures to work in Query Analyzer,
but if I try to pull pack a datatable in ASP.NET I get the following
error:ERROR:Invalid column name 'Store'. Invalid column name
'Division'. Invalid column name 'Company'. Insert Error: Column name or
number of supplied values does not match table definition. Insert
Error: Column name or number of supplied values does not match table
definition. Insert Error: Column name or number of supplied values does
not match table definition. Insert Error: Column name or number of
supplied values does not match table definition.
Any ideas why this would work in Query
Analyzer and not in .NET. By the way, I send the following SQL to an
SQLOLEDB driver, "exec spEmployeeService 'EFS','B'" to be returned in a
datatable.Help is appreciated.
I converted an Access Database to SQL Express. The dates were converted to datetime
I'm using VWD 2005
Here is the source of my date and the query.
sqlDate = (DateTime.Now.AddDays(-7))
sqlTxt = "SELECT Service_Orders.SStore_Assigned_Number, Store_Info.Store_Other, Service_Orders.PO_Number, Service_Orders.SWorkType, Service_Orders.Service_Order_Number, Service_Orders.SDate_Entered, Service_Orders.SContact, Service_Orders.SClosed FROM Service_Orders INNER JOIN Store_Info ON Service_Orders.Store_ID = Store_Info.Store_ID WHERE (Service_Orders.SDate_Entered >= CONVERT(DATETIME, '" + sqlDate + "', 101)) ORDER BY Service_Orders.SDate_Entered DESC"
This retrurns 0 records.
sqlDate = 11/28/2005 12:23:27 AM from the function above.
The query will return records with :
sqlDate = "2005-11-01 21:56:20"
I tried changing the CONVERT(DATETIME, '" + sqlDate + "', 1XX from 100 to 120 with no luck
I know this must be an easy fix, but it is beyond me.
I need to know how to
1. convert my date to the dateformat from "11/28/2005 12:23:27 AM" to "2005-11-01 21:56:20"
or
2. find out how to use the CONVERT(DATETIME, '" + sqlDate + "', 1XX properly
Thanks for any help in advance!
Bill