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.
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'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?
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.
I've got this little problem. I need to insert data from a table to another table. The scenario looks as follows: I've got 'Company' table (no duplicated records there) and 'Contacts' table (one to many relation: for one company there can be more than one contact). The following statement retrieves the data but it shows me everything, including all contacts and therefore I get duplicating values, e.g. company name. Is there any way of changing the following query so it works?
Pulled all my hair out now, can someone please offer some help before I go totally mad...
I have no page header, this is all in the body. I have a List with account number and job number in it at the top, called list1. In list1 I have list2, with the job details, basically a list of people who attended the job. The fields in list2 are textboxes, they're not in a table.
EG: Acc No: 12345 Job No: 54321
Name Fred Bloggs Joe Bloggs Etc
When my list2 of names goes over 1 page, I want to repeat the account number and job number on each subsequent page. In my real report I actually have 10 fields I want to repeat.
I then want to be able to export this report to a PDF.
I can't put the header in the page header and RepeatWith doesn't work when exporting to PDF, or at all depending on what mood it's in.
Can someone tell me why my stored procedure is repeating the Name in the same column? Here's my stored procedure and output: select distinct libraryrequest.loanrequestID, titles.title, requestors.fname + ' ' + requestors.lname as [Name], Cast(DATEPART(m, libraryrequest.requestDate) as Varchar(5)) + '/' + Cast(DATEPART(d, libraryrequest.requestDate) as Varchar(5)) + '/' + Cast(DATEPART(yy, libraryrequest.RequestDate) as Varchar(5)) as RequestDate, Cast(DATEPART(m, libraryrequest.shipdate) as Varchar(5)) + '/' + Cast(DATEPART(d, libraryrequest.shipdate) as Varchar(5)) + '/' + Cast(DATEPART(yy, libraryrequest.shipdate) as Varchar(5)) as ShipDate from LibraryRequest join requestors on requestors.requestorid=libraryrequest.requestoridjoin titles on titles.titleid = requestors.titleidwhere shipdate is not null Output: ID Title Name Request Date Ship Date 29 Heads, You Win Brenda Smith 1/18/2008 1/18/200835 Still More Games Brenda Smith 1/22/2008 1/22/200851 The Key to.. Brenda Smith Brenda Smith 1/29/2008 1/29/200852 PASSION... Brenda Smith Brenda Smith 1/29/2008 1/29/200853 LEADERSHIP Brenda Smith Brenda Smith 1/29/2008 1/29/2008 Going crazy ugh...
I am querying several tables and piping the output to an Excel spreadsheet. Several (not all) columns contain repeating data that I'd prefer not to include on the output. I only want the first row in the set to have that data. Is there a way in the query to do this under SQL 2005?
As an example, my query results are as follows (soory if it does not show correctly): OWNERBARN ROUTE DESCVEHDIST CASE BARBAR TRACKING #70328VEH 32832869.941393 BARBAR TRACKING #70328VEH 32832869.941393 BARBAR TRACKING #70328VEH 32832869.941393 DAXDAX TRACKING #9398VEH 39839834.942471 DAXDAX TRACKING #9398VEH 39839834.942471 DAXDAX TRACKING #9398VEH 39839834.942471 TAXTAX TRACKING #2407 40754.391002 TAXTAX TRACKING #2407 40754.391002 TAXTAX TRACKING #2407 40754.391002
I only want the output to be: OWNERBARN ROUTE DESCVEHDIST CASE BARBAR TRACKING #70328VEH 32832869.941393
Hopefully this makes sense, not sure what to even begin researching...
I'm trying to optimize all facets of this process, as it will take over the resources on my server if not done efficiently.
I have CSV files containing INTs that I need to upsert (match to an existing/earlier imported array or create a new record set) millions of times a day. To be clear, this data is a small subset of the actual import, this arrays contents are not the main data of the process, and the value of the entire array is meant to be related to higher level tables.
The contents of the CSV array are 99.9+% repeating, meaning they will very often share the exact same contents as a a previously imported array. A rough guess is there are 20k combinations existing, and less than 1k new per month, and will range from 6 cols x 15 rows to 6 cols x 50 rows.
So current plan is to use a MD5 hash during the (not SQL related) export process to identify the contents of this CSV file, and export only the md5 (32 digit hex) as a lookup to identify the contents. If the SQL import process finds a new (unknown) MD5 it will request the actual contents, otherwise it will simply use the MD5 as a key/id/code for the actual array contents that are already stored.
There's probably a certain terminology I'm not familiar with for this type of thing.. I've never heard of something like this. I realize collision is a threat, but I'm unsure how much I should be worried about it with this type of data (similar size/contents, but a relatively small amount of possibilities). I think up to even 0.1% collision would be acceptable which is probably way more than enough.
Does this sound like a bad idea to anyone? Are there certain hash functions I should use for this type of thing? Anyone have suggestions of where to look next?
I'm using RS2000 SP2 and am getting an issue when exporting to PDF. If I have a table that spans more than one page and I set the RepeatHeaderOnNewPage to True, then on occasion the table header will be displayed on top of the first few rows of data. It does not happen on all the pages or all the time and I can not find any information on this issue. Has anyone come across this before and solved it?
I have a new SQL 2005 (SP2) Reporting Services server to which I've just upgraded and deployed some SSRS 2000 reports.
I have a subreport that contains a matrix with two groups. The report data seems to be inexplicably repeating the data for the first row in the group for all rows in the group. Example:
ID1 ID2 DisplayData
1 1 A
1 2 B
1 3 C
2 1 A
2 2 B
2 3 C
Parent group is on ID1, child group is on ID2, report would show:
1 1 A
2 A
3 A
2 1 A
2 A
3 A
Is this a matrix bug in 2005 SP2, or do I need to do something differently? I can no longer pull a comparison version from an SSRS 2000 server to verify, but I believe it was working as expected before...
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 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.
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.
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.
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.
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.
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?
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 have a list that is grouped by Department inside it I have a table with two columns: name and hours. For example:
Department A
Name Houres Mike 1
Department B
Name Houres Mike 1 Steve 1
Department C
Name Houres Mike 1 Steve 1
Outside the list I have another table that has a column for total hours for all departments: Which in this case is 5. Everything work fine, but I have problem with displaying the total hours for all departments. In other words this will be my output 5 5 5 5 5 5 5 5 5 5 5 5
It will be repeated multiple times. I believe I have to add group to the table that contains the column (total hours for all departments.) but I can€™t do that b/c It is the total for all departments. Also I tried to hide duplicate wich works, but the problem with that when I export it to pdf it will print empty pages.
