I tried to set double in bottom border of text file. But it turned out to be single line either Previewing in Designer or Printing or Exporting to PDF. Does anyone have any idea?
View 10 RepliesI have scoured the Microsoft forums and the internet to find out how I can generate the output of a CSV report that has double quotes around each value and is comma separated as follows:
"Abcd","123456","Efghi","789012","JKLMN"
If I try to concatenate double quotes around the values in the stored procedure or in the RDL, two double quotes appear around each value as follows.
""Abcd"",""123456"",""Efghi"",""789012"",""JKLMN""
I understand that this is because the default qualifier is double quote. What I see is that every time a double quote appears in a value (along with commas and line breaks), the qualifier will activate. Is there any way to turn this off for double quotes?
If I try to enter: <Qualifier>false</Qualifier>, the word "false" appears as the qualifier instead.
The only way I have found that produces a result similar to what I need ("Abcd","123456","Efghi","789012","JKLMN") is if I add a line break - chr(10) in the RDL in each field. However, this won't work for me because I can't have line breaks in each field in the output.
Note that in SSRS 2005, I was able to produce the report output as I state above by setting the field delimiter and qualifier as follows:
<FieldDelimiter>@?!?@</FieldDelimiter>
<Qualifier>?#^?</Qualifier>
This essentially turned the field delimiter and qualifier off, as the values entered would never appear in the data. I then could add double quotes and commas in the RDL. This used to work in the old version but does not anymore.
I am displaying the red line in the beginning of the current week column in the matrix table. But for few resources the red line is missing. For Example in below screenshot, refer the 'Adrian Larkin' resource. Here is the SSRS report border expression which is comparing the [TimeByDay] with the current Week First Monday Date to display the red bar -
=IIf(Fields!TimeByDay.Value = DATEADD("d", -(WeekDay(Today(),2))+1, Today()), "Red", "Black")"
Here is the dataset result set. If you refer the column [TimeByDay], it has no record for the date 11/05/2015 for the Adrian resource as he has no assignment for the date 11-05-2015.display the red missing line red bar using the expression or any other solution.
How do I achieve double spacing of lines in a large text box in SSRS?
View 6 Replies View RelatedHi all,
I'm having an interesting problem. In my report, I use a table to show my data set. The borders of the table are giving me a hard time...
The problem is, I'm not being able to display the bottom border for a row, but only at the end of the page, causing the table containing multi paged report data to be seen inside a box. Just to be clear;
I don't want to display borders of each row
I don't want to display table footer in each page (Only on last page)
I've already tried to display page footer, top border. Its not the answer :D
Thanks very much...
SMang
Can this be done in SSRS or must it be done in the query? Ideas? Suggestions? See Example Below:
Current table look:
1 Data.........
2 Data.........
3 Data.........
4 Data.........
5 Data.........
6 Data.........
7 Data.........
Etc...
The Way I need it to look
1 Data.........
2 Data.........
3 Data.........
4 Data.........
5 Data.........
6 Data.........
7 Data.........
Etc...
TIA (Thanks in Advance!)
I was wondering if anyone might have any insight into the following issue:
I would like to have a bottom border of 'solid' for the entire row on every fifth row of one of my reports. The rest of the rows should have no bottom border. I know there must be an expression I can write to do this involving RowNumber but I can't seem to figure it out. Does anyone know how to make this happen?
Please zoom your browser to 200% to make the images clearer. Notice that the left and right edges of the parent Month column in the second example are 2pt black and the inner edges of Forecast, Budget and Actual are 1 point light gray.
View 4 Replies View RelatedI am creating a report in which i have used ranges .
i have used ranges inside a rectangle but when i preview the tablix ranges are coming Perfect concern is that during export to excel border of the rectangle comes only for one row and gets hidden for second row .and its working perfect when exporting to PDF .
For my SSRS report, by default the border color is light grey. So how to change the entire border color to black.
FYI: I am able to change the border color for each text box but not able to do for entire table report.
Also i tried by clicking the table report item & then to properties window and changed the border color to black but still there is no change.
I have the following:
-----------------
WHILE PATINDEX('%,%',@Columns)<> 0 BEGIN
SELECT @Separator_position = PATINDEX('%,%',@Columns)
SELECT @array_Value = LEFT(@Columns, @separator_position - 1)
SET @FieldTypeID = (SELECT FieldTypeID FROM [Form].[Fields] WHERE FieldID = (CAST(@array_Value AS INT)))
SET @FieldName = (SELECT [Name] FROM [Form].[Fields] WHERE FieldID = @array_Value)
print 'arry value' + CONVERT(VarChar(500), @array_value)
print 'FieldTypeID: ' + CONVERT(VARCHAR(500), @FieldTypeID)
PRINT 'FieldName: ' + @FieldName
BEGIN
IF @FieldTypeID = 1 OR @FieldTypeID = 2 OR @FieldTypeID = 3 OR @FieldTypeID = 9 OR @FieldTypeID = 10 OR @FieldTypeID = 7
BEGIN
SET @InnerItemSelect = ' (SELECT ISNULL(CONVERT(VARCHAR(MAX),[Value]),'''') FROM [Item].[ItemDetailFieldRecords] IDFR WHERE IDFR.ItemDetailID = ID.ItemDetailID AND IDFR.FieldID = ' + @array_Value + ') AS ''' + @FieldName + ''' '
SET @InnerTaskSelect = ' (SELECT ISNULL(CONVERT(VARCHAR(MAX),[Value]),'''') FROM [Item].[TaskFieldRecords] TFR WHERE TFR.TaskID = T.TaskID AND TFR.FieldID = ' + @array_Value + ') AS ''' + @FieldName + ''' '
END
ELSE IF @FieldTypeID = 4 OR @FieldTypeID = 8 --DropDownList/RadioButtonlist
BEGIN
SET @InnerItemSelect = ' (SELECT [Value] FROM [Form].[FieldListValues] FFLV INNER JOIN [Item].[ItemDetailFieldListRecords] IDFLR ON FFLV.FieldListValueID = IDFLR.FieldListValueID WHERE IDFLR.ItemDetailID = ID.ItemDetailID AND FFLV.FIeldID = ' + @array_value + ') AS ''' + @FieldName + ''' '
SET @InnerTaskSelect = ' (SELECT [Value] FROM [Form].[FieldListValues] FFLV INNER JOIN [Item].[TaskFieldListRecords] TFLR ON FFLV.FieldListValueID = TFLR.FieldListValueID WHERE TFLR.TaskID = T.TaskID AND FFLV.FIeldID = ' + @array_value + ') AS ''' + @FieldName + ''' '
END
ELSE IF @FieldTypeiD = 5 --Cascading
BEGIN
SET @InnerItemSelect = ' (SELECT [FCV].[Value] FROM [Form].[FieldCascadingValues] FCV INNER JOIN [Form].[FieldCascadingLookUpTables] LT ON FCV.FIeldCascadingLookupTableID = LT.FieldCascadingLookupTableID INNER JOIN [Item].[ItemDetailFieldCascadingRecords] IDFCR ON IDFCR.FieldCascadingValueID = FCV.FieldCascadingValueID WHERE IDFCR.ItemDetailID = ID.ItemDetailID AND LT.FieldID = ' + @array_value + ') AS ''' + @FieldName + ''' '
SET @InnerTaskSelect = ' (SELECT [FCV].[Value] FROM [Form].[FieldCascadingValues] FCV INNER JOIN [Form].[FieldCascadingLookUpTables] LT ON FCV.FIeldCascadingLookupTableID = LT.FieldCascadingLookupTableID INNER JOIN [Item].[TaskFieldCascadingRecords] TFCR ON TFCR.FieldCascadingValueID = FCV.FieldCascadingValueID WHERE TFCR.TaskID = T.TaskID AND LT.FieldID = ' + @array_value + ') AS ''' + @FieldName + ''' '
END
ELSE IF @FieldTypeiD = 6 --ListBox
BEGIN
SET @InnerItemSelect = ' (SELECT i.[CSV] FROM @ItemDetailLV i WHERE i.ID = ID.ItemDetailID AND i.FieldID = ' + @array_value + ') AS ''' + @FieldName + ''' '
SET @InnerTaskSelect = ' (SELECT it.[CSV] FROM @TaskLV it WHERE it.ID = T.TaskID AND it.FieldID = ' + @array_value + ') AS ''' + @FieldName + ''' '
END
ELSE IF @FieldTypeID = 11 --Users
BEGIN
SET @InnerItemSelect = ' (SELECT SU.[UserID] FROM [Security].[Users] SU INNER JOIN [Item].[ItemDetailUserRecords] IDUR ON SU.UserID = IDUR.UserID WHERE IDUR.ItemDetailID = ID.ItemDetailID AND IDUR.FieldID = ' + @array_value + ') AS ''' + @FieldName + ''' '
SET @InnerTaskSelect = ' (SELECT SU.[UserID] FROM [Security].[Users] SU INNER JOIN [Item].[TaskUserRecords] TUR ON SU.UserID = TUR.UserID WHERE TUR.TaskID = T.TaskID AND TUR.FieldID = ' + @array_value + ') AS ''' + @FieldName + ''' '
END
ELSE IF @FIelDTypeID = 12 --Group
BEGIN
SET @InnerItemSelect = ' (SELECT SG.[GroupID] FROM [Security].[Groups] SG INNER JOIN [Item].[ItemDetailGroupRecords] IDGR ON SG.GroupID = IDGR.GroupID WHERE IDGR.ItemDetailID = ID.ItemDetailID AND IDGR.FieldID = ' + @array_value + ') AS ''' + @FieldName + ''' '
SET @InnerTaskSelect = ' (SELECT SG.[GroupID] FROM [Security].[Groups] SG INNER JOIN [Item].[TaskGroupRecords] TGR ON SG.GroupID = TGR.GroupID WHERE TGR.TaskID = T.TaskID AND TGR.FieldID = ' + @array_value + ') AS ''' + @FieldName + ''' '
END
END
PRINT 'Inner Item Select:' + @InnerItemSelect
PRINT 'Inner Task Select:' + @InnerTaskSelect
SET @IDSelect = @IDSelect + @InnerItemSelect + ', '
SET @TSelect = @TSelect + @InnerTaskSelect + ', '
SELECT @Columns = STUFF(@Columns, 1, @separator_position, '')
END
---------------
That is only part of a large query that writs a SQL Query to a column in a Database. That Query (in the column) is just ran normally so I don't need to compile it each time I want to run it.
THe problem I have is @FieldName might be: ryan's field.
That apostrophe is killing me because the SQL keeps it as ryan's field, not ryan''s field(note the 2 apostrophes). I cannot do: REPLACE(@FieldName, ''', '''') because it's not closing the apostrophes. Is there an escape character that I can use to say only one: ' ?
I tried:
DECLARE @t VARCHAR(500)
SET @t = (SELECT [Name] FROM [Form].[Fields] WHERE FieldID = 1)
print @t -- @t will print: Ryan's Field
PRINT QUOTENAME('' + @t + '', '''')
but that gives me: 'Ryan''s Field'
Any help would rock. If I make any changes to the way this field is input into the DataBase, I know I will need to do a lot of re-work in many spots. WHich is why i'm trying to solve this on the SQL side.
Hi All, I am facing quotes problem. Without using the quotes
my query is running fine, but I need to use IIF condition so for that I
need quotes adjustment. I didn't figured it out how to adjust them, try
several techniques but no success. I am using dotnetnuke. {IIF,"[frmradio,form]=text"," SELECT Docs.FileName, Dept_LegalLaw.MediaID, Dept_LegalLaw.ID, Dept_LegalLaw.LevelID, Dept_LegalLaw.LawID, Dept_LegalLaw.LawDate, Dept_LegalLaw.Agreement, Dept_LegalLaw.Name, Dept_LegalLaw.NameSearch, Dept_LegalLawType.LawType, Dept_LegalLaw.LawNo, Dept_LegalMinistries.RegID, Dept_LegalLaw.IssueNo, Dept_LegalLaw.Attachment, Dept_LegalLaw.Amendment, Dept_LegalLaw.Scanned, Dept_LegalLaw.Html, Dept_LegalMinistries.Description FROM OPENQUERY(LEGALDBSERVER, 'SELECT Filename FROM SCOPE() WHERE Contains('" @FilterAnyWrd ")' ) AS Docs INNER JOIN Dept_LegalLaw ON Docs.FileName = Dept_LegalLaw.FileName INNER JOIN Dept_LegalMinistries ON Dept_LegalLaw.RegID = Dept_LegalMinistries.RegID INNER JOIN Dept_LegalLawType ON Dept_LegalLaw.LawID = Dept_LegalLawType.LawID ", " "} {IIF,"'[frmradio,form]'='title'"," SELECT MediaID, Dept_LegalLaw.ID, Dept_LegalLaw.LevelID, Dept_LegalLaw.LawID, LawDate, Agreement, Name, NameSearch, Dept_LegalLawType.LawType, LawNo, Dept_LegalMinistries.RegID, IssueNo, Attachment, Amendment, Scanned, Html, Dept_LegalMinistries.Description, Dept_LegalLaw.FileName FROM Dept_LegalLaw LEFT JOIN Dept_LegalMinistries ON Dept_LegalLaw.RegID COLLATE DATABASE_DEFAULT = Dept_LegalMinistries.RegID COLLATE DATABASE_DEFAULT INNER JOIN Dept_LegalLawType ON Dept_LegalLaw.LawID COLLATE DATABASE_DEFAULT = Dept_LegalLawType.LawID COLLATE DATABASE_DEFAULT WHERE @FilterLawNo AND @FilterLawID AND @FilterRegID AND @FilterIssueNo AND @FilterFromDate AND @FilterToDate AND @FilterNtContNew AND @FilterAgreement AND @FilterAllWrdNew AND @FilterExWrdNew AND @FilterAnyWrdNew ORDER BY [SORTTAG] ", " "} Thanks for any help
I need the Trend line for the following data in Line chart they are the following data. The following are the graph are my output and i need the trend line for these Key_gap value.
This is the link [URL] ....
I need the same trend line for the Bar-Chart in SSRS 2005.
Hi: Got a newbie question that's been giving me fits! Basically I'm replicating what's going on here on this board...creating a "posting" interface that takes the "message" and inserts it into a table using an ADODB connection (using INSERT INTO table name,tablecells and VALUES)
However, if someone types in a single or double quote in the body of the message, I get an error similar to this:
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near 's'.
/test.asp, line 29
I think I understand why it's happening (SQL is interpreting the quote mark as a string-end), but what am I supposed to do to get around it?
I've got a flat file data source, that is to large to edit with most Windows apps on my server that contains both single and double quote characters that I need to load in a varchar column.
So I attempted to do it with a Replace in data transformation, but I can't get SSIS to allow me to use a variable or pair of single or double quotes within the replace.
If I don't replace the single quote characters with a pair then the records containing these characters all end up in my failed records output file.
Here are 5 example property legal descriptions from my FLAT FILE data source:
COM 441'6" N OF SW/C OF NW4 OF SEC 22-29-20 ELY1340' N200' CROSSING THE CNTR OF TR AT 100 WLY1240' S200' TO POB CONTAINING 6 3/10 ACRE MOL
N 50' OF S 330' OF W 122' OF E 735' OF SW4 OF NE4 OF SEC 28/28/18 A/K/A LOT
271 BLK "M" OF$PB 14/36-T
LOT 9 BLK "BA" OF$PB 39/1
OVERCODED POST LTS 17 21-42 47-55 & 69 PB 27/110 "ALL" SECS 16-21, 28 29/31/19 & "ALL"
N 100' OF S 815' OF TR "H" OF PB 28/58 LESS W 15' FOR ESMT ESMT DESC AS W 15' OF S 815' OF TR "H" OF PB 28/58
I've a weird problem in my application. In of the pages, while trying to update the text box "Name", when I enter Linda's test, it gets saved as Linda''s test. I'm not sure if this is a problem due to SQL server. When I look at the stored procedure, I don't anything different. Also, when I update the table directly in SQL Server, the result is displayed in single quote. But if I update the field thro' the application, the returned name is with double quotes instead of single quote. Has any of you faced problems like this? What am I missing? What do I need to do to get the name saved the way I entered (with single quotes) instead of double quotes?
View 1 Replies View Related
i have a list report
some values of this are blank or null
i want to avoid that spacing if it is null
like
name:jjj
lastname kkk
firstname
address uuuu
i want it should be
name jjj
lastname kkk
address uuuu
I want use an action to launch another report when clicking on a textbox however I only want to do it under certain circumstances. Strangely I don't see an option to put an expression on the "Action" property. Why is this?
Is there any other way to conditionally set the Action?
[URL]
Would turning off ViewState in Report Manager be safe? Or at least is there a way to deactivate ViewState for viewing reports in Report Manager instead of all pages in the web site?
I'm not sure of the impact of deactivating this with the internals of the web application.
Just messing about with a silly little problem in SQL which has me stumped!
The problem goes like this;
There are lots of books.
There are lots of authors.
Every book has atleast one author (primary author).
Some books have a secondary author too!
Books(BookId, BookName)
Authors(AuthorId, AuthorName)
BookAuth(BookId, AuthorId, PrimaryAuth)
Where primary auth is a bit field; 1 denotes primary, 0 denotes secondary.
The problem is trying to return a single line per book - something like
BookNamePrimary AuthorSecondary Author
Book 1DaveNULL
Book 2JohnDave
Book 3LauraJohn
The following code is the little test script I've written which you may wish to utilise
IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'Books') BEGIN
DROP TABLE Books
END
IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'Authors') BEGIN
DROP TABLE Authors
END
IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'BookAuth') BEGIN
DROP TABLE BookAuth
END
CREATE TABLE Books (bookid int, bookname varchar(50))
CREATE TABLE Authors (authorid int, authorname varchar(50) )
CREATE TABLE BookAuth (authorid int, bookid int, primaryauth bit)
GO
INSERT INTO Books
SELECT 1, 'MS Access Book'UNION ALL
SELECT 2, 'Microsoft Excel for Peanuts' UNION ALL
SELECT 3, 'Some Autobiography'UNION ALL
SELECT 4, 'SQL for Beginners'UNION ALL
SELECT 5, 'Advanced T-SQL'UNION ALL
SELECT 6, 'I can''t think of another title'
INSERT INTO Authors
SELECT 1, 'Dave'UNION ALL
SELECT 2, 'Steve'UNION ALL
SELECT 3, 'John'UNION ALL
SELECT 4, 'Joanne'UNION ALL
SELECT 5, 'Christina'UNION ALL
SELECT 6, 'Alex'
INSERT INTO BookAuth
SELECT 1,1,1 UNION ALL
SELECT 2,1,0 UNION ALL
SELECT 1,2,1 UNION ALL
SELECT 1,3,1 UNION ALL
SELECT 3,3,0 UNION ALL
SELECT 4,4,1 UNION ALL
SELECT 4,5,1 UNION ALL
SELECT 5,6,1 UNION ALL
SELECT 6,6,0
SELECT * FROM bookauth
DROP TABLE Books
DROP TABLE Authors
DROP TABLE BookAuth
It should be so easy - but I can't get it so far! :D
i have a query in which i need to show all rows of same id in singel row and last column should be comma seperated .my query is :
BEGIN TRY
SET NOCOUNT ON
DECLARE @l_numberOfRecordsInserted INT
-- Check if valid Application User ID is passed
IF ( @i_AppUserId IS NULL ) OR ( @i_AppUserId <= 0 )
[code]....
so last column section name should come as comma seperated .
Hi,
I am executing the folowing statements in my stored procedure, I want to ask that can all these staments will be executed and combined in a single line of code, meaning can we run all these statemnts in a single line. if not, then any other optimized way to perform this task and other way which will execute with less load on the server.
Code Block
SET NOCOUNT ON
TRUNCATE TABLE WebNew_T_Dump
TRUNCATE TABLE WebNew_TOD_Dump
TRUNCATE TABLE WebNew_T_S_Dump
TRUNCATE TABLE WebNew_AT_Dump
TRUNCATE TABLE WebNew_MMM_Dump
TRUNCATE TABLE WebNew_MMT_Dump
TRUNCATE TABLE WebNew_TC_Dump
TRUNCATE TABLE WebNew_TRW_Dump
TRUNCATE TABLE WebNew_SM_Dump
TRUNCATE TABLE WebNew_TS_Dump
TRUNCATE TABLE WebNew_TL_Dump
TRUNCATE TABLE WebNew_TLC_Dump
TRUNCATE TABLE WebNew_TS_Dump
TRUNCATE TABLE WebNew_TP_Dump
TRUNCATE TABLE WebNew_TSS_Dump
TRUNCATE TABLE WebNew_IT_Dump
Thanks,
When I edit package configuration files inside of Visual Studio, it puts all the XML on a single line. Does anyone know how to automatically spread the information onto multiple lines, to make comparing file differentials easier.
Thank you,
Mike
I have a Reporting Services implementation setup to run in Native mode. Reports on the server are being accessed through a Sharepoint website that has a Report Viewer control.
This is posing a problem because after the user logs into Sharepoint, they are again prompted to login to the report server. Is there a way to pass the users Sharepoint credentials to the report server to prevent this double login prompt?
Thanks in advance for your help!!!
We run std 2008 r2. On my col heading text boxes and data text boxes i'm purposely picking only borders above and left in an attempt to get rid of what looks like a double border between text boxes.
The only places I see what appears to be a single thickness is the left of the 1st column hdg and data, and above any col hdg text box I set this way.
Also, when I pick .5 instead of 1 pt thickness, ssrs always puts it back to 1 pt.
How can I get the appearance of single thickness borders instead double?
Filtrate the value in the column by using filter function..I get error message due to decimal and double.How should I convert to value 55 into double or decimal? Today, I'm using SSRS 2012.
View 4 Replies View Relatedwe have a value in a column with double space between characters like" abcd efgh", when I do the preview of the report I see the value as it is with double space; but when I deploy the report on to the reporting server, I just see only single space between the characters. whys is it eliminating one space when it is deployed on the reporting server? how can I get the same value with 2 spaces?
View 2 Replies View RelatedI need to, ultimately, create a flatfile for exporting insurance information to a third-party vendor. Each individual is to have no more than 1 line per file, which contains their coverage information, if any, on 4 different type of insurance. What i need is for 2 fields in a table to determine the output for multiple fields in the flatfile.
What I have so far works, to an extent. If I have insurance types 1, 2, 4 (of types 1-4) required output is (__ = 2 blank spaces):
1A 1B 1C 1D 1E 2A 2B 2C 2D 2E 3A 3B 3C 3D 3E 4A 4B 4C 4D 4E
== == == == == == == == == == == == == == == == == == == ==
Y N __ MD XX Y N __ MD XX N __ __ __ __ Y N __ DN XX
If they have coverage, A always = Y, B always = N, C always = blank(null), D is their ins. type, E is their cov. type(CASE statement). if they DON'T have that type of coverage, A always = N and the remaining field are NULL.
After a lot of work, and scouring a forum or 2, I attempted a whole lot of CASE functions. Below is an sample of code representing the 1x statements. This same code is repeated 4 times with the 1x being altered with 2x, 3x, 4x.
CASE HB.PLAN_TYPE
WHEN '10' THEN 'Y'
ELSE 'N' END AS 1A,
CASE HB.PLAN_TYPE
WHEN '10' THEN 'N'
ELSE ' ' END AS 1B,
' ' AS 1C,
CASE HB.PLAN_TYPE
WHEN '10' THEN HB.BENEFIT_PLAN
ELSE ' ' END AS 1D,
CASE HB.PLAN_TYPE
WHEN '10' THEN (CASE WHEN HB.COVRG_CD ='1' THEN 'XX'
WHEN HB.COVRG_CD ='2' THEN 'YY'
WHEN HB.COVRG_CD ='3' THEN 'ZZ'
ELSE 'WW' END)
ELSE ' ' END AS 1E,
It works to an extent. While the desires/required output it above, the output this produces (same scenario) is:
1A 1B 1C 1D 1E 2A 2B 2C 2D 2E 3A 3B 3C 3D 3E 4A 4B 4C 4D 4E
== == == == == == == == == == == == == == == == == == == ==
Y N __ MD XX N __ __ __ __ N __ __ __ __ N __ __ __ __
1A 1B 1C 1D 1E 2A 2B 2C 2D 2E 3A 3B 3C 3D 3E 4A 4B 4C 4D 4E
== == == == == == == == == == == == == == == == == == == ==
N __ __ __ __ Y N __ MD XX N __ __ __ __ N __ __ __ __
1A 1B 1C 1D 1E 2A 2B 2C 2D 2E 3A 3B 3C 3D 3E 4A 4B 4C 4D 4E
== == == == == == == == == == == == == == == == == == == ==
N __ __ __ __ N __ __ __ __ N __ __ __ __ Y N __ DN XX
While there is supposed to be 1 line, regardless of number of insurance types, it only produces 1 line per type. I first tried this in Access, and was able to get it to work, but it required multiple queries resulting in a crosstab, export to Excel and manually manipulate the data, export to text file to manipulate, import back into Excel to manipulate, import back into Access and finally export into a flatfile. Far too much work to produce a file which is why I'm trying to convert it to raw SQL.
Any assistance in this matter would be greatly appreciated.
This is driving me bananas. Can't find any info on this anywhere....SQL 2000 seems to replace double space with a single space when I seta varchar field to " " (2spaces), it only stores " " (1space). Whyon earth would microsoft do this? If I save 2 spaces - I WANT TO SEE2 SPACES!!!!Can anyone help? Is this a database setting? Is this due to usingvarchar?Any help appreciated.Colin Hale
View 2 Replies View RelatedI am performing a series of calculations where accuracy is very important, so have a quick question about single vs double precision variables in SQL 2008.
I'm assuming that there is an easy way to cast a variable that is currently stored as a FLOAT as a DOUBLE prior to these calculations for reduced rounding errors, but I can't seem to find it.
I've tried CAST and CONVERT, but get errors when I try to convert to DOUBLE.
For example...
SELECT CAST(1.0/7.0 AS FLOAT)
SELECT CONVERT(FLOAT, 1.0/7.0)
both give the same 6 decimal place approximation, and the 6 decimals make me think this is single precision.
But I get errors if I try to change the word FLOAT to DOUBLE in either one of those commands...
SELECT CAST(1.0/7.0 AS DOUBLE)
gives "Incorrect syntax near )"
SELECT CONVERT(DOUBLE, 1.0/7.0)
gives "Incorrect syntax near ,"
Every night we connect to a remote server using Linked Server and copy details from that database to a loading table, then load it into the 'real' table in our own environment. The remove database we load it from has indexes/primary keys that match the 'real', however the 'loading' table itself does not have any indexes or primary keys, both are SQL Server 2005 machines.
In the loading table we first of all truncate it then do a select insert statement from the remote server, then we then truncate the 'real' table and load iit from the 'loading' table.
The issue is when we attempted to load it into our 'real' table from our loading table there was a duplicate row, and our process failed with a Primary Key violation.
I checked the source with does have the same primary key's in, it did not contain a duplicate row and I checked the loading table and that did contain a duplicate row.
My question this is in what circumstances this could happen ?
Any better way to query SQL 2012 to display the code of a stored proc to a single line. I'm trying to write a script to insert the contents of the procs between my devestprod environments. So people can query a single table for any proc that is different between environments. At the moment I am using the syscomments view and the text column but the problem here is if you get a lengthy proc it cuts it up into multiple rows.
I can get around it by converting the text to a varchar(max) and outer joining the query, but as you can see by my code below I have to try and guess what the maximum number of rows I'm going to get back for my largest proc. If someone adds a new one that returns 8 rows I'm going to miss it with this query.
Select col1.[type],col1.[name],convert(varchar(max),col1.text) + isnull(convert(varchar(max),col2.Text),'')
+ isnull(convert(varchar(max),col3.Text),'')
+ isnull(convert(varchar(max),col4.Text),'')
+ isnull(convert(varchar(max),col5.Text),'')
+ isnull(convert(varchar(max),col6.Text),'')
+ isnull(convert(varchar(max),col7.Text),'')
[Code] .....
I am storing formatted data (including spaces and line breaks) in a single field in a table.
When I run a report on that field, the preview of the report is automatically removing all the extra spaces and line breaks, making the report unreadable.
When exporting the report to PDF or printing it, it shows the line breaks and spaces as expected.
Does anyone know how to make the report preview show the spaces and line breaks?