SQL EXPRESS Connection Problem

[S37N]

I have Visual Studio 2010, with SQL EXPRESS which came with VS2010 from what I understand. I built a database in my VB.NET project, and am trying to copy data from the corporate SQL SERVER to my VB.NET created database (SQL EXPRESS).

Below I've included my script, with both connection strings. The SQL SERVER version works, but not the SQL EXPRESS one. If anyone could led me in the right direction, it would be much appreciated.

    Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
        
        'SQL EXPRESS COnnection String
        Dim con As SqlConnection = New SqlConnection("Server=OVP-L-R8MXE5M\SQLEXPRESS;" & "Database=dbTest;" & "Trusted_Connection=TRUE;")
        
        'SQL SERVER COnnection String
        'Dim con As SqlConnection = New SqlConnection("Server = ***-*-*******;" & "Database = CMP;" & "Trusted_Connection=TRUE")
        
        con.Open()

        Dim cmd As SqlCommand = New SqlCommand("INSERT INTO dbTest.cbo.tblFactSales (BILLTO,BRANCHPLANT,COMPANY,DATASRC,DATATYPE,FRTHANDLE,MODE,ORDERTYPE,ORIGINPLANT,PRODUCT,RPTCURRENCY,SALESDATA,SHIPTO,TIMEID,SIGNEDDATA,SOURCE ) " & _
                                                "SELECT CMP.dbo.tblFactSales.*  " & _
                                                "FROM CMP.dbo.tblFactSales " & _
                                                "WHERE DATATYPE='FORECAST' AND TIMEID='20120700'", con)

        cmd.ExecuteNonQuery()

        MessageBox.Show("Records Moved Successfully.", "Move Complete", _
            MessageBoxButtons.OK, MessageBoxIcon.Information, MessageBoxDefaultButton.Button1)
    End Sub

[smendoza]

What error does it throw?

[S37N]

What error does it throw?

so sorry, forgot the most important part...
When I try to connect with the SQLEXPRESS connection, the error reads: Cannot open database dbTest requested by the login. The login failed. Login failed for user ****

it's my corporate laptop. un/pw should be identical... i think :P

[smendoza]

Well, it's saying the login is incorrect. It looks like you're trying to use Win auth to login into the db. I'd start by trying to connect to the db through SSMS first. If it throws the same error with smss then we have a prob.

[smendoza]

Oh and whats the error number sql server is returning like Msg 18456, Level 14, State 1

[S37N]

Well, it's saying the login is incorrect. It looks like you're trying to use Win auth to login into the db. I'd start by trying to connect to the db through SSMS first. If it throws the same error with smss then we have a prob.

i cant see the db from SSMS. The only dbs i see when i log into SSMS are the system dbs (master, model, msdb, and tempdb) i created this db inside visual studio.

Oh and whats the error number sql server is returning like Msg 18456, Level 14, State 1

Here's the message info I get from Visual Studio
Msg: 4060
Level: I do not know where to find this.
State: 1

I have attached a screenshot.

[szlamany]

i cant see the db from SSMS. The only dbs i see when i log into SSMS are the system dbs (master, model, msdb, and tempdb)

Well - that's kind of a deal breaker!

btw - it's not that "i cant see the db from SSMS" - it's that the SERVICE running cannot see the DB.

What makes you think you have properly attached the database the local sqlexpress instance running on the workstation??

[smendoza]

I agree with szlamany..... I'd start but getting the db installed in instance on your pc. I usually create my DB's with SSMS. Once you have the db running on the instance you should be fine, you might need to enable remote connections if you haven't already.

[szlamany]

MS SSMS Express is a free download...

btw - I always use BACKUP and RESTORE to move DB from production server to test servers

Something like this

--drop database stufiles
/*
BACKUP DATABASE Stufiles
 TO DISK = 'C:\SQL Backup\Stufiles.bak'*/
RESTORE FILELISTONLY 
   FROM DISK = 'd:\Stufiles_backup_201203292100.bak'
RESTORE DATABASE Stufiles
   FROM DISK = 'd:\Stufiles_backup_201203292100.bak'
   WITH MOVE 'Stufiles_data' TO 'd:\SQL data\Stufiles.mdf',
   MOVE 'Stufiles_log' TO 'd:\SQL Logs\Stufiles.ldf'

The BACKUP is commented out at the top of the script - I run that on the production server...

Then bring back a .BAK file and restore it making sure to designate nice intelligent locations for the MDF and LDF to appear into.

RESTORE FILELISTONLY is just that - displays a file list in the query response pane - so you can see what the "logical" names of the DATA and LOG sections are in the DB.

[S37N]

ok, perhaps im using the incorrect terminology. here's what I did in Visual Studio, step by step.

