create table query 1

[bunmiA]

I am writing a series of sql queries which go thru the following steps.
1. create the data base using a date extension..
2. create a number of tables in the database created in step 1
3. populate the tables created in step 2

The problem I keep having is, even when I try to create the tables within a dynamic sql command...so that I can have a 'use' which uses the new database created...., the tables get created in the master database.. I think this might be because the query which creates the tables is held on the master db, and the sql job which runs all the steps runs step 2 using the master database... how do I force this query to use the new database created?????? I tried doing it through a batch job so that I create the database name as a command line variable, and then call the 'create table' query by doing -q ''master...creattablequery'' but it still creates the tables on master.... Does anyone know how I can achieve my main objective... which is to create the tables in the correct database (created in a variable).

 

[donutman]

I don't know if this will work, but give it a shot. Put the 'Use database' statement at the end of the first step instead of at the beginning of the 2nd step.
-Karl

 

[bunmiA]

Thanks karl... but this doesnt work... I have found a work around which is to run the query with the db name i.e.

'create ' + @dbname + '..tablename etc.
this is all run as dynamic sql. The problem I am having is, the table being created is more than 8000 characters long...(defined as a sql command). The table has over 200 fields. which means I have to split the dynamic sql command in half... i.e
set @comm1 = 'create ' + @dbname + '..tablename etc.
field1 char()2),
.
.
set @comm2 = 'field100 int,

comm3 = @comm1 + @comm2
execute(@comm3)

this doesnt work.. it doesnt seem to like the concatenation.... does anyone have any ideas on this @ all!!!... I've been trying to sort this for a few days now..

 

[Pattycake245]

Unless you were trying to save typing in your question, the first problem is that you create statement is missing the table keyword:

set @comm1 = 'create table ' + @dbname + '..tablename etc.
field1 char()2),

If you were trying to save typing ignore this.

For varchar strings, they need to be executed differently instead of execute(@comm3). Take the following example for instance:

declare @SQLstring varchar(4000), @SQLString2 varchar(4000), @dbname nvarchar(30)
set @dbname = 'databasename'

