ETL : rows with Errors

I'm using a "Execute SQL Task" to run a stored procedure to populate a Fact table in our dimensional datawarehouse. There is one row which violates a foreign key constraint, which causes the entire task to fail so zero rows are loaded. Is there any way to grab the offending row and send it off to some holding ground and go ahead and load the rest of the rows.

I'm using Execute SQL Task mostly because I am very comfortable with writing SQL whereas the rest of SSIS is a bit unfamiliar to me, but I'm guessing that to handle error rows I might have to change to a different kind of task ?

Thanks

Richard

The best way to do this is to use a lookup in the dataflow to determine if the incoming record violates foreign key rules.

As far as your stored procedure goes, head over to the Transact-SQL forum for help with that.

estimate size of table based on number of rows

Hi everyone,
This might be a tough one, I don't know if this is doable.
Basically I want to create a stored procedure to return estimated size of a
table.
because I don't know how many rows it will have in the future, I want to
calcuate how much disk space it takes to have one row,
then multiply by number of rows I specified. (space calculation for index is
not necessary).
EXEC GetEstimateTableSize @.TableName='Table1', @.NumberOfRows ='3000000'
it'll return value in KBs after I execute proc. possible?If you run :
EXEC dbo.sp_spaceused table_name
You get the current space usage. Divide it by the current number of rows,
and multiply by the projected one.
If the table is completely empty or you'd rather calculate theoretical size,
there are formulas you can use from BOL, or better yet, a lengthy discussion
on internal structures and row sizes in Inside SQL Server 2000.
BG, SQL Server MVP
www.SolidQualityLearning.com
"Britney" <britneychen_2001@.yahoo.com> wrote in message
news:uWKIMMBpFHA.3936@.TK2MSFTNGP10.phx.gbl...
> Hi everyone,
> This might be a tough one, I don't know if this is doable.
> Basically I want to create a stored procedure to return estimated size of
> a table.
> because I don't know how many rows it will have in the future, I want to
> calcuate how much disk space it takes to have one row,
> then multiply by number of rows I specified. (space calculation for index
> is not necessary).
>
> EXEC GetEstimateTableSize @.TableName='Table1', @.NumberOfRows ='3000000'
>
> it'll return value in KBs after I execute proc. possible?
>
>
>
>
>
>|||Have you looked in the Books Online for sp_spaceused? It'll get you
part of the way there because it returns the rows used and the current
size of the table. Here's a quick and dirty stab at it; obviously
it'll need polishing:
This is actually a pretty useful idea; I'm planning on using this
myself.
Stu
DECLARE @.Table varchar(255)
DECLARE @.NumberOfRows int
SET @.Table = 'Splat'
SET @.NumberOfRows = 1
CREATE TABLE #t (name varchar(255),
rows int,
reserved varchar(100),
data varchar(100),
index_size varchar(100),
unused varchar(100))
--how big is the table now?
exec sp_spaceused @.Table
INSERT INTO #t
exec sp_spaceused @.Table
--strip off the ' KB' from the data column
--convert data and rows to decimal, and
--divide data by number of rows and multiply by number of anticipated
rows
SELECT rows, data, (data/rows) * @.NumberOfRows
FROM ( SELECT rows = CONVERT(decimal(32,3), rows),
data = CONVERT(decimal(32,3), LEFT(data, LEN(data)-3))
FROM #t) x
DROP TABLE #t
HTH,
Stu|||The following may help:
http://www.microsoft.com/downloads/...&displaylang=en
If you need it in a SP, try to understand the formulas used in the
spreadsheet and translate them in T-SQL (using the data from syscolumns
and other system tables).
Razvan|||I see 4 columns called reserved , index_size, unused, data
I guess I need to add 4 columns to get the total size, then divide by number
of row to find out how much disk space per row?
then disk space per row * estimate number of rows to find out estimate size?
"Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in message
news:etjHceBpFHA.2888@.TK2MSFTNGP10.phx.gbl...
> If you run :
> EXEC dbo.sp_spaceused table_name
> You get the current space usage. Divide it by the current number of rows,
> and multiply by the projected one.
> If the table is completely empty or you'd rather calculate theoretical
> size, there are formulas you can use from BOL, or better yet, a lengthy
> discussion on internal structures and row sizes in Inside SQL Server 2000.
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
>
> "Britney" <britneychen_2001@.yahoo.com> wrote in message
> news:uWKIMMBpFHA.3936@.TK2MSFTNGP10.phx.gbl...
>|||You need the data + index_size.
Reserved includes: data + index_size + unused.
BG, SQL Server MVP
www.SolidQualityLearning.com
"Britney" wrote:

> I see 4 columns called reserved , index_size, unused, data
> I guess I need to add 4 columns to get the total size, then divide by numb
er
> of row to find out how much disk space per row?
> then disk space per row * estimate number of rows to find out estimate siz
e?
> "Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in messa
ge
> news:etjHceBpFHA.2888@.TK2MSFTNGP10.phx.gbl...
>
>|||but I have a NTEXT column,
I don't think it can calculate NTEXT.
"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:1124386169.690259.289640@.z14g2000cwz.googlegroups.com...
> The following may help:
> http://www.microsoft.com/downloads/...&displaylang=en
> If you need it in a SP, try to understand the formulas used in the
> spreadsheet and translate them in T-SQL (using the data from syscolumns
> and other system tables).
> Razvan
>|||Indeed, ntext columns are not covered by the spreadsheed.
In the DataSizer.doc file, they wrote:
The tool does not include the formula to estimate the size of a table
that has Text columns. Not NULL text values consume 16 bytes in the
data row and have a minimum size of 84 bytes on the text page. Text
values are packed onto text pages with the same algorithm as data
rows so it should be possible to estimate the size of text data
storage using the HEAP table spreadsheet if you know the average size
of your text values.
Razvan|||if I use sp_spaceused 'tablename' command,
if that table has a few NText Columns,
I think it will calculate total spaces including disk spaces for NTEXT
column, correct?
"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:1124483366.622335.202490@.g49g2000cwa.googlegroups.com...
> Indeed, ntext columns are not covered by the spreadsheed.
> In the DataSizer.doc file, they wrote:
> The tool does not include the formula to estimate the size of a table
> that has Text columns. Not NULL text values consume 16 bytes in the
> data row and have a minimum size of 84 bytes on the text page. Text
> values are packed onto text pages with the same algorithm as data
> rows so it should be possible to estimate the size of text data
> storage using the HEAP table spreadsheet if you know the average size
> of your text values.
> Razvan
>

Esql/C calling stored procedure with output parameters

I'm trying to write an esqlc program that will run a stored procedure that returns several output parameters. I haven't been able to find any documentation to date that explains how to run the "EXEC SQL EXECUTE procname" command and specify the output parameters.

My stored procedure "aek_proc1" takes one input parameter (p1 - an 8-character string) and 3 output parameters (p2 - an integer; p3 - an 8-character string, and p4 a 40-character string).

My esqlc program contains the following code.

EXEC SQL BEGIN DECLARE SECTION;
char p1[9];
int p2;
char p3[9];
char p4[41];
WXEC SQL END DECLARE SECTION;

sprintf(&p1[0], "GL");
p2 = 0;
sprintf(&p3[0], "");
sprintf(&p4[0], "");

EXEC SQL EXECUTE aek_proc1 :p1,
:p2 OUTPUT,
:p3 OUTPUT,
:p4 OUTPUT;

I am getting errors at runtime about constants being passed for OUTPUT parameters.

I can run the same stored procedure in Query Analyser and it works beautifully (see below)

declare @.p1 char(8)
declare @.p2 integer
declare @.p3 char(8)
declare @.p4 char(40)

set @.p1 = 'GL'

execute aek_proc1 @.p1, @.p2 output, @.p3 output, @.p4 output

select @.p1 p1, @.p2 p2, @.p3 p3, @.p4 p4

Any idea what I'm doing wrong or how it should be coded?

I'd really appreciate any advice you can offer!!

I've spend hours browsing this newsgroup and found lots of examples of how to do this in VB and from Query Analyser but I can't find any examples for ESQL/C that work.

So, please help!!!

Thanks,

AllanK :rolleyes:Oops.

Don't know why the host variable names got turned into smileys, but that bit should have read...

EXEC SQL EXECUTE aek_proc1 :p1,
:p2 OUTPUT,
:p3 OUTPUT,
:p4 OUTPUT;|||Have you tried to replace : with @.?|||I tried changing the EXEC SQL EXEC command in the ESQL/C program to read...

EXEC SQL EXEC aek_proc1 @.p1, @.p2 OUTPUT, @.p3 OUTPUT, @.p4

after which I got the runtime error...

"0137- Must declare the variable '@.p1'."

I tried adding...

EXEC SQL DECLARE @.p1 char(8);
EXEC SQL DECLARE @.p2 int;
EXEC SQL DECLARE @.p3 char(8);
EXEC SQL DECLARE @.p4 char(40);

EXEC SQL SET @.p1 = 'GL';
EXEC SQL SET @.p2 = 0;
EXEC SQL SET @.p3 = '';
EXEC SQL SET @.p4 = '';

EXEC SQL EXEC aek_proc1 @.p1, @.p2 OUTPUT, @.p3 OUTPUT, @.p4 OUTPUT;

but the error persists.

Any further suggestions?

Thanks,

AllanK

Errror 8144

I get following error
Server: Msg 8144, Level 16, State 2, Procedure pPMEmployeeUpdate, Line 0
[Microsoft][ODBC SQL Server Driver][SQL Server]Procedure or function
pPMEmployeeUpdate has too many arguments specified.
Any Ideas Why can it appear?
Following are NOT the reason:
1. C# code is correct
The error appears even if I debug the procedure in QA with the same error.
Thanks
ShimonI suggest that you post the TSQL signature of the procedure and use a Profil
er trace to catch the
call of the procedure and post that as well. It sounds like you have called
the procedure with more
parameters than was defined in the CREATE PROC statement.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Shimon Sim" <estshim@.att.net> wrote in message news:%23a5b7MnGFHA.1740@.TK2MSFTNGP09.phx.gb
l...
>I get following error
> Server: Msg 8144, Level 16, State 2, Procedure pPMEmployeeUpdate, Line 0
> [Microsoft][ODBC SQL Server Driver][SQL Server]Procedure or function pPMEmployeeUpdat
e has too
> many arguments specified.
> Any Ideas Why can it appear?
> Following are NOT the reason:
> 1. C# code is correct
> The error appears even if I debug the procedure in QA with the same error.
> Thanks
> Shimon
>|||You have given more parameters than required
Madhivanan|||Thanks,
create PROCEDURE pPropertyManagerUpdate
(
@.PropertyManagerID int,
@.FirstName varchar( 25 ),
@.LastName varchar( 25 ),
@.Phone varchar( 15 ),
@.Fax varchar( 15 ),
@.WirelessPhone varchar( 15 ),
@.Email varchar( 25 ),
@.UserName varchar( 25 ),
@.Password binary( 24 )
)
AS
UPDATE PropertyManager
SET FirstName = @.FirstName, LastName = @.LastName, Phone = @.Phone,
Fax = @.Fax, WirelessPhone = @.WirelessPhone, Email =
@.Email,
UserName = @.UserName, [Password] = @.Password
WHERE PropertyManagerID = @.PropertyManagerID
RETURN
GO
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uxjsVVnGFHA.4032@.TK2MSFTNGP12.phx.gbl...
>I suggest that you post the TSQL signature of the procedure and use a
>Profiler trace to catch the call of the procedure and post that as well. It
>sounds like you have called the procedure with more parameters than was
>defined in the CREATE PROC statement.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Shimon Sim" <estshim@.att.net> wrote in message
> news:%23a5b7MnGFHA.1740@.TK2MSFTNGP09.phx.gbl...
>|||It is imporsible to do if you debug SP in QA.
Thanks
Shimon
<madhivanan2001@.gmail.com> wrote in message
news:1109252093.459864.275520@.l41g2000cwc.googlegroups.com...
> You have given more parameters than required
> Madhivanan
>|||Shimon,
The Error is for the sp pPMEmployeeUpdate and you
posted the code for pPropertyManagerUpdate !!!
BTW, do you have a trigger on the PropertyManager table?
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Shimon Sim" <estshim@.att.net> wrote in message
news:%23h7dQbnGFHA.3912@.TK2MSFTNGP10.phx.gbl...
> Thanks,
> create PROCEDURE pPropertyManagerUpdate
> (
> @.PropertyManagerID int,
> @.FirstName varchar( 25 ),
> @.LastName varchar( 25 ),
> @.Phone varchar( 15 ),
> @.Fax varchar( 15 ),
> @.WirelessPhone varchar( 15 ),
> @.Email varchar( 25 ),
> @.UserName varchar( 25 ),
> @.Password binary( 24 )
> )
> AS
> UPDATE PropertyManager
> SET FirstName = @.FirstName, LastName = @.LastName, Phone = @.Phone,
> Fax = @.Fax, WirelessPhone = @.WirelessPhone, Email =
> @.Email,
> UserName = @.UserName, [Password] = @.Password
> WHERE PropertyManagerID = @.PropertyManagerID
> RETURN
> GO
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in message news:uxjsVVnGFHA.4032@.TK2MSFTNGP12.phx.gbl...
>|||My best guess is that you are calling pPMEmployeeUpdate
From the Insert trigger for the table PropertyManager.
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Shimon Sim" <estshim@.att.net> wrote in message
news:eravnbnGFHA.2616@.tk2msftngp13.phx.gbl...
> It is imporsible to do if you debug SP in QA.
> Thanks
> Shimon
> <madhivanan2001@.gmail.com> wrote in message
> news:1109252093.459864.275520@.l41g2000cwc.googlegroups.com...
>|||Wow
How come I didn't notice that.
Thanks, let me look again.
Shimon.
"Roji. P. Thomas" <thomasroji@.gmail.com> wrote in message
news:ees1thnGFHA.3088@.tk2msftngp13.phx.gbl...
> Shimon,
> The Error is for the sp pPMEmployeeUpdate and you
> posted the code for pPropertyManagerUpdate !!!
> BTW, do you have a trigger on the PropertyManager table?
>
> --
> Roji. P. Thomas
> Net Asset Management
> https://www.netassetmanagement.com
>
> "Shimon Sim" <estshim@.att.net> wrote in message
> news:%23h7dQbnGFHA.3912@.TK2MSFTNGP10.phx.gbl...
>

Errors using sp_refreshview

Hi ... I hope someone can explain this. Some time ago we created a stored
procedure that loops through all SPs and Views and refreshes them. Yesterday
we noticed a problem where two specific views would not refresh (don't seem
to have the problem with SPs and sp_recompile). After manually dropping and
recreating the view and then rerunning the proc then worked without error.
Of course it wasn't necessary but we wanted to retest the main SP running the
refresh process.
Of course, nothing else (that we can see) changed. In addition, we have
several deployments of the same database structure, and this did NOT happen
in all databases ... in fact, in most deployments worked without error. On
our dev server there are several databases, all (supposedly) the same
structure and since on the same DB server it is of course the same version of
SQLServer. Yet we had the failure on two separate tables in two separate
databases (unknown if it was the same tables in the two databases).
We're still evaluating of course, but in the meanwhile ... any ideas'
Thanks in advance ...
--
Brad AshforthHi,
Did u checked the log why it happend like this as u explained .
Have u scheduled a job to execute ur SP.
From
Doller
Brad Ashforth wrote:
> Hi ... I hope someone can explain this. Some time ago we created a stored
> procedure that loops through all SPs and Views and refreshes them. Yesterday
> we noticed a problem where two specific views would not refresh (don't seem
> to have the problem with SPs and sp_recompile). After manually dropping and
> recreating the view and then rerunning the proc then worked without error.
> Of course it wasn't necessary but we wanted to retest the main SP running the
> refresh process.
> Of course, nothing else (that we can see) changed. In addition, we have
> several deployments of the same database structure, and this did NOT happen
> in all databases ... in fact, in most deployments worked without error. On
> our dev server there are several databases, all (supposedly) the same
> structure and since on the same DB server it is of course the same version of
> SQLServer. Yet we had the failure on two separate tables in two separate
> databases (unknown if it was the same tables in the two databases).
> We're still evaluating of course, but in the meanwhile ... any ideas'
> Thanks in advance ...
> --
> Brad Ashforth|||Hi Brad,
Welcome to use MSDN Managed Newsgroup!
From your descriptions, I understood it happened to failed refreshing for
two of your views via sp_refreshview. If I have misunderstood your
concern, please feel free to point it out.
It seems strange and it seems you have fixed this by dropping and
recreating the Views. Could you find any more related information in Error
Log?
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Michael ... by Error Log I'm assuming you mean the SQL Server Error Log,
under the Management folder in Enterprise Manager. There is no entry in the
error log for this (at least no entry that mentions the databases in
question). Is it possible that the server is not configured to report errors
at this level? If so, how can I configure it to report these types of errors
should they occur again? We are concerned because we have seen this error not
only on our Dev server but on two different client production servers.
Thank you for your assistance ...
--
Brad Ashforth
"Michael Cheng [MSFT]" wrote:
> Hi Brad,
> Welcome to use MSDN Managed Newsgroup!
> From your descriptions, I understood it happened to failed refreshing for
> two of your views via sp_refreshview. If I have misunderstood your
> concern, please feel free to point it out.
> It seems strange and it seems you have fixed this by dropping and
> recreating the Views. Could you find any more related information in Error
> Log?
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||Hi Brad,
This seems to be related to a known issue of us when the views are updated
in not dependent order. Please use sp_refreshview to update views in
dependent order.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Michael ... Thank you for your input. The next time it occurs, we will try
to determine if the view is dependent on other views and if so will try to
refresh again (assuming the view that it was dependent on was refreshed later
in the 1st attempt).
--
Brad Ashforth
"Michael Cheng [MSFT]" wrote:
> Hi Brad,
> This seems to be related to a known issue of us when the views are updated
> in not dependent order. Please use sp_refreshview to update views in
> dependent order.
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||Hi Brad,
You are welcome to reply here whenever it occurs again. Thank you for your
patience and cooperation.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.

errors in storedprocedures

Is there a function that will indicate if there is an error in your stored procedure? Like for example, I want to create a stored procedure and if there is an error I want it to print out an error. How can I do this?It' called the query analyzer and there are 2 options the parse button and the execute button.|||Thanks, I am aware of the query panel. Are you suggestion that there isn't anything else? I know you can use the SQL server agent or DTS to do some error handling on your job but I wanted to know if there was a function you could add similar to the onerror function in visual basic.|||Check @.@.error in BOL.|||Sure

http://weblogs.sqlteam.com/brettk/archive/2004/05/25/1378.aspx

error-checking on INSERT

I'm writing a simple stored procedure in which I use INSERT/VALUES.
What is the best way to do error-checking to ensure that my rows got
written to the table? Is checking @.@.rowcount the best way?Check @.@.ERROR = 0 directly after the INSERT.
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"Rick Charnes" <rickxyz--nospam.zyxcharnes@.thehartford.com> wrote in message
news:MPG.1ee281022fb6137798993b@.msnews.microsoft.com...
> I'm writing a simple stored procedure in which I use INSERT/VALUES.
> What is the best way to do error-checking to ensure that my rows got
> written to the table? Is checking @.@.rowcount the best way?|||http://www.sommarskog.se/error-handling-I.html
Madhivanan

Error:Procedure or function CatalogJSummary has too many arguments specified

hello, all

I am using a Stored procedure and calling this in my code in C#.

Here is my procedure:

CatalogJSummary

(

@.SeriesId INT

)

AS

SELECT Games.GameName, Games.Id AS GameId

FROM GameCodes INNER JOIN

Games ON GameCodes.GameId = Games.Id INNER JOIN

MobileSeries ON GameCodes.SeriesId = MobileSeries.Id INNER JOIN

Mobiles ON MobileSeries.Id = Mobiles.SeriesId

GROUP BY Games.GameName, Games.Id, MobileSeries.Id

HAVING (MobileSeries.Id = @.SeriesId)

RETURN

and here is the calling C# code::

public DataSet CatalogJSummary(int sid)

{

SqlConnection cnUnique=new SqlConnection();

cnUnique.ConnectionString=System.Configuration.ConfigurationSettings.AppSettings["jconnection"];

//con.ConnectionString=System.Configuration.ConfigurationSettings.AppSettings["jconnection"];

com.CommandText="CatalogJSummary";

com.CommandType=CommandType.StoredProcedure;

com.Parameters.Add("@.SeriesId",SqlDbType.Int);

com.Parameters["@.SeriesId"].Value=sid;

com.Connection=cnUnique;

// con.Open();

cnUnique.Open();

adp=new SqlDataAdapter();

adp.SelectCommand=com;

DataSet ds1=new DataSet();

adp.Fill(ds1);

cnUnique.Close();

return ds1;

}

but when this code execute,with the parameter passed to stored procedure using this code at runtime, i get the following error::

Procedure or function CatalogJSummary has too many arguments specified.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Procedure or function CatalogJSummary has too many arguments specified.

Source Error:

Line 243:adp.SelectCommand=com; Line 244:DataSet ds1=new DataSet(); Line 245:adp.Fill(ds1); Line 246:cnUnique.Close(); Line 247:return ds1;

Source File: c:\inetpub\wwwroot\mobmasti.catalog\classes\truetonescls.cs Line: 245

Anyone please help me to resolve this error

thanks in advance

There are many options you can try:

1. From SQL Server books online:
If the first three characters of the procedure name are sp_, SQL Server
searches the master database for the procedure. If no qualified procedure name is provided, SQL Server searches for the procedure as if the owner name is dbo. To resolve the stored procedure name as a user-defined stored procedure with the same name as a system stored procedure, provide the fully qualified procedure name.

2. Make sure the field names between your calls and the procedure are the same

3. Make sure you attached the database with the user it was intended to be used with. Otherwise you may not have sufficient rights and this message will show up.

Error:208 [SQL Server] Invalid Object Name

Hello
I was adding a line to a store procedure in MS SQL 6.5 when I saved the server gave an error about the stored procedure being more than 255 lines after my edit. I selected NO to save changes but when the Enterprise Manager windows refreshed the stored procedure was gone from the list
I had backed up the text of the SP in a text file so I open a new SP windows, pasted in the text, but received the Error: 208 Invalid Object Name error when I tried to hit save. Rebooted the server...same issue. I have checked at least 10 times and SP is not listed. I have modified nothing about this SP so would think it would just 'go back in.
Anyhelp would be greatly appreciated
Thanks in advance.BK,
Try running a DROP PROCEDURE [myProcName] to see if that wipes it out before
you attempt to create it again.
James Hokes
"BK" <bakzzkk@.charter.net> wrote in message
news:2248F174-8440-442F-8F09-031A679ADE52@.microsoft.com...
> Hello,
> I was adding a line to a store procedure in MS SQL 6.5 when I saved the
server gave an error about the stored procedure being more than 255 lines
after my edit. I selected NO to save changes but when the Enterprise
Manager windows refreshed the stored procedure was gone from the list.
> I had backed up the text of the SP in a text file so I open a new SP
windows, pasted in the text, but received the Error: 208 Invalid Object Name
error when I tried to hit save. Rebooted the server...same issue. I have
checked at least 10 times and SP is not listed. I have modified nothing
about this SP so would think it would just 'go back in.'
> Anyhelp would be greatly appreciated.
> Thanks in advance.|||=?Utf-8?B?Qks=?= (bakzzkk@.charter.net) writes:
> I was adding a line to a store procedure in MS SQL 6.5 when I saved the
> server gave an error about the stored procedure being more than 255
> lines after my edit. I selected NO to save changes but when the
> Enterprise Manager windows refreshed the stored procedure was gone from
> the list.
> I had backed up the text of the SP in a text file so I open a new SP
> windows, pasted in the text, but received the Error: 208 Invalid Object
> Name error when I tried to hit save. Rebooted the server...same issue.
> I have checked at least 10 times and SP is not listed. I have modified
> nothing about this SP so would think it would just 'go back in.'
So which name did SQL Server list in the 208 message? I would guess
that you are trying to create the procedure in the wrong database, and
one or more tables that the procedure refers to does not exist in this
database.
You could also try to create the procedure through a query window.
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp|||Hi,
Before creating the procedure , can you login to ISQLW and run the below
select statement on your database
select name from sysobjects where type='p' and name ='name of the procedure'
Incase if the statement return values , use drop proc <proc name> to drop.
After this cut and paste the procedure in the same window and create the
procedure.
Thanks
Hari
MCDBA
"Erland Sommarskog" <sommar@.algonet.se> wrote in message
news:Xns94657977BE41Yazorman@.127.0.0.1...
> =?Utf-8?B?Qks=?= (bakzzkk@.charter.net) writes:
> > I was adding a line to a store procedure in MS SQL 6.5 when I saved the
> > server gave an error about the stored procedure being more than 255
> > lines after my edit. I selected NO to save changes but when the
> > Enterprise Manager windows refreshed the stored procedure was gone from
> > the list.
> >
> > I had backed up the text of the SP in a text file so I open a new SP
> > windows, pasted in the text, but received the Error: 208 Invalid Object
> > Name error when I tried to hit save. Rebooted the server...same issue.
> > I have checked at least 10 times and SP is not listed. I have modified
> > nothing about this SP so would think it would just 'go back in.'
> So which name did SQL Server list in the 208 message? I would guess
> that you are trying to create the procedure in the wrong database, and
> one or more tables that the procedure refers to does not exist in this
> database.
> You could also try to create the procedure through a query window.
>
> --
> Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp|||A common reason for this in SQL 6.5 is that the sp refers to a temp table...
If it does, you must create the temp table, then create the sp, then drop
the temp table to get the sp created.
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"BK" <bakzzkk@.charter.net> wrote in message
news:2248F174-8440-442F-8F09-031A679ADE52@.microsoft.com...
> Hello,
> I was adding a line to a store procedure in MS SQL 6.5 when I saved the
server gave an error about the stored procedure being more than 255 lines
after my edit. I selected NO to save changes but when the Enterprise
Manager windows refreshed the stored procedure was gone from the list.
> I had backed up the text of the SP in a text file so I open a new SP
windows, pasted in the text, but received the Error: 208 Invalid Object Name
error when I tried to hit save. Rebooted the server...same issue. I have
checked at least 10 times and SP is not listed. I have modified nothing
about this SP so would think it would just 'go back in.'
> Anyhelp would be greatly appreciated.
> Thanks in advance.|||Hi Brandon
Can you try using isqlw instead of EM to create the proc from your saved
text file.
If that doesn't help, can you post the text of the procedure?
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"BK" <bakzzkk@.charter.net> wrote in message
news:2248F174-8440-442F-8F09-031A679ADE52@.microsoft.com...
> Hello,
> I was adding a line to a store procedure in MS SQL 6.5 when I saved the
server gave an error about the stored procedure being more than 255 lines
after my edit. I selected NO to save changes but when the Enterprise
Manager windows refreshed the stored procedure was gone from the list.
> I had backed up the text of the SP in a text file so I open a new SP
windows, pasted in the text, but received the Error: 208 Invalid Object Name
error when I tried to hit save. Rebooted the server...same issue. I have
checked at least 10 times and SP is not listed. I have modified nothing
about this SP so would think it would just 'go back in.'
> Anyhelp would be greatly appreciated.
> Thanks in advance.

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: Timeout Expired

I have an Update stored procedure that is used to update four tables at the same time. The issue is that it works perfect when i run the application in local server,but when i upload the application on to the server that is located in U.S, it gives an error "System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding."

I think the sqlCommand is timing out and the value is not returned. Is there a workaround to this issue? What could be the reason for this?

Any ideas.. Please help..

Do any of the tables you are tying to update have indexes on them? If not, adding indexes might help.

Ryan

|||How long will it takes to complete update on the local? Try to set the SqlCommand.CommandTimeout (default to 30 secs) to a larger value, as well as the SqlConnection.ConnectionTimeout.|||I haven't given any indexes.. But usually indexes are helpful when you perform a search on the data , rt? Will it help if while i update a table.. Im not sure.. could you give me more info on this? pls..|||I tried specifying the CommandTimeout to a larger value (4 minutes) and it still times out.. Is there a diff approach?|||

Developer_.NET:

I haven't given any indexes.. But usually indexes are helpful when you perform a search on the data , rt? Will it help if while i update a table.. Im not sure.. could you give me more info on this? pls..

Seehttp://www.odetocode.com/Articles/70.aspx.

Ryan