DTS exec error!
-
Hi guys!
I'm getting the following error on the DTS execution.
Does anyone have any idea how to solve this matter!
-
Your picture didn't show up.. can you describe the problem instead?
-
This is the SQL Statement the task on the DTS executes:
execute dm_conso.load_tmp_clients_affaires
This is the error that pops up:
Could not use view or function 'v_client_affaire' because of binding errors.
Invalid object name 'conso.conso.affaires'
Invalid object name 'conso.conso.clients'
--
This is the SQL on the sp: "load_tmp_clients_affaires" :
CREATE proc dm_conso.load_tmp_clients_affaires as
begin
INSERT into tmp_clients_affaires
select * from v_client_affaire
END
GO
--
When I open up the view: 'v_client_affaire',
I get the following error:
Invalid object name 'conso.conso.clients'
--
THX for your help!
[Bruno]
-
This is sctricly a guess here, but you're still not qualifying the objects with there owners... I'm really not sure that this is the problem here but it could be a place to start.
-
Are you saying I should classify all views and tables with the specific owners?
Should this be done with the syntax:
"owner.object"
btw, should the correct syntax be:
"db.owner.object"
on all objects?
--
THX!
-
owner.object works is how I do it.
dbo.tablename
-
you don't need to specify the dbname unless you are connection do northwind and want to get data from pubs.
It's considered a best pratice to ALWAYS specify the owner of the object, be it a view, table, stored proc or function.
-
I'm specifying the owner of the obbjects, still...it pops up the same error!
-
I can't help you any further than this... maybe some dts guru will be more fit to solve your problem than me.
-
It looks like you might be specifying the object in your view incorrectly. It looks like you are doing query on conso.conso.clients and maybe it should be conso.clients?
Post the syntax for your view so we can take a look.
-
THX for your help!
I've just seen your post, and it was exactly what I did prior to viewing your post!
The object reference on the view was "conso.conso.clients", and I changed it to "conso.dbo.clients", and now it's working!
BTW, should it work with just the reference: "clients"?
When should objects be fully referenced?
When can you use just a partial reference?
Well, at least this task on the DTS is runing, I'll get back to you with further errors on this particular DTS as they come up!
I'm currently working on location, and waiting for the DTS to run.
You guys have been great! Thank you all! I feel I can learn a lot from all of you!
Bruno aka extreme_newbie_dba
[]
-
You should always use owner.objectname. You only need to add the database name for cross database/cross server queries.
-
THX!
Has I suspected I've just stumbled on yet another error on the execution of the DTS.
Here's the problem:
Insert error: Column name or number of supplied values does not match table definition.
SQL Task:
execute load_indicadores1
--
SP:´
load_indicadores1
SQL:
CREATE proc dm_conso.load_indicadores1 as
begin
INSERT into indicadores
select
reference,
prenom,
nom,
adresse,
code_postal,
id_postal_code,
id_postal_code2,
cl_info_comp2,
cl_info_comp27,
cl_rub3,
cl_info_comp26,
cl_info_comp22,
cl_info_comp28,
cl_info_comp5,
cl_rub2,
cl_tel,
cl_rub1,
cl_email,
cl_info_comp6,
cl_info_comp3,
af_code,
af_date_cont,
af_cout_supl,
af_info_comp18,
af_cout_total,
sum_lhtc_qte,
n_dias_assunto,
NULL,
NULL,
NULL,
ap_code,
t15_code,
t2_code,
t8_code,
t13_code ,
t6_code ,
t10_code ,
t3_code,
t9_code ,
t14_code,
t7_code ,
t4_code,
cod_escalao_idade,
-1 ,
-1
from v_clients_sum_lhtc_qte
END
GO
--
Any ideas?
THX!
[]Bruno[]
-
Yes, you should always specify the column names for the insert before inserting the valu.
ex :
Insert into dbo.DummyTable (col1, col2, name) Select col1, col2, name from dbo.Table2.
That way sqlserver doesn't ahve to guess in what column to insert the values. It's considered a best pratice to do so.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply