In Reporting Services, is it possible to display a field in Engineering Notation or Scientific notation? I guess can do it by adding a format code, can somebody tell me the format for those notations.
Thanks
Does anyone know of a way to determine the code of a stored procedure that was built with encryption? I inherited a db with many of these and we are not sure which version of the source code exists on the server. Alternatively, can I compile a new SP and compare it to the old one in any way other than testing lots of data?
thanks in advance.
Two Question
1. Is there a stored procedure, or a way to reverse engineer a database? I have a lot of legacy dbs that I have inherited that I need to create a recovery system for. Part of this is creating the scripts to build all of the tables and I would like to automate it.
2. How can I output the results of a stored procedure to a text file? When I run sp_configure I would like that information to go to a text file.
Thanks in advance
Ken Earley
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
Hi,I'd like to reverse engineer the schema definitions in the SQL Serveron a daily basis and store them in a version control system.Could anyone please let know if there are any tools to reverse engineerthe entire schema definitions in the SQL server. I'd like somethinglike the perl script 'dbschema.pl' for the Sybase ASE.Regards,Dellit.
View 3 Replies View RelatedI'm not sure where to post this - but, I'm a DBA trying to do some modeling for a client developing an Engineering applciation.
There is a lot of interaction at the application layer between external systems and the database - I'm trying to find some ideas or templates of how others designing for a similar functionality did this.
THere are real-time displays for engineering equipment where the database must interact with different states of the equipment. The user enters search criteria for data stored in the database, and results and real-time attributes are displayed.
THis seems to be similar to a manufacturing model, but I'm struggling. I'm used to doing models for OLTP or business reporting databases. Part of the problem may be that there doesn't seem to be a good way to translate relational concepts to engineers. There are a lot of requests for bit fields.
Can anyone reccomend a reference I might look at to get some ideas on how to mesh the modeling of external processes with the database? A simple flow model doesn't seem to work (either technically or as a communication tool - I'm not sure which)
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'm not a professional dba or dbd, but I'm proficient in the basics ofdatabase design and sql. I want to create a database of mathdefinitions, and I'm wondering how one would go about creating adatabase that contains mathematical notation (and I'm not just talkingabout basic symbols where I could get away with ascii code). I needto be able to insert a wide variety of mathematical expressions, fromfractions to integrals, into fields (just like you can enter in-linemath symbols in MS Word using equation editor). I have no clue how togo about this. Is it a matter of developing certain programmingskills/languages? Would such a capabliltiy be proprietary (dbms-specific)? Is it possible at all? Any help would be appreciated.Thank you.
View 3 Replies View RelatedI've got some values stored in nvachar(255) field stored by mistake as scientific notation (eg 7.5013e+006 instead of 7501301) and I need to convert and update the field with normal entry, not scientific notation. Is there a way to do that?
View 6 Replies View RelatedI 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.
HiWe've got some numbers stored as Reals which are returning values inscientific notation that we need rounded down to 3 digits to the rightof the decimal.ie 8.7499999E-2 needs to return 8.75.Round, cast, convert, formatnumber in the dts package all fail.Help!Thanks Moe
View 2 Replies View Related
I'm in Visual Studios working on a data flow. I want to add some notes. I open a text box (by typing on the screen).
Why can I only get ONE line of text???? The return key doesn't work.
I was looking to modify how the INSERT happens with regards to replication only to find my solution in the proc itself. When I edit the proc this is what I am displayed in SQL QA or EM:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER procedure "sp_MSins_dboMEETING" @c1 int,@c2 int,@c3 int,@c4 varchar(250),@c5 datetime,@c6 datetime,@c7 bit,@c8 char(1),@c9 datetime,@c10 bit,@c11 bit,@c12 bit,@c13 datetime,@c14 smallint,@c15 datetime,@c16 smallint,@c17 binary(8),@c18 bit,@c19 bit,@c20 bit,@c21 varchar(1000)
AS
BEGIN
insert into "dbo"."MEETING"(
"MEETING_ID", "MEETING_TYPE_ID", "MEETING_STATUS_ID", "TITLE", "START_DATE", "END_DATE", "PUBLISH_IND", "GROUP_IND", "PUBLISH_DATE", "MY_ADVISORS", "SUBMITTED_IND", "ACTIVE_IND", "CREATE_DATE", "CREATED_BY", "LAST_UPDATE_DATE", "LAST_UPDATED_BY", "DATE_INDEXED", "ON_DEMAND_IND", "NOT_REPORTED_IND", "MAJOR_PROJECT_IND", "MAJOR_PROJECT_COMMENT"
)
values (
@c1, @c2, @c3, @c4, @c5, @c6, @c7, @c8, @c9, @c10, @c11, @c12, @c13, @c14, @c15, @c16, @c17, @c18, @c19, @c20, @c21
)
END
GO
create procedure "sp_MSins_dboMEETING";2 @c1 int,@c2 int,@c3 int,@c4 varchar(250),@c5 datetime,@c6 datetime,@c7 bit,@c8 char(1),@c9 datetime,@c10 bit,@c11 bit,@c12 bit,@c13 datetime,@c14 smallint,@c15 datetime,@c16 smallint,@c17 binary(8),@c18 bit,@c19 bit,@c20 bit,@c21 varchar(1000)
as
if exists ( select * from "dbo"."MEETING"
where "MEETING_ID" = @c1
)
begin
update "dbo"."MEETING" set "MEETING_TYPE_ID" = @c2,"MEETING_STATUS_ID" = @c3,"TITLE" = @c4,"START_DATE" = @c5,"END_DATE" = @c6,"PUBLISH_IND" = @c7,"GROUP_IND" = @c8,"PUBLISH_DATE" = @c9,"MY_ADVISORS" = @c10,"SUBMITTED_IND" = @c11,"ACTIVE_IND" = @c12,"CREATE_DATE" = @c13,"CREATED_BY" = @c14,"LAST_UPDATE_DATE" = @c15,"LAST_UPDATED_BY" = @c16,"DATE_INDEXED" = @c17,"ON_DEMAND_IND" = @c18,"NOT_REPORTED_IND" = @c19,"MAJOR_PROJECT_IND" = @c20,"MAJOR_PROJECT_COMMENT" = @c21
where "MEETING_ID" = @c1
end
else
begin
insert into "dbo"."MEETING" ( "MEETING_ID","MEETING_TYPE_ID","MEETING_STATUS_ID","TITLE","START_DATE","END_DATE","PUBLISH_IND","GROUP_IND","PUBLISH_DATE","MY_ADVISORS","SUBMITTED_IND","ACTIVE_IND","CREATE_DATE","CREATED_BY","LAST_UPDATE_DATE","LAST_UPDATED_BY","DATE_INDEXED","ON_DEMAND_IND","NOT_REPORTED_IND","MAJOR_PROJECT_IND","MAJOR_PROJECT_COMMENT" ) values ( @c1,@c2,@c3,@c4,@c5,@c6,@c7,@c8,@c9,@c10,@c11,@c12 ,@c13,@c14,@c15,@c16,@c17,@c18,@c19,@c20,@c21 )
end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Is the second version listed at the bottom like a comment or can it actually get called? I am going to script out all of these procs and save them, and then remove the first version (listed at the top) and the use the second version since it does what I need. I just thought it was interesting to see two stored procedures in a single definition, never seen the "PROC_NAME";2 notation, have you? If so please tell me what it does, is it just a way to create a second version of the procedure in a comment type fashion or is it used another way?
We sometimes have small values stored in a column with datatype of float like 0.000644470739403048 which is being converted to -5.8E-05. Perhaps that is OK to be stored in the database however I need the value in decimal format to use. (I'm using longitude values in google maps).
is there anything I can do at the database level. I was looking at the properties which is 53 numeric precision and 8 length.
I'm trying to find a way to format a FLOAT variable into a varchar inSQL Server 2000 but using CAST/CONVERT I can only get scientificnotation i.e. 1e+006 instead of 1000000 which isn't really what Iwanted.Preferably the varchar would display the number to 2 decimal placesbut I'd settle for integers only as this conversion isn't businesscritical and is a nice to have for background information.Casting to MONEY or NUMERIC before converting to a varchar works finefor most cases but of course runs the risk of arithmetic overflow ifthe FLOAT value is too precise for MONEY/NUMERIC to handle. If anyoneknows of an easy way to test whether overflow will occur and thereforeto know not to convert it then that would be an option.I appreciate SQL Server isn't great at formatting and it would be fareasier in the client code but code this is being performed as adescription of a very simple calculation in a trigger, all stored tothe database on the server side so there's no opportunity for clientintervention.Example code:declare @testFloat floatselect @testFloat = 1000000.12select convert(varchar(100),@testFloat) -- gives 1e+006select cast(@testFloat as varchar(100)) -- gives 1e+006select convert(varchar(100),cast(@testFloat as money)) -- gives1000000.12select @testFloat = 12345678905345633453453624453453524.123select convert(varchar(100),cast(@testFloat as money)) -- givesarithmetic overflow errorselect convert(varchar(100),cast(@testFloat as numeric)) -- givesarithmetic overflow errorAny suggestions welcome...CheersDave
View 3 Replies View Related
Hi All,
Am I missing something or is there something odd with float data types. i know that float is not the most precise definition but i came across something really odd today.
first let me define the scenario.
this is sql server 2005, standard edition build 3042.
I have a table defined as
CREATE TABLE [dbo].[ASSET](
[Property_Num] [numeric](10, 0) NOT NULL,
[Accrual_Factor_Val] [float] NULL
)
the accrual_factor_val was updated to a value of 0.00005 then the web service failed because the proc returned 5E-05!
i opened the table, and discovered this is the stored value. is this correct?
thoughts and ideas?
thanks for your input!
SELECT membername, outputval
case when choice = 0 then outputval else null end as outputval
from MyDatabase
group by membername, outputval
how to format outputval:
if outputval < 40000
format outputval as:
5 - 5.78 - 6.9 - 6,778 - 4,567.8 - 12,456.78 - etc.
if outputval >= 40000
format it as a scientific.
Hi ,
I have one problem regarding format of mixed data columns in excel. I am using this query to retieve data from my excel spreadsheet
Select * from OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;IMEX=1;IHDR=yes;Database=E: est.xls;HDR=YES',
'SELECT * FROM [original$]')
to retrieve data from excel sheet.
I am able to retreive all data (numaric and text) , but for few cells I am getting wrong values.
like this
15106749310-> 1.51067e+010
70400672657-> 7.04007e+010
48110753143-> 4.81108e+010
registry setting are €œtext and 0€³. I am also using IMEX=1 in connection string.
I have numeric and text data both in same column,
eg:
70400503549
70400672657
KF1080420908
KF1260370908
KF1260880908
KF1260960908
Could you please help me to get the correct value from excel .
thanks in advance ,
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
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.
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.