how to connect to SQL server express 2005 database at the same time from both SSMSE and VB2005 a

Here is the situation:

I have SQL server express 2005 installed on my pc as instance SQLEXPRESS.

I have created a Visual Basic applicaion with the following as connection to the SQL server express 2005 running on the same PC:
****************************************************************************************************
Dim lconnectionString As String
Dim builder As New SqlConnectionStringBuilder
Dim cmd As New SqlCommand
Dim reader As SqlDataReader
Dim parameter As SqlParameter


builder("Data Source") = ".\SQLEXPRESS"
builder("Initial Catalog") = ""
builder("AttachDbFilename") = "C:\My Documents\Visual Studio 2005\Projects\abc\abc\abc.mdf"
builder("Integrated Security") = True
builder("User Instance") = True
lconnectionString = builder.ConnectionString

Dim sqlConnection1 As New SqlConnection(lconnectionString)

cmd.CommandText = "SP_add_collection"
cmd.CommandType = CommandType.StoredProcedure
cmd.Connection = sqlConnection1

sqlConnection1.Open()
*******************************************************************************************************************

It seems that i can not connect to the abc.mdf in SSMSE while the VB program is running. (ERROR:
Database 'C:\My Documents\Visual Studio 2005\Projects\abc\abc\abc.mdf' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details. (.Net SqlClient Data Provider) )

If i connect to the abc.mdf first in SSMSE, then run the VB program afterwards, it gives me the error on this line -- sqlConnection1.Open()

I want to be able to access the abc.mdf database with both SSMSE and VB at the same time. Could anyone help me on this ?

Thanks very much !

apple

I'll go out on a limb here and assume that you've attached the .mdf to SSMSE which is essentially just a pointer to the db. It is not technically a SQL database.

When you're connecting the .mdf through code, use the same connection as if you were connecting to MS Access.

If you have a stored procedure that uses the .mdf, use a sql connection.

Good luck,

Adamus

|||yes, i attached the .mdf file to the SSMSE. and i call the stored procedures in the VB application.

I want to see the result of data change of the VB application in the SSMSE right away, while the application updates the date in the table. is it possible ?|||

Yes it's possible but you have to connect directly to the .mdf not SSMS.

Adamus

sql

how to connect to SQL server express 2005 database at the same time from both SSMSE and VB20

Here is the situation:

I have SQL server express 2005 installed on my pc as instance SQLEXPRESS.

I have created a Visual Basic applicaion with the following as connection to the SQL server express 2005 running on the same PC:
****************************************************************************************************
Dim lconnectionString As String
Dim builder As New SqlConnectionStringBuilder
Dim cmd As New SqlCommand
Dim reader As SqlDataReader
Dim parameter As SqlParameter


builder("Data Source") = ".\SQLEXPRESS"
builder("Initial Catalog") = ""
builder("AttachDbFilename") = "C:\My Documents\Visual Studio 2005\Projects\abc\abc\abc.mdf"
builder("Integrated Security") = True
builder("User Instance") = True
lconnectionString = builder.ConnectionString

Dim sqlConnection1 As New SqlConnection(lconnectionString)

cmd.CommandText = "SP_add_collection"
cmd.CommandType = CommandType.StoredProcedure
cmd.Connection = sqlConnection1

sqlConnection1.Open()
*******************************************************************************************************************

It seems that i can not connect to the abc.mdf in SSMSE while the VB program is running. (ERROR:
Database 'C:\My Documents\Visual Studio 2005\Projects\abc\abc\abc.mdf' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details. (.Net SqlClient Data Provider) )

If i connect to the abc.mdf first in SSMSE, then run the VB program afterwards, it gives me the error on this line -- sqlConnection1.Open()

I want to be able to access the abc.mdf database with both SSMSE and VB at the same time. Could anyone help me on this ?

Thanks very much !

apple

I'll go out on a limb here and assume that you've attached the .mdf to SSMSE which is essentially just a pointer to the db. It is not technically a SQL database.

When you're connecting the .mdf through code, use the same connection as if you were connecting to MS Access.

If you have a stored procedure that uses the .mdf, use a sql connection.

Good luck,

Adamus

|||yes, i attached the .mdf file to the SSMSE. and i call the stored procedures in the VB application.

I want to see the result of data change of the VB application in the SSMSE right away, while the application updates the date in the table. is it possible ?|||

Yes it's possible but you have to connect directly to the .mdf not SSMS.

Adamus

How to connect to remote server

Hi All,

I have created my sql database script using SQL data publishing wizard, now i want to execute the same on my shared hosting database. But i am not getting any option to connect to remote server in SSMSE.