select @SQLString = N'create table '+@dbname+'.dbo.testtable(field1 int, field2 int,'
select @SQLString2 = N' field3 int, field4 int)'
print @SQLString + @SQLString2
exec('sp_executesql N''''' + @SQLString + '' + @SQLString2+'')

This will create a table with four fields in it.

Hope this helps, Tim

 

[bunmiA]

Thanks Tim....

I get the following error
Implicit conversion from data type ntext to varchar is not allowed. Use the CONVERT function to run this query.?>?

 

[Pattycake245]

Can you post some or all of your code please?

Tim

 

[bunmiA]

Thanks Tim...I've ammended it a bit, so I no longer get the ntext error... Here is the actual code... and the new error


declare @vcdbname varchar(14),
@sql_command1 varchar(18),
@sql_command2 varchar(8000),
@sql_command3 varchar(8000)

set @vcdbname = 'offline' + '_' + convert(char(6),getdate(),112)
set @sql_command1 = 'use '+ @vcdbname

set @sql_command2 =
'if exists (select 1 from sysobjects where id = object_id(N' + '''' + 'tm_trans'
+ '''' + ') and objectproperty(id, N'+ ''''+ 'IsUserTable' + '''' + ') = 1)
drop table ' + @vcdbname + '..tm_trans
create table ' + @vcdbname + '..tm_trans
(
tran_nr int not null ,
totals_processing int not null ,
state int not null ,
msg_class int not null ,
msg_type int not null ,
draft_capture int not null ,
stand_in int not null ,
source_node varchar (12) null ,
source_node_key varchar (32) null ,
source_node_sys_trace char (6) null ,
source_node_settlement_entity int not null ,
source_node_batch int not null ,
source_node_batch_exception int not null ,
source_node_date_settlement char (4) null ,
source_node_amount_requested float not null ,
source_node_amount_approved float not null ,
source_node_amount_final float not null ,
source_node_cash_requested float not null ,
source_node_cash_approved float not null ,
source_node_cash_final float not null ,
source_node_fee float not null ,
source_node_fee_proc float not null ,
source_node_currency_code char (3) null ,
source_node_conversion_rate char (8) null ,
source_node_date_conversion char (4) null ,
source_node_original_data char (42) null ,
source_node_echo_data varchar (255) null ,
source_node_additional_data varchar (255) null ,
sink_node varchar (12) null ,
sink_node_req_sys_trace char (6) null ,
sink_node_rev_sys_trace char (6) null ,
sink_node_adv_sys_trace char (6) null ,
sink_node_settlement_entity int not null ,
sink_node_batch int not null ,
sink_node_batch_exception int not null ,
sink_node_date_settlement char (4) null ,
sink_node_amount_requested float not null ,
sink_node_amount_approved float not null ,
sink_node_amount_final float not null ,
sink_node_cash_requested float not null ,
sink_node_cash_approved float not null ,
sink_node_cash_final float not null ,
sink_node_fee float not null ,
sink_node_fee_proc float not null ,
sink_node_currency_code char (3) null ,
sink_node_conversion_rate char (8) null ,
sink_node_date_conversion char (4) null ,
sink_node_original_data char (42) null ,
sink_node_echo_data varchar (255) null ,
control_node varchar (12) null ,
totals_group varchar (12) null ,
pan varchar (19) not null ,
tran_type char (2) null ,
from_account char (2) null ,
to_account char (2) null ,
amount_tran_requested float not null ,
amount_tran_approved float not null ,
amount_tran_final float not null ,
amount_cash_requested float not null ,
amount_cash_approved float not null ,
amount_cash_final float not null ,
gmt_date_time char (10) null ,
time_local char (6) null ,
date_local char (4) null ,
expiry_date char (4) null ,
merchant_type char (4) null ,
pos_entry_mode char (3) null ,
pos_condition_code char (2) null ,
pos_pin_capture_code char (2) null ,
auth_id_rsp_length char (1) null ,
fee_tran float not null ,
fee_tran_proc float not null ,
acquiring_inst varchar (11) null ,
forwarding_inst varchar (11) null ,
track2_data varchar (37) null ,
ret_ref_no char (12) null ,
auth_id_rsp char (6) null ,
rsp_code_req_rsp char (2) null ,
rsp_code_cmp char (2) null ,
rsp_code_rev char (2) null ,
service_restriction_code char (3) null ,
card_acceptor_term_id char (8) null ,
card_acceptor_id_code char (15) null ,
card_acceptor_name_loc char (40) null ,
additional_rsp_data varchar (25) null ,
currency_code_tran char (3) null ,
amount_available float not null ,
ledger_balance float not null ,
auth_life_cycle char (3) null ,
authorising_inst varchar (11) null ,
extended_payment_code char (2) null ,
payee char (25) null ,
receiving_inst varchar (11) null ,
account_id_1 varchar (28) null ,
account_id_2 varchar (28) null ,
pos_data_code char (15) null ,
pos_data char (22) null ,
service_station_data char (73) null ,
authorisation_reason char (1) null ,
authorisation_type char (1) null ,
check_data varchar (50) null ,
msg_reason_code_req_in char (4) null ,
msg_reason_code_req_out char (4) null ,
msg_reason_code_rev char (4) null ,
msg_reason_code_adv char (4) null ,
terminal_owner varchar (25) null ,
pos_geographic_data char (17) null ,
sponsor_bank char (8) null ,
address_verification_data varchar (29) null ,
address_verification_result char (1) null ,
abort_time datetime null ,
abort_reason int null ,
abort_state int null ,
abort_rsp_code char (2) null ,
'
set @sql_command3 =
'in_req datetime null ,
in_req_rsp datetime null ,
in_cmp datetime null ,
in_cmp_rsp datetime null ,
in_adv datetime null ,
in_adv_rsp datetime null ,
in_rev datetime null ,
in_rev_rsp datetime null ,
in_recon_adv datetime null ,
in_recon_adv_rsp datetime null ,
in_activ_adv datetime null ,
in_activ_adv_rsp datetime null ,
in_notify_adv_rsp datetime null ,
out_req datetime null ,
out_req_rsp datetime null ,
out_cmp datetime null ,
out_cmp_rsp datetime null ,
out_adv datetime null ,
out_adv_rsp datetime null ,
out_rev datetime null ,
out_rev_rsp datetime null ,
out_recon_adv datetime null ,
out_recon_adv_rsp datetime null ,
out_activ_adv datetime null ,
out_activ_adv_rsp datetime null ,
out_notify_adv datetime null ,
user_reserved_1 varchar (10) null ,
card_seq_nr char (3) null ,
tran_nr_prev int null ,
tran_nr_next int null ,
sink_node_acquiring_inst varchar (11) null ,
sink_node_forwarding_inst varchar (11) null ,
fee_tran_original float null ,
source_node_fee_original float null ,
sink_node_fee_original float null ,
acquirer_participant int null ,
issuer_participant int null ,
file_update_code char (1) null ,
file_update_name varchar (17) null ,
file_record_id varchar (12) null ,
bank_details varchar (31) null ,
payee_name_and_address varchar (253) null ,
payer_account_id varchar (28) null ,
icc_data_req text null ,
icc_data_rsp text null ,
structured_data_req text null ,
structured_data_rsp text null ,
card_product varchar (20) null ,
source_node_original_node varchar (20) null ,
source_node_original_key varchar (32) null ,
card_verification_result char (1) null ,
secure_3d_result char (1) null ,
track1_data varchar (76) null ,
source_node_amount_impact float null ,
sink_node_amount_impact float null ,
amount_tran_impact float null ,
orig_auth_date_settle_req char (8) null ,
orig_auth_date_settle_rsp char (8) null ,
issuer_network_id varchar (11) null ,
ucaf_data varchar (33) null ,
extended_tran_type char (4) null ,
from_account_type_qualifier char (1) null ,
to_account_type_qualifier char (1) null ,
acquirer_network_id varchar (11) null ,
sink_node_card_acceptor_id char (15) null ,
source_node_batch_settlement_date datetime null ,
sink_node_batch_settlement_date datetime null ,
x_transaction_timestamp datetime null ,
x_transaction_desc varchar (30) not null ,
x_transaction_amount float not null ,
x_settlement_amount float not null ,
x_surcharge_count int not null ,
x_surcharge_amount float not null ,
x_rsp_code char (2) null ,
x_updated_flag tinyint not null
) '

print @sql_command2 + @sql_command3

exec('execute ' + @sql_command2 + '' + @sql_command3 +'')

/*the error I get is:Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'if'.*/
--sorry about the naf formatting

 

[Pattycake245]

Looks like you have a ton of spaces between all your table field declarations. If you print out the strings you'll see that the @sql_command2 string isn't even finishing because you've reached 8000 chars. I'm in the process of taking out all your extra "white spaces", then we'll see what we get.

Tim

 

[bunmiA]

For the benefit of Tim and others who were helping with this thread. I have found a way around the problem. I am calling the sql query as a file (not a stored proc), (this means the query never needs to go to the master database), from a batch job. The batch job sets up the default database as '@dbname' (this variable is set up in the batch job). and like magic!! the tables get created in '@dbname'.

Thanks to everyone who helped.