Moved Report To Another Solution, Now Parm Won't Work
Apr 25, 2008
Hi all,
I moved a report to a different solution using the "Add Existing Item" function. Verified that all the data sources were properly connected and that my queries to provide parameter lists were running ok. When I try to run the main data source (a stored proc) from the data tab, I get this message:
"..An error occurred while executing the query.
Procedure or function 'ReportSP' expects parameter '@Item, which was not supplied.
(Microsoft SQL Server, Error: 201)"
This SP runs fine from the old solution and in Management Studio, so there must be something I need to do in the new report. Can anyone point me in the right direction?
I checked the report parameters and it's there. When I try to preview the report, it lets me pick all the parameters, including the one it's griping about, then it throws the message.
I have been working on an application which require as much percision as we can get. We have defined all numeric values as (38,16) however when ever we divide a number by the result of a built in function it truncate the percision to 5 decimal place this cause us to have a small but enoying loss in the numbers being caculated we need a minimuim of 8 decimal places or better is there a work around or solution for this problem in sql server 2005. The more percision the better.
example: select tna/(select sum(tna) from taum) from taum where fund_id = 2345
the result is always 5 decimal places even though fund_id tna = 2569698.23 and sum(tna) =98745612325879.36 which should equal .00000002602341683313994 instead the result = .00000
I've seen some similar posts to this one, however none with an answer and I'm wondering if anyone has actually figured out what causes this issue. I'm running SQL Server 2005 Reporting Services.
I'm attempting to implement a folder structure for the report server, therefore am creating individual solutions in Visual Studio for each folder. At the top level i.e http://servername/reportserver/ I am able to deploy reports no problem.
However creating a folder at the next level in the tree and then deploying to that level such as http://servername/reportserver/ManagementReports causes a Reporting Services Login prompt to appear. No matter what user credentials I enter they are not accepted. I have full administrator rights the server, the site and the folders in question.
Has anyone else experienced this? Does anyone have any suggestions?
How can I copy a report and all its supporing queries and make a duplicate so that report1 and report2 are duplicates.
Report 2 will be little different than report1 but the basic queries and parameters will all the same. I can then edit the report layout to what the new report will look like. I do not want to have to cut/paste one object at a time.
I have been asked to create an archive database which we can use for reporting purpuse . The idea is to have a seperate data base which is an exact copy of the current production database , Once we setup the archive DB . we will remove all 40 days old data from Production database and form that day on should have archive DB with full data and production DB with 40 days old. I thought go going log shipping or back up restor or snapshortor something but the problem is with all these technology the both data base will come in sync . If i delete data from production that will reflect in arvhive also .
I thought of keeping the 40 days only in production and the rest in archive . But they want to wiew the both data together in one aspx page . so i had think of some joint view . But when the archive data gets grow this would be the main perfomence bottlentck
Can any one help me to select a good solution for this, thanks in advance
When you create a Reporting Services project it by default creates two folders: Shared Data Sources & Reports. When I right click on the Reports folder in solution explorer in Visual Studio 2005 there is no option to add subfolders, I am simply left with handful of options adding report files. Is there a way to add subfolders to the Reports folder? I am going to be migrating over 40-50+ reports and it would be helpful to organize them into their own folders.
CREATE PROCEDURE GetUserID ( @Email nvarchar(100), @Password nvarchar(50), @UserID int OUTPUT ) AS SET @UserID = (SELECT fldResourceID FROM tblResources WHERE fldEmail = @Email AND fldPassword = @Password)
GO
The Code Behind to call it.
Dim intUserID As Integer = 0 Dim strConnectionString As String = dbConnect() Dim objConnection As SqlConnection = New SqlConnection(strConnectionString) objConnection.Open()
Dim objCmd As SqlCommand = New SqlCommand("EXEC GetUserID '" & strEmail & "','" & strPassword & "', " & intUserID, objConnection)
intUserID = objCmd.ExecuteScalar()
objCmd = Nothing objConnection = Nothing
Return intUserID
However nothing is returned from the objCmd.ExecuteScalar line. Can anyone help me out here. All I need it to return is a single value. Thanks
does anyone have a working example of calling sp_executesql passing a datetime parameter? I can't get this to work. I even tried passing the datetime as a string but keep getting an error on converting a varchar to a datetime. I am calling with varchar, int and datetime parms, if I take out the datetime parm everything works.
I have a simple stored proc with one input parm that gets first and last name based on an ID#. There will be a connection established to this database and sproc from a VB 2005 front end. Do I need output parms for the name fields?
I have a simple stored proc with one input parm that gets first and last name based on an ID#. There will be a connection established to this database and sproc from a VB 2005 front end. Do I need output parms for the name fields?
It appears to work fine on my end however on my clients end, whenever they try to print a report that displays perfectly within the report viewer to their printer by selecting the printer button in the report it never ends up printing.., however if they go into printer setup and change the page range to print from all pages to just maybe one page and then select the print button it seems to print fine..
The printer seems to receive the information as the light flashes but no page kick out when all pages is selected to print..
I am writing a stored procedure: CREATE PROCEDURE rasp_FillDescrColumnNames @TableNameD varchar(50), @TableNameU varchar(50), @TableUID int -- -- AS ......
Insert into @TableNameD(ColumnName) Select #TempColumnNames.Name From #TempColumnNames
----------------------------- I get "incorrect syntax near '@TableName'". (The input table does have a column called 'ColumnName'). How do you parametized an 'Insert Into' sql statement? I have tried a number of ways to no avail. Thanks, Judith
I have a report model based on Oracle database. My model contains 3 tables and one of them has about 20 millions records. When I create a report using Report Builder, it just a very simple report - 2 columns: name and total sales, the performance is really slow. I am wondering how the report builder + report model works. I did apply a filter and expects to see around 40 rows ( I knew it because I ran a SQL query).
Is there anyone could help me? Perhaps SSRS Team? I am trying to promote Report Builder as an ad-hoc tool in my company.
VB.NET 2005 Express and SQL Server 2005 Express - NOT saving updates to DB - SOLUTION!
-----------------------------------
The following article is bogus and confusing:
How to: Manage Local Data Files - Setting 'Copy to Output Directory' to 'Do not copy' http://msdn2.microsoft.com/en-us/library/ms246989.aspx
You must manually copy the database file to the output directory AFTER setting 'Copy to Output Directory' to 'Do not copy'.
Do not copy
The file is never copied or overwritten by the project system. Because your application creates a dynamic connection string that points to the database file in the output directory, this setting only works for local database files when you manually copy the file yourself.
You must manually copy the database file to the output directory AFTER setting 'Copy to Output Directory' to 'Do not copy'.
-----------------------------------
The above article is bogus and confusing.
This is rediculous!
This is the most vague and convoluted bunch of nonsince I've ever come accross!
Getting caught out on this issue for the 10th time! And not being able to find an exact step-by-step solution.
--------------------------
I've tried it and it doesn't work for me.
Please don't try what the article eludes to as I'm still sorting out exactly what is supposed to be happening.
If you have a step-by-step procedure that can be reproduced this properly please PM me.
I would like to test its validity then update this exact post as a solution rather than just another dicussion thread.
Many thanks.
This is the exact procedure I have come up with:
NOTE 1: DO NOT allow VB.net to copy the database into its folders/directorys.
NOTE 2: DO NOT hand copy the database to a folder/directory in your project.
Yes, I know its hard not to do it because you want your project nice and tidy. I just simply could NOT get it to work. You should NOT have myData.mdf listed in the Solution Explorer. Ever.
Create a folder for your data following NOTE 2.
Copy your data to that folder. * mine was C:mydatamyData.mdf
Create a NEW project.
Remove any Data Connections. ( no matter what)
Save it.
Data | View Data Sources
Add New Data Source
select NEW CONNECTION ( No Matter what, do it!
Select the database. * again mine was C:mydatamyData.mdf
Answer NO to the question: Would you like to copy the file to your project and modify the connection? - NO ( no matter what - ANSWER NO ! - Absolutely NO ) Then select the tables you want in the DataSet. and Finish.
To Test ----------
From the Solution Explorer | click the table name drop down arrow | select details Now Drag the table name onto the form.
The form is then populated with a Navigation control and matching Labels with corresponding Textboxes for each field in the table.
Save it.
1) Run the app.
Add one database record to the database by pressing the Add(+) icon
Just add some quick junk data that you don't mind getting lost if it doesn't save.
YOU MUST CLICK THE SAVE ICON to save the data you just entered.
Now exit the application.
2) Run the app again.
And verify there is one record already there.
Now add a second database record to the database by pressing the Add (+) icon.
NOW add some quick junk data that you WILL intentionally loose.
*** DO NOT *** press the save icon.
Just Exit the app.
3) Again, Run the app.
Verify that the first record is still there.
Verify that the Second record is NOT there. Its NOT there because you didn't save the data before exiting the app.
Proving that YOU MUST CLICK THE SAVE ICON to save the data you just entered.
Also proving you must add your own code to catch the changes and ask the user to save the data before exitiing or moving to another record.
As a side note, since vb.net uses detached datasets, (a copy/snapshot of the dataset in memory and NOT directly linked to the database) the dataset will reflect all changes made when moving around the detached datasets. YOU MUT REMEMBER TO SUBMIT YOUR CHANGES TO THE DATABASE TO SAVE THEM. Otherwise, they will simply be discarded without notice.
Whewh!
I hope this saves me some time the next time I want to start a new database project.
Oh, and uh, for anyone else reading this post.
Thanks, Barry G. Sumpter
Currently working with: Visual Basic 2005 Express SQL Server 2005 Express
Developing Windows Forms with 101 Samples for Visual Basic 2005 using the DataGridView thru code and every development wizard I can find within vb.net unless otherwise individually stated within a thread.
Hi! I hope anyone can help me out. Our Transaction Log file of our PUBLISHED Database has unfortunately been moved to a differant folder. After that a new one has been created. After movin back the old one, the DB isn't accessible from the clients anymore. (ODBC error message) How can I get that old log file back to work??? Is it possible to use sp_detach and sp_attach on a replicated database?
Please help me with that urgent problem! Thanx in advance Gert
what happens if the physical location of a box(which had sql server 2000 on it) is chaned. what happens to the replication and distributed queries. Thanks.
I just installed a fresh lab machine with WSS 3.0 and SQL 2005. I registered the RS web parts.
In the home page I added the Reports Explorer and the Report viewer web parts. I set up the Reports Explorer and the list of reports are showed.
When I connect the Report Viewer Web part to get report from the Report Explorer web part I get the maintenance WSS page. I have to delete the Report Viewer web part to have the page working again.
If I use only the Reports Viewer with a report (not connected to Reports Explorer) is working just fine.
Any ideas what could be the problem? Any hints on how to investigate this is also much appreciated.
I've made a MDX calculation in SSAS and it works fine, but when i look at my report (which is based on my cube), the same calculation gives "infinity".
I have a SQL Server 2000 database and want to now split up the tablesonto seperate file groups as well as some indices.How do you breakup an existing table to move it from one filegroup(Primary) to the new filegroup ?Thanks.Craig
database has been recently upgraded from ms sql 2000 to ms sql 2005.are there anything I need to be aware after upgrading to ms sql 2005?for my experience, i got an error if i use column alias in ORDER BYclause which was fine on ms sql 200.thanks
I haven't gotten a response yet, so I moved this from another group. Ihave been working on this for 2 days so if anyone has any ideas, Iwould be grateful.I have a 3rd party program that creates and populates tables in mySQLServer 2005 database.The program fails on the inserts on "tblB" because the field itcreates is too small for the data that it is trying to put in it(stupid).I wrote a DDL trigger that attempts to alter the table as soon as itcreated, allowing all the data to be loaded.However, something about this trigger causes a prior table "tblA" tofail.Here is the error message that I get on inserting into tblA with thetrigger for tblB in place:Execution of this SQL statement failed: Create table tblA(STATUSCHAR(1) NOT NULL DEFAULT'', SCHOOLNUM[Microsoft][ODBC SQL Server Driver][SQL Server]SELECT failed becausethe following SET options have incorrect settings:'CONCAT_NULL_YIELDS_NULL, ANSI_WARNINGS, ANSI_PADDING'. Verify thatSET options are correct for use with indexed views and/or indexes o(yes, it truncates the error message)My trigger is basically:USE [IGPLINK]GO/****** Object: DdlTrigger [NO_SOUP_FOR_YOU] Script Date:03/24/2008 16:04:42 ******/SET ARITHABORT ONGOSET CONCAT_NULL_YIELDS_NULL ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_NULLS ONGOSET ANSI_PADDING ONGOSET ANSI_WARNINGS ONGOSET NUMERIC_ROUNDABORT OFFGOCREATE TRIGGER [NO_SOUP_FOR_YOU] ON DATABASEFOR CREATE_TABLEASSET NOCOUNT ONDECLARE @xmlEventData XML,@tableName VARCHAR(50)SET @xmlEventData = eventdata()SET @tableName = CONVERT(VARCHAR(25), @xmlEventData.query('data(/EVENT_INSTANCE/ObjectName)'))IF @tableName ='tblB'BEGINALTER TABLE dbo.tblB ALTER COLUMN STULINK Numeric(16,0)ENDHowever, when I have enterprise manager script my trigger, it looksaltered. I think these ON/OFF settings at the end are screwing thingsup. Any suggestions?USE [IGPLINK]GO/****** Object: DdlTrigger [NO_SOUP_FOR_YOU] Script Date:03/25/2008 11:10:05 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TRIGGER [NO_SOUP_FOR_YOU] ON DATABASEFOR CREATE_TABLEASSET NOCOUNT ONDECLARE @xmlEventData XML,@tableName VARCHAR(50)SET @xmlEventData = eventdata()SET @tableName = CONVERT(VARCHAR(25), @xmlEventData.query('data(/EVENT_INSTANCE/ObjectName)'))IF @tableName ='tblB'BEGINALTER TABLE dbo.tblB ALTER COLUMN STULINK Numeric(16,0)ENDGOSET ANSI_NULLS OFFGOSET QUOTED_IDENTIFIER OFFGOENABLE TRIGGER [NO_SOUP_FOR_YOU] ON DATABASE
Primary platform is XP as client side and 2003 as server.
When I press F5 in order to see my report from IE appears this error:
An error has occurred during report processing. (rsProcessingAborted) Cannot create a connection to data source 'SQL1.BDADMIN'. (rsErrorOpeningConnection) For more information about this error navigate to the report server on the local server machine, or enable remote errors
However, preview button works properly and data are showed. Let me know where am I failing.
Hi, I'm using one report tool by 'AriaCom' and have got such problems with FREE SQL in that tool. Pls advise!
Message: SQLExecDirect>[ODBC 195][37000][Microsoft][ODBC SQL Server Driver][SQL Server]'instr' is not a recognized built-in function name. Message: SQLExecDirect>[ODBC 195][37000][Microsoft][ODBC SQL Server Driver][SQL Server]'mid' is not a recognized built-in function name.
I've recently moved an asp.net website from my PC to a network share because another tech it going to be working on it. I finally got the correct permissions on the network share and the correct .NET Framework settings on my PC to be able to run the app. Now I can't access the SQL server which is on a different server. Getting the following error: Request for the permission of type 'System.Data.SqlClient.SqlClientPermission, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed. How to I setup access to my SQL server for the app from any given PC on my LAN?
I have a table named [Customers] which has about 100 fields and tens of thousands of records. These fields have been created over a couple years, but now they are so disorganized its getting crazy. I want to move the fields using click and drag on the record selectors the GUI in design view of SQL Server Management Studio, but I am worried about possible dangers. Is there anything I should be worried about by doing this before saving the changes? I don't want to make a view because the front end program is pretty big and would require way too much reprogramming to point all its parts to a new view on a table used so much already.
The question arises because when I try, it wants to also save all tables that are FKs to this table, which are many(and have cascade updates on). So I decided to abort and get info I should know first. Although the data shouldn't change when moving the field, I don't want to just assume that nothing will go wrong.
Also, the system is live, and it isn't easy to shut down to do this change. Is it essential that the system be offline, or at least has no users connected to the tables?
1.) Can an aspnetdb.mdf database be configured and setup on one server and then be moved to a production server or is there something machine specific that keeps this from being possible? 2.) Is putting this file in the app_data folder something that is used only for SQL 2005 or SQL express? I had to set up a connection string in my SQL 2000 installation to get the connection to work. Thanks for any input! Colelaus
Hi there, I am not sure if this is the right place to seek help for the problem i have but as i don't see any other link to discuss the situation i have i am just posting it here. To explain a little bit about the project i am on..... Originally the appication(developed in asp.net and vb) i am assigned to was developed by a different team and most of the databases were on SQL server 6.5 servers. So they have used oledb connections where ever they had to connect SQL server 6.5 data sources. Lately the client has moved all of the sql server 6.5 data bases to SQL server 2000 and now the application is kinda not working as it is suppose to as it was before. So i am hired to fix the problem. So as a first step what i am doing is finding out all of the .net oledb proivder part of the database connections code to SQL server 6.5 data sources and changing them to .NET provider for SQL server. Say for example in the below GetConnection function i am commenting out OledbConnection and defining a SqlConnection instead. Private Function GetConnection() As IDbConnection 'Dim conn As IDbConnection = New System.Data.OleDb.OleDbConnection Dim conn As IDbConnection = New System.Data.SqlClient.SqlConnection conn.ConnectionString = Helpers.DBHelpers.GetConnectionString(Helpers.DBHelpers.COMMON) Return conn End Function and also commenting out OledbDataAdapter line of code and defining a SqlDataAdapter instead. You can see it below 'Dim adapter As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter(CType(cmd, OleDb.OleDbCommand)) Dim adapter As SqlClient.SqlDataAdapter = New SqlClient.SqlDataAdapter(CType(cmd, SqlClient.SqlCommand)) And connection strings are defined in web.config file and also i am changing those as well . See below.
<!--<add key="Common" value="User ID=Test;pwd=*****;Data Source=ESMALLDB2K;Initial Catalog=cj_common;Auto Translate=True;Persist Security Info=False;Provider="SQLOLEDB.1";" />--> <add key="Common" value="User ID=Test;pwd=*****;Data Source=ESMALLDB2K;Initial Catalog=cj_common;" /> So i hope i am in the right direction as far as the first step. But please throw in any kind of suggestions on this.
One more thing. I have a search screen and T-sql query thats built for this purpose searches 4,5 tables and brings the data back. When i make a search from the web browser it doesn't return the data for the first couple of times but it brings the data 3rd time but even its taking as long as 60 seconds to bring the data back. when i close the browser and debug and paste the SQL query in the query analyzer it returns the data in the query analyzer and when i complete the remaining part of debugging and bind the data to the gird i also see the data on the broswer for the first time itself. Question : Why i don't get the data for the first time when i search it from the front(web browser)? But like i said the executing time to the query in the query analyzer itself takes considerably long time( i would say around 60 seconds just to return 3,4 records)) in the query analyzer. When i talked to the database guys why sql queries are a little slow they say they have a lot of datat out there around 180 thousand records in it and thats why its taking that much time to search agains all of the rows. Question - Do you think it could be some thing to do with dropping and recreating the indexes should solve our problem? May be its some thing to do with the indexes but i am sure they have not dropped out the indexes of all of the table objects and recreated yet after the databases are moved to SQL servere 2000. Hope i am able to explaing what i am looking for and what i am doing. Please help me understand in solving these problems. Thanks in advance -D
I am totally confused, please help me. I am new to this
I am trying to split one DB (A) into two databases original (A) and new-Blank(B) by moving some tables from DB (A) to the new DB (B) however some of the tables has FK and Stored procedures referencing other tables that need to stay in DB (B), The questions are
1. after scritping these tables while they are in DB (A), and runing the script in DB (B) to re-create them, do I delete these table from DB (A), and the FK that references them.
2. What shall I do with the stored procedures. Turn them into trigers or else if turn them into trigers, in which DB should the triggers run (DB A of DB B),if these are becoming triggers do I delete the stored procedures from DB (A)
works fine in designer but when i load the report services I get the following error anybody know what to do there is one subreport with this report maybe the passing value but what could be wrong ????
Item has already been added. Key in dictionary: '9' Key being added: '9'
