none
Error al comprobar/crear BD (IF...ELSE) RRS feed

  • Pregunta

  • Buenas tardes, acualmente tengo un script para crear una BD en caso de que no exista, en caso de existir no pasa nada.

    Cuando la BD no existe la crea sin problemas, sin embargo cuando la BD existe el script se queda "colgado" la consulta se queda pensando por minutos hasta que yo manualmente la cancelo, al cancelarla el mensaje de error es:

    Ya existe la BD
    Mens. 1801, Nivel 16, Estado 3, Línea 10
    La base de datos 'PROSMART' ya existe. Elija un nombre diferente para la base de datos.
    Mens. 5069, Nivel 16, Estado 1, Línea 52
    Error de la instrucción ALTER DATABASE.
    Consulta cancelada por el usuario.

    USE [master]
    IF EXISTS (Select * from SysDataBases where name = 'PROSMART') 
    
    PRINT 'Ya existe la BD'
    
    ELSE
    USE [master]
    GO
    /****** Object:  Database [PROSMART]    Script Date: 11/04/2018 12:28:33 PM ******/
    CREATE DATABASE [PROSMART]
     CONTAINMENT = NONE
     ON  PRIMARY 
    ( NAME = N'PROSMART', FILENAME = N'C:\Program Files (x86)\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\PROSMART.mdf' , SIZE = 5120KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
     LOG ON 
    ( NAME = N'PROSMART_log', FILENAME = N'C:\Program Files (x86)\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\PROSMART_log.ldf' , SIZE = 2048KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
    GO
    ALTER DATABASE [PROSMART] SET COMPATIBILITY_LEVEL = 120
    GO
    IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
    begin
    EXEC [PROSMART].[dbo].[sp_fulltext_database] @action = 'enable'
    end
    GO
    ALTER DATABASE [PROSMART] SET ANSI_NULL_DEFAULT OFF 
    GO
    ALTER DATABASE [PROSMART] SET ANSI_NULLS OFF 
    GO
    ALTER DATABASE [PROSMART] SET ANSI_PADDING OFF 
    GO
    ALTER DATABASE [PROSMART] SET ANSI_WARNINGS OFF 
    GO
    ALTER DATABASE [PROSMART] SET ARITHABORT OFF 
    GO
    ALTER DATABASE [PROSMART] SET AUTO_CLOSE OFF 
    GO
    ALTER DATABASE [PROSMART] SET AUTO_SHRINK OFF 
    GO
    ALTER DATABASE [PROSMART] SET AUTO_UPDATE_STATISTICS ON 
    GO
    ALTER DATABASE [PROSMART] SET CURSOR_CLOSE_ON_COMMIT OFF 
    GO
    ALTER DATABASE [PROSMART] SET CURSOR_DEFAULT  GLOBAL 
    GO
    ALTER DATABASE [PROSMART] SET CONCAT_NULL_YIELDS_NULL OFF 
    GO
    ALTER DATABASE [PROSMART] SET NUMERIC_ROUNDABORT OFF 
    GO
    ALTER DATABASE [PROSMART] SET QUOTED_IDENTIFIER OFF 
    GO
    ALTER DATABASE [PROSMART] SET RECURSIVE_TRIGGERS OFF 
    GO
    ALTER DATABASE [PROSMART] SET  DISABLE_BROKER 
    GO
    ALTER DATABASE [PROSMART] SET AUTO_UPDATE_STATISTICS_ASYNC OFF 
    GO
    ALTER DATABASE [PROSMART] SET DATE_CORRELATION_OPTIMIZATION OFF 
    GO
    ALTER DATABASE [PROSMART] SET TRUSTWORTHY OFF 
    GO
    ALTER DATABASE [PROSMART] SET ALLOW_SNAPSHOT_ISOLATION OFF 
    GO
    ALTER DATABASE [PROSMART] SET PARAMETERIZATION SIMPLE 
    GO
    ALTER DATABASE [PROSMART] SET READ_COMMITTED_SNAPSHOT OFF 
    GO
    ALTER DATABASE [PROSMART] SET HONOR_BROKER_PRIORITY OFF 
    GO
    ALTER DATABASE [PROSMART] SET RECOVERY FULL 
    GO
    ALTER DATABASE [PROSMART] SET  MULTI_USER 
    GO
    ALTER DATABASE [PROSMART] SET PAGE_VERIFY CHECKSUM  
    GO
    ALTER DATABASE [PROSMART] SET DB_CHAINING OFF 
    GO
    ALTER DATABASE [PROSMART] SET FILESTREAM( NON_TRANSACTED_ACCESS = OFF ) 
    GO
    ALTER DATABASE [PROSMART] SET TARGET_RECOVERY_TIME = 0 SECONDS 
    GO
    ALTER DATABASE [PROSMART] SET DELAYED_DURABILITY = DISABLED 
    GO
    USE [PROSMART]
    GO
    /****** Object:  Table [dbo].[budget]    Script Date: 11/04/2018 12:28:33 PM ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_PADDING ON
    GO
    CREATE TABLE [dbo].[budget](
    	[idbudget] [int] IDENTITY(1,1) NOT NULL,
    	[montobudget] [float] NULL,
    	[outbudget] [float] NULL,
    	[inbudget] [float] NULL,
    	[descripbudget] [varchar](150) NULL,
    	[fechabudget] [datetime] NULL,
    	[idpedidobudget] [int] NULL,
    	[idpobudget] [int] NULL,
     CONSTRAINT [PK_budget] PRIMARY KEY CLUSTERED 
    (
    	[idbudget] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    
    GO
    SET ANSI_PADDING OFF
    GO
    /****** Object:  Table [dbo].[cotiz_detalle]    Script Date: 11/04/2018 12:28:33 PM ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_PADDING ON
    GO
    CREATE TABLE [dbo].[cotiz_detalle](
    	[idcotizdetalle] [int] IDENTITY(1,1) NOT NULL,
    	[descripitem] [varchar](150) NULL,
    	[umedidaitem] [varchar](50) NULL,
    	[preciounit] [float] NULL,
    	[ivaitem] [float] NULL,
    	[cantitem] [int] NULL,
    	[totalitem] [float] NULL,
    	[idcotizmaestro] [int] NOT NULL,
    	[idproducto] [int] NULL,
     CONSTRAINT [PK_cotiz_detalle] PRIMARY KEY CLUSTERED 
    (
    	[idcotizdetalle] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    
    GO
    SET ANSI_PADDING OFF
    GO
    /****** Object:  Table [dbo].[cotiz_maestro]    Script Date: 11/04/2018 12:28:33 PM ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_PADDING ON
    GO
    CREATE TABLE [dbo].[cotiz_maestro](
    	[idcotizmaestro] [int] IDENTITY(1,1) NOT NULL,
    	[consecotiz] [varchar](20) NULL,
    	[descripcotiz] [nvarchar](50) NULL,
    	[fechacotiz] [datetime] NULL,
    	[fechaini] [datetime] NULL,
    	[fechafin] [datetime] NULL,
    	[fechaentrega] [datetime] NULL,
    	[fechaaprobcotiz] [datetime] NULL,
    	[idcotizempresa] [int] NULL,
    	[idmetodocotiz] [int] NULL,
    	[idvendedorcotiz] [int] NULL,
    	[idaprobcotiz] [int] NULL,
    	[idtipocotiz] [int] NULL,
    	[estadocotiz] [int] NULL,
    	[revisioncotiz] [int] NULL,
     CONSTRAINT [PK_cotiz_maestro] PRIMARY KEY CLUSTERED 
    (
    	[idcotizmaestro] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    
    GO
    SET ANSI_PADDING OFF
    GO
    /****** Object:  Table [dbo].[departamento]    Script Date: 11/04/2018 12:28:33 PM ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_PADDING ON
    GO
    CREATE TABLE [dbo].[departamento](
    	[iddepartamento] [int] IDENTITY(1,1) NOT NULL,
    	[descdepartamento] [varchar](50) NOT NULL,
    	[codigodepartamento] [varchar](10) NOT NULL,
    	[bajadep] [bit] NULL,
     CONSTRAINT [PK_departamento] PRIMARY KEY CLUSTERED 
    (
    	[iddepartamento] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    
    GO
    SET ANSI_PADDING OFF
    GO
    /****** Object:  Table [dbo].[empleados]    Script Date: 11/04/2018 12:28:33 PM ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_PADDING ON
    GO
    CREATE TABLE [dbo].[empleados](
    	[idempleado] [int] IDENTITY(1,1) NOT NULL,
    	[idempleado2] [varchar](5) NULL,
    	[nombempleado] [varchar](50) NULL,
    	[apempleado] [varchar](50) NULL,
    	[especempleado] [varchar](50) NULL,
    	[ingresoempleado] [datetime] NULL,
    	[telfempleado1] [varchar](50) NULL,
    	[telfempleado2] [varchar](50) NULL,
    	[emailempleado1] [varchar](50) NULL,
    	[emailempleado2] [varchar](50) NULL,
    	[iddepempleado] [int] NULL,
    	[idemprempleado] [int] NULL,
    	[fotoempleado] [text] NULL,
    	[comentempleado] [varchar](250) NULL,
    	[bajaempleado] [bit] NULL,
     CONSTRAINT [PK_empleados] PRIMARY KEY CLUSTERED 
    (
    	[idempleado] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
    
    GO
    SET ANSI_PADDING OFF
    GO
    /****** Object:  Table [dbo].[empresas]    Script Date: 11/04/2018 12:28:33 PM ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_PADDING ON
    GO
    CREATE TABLE [dbo].[empresas](
    	[idempresa] [int] IDENTITY(1,1) NOT NULL,
    	[idempresa2] [varchar](10) NOT NULL,
    	[idempresa3] [int] NULL,
    	[rfcempresa] [varchar](50) NOT NULL,
    	[nombreempresa] [varchar](100) NOT NULL,
    	[rsocialempresa] [varchar](150) NOT NULL,
    	[dirempresa] [varchar](150) NOT NULL,
    	[telfempresa] [varchar](50) NOT NULL,
    	[telfempresa2] [varchar](50) NULL,
    	[emailempresa1] [varchar](50) NOT NULL,
    	[emailempresa2] [varchar](50) NULL,
    	[webempresa] [varchar](50) NULL,
    	[comentempresa] [varchar](250) NULL,
    	[logoempresa] [text] NULL,
    	[idmetodoempresa] [int] NULL,
    	[idmonedaempresa] [int] NULL,
    	[bajaempresa] [bit] NULL,
     CONSTRAINT [PK_empresas] PRIMARY KEY CLUSTERED 
    (
    	[idempresa] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
    
    GO
    SET ANSI_PADDING OFF
    GO
    /****** Object:  Table [dbo].[envios]    Script Date: 11/04/2018 12:28:33 PM ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_PADDING ON
    GO
    CREATE TABLE [dbo].[envios](
    	[idmetodo] [int] IDENTITY(1,1) NOT NULL,
    	[codigoenvios] [int] NULL,
    	[descmetodo] [varchar](50) NOT NULL,
    	[bajaenvios] [bit] NOT NULL,
     CONSTRAINT [PK_envios] PRIMARY KEY CLUSTERED 
    (
    	[idmetodo] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    
    GO
    SET ANSI_PADDING OFF
    GO
    /****** Object:  Table [dbo].[especialidad]    Script Date: 11/04/2018 12:28:33 PM ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_PADDING ON
    GO
    CREATE TABLE [dbo].[especialidad](
    	[idespec] [int] IDENTITY(1,1) NOT NULL,
    	[codigoespec] [int] NOT NULL,
    	[descespec] [varchar](50) NOT NULL,
    	[bajaespec] [bit] NOT NULL
    ) ON [PRIMARY]
    
    GO
    SET ANSI_PADDING OFF
    GO
    /****** Object:  Table [dbo].[metodo]    Script Date: 11/04/2018 12:28:33 PM ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_PADDING ON
    GO
    CREATE TABLE [dbo].[metodo](
    	[idmetodo] [int] IDENTITY(1,1) NOT NULL,
    	[descmetodo] [varchar](50) NULL,
     CONSTRAINT [PK_metodo] PRIMARY KEY CLUSTERED 
    (
    	[idmetodo] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    
    GO
    SET ANSI_PADDING OFF
    GO
    /****** Object:  Table [dbo].[moneda]    Script Date: 11/04/2018 12:28:33 PM ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_PADDING ON
    GO
    CREATE TABLE [dbo].[moneda](
    	[idmoneda] [int] IDENTITY(1,1) NOT NULL,
    	[descmoneda] [varchar](50) NOT NULL,
    	[simbolomoneda] [varchar](50) NOT NULL,
    	[bajamoneda] [bit] NOT NULL,
     CONSTRAINT [PK_moneda] PRIMARY KEY CLUSTERED 
    (
    	[idmoneda] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    
    GO
    SET ANSI_PADDING OFF
    GO
    /****** Object:  Table [dbo].[pais]    Script Date: 11/04/2018 12:28:33 PM ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_PADDING ON
    GO
    CREATE TABLE [dbo].[pais](
    	[idpais] [int] IDENTITY(1,1) NOT NULL,
    	[iso2] [varchar](5) NOT NULL,
    	[iso3] [varchar](5) NOT NULL,
    	[pais] [varchar](50) NOT NULL,
    	[capitalpais] [varchar](50) NOT NULL,
    	[prefijopais] [int] NOT NULL,
     CONSTRAINT [PK_pais] PRIMARY KEY CLUSTERED 
    (
    	[idpais] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    
    GO
    SET ANSI_PADDING OFF
    GO
    /****** Object:  Table [dbo].[pedidos]    Script Date: 11/04/2018 12:28:33 PM ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_PADDING ON
    GO
    CREATE TABLE [dbo].[pedidos](
    	[idpedido] [int] IDENTITY(1,1) NOT NULL,
    	[consecpedido] [varchar](50) NULL,
    	[fechapedido] [date] NULL,
    	[authpedido] [int] NULL,
    	[metodoconfpedido] [varchar](50) NULL,
    	[idempleadopedido] [int] NULL,
    	[cantpagospedido] [varchar](50) NULL,
    	[finipedido] [datetime] NULL,
    	[ffinpedido] [datetime] NULL,
    	[idcotizmaestropo] [int] NULL,
    	[bajapedido] [bit] NULL,
     CONSTRAINT [PK_pedidos] PRIMARY KEY CLUSTERED 
    (
    	[idpedido] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    
    GO
    SET ANSI_PADDING OFF
    GO
    /****** Object:  Table [dbo].[po_detalle]    Script Date: 11/04/2018 12:28:33 PM ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_PADDING ON
    GO
    CREATE TABLE [dbo].[po_detalle](
    	[idpodetalle] [int] IDENTITY(1,1) NOT NULL,
    	[descripitemdetalle] [varchar](150) NULL,
    	[umpodetalle] [varchar](50) NULL,
    	[preciounitdetalle] [float] NULL,
    	[ivaitemdetalle] [float] NULL,
    	[cantitemdetalle] [int] NULL,
    	[totalitemdetalle] [float] NULL,
    	[idpomaestro] [int] NULL,
     CONSTRAINT [PK_po_detalle] PRIMARY KEY CLUSTERED 
    (
    	[idpodetalle] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    
    GO
    SET ANSI_PADDING OFF
    GO
    /****** Object:  Table [dbo].[po_maestro]    Script Date: 11/04/2018 12:28:33 PM ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_PADDING ON
    GO
    CREATE TABLE [dbo].[po_maestro](
    	[idpomaestro] [int] IDENTITY(1,1) NOT NULL,
    	[consecpomaestro] [varchar](30) NULL,
    	[descrippomaestro] [nvarchar](50) NULL,
    	[fechapomaestro] [datetime] NULL,
    	[plazopomaestro] [datetime] NULL,
    	[fechaentrega] [datetime] NULL,
    	[fechaaprobpomaestro] [datetime] NULL,
    	[idproveedorpomaestro] [int] NULL,
    	[metodopomaestro] [nvarchar](100) NULL,
    	[termenviopo] [nvarchar](100) NULL,
    	[idvendedorpomaestro] [int] NULL,
    	[idaprobpomaestro] [int] NULL,
    	[idtipopomaestro] [int] NULL,
    	[estadopomaestro] [int] NULL,
    	[revisionpomaestro] [int] NULL,
    	[idpedidopo] [int] NULL,
     CONSTRAINT [PK_po_maestro] PRIMARY KEY CLUSTERED 
    (
    	[idpomaestro] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    
    GO
    SET ANSI_PADDING OFF
    GO
    /****** Object:  Table [dbo].[productos]    Script Date: 11/04/2018 12:28:33 PM ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_PADDING ON
    GO
    CREATE TABLE [dbo].[productos](
    	[idproducto] [int] IDENTITY(1,1) NOT NULL,
    	[idproducto2] [nvarchar](10) NULL,
    	[descproducto] [varchar](50) NOT NULL,
    	[costoproducto] [float] NOT NULL,
    	[ivaproducto] [float] NOT NULL,
    	[imgproducto] [text] NULL,
    	[umproducto] [varchar](50) NULL,
    	[numserieproducto] [varchar](50) NULL,
    	[bajaproducto] [bit] NULL,
    	[idproveedor] [int] NULL,
     CONSTRAINT [PK_productos] PRIMARY KEY CLUSTERED 
    (
    	[idproducto] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
    
    GO
    SET ANSI_PADDING OFF
    GO
    /****** Object:  Table [dbo].[propietario]    Script Date: 11/04/2018 12:28:33 PM ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_PADDING ON
    GO
    CREATE TABLE [dbo].[propietario](
    	[idpropietario] [int] IDENTITY(1,1) NOT NULL,
    	[idpropietario2] [varchar](3) NOT NULL,
    	[rfcpropietario] [varchar](50) NOT NULL,
    	[nombrepropietario] [varchar](100) NOT NULL,
    	[rsocialpropietario] [varchar](150) NOT NULL,
    	[dirpropietario] [varchar](150) NOT NULL,
    	[telfpropietario] [varchar](50) NOT NULL,
    	[telfpropietario2] [varchar](50) NULL,
    	[emailpropietario1] [varchar](50) NOT NULL,
    	[emailpropietario2] [varchar](50) NULL,
    	[webpropietario] [varchar](50) NULL,
    	[logopropietario] [text] NULL,
    	[bajapropietario] [bit] NULL,
     CONSTRAINT [PK_propietario] PRIMARY KEY CLUSTERED 
    (
    	[idpropietario] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
    
    GO
    SET ANSI_PADDING OFF
    GO
    /****** Object:  Table [dbo].[proveedores]    Script Date: 11/04/2018 12:28:33 PM ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_PADDING ON
    GO
    CREATE TABLE [dbo].[proveedores](
    	[idproveedor] [int] IDENTITY(1,1) NOT NULL,
    	[idproveedor2] [varchar](5) NOT NULL,
    	[idproveedor3] [int] NULL,
    	[rfcproveedor] [varchar](50) NOT NULL,
    	[nombreprov] [varchar](50) NULL,
    	[rsocialprov] [varchar](50) NULL,
    	[dirproveedor] [varchar](50) NOT NULL,
    	[telfproveedor] [varchar](50) NOT NULL,
    	[telfproveedor2] [varchar](50) NULL,
    	[emailproveedor1] [varchar](50) NULL,
    	[emailproveedor2] [varchar](50) NULL,
    	[webproveedor] [varchar](50) NULL,
    	[idmetodoprov] [int] NULL,
    	[idmonedaprov] [int] NULL,
    	[logoproveedor] [text] NULL,
    	[comentprov] [varchar](250) NULL,
    	[bajaprov] [bit] NULL,
     CONSTRAINT [PK_proveedores] PRIMARY KEY CLUSTERED 
    (
    	[idproveedor] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
    
    GO
    SET ANSI_PADDING OFF
    GO
    /****** Object:  Table [dbo].[tareas]    Script Date: 11/04/2018 12:28:33 PM ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_PADDING ON
    GO
    CREATE TABLE [dbo].[tareas](
    	[idtarea] [int] IDENTITY(1,1) NOT NULL,
    	[consectarea] [varchar](20) NULL,
    	[descriptare] [varchar](100) NULL,
    	[idempleadotarea] [int] NULL,
    	[iddeptarea] [int] NULL,
    	[fechainitarea] [datetime] NULL,
    	[fechafintarea] [datetime] NULL,
    	[avancetarea] [float] NULL,
    	[comentarea] [varchar](200) NULL,
    	[idpedidotarea] [int] NULL,
    	[bajatarea] [bit] NULL,
     CONSTRAINT [PK_tareas] PRIMARY KEY CLUSTERED 
    (
    	[idtarea] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    
    GO
    SET ANSI_PADDING OFF
    GO
    /****** Object:  Table [dbo].[tipocotiz]    Script Date: 11/04/2018 12:28:33 PM ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_PADDING ON
    GO
    CREATE TABLE [dbo].[tipocotiz](
    	[idcotiz] [int] IDENTITY(1,1) NOT NULL,
    	[tipocotiz] [varchar](100) NOT NULL,
    	[abrecotiz] [varchar](2) NULL,
     CONSTRAINT [PK_tipocotiz] PRIMARY KEY CLUSTERED 
    (
    	[idcotiz] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    
    GO
    SET ANSI_PADDING OFF
    GO
    USE [master]
    GO
    ALTER DATABASE [PROSMART] SET  READ_WRITE 
    GO
    
    
    USE [PROSMART]
    INSERT INTO [dbo].[tipocotiz]
               ([tipocotiz]
               ,[abrecotiz])
         VALUES
               ('PROYECTO','PJ'),('EQUIPO DE CONTROL','CE'),('SERVICIO','SR'),('NO DEFINIDO','ND')
    GO
    
    USE [PROSMART]
    INSERT INTO [dbo].[metodo]
               ([descmetodo])
         VALUES
               ('ORDEN DE COMPRA'),('PAGO DE SERVICIO'),('OTROS')
    GO
    
    USE [PROSMART]
    INSERT INTO [dbo].[envios]
               ([codigoenvios]
               ,[descmetodo]
               ,[bajaenvios])
         VALUES
               (1,'URGENTE',0),(2,'SERVICIO NORMAL',0),(3,'OCURRE',0),(4,'RECOLECCIÓN EN OFICINA',0)
    GO
    

    No se si estoy haciendo algo mal, quisiera orientación al respecto. Gracias


    SQL ADICTO Queriendo aprender cada día mas...

    miércoles, 6 de junio de 2018 19:04

Todas las respuestas

  • Hola.

    Intenta poniendo después del ELSE un BEGIN y, al final del código, un END.

    Saludos,


    Guillermo Taylor F.
    MVP Data Platform & IT Pro
    Mi Blog

    miércoles, 6 de junio de 2018 19:13
  • Hola, Pasa lo mismo  :(

    SQL ADICTO Queriendo aprender cada día mas...

    miércoles, 6 de junio de 2018 19:33
  • Hola Ali Reyes:

    No creo que puedas cambiar de contexto en medio de una transaccion.

    Porque no metes la creacion dentro de un procedure, y haces el if en otro.

    CREATE PROCEDURE SP_CREARBBDD
    AS
    ....
    ....
    RETURN
    
    
    USE [master]
    GO
    IF EXISTS (Select * from SysDataBases where name = N'PROSMART') 
    
    PRINT 'Ya existe la BD'
    
    ELSE
    EXEC SP_CREARBBDD;
    
    
    

    Un saludo

    miércoles, 6 de junio de 2018 19:44
  • Creo el SP y me lo crea hasta aca:

    USE [master]
    GO
    /****** Object:  StoredProcedure [dbo].[SP_CREARBBDD]    Script Date: 06/06/2018 04:37:22 PM ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    ALTER PROCEDURE [dbo].[SP_CREARBBDD]
    AS
    CREATE DATABASE [PROSMART]
     CONTAINMENT = NONE
     ON  PRIMARY 
    ( NAME = N'PROSMART', FILENAME = N'C:\Program Files (x86)\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\PROSMART.mdf' , SIZE = 5120KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
     LOG ON 
    ( NAME = N'PROSMART_log', FILENAME = N'C:\Program Files (x86)\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\PROSMART_log.ldf' , SIZE = 2048KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
    

    Me da le siguiente error:

    Mens 2714, Nivel 16, Estado 3, Procedimiento SP_CREARBBDD, Línea 1
    Ya hay un objeto con el nombre 'SP_CREARBBDD' en la base de datos.
    Mens. 5069, Nivel 16, Estado 1, Línea 45
    Error de la instrucción ALTER DATABASE.
    Consulta cancelada por el usuario.

    No he encontrado nada en la red, no puedo creer que sea tan complicado, :(


    SQL ADICTO Queriendo aprender cada día mas...

    miércoles, 6 de junio de 2018 20:40
  • Hola Ali Reyes:

    Estas utilizando GO para cada linea, como si fuera el mismo contexto y no lo es.

    Todas las instrucciónes que estan contenidas entre un GO y otro, estan finalizadas.

    GO delimita un lote de ejecución, de manera que no puedes hacer un if y dentro cosas cuando se ejecuta el GO, se terminan, las cosas. Al no tener begin y end, "el" entiende una sola instrucción y no te da error de sintaxis.

    Lo mismo con el procedimiento.

    La opción más fácil es dividir y separa.

    Create una serie de procedimientos a los cuales, llamas desde un procedimiento principal.

    En los procedimientos agrupa todos los alter y lo que quieras hacer que se puedan ejecutar en el mismo contexto.

    Por ejemplo

    ALTER DATABASE [PROSMART] SET ANSI_NULL_DEFAULT OFF 
    ALTER DATABASE [PROSMART] SET ANSI_NULLS OFF 
    ALTER DATABASE [PROSMART] SET ANSI_PADDING OFF 
    ALTER DATABASE [PROSMART] SET ANSI_WARNINGS OFF 
    ALTER DATABASE [PROSMART] SET ARITHABORT OFF 
    ALTER DATABASE [PROSMART] SET AUTO_CLOSE OFF 
    ALTER DATABASE [PROSMART] SET AUTO_SHRINK OFF 
    ALTER DATABASE [PROSMART] SET AUTO_UPDATE_STATISTICS ON 
    ALTER DATABASE [PROSMART] SET CURSOR_CLOSE_ON_COMMIT OFF 
    ALTER DATABASE [PROSMART] SET CURSOR_DEFAULT  GLOBAL 
    ALTER DATABASE [PROSMART] SET CONCAT_NULL_YIELDS_NULL OFF 
    ALTER DATABASE [PROSMART] SET NUMERIC_ROUNDABORT OFF 
    ALTER DATABASE [PROSMART] SET QUOTED_IDENTIFIER OFF 
    ALTER DATABASE [PROSMART] SET RECURSIVE_TRIGGERS OFF 
    ALTER DATABASE [PROSMART] SET  DISABLE_BROKER 
    ALTER DATABASE [PROSMART] SET AUTO_UPDATE_STATISTICS_ASYNC OFF 
    ALTER DATABASE [PROSMART] SET DATE_CORRELATION_OPTIMIZATION OFF 
    ALTER DATABASE [PROSMART] SET TRUSTWORTHY OFF 
    ALTER DATABASE [PROSMART] SET ALLOW_SNAPSHOT_ISOLATION OFF 
    ALTER DATABASE [PROSMART] SET PARAMETERIZATION SIMPLE 
    ALTER DATABASE [PROSMART] SET READ_COMMITTED_SNAPSHOT OFF 
    ALTER DATABASE [PROSMART] SET HONOR_BROKER_PRIORITY OFF 
    ALTER DATABASE [PROSMART] SET RECOVERY FULL 
    ALTER DATABASE [PROSMART] SET  MULTI_USER 
    ALTER DATABASE [PROSMART] SET PAGE_VERIFY CHECKSUM  
    ALTER DATABASE [PROSMART] SET DB_CHAINING OFF 
    ALTER DATABASE [PROSMART] SET FILESTREAM( NON_TRANSACTED_ACCESS = OFF ) 
    ALTER DATABASE [PROSMART] SET TARGET_RECOVERY_TIME = 0 SECONDS 
    ALTER DATABASE [PROSMART] SET DELAYED_DURABILITY = DISABLED 
    /****** Object:  Table [dbo].[budget]    Script Date: 11/04/2018 12:28:33 PM ******/
    SET ANSI_NULLS ON
    SET QUOTED_IDENTIFIER ON
    SET ANSI_PADDING ON
    CREATE TABLE [PROSMART].dbo.[budget](
    	[idbudget] [int] IDENTITY(1,1) NOT NULL,
    	[montobudget] [float] NULL,
    	[outbudget] [float] NULL,
    	[inbudget] [float] NULL,
    	[descripbudget] [varchar](150) NULL,
    	[fechabudget] [datetime] NULL,
    	[idpedidobudget] [int] NULL,
    	[idpobudget] [int] NULL,
     CONSTRAINT [PK_budget] PRIMARY KEY CLUSTERED 
    (
    	[idbudget] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    


    Observa que te he quitado todos los GO, de este bloque de ejemplo y para no cambiar el contexto USE tambien fuera. Como tu puedes invocar a una base de datos, aunque el contexto sea otro (estabas en master), pero las sentencias conllevan ya la definición de la base de datos.

    CREATE TABLE [PROSMART].dbo.[budget]

    Cuando por definicion, necesitas un requsito previo, por ejemplo tener la tabla creada para hacer la insercción, hay terminas el procedure 1.

    Comienzas con el procedure2.. que ya tendrá ejecutado su creación....

    Así sucesivamente hasta el final.

    Y en el procedimiento "central", llamas a exec procedure1

    exec procedure 2 @parametro1='nombre'

    exec procedure3

    Y con eso tienes solucionado el tema.

    Existen otras opciones, pero esta es la más fácil. En media hora lo tienes resuelto.

    Un saludo

    miércoles, 6 de junio de 2018 21:21
  • Perdón se me olvido el link de GO

    https://docs.microsoft.com/es-es/sql/t-sql/language-elements/sql-server-utilities-statements-go?view=sql-server-2017

    miércoles, 6 de junio de 2018 21:21
  • El script

    USE MASTER 
    GO
    IF EXISTS (Select * from SysDataBases where name = N'PROSMART') 
    
    PRINT 'Ya existe la BD'
    
    ELSE
    BEGIN
    
    /****** Object:  Database [PROSMART]    Script Date: 11/04/2018 12:28:33 PM ******/
    CREATE DATABASE [PROSMART]
     CONTAINMENT = NONE
     ON  PRIMARY 
    ( NAME = N'PROSMART', FILENAME = N'C:\Program Files (x86)\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\PROSMART.mdf' , SIZE = 5120KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
     LOG ON 
    ( NAME = N'PROSMART_log', FILENAME = N'C:\Program Files (x86)\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\PROSMART_log.ldf' , SIZE = 2048KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
    
    
    END;
    /*primero ejecutas esto*/

    Conforme la ejecución haya sido la esperada.

    USE PROSMART
    GO
    
    CREATE PROCEDURE SEGUNDO AS
    ALTER DATABASE [PROSMART] SET COMPATIBILITY_LEVEL = 120
    
    IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
    begin
    EXEC [PROSMART].[dbo].[sp_fulltext_database] @action = 'enable'
    end;
    RETURN 
    GO
    CREATE PROCEDURE TERCERO AS 
    ALTER DATABASE [PROSMART] SET ANSI_NULL_DEFAULT OFF 
    ALTER DATABASE [PROSMART] SET ANSI_NULLS OFF 
    ALTER DATABASE [PROSMART] SET ANSI_PADDING OFF 
    ALTER DATABASE [PROSMART] SET ANSI_WARNINGS OFF 
    ALTER DATABASE [PROSMART] SET ARITHABORT OFF 
    ALTER DATABASE [PROSMART] SET AUTO_CLOSE OFF 
    ALTER DATABASE [PROSMART] SET AUTO_SHRINK OFF 
    ALTER DATABASE [PROSMART] SET AUTO_UPDATE_STATISTICS ON 
    ALTER DATABASE [PROSMART] SET CURSOR_CLOSE_ON_COMMIT OFF 
    ALTER DATABASE [PROSMART] SET CURSOR_DEFAULT  GLOBAL 
    ALTER DATABASE [PROSMART] SET CONCAT_NULL_YIELDS_NULL OFF 
    ALTER DATABASE [PROSMART] SET NUMERIC_ROUNDABORT OFF 
    ALTER DATABASE [PROSMART] SET QUOTED_IDENTIFIER OFF 
    ALTER DATABASE [PROSMART] SET RECURSIVE_TRIGGERS OFF 
    ALTER DATABASE [PROSMART] SET  DISABLE_BROKER 
    ALTER DATABASE [PROSMART] SET AUTO_UPDATE_STATISTICS_ASYNC OFF 
    ALTER DATABASE [PROSMART] SET DATE_CORRELATION_OPTIMIZATION OFF 
    ALTER DATABASE [PROSMART] SET TRUSTWORTHY OFF 
    ALTER DATABASE [PROSMART] SET ALLOW_SNAPSHOT_ISOLATION OFF 
    ALTER DATABASE [PROSMART] SET PARAMETERIZATION SIMPLE 
    ALTER DATABASE [PROSMART] SET READ_COMMITTED_SNAPSHOT OFF 
    ALTER DATABASE [PROSMART] SET HONOR_BROKER_PRIORITY OFF 
    ALTER DATABASE [PROSMART] SET RECOVERY FULL 
    ALTER DATABASE [PROSMART] SET  MULTI_USER 
    ALTER DATABASE [PROSMART] SET PAGE_VERIFY CHECKSUM  
    ALTER DATABASE [PROSMART] SET DB_CHAINING OFF 
    ALTER DATABASE [PROSMART] SET FILESTREAM( NON_TRANSACTED_ACCESS = OFF ) 
    ALTER DATABASE [PROSMART] SET TARGET_RECOVERY_TIME = 0 SECONDS 
    ALTER DATABASE [PROSMART] SET DELAYED_DURABILITY = DISABLED 
    GO
    CREATE PROCEDURE TERCERO_CREACION AS
    /****** Object:  Table [dbo].[budget]    Script Date: 11/04/2018 12:28:33 PM ******/
    SET ANSI_NULLS ON
    SET QUOTED_IDENTIFIER ON
    SET ANSI_PADDING ON
    GO
    CREATE TABLE [PROSMART].[dbo].[budget](
    	[idbudget] [int] IDENTITY(1,1) NOT NULL,
    	[montobudget] [float] NULL,
    	[outbudget] [float] NULL,
    	[inbudget] [float] NULL,
    	[descripbudget] [varchar](150) NULL,
    	[fechabudget] [datetime] NULL,
    	[idpedidobudget] [int] NULL,
    	[idpobudget] [int] NULL,
     CONSTRAINT [PK_budget] PRIMARY KEY CLUSTERED 
    (
    	[idbudget] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY];
    RETURN 
    GO
    
    CREATE PROCEDURE CUARTO AS
    SET ANSI_PADDING OFF
    
    /****** Object:  Table [dbo].[cotiz_detalle]    Script Date: 11/04/2018 12:28:33 PM ******/
    SET ANSI_NULLS ON
    SET QUOTED_IDENTIFIER ON
    SET ANSI_PADDING ON
    CREATE TABLE [PROSMART].[dbo].[cotiz_detalle](
    	[idcotizdetalle] [int] IDENTITY(1,1) NOT NULL,
    	[descripitem] [varchar](150) NULL,
    	[umedidaitem] [varchar](50) NULL,
    	[preciounit] [float] NULL,
    	[ivaitem] [float] NULL,
    	[cantitem] [int] NULL,
    	[totalitem] [float] NULL,
    	[idcotizmaestro] [int] NOT NULL,
    	[idproducto] [int] NULL,
     CONSTRAINT [PK_cotiz_detalle] PRIMARY KEY CLUSTERED 
    (
    	[idcotizdetalle] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY];
    RETURN 
    GO
    CREATE PROCEDURE QUINTO AS
    SET ANSI_PADDING OFF
    /****** Object:  Table [dbo].[cotiz_maestro]    Script Date: 11/04/2018 12:28:33 PM ******/
    SET ANSI_NULLS ON
    SET QUOTED_IDENTIFIER ON
    SET ANSI_PADDING ON
    CREATE TABLE [PROSMART].[dbo].[cotiz_maestro](
    	[idcotizmaestro] [int] IDENTITY(1,1) NOT NULL,
    	[consecotiz] [varchar](20) NULL,
    	[descripcotiz] [nvarchar](50) NULL,
    	[fechacotiz] [datetime] NULL,
    	[fechaini] [datetime] NULL,
    	[fechafin] [datetime] NULL,
    	[fechaentrega] [datetime] NULL,
    	[fechaaprobcotiz] [datetime] NULL,
    	[idcotizempresa] [int] NULL,
    	[idmetodocotiz] [int] NULL,
    	[idvendedorcotiz] [int] NULL,
    	[idaprobcotiz] [int] NULL,
    	[idtipocotiz] [int] NULL,
    	[estadocotiz] [int] NULL,
    	[revisioncotiz] [int] NULL,
     CONSTRAINT [PK_cotiz_maestro] PRIMARY KEY CLUSTERED 
    (
    	[idcotizmaestro] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY];
    RETURN
    
    GO
    CREATE PROCEDURE SEXTO AS
    SET ANSI_PADDING OFF
    /****** Object:  Table [dbo].[departamento]    Script Date: 11/04/2018 12:28:33 PM ******/
    SET ANSI_NULLS ON
    SET QUOTED_IDENTIFIER ON
    SET ANSI_PADDING ON
    CREATE TABLE [PROSMART].[dbo].[departamento](
    	[iddepartamento] [int] IDENTITY(1,1) NOT NULL,
    	[descdepartamento] [varchar](50) NOT NULL,
    	[codigodepartamento] [varchar](10) NOT NULL,
    	[bajadep] [bit] NULL,
     CONSTRAINT [PK_departamento] PRIMARY KEY CLUSTERED 
    (
    	[iddepartamento] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY];
    RETURN
    GO
    CREATE PROCEDURE SEPTIMO AS
    SET ANSI_PADDING OFF
    /****** Object:  Table [dbo].[empleados]    Script Date: 11/04/2018 12:28:33 PM ******/
    SET ANSI_NULLS ON
    SET QUOTED_IDENTIFIER ON
    SET ANSI_PADDING ON
    CREATE TABLE [PROSMART].[dbo].[empleados](
    	[idempleado] [int] IDENTITY(1,1) NOT NULL,
    	[idempleado2] [varchar](5) NULL,
    	[nombempleado] [varchar](50) NULL,
    	[apempleado] [varchar](50) NULL,
    	[especempleado] [varchar](50) NULL,
    	[ingresoempleado] [datetime] NULL,
    	[telfempleado1] [varchar](50) NULL,
    	[telfempleado2] [varchar](50) NULL,
    	[emailempleado1] [varchar](50) NULL,
    	[emailempleado2] [varchar](50) NULL,
    	[iddepempleado] [int] NULL,
    	[idemprempleado] [int] NULL,
    	[fotoempleado] [text] NULL,
    	[comentempleado] [varchar](250) NULL,
    	[bajaempleado] [bit] NULL,
     CONSTRAINT [PK_empleados] PRIMARY KEY CLUSTERED 
    (
    	[idempleado] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY];
    RETURN
    GO
    CREATE PROCEDURE OCTAVO AS
    SET ANSI_PADDING OFF
    /****** Object:  Table [dbo].[empresas]    Script Date: 11/04/2018 12:28:33 PM ******/
    SET ANSI_NULLS ON
    SET QUOTED_IDENTIFIER ON
    SET ANSI_PADDING ON
    CREATE TABLE [PROSMART].[dbo].[empresas](
    	[idempresa] [int] IDENTITY(1,1) NOT NULL,
    	[idempresa2] [varchar](10) NOT NULL,
    	[idempresa3] [int] NULL,
    	[rfcempresa] [varchar](50) NOT NULL,
    	[nombreempresa] [varchar](100) NOT NULL,
    	[rsocialempresa] [varchar](150) NOT NULL,
    	[dirempresa] [varchar](150) NOT NULL,
    	[telfempresa] [varchar](50) NOT NULL,
    	[telfempresa2] [varchar](50) NULL,
    	[emailempresa1] [varchar](50) NOT NULL,
    	[emailempresa2] [varchar](50) NULL,
    	[webempresa] [varchar](50) NULL,
    	[comentempresa] [varchar](250) NULL,
    	[logoempresa] [text] NULL,
    	[idmetodoempresa] [int] NULL,
    	[idmonedaempresa] [int] NULL,
    	[bajaempresa] [bit] NULL,
     CONSTRAINT [PK_empresas] PRIMARY KEY CLUSTERED 
    (
    	[idempresa] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY];
    RETURN 
    GO
    CREATE PROCEDURE NOVENO AS
    SET ANSI_PADDING OFF
    /****** Object:  Table [dbo].[envios]    Script Date: 11/04/2018 12:28:33 PM ******/
    SET ANSI_NULLS ON
    SET QUOTED_IDENTIFIER ON
    SET ANSI_PADDING ON
    CREATE TABLE [PROSMART].[dbo].[envios](
    	[idmetodo] [int] IDENTITY(1,1) NOT NULL,
    	[codigoenvios] [int] NULL,
    	[descmetodo] [varchar](50) NOT NULL,
    	[bajaenvios] [bit] NOT NULL,
     CONSTRAINT [PK_envios] PRIMARY KEY CLUSTERED 
    (
    	[idmetodo] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY];
    RETURN
    GO
    CREATE PROCEDURE DECIMO AS
    SET ANSI_PADDING OFF
    /****** Object:  Table [dbo].[especialidad]    Script Date: 11/04/2018 12:28:33 PM ******/
    SET ANSI_NULLS ON
    SET QUOTED_IDENTIFIER ON
    SET ANSI_PADDING ON
    CREATE TABLE [PROSMART].[dbo].[especialidad](
    	[idespec] [int] IDENTITY(1,1) NOT NULL,
    	[codigoespec] [int] NOT NULL,
    	[descespec] [varchar](50) NOT NULL,
    	[bajaespec] [bit] NOT NULL
    ) ON [PRIMARY];
    RETURN
    GO
    CREATE PROCEDURE ONCE AS
    
    SET ANSI_PADDING OFF
    /****** Object:  Table [dbo].[metodo]    Script Date: 11/04/2018 12:28:33 PM ******/
    SET ANSI_NULLS ON
    SET QUOTED_IDENTIFIER ON
    SET ANSI_PADDING ON
    CREATE TABLE [PROSMART].[dbo].[metodo](
    	[idmetodo] [int] IDENTITY(1,1) NOT NULL,
    	[descmetodo] [varchar](50) NULL,
     CONSTRAINT [PK_metodo] PRIMARY KEY CLUSTERED 
    (
    	[idmetodo] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY];
    RETURN 
    GO 
    CREATE PROCEDURE DOCE AS
    
    SET ANSI_PADDING OFF
    /****** Object:  Table [dbo].[moneda]    Script Date: 11/04/2018 12:28:33 PM ******/
    SET ANSI_NULLS ON
    SET QUOTED_IDENTIFIER ON
    SET ANSI_PADDING ON
    CREATE TABLE [PROSMART].[dbo].[moneda](
    	[idmoneda] [int] IDENTITY(1,1) NOT NULL,
    	[descmoneda] [varchar](50) NOT NULL,
    	[simbolomoneda] [varchar](50) NOT NULL,
    	[bajamoneda] [bit] NOT NULL,
     CONSTRAINT [PK_moneda] PRIMARY KEY CLUSTERED 
    (
    	[idmoneda] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY];
    RETURN
    GO
    CREATE PROCEDURE TRECE AS
    
    SET ANSI_PADDING OFF
    
    /****** Object:  Table [dbo].[pais]    Script Date: 11/04/2018 12:28:33 PM ******/
    SET ANSI_NULLS ON
    SET QUOTED_IDENTIFIER ON
    SET ANSI_PADDING ON
    CREATE TABLE [PROSMART].[dbo].[pais](
    	[idpais] [int] IDENTITY(1,1) NOT NULL,
    	[iso2] [varchar](5) NOT NULL,
    	[iso3] [varchar](5) NOT NULL,
    	[pais] [varchar](50) NOT NULL,
    	[capitalpais] [varchar](50) NOT NULL,
    	[prefijopais] [int] NOT NULL,
     CONSTRAINT [PK_pais] PRIMARY KEY CLUSTERED 
    (
    	[idpais] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY];
    RETURN
    GO 
    CREATE PROCEDURE CATORCE AS
    
    
    SET ANSI_PADDING OFF
    
    /****** Object:  Table [dbo].[pedidos]    Script Date: 11/04/2018 12:28:33 PM ******/
    SET ANSI_NULLS ON
    
    SET QUOTED_IDENTIFIER ON
    
    SET ANSI_PADDING ON
    
    CREATE TABLE [PROSMART].[dbo].[pedidos](
    	[idpedido] [int] IDENTITY(1,1) NOT NULL,
    	[consecpedido] [varchar](50) NULL,
    	[fechapedido] [date] NULL,
    	[authpedido] [int] NULL,
    	[metodoconfpedido] [varchar](50) NULL,
    	[idempleadopedido] [int] NULL,
    	[cantpagospedido] [varchar](50) NULL,
    	[finipedido] [datetime] NULL,
    	[ffinpedido] [datetime] NULL,
    	[idcotizmaestropo] [int] NULL,
    	[bajapedido] [bit] NULL,
     CONSTRAINT [PK_pedidos] PRIMARY KEY CLUSTERED 
    (
    	[idpedido] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY];
    RETURN 
    GO
    
    CREATE PROCEDURE QUINCE AS
    SET ANSI_PADDING OFF
    /****** Object:  Table [dbo].[po_detalle]    Script Date: 11/04/2018 12:28:33 PM ******/
    SET ANSI_NULLS ON
    SET QUOTED_IDENTIFIER ON
    SET ANSI_PADDING ON
    CREATE TABLE [PROSMART].[dbo].[po_detalle](
    	[idpodetalle] [int] IDENTITY(1,1) NOT NULL,
    	[descripitemdetalle] [varchar](150) NULL,
    	[umpodetalle] [varchar](50) NULL,
    	[preciounitdetalle] [float] NULL,
    	[ivaitemdetalle] [float] NULL,
    	[cantitemdetalle] [int] NULL,
    	[totalitemdetalle] [float] NULL,
    	[idpomaestro] [int] NULL,
     CONSTRAINT [PK_po_detalle] PRIMARY KEY CLUSTERED 
    (
    	[idpodetalle] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY];
    RETURN
    GO
    CREATE PROCEDURE DIECISEIS AS
    
    SET ANSI_PADDING OFF
    /****** Object:  Table [dbo].[po_maestro]    Script Date: 11/04/2018 12:28:33 PM ******/
    SET ANSI_NULLS ON
    SET QUOTED_IDENTIFIER ON
    SET ANSI_PADDING ON
    CREATE TABLE [PROSMART].[dbo].[po_maestro](
    	[idpomaestro] [int] IDENTITY(1,1) NOT NULL,
    	[consecpomaestro] [varchar](30) NULL,
    	[descrippomaestro] [nvarchar](50) NULL,
    	[fechapomaestro] [datetime] NULL,
    	[plazopomaestro] [datetime] NULL,
    	[fechaentrega] [datetime] NULL,
    	[fechaaprobpomaestro] [datetime] NULL,
    	[idproveedorpomaestro] [int] NULL,
    	[metodopomaestro] [nvarchar](100) NULL,
    	[termenviopo] [nvarchar](100) NULL,
    	[idvendedorpomaestro] [int] NULL,
    	[idaprobpomaestro] [int] NULL,
    	[idtipopomaestro] [int] NULL,
    	[estadopomaestro] [int] NULL,
    	[revisionpomaestro] [int] NULL,
    	[idpedidopo] [int] NULL,
     CONSTRAINT [PK_po_maestro] PRIMARY KEY CLUSTERED 
    (
    	[idpomaestro] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY];
    RETURN 
    GO
    CREATE PROCEDURE DIECISIETE AS
    
    
    SET ANSI_PADDING OFF
    /****** Object:  Table [dbo].[productos]    Script Date: 11/04/2018 12:28:33 PM ******/
    SET ANSI_NULLS ON
    SET QUOTED_IDENTIFIER ON
    SET ANSI_PADDING ON
    CREATE TABLE [PROSMART].[dbo].[productos](
    	[idproducto] [int] IDENTITY(1,1) NOT NULL,
    	[idproducto2] [nvarchar](10) NULL,
    	[descproducto] [varchar](50) NOT NULL,
    	[costoproducto] [float] NOT NULL,
    	[ivaproducto] [float] NOT NULL,
    	[imgproducto] [text] NULL,
    	[umproducto] [varchar](50) NULL,
    	[numserieproducto] [varchar](50) NULL,
    	[bajaproducto] [bit] NULL,
    	[idproveedor] [int] NULL,
     CONSTRAINT [PK_productos] PRIMARY KEY CLUSTERED 
    (
    	[idproducto] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY];
    RETURN
    GO
    CREATE PROCEDURE DIECIOCHO AS
    
    SET ANSI_PADDING OFF
    /****** Object:  Table [dbo].[propietario]    Script Date: 11/04/2018 12:28:33 PM ******/
    SET ANSI_NULLS ON
    SET QUOTED_IDENTIFIER ON
    SET ANSI_PADDING ON
    CREATE TABLE [PROSMART].[dbo].[propietario](
    	[idpropietario] [int] IDENTITY(1,1) NOT NULL,
    	[idpropietario2] [varchar](3) NOT NULL,
    	[rfcpropietario] [varchar](50) NOT NULL,
    	[nombrepropietario] [varchar](100) NOT NULL,
    	[rsocialpropietario] [varchar](150) NOT NULL,
    	[dirpropietario] [varchar](150) NOT NULL,
    	[telfpropietario] [varchar](50) NOT NULL,
    	[telfpropietario2] [varchar](50) NULL,
    	[emailpropietario1] [varchar](50) NOT NULL,
    	[emailpropietario2] [varchar](50) NULL,
    	[webpropietario] [varchar](50) NULL,
    	[logopropietario] [text] NULL,
    	[bajapropietario] [bit] NULL,
     CONSTRAINT [PK_propietario] PRIMARY KEY CLUSTERED 
    (
    	[idpropietario] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY];
    RETURN
    GO
    CREATE PROCEDURE DIECINUEVE AS 
    
    SET ANSI_PADDING OFF
    /****** Object:  Table [dbo].[proveedores]    Script Date: 11/04/2018 12:28:33 PM ******/
    SET ANSI_NULLS ON
    SET QUOTED_IDENTIFIER ON
    SET ANSI_PADDING ON
    CREATE TABLE [PROSMART].[dbo].[proveedores](
    	[idproveedor] [int] IDENTITY(1,1) NOT NULL,
    	[idproveedor2] [varchar](5) NOT NULL,
    	[idproveedor3] [int] NULL,
    	[rfcproveedor] [varchar](50) NOT NULL,
    	[nombreprov] [varchar](50) NULL,
    	[rsocialprov] [varchar](50) NULL,
    	[dirproveedor] [varchar](50) NOT NULL,
    	[telfproveedor] [varchar](50) NOT NULL,
    	[telfproveedor2] [varchar](50) NULL,
    	[emailproveedor1] [varchar](50) NULL,
    	[emailproveedor2] [varchar](50) NULL,
    	[webproveedor] [varchar](50) NULL,
    	[idmetodoprov] [int] NULL,
    	[idmonedaprov] [int] NULL,
    	[logoproveedor] [text] NULL,
    	[comentprov] [varchar](250) NULL,
    	[bajaprov] [bit] NULL,
     CONSTRAINT [PK_proveedores] PRIMARY KEY CLUSTERED 
    (
    	[idproveedor] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY];
    GO
    CREATE PROCEDURE VEINTE AS
    
    
    SET ANSI_PADDING OFF
    /****** Object:  Table [dbo].[tareas]    Script Date: 11/04/2018 12:28:33 PM ******/
    SET ANSI_NULLS ON
    SET QUOTED_IDENTIFIER ON
    SET ANSI_PADDING ON
    CREATE TABLE [PROSMART].[dbo].[tareas](
    	[idtarea] [int] IDENTITY(1,1) NOT NULL,
    	[consectarea] [varchar](20) NULL,
    	[descriptare] [varchar](100) NULL,
    	[idempleadotarea] [int] NULL,
    	[iddeptarea] [int] NULL,
    	[fechainitarea] [datetime] NULL,
    	[fechafintarea] [datetime] NULL,
    	[avancetarea] [float] NULL,
    	[comentarea] [varchar](200) NULL,
    	[idpedidotarea] [int] NULL,
    	[bajatarea] [bit] NULL,
     CONSTRAINT [PK_tareas] PRIMARY KEY CLUSTERED 
    (
    	[idtarea] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY];
    RETURN
    GO
    CREATE PROCEDURE VEINTIUNO
    AS
    
    SET ANSI_PADDING OFF
    /****** Object:  Table [dbo].[tipocotiz]    Script Date: 11/04/2018 12:28:33 PM ******/
    SET ANSI_NULLS ON
    SET QUOTED_IDENTIFIER ON
    SET ANSI_PADDING ON
    CREATE TABLE [PROSMART].[dbo].[tipocotiz](
    	[idcotiz] [int] IDENTITY(1,1) NOT NULL,
    	[tipocotiz] [varchar](100) NOT NULL,
    	[abrecotiz] [varchar](2) NULL,
     CONSTRAINT [PK_tipocotiz] PRIMARY KEY CLUSTERED 
    (
    	[idcotiz] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY];
    RETURN
    GO
    CREATE PROCEDURE VEINTIDOS AS
    SET ANSI_PADDING OFF
    ALTER DATABASE [PROSMART] SET  READ_WRITE 
    RETURN
    GO
    CREATE PROCEDURE VEINTITRES AS
    
    INSERT INTO [PROSMART].[dbo].[tipocotiz]
               ([tipocotiz]
               ,[abrecotiz])
         VALUES
               ('PROYECTO','PJ'),('EQUIPO DE CONTROL','CE'),('SERVICIO','SR'),('NO DEFINIDO','ND')
    GO
    CREATE PROCEDURE VEINTICUATRO AS
    
    
    INSERT INTO [PROSMART].[dbo].[metodo]
               ([descmetodo])
         VALUES
               ('ORDEN DE COMPRA'),('PAGO DE SERVICIO'),('OTROS')
    GO
    CREATE PROCEDURE VEINTICINCO
    AS
    
    INSERT INTO [PROSMART].[dbo].[envios]
               ([codigoenvios]
               ,[descmetodo]
               ,[bajaenvios])
         VALUES
               (1,'URGENTE',0),(2,'SERVICIO NORMAL',0),(3,'OCURRE',0),(4,'RECOLECCIÓN EN OFICINA',0)
    GO
    

    Luego este, hasta la creacion de los 24 procedimientos almacenados

    Y por ultimo

    IF EXISTS (Select * from SysDataBases where name = N'PROSMART') 
    BEGIN
    EXEC SEGUNDO;
    EXEC TERCERO;
    EXEC CUARTO;
    EXEC QUINTO;
    EXEC SEXTO;
    EXEC SEPTIMO;
    EXEC OCTAVO;
    EXEC NOVENO;
    EXEC DECIMO;
    EXEC ONCE;
    EXEC DOCE;
    EXEC TRECE;
    EXEC CATORCe;
    EXEC QUINCE;
    EXEC DIECISEIS;
    EXEC DIECISIETE;
    EXEC DIECIOCHO;
    EXEC DIECINUEVE;
    EXEC VEINTE;
    EXEC VEINTIUNO;
    EXEC VEINTIDOS;
    EXEC VEINTITRES
    EXEC VEINTICUATRO;
    EXEC VEINTICINCO;
    
    END

    El resultado

    miércoles, 6 de junio de 2018 21:56