1. Under Server Explorer, on Data Connections, I clicked "Add Connection".
2. I change Data Source to Microsoft SQL Server Database File.
3. I typed in my database name (dbTest) for Database file name.
4. Selected "Use Windows Authentication".
5. Test Connection failed, since it was a new database, so I clicked OK.
6. It then notified me that I was creating a NEW database.
7. Once database appeared in Server Explorer, I created right-clicked on :"Tables", and selected "Add new Table".

I have copied some data manually (copy/paste) into this table from EXCEL, and I can right-click the table, and can create Queries against it through Server Explorer, so it's clearly has data in it.

I have logged into SSMS SEVERAL times, and dont see this database ANYWHERE, only the system databases that come with SQL EXPRESS

Maybe I'm more lost than I initially thought.

[S37N]

MS SSMS Express is a free download...

btw - I always use BACKUP and RESTORE to move DB from production server to test servers

Something like this

--drop database stufiles
/*
BACKUP DATABASE Stufiles
 TO DISK = 'C:\SQL Backup\Stufiles.bak'*/
RESTORE FILELISTONLY 
   FROM DISK = 'd:\Stufiles_backup_201203292100.bak'
RESTORE DATABASE Stufiles
   FROM DISK = 'd:\Stufiles_backup_201203292100.bak'
   WITH MOVE 'Stufiles_data' TO 'd:\SQL data\Stufiles.mdf',
   MOVE 'Stufiles_log' TO 'd:\SQL Logs\Stufiles.ldf'

The BACKUP is commented out at the top of the script - I run that on the production server...

Then bring back a .BAK file and restore it making sure to designate nice intelligent locations for the MDF and LDF to appear into.

RESTORE FILELISTONLY is just that - displays a file list in the query response pane - so you can see what the "logical" names of the DATA and LOG sections are in the DB.

i dont need the whole table, but rather a subset. there's ~130 million rows in the table. I only need ~300K. if i cant figure it out, I may have to do that.

[szlamany]

Well - that isn't how it works.

A database is a collection of tables.

You must bring a DATABASE in whole from production to test.

If you want just one table then you need to "extract" that data into a good text format - I like tab delimited.

Then you create a NEW DATABASE - create a single TABLE within - and then BULK INSERT that data from the text file into the table.

Those are your two choices.

You never explained in the OP that you wanted to work with a single table...

[S37N]

ok, I think I understand what I need to do.

1. connect to Production server
2. SELECT statement for data I want to retrieve
3. Fill selected data into DataSet
4. close production server connection
5. connect to dbtest database inside Visual Studio
6. INSERT DataSet from first query into dbTest (inside Visual Studio)
7. close connection

[szlamany]

You do not need to write your own code to move data from a table in one database to a table in another database.

All that is given to you with the SSMS utility and T-SQL "BULK INSERT" statement.

[S37N]

as i said before, I can NOT access the table I created in Visual Studio through SSMS, as it is NOT in my list of databases within SSMS.

See screenshots.

[S37N]

heres a picture of my server explorer inside visual studio

[szlamany]

You created the DB from within VS - that means VS put it someplace that it's hiding from you. You could probably hunt around the DATA CONNECTIONS in the SERVER EXPLORER with right-clicks and looking at properties and what not...

But why not create it within SSMS? So you know it's a DB attached to the SQL EXPRESS instance you know of?

Or - can you open a query window from within VS? I never tried that as I prefer the actual SSMS tool for working with a SQL instance.

[S37N]

i tried to make the db in SSMS to start, but got a permissions error, so I went back to visual studio to do it.

also, I did NOT use "Create New SQl Server Database" option in Visual Studio, I used "Add Connection" option.

see screenshots

[szlamany]

btw - you might want to edit that image - I see you wanted to redact specific info on your username...

Let's get you so you can create the DB from within SSMS - you seem hell bent on working with the DB from within SSMS - so let's make it there in the first place.

Who created this SQLExpress instance? Who installed it? What username? Was it a domain username?

Are you an admin on this workstation? Can you go into the SECURITY branch in SSMS and see un's?

[smendoza]

ok, perhaps im using the incorrect terminology. here's what I did in Visual Studio, step by step.

1. Under Server Explorer, on Data Connections, I clicked "Add Connection".
2. I change Data Source to Microsoft SQL Server Database File.
3. I typed in my database name (dbTest) for Database file name.
4. Selected "Use Windows Authentication".
5. Test Connection failed, since it was a new database, so I clicked OK.
6. It then notified me that I was creating a NEW database.
7. Once database appeared in Server Explorer, I created right-clicked on :"Tables", and selected "Add new Table".

I have copied some data manually (copy/paste) into this table from EXCEL, and I can right-click the table, and can create Queries against it through Server Explorer, so it's clearly has data in it.

I have logged into SSMS SEVERAL times, and dont see this database ANYWHERE, only the system databases that come with SQL EXPRESS

Maybe I'm more lost than I initially thought.

You just created the physical data file. You still need to attach the physical data file to the instance of the sqlserver.

