I have a customer who is running a script that generates a custom report. IT is not populating as it should and is returning zeros for everything.
I have tested the the script in the office and it is populating as intended. I have run a debug on the script and it is executing the correct SQL commands. The debug results for the customer and for the one tested in the office are identical. For some reason, the script is not writing to file but is looking at the correct data. I suspect that it is an environment issue most likely on the SQL level.
Could this be an issue with character set? How can I check their character set and language preferences? I understand they are set during installation.
They are using the same collation as us.
What else can I check as I am running low on ideas.
I advised the client to create a new DB and restore over the top. The script was then tested and it was found to be working fine. As it was given an inappropriate name (ie test), I advised to create a new DB with a production name and restore over the top again. We have since returned back to where we started as the report is generating only zeros.
For some reason a stored procedure which I have created is incorrectlysaving the date to the table. It seems the day and month are beingswapped around e.g. a date which should be the 12th April (12/04/2005)is saving as the 4th December (04/12/2005).The parameter used in the stored procedure comes from a VB6 app, Iamended this so the format was "yyyymmdd hh:mm:ss". The full line in VBbeing,Parameters.Append .CreateParameter("date_of_call", adChar, , 17,Format(firstCallDateTime, "yyyymmdd hh:mm:ss"))When I run my VB app it works fine, the syntax in the stored procedureis,CREATE PROCEDURE dbo.spUpdValues@data_id int,@date_of_call datetimeasupdate dataSET date_of_call = CONVERT(char, @date_of_call, 101)where data_id=@data_idIs it because the convert format is using an american date format ? Ican't see why as I can't reproduce this error using my own PC as thedate saves correctly, I can also confirm it's not happening to everybodywho uses the app. If it is happening for specifc users then what couldbe the cause. I've checked Regional Settings and all seems fine there.Any ideas on what could be doing this as I'm struggling to investigateany further.To debug I ran the stored procedure direct, manually inputting thevariable - again no problem. Also, the following SQL statment shows noproblem...declare @date_of_call datetimeset @date_of_call = '20041101 08:30:00'select CONVERT(char, @date_of_call, 101)select CONVERT(char, @date_of_call, 106)------------------------------11/01/2004(1 row(s) affected)------------------------------01 Nov 2004(1 row(s) affected)Any help would be much appreciated.*** Sent via Developersdex http://www.developersdex.com ***
I have a rather complicated report with lots and lots of textbox and line controls. When I preview the report on the Report Server the layout is all kinds of skewed and all kinds of stuff is out of place. But when I export the report to PDF or TIFF, the output reverts to it's proper form. Why is it doing this? Is there anything that I can do to not make it so ugly when previewing?
Hi. I have a report which has several datasources which require a table to be populated before they read from it. i.e. The first thing that needs to happen whenever the report is run, is a call to a stored procedure which populates the table the report datasources are based off of. The SP takes several minutes to complete and MUST complete before any of the datasources fetch their data.
How can this be achieved?
I can not find anything in the Visual Studio Report Designer which allows to me to instruct Datasource B to not execute before Datasource A has completed (or any other way to call a data population SP, before the data reader SP's execute).
I populed my data base. But before I populate I created some reports beased on this DB. Now after I populating I can execute query and see results in Management studio or query designer. But When I change it the report to preview mode I can not see it. Does anyone know why this is?
Is there a property or a format type that I can use on a field to enter an excel formula and have it actually be a formula once it is saved to excel out of reporting services.
This is a 'what if' report. That the users want to fiddle with after it is generated.
I have item number, cost, sell price, and discount from the database. I calculate the gross profit and the percent from those fields. They want to take this report, save it to excel and then have the gross profit change when they enter a new discount amount.
I have tried to just enter the fields like they would look in excel but the report just shows A3 - A2. If I use the = before it will not run and says A3 is not defined. I have tried quotes, parenthesis, square brackets any thing that looked remotely possible in properties menu to have the values save as a formula.
I don't think it is possible but would like a second opinion.
OK, I'm pretty sure that the answer to my questions is "no", but the boss is pushing, and I thought I'd double check.
I want the selection of one report parameter to decide what shows up for another report parameter. Specifically in my case it has to do with dates. I have narrowed the results down to monthly dates, meaning that the starting dates always start with mm/1/yyyy and the ending dates are always mm/31/yyyy (for a 31 day month).
The boss wants the ending dates to always be >= the month of the starting dates. So, if the user choose 1/1/2007 for starting, the minimum ending date would be 1/31/2007, etc.. Since the parameters are loaded at report initiation, and SSRS isn't really event driven, I didn't think this was possible. I just wanted to make sure before I tell the boss that it's not. Please advise.
Hi all, I have an odd issue. I have a query that returns one row from SQL Server. The value for one column (payment) in this row is 5.00. There are no other rows returned, only one row. However, when I do a sum on payment it returns 195! I dont know where its getting this from, especially since I can see all the rows by removing the SUM() and there only is one row. Here is the query:Select v_payments.tripid, Instransporttotal, lastName, firstName, tripDate, v_payments.payment,credit, translation, inscoid, v_payments.checkid from v_billbuckets left join v_patient on v_billbuckets.patientID = v_patient.ssnleft join v_payments on v_billbuckets.tripid = v_payments.tripid left join v_InsCompany on v_billbuckets.inscompanyid= v_inscompany.inscoidleft join v_billRecItem on v_payments.tripid = v_billRecItem.tripid WHERE v_payments.checkID = 315898GROUP BY v_payments.tripid, Instransporttotal, lastName, firstName, tripDate, v_payments.payment, v_payments.credit, translation, inscoid, v_payments.checkid The above query returns one row. However, If I put SUM() around v_payments.payment in the SELECT line then it is 195. I dont know why it is doing this. Any ideas? Thanks! John
I have a report that is deployed at multiple sites without any problems, except one. At one site, when I render it to pdf, it doesn't look at all right, and the alignment on a table goes from center to right aligned for no obvious reason. It then stretches a textbox horizontally to force it onto another page. I've doublechecked and made sure that all the expand options on every single thing on the report is off, and I really don't understand why it would happen at only one site, even though the data is almost the exact same. Not only that, table cells don't stretch horizontally, they stretch vertically, so again, I'm stumped about why this one site would render incorrectly to a .pdf. Even in the report viewer, everything looks fine.
I am wondering if anyone has had a problem with rendering to pdf incorrectly when the rdl and data are correct, and what your solution was.
P.S. IT isn't a problem with margining, so please, don't give the generic "CHECK YOUR MARGINS" response.
Hi,I've got a question about saving into datetime fields in a SQL Server table. A form I have create has two fields both for dates as well as other form fields, but the user may or may not fill in all the form fields, so when they click the save link I have a query which saves all the form fields whether they are blank or not. Unfortunately this is causing the two date fields to be saved in the database as "01/01/1900" even thoough they are blank fields.What is a good way to not save blank date fields as "01/01/1900"?ThanksStephen
I get this message in the SQL Server logs after I restore a db. I copied this backup from another server is that why it's telling me it formatted incorrectly? The strange thing is in QA it restores successfully but when I look @ EM it's remains in Loading mode and never completes
The backup data in 'D:SQLServerMSSQLackupjan_prod_db_20050214173 0.BAK' is incorrectly formatted. Backups cannot be appended, but existing backup sets may still be usable.
I have a .NET application with a sql server database backend. Ourclient has been having problems lately with data being returned fromthe database with wrong data. We have error logging in our storedprocedures and even this is reporting wrong. In every stored procedurewe have the following:IF @@Error <> 0BEGINEXEC dbo.HandleError@ErrorNum = @@Error,@ProcID = @@PROCIDENDThen, HandleError looks like:CREATE PROCEDURE dbo.HandleError@ErrorNum int,@ProcID intAS/************************************************** ****************** PROCEDURE: HandleError* PURPOSE: Handles logging an error* CALLED FM: Other stored procedures************************************************** ******************/SET NOCOUNT ONDECLARE @UserID intDECLARE @Host varchar(50)DECLARE @Len intDECLARE @SPName VARCHAR(100)DECLARE @ErrorMsg varchar(500)DECLARE @ErrorTrace varchar(4000)-- Get our host nameSET @Host = HOST_NAME()SET @Len = LEN(@Host) - CHARINDEX(':', @Host)IF @Len > 0 AND CHARINDEX(':', @Host) > 0SET @UserID = RIGHT(@Host, LEN(@Host) - CHARINDEX(':', @Host))ELSESET @UserID = NULLSET @SPName = OBJECT_NAME(@ProcID)SET @ErrorMsg = 'SQL Error: ' + @SPNameSET @ErrorTrace = 'Error: '+ CAST(@ErrorNum AS VARCHAR(50))EXEC dbo.InsertApplicationErrors@ErrorMessage = @ErrorMsg,@ExceptionMessage = '',@ErrorStackTrace = @ErrorTrace,@UserID = @UserID,@HostID = @Host,@Logfile = '';SET NOCOUNT OFFGOInsertApplicationErrors inserts a log into a table we have that wemonitor. We got an error back in the ErrorStackTrace column with'Error: 0'. That shouldn't happen, as the only time we log error is ifit is not 0.Does anyone have any ideas on what might be wrong?Steve
I downloaded this 7/6/07 and am trying to debug. I have a field that is PICs9(5) and has 00 00 1D as its hex value. The component returns a dt_decimal field value of 20201-
I believe it thinks the 00 are spaces and converts them to ASCII 20. Any ideas on what is wrong here? Is there another step I am missing after the conversion? Any help would be appreciated.
I have run into a strange issue that I believe is a SQL Reporting Services issue.
I have a report laid out in landscape setting that has 4 columns of text. Two of the columns are sub-reports (due to the complexity and size, we did not flatten out the data in the stored procedure) and two of the columns are regular fields.
The 2 columns of regular fields are smaller, and normally only grow to about 1/2 the height of he page. One of the two sub-reports contains large amounts of text, and at time grows larger than the height of the page.
When the sub-report grows larger than the current page, it correctly starts up on the next page. But the 2 fields of data from the main dataset (not the sub-report columns) repeat themselves on the next page as well.
What is even more strange is the 2 fields of data from the main dataset only repeat data to grow vertically as far as the sub-report needs to grow. So if there is more data in either of these 2 fields than is needed for the sub-report to grow on the 2nd page, it will cut off the data in both of these fields.
I have tried placing the information in a group header. Turned the "Repeat on new page" both True and False, Took away the table header and footer, forced a page break after each group, tried using the "Hide Duplicates" property on the field within the details section, and nothing has seemed to fix the issue.
If anyone has run into this and found a work around, let me know.
Hello All:I tried many times to install SQLserver 2005 on my My PC (VISTA Ultimate version). I always find configuration tools and working properly and can start any services and stop but I searched everywhereto find sql server management studio (SSMS) to connect but I don't find it ..... can someone help me because I've uninstalled and re-installed it many times but not found........
What does this error message mean ? We have SQL2000, with database FULL RECOVERY. Other databases on the server are fine. Backups stopped working a week ago. Can't remeber anything changing +++++++++++++++++++++++++++
The backup data in 'D:BackupTI backup.BAK' is incorrectly formatted. Backups cannot be appended, but existing backup sets may still be usable. [SQLSTATE 42000] (Error 3266) BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed.
among our server-agent jobs is one for the log-reader-agent and its run status is displayed as error though the log-reader is working correctly, replication is working fine. it had hit an error some days ago after an unexpected shutdown - but it has been auto-restarted correctly on restart of the sql-server agent. now - what can i do to get back to a sensible run-status report? i have already deleted the job-history with the hope that this might help - but it didn't. should i just stop and restart the job again? can i somehow delete the status in the jobhistory? i would gladly appreciate any ideas because it's making me just mad to have a 'failed' job status on the monitor *g*
I have seen it happen frequently that I type in a perfectly valid SSIS expression (this is easy for me since I am an old hand at C++/C/C#) in a row in a Derived Column transformation, and it turns red. Or sometimes, I will have an invalid expression that I correct, but it stays red. Finally I have also seen it happen that I make some change in the data flow pipeline and suddenly a Derived Column transform develops an error. I then go into the Derived Column transform and find that the expression has turned red. So, I literally have to go into the expression in these cases, and make a trivial change to them to get the red error to go away. Alternatively, I can cut the derived column expression text, and then paste it back in and it works (this is most telling.)
So, it seems to me the Derived Column is somehow holding onto some meta data about the Derived Column that is getting out of date (rather than re-evaluating the correctness of the Expression.) One thing I usually can do to repro this at times is to remove a column (that the Derived Column depends upon) from the pipeline and then re-add it. When I go into the Derived Column it will be red, and then like I said I have to tweak the expression to force SSIS to re-evaluate the expression.
I'm trying to import some data from an Excel 2007 file into a SQL table. I created the Source Connection Manager and an OLE DB Source Data Flow Component which uses it. (Correct me if I'm wrong, but I can't use the Excel Source because of the version of Excel the file is saved in.) The outgoing Data Flow Path thinks some of the fields being imported should be of type float, when in fact they have alpha characters in them.
The fields in the database are defined as varchars.
A Data Conversion Transform doesn't seem right because I need the data to come out of the source as string data (which it actually is in the Excel file). Even if I convert it to string on the way to the destination, I would still be missing the original alpha characters.
How/Where do I change it (Source Connect Manager, OLE DB Source Data Flow Component, something else) to correctly identify the field's type?
With Windows regional options are set to Dutch/Netherlands (or any other country using comma as the decimal symbol), MS SQL Server BCP Delivers incorrect figures to the target table.
The source table is a text file containing commas as the decimal character (As an example, in the text file 100.000 is represented as 100,000).
The target table is a Sql Server database table, with the column in question defined as a FLOAT datatype.
When BCP is run to deliver the data from the text file to Sql Server, all figures are multiplied by 1000 because the input file for BCP contains a comma as the decimal symbol.
Thus, instead of the data being delivered with a value of 100, it ends up having a value of 100000
Is there anyone out there who is aware of any command line parameters that can be added to the bcp command in order to avoid this ? Note, the field in question is defined as SQLCHAR in the BCP format file.
before anyone even says it, i checked the collation order on everything and it's the same. i get the error when the snapshot is trying to be bulk copied to the subscriber.
i'm on sql2k sp4, server and db collations are SQL_Latin1_General_SP1_CI_AS. here's a repro. 1st, run this in a blank db:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Event_Transactions]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[Event_Transactions] GO
CREATE TABLE [dbo].[Event_Transactions] ( [EventTransactionId] [int] IDENTITY (1, 1) NOT NULL , [OrphanedFlag] [bit] NOT NULL , [ProcessedFlag] [bit] NOT NULL , [ProcessedTimeStamp] [datetime] NULL , [EventTimeStamp] [datetime] NOT NULL ) ON [PRIMARY] GO
CREATE CLUSTERED INDEX [EventTransactions_IDX_ProcessedOrphanedEventTimeSt amp] ON [Event_Transactions] ( [ProcessedFlag], [OrphanedFlag], [EventTimeStamp] ) ON [PRIMARY] GO
ALTER TABLE [dbo].[Event_Transactions] ADD CONSTRAINT [PK_Event_Transactions] PRIMARY KEY NONCLUSTERED ( [EventTransactionId] ) ON [PRIMARY] GO
Now, use transactional replication to replicate it to another db taking all the defaults. when the distribution agent tries to apply the snapshot, it fails with the message mentioned in the title..
Has anyone ever seen this? It's keeping us from considering MS replication for one of our major products. Thanks.
Could someone from the SQL Server Express team confirm that SQL Server Express SP2 w/Advanced Services shows up as "SQL Server 2005" in the installed programs list? The "Express" portion seems to be missing. And how would one tell the difference between the Standard Edition and the Express Edition based on this?
I am trying to restore a 2005 backup to a 2005 database on another server. This has worked for me before. I have tried to take the backup 5 times now and each time I get the error media family incorrectly formed. Since I have successfully backed up and restored before between these same two databases I do not understand what is wrong now.
I found other questions with this error; however, they were taking a 2000 backup to a 2005 database. I am using a backup of a 2005 database to a 2005 restore.
I have a hungarian character which looks like a lower case o with two single quotes on top of it --> ő
I have this character stored in two table the datatype of the column where this is stored at is varchar in one table and nvarchar in the other. When I try to view the field in enterprise manager the character appears as it should in the 2 tables, but when I use a jsp page deployed on weblogic to look at this character the one stored in the column of type varchar displays perfectly, but the table in which the column is nvarchar the character on the jsp page appears as a Q instead.
Any inputs on how to correct this issue will be much appreciated. Any changes to the character set on the html / jsp pages has no affect on the result.
This doesn't make any sense. I am trying to install SQL Server 2005 on SBS 2003 with Service Pack 1. According to Windows Update, there is nothing left for me to install. However, I am getting this error (while installing SQL Server Express and the Developer Edition):
"Your operating system does not meet Service Pack level requirements for this SQL Server release. Install the Service Pack from the Microsoft Download Center at http://go.microsoft.com/fwlink/?LinkId=50380, and then run SQL Server setup again."
When I go to the link, there is nothing there to download (it takes me to the main Microsoft download page). I meet all the requirements that I have found. What gives?
I'm trying to populate the variable "STATUS" with the BEFORE value from TABLE1 to insert into TABLE2, but not sure how to do that. Attached is a stripped down code I'm working on. Sorry, I'm new at this...// some variable stuff protected ErrorText ErrorText1;protected System.Web.UI.WebControls.DropDownList DISP_CD;public System.Web.UI.HtmlControls.HtmlInputText DISP_DOC;protected System.Web.UI.HtmlControls.HtmlInputText DISP_DATE;protected System.Web.UI.WebControls.DataList DataTagList;protected System.Web.UI.WebControls.Button BtnUpd;public string STATUS = string.Empty; <---- Help me.// some update stuffprivate void UpdateTable(){ using(DatabaseConnection conn = new DatabaseConnection()) { try { conn.OpenConnection(devSettings.junk); for (int i = 0; i < DataTagList.Items.Count; i++) { HtmlInputText textTag = (HtmlInputText)DataTagList.Items[i].FindControl("TagList"); if (textTag.Value.Trim() != string.Empty) { GetOldStatus(conn, textTag.Value.Trim()); <---- Help me. UpdateTable1(conn, textTag.Value.Trim()); UpdateTable2(conn, textTag.Value.Trim()); } } } catch ( Exception ex ) { conn.Rollback(); throw ex; } }}// some sql table stuffprivate string GetOldStatus(DatabaseConnection conn, string tag){ StringBuilder sqlStr = new StringBuilder(); sqlStr.Append(" SELECT "); sqlStr.Append(" STATUS AS STATUS"); <---- Help me. sqlStr.Append(" FROM "); sqlStr.Append(" TABLE1 "); sqlStr.AppendFormat(" WHERE TAG in '{0}'", Functions.DBFormatUpper(tag)); conn.Update(sqlStr.ToString()); return sqlStr.ToString();}private string UpdateTable1(DatabaseConnection conn, string tag){ StringBuilder sqlStr = new StringBuilder(); sqlStr.Append(" UPDATE TABLE1 "); sqlStr.AppendFormat(" SET DISP_DOC = '{0}',",Functions.DBFormatUpper(this.DISP_DOC.Value)); sqlStr.AppendFormat(" DISP_DATE = to_date('{0}', 'mm/dd/yyyy'),", DISP_DATE.Value); sqlStr.AppendFormat(" STATUS = '{0}',", Functions.DBFormatUpper(DISP_CD.SelectedValue)); sqlStr.Append(" UPDT_DATE = sysdate "); sqlStr.AppendFormat(" WHERE TAG in '{0}'", Functions.DBFormatUpper(tag)); sqlStr.Append(" AND STATUS in ('1','2','3')"); conn.Update(sqlStr.ToString()); return sqlStr.ToString();}private string UpdateTable2(DatabaseConnection conn, string tag){ StringBuilder sqlStr = new StringBuilder(); sqlStr.Append(" INSERT INTO TABLE2 ("); sqlStr.Append(" TAG"); sqlStr.Append(" ,DATE"); sqlStr.Append(" ,FIELD1"); sqlStr.Append(" ,FIELD2"); sqlStr.Append(" ,BEFORE"); <---- Help me. sqlStr.Append(" ,AFTER"); sqlStr.Append(" ,USER"); sqlStr.Append(" )"); sqlStr.Append(" VALUES ("); sqlStr.AppendFormat(" '{0}'", Functions.DBFormatUpper(tag)); sqlStr.Append(" ,sysdate "); sqlStr.Append(" ,'JUNK1'"); sqlStr.Append(" ,'JUNK2' "); sqlStr.AppendFormat(" ,'{0}'", Functions.DBFormatUpper(this.STATUS)); <---- Help me. sqlStr.AppendFormat(" ,'{0}'", Functions.DBFormatUpper(DISP_CD.SelectedValue)); sqlStr.AppendFormat(" ,'{0}'", Functions.DBFormatUpper(devState.UserId)); sqlStr.Append(" )"); conn.Update(sqlStr.ToString()); return sqlStr.ToString();}
I've got the following code and it's not really what I want. With the below code I can select in a dropdownlist a value and in the other dropdownlist the correspondending value will be selected. But when I select a value the second dropdownlist won't be filled with all the data in the database. It is filled only with the correspondending value and not with the rest of the value. When someone changes his mind and want to select a value in the dropdownlist it can't be done. Any ideas??Default.aspx:<body> <form id="form1" runat="server"> <div> <asp:Label ID="Label1" runat="server" Text="Botanische Naam: "> </asp:Label> <asp:DropDownList ID="DDL1" AutoPostBack="True" runat="server" OnSelectedIndexChanged="ChangeBotanicName" DataSourceID="SqlDataSource1" DataTextField="Botanische_Naam" DataValueField="Botanische_Naam"> </asp:DropDownList> <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:BonsaiDataBaseConnectionString %>" SelectCommand="SELECT [Botanische Naam] AS Botanische_Naam FROM [BonsaiSoorten]"> </asp:SqlDataSource> <asp:sqldatasource id="SqlDataSource2" runat="server" connectionstring="<%$ ConnectionStrings:BonsaiDataBaseConnectionString%>" selectcommand="SELECT [Nederlandse Naam] AS Nederlandse_Naam FROM [BonsaiSoorten]WHERE [Botanische Naam] = @Title1"> <selectparameters> <asp:controlparameter name="Title1" controlid="DDL1" propertyname="SelectedValue" /> </selectparameters> </asp:sqldatasource> <asp:Label ID="Label2" runat="server">Nederlandse Naam:</asp:Label> <asp:DropDownList ID="DDL2" AutoPostBack="True" runat="server" OnSelectedIndexChanged="ChangeDutchName" DataSourceID="SqlDataSource3" DataTextField="Nederlandse_Naam" DataValueField="Nederlandse_Naam"> </asp:DropDownList> <asp:SqlDataSource ID="SqlDataSource3" runat="server" ConnectionString="<%$ ConnectionStrings:BonsaiDataBaseConnectionString %>" SelectCommand="SELECT [Nederlandse Naam] AS Nederlandse_Naam FROM [BonsaiSoorten]"> </asp:SqlDataSource> <asp:sqldatasource id="SqlDataSource4" runat="server" connectionstring="<%$ ConnectionStrings:BonsaiDataBaseConnectionString%>" selectcommand="SELECT [Botanische Naam] AS Botanische_Naam FROM [BonsaiSoorten]WHERE [Nederlandse Naam] = @Title2"> <selectparameters> <asp:controlparameter name="Title2" controlid="DDL2" propertyname="SelectedValue" /> </selectparameters> </asp:sqldatasource> </div> </form></body>Default.aspx.vb:Partial Class _Default Inherits System.Web.UI.Page Sub ChangeBotanicName(ByVal Sender As Object, ByVal e As System.EventArgs) DDL2.DataSourceID = "SqlDataSource2" End Sub Sub ChangeDutchName(ByVal Sender As Object, ByVal e As System.EventArgs) DDL1.DataSourceID = "SqlDataSource4" End SubEnd Class P.S. I posted this before but can't find it anymore so here it is again
I have a table with a list of products,once I enter the data into the table and start using it on my web site as a drop down list,the list is sorted as an alphabetical list,is there are way to have a single drop down list but still be able to group the those products,in order words force them not to get sorted aphabetically.
I have a DataSet (which requires 2 parameters), and for some reason even though it works in Query Analyzer and on the Data Tab of VS2003 it will not work when I choose to Preview it. Can anybody shed some light on this behaviour?
If you need to see the dataSet I can post it up! Thanks!
