Showing posts with label column. Show all posts
Showing posts with label column. Show all posts

Thursday, March 29, 2012

estimating maximum row size

hi,
give a table definition, how can i estimate the maximum row size ?
eg:
table (a varchar(8000),
b varchar(100))
the row size (from the column lengths) is 8100, but actually it is more than
that.
i know that coz when i create the above table i get an error as:
Warning: The table 'size_test' has been created but its maximum row size
(8125) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE
of a row in this table will fail if the resulting row length exceeds 8060
bytes.
i would like to check against this limit. that is why i need the max row size.
thanks
--
Vivek T S
Member Technical Staff (Inucom)Hello,
Check the following link
http://msdn.microsoft.com/library/default.asp?
url=/library/en-us/architec/8_ar_ts_8dbn.asp which gives
the max size per row as 8060 bytes.
I would sugest you change your varchar to something like a
text for some other large object. The reason is that they
can store a lot more than 8060 and still be on the same
table as other fields.
Peter
"Happiness is nothing more than good health and a bad
memory."
Albert Schweitzer
>--Original Message--
> hi,
> give a table definition, how can i estimate the
maximum row size ?
> eg:
> table (a varchar(8000),
> b varchar(100))
>the row size (from the column lengths) is 8100, but
actually it is more than
>that.
>i know that coz when i create the above table i get an
error as:
>Warning: The table 'size_test' has been created but its
maximum row size
>(8125) exceeds the maximum number of bytes per row
(8060). INSERT or UPDATE
>of a row in this table will fail if the resulting row
length exceeds 8060
>bytes.
>i would like to check against this limit. that is why i
need the max row size.
>thanks
>--
>Vivek T S
>Member Technical Staff (Inucom)
>.
>sql

Tuesday, March 27, 2012

Estimate Log Space for Alter Table Statement

My client's website database is hosted by a third party. I need to alter one of the column definitions for the largest table in the database. Unfortunately, the transaction log fills up if I try to alter the table. I've done all the usual stuff like truncating the log, etc., but the simple fact is that the operation requires more log space than we have available. Therefore, we need to purchase additional disk space for the database.

What I'm looking for is a way to roughly estimate how much log space will be required to alter this table so that we purchase enough but not too much additional space. The table has an identity primary key and 4 other single column indexes: one int, one datetime and two varchar(30) columns.

Any suggestions? Thanks in advance.

You can change the truncate log setting in a database if you don't need it to recover in time of emergency it is a very complicated subject so run a search for truncate log on checkpoint option for your database setting in the BOL(books online). The links below are basic guidelines from Microsoft. Hope this helps.

http://support.microsoft.com/kb/873235

http://msdn2.microsoft.com/en-us/library/aa933068(SQL.80).aspx

|||

If I remember correctly when you alter column in table, SQL server renames old one, created new table with new structure and next moves data into new table. So in your case you need at least amount of space your data table takes for log because all process has to be done at one shot.
But maybe you can do it yourself: create table with new structure and move data from old table to new table with quantity like 100 or 1000 records in one shot? Log file will be small and you should succeed. You only have to take care about identity insert if you have one in your table.

Thanks

|||

Thanks jpazgier, that's a great suggestion! I usually do the alter using Enterprise Manager directly for this client but I have scripted this type of change for other clients. I never thought about taking the generated script and breaking up the insert part of it into multiple inserts and performing the truncate log after each insert. That would reduce the log space required.

If I don't delete the old records as I go, I still need twice the data just for the old and new copies but I wouldn't need so much log space. If I trust my alterations, then I could delete the old records as they get moved into the new copy and really reduce the free space required. Not as safe but probably sufficient.

Thanks

|||

(1) You could keep the DB in simple mode while you do the data transfer.

(2) You can also have a job running to truncate the log during the transfer and let the job run every 1 minute, so you dont have to do it yourself manually.

(3) You can use bcp/Bulk Insert to transfer the data.

Monday, March 26, 2012

escaping data for update query

