sql server dts: Hi,

I have a SSIS package with sql command for SQL server 2005 and VS2005.
First, I create a database in the database Master using the commande USE
[master] then I had tables in that new database using the commande USE [new
database] and the command USE does'nt work any more. All the tables are
created in the database Master and not in the new database. Before the
installation of SP2 for sqlserver 2005, this was working.

My commande is an ExecuteSQL task

The commande is .

USE [master]
create database [xxxx]
bla bla bla

USE [xxxx]
Create Tables
bla bla bla

and it cretaes tables in Master

To resolve this probleme now, i had to separate the task and I did make two
dataconnection, first create database with dataconnection Master, then
create tables with dataconnection on xxxx.

Before, I need not to do this, I still got another pc wich was not upgrade
with SP2 and it still works with the old procedure.

Thank's helping me.


--
D'avance merci
Meilleures salutations
Pierre

I wonder how this might have worked before
create database statements required to be in a seperate query (use keyword
"GO" between 2 queries in the same text stream), otherwise script will not
know database in time of "Use" close

"GO" is a client specific keyword, works in dts, ssis, and query analyser


[quoted text, click to view]

yes, in the command, I have

USE [master]
GO
/****** Objet : Database [Pharma] Date de génération du script :
04/21/2006 23:57:44 ******/
CREATE DATABASE [XXXX] ON PRIMARY
( NAME = N'Pharma', FILENAME = N'C:\Program Files\Microsoft SQL
Server\MSSQL.2\MSSQL\DATA\Pharma.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED,
FILEGROWTH = 1024KB )
LOG ON
Blh Blah Blah

then

USE [XXXX]
GO
Blah Blah Blah

and it deos not change database, before yes.

--
D'avance merci
Meilleures salutations
Pierre
"narayaana" <ugo.peltier@free.fr> a écrit dans le message de news:
etMDIeTbHHA.588@TK2MSFTNGP06.phx.gbl...
[quoted text, click to view]

yes, in the command, I have

USE [master]
GO
/****** Objet : Database [Pharma] Date de génération du script :
04/21/2006 23:57:44 ******/
CREATE DATABASE [Pharma] ON PRIMARY
( NAME = N'Pharma', FILENAME = N'C:\Program Files\Microsoft SQL
Server\MSSQL.2\MSSQL\DATA\Pharma.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED,
FILEGROWTH = 1024KB )
LOG ON
Blh Blah Blah

then

USE [Pharma]
GO
Blah Blah Blah

and it deos not change database, before yes.

PS : in the premium message, XXXX = Pharma
--
D'avance merci
Meilleures salutations
Pierre
"narayaana" <ugo.peltier@free.fr> a écrit dans le message de news:
etMDIeTbHHA.588@TK2MSFTNGP06.phx.gbl...
[quoted text, click to view]

What I do:

-- this does not work:
create database toto
use toto

--this works
create database toto
go
use toto

[quoted text, click to view]

this is exacly what I did and it deos'nt work. Did you try it, creta
database under master and the use pharma, GO, and creat tables.

I use version sql server 2005 and in sql, the same command works but not in
SSIS with the last version SP1. This has changed since the SP1 of SQL server
2005. I don't where is the link. I will try again on another project test
and let you know.

By the way, thank you very much.

--
D'avance merci
Meilleures salutations
Pierre
"narayaana" <ugo.peltier@free.fr> a écrit dans le message de news:
OmHpofUbHHA.1300@TK2MSFTNGP02.phx.gbl...
[quoted text, click to view]

[quoted text, click to view]

being currious per nature,
I have open an SSIS SP2 and lastest hotfix,

query is set to direct input type and text is:

use master
create database toto
go
use toto
select 'AAA' AS TEMP INTO TEMP_TABLE

after query I open query analyse and my table is there in toto

was it the result you expected?

Narayaana,

The result you get is what I expected.

I am very please that you are currious. As I told you, I'll try on an new
project the same procedure and let you know my result.
Thank's again for your help.

--
D'avance merci
Meilleures salutations
Pierre
"narayaana" <ugo.peltier@free.fr> a écrit dans le message de news:
%23HVjMmVbHHA.1300@TK2MSFTNGP02.phx.gbl...
[quoted text, click to view]

The problem is reproduce with this small exemple.
Here is the code I have in the SQL Task on the SSIS

