MS SQL Server :: Limitations In Term Of Number Of Tasks And Number Of Columns

Syntax Error Near '&&<term&&>' In The Full-text Search Condition '&&<term&&> &&<term&&> &&<term&&>'.
Hello,

Although this SP intends to sorround a search text in double quotes, it seems that when called from Management Studio it throws a Syntax Error even before entering the SP.

createproc fts(@t nvarchar(1000)=null) as begin

select @t = '"' + @t + '"'

select @t

select * from dbo.products where CONTAINS(name, @t)

end

GO

exec fts @t = 'my product name'

GO

Msg 7630, Level 15, State 3, Procedure fts, Line 4

Syntax error near 'product' in the full-text search condition 'my product name'.

-------------------------

If I pass the string in double quotes I get a different error:

exec fts @t = '"my product name"'

Go

Msg 7630, Level 15, State 3, Procedure fts, Line 4

Syntax error near 'my' in the full-text search condition '""my product name""'.

Now, if I remove the quotes again and make the original call:

exec fts @t = 'my product name'

GO

-- Then it works fine.

Doing a :

dbcc freeproccache

GO

Shows the same behavior as outlined initially.

View Replies !

SELECT Query - Different Columns/Number Of Columns In Condition
I am working on a Statistical Reporting system where:

Data Repository: SQL Server 2005
Business Logic Tier: Views, User Defined Functions, Stored Procedures
Data Access Tier: Stored Procedures
Presentation Tier: Reporting ServicesThe end user will be able to slice & dice the data for the report by

different organizational hierarchies
different number of layers within a hierarchy
select a organization or select All of the organizations with the organizational hierarchy
combinations of selection criteria, where this selection criteria is independent of each other, and also differeBelow is an example of 2 Organizational Hierarchies:
Hierarchy 1

Country -> Work Group -> Project Team (Project Team within Work Group within Country)
Hierarchy 2

Client -> Contract -> Project (Project within Contract within Client)Based on 2 different Hierarchies from above - here are a couple of use cases:

Country = "USA", Work Group = "Network Infrastructure", Project Team = all teams
Country = "USA", Work Group = all work groups

Client = "Client A", Contract = "2007-2008 Maint", Project = "Accounts Payable Maintenance"
Client = "Client A", Contract = "2007-2008 Maint", Project = all
Client = "Client A", Contract = allI am totally stuck on:

How to implement the data interface (Stored Procs) to the Reports
Implement the business logic to handle the different hierarchies & different number of levelsI did get help earlier in this forum for how to handle a parameter having a specific value or NULL value (to select "all")
(WorkGroup = @argWorkGroup OR @argWorkGrop is NULL)

Any Ideas? Should I be doing this in SQL Statements or should I be looking to use Analysis Services.

Thanks for all your help!

View Replies !

Max. Number Of Columns
does anyone know what the maximum number of columns is that an SQL table can contain ?

i seem to remember that in the past it was something like 200 columns max, but i don't know whether that has changed with newer versions of SQL Server

looking at the spec for SQL Server 2000 i see a maximum of 1024 columns per base table - am i right in interpreting this to mean the maximum is now 1024 columns or is a base table different from an SQL table ?

View Replies !

Number Of Columns And Performance
Hi,I'm designing a new database and I have a doubt in which surely youcan help me.I'm storing in this database historical data of some measurements andthe system in constantly growing, new measurements are added everyday.So, I have to set some extra columns in advance, so space is availablewhenever is needed and the client doesn't have to modify the structurein SQL server.The question is: the more columns I add "just in case", the slower theSQL reads the table?Of course the "empty" columns are not included in any query until theyhave some valid data inside.Will I have better performance if I configure only the columns beingused at the moment, without any empty columns?Thanks in advance.Ignacio

View Replies !

Number Of Columns In Table
How can I determine the number of columns in a table?

View Replies !

Dynamic Number Of Columns
when using sorred procedure to create a temporary table is it possable to base the number of columns in that table on another variable?

View Replies !

ADO 2.8 Is There Any Limitation On Number Of Columns?

Hi,

I'm using ADO 2.8 in a vb.net code. The .Net framework version is 1.1 and windows server 2003.

I'm firing a query that result in 256 columns and few hundred rows. Here is the snapshot of the code

adoRs = New ADODB.Recordset

With adoRs

.CursorLocation = CursorLocationEnum.adUseClient ' adUseClient

.ActiveConnection = adoCn

.CursorType = CursorTypeEnum.adOpenStatic ' adOpenStatic

.LockType = LockTypeEnum.adLockBatchOptimistic ' adLockBatchOptimistic

.Open(strSQL)

End With

The returned record set is empty with all the the 256 columns name. Could anyone shed light why it is returning empty recordset. Is there any limitation on number of columns that a recordset can hold.

Thanks in advance.

With regards
Ganesh

View Replies !

The Number Of Columns Is Incorrect
While Running a SSIS package after migrating it from DTS to SSIS , in MS SQL Server 2005 ,
it gives error while execution :

DTS_DTSTASK_DATAPUMPTASK_2
The number of columns is incorrect.verify the column metadata is valid.
"OLEDB Destination "(22) Failed the pre execution phase and returned error code 0xC0202025
Thanks for the response .....

View Replies !

How To Change A Decimal Number To Percent Format Number?
in my sql, i want to change a decimal number to percent format number, just so it is convenient for users. for example there is a decimal number 0.98, i want to change it to 98%, how can i complete it?

thks

View Replies !

Number Of Columns In Table And Performance
Hi,
I have a denormalized table (done so with reason) with around 40 columns. I would never have to retrieve data for all of those columns together.
I haven't done any performance measurements yet but just wondering if anyone has ready answer to this: Will there be a performance degradation if I retrieve data from a table with many columns, even if not all columns are referred in the query? (for making it simple, lets assume that all or varchar type of columns, I just want to find out if performance degrades if there are too many columns in table)

Thanks in advance,
Sandeep

View Replies !

Number Manipulation In Non-identity Columns
hi.i am using ms sql server 2000. can somebody tell me what the code would be to remove all the values in a given column and replace them with the associated number of the row with each execution. so, if i have a column:nums|1||2||3||4|and somebody deletes record |2|i would like the nums colum to update to|1||2||3|not:|1||3||4|it seems simple but i am having a hard time with this. how is it done?thanks.

View Replies !

