Regarding Import table

[Former Member]

Hi Gurus,

     Actually I am facing an issue. When I am trying to Import a Template table into my database it is showing at the Datastore but when I am checking it in the Database in the specified Schema it is not available. Please help me to resolve this issue.

     When I am executing the JOB than it is throwing an error that invalid object name (Table_Name), Table is not found.

Please help me to resolve the issue.

Regards,

DJ

[former_member211387]

Hi

When you create a template table in a dataflow, the table does not get created into the underlying database targetted by the datastore. the table actually gets created the first time you execute the dataflow in a batch or realtime job. The property of the template table is that it will be dropped and re-created during every execution of the dataflow (batch or realtime). Each time after completion of dataflow in which the template table is the target, the table is created and data loaded (if any) as a result fo the execution of the dataflow.

So if you import the template table before executing the dataflow for even the first time, the table is not created in the target database. So subsequent execution of the dataflow after importing the template table would result in the error that the Table does not exist in the datastore. This applies to all schemas in the MS SQL Server.

As long as the alias you ahve specified in the datastore is valid, then I think your issue is to do with permissions. This will be the case if you are targetting any other schema other than dbo. please check with the dba to get the user you are connecting with to have db_owner privilege as well as create, alter, drop, delete and update privileges on the BODS schema. Normally db_owner should do the job but you never know.

kind regards

Raghu

[Former Member]

Hi Raghunathan,

Thanks for the reply actually I am using the SA user for creating the tables but still I am getting the same error I have imported the table even before running for the second time.

Regards,

DJ

[former_member187000]

Hi

What is the database type you are using to create datastore.? is it ODBC type. If Yes, Try changing the Parameterized SQL to No under datastore properties.

[akhileshkiran]

Hi

If you have problem with Schema name like if you want to use your own Schema name like BODS instead of DBO.

If you are facing the below error.

Login to your SQL Server Management Studio.

Expand Security ----> Logins ----> Select your associated Database User ----> Double Click or Right click and select Properties.

Go to User Mapping Tab. Scroll Down and Locate your Database mapped to the User. Change Default Schema as BODS.

Then you can use BODS as the Owner name in Template Table. You can import table easily.

If you browse through your DB.

I hope this will help you out

Regards,

Akhileshkiran.

[Former Member]

Thanks for the reply Akhilesh but i am facing some different issue i will post the error tommorow.

Regards,

DJ

[Former Member]

Hi Akhilesh ,

Below is the screen shot of the error I was getting please review it and tell me the possible reasons.

Thanks in Advance,

DJ

[akhileshkiran]

Hi

Once try to create table in SQL Server Management Studio using Query in your database.

CREATE TABLE [BODS].[Test](
    [ID] [int] NULL,
    [NAME] [varchar](30) NULL,
    [AGE] [int] NULL,
    [CITY] [varchar](30) NULL,
    [COUNTRY] [varchar](30) NULL,
    [DOJ] [date] NULL,
    [DOR] [date] NULL
) 

Note: if you get this below error

The specified schema name "BODS" either does not exist or you do not have permission to use it.

You can follow the above steps in the previous thread .

Regards,

Akhileshkiran

[Former Member]

Hi Akhilesh,

     Actually when I am creating any table in BODS schema in SQL server it is getting created and when I am trying to use also it is working fine but when I am trying to Import a template table into the same schema it no getting created in the database.

Thanks a Lot for you reply..

Regards,

DJ

[akhileshkiran]

Hi

Have you tried keeping default schema as BODS in MS SQL ?

Regards,

Akhileshkiran.

[former_member186897]

Hi Deepak Joshi

Do the followings, hope that it shall solve the issue.

1) Drop the table from the database which is failing.Ex:

DROP TABLE MULTIPLE_TAB_EXCEL_SHEET_TGT;

2) Once table dropped, remove the target template table from the dataflow (i.e. DF_EXTRACTSHEETNAME ) and re-create the target table with the same name under given database.

3) Save the Job and execute it.

Let us know if it works.

[Former Member]

Hi Mohd Shahanshah,

I have already tried what you said but i dont want to create this table in the database by wighting a code i want to import the table from BODS so how can I do that.

Regards,

DJ

[former_member200473]

Hi Deepak ,

You have two choices to handle this issue.

1- Delete this table from the datastore and recreate by drag and drop process.

2- This is a kind of BUG, you can use a script to create a table .

Please try and update the thread .

Regards,

Shiva Sahu

[Former Member]

Hi shiva,

     Can you tell me how to follow the second method as I tried the first one several times and I already It is giving problems in all the Schema other than DBO.

Regards,

DJ

[Former Member]

Hi,

By default the schema will be only DBO, you have to manually change it at the time of creation.

Arun

[former_member186897]

Hi Deepak Joshi,

Not sure if it is a problem with name. But you can always have a permanent table to overcome this issue. Try this if having template table is not a constraint. You can write a script to re-create this table if it does not exits so that once code promoted to any new environment it shall not fail.

Regards,

MSA

[former_member200473]

Hi Deepak Joshi

Kindly find below  and write into script before the your dataflow that uses/creates table.

$G_TableName = SQL('YOU_RDATASTORE_NAME' , 'select NAME FROM sys.objects WHERE name = 'YOUR_TABLENAME'\');

Note : above code will work for MS sql server.

Print (\'table name\'||$G_TableName );

if ($G_TableName is null)

begin

SQL('YOUR_DATASTORE_NAME','CREATE TABLE yOUR_TABLENAME(ID int,NAME varchar(255),ATTRIBUTE varchar(255))');

PRINT(\'TABLE CREATED\');

End

else

begin

SQL('YOUR_DATASTORE_NAME','DROP TABLE YOUR_TABLE_NAME');

print( ' TABLE DROPED\');

end

You can modify the code as per your requirement.

Regards,

Shiva Sahu

[former_member186897]

Try re-creating this template table in same database. If creates then execute the job. If not then delete from the specified datastore and then re-create this template table.

[Former Member]

Hello Mohd Shahanshah Ansari,

     Firstly, thanks for your Quick reply, I previously did the same thing but it is happening with the Schema which i am creating but in the case of DBO it is working fine I want the solution for this.

Regards,

DJ

[former_member186897]

Can you please share the error screen shot?