[S37N]

btw - you might want to edit that image - I see you wanted to redact specific info on your username...

Let's get you so you can create the DB from within SSMS - you seem hell bent on working with the DB from within SSMS - so let's make it there in the first place.

Who created this SQLExpress instance? Who installed it? What username? Was it a domain username?

Are you an admin on this workstation? Can you go into the SECURITY branch in SSMS and see un's?

im only worried about redacting our company domain, but thank you.

i dont really care which method i use, but im new at this, so im learning as i go. in the end, i would like to access my "test" dbs from both VS & SSMS.

im just a developer, and am not listed an an admin on the machine. our Technical Services dept set it all up, so I would assume it was a domain username.

screenshot of security tree within SSMS attached. my UN is NOT listed anywhere.

[S37N]

You just created the physical data file. You still need to attach the physical data file to the instance of the sqlserver.

how do i do that? i didnt see that in the tutorial I went through

[szlamany]

Stop - and let's focus here.

You do not have rights to the instance you are trying to work with.

You need to fix this first - otherwise it's all for nothing.

Stop trying to create db and tables - let's just get you connected to the instance.

Whoever has rights to that instance needs to give you admin rights so you can play.

If they don't want to do that then they have to give you specific db and table create permissions.

[szlamany]

im only worried about redacting our company domain, but thank you.

Yeah - that's what I see in that screenshot - right?

[S37N]

Yeah - that's what I see in that screenshot - right?

ok, i see it now, and have hidden it.

also, I have reported the SSMS permissions error to my HelpDesk.

[S37N]

ok, i now have admin rights to my machine.

will i need to remove all dbTest references within Visual Studio, and recreate my db in SSMS, or is there a way to make SSMS recognize the dbTest I have made already?

[szlamany]

You would have to find the physical MDF/LDF files and attach them.

How much work have you put into creating that table? Does it already have data loaded that you can see from VS??

[S37N]

You would have to find the physical MDF/LDF files and attach them.

How much work have you put into creating that table? Does it already have data loaded that you can see from VS??

yes, but the data is easily replaced. i can start over with new db.

[szlamany]

I always prefer to make a script to create a table - and execute that in a query window in SSMS.

Then you can save the script as a .SQL text file (like a notepad file)...

Use Acctfiles
Go
Drop Table PayAddress_T
Go
Create Table PayAddress_T
	(MasId		int not null
	,LastPP		varchar(7) not null
	,TaxAgency	varchar(6)
	,Apartment	varchar(6)
	,House#		varchar(6)
	,Address1	varchar(20)
	,Address2	varchar(28)
	,City		varchar(17)
	,State		varchar(2)
	,Zip		varchar(9)
	,TDate		datetime
	,Constraint PKPayAddress
		Primary Key (MasId,LastPP)
	)
Go
Insert into PayAddress_T values (619,'2012008','','','','','','','',null)
Insert into PayAddress_T values (620,'2012011','','','','','','','',null)
Insert into PayAddress_T values (684,'2012011','','','','','','','',null)
Insert into PayAddress_T values (348,'2012011','','','','','','','',null)

[S37N]

thank you so much for helping me out. as i said, i'm learning. ive been writing VBA for access & excel for a few years, but this is my first attempt at VB.

i will set everything up, and post my results.

thank you again for your patience.

[S37N]

i may have to wait until monday. they gave me admin rights to the computer, but I still get a permissions error in SQL, just like before. thanks again for your help.

[szlamany]

What does the SECURITY branch look like now?

Can you open a QUERY window - type this - and execute it??

CREATE DATABASE TESTXYZ

It should execute and show no error messages - then you refresh the DATABASE branch and you should see TESTXYZ as a database.

[S37N]

security branch looks the same. i tried to create db, and got the same permissions error as before.

[S37N]

it does not seem like i have proper access to SQL. i created a login, and was not able to add dbCreator role to it. when I try to delete the user I just made, it tells me i dont have access.

[szlamany]

My SQLExpress running on this laptop looks like this - and this is a non-domain laptop...

I'm sure I inherit from BUILTIN\Administrators but obviously I'm in as codevlapsz\steve

[S37N]

here's what mines looks like on mine, which is on a domain. the \youngje login is the one I just tried to make, and add dbCreator to. it errored out saying I didnt have permission to do that, but creatoed the user anyways. when i try to delete the user, it tells me I dont have permission.

[S37N]

i found that my SQL install was misconfigured. problem resolved. I used SQL import/export tool once install was corrected.

[smendoza]

i found that my SQL install was misconfigured. problem resolved. I used SQL import/export tool once install was corrected.

That is always fun, I hope you still have some hair left? What was installed incorrectly? The whole thing?

[S37N]

not exactly sure, but I think it was a problem with the instance owner. I didnt have access to create or change anything. I suggested a fresh install under my UN, based on a few posts I read, and it seems to have worked.