• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 395
  • Last Modified:

Problem in altering a procedure. (Sql Server 2000)

In database [siddiqsons] I have created this procedure.
At the time of creation the database [[pack_web_sql] did not exist.

create procedure usp_build_pack_web_sql as
IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'pack_web_sql')
      DROP DATABASE [pack_web_sql]
GO
CREATE DATABASE [pack_web_sql]  ON (NAME = N'siddiqsons_Data',
FILENAME     = N'D:\datafiles\local\pack_web_sql.mdf' , SIZE = 100, MAXSIZE = 200, FILEGROWTH = 10%)
LOG ON (NAME = N'siddiqsons_Log', FILENAME = N'D:\datafiles\local\pack_web_sql_log.ldf' , SIZE = 100, FILEGROWTH = 10%)
 COLLATE SQL_Latin1_General_CP1_CI_AS
GO

use [pack_web_sql]
GO

CREATE TABLE [dbo].[matcher] (
      [matcher_code] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [matcher_name] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[customer] (
      [cust_code] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [cust_name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [attn_person] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [address1] [varchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [address2] [varchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [address3] [varchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [city] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [phone] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [fax] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [telex] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [e_mail_address] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [s_tax_no] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [stregno] AS (case when (len([s_tax_no]) > 0) then (replace([s_tax_no],'-','')) else '0' end) ,
      [abbr] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [party_type] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [major_cust_code] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [executive_code] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [com_agent_code] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [report_tag] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [gst_rate_tag] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [credit_limit] [numeric](10, 0) NOT NULL ,
      [account_code] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
etc, etc ...........................................................................

The problem is , when I changed the word create with alter and run the script it says
Database 'pack_web_sql' already exists.
There is already an object named 'matcher' in the database.
There is already an object named 'customer' in the database.

I have to execute
drop database 'pack_web_sql'  
to avoid the above error.

Further, I intend , to create/modify the procedure so that When I run the procedure
by exec command, it wil drop/create database and other tables.
Currently the whole thing is being executed at the time of creating/altering the procedure.

Further, when I check the procedure in enterprise manager I see only

create procedure  usp_build_pack_web_sql as
IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'pack_web_sql')
      DROP DATABASE [pack_web_sql]

GO

where r other lines.

0
Mateen
Asked:
Mateen
  • 2
2 Solutions
 
BillAn1Commented:
GO is not a command you can use within a stored procedure. rather it is a command to Query Analysier that marks the end of a Batch. It basically means "take the code up to this point and execute it now before continuing any further"
So in this case the GO marks the end of your stored procedure. all commands after the GO will be interpreted as new batches to be run immediately, not as part of your procedure.
0
 
MateenAuthor Commented:
Hi BillAn1

Quite right.

Can U suggest , How to get rid of [go].

Here is the full code.

IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'pack_web_sql')
      DROP DATABASE [pack_web_sql]
GO

CREATE DATABASE [pack_web_sql]  ON (NAME = N'siddiqsons_Data',
FILENAME     = N'D:\datafiles\local\pack_web_sql.mdf' , SIZE = 100, MAXSIZE = 200, FILEGROWTH = 10%)
LOG ON (NAME = N'siddiqsons_Log', FILENAME = N'D:\datafiles\local\pack_web_sql_log.ldf' , SIZE = 100, FILEGROWTH = 10%)
 COLLATE SQL_Latin1_General_CP1_CI_AS
GO

use [pack_web_sql]
GO

CREATE TABLE [dbo].[matcher] (
      [matcher_code] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [matcher_name] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[customer] (
      [cust_code] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [cust_name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [attn_person] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [address1] [varchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [address2] [varchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [address3] [varchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [city] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [phone] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [fax] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [telex] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [e_mail_address] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [s_tax_no] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [stregno] AS (case when (len([s_tax_no]) > 0) then (replace([s_tax_no],'-','')) else '0' end) ,
      [abbr] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [party_type] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [major_cust_code] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [executive_code] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [com_agent_code] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [report_tag] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [gst_rate_tag] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [credit_limit] [numeric](10, 0) NOT NULL ,
      [account_code] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[colour] (
      [colour_code] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [colour_name] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [user_name] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [login_time] [datetime] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[company] (
      [company_code] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [company_name] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [user_name] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [create_date] [smalldatetime] NULL ,
      [edit_date] [smalldatetime] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[desp_det_direct] (
      [desp_no] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [colour_code] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [rope_slasher] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [grade_id] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [pl_no] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [no_of_roll_pcs] [numeric](7, 0) NOT NULL ,
      [measure_unit] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [qty] [numeric](10, 2) NOT NULL ,
      [quality_width] [numeric](5, 0) NOT NULL ,
      [company_code] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [fabric_unit_code] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [fabric_type] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [quality_code] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [do_no] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [fac_order_no] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [left_right_tag] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [MUF] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[desp_mst_direct] (
      [desp_no] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [ship_date] [smalldatetime] NOT NULL ,
      [unit_code] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [customer_code] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [desp_type] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [desp_by] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [sample_ito_inv_no] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [gp_no] [numeric](10, 0) NOT NULL ,
      [outward_no] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [truck_no] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [lc_no] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [container_no] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [ship_mark_code] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [remarks] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [company_code] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [post_tag] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [pack_list_wt] [numeric](18, 3) NULL ,
      [gine_wt] [numeric](10, 3) NOT NULL ,
      [user_name] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [edit_date] [smalldatetime] NULL ,
      [MUF] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[fabric_type] (
      [fabric_type] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [fabric_name] [varchar] (22) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [tag] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [user_name] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [create_date] [smalldatetime] NULL ,
      [edit_date] [smalldatetime] NULL ,
      [main_cat] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[grade] (
      [grade_id] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [grade_name] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [user_name] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [create_date] [smalldatetime] NULL ,
      [edit_date] [smalldatetime] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[log_of_setups] (
      [trans_no] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
      [code] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [title] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [table_name] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [edited_by] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [edit_date] [smalldatetime] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[measure_unit] (
      [measure_unit] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [measure_unit_name] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [uom] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [user_name] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [login_time] [datetime] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[menu] (
      [user_id] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [visible] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [bar_name] [varchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [lock_name] [varchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [menu_level] [numeric](5, 0) NULL ,
      [upper_menu] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [seq_no] [numeric](18, 0) NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[pack_local_det_fox] (
      [trans_no] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [pl_date] [smalldatetime] NULL ,
      [rollno] [numeric](18, 0) NOT NULL ,
      [dup_ser] [int] NOT NULL ,
      [cont] [numeric](4, 0) NOT NULL ,
      [dl] [numeric](4, 0) NOT NULL ,
      [shade] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [pcs] [numeric](2, 0) NOT NULL ,
      [p1] [numeric](3, 0) NOT NULL ,
      [p2] [numeric](3, 0) NOT NULL ,
      [p3] [numeric](3, 0) NOT NULL ,
      [p4] [numeric](3, 0) NOT NULL ,
      [mtrs] AS ([p1] + [p2] + [p3] + [p4]) ,
      [yards] [numeric](6, 2) NOT NULL ,
      [points] [numeric](3, 0) NOT NULL ,
      [netwt] [numeric](6, 2) NOT NULL ,
      [grosswt] [numeric](6, 2) NOT NULL ,
      [msqyd] [numeric](6, 2) NOT NULL ,
      [width] [numeric](5, 2) NOT NULL ,
      [company_code] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [post_tag] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [move_tag] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [pack_roll_no] [numeric](18, 0) NOT NULL ,
      [matcher_code] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [recalc_tag] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [prod_date] [smalldatetime] NULL ,
      [barcode] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [actual_weight] [numeric](6, 2) NOT NULL ,
      [remarks] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [original_pl] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [MUF] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [cut_separate] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[pack_ssi_head_fox] (
      [trans_no] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [shp] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [cust_code] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [lc] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [ord] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [gp] [numeric](10, 0) NOT NULL ,
      [do] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [inv] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [pl_date] [smalldatetime] NOT NULL ,
      [cust_date] [smalldatetime] NULL ,
      [weigh] [numeric](10, 4) NOT NULL ,
      [width] [numeric](6, 2) NOT NULL ,
      [company_code] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [post_tag] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [quality_code] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [fabric_type] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [color_code] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [unit_transferred] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [pl_transferred_tag] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [pl_transf_no] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [grade_id] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [bar_code] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [reg_no] [int] NOT NULL ,
      [reg_page_no] [int] NOT NULL ,
      [rope_slasher] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [PLM] [numeric](18, 0) NOT NULL ,
      [location] [varchar] (70) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [left_right_tag] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [desp_date] [smalldatetime] NULL ,
      [container_no] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [seal_no] [varchar] (7) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [UNT] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [ITO_NO] [numeric](18, 0) NOT NULL ,
      [remarks] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [packing_wt] [numeric](18, 4) NOT NULL ,
      [style] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [inspected] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [cut_separately] [varchar] (180) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [executive_code] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [user_name] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [edit_date] [smalldatetime] NULL ,
      [MUF] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[password] (
      [user_id] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [password] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [company_code] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [user_name] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [apps_name] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [create_date] [smalldatetime] NULL ,
      [edit_date] [smalldatetime] NULL ,
      [exec_code] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [password2] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[menu_group] (
      [group_id] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [company_code] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [apps_name] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [visible] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [bar_name] [varchar] (99) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [lock_name] [varchar] (99) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[user_group_det] (
      [group_id] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [company_code] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [apps_name] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [user_id] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[user_group_mst] (
      [group_id] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [company_code] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [apps_name] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [post_tag] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[pbcatcol] (
      [pbc_tnam] [varchar] (129) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [pbc_tid] [int] NULL ,
      [pbc_ownr] [varchar] (129) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [pbc_cnam] [varchar] (129) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [pbc_cid] [smallint] NULL ,
      [pbc_labl] [varchar] (254) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [pbc_lpos] [smallint] NULL ,
      [pbc_hdr] [varchar] (254) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [pbc_hpos] [smallint] NULL ,
      [pbc_jtfy] [smallint] NULL ,
      [pbc_mask] [varchar] (31) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [pbc_case] [smallint] NULL ,
      [pbc_hght] [smallint] NULL ,
      [pbc_wdth] [smallint] NULL ,
      [pbc_ptrn] [varchar] (31) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [pbc_bmap] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [pbc_init] [varchar] (254) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [pbc_cmnt] [varchar] (254) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [pbc_edit] [varchar] (31) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [pbc_tag] [varchar] (254) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[pbcatedt] (
      [pbe_name] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [pbe_edit] [varchar] (254) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [pbe_type] [smallint] NULL ,
      [pbe_cntr] [int] NULL ,
      [pbe_seqn] [smallint] NOT NULL ,
      [pbe_flag] [int] NULL ,
      [pbe_work] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[pbcatfmt] (
      [pbf_name] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [pbf_frmt] [varchar] (254) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [pbf_type] [smallint] NULL ,
      [pbf_cntr] [int] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[pbcattbl] (
      [pbt_tnam] [varchar] (129) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [pbt_tid] [int] NULL ,
      [pbt_ownr] [varchar] (129) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [pbd_fhgt] [smallint] NULL ,
      [pbd_fwgt] [smallint] NULL ,
      [pbd_fitl] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [pbd_funl] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [pbd_fchr] [smallint] NULL ,
      [pbd_fptc] [smallint] NULL ,
      [pbd_ffce] [varchar] (18) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [pbh_fhgt] [smallint] NULL ,
      [pbh_fwgt] [smallint] NULL ,
      [pbh_fitl] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [pbh_funl] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [pbh_fchr] [smallint] NULL ,
      [pbh_fptc] [smallint] NULL ,
      [pbh_ffce] [varchar] (18) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [pbl_fhgt] [smallint] NULL ,
      [pbl_fwgt] [smallint] NULL ,
      [pbl_fitl] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [pbl_funl] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [pbl_fchr] [smallint] NULL ,
      [pbl_fptc] [smallint] NULL ,
      [pbl_ffce] [varchar] (18) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [pbt_cmnt] [varchar] (254) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[pbcatvld] (
      [pbv_name] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [pbv_vald] [varchar] (254) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [pbv_type] [smallint] NULL ,
      [pbv_cntr] [int] NULL ,
      [pbv_msg] [varchar] (254) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[period] (
      [period] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [period_name] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [ass_year] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [start_date] [smalldatetime] NULL ,
      [end_date] [smalldatetime] NULL ,
      [user_name] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [create_date] [smalldatetime] NULL ,
      [edit_date] [smalldatetime] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[pk_miss] (
      [pk_miss] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [verifly] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [grd] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[printer_setup] (
      [printer_paper_no] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [description] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [printer_paper_size_length] [numeric](6, 2) NULL ,
      [printer_paper_size_width] [numeric](6, 2) NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[quality] (
      [quality_code] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [quality_name] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [quality_description] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [quality_wt] [numeric](5, 2) NULL ,
      [weav_type] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [user_name] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [create_date] [smalldatetime] NULL ,
      [edit_date] [smalldatetime] NULL ,
      [oz] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [weav_tag] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[rs_lastcommit] (
      [origin] [int] NOT NULL ,
      [origin_qid] [binary] (36) NULL ,
      [secondary_qid] [binary] (36) NULL ,
      [origin_time] [datetime] NULL ,
      [commit_time] [datetime] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[rs_threads] (
      [id] [int] NOT NULL ,
      [seq] [int] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[unit] (
      [unit_code] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [unit_name] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [pack_grp] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [tag] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [address] [varchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [phone] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [fax] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [telex] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [reg_no] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [reg_date] [smalldatetime] NULL ,
      [cci_reg_no] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [cci_reg_date] [smalldatetime] NULL ,
      [expid] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [gir_no] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [it_circle] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [abbr] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [user_name] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [create_date] [smalldatetime] NULL ,
      [edit_date] [smalldatetime] NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[customer] WITH NOCHECK ADD
      CONSTRAINT [customer_sidsale_x] PRIMARY KEY  CLUSTERED
      (
            [cust_code]
      )  ON [PRIMARY]
GO

ALTER TABLE [dbo].[colour] WITH NOCHECK ADD
      CONSTRAINT [pk_colour_code] PRIMARY KEY  CLUSTERED
      (
            [colour_code]
      )  ON [PRIMARY]
GO

ALTER TABLE [dbo].[company] WITH NOCHECK ADD
      CONSTRAINT [pk_comp_code] PRIMARY KEY  CLUSTERED
      (
            [company_code]
      )  ON [PRIMARY]
GO

ALTER TABLE [dbo].[desp_det_direct] WITH NOCHECK ADD
      CONSTRAINT [PK_desp_det_direct] PRIMARY KEY  CLUSTERED
      (
            [desp_no],
            [colour_code],
            [pl_no],
            [company_code],
            [fabric_type],
            [quality_code],
            [do_no]
      )  ON [PRIMARY]
GO

ALTER TABLE [dbo].[desp_mst_direct] WITH NOCHECK ADD
      CONSTRAINT [PK_desp_mst_direct] PRIMARY KEY  CLUSTERED
      (
            [desp_no],
            [company_code]
      )  ON [PRIMARY]
GO

ALTER TABLE [dbo].[fabric_type] WITH NOCHECK ADD
      CONSTRAINT [pk_fab_type] PRIMARY KEY  CLUSTERED
      (
            [fabric_type]
      )  ON [PRIMARY]
GO

ALTER TABLE [dbo].[grade] WITH NOCHECK ADD
      CONSTRAINT [pk_grade_id] PRIMARY KEY  CLUSTERED
      (
            [grade_id]
      )  ON [PRIMARY]
GO

ALTER TABLE [dbo].[log_of_setups] WITH NOCHECK ADD
      CONSTRAINT [PK_log_of_setups] PRIMARY KEY  CLUSTERED
      (
            [trans_no]
      )  ON [PRIMARY]
GO

ALTER TABLE [dbo].[measure_unit] WITH NOCHECK ADD
      CONSTRAINT [pk_measure_unit] PRIMARY KEY  CLUSTERED
      (
            [measure_unit]
      )  ON [PRIMARY]
GO

ALTER TABLE [dbo].[menu] WITH NOCHECK ADD
      CONSTRAINT [pk_menu_user_id] PRIMARY KEY  CLUSTERED
      (
            [user_id],
            [lock_name]
      )  ON [PRIMARY]
GO

ALTER TABLE [dbo].[pack_local_det_fox] WITH NOCHECK ADD
      CONSTRAINT [PK_pack_local_det_fox] PRIMARY KEY  CLUSTERED
      (
            [trans_no],
            [rollno],
            [company_code]
      )  ON [PRIMARY]
GO

ALTER TABLE [dbo].[pack_ssi_head_fox] WITH NOCHECK ADD
      CONSTRAINT [PK_pack_ssi_head_fox] PRIMARY KEY  CLUSTERED
      (
            [trans_no],
            [company_code]
      )  ON [PRIMARY]
GO

ALTER TABLE [dbo].[password] WITH NOCHECK ADD
      CONSTRAINT [pk_pass_user_id] PRIMARY KEY  CLUSTERED
      (
            [user_id],
                [company_code]
      )  ON [PRIMARY]
GO

ALTER TABLE [dbo].[period] WITH NOCHECK ADD
      CONSTRAINT [pk_period] PRIMARY KEY  CLUSTERED
      (
            [period]
      )  ON [PRIMARY]
GO

ALTER TABLE [dbo].[pk_miss] WITH NOCHECK ADD
      CONSTRAINT [PK_pk_miss] PRIMARY KEY  CLUSTERED
      (
            [pk_miss]
      )  ON [PRIMARY]
GO

ALTER TABLE [dbo].[printer_setup] WITH NOCHECK ADD
      CONSTRAINT [pk_prn_setup_paper_no] PRIMARY KEY  CLUSTERED
      (
            [printer_paper_no]
      )  ON [PRIMARY]
GO

ALTER TABLE [dbo].[quality] WITH NOCHECK ADD
      CONSTRAINT [pk_quality_code2] PRIMARY KEY  CLUSTERED
      (
            [quality_code]
      )  ON [PRIMARY]
GO

ALTER TABLE [dbo].[unit] WITH NOCHECK ADD
      CONSTRAINT [pk_unit_code] PRIMARY KEY  CLUSTERED
      (
            [unit_code]
      )  ON [PRIMARY]
GO

ALTER TABLE [dbo].[colour] WITH NOCHECK ADD
      CONSTRAINT [IX_colour] UNIQUE  NONCLUSTERED
      (
            [colour_name]
      )  ON [PRIMARY]
GO

ALTER TABLE [dbo].[company] WITH NOCHECK ADD
      CONSTRAINT [IX_company] UNIQUE  NONCLUSTERED
      (
            [company_name]
      )  ON [PRIMARY]
GO

ALTER TABLE [dbo].[fabric_type] WITH NOCHECK ADD
      CONSTRAINT [IX_fabric_type] UNIQUE  NONCLUSTERED
      (
            [fabric_name]
      )  ON [PRIMARY]
GO

ALTER TABLE [dbo].[grade] WITH NOCHECK ADD
      CONSTRAINT [IX_grade] UNIQUE  NONCLUSTERED
      (
            [grade_name]
      )  ON [PRIMARY]
GO

ALTER TABLE [dbo].[measure_unit] WITH NOCHECK ADD
      CONSTRAINT [IX_measure_unit] UNIQUE  NONCLUSTERED
      (
            [measure_unit_name]
      )  ON [PRIMARY]
GO

ALTER TABLE [dbo].[pack_local_det_fox] WITH NOCHECK ADD
      CONSTRAINT [DF_pack_local_det_fox_pcs] DEFAULT (0) FOR [pcs],
      CONSTRAINT [DF_pack_local_det_fox_p1] DEFAULT (0) FOR [p1],
      CONSTRAINT [DF_pack_local_det_fox_p2] DEFAULT (0) FOR [p2],
      CONSTRAINT [DF_pack_local_det_fox_p30] DEFAULT (0) FOR [p3],
      CONSTRAINT [DF_pack_local_det_fox_p4] DEFAULT (0) FOR [p4],
      CONSTRAINT [DF_pack_local_det_fox_yards] DEFAULT (0) FOR [yards],
      CONSTRAINT [DF_pack_local_det_fox_points] DEFAULT (0) FOR [points],
      CONSTRAINT [DF_pack_local_det_fox_netwt] DEFAULT (0) FOR [netwt],
      CONSTRAINT [DF_pack_local_det_fox_grosswt] DEFAULT (0) FOR [grosswt],
      CONSTRAINT [DF_pack_local_det_fox_msqyd] DEFAULT (0) FOR [msqyd],
      CONSTRAINT [DF_pack_local_det_fox_width] DEFAULT (0) FOR [width],
      CONSTRAINT [DF_pack_local_det_fox_pack_roll_no] DEFAULT (0) FOR [pack_roll_no],
      CONSTRAINT [DF_pack_local_det_fox_recalc_tag] DEFAULT ('Y') FOR [recalc_tag]
GO

ALTER TABLE [dbo].[pack_ssi_head_fox] WITH NOCHECK ADD
      CONSTRAINT [DF_pack_ssi_head_fox_PLM] DEFAULT (0) FOR [PLM]
GO

ALTER TABLE [dbo].[period] WITH NOCHECK ADD
      CONSTRAINT [IX_period] UNIQUE  NONCLUSTERED
      (
            [period]
      )  ON [PRIMARY]
GO

ALTER TABLE [dbo].[printer_setup] WITH NOCHECK ADD
      CONSTRAINT [DF_printer_setup_printer_paper_size_length] DEFAULT (0) FOR [printer_paper_size_length],
      CONSTRAINT [DF_printer_setup_printer_paper_size_width] DEFAULT (0) FOR [printer_paper_size_width]
GO

ALTER TABLE [dbo].[quality] WITH NOCHECK ADD
      CONSTRAINT [DF_quality_quality_wt] DEFAULT (0) FOR [quality_wt],
      CONSTRAINT [IX_quality] UNIQUE  NONCLUSTERED
      (
            [quality_name]
      )  ON [PRIMARY]
GO

ALTER TABLE [dbo].[unit] WITH NOCHECK ADD
      CONSTRAINT [IX_unit] UNIQUE  NONCLUSTERED
      (
            [unit_name]
      )  ON [PRIMARY]
GO

 CREATE  INDEX [pack_local_det_fox35] ON [dbo].[pack_local_det_fox]([rollno]) ON [PRIMARY]
GO


ALTER TABLE [dbo].[desp_det_direct] ADD
      CONSTRAINT [FK_desp_det_direct_colour] FOREIGN KEY
      (
            [colour_code]
      ) REFERENCES [dbo].[colour] (
            [colour_code]
      ),
      CONSTRAINT [FK_desp_det_direct_company] FOREIGN KEY
      (
            [company_code]
      ) REFERENCES [dbo].[company] (
            [company_code]
      ),
      CONSTRAINT [FK_desp_det_direct_desp_mst_direct] FOREIGN KEY
      (
            [desp_no],
            [company_code]
      ) REFERENCES [dbo].[desp_mst_direct] (
            [desp_no],
            [company_code]
      ) ON DELETE CASCADE ,
      CONSTRAINT [FK_desp_det_direct_fabric_type] FOREIGN KEY
      (
            [fabric_type]
      ) REFERENCES [dbo].[fabric_type] (
            [fabric_type]
      ),
      CONSTRAINT [FK_desp_det_direct_measure_unit] FOREIGN KEY
      (
            [measure_unit]
      ) REFERENCES [dbo].[measure_unit] (
            [measure_unit]
      ),
      CONSTRAINT [FK_desp_det_direct_quality] FOREIGN KEY
      (
            [quality_code]
      ) REFERENCES [dbo].[quality] (
            [quality_code]
      )
GO

ALTER TABLE [dbo].[pack_local_det_fox] ADD
      CONSTRAINT [FK_pack_local_det_fox_pack_ssi_head_fox] FOREIGN KEY
      (
            [trans_no],
            [company_code]
      ) REFERENCES [dbo].[pack_ssi_head_fox] (
            [trans_no],
            [company_code]
      ) ON DELETE CASCADE
GO


SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

-------------- INSERT RECORDS ------------------

-- company
insert into   pack_web_sql.dbo.company
select * from siddiqsons.dbo.company
go

-- unit
insert into   pack_web_sql.dbo.unit(unit_code,unit_name,pack_grp,tag,address,reg_no,abbr)
select unit_code,unit_name,pack_grp,tag,address,reg_no,abbr from siddiqsons.dbo.unit
go

-- grade
insert into   pack_web_sql.dbo.grade
select * from siddiqsons.dbo.grade
go

-- colour
insert into   pack_web_sql.dbo.colour
select * from siddiqsons.dbo.colour
go


-- quality
insert into   pack_web_sql.dbo.quality
select * from siddiqsons.dbo.quality
go

-- fabric_type
insert into   pack_web_sql.dbo.fabric_type
select * from siddiqsons.dbo.fabric_type
go

-- measure_unit
insert into   pack_web_sql.dbo.measure_unit
select * from siddiqsons.dbo.measure_unit
go
-- printer_setup
insert into   pack_web_sql.dbo.printer_setup
select * from siddiqsons.dbo.printer_setup
go

-- pk_miss
insert into   pack_web_sql.dbo.pk_miss
select * from siddiqsons.dbo.pk_miss
go

-- password
insert into password(user_id,password,company_code,apps_name)
SELECT 'LAHORE','•­¥•…™','01','PACKING'
UNION ALL
SELECT 'LAHORE','•­¥•…™','02','PACKING'

GO
insert into user_group_mst(group_id,company_code,apps_name)
select 'LAHORE_D53','01','PACKING'
UNION
select 'LAHORE_B26','02','PACKING'
GO
insert into user_group_DEt(group_id,company_code,apps_name,user_id)
select 'LAHORE_D53','01','PACKING','LAHORE'
UNION
select 'LAHORE_B26','02','PACKING','LAHORE'
GO

INSERT PACK_WEB_SQL.DBO.MENU_GROUP(group_id,company_code,apps_name,lock_name,visible,bar_name)
SELECT * FROM SIDDIQSONS.DBO.MENU_GROUP
WHERE GROUP_ID IN ('LAHORE_B26','LAHORE_D53')
GO

--customer
insert into   pack_web_sql.dbo.customer(
              cust_code,cust_name,attn_person,address1,address2,address3,city,phone,fax,telex,e_mail_address,s_tax_no,
              abbr,party_type,major_cust_code,executive_code,com_agent_code,report_tag,gst_rate_tag,credit_limit,
              account_code)
select        cust_code,cust_name,attn_person,address1,address2,address3,city,phone,fax,telex,e_mail_address,s_tax_no,
              abbr,party_type,major_cust_code,executive_code,com_agent_code,report_tag,gst_rate_tag,credit_limit,
              account_code

from siddiqsons.dbo.customer

--- matcher
insert into pack_web_sql.dbo.matcher
select * from siddiqsons.dbo.matcher
GO


-- pack_ssi_head_fox
insert into   pack_web_sql.dbo.pack_ssi_head_fox
( trans_no,shp,lc,ord,gp,do,inv,pl_date,cust_date,weigh,width,company_code,post_tag,quality_code,fabric_type,
  color_code,unit_transferred,pl_transferred_tag,pl_transf_no,grade_id,bar_code,reg_no,reg_page_no,rope_slasher,
  PLM,location,left_right_tag,desp_date,container_no,UNT,ITO_NO,remarks,packing_wt,style,inspected,cut_separately,
  executive_code,cust_code)

select trans_no,shp,lc,ord,gp,do,inv,pl_date,cust_date,weigh,width,company_code,post_tag,quality_code,fabric_type,
  color_code,unit_transferred,pl_transferred_tag,pl_transf_no,grade_id,bar_code,reg_no,reg_page_no,rope_slasher,
  PLM,location,left_right_tag,desp_date,container_no,UNT,ITO_NO,remarks,packing_wt,style,inspected,cut_separately,
  executive_code,cust_code
 from  siddiqsons.dbo.pack_ssi_head_fox
 where siddiqsons.dbo.pack_ssi_head_fox.pl_transferred_tag in ('A','B','H')
go

-- pack_local_det_fox
insert into   pack_web_sql.dbo.pack_local_det_fox
  (trans_no,pl_date,rollno,dup_ser,cont,dl,shade,pcs,p1,p2,p3,p4,yards,points,netwt,grosswt,msqyd,width,
  company_code,post_tag,move_tag,pack_roll_no,matcher_code,recalc_tag,prod_date,barcode,actual_weight,remarks)
select  a.trans_no,a.pl_date,a.rollno,a.dup_ser,a.cont,a.dl,a.shade,a.pcs,a.p1,a.p2,a.p3,a.p4,a.yards,a.points,
        a.netwt,a.grosswt,a.msqyd,a.width,a.company_code,a.post_tag,a.move_tag,a.pack_roll_no,a.matcher_code,a.recalc_tag,
        a.prod_date,a.barcode,a.actual_weight,a.remarks
from       siddiqsons.dbo.pack_local_det_fox a
join    siddiqsons.dbo.pack_ssi_head_fox b on (a.trans_no=b.trans_no and a.company_code=b.company_code)
where   b.pl_transferred_tag in ('A','B','H')
go

----------
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE view   v_loc_exp_cust(cust_code,cust_name,tag,abbr,executive_code,city)
 as select 'L'+right(cust_code,3),cust_name,'L',abbr,executive_code,city
 from customer
 where left(customer.cust_code,2)='01'
 union all
 select 'E'+right(cust_code,3),cust_name,'E',abbr,executive_code,city
from customer
 where left(customer.cust_code,2)='02'
go


CREATE view
  dbo.v_unit(code,name,tag) as select 'UN'+unit.unit_code,unit.unit_name,'U' from dbo.unit
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

0
 
nmcdermaidCommented:
'GO' finishes a batch.

In your case the first 'GO' has finished the 'create procedure' batch so its only the bits in the middle that get saved as the body of the stored procedure

Then the rest of the statement runs immediately.


I suggest the following:

1. Put the drop and create DB into its own stored procedure, then call these from your main stored procedure. This will hopefully 'force' it to complete before the rest
2. I seem to recall having problems with 'USE' in a stored procedure so I suggest you get rid of the 'USE' and use three part table names instead



So an abridged version of your SP would be




create procedure usp_build_pack_web_sql as
IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'pack_web_sql')
BEGIN
  EXEC usp_Pack_web_sql_DROP         -- Need to create this SP
  EXEC usp_Pack_web_sql_CREATE     -- Need to create this SP
END


CREATE TABLE [pack_web_sql].[dbo].[matcher] (
     [matcher_code] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
     [matcher_name] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]

.........
...
.. rest of SP (without GO's)
....
..


GO




There are easier ways to clear a database than dropping it and recreating it though!! I would be interested to know why you are trying to do that in a stored proc.
0
 
nmcdermaidCommented:
It looks like you are basically running an entire database creation script.

You know you can just run the table drop/creation parts without recreating the database.

0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now