How To Define Programatically The Width For Columns??
Nov 21, 2006
Hi everyone,
Either Sql2k or Sql25k are targeted if you answer to this thread. When we have source/destination files we usually wish to define its properties, the width for each field and so on. My question is related with this, how do such by-hand tasks via scripting inside the own ETL? Tedious tasks are if there are more than 20 columns.
Is it possible? I think so regarding 2005 but about 2000 I haven't idea at all how to begin. Issue comes when one programmer must alter lots of columns due to for example, a new file format from mainframe is released.
is there a way to define a row delimiter to fixed width files? such as this one:
1122333
4455666
7788999
in this file i have 3 columns that are fixed (col1.width = 2, col2.width = 2, col3.width = 3) but have {CR}-{LF} as the row delimiter. when i try to create a flat file connection to these kind of files- he always reads the CR-LF as part of the file text, and there is no place where i can define the row delimiter if i have.
I am trying to create a program that transfers tables to flat files. At this point in time, I have suceeded in created one that creates delimited files.
However, I am now trying to create fixed-width files as you can do with the SSIS designer, but programatically.
Is there a way to programatically determine the width of a column from the source table? I can not seem to find any kind of function or member that stores this information or allows me to retrieve it.
I know what I need to change in order to set a width for a column, but I just don't know how to find the width without just asking the user to provide one.
The SQL below is the start of a massive Stored Procedure for Comparing two Datasets, which will be produced onto a report.
I was wondering if I could call an SQLserver Procedure that would tell me the names of all the Columns that are produced by this SP, so I can print them out and more easily code the report?
SELECT stk.StockNumber, stk.DefaultImageName, tVTP.Make as PolMake, stkV.VehicleMake, tVTP.Model as PolModel, stkV.VehicleModel, tVTP.ModelNo as PolModelNo, stkV.VehicleModelNo, tVTP.EngineNumber as PolEngineNumber, Stk.EngineNumber, tVTP.comHeadLightNumber as PolHeadLightNumber, Stk.comHeadLightNumber, tVTP.comTailLightNumber as PolTailLightNumber, Stk.comTailLightNumber , tVTP.comBumperLightNumber as PolBumperLightNumber, Stk.comBumperLightNumber, tVTP.comCornerLightNumber as PolCornerLightNumber, Stk.comCornerLightNumber, tVTP.Chassis as PolChassis, --Drive Train tVD.DriveTrainDescription as POlDriveTrainDescription, StktVD.DriveTrainDescription, --Body Type tVBT.BodyTypeDescription as PolBodyDescription , StkVBT.BodyTypeDescription
FROM tblStock Stk --JOINS FOR THE Policy Definition INNER JOIN tblVehicles V ON V.VehicleID = Stk.VehicleID INNER JOIN tblVehicleType_Policy tVtP ON tVTP.VehicleMaster = V.VehicleMaster AND tVTP.Make = V.VehicleMake AND tVTP.Model = V.VehicleModel AND tVTP.ModelNo = V.VehicleModelNo INNER JOIN tblVehicleDriveTrain tVD ON tVD.vehicleDrivetrainID = tVTP.DrivetrainID INNER JOIN tblvehicleBodyType tVBT ON tVBT.VehicleBodyTypeID = tVTP.BodyTypeID
--JOINS FOR the Stock Definition INNER JOIN tblVehicles STkV ON StkV.VehicleID = Stk.VehicleID INNER JOIN tblvehicleBodyType StkVBT ON StkVBT.VehicleBodyTypeID = Stk.BodyTypeID INNER JOIN tblVehicleDriveTrain StktVD ON StktVD.vehicleDrivetrainID = stk.DrivetrainID
Hi, I've written a SELECT statement that returns columns dependant on a bitwise parameter @Populate.SELECTCASE WHEN (1 & @Populate) = 1 THEN [Column1] ELSE Null END AS [Column1],CASE WHEN (2 & @Populate) = 2 THEN [Column2] ELSE Null END AS [Column2],CASE WHEN (4 & @Populate) = 4 THEN [Column3] ELSE Null END AS [Column3],CASE WHEN (8 & @Populate) = 8 THEN [Column4] ELSE Null END AS [Column4],etcIs this the most efficient way to acheive this since I am only seeing a small performance gain. It still returns all columns but depending on @Populate will leave some columns with Null values.Any help much appreciated, thanks.
Just found that we are not able to define multiple key columns for a mining structure in SQL Server 2005 Data Mining engine, just wondering is there other way to define multiple key columns for a mining structure there? As in many cases, the table we are mining are with composite key consisting of different foriengn keys, e.g. A fact table are with transaction information and other foreign keys. If I am not able to define these composite key here for this fact table, I will have to have a named calculation in data source view to have a key column which is based on these original composite keys? Is this a better way to solve this problem or there is any other alternatives to figure it out?
Hope my question is clear for your help and I am looking forward to heaing from you shortly for your kind advices and help and thanks a lot in advance.
I've two tables A, B. In A table, I need to define the primary key with combination of 2 columns and this Primary Key will be a foreign key in table B. Based on these PK and FK I'll be writing a join to get the second column in table B.
I have set up a script task in one of my packages that I have set up to modify another package right before running it. This package is nothing more than a data flow task that transfers rows via an sql command from one table into another. The strange thing is I have gotten it to work with some tables but not with others. T
he script bombs out in the loop where i map all of the columns found below, where i use MapInputColumn with the error HRESULT: 0xC0010009 On Microsoft.SqlServer.Dts.Pipeline.Wrapper.IDTSExternalMetadataColumnCollection90.get_Item(Object Index)
The thing is this happens after looping roughly 55 times but there are still about 100 columns that it needs to loop through still.
Code Block
Dim input As IDTSInput90 = data_destination.InputCollection(0) Dim virtual_input As IDTSVirtualInput90 = input.GetVirtualInput Dim input_column As IDTSInputColumn90 Dim virtual_column As IDTSVirtualInputColumn90
' Iterate through the virtual input column collection and map field names For Each virtual_column In virtual_input.VirtualInputColumnCollection input_column = inst_data_destination.SetUsageType(input.ID, virtual_input, virtual_column.LineageID, DTSUsageType.UT_READONLY)
inst_data_destination.MapInputColumn(input.ID, input_column.ID, input.ExternalMetadataColumnCollection.FindObjectByID(virtual_column.Name).ID) Next
Just for kicks i removed the mapping portion of the code and left in the SetUsageType to see if it would update the available input columns in the destination. The script will then finish successfully but still only the 55 or so fields out of 155 are available in the input. So i then stepped through the script with the mapping portion still disabled and after it loops successfully, i call reinitialize meta data and it produces an error in the input_column variable: HRESULT: 0xC0047041.
I find it odd that this still reports to me that the script finished successfully and I also find it odd that this works fine on two other tables I've tested but not this one. Any insight would be greatly appreciated.
I found the following paragraph while searching on here:
************************************************************************ You could easily set up a parameter for each column and then display that column conditionally based on the parameter.
For instance, if you have a column that displays First Name, you could have a parameter called DisplayFirstName. Then in design view you'd select the whole FirstName column and in the Visibility-Hidden property set it to :
This could easily become a big, unwieldy report, though, if you have a great number of dynamic columns. Also, The width of the report is set at design time, and it includes the width of all your columns, not just the visible ones. This could cause you some pagination problems. ************************************************************************
That is exactly my problem. I have lots of dynamic columns which causes the width of the report to be wide. Thus even though at run time the report only shows columns within a page, the report itself consists of a lot of white spaces after the selected columns. Does anyone know a solution to this? If not, I guess creating the rdl with code manually is the only way? Please advise. Thanks in advance.
I have a subreport on my main report, and the subreport contains a matrix. When I run the report, the subreport seems to expand beyond the width of the main report. The matrix itself does not expand beyond the width of the body of the main report, but for some reason it seems that the subreport does. The subreport does not contain any headers that might be messing up the width. If I cut and paste the matrix from the subreport directly onto the main report, and remove the subreport, it prints fine. But as soon as I include the subreport on the main report it prints with blank pages because the subreport expands beyond the width of the body of the main report. I have checked the width and margins on the subreport and compared to the width of the main report and all looks good. Can anyone help?
I'm trying to read in a flat file (which, admittedly, has one very wide column), and it keeps breaking because of truncation when it tries to read in the file.
I've a report projects where each report has a number of columns and the spec is to have all the columns print-out on the same page. Is there a setting that will auto-scale these columns to fit to the page or will I have to edit the font size and widths manually on each report to fit to the page?
There is a table t1 with two fields, such as, ID NAME １ Ｔｏｋｙｏ 2 Xian 3 America
For there are full-width and half-width strings in the values of the two fields, I can not select and get the right records. So I want to transform the two fields ID and NAME, I fail to find the function in the SQL Server 2005. Please give me some advice.
I have to use a CTE based on conditions. But I am not able to write a common CTE and access it on different conditions. Now I am duplicating the CTE definition inside each if condition. But I expects a solution like bellow to reduce code duplication.
For example
---- I need a common definition of CTE like this. with CustomGroup( id) as ( Select id from Groups g Inner join GroupStatus s on s.Id = g.Id Where s.Status = €˜Active€™ )
IF @DisplyStatus=€?UserDetail€? begin <!--[if !supportLists]-->- <!--[endif]-->at present CTE is defined here. select u.* from users u inner join userGroups ug on ug.UserId = u.Id inner join CustomGroup cg on cg.Id =ug.GroupId -- Accessing the CTE end else if @DisplyStatus=€?UserSimple€? begin <!--[if !supportLists]-->- <!--[endif]-->at present CTE is defined here. select u.FirstName,u.LastName,u.DOB from users u inner join userGroups ug on ug.UserId = u.Id inner join CustomGroup cg on cg.Id =ug.GroupId -- Accessing the CTE end else if @DisplyStatus=€?UserWithAddress€? begin
end
Please suggest possibilities, and your suggestions.
Hi, here am i back ..not sure it's possible to solve it in one query ...there are four tables:1) headquarters---------------hqID (primary key)hqname2) department---------------depID (primary key)depnamehqID3) reports----------repID (p. key)depIDuserID4) users--------userID (p.key)usernameI want to get in one query those four fields:hqID, hqname, depID, depname + varchar(number of distinct users that has made a report foreach department) . This last field is a concatenation.The complexity resides in the fact there is need for a COUNT among other fields ...E.g.: table reports may look like this: repID depID userID 1 1 1 2 1 1 3 1 3 4 3 6 5 4 8This gives:for dep 1: 2 distinct usersfor dep 2: 0for dep 3: 1 distinct userfor dep 4: 1 distinct userThanks for helpTartuffe
Hi,i have a dropdownlist connected to a sqldatasource like this: <asp:DropDownList ID="DropDownList1" runat="server" DataSourceID="SqlDataSource1" DataTextField="Name" DataValueField="Name"> </asp:DropDownList>What i want is to put a particular value on the top of the dd. I can't use ORDER BY.e.g. the table which feeds the dd contains this:RDAhow to put value D on top of the dd?ThanksTartuffe
I get the word TempleMode marked in Yellow. it's to clear all recoreds from my Pivot tables linked to SQL Database.
Sub ClearAllSheets() On Error Resume Next 'Dim pt As PivotTable Dim Sh As Worksheet Dim refresh As Boolean Dim tempMode As Boolean Dim startTime As Date
Is there a way to define a key that puts the text 'SELECT * FROM 'into the Query Analyzer window?I must type this about 50 times a day but cannot see a simple way ofdefining a key to write it for me..(tools/customize) seems to execute everything you put in there ratherthan leave it on the screen for me to add table names etc to.thanks for your time...
I am having a question on defining the value of Periodicity_hint. e.g, I wanna predict the monthly sales amont of a product, and assume the data follows monthly patterns, then in this case, what value should we set for the periodicity_hint parametre?
Thanks a lot for your kind attention and I am looking forward to hearing from you shortly.
Case: Exporting Report to PDF/Printing/TIFF Report: Contains 1 table with 19 Columns. 1 column is static, the other 18 are visible at the users descretion. Report when printed/exported to pdf spans 2 pages naturally, 16 on the first page, 3 on the second, and the column widths have been adjusted to provide a perfect page span .
User A elects to hide two of the columns, and show the rest. The report complies and the viewable version is perfect, the excel export is perfect.. the PDF export on the first page causes every fith column, starting with the last column that was hidden to be expanded to take up additional width. On the spanned page, it renders the first column on that page correctly, then there is a white space gap equal to the width of the hidden columns and then the rest of the cells show with the last column expanded to take up the same width that the original 2 columns were going to take up, plus its width.
We have tried several different settings to see if it helps this issue or makes it worse. So far cangrow/canshrink/keep together have made no impact. It is not possible to increase the page size due to limited page size selection availablility for the client. There are far too many combinations of what the user can elect to show or hide to put together different tables to show and hide on the same report to remove this effect.
Any help or suggestion on this issue would be appreciated
Hi I have a DropDownlist (Drop1) and a GridView,the GridView is bount to an SqlDataSource1 that has 2 Select parameters CatId and SourceId The dropdownlist has a selectedvalue of the following format 15-10(2 numbers seperated by -).I want to set CatId to 15 and SourceId to 10 <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:Art %>" SelectCommand="Select * from Option Where SourceId=@SourceId And CatId=@CatId"> <SelectParameters> <asp:ControlParameter ControlID="Drop1" Name="SourceId" /> <asp:ControlParameter ControlID="Drop1" Name="CatId" /> </SelectParameters> </asp:SqlDataSource> Can anyone help me to define the parameters? thanks
I've always created database tables with one Primary Key that increments by one for each new record. I'm working on a database that was built by someone else now that has a lot of defined foreign key relationships (under Tables > specific table > Keys). How helpful is it to define these Keys? I assume it helps make the database more efficient but is it necessary if you're working with small databases?
Why is it not possible to define more than one relationship per table?
i have a primary table that i would like to cascade deletes to 2 other foreign tables in 2 separate relationships. why can't i do this and what are my alternatives?
Hi, I am trying to connect to my local SQL Server through a webform ( on VS.NET ). My web.config shows something like this - sqlConnectionString="data source=IP address;Trusted_Connection=yes"and the tutorial I am following says something this - SqlConnection myConnection = new SqlConnection( "server=(local)\NetSDK;database=pubs;Integrated Security=SSPI");If I just replace the NetSDK either with my IP address or the servername (my machine name), I get a SQLException that server doesn't exist or access denied. I have specified "windows authentication". How do I correctly define my connection string ? Please guide me.Thanks,sbs.