I have some code (C#) that runs an SQL update query that sets the
value of a column to what the user passes. So, this causes an error
when anything the user passes in has a ' character in it. I'm sure
there's other characters that'll break it too. So, I was wondering,
how do I get around this? Is there some commonly accepted regex
pattern that will make the value safe to run in an SQL query? How can
I take care of any values that need to be escaped?
I'm not using any fancy ado.net objects:
string sql= [whatever the user passes in]
SqlConnection connection = new
SqlConnection(ConfigurationManager.ConnectionStrin gs[Utils.GetConnectionString].ToString());
connection.Open();
SqlCommand command = connection.CreateCommand();
command.CommandType = CommandType.Text;
command.CommandText = sql;
try
{
int result = command.ExecuteNonQuery();
if (result != 1)
{
Response.StatusCode = 500;
Response.Write("The file has been uploaded, but we
could not update the DB");
Response.End();
}
}
catch (InvalidOperationException)
{
Response.Clear();
Response.Write("error");
Response.StatusCode = 500;
Response.End();
}
connection.Close();
On 6 4 , 8 48 , eggie5 <egg...@.gmail.com> wrote:
> I have some code (C#) that runs an SQL update query that sets the
> value of a column to what the user passes. So, this causes an error
> when anything the user passes in has a ' character in it. I'm sure
> there's other characters that'll break it too. So, I was wondering,
> how do I get around this? Is there some commonly accepted regex
> pattern that will make the value safe to run in an SQL query? How can
> I take care of any values that need to be escaped?
> I'm not using any fancy ado.net objects:
> string sql= [whatever the user passes in]
> SqlConnection connection = new
> SqlConnection(ConfigurationManager.ConnectionStrin gs[Utils.GetConnectionStrXing].ToString());
> connection.Open();
> SqlCommand command = connection.CreateCommand();
> command.CommandType = CommandType.Text;
> command.CommandText = sql;
> try
> {
> int result = command.ExecuteNonQuery();
> if (result != 1)
> {
> Response.StatusCode = 500;
> Response.Write("The file has been uploaded, but we
> could not update the DB");
> Response.End();
> }
> }
> catch (InvalidOperationException)
> {
> Response.Clear();
> Response.Write("error");
> Response.StatusCode = 500;
> Response.End();
> }
> connection.Close();
You can string.replace() method to escape
charater ' by ''(double single quotes).
|||If you post the same question to multiple groups, send the message once and
specify all groups (crosspost) rather than post independent messages. This
courtesy allows everyone involved to track the responses and prevents
duplication of effort.

> Is there some commonly accepted regex
> pattern that will make the value safe to run in an SQL query? How can
> I take care of any values that need to be escaped?
The Best Practice is to use parameters rather than build a SQL statement
string. Not only does this eliminate the need to escape quotes, it's much
more secure because it's not vulnerable to SQL injection. Simple example
below.
command.CommandText = "INSERT INTO dbo.MyTable VALUES(@.UserParameter)";
SqlParameter param = new SqlParameter("@.UserParameter",
userSuppliedValue);
command.Parameters.Add(param);
command.ExecuteNonQuery();
Hope this helps.
Dan Guzman
SQL Server MVP
"eggie5" <eggie5@.gmail.com> wrote in message
news:1180918088.976008.41270@.q75g2000hsh.googlegro ups.com...
>I have some code (C#) that runs an SQL update query that sets the
> value of a column to what the user passes. So, this causes an error
> when anything the user passes in has a ' character in it. I'm sure
> there's other characters that'll break it too. So, I was wondering,
> how do I get around this? Is there some commonly accepted regex
> pattern that will make the value safe to run in an SQL query? How can
> I take care of any values that need to be escaped?
> I'm not using any fancy ado.net objects:
> string sql= [whatever the user passes in]
> SqlConnection connection = new
> SqlConnection(ConfigurationManager.ConnectionStrin gs[Utils.GetConnectionString].ToString());
> connection.Open();
> SqlCommand command = connection.CreateCommand();
> command.CommandType = CommandType.Text;
> command.CommandText = sql;
>
> try
> {
> int result = command.ExecuteNonQuery();
> if (result != 1)
> {
> Response.StatusCode = 500;
> Response.Write("The file has been uploaded, but we
> could not update the DB");
> Response.End();
> }
> }
> catch (InvalidOperationException)
> {
> Response.Clear();
> Response.Write("error");
> Response.StatusCode = 500;
> Response.End();
> }
> connection.Close();
>

escaping data for update query

I have some code (C#) that runs an SQL update query that sets the
value of a column to what the user passes. So, this causes an error
when anything the user passes in has a ' character in it. I'm sure
there's other characters that'll break it too. So, I was wondering,
how do I get around this? Is there some commonly accepted regex
pattern that will make the value safe to run in an SQL query? How can
I take care of any values that need to be escaped?
I'm not using any fancy ado.net objects:
string sql= [whatever the user passes in]
SqlConnection connection = new
SqlConnection(ConfigurationManager.ConnectionStrings[Utils.GetConnectionString].ToString());
connection.Open();
SqlCommand command = connection.CreateCommand();
command.CommandType = CommandType.Text;
command.CommandText = sql;
try
{
int result = command.ExecuteNonQuery();
if (result != 1)
{
Response.StatusCode = 500;
Response.Write("The file has been uploaded, but we
could not update the DB");
Response.End();
}
}
catch (InvalidOperationException)
{
Response.Clear();
Response.Write("error");
Response.StatusCode = 500;
Response.End();
}
connection.Close();On 6 4 , 8 48 , eggie5 <egg...@.gmail.com> wrote:
> I have some code (C#) that runs an SQL update query that sets the
> value of a column to what the user passes. So, this causes an error
> when anything the user passes in has a ' character in it. I'm sure
> there's other characters that'll break it too. So, I was wondering,
> how do I get around this? Is there some commonly accepted regex
> pattern that will make the value safe to run in an SQL query? How can
> I take care of any values that need to be escaped?
> I'm not using any fancy ado.net objects:
> string sql=3D [whatever the user passes in]
> SqlConnection connection =3D new
> SqlConnection(ConfigurationManager.ConnectionStrings[Utils.GetConnectionS=tr=ADing].ToString());
> connection.Open();
> SqlCommand command =3D connection.CreateCommand();
> command.CommandType =3D CommandType.Text;
> command.CommandText =3D sql;
> try
> {
> int result =3D command.ExecuteNonQuery();
> if (result !=3D 1)
> {
> Response.StatusCode =3D 500;
> Response.Write("The file has been uploaded, but we
> could not update the DB");
> Response.End();
> }
> }
> catch (InvalidOperationException)
> {
> Response.Clear();
> Response.Write("error");
> Response.StatusCode =3D 500;
> Response.End();
> }
> connection.Close();
You can string.replace() method to escape
charater ' by ''(double single quotes).|||If you post the same question to multiple groups, send the message once and
specify all groups (crosspost) rather than post independent messages. This
courtesy allows everyone involved to track the responses and prevents
duplication of effort.
> Is there some commonly accepted regex
> pattern that will make the value safe to run in an SQL query? How can
> I take care of any values that need to be escaped?
The Best Practice is to use parameters rather than build a SQL statement
string. Not only does this eliminate the need to escape quotes, it's much
more secure because it's not vulnerable to SQL injection. Simple example
below.
command.CommandText = "INSERT INTO dbo.MyTable VALUES(@.UserParameter)";
SqlParameter param = new SqlParameter("@.UserParameter",
userSuppliedValue);
command.Parameters.Add(param);
command.ExecuteNonQuery();
--
Hope this helps.
Dan Guzman
SQL Server MVP
"eggie5" <eggie5@.gmail.com> wrote in message
news:1180918088.976008.41270@.q75g2000hsh.googlegroups.com...
>I have some code (C#) that runs an SQL update query that sets the
> value of a column to what the user passes. So, this causes an error
> when anything the user passes in has a ' character in it. I'm sure
> there's other characters that'll break it too. So, I was wondering,
> how do I get around this? Is there some commonly accepted regex
> pattern that will make the value safe to run in an SQL query? How can
> I take care of any values that need to be escaped?
> I'm not using any fancy ado.net objects:
> string sql= [whatever the user passes in]
> SqlConnection connection = new
> SqlConnection(ConfigurationManager.ConnectionStrings[Utils.GetConnectionString].ToString());
> connection.Open();
> SqlCommand command = connection.CreateCommand();
> command.CommandType = CommandType.Text;
> command.CommandText = sql;
>
> try
> {
> int result = command.ExecuteNonQuery();
> if (result != 1)
> {
> Response.StatusCode = 500;
> Response.Write("The file has been uploaded, but we
> could not update the DB");
> Response.End();
> }
> }
> catch (InvalidOperationException)
> {
> Response.Clear();
> Response.Write("error");
> Response.StatusCode = 500;
> Response.End();
> }
> connection.Close();
>

escaping data for update query

I have some code (C#) that runs an SQL update query that sets the
value of a column to what the user passes. So, this causes an error
when anything the user passes in has a ' character in it. I'm sure
there's other characters that'll break it too. So, I was wondering,
how do I get around this? Is there some commonly accepted regex
pattern that will make the value safe to run in an SQL query? How can
I take care of any values that need to be escaped?
I'm not using any fancy ado.net objects:
string sql= [whatever the user passes in]
SqlConnection connection = new
SqlConnection(ConfigurationManager.ConnectionStrings[Utils.GetConnection
String].ToString());
connection.Open();
SqlCommand command = connection.CreateCommand();
command.CommandType = CommandType.Text;
command.CommandText = sql;
try
{
int result = command.ExecuteNonQuery();
if (result != 1)
{
Response.StatusCode = 500;
Response.Write("The file has been uploaded, but we
could not update the DB");
Response.End();
}
}
catch (InvalidOperationException)
{
Response.Clear();
Response.Write("error");
Response.StatusCode = 500;
Response.End();
}
connection.Close();On 6 4 , 8 48 , eggie5 <egg...@.gmail.com> wrote:
> I have some code (C#) that runs an SQL update query that sets the
> value of a column to what the user passes. So, this causes an error
> when anything the user passes in has a ' character in it. I'm sure
> there's other characters that'll break it too. So, I was wondering,
> how do I get around this? Is there some commonly accepted regex
> pattern that will make the value safe to run in an SQL query? How can
> I take care of any values that need to be escaped?
> I'm not using any fancy ado.net objects:
> string sql=3D [whatever the user passes in]
> SqlConnection connection =3D new
> SqlConnection(ConfigurationManager.ConnectionStrings[Utils.GetConnectionS=[/vb
col]
tr=ADing].ToString());[vbcol=seagreen]
> connection.Open();
> SqlCommand command =3D connection.CreateCommand();
> command.CommandType =3D CommandType.Text;
> command.CommandText =3D sql;
> try
> {
> int result =3D command.ExecuteNonQuery();
> if (result !=3D 1)
> {
> Response.StatusCode =3D 500;
> Response.Write("The file has been uploaded, but we
> could not update the DB");
> Response.End();
> }
> }
> catch (InvalidOperationException)
> {
> Response.Clear();
> Response.Write("error");
> Response.StatusCode =3D 500;
> Response.End();
> }
> connection.Close();
You can string.replace() method to escape
charater ' by ''(double single quotes).|||If you post the same question to multiple groups, send the message once and
specify all groups (crosspost) rather than post independent messages. This
courtesy allows everyone involved to track the responses and prevents
duplication of effort.

> Is there some commonly accepted regex
> pattern that will make the value safe to run in an SQL query? How can
> I take care of any values that need to be escaped?
The Best Practice is to use parameters rather than build a SQL statement
string. Not only does this eliminate the need to escape quotes, it's much
more secure because it's not vulnerable to SQL injection. Simple example
below.
command.CommandText = "INSERT INTO dbo.MyTable VALUES(@.UserParameter)";
SqlParameter param = new SqlParameter("@.UserParameter",
userSuppliedValue);
command.Parameters.Add(param);
command.ExecuteNonQuery();
Hope this helps.
Dan Guzman
SQL Server MVP
"eggie5" <eggie5@.gmail.com> wrote in message
news:1180918088.976008.41270@.q75g2000hsh.googlegroups.com...
>I have some code (C#) that runs an SQL update query that sets the
> value of a column to what the user passes. So, this causes an error
> when anything the user passes in has a ' character in it. I'm sure
> there's other characters that'll break it too. So, I was wondering,
> how do I get around this? Is there some commonly accepted regex
> pattern that will make the value safe to run in an SQL query? How can
> I take care of any values that need to be escaped?
> I'm not using any fancy ado.net objects:
> string sql= [whatever the user passes in]
> SqlConnection connection = new
> SqlConnection(ConfigurationManager.ConnectionStrings[Utils.GetConnecti
onString].ToString());
> connection.Open();
> SqlCommand command = connection.CreateCommand();
> command.CommandType = CommandType.Text;
> command.CommandText = sql;
>
> try
> {
> int result = command.ExecuteNonQuery();
> if (result != 1)
> {
> Response.StatusCode = 500;
> Response.Write("The file has been uploaded, but we
> could not update the DB");
> Response.End();
> }
> }
> catch (InvalidOperationException)
> {
> Response.Clear();
> Response.Write("error");
> Response.StatusCode = 500;
> Response.End();
> }
> connection.Close();
>

escaping data for update query

I have some code (C#) that runs an SQL update query that sets the
value of a column to what the user passes. So, this causes an error
when anything the user passes in has a ' character in it. I'm sure
there's other characters that'll break it too. So, I was wondering,
how do I get around this? Is there some commonly accepted regex
pattern that will make the value safe to run in an SQL query? How can
I take care of any values that need to be escaped?

I'm not using any fancy ado.net objects:

string sql= [whatever the user passes in]

SqlConnection connection = new
SqlConnection(ConfigurationManager.ConnectionStrin gs[Utils.GetConnectionString].ToString());
connection.Open();

SqlCommand command = connection.CreateCommand();
command.CommandType = CommandType.Text;
command.CommandText = sql;

try
{
int result = command.ExecuteNonQuery();

if (result != 1)
{
Response.StatusCode = 500;
Response.Write("The file has been uploaded, but we
could not update the DB");
Response.End();
}
}
catch (InvalidOperationException)
{
Response.Clear();
Response.Write("error");
Response.StatusCode = 500;
Response.End();
}

connection.Close();If you post the same question to multiple groups, send the message once and
specify all groups (crosspost) rather than post independent messages. This
courtesy allows everyone involved to track the responses and prevents
duplication of effort.

This question has been answered in both microsoft.public.sqlserver.server
and microsoft.public.sqlserver.programming.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"eggie5" <eggie5@.gmail.comwrote in message
news:1180917930.810194.38600@.q75g2000hsh.googlegro ups.com...

Quote:

Originally Posted by

>I have some code (C#) that runs an SQL update query that sets the
value of a column to what the user passes. So, this causes an error
when anything the user passes in has a ' character in it. I'm sure
there's other characters that'll break it too. So, I was wondering,
how do I get around this? Is there some commonly accepted regex
pattern that will make the value safe to run in an SQL query? How can
I take care of any values that need to be escaped?
>
I'm not using any fancy ado.net objects:
>
string sql= [whatever the user passes in]
>
SqlConnection connection = new
SqlConnection(ConfigurationManager.ConnectionStrin gs[Utils.GetConnectionString].ToString());
connection.Open();
>
SqlCommand command = connection.CreateCommand();
command.CommandType = CommandType.Text;
command.CommandText = sql;
>
>
try
{
int result = command.ExecuteNonQuery();
>
if (result != 1)
{
Response.StatusCode = 500;
Response.Write("The file has been uploaded, but we
could not update the DB");
Response.End();
}
}
catch (InvalidOperationException)
{
Response.Clear();
Response.Write("error");
Response.StatusCode = 500;
Response.End();
}
>
connection.Close();
>

|||oops sorry!

On Jun 3, 8:18 pm, "Dan Guzman" <guzma...@.nospam-online.sbcglobal.net>
wrote:

Quote:

Originally Posted by

If you post the same question to multiple groups, send the message once and
specify all groups (crosspost) rather than post independent messages. This
courtesy allows everyone involved to track the responses and prevents
duplication of effort.
>
This question has been answered in both microsoft.public.sqlserver.server
and microsoft.public.sqlserver.programming.
>
--
Hope this helps.
>
Dan Guzman
SQL Server MVP
>
"eggie5" <egg...@.gmail.comwrote in message
>
news:1180917930.810194.38600@.q75g2000hsh.googlegro ups.com...
>

Quote:

Originally Posted by

I have some code (C#) that runs an SQL update query that sets the
value of a column to what the user passes. So, this causes an error
when anything the user passes in has a ' character in it. I'm sure
there's other characters that'll break it too. So, I was wondering,
how do I get around this? Is there some commonly accepted regex
pattern that will make the value safe to run in an SQL query? How can
I take care of any values that need to be escaped?


>

Quote:

Originally Posted by

I'm not using any fancy ado.net objects:


>

Quote:

Originally Posted by

string sql= [whatever the user passes in]


>

Quote:

Originally Posted by

SqlConnection connection = new
SqlConnection(ConfigurationManager.ConnectionStrin gs[Utils.GetConnectionStr ing].ToString());
connection.Open();


>

Quote:

Originally Posted by

SqlCommand command = connection.CreateCommand();
command.CommandType = CommandType.Text;
command.CommandText = sql;


>

Quote:

Originally Posted by

try
{
int result = command.ExecuteNonQuery();


>

Quote:

Originally Posted by

if (result != 1)
{
Response.StatusCode = 500;
Response.Write("The file has been uploaded, but we
could not update the DB");
Response.End();
}
}
catch (InvalidOperationException)
{
Response.Clear();
Response.Write("error");
Response.StatusCode = 500;
Response.End();
}


>

Quote:

Originally Posted by

connection.Close();

Monday, March 19, 2012

Errors in the OLAP storage engine

Dear all

I have one table 'Sales' as dimension in a cube for an analysis. In that table one column exist ' DateofOrder'. I want to generate the analysis as Year, Quarter, Month like that using the column DateOfOrder....

What I have to do

Please help ...

Hi,

You can ceate an attribute as DateOfOrder with composite columns Year, Quater, Month, and define the key attribute columns for this attribute as well. (in this case, you could define the key columns as year, quater, month).

Regards,

|||

Thank a lot Helen..

But please help me how to can I create an attrrbute in a dimension 'Sales' for orderdate

|||

Go to the dimension in the data source view, if you are using Analysis Services 2005, and add new named calculation(right click on the dimension table). You use TSQL time functions like YEAR(MyDateColumn), QUARTER(MyDateColumn) and DATEPART(mm,MyDateColumn) to create these levels.

HTH

Thomas Ivarsson

|||

The name of the table 'Sales' does not sound like a "dimension table". Did you mean "Sales" is your Fact table, which is the source of the measure group? What other columns in Sales table would be the source of attributes in the dimension?

Suppose my suspecion is right and Sales is your fact table, then...

The easiest way is to use Server Time generated dimension. Start creating new dimension. The dimension wizard will show up. Select "Build the dimension without data source", navigate to the next page of the wizard. Select "Server time dimension". After that follow the wizard to specify which attributes your dimension will have. Once you have your time dimension you need to relate it to your measure group. Go to Dimension Usage panel of the Cube Designer and click the cell at the intersection of your generated Time dimension and Sales measure group. In the dialog box, which will show up, select Regular relationship and select DateOfOrder column as a measure group column.

If my guess is not right and you indeed need to have a dimension out of Sales table then you need to follow the suggestions you already got.

|||

Sir,

Many Thanks to u sir.... Many thanks ... ..

With regards

Polachan

|||

Error 4

When I am deploying the project I got the following error. Please help me sir


Errors in the OLAP storage engine: An error occurred while processing the 'Product Tran Header' partition of the 'Product Tran Header' measure group for the 'Test' cube from the productreport database. 0 0

I did the following steps

1. added new measure selecting new source table

2. selected one column dateoforder

3. Add new dimension as without using data source

4. selected server time dimension

5. Selected Year/Month/Quarter/date

6 Selected fiscal year

7.Selected dimension usage in cube desgn

8. selected time dimension and selected regular relation, Granulary attribute as Date

9. Measure group table 'Product tran header' new measure group

10 selected measure group column as dateoforder.

after that while deploying the above mentioned error will come

Please help

|||

There are 2 things. Did i understand right that on the step 2 you selected [dateoforder] column as a source of measure? What is your measure? Is it count of transactions? If you measure something like [Order Amount] then you nees to drag [Order Amount] column onto measure group tree view and not [dateoforder]. This column will just be used later in Dimension Usage step, which you made right in step 10.

If you open your server generated dimension in the designer and select tree node related to the dimension itself you can browse the properties of that dimension. There is one of the properties (forgot its exact name), which describes the time range of the dimension like 1/1/2000 - 12/31/2005. Please make sure that this range covers all the instances of [dateoforder] from your fact table.

|||

Dear Sir,

I wil explain what I need

I have one table 'Transactioheader' with the following column

1. id

2. DateofTransaction

Another table with the following column

1. headerid

2. qty

3. productcode

4. areacode

I wanto get the report with sqlanalyis with following format

Quarter1 Quarter2 Quarter3

Qty Qty Qty

Area

Omagh 200 10 100

Belfast 10 05 04

The Quarter 1, Quarter2, Quarter 3 have to be derived from the basis of DateofTransaction column with mapping of Servertime dimension..Please help me sir in this situation what I have to do to use servertime dimension

Thanks a lot for ur caring my queries...

Friday, March 9, 2012

Errors adding a column in 64-bit version

I have run into an interesting problem. I have some code that adds a column to an existing table. The column is set as primary key, identity and clustered. On the 32-bit version of SQL 2005 it works fine. It fails on the 64-bit version of 2005 intermittently with a "could not create unique index because duplicate values were found". Kind of odd, considering as an identity field it's creating the values. I was able to recreate the problem with the following schema:

Create table test1

(col1 varchar(20),

col2 varchar(20),

col3 uniqueidentifier default newid())

-- insert data

Declare @.counter int

set @.counter=1

While @.counter < 1000000

BEGIN

insert into test1

values ('Joe','Smith',default)

Set @.counter=@.counter+1

END

-- add column

Alter table test1 add col4 int constraint PK_test1 primary key clustered identity

After Running this, I get this error:

CREATE UNIQUE INDEX terminated because a duplicate key was found for object name 'dbo.Test1' and index name 'PK_test1'. The duplicate key value is (28).

Anybody else run into this? Why would this be happening, and is there any way to fix it? I'm running the 64-bit version of Windows 2003 and the the 64-bit version of SQL Server 2005.

Thanks in advance,

Mark

Could you please file a bug with these details / repro steps at the MSDN Product Feedback Center? I do not have a 64-bit m/c to try this out. Also, please mention the editions of Windows and SQL Server (x64, IA, EMT). Thanks.

Wednesday, March 7, 2012

ErrorCode and ErrorColumn in Excel Destination

I am exporting records with errors to Excel using the Excel Destination tool. The ErrorColumn is a numeric. How do I find out which column it is?

If you want to do this manually (i.e. not programmatically), you can open the Advanced UI for the Excel Dest, and go to the "Input and Output Properties" tab. There, under "Excel Destination Input", you can look through the colums. As you click on each one, look for the "ID" property on the right that has the value you found in the ErrorColumn field of the error row.

Let me know if this helps or not.

Thanks,
Mark|||Hi

How would you do this programmatically? Ideally, I'd like to include the actual column name as another output column in my error destination table. Also, is it possible to obtain the error description as well as the ErrorCode (similar to what you might see in the Execution Results pane when debugging)?

Thanks

Rob|||Hi Rob

I want to do something similar but write out the column name to a table along with the data when rejected. Did you ever figure out how to do this?

Thanks

Marcus|||While it's fairly easy to add an ErrorDescription column on top of the ErrorCode, it's NOT so easy to add an ErrorColumnName on top of the ErrorColumn numeric ID value, because of all the ifs ands and buts that determine whether the column name is readily available or not. (For example, did the preceding component have synchronous or asynchronous outputs, and so forth.)

You can run your error output through a Script Component and try looping through the columns in its InputColumnCollection and VirtualInputColumnCollection looking for the ID value that you've got in the ErrorColumn column to see that this information is usually unavailable.

-Doug

ErrorCode 14

I've scripted a sql trace for Sql Server 2000 and added the filename in the appropreate location andthe result is a column called ErrorCode with a value of 14. Can anyone tell me what this error code means and what could be infered about what I need to resolve it?

Thanks,
Marc

It is most probably the call to sp_trace_create that fails. You can inspect the script to find out which one of the system stored procedure call is failing. For example, error code 14 indicates the stop time is incorrect for sp_trace_create. See BOL topic below:

http://msdn2.microsoft.com/en-us/library/ms190362.aspx

Also, if you double-click on the error message in SSMS you should be positioned on the call that failed. This should give some idea as to which system SP call failed.

ErrorCode 1038 during push replication

I’m setting up transactional replication, the snapshot generates fine, but I
get error code 1038 “Cannot use empty object or column names. Use a single
space if necessary.” when the distribution agent does the push replication
and reaches a particular SP.
Something to note is that table names wrapped in brackets (found in SQL
scripts in the snapshot folder) are being converted to quotation, is that
caused by SET QUOTED_IDENTIFIER ON?
so this: FROM [Pub_Articles-Keywords] StaArtK
is converted to: FROM "Pub_Articles-Keywords" StaArtK
The following is the detailed error log. Your help is appreciated.
[12/20/2005 4:44:00 PM]SERVER2.Keyword DB: SET QUOTED_IDENTIFIER ON
[12/20/2005 4:44:00 PM]SERVER2.Keyword DB: drop procedure
"sp_Search_KeywordBrowser"
[12/20/2005 4:44:00 PM]SERVER2.Keyword DB: CREATE PROCEDURE
"sp_Search_KeywordBrowser"
@.KeywordRoot int,
@.ArtRoot int,
@.start varchar(4) = '0',
@.quantity varchar(3) = '10',
@.Sort varchar(25) = 'priority',
@.YahooRoot int = 0
AS
if @.yahooroot = 0 set @.YahooRoot = @.ArtRoot
CREATE TABLE #TempTable
(PK int IDENTITY,
ArticleID int,
Link int,
priority int,
created datetime)
SET NOCOUNT ON
Insert INTO #TempTable (articleid, link, priority, created)
SELECT StaArtK.ArticleID ArticleID, A.Link Link,
StaArtK.priority, coalesce (b.posteddate1, a.posteddate1, a.created)
FROM "Pub_Articles-Keywords" StaArtK
JOIN Pub_Articles AS A ON StaArtK.ArticleID = A.ArticleID
left join Pub_Articles B on a.link = b.articleid
JOIN SpeedIndexes.dbo.Pub_Articles_Static AS pas ON Pas.BranchID =
StaArtK.ArticleID
WHERE StaArtK.KeywordID = @.KeywordRoot AND pas.ParentID = @.ArtRoot
AND StaArtK.articleid not in (select articleid from
SpeedIndexes.dbo."Pub_Articles-Keywords_Static" where
keywordid = 272 or keywordid = 2471) and StaArtK.block = 0
Insert INTO #TempTable (articleid, link, priority, created)
SELECT StaArtK.ArticleID ArticleID, A.Link Link,
pak.priority, coalesce (b.posteddate1, a.posteddate1, a.created)
FROM SpeedIndexes.dbo."Pub_Articles-Keywords_static" StaArtK
JOIN Pub_Articles AS A ON StaArtK.ArticleID = A.ArticleID
left join Pub_Articles B on a.link = b.articleid
JOIN SpeedIndexes.dbo.Pub_Articles_Static AS pas ON Pas.BranchID =
StaArtK.ArticleID
Join "pub_articles-keywords" pak on StaArtK.nativeid = pak.articleid and
StaArtK.keywordid = pak.keywordid
WHERE StaArtK.KeywordID = @.KeywordRoot AND pas.ParentID = @.ArtRoot
AND StaArtK.articleid not in (select articleid from
SpeedIndexes.dbo."Pub_Articles-Keywords_Static" where
keywordid = 272 or keywordid = 2471)
and StaArtK.nativeid not in (select articleid from #temptable)
update t set t.ArticleID = st.nativeid from #TempTable t join
speedindexes.dbo."pub_articles-keywords_static" st on t.articleid =
st.articleid where st.keywordid = 1060 and t.articleid <> st.nativeid
delete #tempTable where pk not in (select Max(pk) from #tempTable group by
articleid)
DELETE #TempTable WHERE (Link <> 0 AND Link IN (SELECT ArticleID FROM
#TempTable))
--Remove Links that have 'cousin links' in the result set
--OR (ArticleID NOT IN
--(SELECT TOP 1 ArticleID
--FROM #TempTable
-- GROUP BY Link, ArticleID
--HAVING COUNT(Link) > 1) AND Link <> 0)
declare @.resultcount int
set @.resultcount = (select count(*) from #TempTable)
declare @.sql nvarchar(500)
set @.sql = 'delete #TempTable where articleid not in (SELECT top ' +
@.quantity + ' articleid FROM #TempTable where pk not in (select top ' +
@.start + ' pk from #TempTable order by ' + @.sort + ' , pk) ORDER BY ' + @.sort
+ ', pk) '
EXEC sp_executesql @.sql
SELECT t.priority, a.ArticleID,
isnull(dbo.SF_PUB_GetAuthorByline(a.articleid),"") toptext,
dbo.SF_Pub_GetArticlePath (a.articleid,@.YahooRoot) path, t.created,
COALESCE (A.Title2, A2.Title2, A.Title1, A2.Title1) Title ,
COALESCE(a.subtitle, a2.subtitle,'') subtitle,
isnull(dbo.SF_PUB_GetInhKeywordID(a.articleid, 6913),0) DocType,
COALESCE(A.Synopsis0, A2.Synopsis0, A.Synopsis1, A2.Synopsis1,
A.Synopsis2, A2.Synopsis2, A.body, A2.Body, '') AS Synopsis
FROM #TempTable t
join pub_articles a on t.articleid = a.articleid left join
pub_articles a2 on a.link = a2.articleid
order by
case when @.sort = 'priority' or @.sort = 'priority,created' then
t.priority else '' end,
case when @.sort = 'created' or @.sort = 'priority,created' then
a.posteddate1 elsAgent message code 20046. Cannot use empty object or column
names. Use a single space if necessary.
[12/20/2005 4:44:00 PM]SERVER1.distribution: {call
sp_MSadd_distribution_history(7, 6, ?, ?, 0, 0, 0.00, 0x01, 1, ?, 20, 0x01,
0x01)}
Adding alert to msdb..sysreplicationalerts: ErrorId = 21,
Transaction Seqno = 000275890000022200b100000001, Command ID = 20
Message: Replication-Replication Distribution Subsystem: agent
SERVER1-Keyword DB-SERVER2-7 failed. Cannot use empty object or column names.
Use a single space if necessary.[12/20/2005 4:44:00 PM]SERVER1.distribution:
{call sp_MSadd_repl_alert(3, 7, 21, 14151, ?, 20, N'SERVER1', N'Keyword DB',
N'SERVER2', N'Keyword DB', ?)}
ErrorId = 21, SourceTypeId = 5
ErrorCode = '1038'
ErrorText = 'Cannot use empty object or column names. Use a single space if
necessary.'
[12/20/2005 4:44:00 PM]SERVER1.distribution: {call sp_MSadd_repl_error(21,
0, 5, ?, N'1038', ?)}
Category:SQLSERVER
Source: SERVER2
Number: 1038
Message: Cannot use empty object or column names. Use a single space if
necessary.
[12/20/2005 4:44:00 PM]SERVER2.Keyword DB: exec dbo.sp_MSupdatelastsyncinfo
N'SERVER1',N'Keyword DB', N'', 0, 6, N'Cannot use empty object or column
names. Use a single space if necessary.'
Disconnecting from Subscriber 'SERVER2'
Disconnecting from Distributor 'SERVER1'
Disconnecting from Distributor History 'SERVER1'
Thanks,
- Moshe
Hi Moshe,
You would need to replace the "" in the following code fragment with '':
SELECT t.priority, a.ArticleID,
isnull(dbo.SF_PUB_GetAuthorByline(a.articleid),"") toptext,
To make your procedure compliant with quoted_identifier on setting. You can
search this newsgroup for some of the responses I made in the past on how
this came to be. Note that this translation should be done automatically for
you by the SQL2005 snapshot agent.
HTH
-Raymond
"Moshe" wrote:

> I’m setting up transactional replication, the snapshot generates fine, but I
> get error code 1038 “Cannot use empty object or column names. Use a single
> space if necessary.” when the distribution agent does the push replication
> and reaches a particular SP.
> Something to note is that table names wrapped in brackets (found in SQL
> scripts in the snapshot folder) are being converted to quotation, is that
> caused by SET QUOTED_IDENTIFIER ON?
> so this: FROM [Pub_Articles-Keywords] StaArtK
> is converted to: FROM "Pub_Articles-Keywords" StaArtK
> The following is the detailed error log. Your help is appreciated.
> [12/20/2005 4:44:00 PM]SERVER2.Keyword DB: SET QUOTED_IDENTIFIER ON
> [12/20/2005 4:44:00 PM]SERVER2.Keyword DB: drop procedure
> "sp_Search_KeywordBrowser"
> [12/20/2005 4:44:00 PM]SERVER2.Keyword DB: CREATE PROCEDURE
> "sp_Search_KeywordBrowser"
> @.KeywordRoot int,
> @.ArtRoot int,
> @.start varchar(4) = '0',
> @.quantity varchar(3) = '10',
> @.Sort varchar(25) = 'priority',
> @.YahooRoot int = 0
> AS
>
> if @.yahooroot = 0 set @.YahooRoot = @.ArtRoot
> CREATE TABLE #TempTable
> (PK int IDENTITY,
> ArticleID int,
> Link int,
> priority int,
> created datetime)
> SET NOCOUNT ON
> Insert INTO #TempTable (articleid, link, priority, created)
> SELECT StaArtK.ArticleID ArticleID, A.Link Link,
> StaArtK.priority, coalesce (b.posteddate1, a.posteddate1, a.created)
> FROM "Pub_Articles-Keywords" StaArtK
> JOIN Pub_Articles AS A ON StaArtK.ArticleID = A.ArticleID
> left join Pub_Articles B on a.link = b.articleid
> JOIN SpeedIndexes.dbo.Pub_Articles_Static AS pas ON Pas.BranchID =
> StaArtK.ArticleID
> WHERE StaArtK.KeywordID = @.KeywordRoot AND pas.ParentID = @.ArtRoot
> AND StaArtK.articleid not in (select articleid from
> SpeedIndexes.dbo."Pub_Articles-Keywords_Static" where
> keywordid = 272 or keywordid = 2471) and StaArtK.block = 0
>
> Insert INTO #TempTable (articleid, link, priority, created)
> SELECT StaArtK.ArticleID ArticleID, A.Link Link,
> pak.priority, coalesce (b.posteddate1, a.posteddate1, a.created)
> FROM SpeedIndexes.dbo."Pub_Articles-Keywords_static" StaArtK
> JOIN Pub_Articles AS A ON StaArtK.ArticleID = A.ArticleID
> left join Pub_Articles B on a.link = b.articleid
> JOIN SpeedIndexes.dbo.Pub_Articles_Static AS pas ON Pas.BranchID =
> StaArtK.ArticleID
> Join "pub_articles-keywords" pak on StaArtK.nativeid = pak.articleid and
> StaArtK.keywordid = pak.keywordid
> WHERE StaArtK.KeywordID = @.KeywordRoot AND pas.ParentID = @.ArtRoot
> AND StaArtK.articleid not in (select articleid from
> SpeedIndexes.dbo."Pub_Articles-Keywords_Static" where
> keywordid = 272 or keywordid = 2471)
> and StaArtK.nativeid not in (select articleid from #temptable)
>
> update t set t.ArticleID = st.nativeid from #TempTable t join
> speedindexes.dbo."pub_articles-keywords_static" st on t.articleid =
> st.articleid where st.keywordid = 1060 and t.articleid <> st.nativeid
> delete #tempTable where pk not in (select Max(pk) from #tempTable group by
> articleid)
>
> DELETE #TempTable WHERE (Link <> 0 AND Link IN (SELECT ArticleID FROM
> #TempTable))
> --Remove Links that have 'cousin links' in the result set
> --OR (ArticleID NOT IN
> --(SELECT TOP 1 ArticleID
> --FROM #TempTable
> -- GROUP BY Link, ArticleID
> --HAVING COUNT(Link) > 1) AND Link <> 0)
>
> declare @.resultcount int
> set @.resultcount = (select count(*) from #TempTable)
>
> declare @.sql nvarchar(500)
> set @.sql = 'delete #TempTable where articleid not in (SELECT top ' +
> @.quantity + ' articleid FROM #TempTable where pk not in (select top ' +
> @.start + ' pk from #TempTable order by ' + @.sort + ' , pk) ORDER BY ' + @.sort
> + ', pk) '
> EXEC sp_executesql @.sql
> SELECT t.priority, a.ArticleID,
> isnull(dbo.SF_PUB_GetAuthorByline(a.articleid),"") toptext,
> dbo.SF_Pub_GetArticlePath (a.articleid,@.YahooRoot) path, t.created,
> COALESCE (A.Title2, A2.Title2, A.Title1, A2.Title1) Title ,
> COALESCE(a.subtitle, a2.subtitle,'') subtitle,
> isnull(dbo.SF_PUB_GetInhKeywordID(a.articleid, 6913),0) DocType,
> COALESCE(A.Synopsis0, A2.Synopsis0, A.Synopsis1, A2.Synopsis1,
> A.Synopsis2, A2.Synopsis2, A.body, A2.Body, '') AS Synopsis
> FROM #TempTable t
> join pub_articles a on t.articleid = a.articleid left join
> pub_articles a2 on a.link = a2.articleid
> order by
> case when @.sort = 'priority' or @.sort = 'priority,created' then
> t.priority else '' end,
> case when @.sort = 'created' or @.sort = 'priority,created' then
> a.posteddate1 elsAgent message code 20046. Cannot use empty object or column
> names. Use a single space if necessary.
> [12/20/2005 4:44:00 PM]SERVER1.distribution: {call
> sp_MSadd_distribution_history(7, 6, ?, ?, 0, 0, 0.00, 0x01, 1, ?, 20, 0x01,
> 0x01)}
> Adding alert to msdb..sysreplicationalerts: ErrorId = 21,
> Transaction Seqno = 000275890000022200b100000001, Command ID = 20
> Message: Replication-Replication Distribution Subsystem: agent
> SERVER1-Keyword DB-SERVER2-7 failed. Cannot use empty object or column names.
> Use a single space if necessary.[12/20/2005 4:44:00 PM]SERVER1.distribution:
> {call sp_MSadd_repl_alert(3, 7, 21, 14151, ?, 20, N'SERVER1', N'Keyword DB',
> N'SERVER2', N'Keyword DB', ?)}
> ErrorId = 21, SourceTypeId = 5
> ErrorCode = '1038'
> ErrorText = 'Cannot use empty object or column names. Use a single space if
> necessary.'
> [12/20/2005 4:44:00 PM]SERVER1.distribution: {call sp_MSadd_repl_error(21,
> 0, 5, ?, N'1038', ?)}
> Category:SQLSERVER
> Source: SERVER2
> Number: 1038
> Message: Cannot use empty object or column names. Use a single space if
> necessary.
> [12/20/2005 4:44:00 PM]SERVER2.Keyword DB: exec dbo.sp_MSupdatelastsyncinfo
> N'SERVER1',N'Keyword DB', N'', 0, 6, N'Cannot use empty object or column
> names. Use a single space if necessary.'
> Disconnecting from Subscriber 'SERVER2'
> Disconnecting from Distributor 'SERVER1'
> Disconnecting from Distributor History 'SERVER1'
> Thanks,
> - Moshe

Friday, February 24, 2012

Error: unable to retrieve column information from the data source

Hi,

I am trying to set up a data flow task. The source is "SQL Command" which is
a stored procedure. The proc has a few temp tables that it outputs the final
resultset from. When I hit preview in the ole db source editor, I see the
right output. When I select the "Columns" tab on the right, the "Available
External Column List" is empty. Why don't the column names appear? What is
the work around to get the column mappings to work b/w source and
destination in this scenario.

In DTS previously, you could "fool" the package by first compiling the
stored procedure with hardcoded column names and dummy values, creating and
saving the package and finally changing the procedure back to the actual
output. As long as the columns remained the same, all would work.
Thats not working for me in SSIS.

Thanks in advance.
Asim.

Anyone?

|||I am having the same problem. I assume it is because the stored procedure I am using is long running (1min 20s) The designer looks like it starts to run the sproc to get the metadata, but then gives up after 15 or 30 seconds. Any solutions?|||

Hi,

If your final select statement is for a temporary table, u will get this problem. Use table variable or actual table instead of temp table.

|||

Yes, I figured out a solution. Basically create a hard coded resultset (select stmt) at the top of the procedure with the same columns as the actual resultset.

That did it for me.

Asim.

|||

Ok, that solution does not work. By adding the header, the package compiles and runs, but the row from the header is inserted in the destination, not from the (actual) second resultset. So I am back to square one.

Can anyone please help.

Asim.

|||This solved my problem. However, it was not enough to change the last select from a temp table to table variable. I had to change all occurences of temp tables to table variables. I also found that it sped up my query by a factor of 16x from 1min 20s to 5s.|||My workaround is to create output columns that are being returned by the query manually.

Error: The value was too large to fit in the output column "colName" (60).

Hi All,

I'm trying to transfer data from DB2 Database to SQL Server 2005.

Well, i used the OLE DB Source, the Data Conversion Component and the OLE DB Destination component.

I have five Data flows with this configuration above. But I am receiving an error message from one of them.

Please check below the error message:

"[Source Table TARTRATE [1]] Error: The value was too large to fit in the output column "ADJ_RATE_PCT" (60). "

"[Source Table TARTRATE [1]] Error: The "component "Source Table TARTRATE" (1)" failed because error code 0xC02090F8 occurred, and the error row disposition on "output column "ADJ_RATE_PCT" (60)" specifies failure on error. An error occurred on the specified object of the specified component."

Could you please help me with this issue?

Thanks in advance.

Thiago

The ADJ_RATE_PCT column in your data flow has a defined maximum width. You'll be able to find out what it is by double clicking the data-path leading from the source component.

What has happened is that a value has come in that is bigger than that defined maximum. So, you'll have to increase that maximum in the source component.

-Jamie

|||

Hi Jamie,

Thanks for your answer, but i still dont have a solution for this.

I tried to change the field width but i still have the same error message.

I think it is related with the field type. I received the same error message in another package and the field type is the same ( numeric , Precision 4 and Scale 2 ) as the first one.

I will continuing trying to figure out what is happening.

Thanks in advance.

Thiago

|||My guess is that, like Jamie said, a value is coming in that is bigger than the type. Based on the error message, and the type, it sounds like the value probably has a precision larger than 6. Have you tried increasing the precision (and possibly scale) of the output column in the advanced editor (inputs and outputs tab) of the source component?|||

Hi All,

Guys, thanks for your answer but I just don't know what to do now.

I changed the Precison (to 5) and Scale (to 3) in the Adavnced Editor following this sequence: Advanced Editor --> Input and Output Properties --> DataReader Output --> External Columns --> Select the column then change these two Data Types Properities: Precision and Scale. I tried to re-run the package and i still have the error message.

I saw that there is another "option" in the DataReader output menu called "Output Columns". I can't change any property in this menu. I guess these columns ( in the Ouptut Columns menu) are the return types from the source table ( im using a "select" command in a DB2 table ).

Another detail: in the "Output Columns" menu, the field that is returning error, has the precision 4 and Scale 2 ( remember: i cant change these values, they are automatically assigned by the component )). I think these values are comming from the source table. Am i right?

Thanks in advance for your help and atention.

Regards.
Thiago

|||

Hi Thiago,

Yes, in the DataReaderSrc you cannot change the properties of the output columns -- they are fixed at what the provider said the metadata was for the source column. Changing it in the External Columns will have no effect...

Your original post said you used the OLE DB Src -- if that is correct, you CAN change these properties there... Can you give that a try?

Thanks,
Mark

|||

Hi All,

Finally I find out what were happening in my application.

It was not related with the SQL2005 but with the DB2 ODBC Driver.

Follow below the instrunction how to fix this error in your DB2 ODBC Driver:

When you reconfigure your connection ( in the ODBC ), you have an Advanced button. So, you should click there and look for a Service Tab. In this service tab you have some "know workarounds".
There are two workarounds in this tab ( called PATCH 1 and PATCH2). In the second patch you have an option with this description: "Always use period for decimal separator in character output". You MUST select this option then type "15" in the value text box.

After that, just click ok and your DB2 ODBC driver will be configurated.

Thank you all for your answers.

See ya.

Thiago

Sunday, February 19, 2012

Error: The external metadata column collection is out of synchronization with the data source co

Hello,

I have a SSIS package with a Data Flow task. This task transfers the data from SQL Server 2000 to a table in SQL Server 2005.

I deployed and tested this package on the Test Server. Then put this package in a job and executed it - Works fine.

On the production server- If I execute the package through DTEXECUI, it works fine. But when I try executing it through a job- the job fails and it gives me following error:

Description: The external metadata column collection is out of synchronization with the data source columns. The "external metadata column "T_FieldName" (82)" needs to be removed from the external metadata column collection....

What I don't understand is, why are there no errors displayed when I execute the package through DTEXECUI.

Can anyone help me to resolve this issue.

Thanks.

JayaC wrote:

Hello,

I have a SSIS package with a Data Flow task. This task transfers the data from SQL Server 2000 to a table in SQL Server 2005.

I deployed and tested this package on the Test Server. Then put this package in a job and executed it - Works fine.

On the production server- If I execute the package through DTEXECUI, it works fine. But when I try executing it through a job- the job fails and it gives me following error:

Description: The external metadata column collection is out of synchronization with the data source columns. The "external metadata column "T_FieldName" (82)" needs to be removed from the external metadata column collection....

What I don't understand is, why are there no errors displayed when I execute the package through DTEXECUI.

Can anyone help me to resolve this issue.

Thanks.

I can only guess that it is looking at the wrong data source or something. This is most likely symptomatic of something else. Check that the account you are running SQL Agent as has got all the relevant permissions.

-Jamie

|||

Thanks Jamie,

Account under which the SQL Server Agent was running was causing the problem- Permissions Issue.

Wednesday, February 15, 2012

Error: Subreport could not be shown

I have a data dictionary report. It lists table & column definitions, then
has a subreport in the group footer to pull up dependent objects (the only
way I could get more than the first dependent object to list). The report
has parameters where you can choose to look at All Tables or individual
tables in the DB. It passes this param (@.tblName) to the subreport.
When I preview individual tables, the subreport works great! When I preview
"All Tables", the subreport works on some tables, and then has that error
listed after other tables. Even tables that it works on fine individually
can have this error message.
The data set for the subreport is called TableDepends and uses the following
code:
---
Declare @.objid int
If @.tblName <> 'All Tables'
Begin
Set @.objid = Object_ID(@.tblName)
if exists (select *
from sysdepends
where depid = @.objid)
begin
select distinct 'name' = (s.name + '.' + o.name), @.tblName as TableName,
type = substring(v.name, 5, 16)
from sysobjects o, master.dbo.spt_values v, sysdepends d,
sysusers s
where o.id = d.id
and o.xtype = substring(v.name,1,2) collate database_default
and v.type = 'O9T'
and d.depid = @.objid
and o.uid = s.uid
and deptype < 2
End
End
---
It's weird that this code works in some parts of the main report when I make
@.tblName = 'All Tables' in the main report, but then doesn't parse on other
parts of the main report. And I can't figure out how to do an ELSE clause
that still lists all table dependencies due to the whole Object_ID issue.
Any suggestions?
Thanks,Did a little more looking around. It appears that this error renders in the
Preview tab of Report Designer at Page 6 (and above) of 52 pages. The first
5 pages render correctly. So, I guess my question is whether or not the
Designer can handle all those re-renderings of the subreport.
On the first 5 pages, it doesn't matter if there's 1 dependency or many
dependencies, they all seem to render fine.
Thoughts?
Catadmin
--
MCDBA, MCSA
Random Thoughts: If a person is Microsoft Certified, does that mean that
Microsoft pays the bills for the funny white jackets that tie in the back?
@.=)
"Catadmin" wrote:
> I have a data dictionary report. It lists table & column definitions, then
> has a subreport in the group footer to pull up dependent objects (the only
> way I could get more than the first dependent object to list). The report
> has parameters where you can choose to look at All Tables or individual
> tables in the DB. It passes this param (@.tblName) to the subreport.
> When I preview individual tables, the subreport works great! When I preview
> "All Tables", the subreport works on some tables, and then has that error
> listed after other tables. Even tables that it works on fine individually
> can have this error message.
> The data set for the subreport is called TableDepends and uses the following
> code:
> ---
> Declare @.objid int
>
> If @.tblName <> 'All Tables'
> Begin
> Set @.objid = Object_ID(@.tblName)
> if exists (select *
> from sysdepends
> where depid = @.objid)
> begin
> select distinct 'name' = (s.name + '.' + o.name), @.tblName as TableName,
> type = substring(v.name, 5, 16)
> from sysobjects o, master.dbo.spt_values v, sysdepends d,
> sysusers s
> where o.id = d.id
> and o.xtype = substring(v.name,1,2) collate database_default
> and v.type = 'O9T'
> and d.depid = @.objid
> and o.uid = s.uid
> and deptype < 2
> End
> End
> ---
> It's weird that this code works in some parts of the main report when I make
> @.tblName = 'All Tables' in the main report, but then doesn't parse on other
> parts of the main report. And I can't figure out how to do an ELSE clause
> that still lists all table dependencies due to the whole Object_ID issue.
> Any suggestions?
> Thanks,
>|||Further information:
Finally found the ReportServer Error Logs. Here are the error messages I'm
getting:
----
aspnet_wp!processing!12f4!5/12/2005-10:30:34:: w WARN: Data source
'APACS_BASE': Report processing has been aborted.
aspnet_wp!processing!12f4!5/12/2005-10:30:34:: e ERROR: Throwing
Microsoft.ReportingServices.ReportProcessing.ProcessingAbortedException: An
error has occurred during report processing., ;
Info:
Microsoft.ReportingServices.ReportProcessing.ProcessingAbortedException: An
error has occurred during report processing. -->
System.InvalidOperationException: Invalid attempt to read when no data is
present.
at System.Data.SqlClient.SqlDataReader.GetOrdinal(String name)
at
Microsoft.ReportingServices.DataExtensions.DataReaderWrapper.GetOrdinal(String fieldName)
at
Microsoft.ReportingServices.DataExtensions.MappingDataReader..ctor(String
dataSetName, IDataReader sourceReader, String[] aliases, String[] fieldNames)
at Microsoft.ReportingServices.ReportProcessing.h..ctor(String A_0,
IDataReader A_1, String[] A_2, String[] A_3)
at Microsoft.ReportingServices.ReportProcessing.a1.v()
at Microsoft.ReportingServices.ReportProcessing.ax.c()
at Microsoft.ReportingServices.ReportProcessing.a1.a(Boolean& A_0)
at Microsoft.ReportingServices.ReportProcessing.ax.b()
at Microsoft.ReportingServices.ReportProcessing.a1.a(Object A_0)
-- End of inner exception stack trace --
----
This error comes up in the log for every time the subreport tries to print
and can't. Anyone have any ideas how to get around it?
Thanks!
--
MCDBA, MCSA
Random Thoughts: If a person is Microsoft Certified, does that mean that
Microsoft pays the bills for the funny white jackets that tie in the back?
@.=)
"Catadmin" wrote:
> I have a data dictionary report. It lists table & column definitions, then
> has a subreport in the group footer to pull up dependent objects (the only
> way I could get more than the first dependent object to list). The report
> has parameters where you can choose to look at All Tables or individual
> tables in the DB. It passes this param (@.tblName) to the subreport.
> When I preview individual tables, the subreport works great! When I preview
> "All Tables", the subreport works on some tables, and then has that error
> listed after other tables. Even tables that it works on fine individually
> can have this error message.
> The data set for the subreport is called TableDepends and uses the following
> code:
> ---
> Declare @.objid int
>
> If @.tblName <> 'All Tables'
> Begin
> Set @.objid = Object_ID(@.tblName)
> if exists (select *
> from sysdepends
> where depid = @.objid)
> begin
> select distinct 'name' = (s.name + '.' + o.name), @.tblName as TableName,
> type = substring(v.name, 5, 16)
> from sysobjects o, master.dbo.spt_values v, sysdepends d,
> sysusers s
> where o.id = d.id
> and o.xtype = substring(v.name,1,2) collate database_default
> and v.type = 'O9T'
> and d.depid = @.objid
> and o.uid = s.uid
> and deptype < 2
> End
> End
> ---
> It's weird that this code works in some parts of the main report when I make
> @.tblName = 'All Tables' in the main report, but then doesn't parse on other
> parts of the main report. And I can't figure out how to do an ELSE clause
> that still lists all table dependencies due to the whole Object_ID issue.
> Any suggestions?
> Thanks,
>