USE [master]
GO
/****** Objet : Database [test] Date de génération du script :
03/23/2007 17:07:07 ******/
CREATE DATABASE [test] ON PRIMARY
( NAME = N'test', FILENAME = N'C:\Program Files\Microsoft SQL
Server\MSSQL.2\MSSQL\DATA\test.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED,
FILEGROWTH = 1024KB )
LOG ON
( NAME = N'test_log', FILENAME = N'C:\Program Files\Microsoft SQL
Server\MSSQL.2\MSSQL\DATA\test_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB ,
FILEGROWTH = 10%)
GO
EXEC dbo.sp_dbcmptlevel @dbname=N'test', @new_cmptlevel=90
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [test].[dbo].[sp_fulltext_database] @action = 'disable'
end
GO
ALTER DATABASE [test] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [test] SET ANSI_NULLS OFF
GO
ALTER DATABASE [test] SET ANSI_PADDING OFF
GO
ALTER DATABASE [test] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [test] SET ARITHABORT OFF
GO
ALTER DATABASE [test] SET AUTO_CLOSE OFF
GO
ALTER DATABASE [test] SET AUTO_CREATE_STATISTICS ON
GO
ALTER DATABASE [test] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [test] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [test] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [test] SET CURSOR_DEFAULT GLOBAL
GO
ALTER DATABASE [test] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [test] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [test] SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [test] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [test] SET ENABLE_BROKER
GO
ALTER DATABASE [test] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [test] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [test] SET TRUSTWORTHY OFF
GO
ALTER DATABASE [test] SET ALLOW_SNAPSHOT_ISOLATION OFF
GO
ALTER DATABASE [test] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [test] SET READ_WRITE
GO
ALTER DATABASE [test] SET RECOVERY FULL
GO
ALTER DATABASE [test] SET MULTI_USER
GO
ALTER DATABASE [test] SET PAGE_VERIFY CHECKSUM
GO
ALTER DATABASE [test] SET DB_CHAINING OFF
GO

USE [test]
GO
/****** Objet : Table [dbo].[client] Date de génération du script :
03/23/2007 17:08:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[client](
[nom] [nchar](10) NOT NULL,
[prenon] [nchar](10) NOT NULL
) ON [PRIMARY]

and the table client is in the database master. So it does'nt work in one
task and before it did.

--
D'avance merci
Meilleures salutations
Pierre
"narayaana" <ugo.peltier@free.fr> a écrit dans le message de news:
%23HVjMmVbHHA.1300@TK2MSFTNGP02.phx.gbl...
[quoted text, click to view]

the version of SSIS is 9.00.3042.00

--
D'avance merci
Meilleures salutations
Pierre
"narayaana" <ugo.peltier@free.fr> a écrit dans le message de news:
%23HVjMmVbHHA.1300@TK2MSFTNGP02.phx.gbl...
[quoted text, click to view]

Have searched, could not reproduce,
only idea i have is "go use db go" is in other process (sql spid), for
instance if connection pooling is used and in between GOs use each a
differente connection in pool, my version does not do so, even if I set
Connection Pooling to on,
workaround:
go use db /*NO GO*/ create table GO


[quoted text, click to view]

Thank's to you, I have found the solution.

On the property of the connection manager, I had to put the value
RetainSameConnection to True (it was set to false by default) and now it
works ???
I did not find much infoarmation about this feature but I will still have an
eye on it

Have a very good day and thank you for your help

--
D'avance merci
Meilleures salutations
Pierre
"narayaana" <ugo.peltier@free.fr> a écrit dans le message de news:
OQjJvZ3bHHA.4772@TK2MSFTNGP05.phx.gbl...
[quoted text, click to view]

Hello Pierre,


RetainSameConnection allows the connection manager to open and then each
use of the same connection manager is on the same connection. This can be
useful when trying to implement Native Transactions.


--

Allan Mitchell
http://wiki.sqlis.com | http://www.sqlis.com | http://www.sqldts.com |
http://www.konesans.com

[quoted text, click to view]

Hello Allan,

Could you tell me why I have to put RetainSameConnection = true with SQL
2005 SSIS SP2 and before it worked with RetainSameConnection = false ?

Thank you in advance if you read me.

--
D'avance merci
Meilleures salutations
Pierre
"Allan Mitchell" <allan@no-spam.sqldts.com> a écrit dans le message de news:
b067a6250f768c93e0103d45ca6@news.microsoft.com...
[quoted text, click to view]