Selecting One Greatest Number From Two Columns?
I have table1 and table2.In table1 I have a column of numbers, numbers1.In table2 I have a column of numbers, numbers2.I'd like to select the highest number represented in either column.Example:table1:column1--------------345565643656555676table2:column2--------------3456564556456456456456The number I would want would be 56456 since it's the largest numberout of all combined.How can I get that number with one select statement?--[ Sugapablo ][ http://www.sugapablo.com <--music ][ http://www.sugapablo.net <--personal ][ Join Bytes! <--jabber IM ]

View Replies !

MDX: Limit The Number Of Columns Returned
Dear all,

I have a cube with dimension
Time Periods -> YearTotal -> Q1 -> Apr, May, Jun
Q2 -> Jul, Aug, Sep
....

I have tried to using this [Time Periods].[YearTotal].Children to return the month column. Instead of returning just the month column, I am getting YearTotal, Quarter and Month columns.

Is there anyway to reduce the return to just the month column?

Thank you.

regards

View Replies !

Max Number Of Columns In SQlServer Table
We are looking at developing a new app with a SQL Server db. As such, we're all newbies.

What is the maximum number of columns I can put in a SQL Server table?

Thanks in advance.

Bob Anderson

View Replies !

Find The Smallest Number In Two Columns Was: How Do I Do This ?
Hi,
I have a sql query which gives me the result set with lots of columns and rows.

a b c allocated unallocated
- ------ 75458702 0484095809
------- 534534 8743857
------- 953459034 90584395

i have to find of which is the smallest number in both allocated and unallocated columns -
here in this case
it would be 534534.
how do i do this ?

Thanks

View Replies !

Variable Number Of Input Columns
We are trying to use the Import/export wizard to load a text file to a SQL Server 2005 database. The input file has a variable number of columns per row. For example, the first row has 3 columns, the second has 7, the third has 3, etc. The number of columns varies from 2 to 9 in the input file. The columns are separated by an uptick (`) and the rows are terminated by {CR}{LF}. We are using code page 1252. On processing, the wizard reads the first row (with 3 columns) ok, but then assumes all the other rows have 3 columns and parses the rows accordingly, ignoring the field and row terminators.

The process worked fine with SQL Server 2000. Is there some setting that we are missing, or some configuration on the database that we should be checking?

Thank You

View Replies !

Set Number Of Columns In Matrix Table
I am creating a report that uses the Matrix control. I need to display a fixed number of columns (5). In my query, I am returning the top 5 rows of data. However, in some cases there are less than 5 rows of data returned from the dataset. Is there a way to force the number of columns displayed in the matrix control and to populate with some text (such as "n/a") if no data is available?

Thanks!

ads

View Replies !

How To Limit The Number Of Columns In The Matrix ?
HI all !

I am having a bit of a problem trying to limit a number of columns in a matrix appearing on a page.

At the moment, I have a dataset that lists the month and the mail packages that were sent during the month
The matrix works great HOWEVER, if there were more than 8 months in the matrix columns, it does not break and would make the page look like a huge landscape page.

I am trying to limit the number of columns appearing (this is the months column) on the matrix so that the pages stay in a potrait position. IE: every 8 columns appear on one page. Is there an option or an expression I could use in the Matrix ?
Thanks!

BErnard Ong

View Replies !

How To UPDATE A Variable Number Of Columns

Hi, I need to update a number of columns in a number of tables - I just don't know how many. In this case, I am updating all varchar fields and nvarchar fields to be converted to lower case. The problem is that the table structure is amended over time as columns are added programmatically, so I do not know which tables have which columns and if so which of them are varchars.

I can get a table of which fields I need to update using:

SELECT TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION, DATA_TYPE
INTO tblTempLCase
FROM information_schema.columns
WHERE DATA_TYPE LIKE '%varchar'

and I can do the update with UPDATE tblxxx SET column = LOWER(column)

But what I don't know is how to step through my temporary table and do the updates. I can do it in ASP.NET, but that involves pushing commands and data between ASP and SQL, and will be too slow. How do I do it in SQL?

Thanks,

Dave Stephens

View Replies !

How To UPDATE A Variable Number Of Columns
Hi,

I need to update a number of columns within a number of tables - I just don't know how many. In this case, I want to convert all varchar and nvarchar columns to lower-case versions of themselves. The problem is that the table structure is changed programatically, and so at any point in time I cannot be certain what fields are in which table, and what data type they are.

I know that I can get a lit of columns using:

SELECT TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION, DATA_TYPE
INTO tblTempLCase
FROM information_schema.columns
WHERE DATA_TYPE LIKE '%varchar'

and do the update using:

UPDATE tblABCDE SET column = LOWER(column).

In ASP.NET I can pull in this temporary table using a SQL Data Adapter, and then step through the records to formulate the UPDATE statements and execute them all. However, I hope that this is possible in SQL too, so that I do not have to keep firing data/commands between ASP and SQL, as it should be quicker, and is also neater.

If so, how do you do it?

Thanks,

Dave Stephens

View Replies !

Limits On The Number Of Columns In A Table?
I have a database hosted by GoDaddy. Recently they made some changes to the interface and upgraded to SQL Server 2008. One or the other has made it impossible to access my data in one table.

The table is quite large in terms of the numbers of elements. Each row describes a dog and all the elements are components of the description. There are (I would guess) more than 50 elements all together.

When I try to search the database, the query form goes beyond the top and bottom of the page. I can scroll the database but the search tool (which lies atop the data) does not scroll. The result is that I can't activate the search.

I've tried about 10 machines. All with IE6 display this fault. Machines with IE7 do not. I've tried various screen resolutions on the machines with IE6. That doesn't help.

I've checked other tables in the database. No problem.

In short, there's nothing I can do. I can't edit my data and GoDaddy says, "Tough."

Is there a limit on the number of columns (elements) in a table in SQL Server 2008?

Eric

View Replies !

Maximum Number Of Columns In An Index
I am upgrading from Access, where you can only have 10 fields in a primary key or unique index. Is this also the limit in SQL Server? If not, what is the limit?

Thanks for any help on this.

View Replies !

FnParseString And Unknown Number Of Columns
Keshka writes "I'm using function fnParseString form http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76033 in some of my sp.

it's very helpfull, but my question is if there is a way to split variable into columns if I don't know how many columns I'll have? It could be 1 or 2 or 3 and etc.

Thanks"

View Replies !

String Match Number Of Different Columns
Hi,

In short:
I want my following problem to work with a LIKE instead of exact match and if possible be faster. (currently 4s)

Problem:
I got a set of rows with varchar(50), spread out over multiple tables.
All those tables relate to a central Colour table.
For each of the columns, I want to match the values with a set of strings I insert and then return a set of Colour.Id

E.g: input: 'BLACK', 'MERCEDES', '1984'
Would return colour ids "025864", 45987632", "65489" and "63249"
Because they have a colour name containing 'BLACK' or are on a car from 'MERCEDES' or are used in '1984'.

Current Situation:
I) Create a table containing all possible values
CREATE TABLE dbo.CommonSearch(
id int IDENTITY (1, 1) NOT NULL,
clr_id int,
keyWord varchar(40),
fieldType varchar(25)
And fill it with all the values (671694 rows)
)
II) Stored Procedure to cut a string up into a table:
CREATE FUNCTION dbo.SplitString
(
@param varchar(50),
@splitChar char = ''
)
RETURNS
@T TABLE (keyWord varchar(50))
AS
BEGIN
WHILE LEN( @param ) > 0 BEGIN
declare @val varchar(50)
IF CHARINDEX( @splitChar, @param ) > 0
SELECT @val = LEFT( @param, CHARINDEX( @splitChar, @param ) - 1 ) ,
@param = RIGHT( @param, LEN( @param ) - CHARINDEX( @splitChar, @param ) )
ELSE
SELECT @val = @param, @param = SPACE(0)
INSERT INTO @T values (@val)
END
RETURN
END
III)Stored Procedure to query the first table with the second one
CREATE PROCEDURE [dbo].[GetCommonSearchResultForTabDelimitedStrings]
@keyWords varchar(255) = ''
AS
BEGIN
SET NOCOUNT ON;
select clr_id, keyWord, fieldType
from dbo.commonSearch
where keyWord in (select * from splitString(@keyWords, ''))
END

So, how can I use a LIKE statement in the IN statement of the last query.
Furthermore, I was wondering if this is the best sollution to go for.
Are there any better methods? Got any tuning tips to squeeze out an extra second?

View Replies !

NTEXT Vs NVARCHAR For Large Number Of Columns
Hi all,I need to store data into about 104 columns. This is problematic with MSSQL, since it doesn't support rows over 8kb in total size.Most of the columns are of type NVARCHAR(255), which means we can't havemore than 8092/(255*2) = 15 columns of this type.With a row length of more than 8kb, SQL gives a warning that any rows overthat amount will be truncated.So far I'm seeing two possible solutions to this problem:1. Split data into multiple tables with the same ID column accross alltables, and then join them on SELECT statements.2. Use NTEXT instead of NVARCHAR. NTEXT's length is 16 bytes because itcontains a pointer to the actual value stored somewhere else. However, NTEXTdoesn't support regular indexing, only through a Full-Text Index catalog. Inthis case I'll need to user "WHERE CONTAINS(columnName, 'sometext')" toperform searches, which is bearable.I'm inclined toward #2. However I haven't used Full-Text indices before anddon't know their limitations. Will I run into problems with NTEXT? Is therea better solution?Thanks.-Oleg.

View Replies !

Variable Number Of Columns In A Temporary Table
How do I create a temporary table in a stored procedure with differeent number of columns?
That is: sometimes ten columns, sometimes 24 etc.

View Replies !

How To Present An Unknown Number Of Columns And Their Names
I've got a database with an unknown number of columns. Hence, the column names are also unknown. What's the easiest SQL to present the values in each column and the column headings?

View Replies !

Create Table With Veriable Number Of Columns

Hi everyone,

I need to create temporary table in one of the SP.The problem is that number of columns in table will vary depanding on input in SP.

How can i create table with variable number of columns?

Thanks,

Alex

View Replies !

Maximum Number Of Destination Columns In OLE DB Command??
I'm using 2 OLE DB Commands; 1 to perform an insert the other an update. I have found that on the column mapping tab, I only have 10 parameters available to map to. The issue is I need 40 parameters. Am I doing something wrong? Is there a setting I am missing? Is there another way to do this? Am I out of luck .
Here is the sql query I am using, so you can see that I have the correct number of parameters listed:

Insert into Reqs_Data (G_COLLECTDATE, PROGRAM, PRODUCT_ID, TOTAL, ADDED, CHANGED, DELETED, NOT_ALLOCATED, NEWREQS, MODIFIED, LEGACY, UNCATEGORIZED, NOT_TRACED_DOWN, NOT_TRACED_UP, PASSED, PARTIALLY_PASSED, WAIVED, FAILED, NOT_VERIFIED, PLANNED_ADDED, RQTS_TOTAL_TBD_COUNT, RQTS_TOTAL_MODS_IN_MONTH, BUILD_ID, RTM_PRODUCT_ID, TRACED_UP, TRACED_DOWN, ALLOCATED, LASTMONTHTOTAL, CALC_ADD, CALC_DELETE, IS_TRACED_DOWN, IS_TRACED_UP, LEVEL3, LEVEL2, LEVEL1, LEVEL0, IPT1, IPT2, IPT3, IPT4 ) Values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)

Update Reqs_Data SET TOTAL = ?, ADDED = ?, CHANGED = ?, DELETED = ?, NOT_ALLOCATED = ?, NEWREQS = ?, MODIFIED = ?, LEGACY = ?, UNCATEGORIZED = ?, NOT_TRACED_DOWN = ?, NOT_TRACED_UP = ?, PASSED = ?, PARTIALLY_PASSED = ?, WAIVED = ?, FAILED = ?, NOT_VERIFIED = ?, PLANNED_ADDED = ?, RQTS_TOTAL_TBD_COUNT = ?, RQTS_TOTAL_MODS_IN_MONTH = ?, BUILD_ID = ?, RTM_PRODUCT_ID = ?, TRACED_UP = ?, TRACED_DOWN = ?, ALLOCATED = ?, LASTMONTHTOTAL = ?, CALC_ADD = ?, CALC_DELETE = ?, IS_TRACED_DOWN = ?, IS_TRACED_UP = ?, LEVEL3 = ?, LEVEL2 = ?, LEVEL1 = ?, LEVEL0 = ?, IPT1 = ?, IPT2 = ?, IPT3 = ?, IPT4 = ?
WHERE G_Collectdate = ? AND Program = ? AND PRODUCT_ID = ?

View Replies !

Importing Text Files With Different Number Of Columns
Hi!

I have a bit of a problem with importing (DTS Package) from a text file into an existing DB.

The file has no column names and every file only contain 1 row. This row varies in number of columns.

My first thought was to create a table with the maximum numbers oc columns that can appear in the text file.
An example:

Table with 4 columns; Col001, Col002, Col003 and Col004

The first text file:
123,Peter,Ocean Street,NY

The second text file:
356,John,Park Avenue

My aim is that the second file should append to the table, leaving Col004 as (NULL)

Any ideas how to get this to work?

Expekt

View Replies !

Flat File And Uneven Number Of Columns
Please leave feedback for Microsoft regarding this problem at https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=126493

Ok I'm sure it just me, SSIS has been great so far....but how can you import a straight CSV file with and uneven column count.

For example: (assume CR LF row delimiter)

The,Quick,Brown,Fox,Jumps
Hello,World
This,is,a,test

"Normally" I'd expect this

| Col1 |
| Col2 |
| Col3 |
| Col4 |
| Col5 |

The
Quick
Brown
Fox
Jumps

Hello
World
NULL
NULL
NULL

This
is
a
test
NULL

Ok but what we get is the row delimiter is ignored in preference for the column delimiter and the row delimiter gets sucked into the column and the next row starts to get layed down.

So we get

| Col1 |
| Col2 |
| Col3 |
| Col4 |
| Col5 |

The
Quick
Brown
Fox
Jumps

Hello
World{CR}{LF}This
is
a
test

I'm I not seeing a tick box somewhere that says "over here if you want to terminate a row on the row delimiter even if all columns aren't full and we'll pad NULLs in rest of the row columns which you can fix in the flow transformations"

I'm sure it's there.....help!

(By the way SSIS team, great job on the package love using it)

View Replies !

Displaying Only A Fixed Number Of Columns In Matrix
Is it possible to display only a certain number of columns in a matrix, say the first 6 and then hide the rest? That is, does the matrix allow to somehow control how many columns can be displayed from a column group and hide the remaining columns (I need this to limit the number of columns a user is able to see so that the matrix width does not get infinitely long).

In other words.....

I need to display the subtotals for all dynamically generated columns but display only first 6 columns. This way I can avoid having to display 50 columns and not have user scroll to so far right and keep the page width within reasonable limits. Hope I have made it clear.

Thanks.

View Replies !

ADO Limitation To Number Of Excel 2007 Columns??
Hello all,

The following VBA code should theoretically return all the column
names from an Excel 2007 sheet. What I find is that it only returns
the first 255 even though there are many more in the spreadsheet (a
new feature of excel 2007).

Can anyone tell me how to resolve this?

Thanks,

Phil

Private Sub getXL2007ColumnNames()

Dim count As Integer
Dim fName As String
Dim sheetname As String

Dim cnSim As New ADODB.Connection
Dim rsSchema As New ADODB.Recordset

fName = "C:demo datamyfile.xlsx"
sheetname = "mysheet$"

Set cnSim = New ADODB.Connection

'cnSim.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data
Source=" & fName & ";Extended Properties=""Excel 12.0;HDR=YES"";"
cnSim.ConnectionString = "Driver={Microsoft Excel Driver (*.xls,
*.xlsx, *.xlsm, *.xlsb)};DBQ=" & fName
cnSim.Open

Dim aRestrictions As Variant
aRestrictions = Array(Empty, Empty, sheetname, Empty)
Set rsSchema = cnSim.OpenSchema(adSchemaColumns, aRestrictions)

rsSchema.MoveFirst
Do Until rsSchema.EOF = True
count = count + 1
Debug.Print (rsSchema!Column_Name)
rsSchema.MoveNext
Loop

MsgBox "Fields = " & count

rsSchema.Close
Set rsSchema = Nothing
cnSim.Close
Set cnSim = Nothing

End Sub

View Replies !

SqlDataSource With Different Number Of Columns And Insert Parameters And FormView
Dear All,
i have a SqlDataSource with a simple select command(e.g. "select a,b,c from foo"). The insert command is a stored procedure and takes less parameters than there are columns in select statement (e.g. "insertFoo(a char(10))").
When used in combination with form view, i get "Procedure or function insertFoo has too many arguments specified" error.
It seems that form view always posts all columns as parameter collection (breakpoint in formview_inserting event shows this) to insert command.
Am I doing something wrong or is this by design? Is the only solution to manualy tweak parameters in formview_inserting event?
TIA
Jernej

View Replies !

Calrification On Number Of Columns Fetched To Server Memory
Hello,I need a clarification on the mechanism of sql server fetches recordsto memory (buffer)Example: A Table ( Table1 ) having 10 Columns(column1,column2,column3,column4 etc)When SELECT column1,column2 FROM Table1 is executedWhether all columns are trasnferred to the server memory from the diskOR only column1 and column2 are read from disk to memory .My assumption is that, entire page (8K) containg the rows with all thecolunmns are transferred to the memory and from the memory the selectcolumns are displayed .Please confirmThanksM A Srinivas

View Replies !

Database Table Design For Huge Number Of Columns
Hi

I have a table (Sql server 2000) which has 14 cost columns for each record, and now due to a new requirement, I have 2 taxes which needs to be applied on two more fields called Share1 and share 2
e.g
Sales tax = 10%
Use Tax = 10%
Share1 = 60%
Share2 = 40%

So Sales tax Amt (A) = Cost1 * Share1 * Sales Tax
So Use tax Amt (B) = cost1 * share2 * Use tax

same calculation for all the costs and then total cost with Sales tax = Cost 1 + A , Cost 2 + A and so on..
and total cost with Use tax = Cost1 +B, Cost 2 +B etc.

So there are around 14 new fields required to save Sales Tax amt for each cost, another 14 new fields to store Cost with Sales Tax, Cost with Use tax. So that increases the table size.
Some of these fields might be used for making reports.

I was wondering which is a better approach out of the below 3:
1) To calculate these fields dynamically while displaying them on the User interface and not save in DB (while making reports, again calculate these fields dynamically and show), or
2) Add new formula field columns in database table to save each field, which would make the table size bigger, but reporting becomes easier.
3) Add only those columns in database on which reports needs to be made, calculate rest of the fields dynamically on screen.

Your help is greatly appreciated.
Thanks

View Replies !

Database Table Design For Huge Number Of Columns
Hi

I have a table (Sql server 2000) which has 14 cost columns for each record, and now due to a new requirement, I have 2 taxes which needs to be applied on two more fields called Share1 and share 2
e.g
Sales tax = 10%
Use Tax = 10%
Share1 = 60%
Share2 = 40%

So Sales tax Amt (A) = Cost1 * Share1 * Sales Tax
So Use tax Amt (B) = cost1 * share2 * Use tax

same calculation for all the costs and then total cost with Sales tax = Cost 1 + A , Cost 2 + A and so on..
and total cost with Use tax = Cost1 +B, Cost 2 +B etc.

So there are around 14 new fields required to save Sales Tax amt for each cost, another 14 new fields to store Cost with Sales Tax, Cost with Use tax. So that increases the table size.
Some of these fields might be used for making reports.

I was wondering which is a better approach out of the below 4:
1) To calculate these fields dynamically while displaying them on the User interface and not save in DB (while making reports, again calculate these fields dynamically and show), or
2) Add new formula field columns in database table to save each field, which would make the table size bigger, but reporting becomes easier.
3) Add only those columns in database on which reports needs to be made, calculate rest of the fields dynamically on screen.

4) Create a view just for reports, and calculate values dynamically in UI and not adding any computed values in table.

Your help is greatly appreciated.
Thanks

View Replies !

Creating A Generic Package To Import A Variable Number Of Columns
Hi,

We are building an application with
a database that contains Jobs. These Jobs have properties like Name, Code etc.
and some custom properties, definable by the application admin. For bulk import
of Jobs, we want to allow the import of an Excel sheet with the columns Name,
Code and a variable amount of columns. If the header names of these columns in
the Excel sheet match the name of a custom property in the system we want to add
the value of that cell into the database as property
value.

In our Data Flow of our Import
Package in SSIS we added an Excel Source that points to a test excel sheet with
the Name and Code columns and €“ for this example - 3 custom property columns
(Area, Department, Job Family). When we configure the Excel Source in the Excel
Source Editor, we have the option to select the Columns from the Available
External Columns table. But here lays the problem, we do not know at design
time, what custom property columns to expect. We DO expect the Name and Code
columns, but the rest is uncertain at design-time.

That raises the question: Is there
some way to select all of any incoming columns (something like a SELECT * in
T-SQL)? This looks like a big problem since it would mean that the .DTSX XML that is
being generated at design-time would need to be updated at run-time to reflect
the variability of the columns that might be encountered while reading the excel
sheet.

Then, we thought, we could add a Script
Component to our data flow that passes some kind of DataSet (or DataReader) in
which we can walk through the columns ourselves? But then still, we miss the
option to include ANY of the columns while reading an Excel sheet (or any other
datasource by the looks of it)

We are aware of the option of
optional columns in combination with the RaggedRight option, but it seems that
we would have to put all of the columns of a row in just one column and then
extract all the columns later with Derived Columns. But then, since the source
import file is being prepared by an application admin, we want don€™t want to
burden him with this horrendous task of putting everything in one
column.

We would like to have some way of
iterating through all the columns, either in a Script Component or maybe with a
Pivot/Unpivot mechanism.

Does anyone have any suggestions? Are there other options we should have considered?

View Replies !

Counting Occurrence Of A Value && How To Format For Fixed Number Of Rows && Columns
Hi,

I need to count and display the number of records which have GradeTitle="SHO". I'm only starting to use BI development studio and all attempts at using the built in aggregate functions have failed.

Also, the report I wish to create has a fixed number of columns and a fixed number of rows as the info being displayed is really only counting values in the DB. I tried using Table but multiple rows were created.

I'd appreciate if anyone could point me in the right direction, as searching this forum turned out to be pretty fruitless for me.

Thanks in advance,
John

View Replies !

How To Control The Number Of Columns In A Matrix To Be Shown In The Priview(its Orgent)
hii
Everybody
In my report i have a matrix,but i want to show only 10 columns in one page and rest of the data should come in next page?
so is there anyway to control the number of columns in a matrix to be shown in the priview?
please help me with some suggestions.
its orgent

thanks in advance
Mahasweta

View Replies !

Maximum Number Of Columns In A Sql Server Express 2005 Table
What is the maximum number of columns you can have in a sql server express 2005 table?

View Replies !

Flat File Data Source With Variable Number Of Delimited Columns
I am writing a package that will process delimited flat files that will come in one of a few different versions. Within each flat file, the number of delimited columns will be the same, but each version of the file has a different number of columns. I have tried configuring the flat file data source to expect the version with the largest number of columns, but it will then throw away rows that have less than this number of columns (warning: There is a partial row at the end of the file).

Is it possible to use a single flat file data source that will work with all of the different width files?

View Replies !

Dataset Into A Table Or Matrix With Fixed Number Of Columns, Variable Rows
Hi

   I have a dataset with 2 columns, a rownumber and a servername - eg

rownumber servername

1                  server1

2                  server2

....

15               server15

   I want to display the servernames in a report so that you get 3 columns - eg

server1 | server2 | server3

server4 | server5 | server6

...

server13 | server14 | server15

   I have tried using multiple tables and lists and filtering the data on each one but this then makes formating very hard - i either end up with a huge gap between columns or the columns overlap

   I have also tried using a matrix control but cant find a way to do this.

   Does anybody know an easy way to do this? The data comes from sql 2005 so i can use a pivot clause on the dataset if somebody knows a way to do it this way. The reporting service is also RS2005

Thanks

Anthony

View Replies !

The Number Of Requests For &&"XXXServerXXXUser&&" Has Exceeded The Maximum Number Allowed For A Single User

I have created a local user on Report Server Computer and the user has the administrative rights.
When i try to connect Report Server (http://xxx.xxx.xxx.xxx/reportserver) with this user's credantials. (ReportServer directory security is set -only- to Basic Authentication. ).
I get the following error.

Reporting Services Error
--------------------------------------------------------------------------------

The number of requests for "XXXServerXXXUser" has exceeded the maximum number allowed for a single user.
--------------------------------------------------------------------------------
SQL Server Reporting Services

Then i try to login using a different user with administrative rights on the machine, i can logon successfully.
The system is up for a month but this problem occured today?!? What could be the problem?!?

View Replies !

Maximum Number Of Tables - Is 4^15 Big A Number?
Hello people,I might sound a little bit crazy, but is there any possibility that youcan incorporate 4^15 (1,073,741,824) tables into a SQL Database?I mean, is it possible at all? There might be a question of whereanyone would want so many tables, but i'm a bioinformatics guy and I'mtrying to deal with genomic sequences and was coming up with a newalgorithm, where the only limit is the number of tables I can put intoa Database.So, can you please advise if its possible to put in so many tables intoa SQL database? Or is the Bekerley DB better?

View Replies !