I have developed web based application with back hand as SQL Server 2005, so now i want to execute my script file onto the server. so how i will connect with my server using SSMSE ?

My application is located under www.briskonsales.com/sales

Any idea ?

Thanks,

Jay

You will have to work with your Hosting provider to obtain access through their firewalls to access their SQL Server instance.

|||Is the remote instance a SQL Server Express edition ?

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Very few hosting providers will allow you to connect to their server using SSMS; rather they provide their own set of management tools accessable through their site. If you log into your account you will likely find a set of links that take you to the management console for your hosted database. From there you should be able to load the script file that you saved from the DPW and run it to create your objects in the hosted database.

As others mention, contact your hosting provider if you have questions about managing your hosted database.

Mike

How to connect to DB with username and passwd?

Hi I've created a login in my SQL Server 2005 like this:

CREATE LOGIN testuser
WITH PASSWORD = 'TestPWD';
USE DB1;
CREATE USER testuser FOR LOGIN testuser;
GO

now i want to connect from a c# app with the following connection STring
"Data Source=TURM21;Initial Catalog= Db1; Database= Db1 ; USER = testuser; PASSWORD = TestPWD; pooling=false; "

and i get the following Exception:
SqlConnection Handle : System.Data.SqlClient.SqlException: A connection was successfully established with the server, but then an error occurred during the login process. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.)

CAn somebody help me? Whats wrong? Is it my connection string?

GreetzYou should use GRANT statement to grant to this user privillege use the DB1 before use that username to login to DB|||have granted access to user manually in DB.

Changed the Conn String an d now it works:

"Data Source=TURM21; Database= Db1 ; USER id= testuser; PASSWORD = testPWD; Trusted_Connection=True ";

Set the Trusted_Connection to true

how to connect to database in SQL Sever Managment Studio

hi

i have created a .aspx page and a sql database in visual studio ,, my question is how can i connect that database in SQL Server Managment Studio?

Just use SqlConnection to refer to any database on SQL Server as you likeYou can start from here:

http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.connectionstring(d=ide).aspx

|||

thanks for the answer ,, but i still need some help.. I i have never used the sql managment studio before...the sql database iam trying to connect to is on my harddrive in the App_data folder in my asp project ,,,, when i start SQL Managamenet studio it wants me to enter the

Server Name:

Server Type:

Authentication

so i typ in

Server Type: Database Engien

Authentication: Windows Authenication

Server Name: mycomputer\SQLEXPRESS this is set by defult

so when i click on connect , it connects to the mycomputer\SQLEXPRESS. am i suppose to find my database among the databases that is in the Object Explorer?

this is my connection string that i found in the web.config file

connectionString

="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\ASPNETDB.MDF;Integrated Security=True;User Instance=True"providerName="System.Data.SqlClient"/>

thank you for your help

by the way ,, the reason why i want to connect my to my database in sql management studio is that i want to enable full text search and indexing

|||

To manage database file in Management Studio, you have to 'register' the database file under the App_Data to the SQL instance. We can use detaching/attaching to do this: firstly in VS2005 right click the database file ->choose Detach; then in Management Studio right click Databases-> choose Attach. There are a lot of materials in SQL2005 Books Online, here is a start link:

http://msdn2.microsoft.com/en-us/library/ms190794.aspx

How to connect ExecuteSQLTask with data flow task

Hi, there;
I am doing my first SSIS project that moves data from dbf file to SQL2005.
What I did here is that I created a dataflow task (move data)with ASP.NET successfully (Very happy!). Now I want to create an ExecuteSQLTask that runs before data flow runs. This ExecuteSQLTask is used used to create a table if there is no destination table in the SQL2005.
This can be done easily in SSIS design mode, just need to connect those two box with a green line: )
I had a look example from MS website, but I didn't find a code example to connect this two task.
Does anybody know how to resolve this or have any link with sample code?

Big thanks here.

Not sure what the issue is. Just drag the green line to the data flow task.|||

I am creating SSIS package from C# manully, not using the SQL Server Business Intelligence Development Studio at all. So I'd like to know how to connect these two task using C# code.

Thanks.

|||

Try this:

.....

//Create the package
Package pkg = new Package();

//Add the tasks
TaskHost sqlTask = (TaskHost)pkg.Executables.Add("STOCK:SQLTask");
TaskHost pipelinetask = (TaskHost)pkg.Executables.Add("STOCK:PipelineTask");

//Add the PrecedenceConstraint
PrecedenceConstraint pc = pkg.PrecedenceConstraints.Add(sqltask, pipelinetask);
pc.Value = DTSExecResult.Success;

.....

HTH,
Ovidiu Burlacu

How to connect ADODB with Microsoft SQL Server Compact 3.5 (.NET Framework Data Provider for Mic

Hi

We are checking VB 9 (Orcas).

we connected to database created under with sql server 7. with this code

Public cn As New ADODB.Connection

Public Sub OpenDB()

cn.Open("Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial catalog=Reservation;Data Source=.")

End Sub

this code worked well.

we know sql7 is not compatiable with vista. please tell us how to connect it wiith sql2005 . we downloaded orcas express edition beta. we created a database also. please let u know how to connect with Microsoft SQL Server Compact 3.5 (.NET Framework Data Provider for Microsoft SQL Server Compact 3.5).

Rgds

Pramod

Hi Pramod,

VB 08 (orcas) changed a lot. You can now make a connection without having to type one line of code (using Typed dataset)

But this still depend on your need !

|||

Hi

thanks verymuch for your valuable support . We tried dataset also. it dint worked.

please check the code

'================Sample Code==========

Dim conn As New SqlConnection("Server=.\SQLEXPRESS;Database=test;Trusted_Connection=false;")

Dim ds As New DataSet

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

conn.Open()

Dim adp As New SqlDataAdapter("Select * From personal", conn)

adp.Fill(ds)

DataGridView1.DataSource = ds.Tables(0)

conn.Close()

'================Sample Code==========

Cannot open database "test" requested by the login. The login failed. Login failed for user 'Pramod\Administrator'.

plz tell how to build a connection string. (We tried datasource Configuration wizard)

Plz help

rgds

Pramod

|||

try this code

Code Snippet

'Store a connection string
Dim connectionstring As String = "Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Documents and Settings\Omar Abid\Mes Documents\data.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True"
'Create new SQL Connection
Dim sqlconnection As New SqlConnection(connectionstring)
Using sqlconnection
Dim sqlcommand As SqlCommand = sqlconnection.CreateCommand
Using sqlcommand
sqlcommand.CommandType = CommandType.Text
sqlcommand.CommandText = "select * from table1"
Dim adapter As New SqlDataAdapter(sqlcommand)
Using adapter
Dim datatable As New DataTable("user")
Using datatable
adapter.Fill(datatable)
DataGridView.DataSource = datatable

End Using
End Using
End Using
End Using

|||

Thanks dear

it worked well,

our practice is to use pass queries to database like

cn.execute (Insert into ((.......))

how the dame works with data adapter.

Plz help us with a sample code

rgds

Pramod

|||

Hi

please advice us regarding the code. is it the usual option available.

-

Dim ds As New DataSet

Dim adp As New SqlDataAdapter("Select max(" & Field & ") + 1 as MAxid from " & tableName & IIf(Condition <> vbNullString, " where " & Condition, ""), cn)

adp.Fill(ds)

If IsDBNull(ds.Tables(0).Columns("MaxID")) Then

GetMaxID = 1

Else

GetMaxID = ds.Tables(0).Columns("MaxID")

End If

--

|||

sorry i don't know a lot on SQL. But I think that Adapter is the new technology.

For VB 9 there's also LINQ you may be interested on it

|||Hi

how can i connect with a client system. (in case of server we are using a path from the system). we want to connect it from networks also)

How to connect ADODB with Microsoft SQL Server Compact 3.5 (.NET Framework Data Provider for Mic

Hi

We are checking VB 9 (Orcas).

we connected to database created under with sql server 7. with this code

Public cn As New ADODB.Connection

Public Sub OpenDB()

cn.Open("Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial catalog=Reservation;Data Source=.")

End Sub

this code worked well.

we know sql7 is not compatiable with vista. please tell us how to connect it wiith sql2005 . we downloaded orcas express edition beta. we created a database also. please let u know how to connect with Microsoft SQL Server Compact 3.5 (.NET Framework Data Provider for Microsoft SQL Server Compact 3.5).

Rgds

Pramod

Hi Pramod,

VB 08 (orcas) changed a lot. You can now make a connection without having to type one line of code (using Typed dataset)

But this still depend on your need !

|||

Hi

thanks verymuch for your valuable support . We tried dataset also. it dint worked.

please check the code

'================Sample Code==========

Dim conn As New SqlConnection("Server=.\SQLEXPRESS;Database=test;Trusted_Connection=false;")

Dim ds As New DataSet

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

conn.Open()

Dim adp As New SqlDataAdapter("Select * From personal", conn)

adp.Fill(ds)

DataGridView1.DataSource = ds.Tables(0)

conn.Close()

'================Sample Code==========

Cannot open database "test" requested by the login. The login failed. Login failed for user 'Pramod\Administrator'.

plz tell how to build a connection string. (We tried datasource Configuration wizard)

Plz help

rgds

Pramod

|||

try this code

Code Snippet

'Store a connection string
Dim connectionstring As String = "Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Documents and Settings\Omar Abid\Mes Documents\data.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True"
'Create new SQL Connection
Dim sqlconnection As New SqlConnection(connectionstring)
Using sqlconnection
Dim sqlcommand As SqlCommand = sqlconnection.CreateCommand
Using sqlcommand
sqlcommand.CommandType = CommandType.Text
sqlcommand.CommandText = "select * from table1"
Dim adapter As New SqlDataAdapter(sqlcommand)
Using adapter
Dim datatable As New DataTable("user")
Using datatable
adapter.Fill(datatable)
DataGridView.DataSource = datatable

End Using
End Using
End Using
End Using

|||

Thanks dear

it worked well,

our practice is to use pass queries to database like

cn.execute (Insert into ((.......))

how the dame works with data adapter.

Plz help us with a sample code

rgds

Pramod

|||

Hi

please advice us regarding the code. is it the usual option available.

-

Dim ds As New DataSet

Dim adp As New SqlDataAdapter("Select max(" & Field & ") + 1 as MAxid from " & tableName & IIf(Condition <> vbNullString, " where " & Condition, ""), cn)

adp.Fill(ds)

If IsDBNull(ds.Tables(0).Columns("MaxID")) Then

GetMaxID = 1

Else

GetMaxID = ds.Tables(0).Columns("MaxID")

End If

--

|||

sorry i don't know a lot on SQL. But I think that Adapter is the new technology.

For VB 9 there's also LINQ you may be interested on it

|||Hi

how can i connect with a client system. (in case of server we are using a path from the system). we want to connect it from networks also)

How to configure DataReader Source for DataFlow

Dear Friends,

I have to import dBASE files to SQL Server. For this I have created one ODBC connection manager for those dBASE files. This I have to set for DataReader Source. But I am unable to configure the DataReader Source.

So, Please tell me how to configure DataReader Source for ODBC connection Manager.

Eagerly waiting for your valuable reply............

Santosh

INDIA

Are you getting any error?

Once in the data reader; you should provide the query and the connection manager information.

|||

Hello Santosh

Instead of creating an ODBC Connection Manager, you should create a ADO.NET Connnection Manager that uses the OdbcClient provider, and configure it to use the DSN you've set up for dBASE. I haven't tried dBASE specifically, but as a general statement the DataReader Source works only with ADO.NET connections.

-David

How to configure DataReader Source for DataFlow

Dear Friends,

I have to import dBASE files to SQL Server. For this I have created one ODBC connection manager for those dBASE files. This I have to set for DataReader Source. But I am unable to configure the DataReader Source.

So, Please tell me how to configure DataReader Source for ODBC connection Manager.

Eagerly waiting for your valuable reply............

Santosh

INDIA

Are you getting any error?

Once in the data reader; you should provide the query and the connection manager information.

|||

Hello Santosh

Instead of creating an ODBC Connection Manager, you should create a ADO.NET Connnection Manager that uses the OdbcClient provider, and configure it to use the DSN you've set up for dBASE. I haven't tried dBASE specifically, but as a general statement the DataReader Source works only with ADO.NET connections.

-David

How to conditionally create Flat File Destination for Lookup Redirects

Hi all,

I am redirecting the critical lookup errors to the flat files. These flat files got created regardless if there are lookup errors or not.

Is there a better way to conditionally create them only when there are lookup errors?

Thanks.

Its not really possible, no.

What you could do is count the number of records (using a ROWCOUNT component). Then, probably in the OnPostExecute eventhandler for the data-flow, delete the file if the rowcount is zero.

Or you could push everything into a recordset destination and then, in the eventhandler, push the stuff into a flat file IF the rowcount is greater than zero.

-Jamie

how to compare what has been changed between yesterday and today

Hello
1/ I am looking for ways to find out what has been changed per each table.
2/ Everyday I have a full database back up bak file created - about 10G.
(about 400 tables)
3/ Every day I will do a full restore of the day before yesterday database
and yesterday database.
e.g. Name of databases: DayBeforeYestDB,yesterdayDB
4/ Question:
Any tools to generate created another database that will contain only the
list of tables with data changed.
or
do I have to write my own scripts
Regards
it com
www.red-gate.com
SQL Data Compare...best $295 you will ever spend...
Kevin Hill
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
"itcom" <jensonluk1@.netscape.net> wrote in message
news:B5163A1E-880F-4437-AE2C-AFACA43949CD@.microsoft.com...
> Hello
> 1/ I am looking for ways to find out what has been changed per each table.
> 2/ Everyday I have a full database back up bak file created - about 10G.
> (about 400 tables)
> 3/ Every day I will do a full restore of the day before yesterday database
> and yesterday database.
> e.g. Name of databases: DayBeforeYestDB,yesterdayDB
> 4/ Question:
> Any tools to generate created another database that will contain only the
> list of tables with data changed.
> or
> do I have to write my own scripts
>
> Regards
> --
> it com
|||Hi,
What actually u need to know .If i understand ur Ques. u want that what
data chnages took place between last 2 backups.
Red-gate is good it will set a auditing option so will come to know
what datachanges and in which table.
Otherwise u can write ur on script.
U can restore both the database and compare the record count.
Or enable a C2 Auditing mode.
from
Doller

how to compare what has been changed between yesterday and today

Hello
1/ I am looking for ways to find out what has been changed per each table.
2/ Everyday I have a full database back up bak file created - about 10G.
(about 400 tables)
3/ Every day I will do a full restore of the day before yesterday database
and yesterday database.
e.g. Name of databases: DayBeforeYestDB,yesterdayDB
4/ Question:
Any tools to generate created another database that will contain only the
list of tables with data changed.
or
do I have to write my own scripts
Regards
it comwww.red-gate.com
SQL Data Compare...best $295 you will ever spend...
Kevin Hill
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
"itcom" <jensonluk1@.netscape.net> wrote in message
news:B5163A1E-880F-4437-AE2C-AFACA43949CD@.microsoft.com...
> Hello
> 1/ I am looking for ways to find out what has been changed per each table.
> 2/ Everyday I have a full database back up bak file created - about 10G.
> (about 400 tables)
> 3/ Every day I will do a full restore of the day before yesterday database
> and yesterday database.
> e.g. Name of databases: DayBeforeYestDB,yesterdayDB
> 4/ Question:
> Any tools to generate created another database that will contain only the
> list of tables with data changed.
> or
> do I have to write my own scripts
>
> Regards
> --
> it com|||Hi,
What actually u need to know .If i understand ur Ques. u want that what
data chnages took place between last 2 backups.
Red-gate is good it will set a auditing option so will come to know
what datachanges and in which table.
Otherwise u can write ur on script.
U can restore both the database and compare the record count.
Or enable a C2 Auditing mode.
from
Doller

how to compare what has been changed between yesterday and today

Hello
1/ I am looking for ways to find out what has been changed per each table.
2/ Everyday I have a full database back up bak file created - about 10G.
(about 400 tables)
3/ Every day I will do a full restore of the day before yesterday database
and yesterday database.
e.g. Name of databases: DayBeforeYestDB,yesterdayDB
4/ Question:
Any tools to generate created another database that will contain only the
list of tables with data changed.
or
do I have to write my own scripts
Regards
--
it comwww.red-gate.com
SQL Data Compare...best $295 you will ever spend...
--
Kevin Hill
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
"itcom" <jensonluk1@.netscape.net> wrote in message
news:B5163A1E-880F-4437-AE2C-AFACA43949CD@.microsoft.com...
> Hello
> 1/ I am looking for ways to find out what has been changed per each table.
> 2/ Everyday I have a full database back up bak file created - about 10G.
> (about 400 tables)
> 3/ Every day I will do a full restore of the day before yesterday database
> and yesterday database.
> e.g. Name of databases: DayBeforeYestDB,yesterdayDB
> 4/ Question:
> Any tools to generate created another database that will contain only the
> list of tables with data changed.
> or
> do I have to write my own scripts
>
> Regards
> --
> it com|||Hi,
What actually u need to know .If i understand ur Ques. u want that what
data chnages took place between last 2 backups.
Red-gate is good it will set a auditing option so will come to know
what datachanges and in which table.
Otherwise u can write ur on script.
U can restore both the database and compare the record count.
Or enable a C2 Auditing mode.
from
Doller

How to clear the Conflict tables from database

Dear Friends
I have created a Mearge Replication with name TEST for database DBTEST I
have deleted the same Replicaiton but now in my Database DBTEST i see the
tables with name Conflict_TEST_TableName
How i can clean the same.
Your guidance will enable me to solve the problem.
Thanks and best regards
Shailesh.
Shailesh,
you can drop these tables using query analyser.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

How to choose the default hierarchy for a dimension?

I created a server-time dimension and generated several hierarchies within the same dimension, does anyone know how to select a particular hierarchy as the default one?

I didn't see it on the properties for the dimension or hierarchy.What is default hierarchy semantics that you are looking for ? I.e. what will it be used for ? What special treatment will it receive ?|||It's a time dimension and there are 3 hierarchies defined:
Reporting Year -> Reporting Quarter
Year - Quarter - Month - Date
Year - Week - Date

It seems as though the Reporting Year -> Reporting Quarter hierarchy is used by default, how would I change the configuration to use a different hierarchy by default?|||

Can you please explain what do you mean by

"It seems as though the Reporting Year -> Reporting Quarter hierarchy is used by default"

|||The 'first' heirarchy is the default hierarchy. IE: The left most heirarchy in your model.

C|||Chris - can you please explain what does "default hierarchy" means in AS ? Thanks !|||From your browsing client, If you grab a Dimension containing multiple hierarchies, and drag it over (by whatever means you're using) to your Rows, or Columns, or Filter etc... it will show your default hierarchy.|||Ah, that explains it. Sorry for my confusing terminology. Thanks, Chris + Mosha.

How to choose file structure on SQL database?

I created MDF file and LDF for one database on SQL server 2000.
I need to create more DNF files on other 5 logical drives for this datanase.
All these logical drives are located in SAN storage configured with RAID10.
Should I create one NDF on each logical drive OR create multiple DNF on each
logical drive?
Which way is better for performance?
Thanks,
Mike
What is DNF files? Is it NDF? Have you monitor your SQL Server to make such
decision?
"Mike Torry" <MikeTorry@.discussions.microsoft.com> wrote in message
news:FDBA14B3-18FC-4723-8D73-08A46ECD921C@.microsoft.com...
>I created MDF file and LDF for one database on SQL server 2000.
> I need to create more DNF files on other 5 logical drives for this
> datanase.
> All these logical drives are located in SAN storage configured with
> RAID10.
> Should I create one NDF on each logical drive OR create multiple DNF on
> each
> logical drive?
> Which way is better for performance?
> Thanks,
|||Hello,
Probably you should be talking about NDF files. Since it is SAN just try
creating the database in a Single Logical drive (1 logical drive for MDF
and another for LDF) and see how database performs. See the I/O queue length
counter in perfmon and seee how well it goes.
If it quue length is always <=1 then you are good. I have the similar
environemnt and I have created database based on this strategy.
I will recommend you to keep the files in multiple drives if you have
multiple controllers running. Otherwise even if you create files
in multiple logical drives you will not get any performance improvements.
Thanks
Hari
"Mike Torry" <MikeTorry@.discussions.microsoft.com> wrote in message
news:FDBA14B3-18FC-4723-8D73-08A46ECD921C@.microsoft.com...
>I created MDF file and LDF for one database on SQL server 2000.
> I need to create more DNF files on other 5 logical drives for this
> datanase.
> All these logical drives are located in SAN storage configured with
> RAID10.
> Should I create one NDF on each logical drive OR create multiple DNF on
> each
> logical drive?
> Which way is better for performance?
> Thanks,

How to choose file structure on SQL database?

I created MDF file and LDF for one database on SQL server 2000.
I need to create more DNF files on other 5 logical drives for this datanase.
All these logical drives are located in SAN storage configured with RAID10.
Should I create one NDF on each logical drive OR create multiple DNF on each
logical drive?
Which way is better for performance?
Thanks,Mike
What is DNF files? Is it NDF? Have you monitor your SQL Server to make such
decision?
"Mike Torry" <MikeTorry@.discussions.microsoft.com> wrote in message
news:FDBA14B3-18FC-4723-8D73-08A46ECD921C@.microsoft.com...
>I created MDF file and LDF for one database on SQL server 2000.
> I need to create more DNF files on other 5 logical drives for this
> datanase.
> All these logical drives are located in SAN storage configured with
> RAID10.
> Should I create one NDF on each logical drive OR create multiple DNF on
> each
> logical drive?
> Which way is better for performance?
> Thanks,|||Mike Torry wrote:
> I created MDF file and LDF for one database on SQL server 2000.
> I need to create more DNF files on other 5 logical drives for this datanas
e.
> All these logical drives are located in SAN storage configured with RAID10
.
> Should I create one NDF on each logical drive OR create multiple DNF on ea
ch
> logical drive?
> Which way is better for performance?
> Thanks,
Hi,
If it's logical drives rather than physical drives, it won't help you a
lot to split the files on several drives. If you want to increase
performance, the data will have to placed on different physical spindles
and also preferably on different controllers (in most cases you'll just
have your MDF files disks on one controller and your ldf files disks on
a second controller). If you have any influence on hos your SAN is being
configured, you should also consider have more spindles in an array
rather than having many arrays. Depending on the usage of your database
it will give a better performance to have more spindles to read from
rather than splitting the load on several arrays/disks.
Regards
Steen Schlüter Persson
Database Administrator / System Administrator|||Hello,
Probably you should be talking about NDF files. Since it is SAN just try
creating the database in a Single Logical drive (1 logical drive for MDF
and another for LDF) and see how database performs. See the I/O queue length
counter in perfmon and seee how well it goes.
If it quue length is always <=1 then you are good. I have the similar
environemnt and I have created database based on this strategy.
I will recommend you to keep the files in multiple drives if you have
multiple controllers running. Otherwise even if you create files
in multiple logical drives you will not get any performance improvements.
Thanks
Hari
"Mike Torry" <MikeTorry@.discussions.microsoft.com> wrote in message
news:FDBA14B3-18FC-4723-8D73-08A46ECD921C@.microsoft.com...
>I created MDF file and LDF for one database on SQL server 2000.
> I need to create more DNF files on other 5 logical drives for this
> datanase.
> All these logical drives are located in SAN storage configured with
> RAID10.
> Should I create one NDF on each logical drive OR create multiple DNF on
> each
> logical drive?
> Which way is better for performance?
> Thanks,

How to choose file structure on SQL database?

I created MDF file and LDF for one database on SQL server 2000.
I need to create more DNF files on other 5 logical drives for this datanase.
All these logical drives are located in SAN storage configured with RAID10.
Should I create one NDF on each logical drive OR create multiple DNF on each
logical drive?
Which way is better for performance?
Thanks,Mike
What is DNF files? Is it NDF? Have you monitor your SQL Server to make such
decision?
"Mike Torry" <MikeTorry@.discussions.microsoft.com> wrote in message
news:FDBA14B3-18FC-4723-8D73-08A46ECD921C@.microsoft.com...
>I created MDF file and LDF for one database on SQL server 2000.
> I need to create more DNF files on other 5 logical drives for this
> datanase.
> All these logical drives are located in SAN storage configured with
> RAID10.
> Should I create one NDF on each logical drive OR create multiple DNF on
> each
> logical drive?
> Which way is better for performance?
> Thanks,|||Mike Torry wrote:
> I created MDF file and LDF for one database on SQL server 2000.
> I need to create more DNF files on other 5 logical drives for this datanase.
> All these logical drives are located in SAN storage configured with RAID10.
> Should I create one NDF on each logical drive OR create multiple DNF on each
> logical drive?
> Which way is better for performance?
> Thanks,
Hi,
If it's logical drives rather than physical drives, it won't help you a
lot to split the files on several drives. If you want to increase
performance, the data will have to placed on different physical spindles
and also preferably on different controllers (in most cases you'll just
have your MDF files disks on one controller and your ldf files disks on
a second controller). If you have any influence on hos your SAN is being
configured, you should also consider have more spindles in an array
rather than having many arrays. Depending on the usage of your database
it will give a better performance to have more spindles to read from
rather than splitting the load on several arrays/disks.
--
Regards
Steen Schlüter Persson
Database Administrator / System Administrator|||Hello,
Probably you should be talking about NDF files. Since it is SAN just try
creating the database in a Single Logical drive (1 logical drive for MDF
and another for LDF) and see how database performs. See the I/O queue length
counter in perfmon and seee how well it goes.
If it quue length is always <=1 then you are good. I have the similar
environemnt and I have created database based on this strategy.
I will recommend you to keep the files in multiple drives if you have
multiple controllers running. Otherwise even if you create files
in multiple logical drives you will not get any performance improvements.
Thanks
Hari
"Mike Torry" <MikeTorry@.discussions.microsoft.com> wrote in message
news:FDBA14B3-18FC-4723-8D73-08A46ECD921C@.microsoft.com...
>I created MDF file and LDF for one database on SQL server 2000.
> I need to create more DNF files on other 5 logical drives for this
> datanase.
> All these logical drives are located in SAN storage configured with
> RAID10.
> Should I create one NDF on each logical drive OR create multiple DNF on
> each
> logical drive?
> Which way is better for performance?
> Thanks,

How to check whether SSIS is installed

I'm using the import wizard to create a new table from a flat file source. The table gets created but no data gets copied. What's wrong? Here's the report:

Operation stopped...

- Initializing Data Flow Task (Success)

- Initializing Connections (Success)

- Setting SQL Command (Success)

- Setting Source Connection (Success)

- Setting Destination Connection (Success)

- Validating (Error)
Messages
* Error 0xc00470fe: Data Flow Task: The product level is insufficient for component "Source - inSsrc_txt" (1).
(SQL Server Import and Export Wizard)

* Error 0xc00470fe: Data Flow Task: The product level is insufficient for component "Data Conversion 1" (43).
(SQL Server Import and Export Wizard)

- Prepare for Execute (Stopped)

- Pre-execute (Stopped)

- Executing (Success)

- Copying to [BayadaPortal].[dbo].[inSsrc] (Stopped)

- Post-execute (Stopped)

- Cleanup (Stopped)

Please see this thread:
http://forums.microsoft.com/msdn/ShowPost.aspx?PostID=110020

SSIS ships as part of SQL 2005, but the setup give you a choice of whether to install it or not.

Please rerun the setup. In one of the first screens the setup program asks which components to installs, there are 5 checkboxes total: SQL/SSAS/SSRS/SSIS and Workstation components. Please make sure the SSIS check box is checked.

|||"Integration Services" is greyed-out as an installation option, I'm guessing, because I'm running XP. It seems odd that I can install Management Studio but cannot use the import-export wizard. Is this right? IS is not considered to be a "client tool" which the documentation says can be installed on XP?|||

Thanks for reporting this problem, it seems like we have a bug here.

Some components of SSIS (e.g. designer) are installed and work with 'Workstation Components' install. Other components of SSIS (e.g. service, dtexec, and dtexecui) require installation of 'Integration Services'.

In current build the wizard can't execute the package it created without 'Integration Services' installation, which for Enterprise Edition means you need Server OS. You can still save the package and run or schedule it on machine with SSIS installed.

We will fix this problem for SP1 release, the wizard should be able to run the package with just 'Workstation Components' install. Unfortunately, we missed this problem.

Thanks,
Michael.

|||I got around the problem (hope I haven't created any others) by uninstalling Enterprise Edition and installing Developer Edition which allows all installation options. I added IS to the install and then successfully ran the import/export wizard.|||

I ran into the same thing in September CTP with an attempted import of an Excel 2003 spreadsheet. I had installed SSIS and was consistently running numerous fairly sophisticated ETL packages between MSSQL Server 2005, MSSQL Server 2000, Oracle and other sources. It does not appear that the error is related to SSIS not being installed.

Thanks.

|||And BTW - I immediately got it to work after importing the sheet into MS Access, and then using MS Access as the import source for MSSQL Server 2005 ...

Thanks.|||

I'm getting this same error when using the RTM release of the SQL Management Studio Import wizard to import data from Excel into SQL 2000. Does anyone know how to fix this?
TITLE: SQL Server Import and Export Wizard

Error 0xc00470fe: Data Flow Task: The product level is insufficient for component "Source - AgentDirectoryDownload$" (1).

|||I also get this error with the RTM release of Management Studio. It seems ridiculous that the Import/Export wizard can't be run on a machine with just the workstation tools installed. I really don't want to install Developer Edition on all of the machines here. Is there no other workaround? Import/Export seems to be a natural function of a client-only installation.

Sean Carpenter|||

We've reconsidered this issue, and will be providing a fix for SP1 release.

For now, you need to install SSIS to run the package in the wizard.

|||I would really like to see this available as a hotfix before a service pack. Our company does a lot of data loading utilizing the Import/Export wizard and this issue will prevent us from upgrading to SQL Server 2005.

Sean Carpenter|||

If this is blocking your move to 2005, you should consider opening a case with customer support, reviewing with them the content of this thread, and pressing for the hotfix.

With few exceptions, hotfixes are issued in response to escalated customer support calls.

jkh

|||I too am experiencing this same problem. I am using the standard version of SQL Server Management Studio that I obtained for free at the SQL Server 2005 launch party in San Francisco. I am running Windows XP. I have tried to migrate tables from my remote SQL Server 2000 DB without success. Exporting to Access was successful, but when I import to the new DB I get the following errors:
- Pre-execute (Error)
Messages
Error 0xc0202009: Data Flow Task: An OLE DB error has occurred. Error code: 0x80040E21.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E21 Description: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".
(SQL Server Import and Export Wizard)

Error 0xc0202025: Data Flow Task: Cannot create an OLE DB accessor. Verify that the column metadata is valid.
(SQL Server Import and Export Wizard)

Error 0xc004701a: Data Flow Task: component "Destination - flo_layouts" (46) failed the pre-execute phase and returned error code 0xC0202025.
(SQL Server Import and Export Wizard)
I just wanted to weigh in and say that it would seem like this would be important enough for a quick 'hotfix'. Ease of migration using these tools is by far the most important reason I switched from MySQL to SQL Server and with these functions not working it is a real bummer.
Can anyone suggest a temporary work-aruond?
-Damien
|||Damien, your problem is completely unrelated to "product level" issue. I would suggest starting another thread specific to your issue, so that someone who knows this area might notice and help.|||We are having the same problem, only we get it on the server that SQL is installed on. We are running the 64bit RTM version downloaded from MSDN, with all options installed. I get the same errors, if I try to import from Excel, csv and sometimes from other tables on the same 2005 database. This last one is only sometimes.

CJ