SQL Server 2012 - Help

[Bar0n]

I have a weird issue when trying to retrieve results using SQL Server 2012 management studio.

I have a table called "Customers" in a database. The table only has about 5 records in it.

===

Part 1:

At first I had both MSSQL 2008 and MSSQL 2012 installed. I ran into a few issues with mixed mode authentication as it would only let me log in with Windows authentication.

If I right-clicked on the Customers table in the treeview and selected "Select top 100 rows", I could see the 5 records in the table. When I run the query: "select * from Customers", I got only the column headers.

===

Part2:

I uninstalled MSSQL 2008 and did a clean install of MSSQL 2012 with management tools. I could now login with mixed mode authentication.

However, regardless of whether I write my own select query or use the built in "select top 100 rows" function, all I get are column headers.

===

Any ideas on why I'm not seeing the actual data?

 

[guest2013-1]

If you only see row headers and you don't have any weird "where" statements in your query, then there's NO data.

If you right click and "view top 100" and you see the 5 records, there's an issue with your WHERE statement.

When you do a "view top 100" thing, it usually does the SQL for you, copy that sql into a new query and remove the TOP 100 and run it again, if you see nothing still, make sure you're on the correct database

SQL 2008+ will do this:

select top 1000 * from [yourdatabase].dbo.[yourtable]

so if you go select top 1000* from [yourtable] in the same query window, it will usually error out (unless there is a Customers table created in the master database it usually defaults to)

check top left and see which DB you're working with.

Screenshots + your actual queries if none of this works so we can see more

 

[|3ash3r]

If you only see row headers and you don't have any weird "where" statements in your query, then there's NO data.

If you right click and "view top 100" and you see the 5 records, there's an issue with your WHERE statement.

When you do a "view top 100" thing, it usually does the SQL for you, copy that sql into a new query and remove the TOP 100 and run it again, if you see nothing still, make sure you're on the correct database

SQL 2008+ will do this:

select top 1000 * from [yourdatabase].dbo.[yourtable]

so if you go select top 1000* from [yourtable] in the same query window, it will usually error out (unless there is a Customers table created in the master database it usually defaults to)

check top left and see which DB you're working with.

Screenshots + your actual queries if none of this works so we can see more

OP is not using WHERE clause and you have a syntax error in your SQL (you can't use top n and * together in a select - top n means first n results, * means everything).

 

[|3ash3r]

Part 1:

At first I had both MSSQL 2008 and MSSQL 2012 installed. I ran into a few issues with mixed mode authentication as it would only let me log in with Windows authentication.

If I right-clicked on the Customers table in the treeview and selected "Select top 100 rows", I could see the 5 records in the table. When I run the query: "select * from Customers", I got only the column headers.

Check your database connection making sure you're connected to the correct database and not the master database (or some other database). Otherwise, just use "select * from [databaseName].[Customers]" which should work no matter which database you're connected to.

Part2:

I uninstalled MSSQL 2008 and did a clean install of MSSQL 2012 with management tools. I could now login with mixed mode authentication.

However, regardless of whether I write my own select query or use the built in "select top 100 rows" function, all I get are column headers.

This definitely means you have no data in the table (using the top 100 rows function will return at most 100 rows only if you have data in there).

Try using "insert into Customers values (value1, value2, etc.)" 5 times and then select top 100 rows.

 

[Cool E]

Do this:

Use [databaseName]

select * from [tablename]


And let us know if you get results.

 

[Bar0n]

If you only see row headers and you don't have any weird "where" statements in your query, then there's NO data.

If you right click and "view top 100" and you see the 5 records, there's an issue with your WHERE statement.

There IS data (definitely for the first part of my story) and there is no where clause. I've worked with SQL server before, but this is the first time I've come across this particular issue.

Otherwise, just use "select * from [databaseName].[Customers]" which should work no matter which database you're connected to.

This definitely means you have no data in the table (using the top 100 rows function will return at most 100 rows only if you have data in there).

I did try the [DB name].[TableName] method, also didn't work.

I will run the script to insert the data again (after work) and will report back tonight.

Do this:

Use [databaseName]

select * from [tablename]


And let us know if you get results.

Didn't think of using that method but [DB name].[TableName] should have sorted out any ambiguity. Regardless, MSSQL surely knows which DB I'm referring to if it retrieves the correct column headers.

 

[guest2013-1]

OP is not using WHERE clause and you have a syntax error in your SQL (you can't use top n and * together in a select - top n means first n results, * means everything).

watter BOOM rook jy boeta?

select top 100 * from table; <-- gives you the first 100 rows of ALL columns in the table specified.

 

[guest2013-1]

There IS data (definitely for the first part of my story) and there is no where clause. I've worked with SQL server before, but this is the first time I've come across this particular issue.



I did try the [DB name].[TableName] method, also didn't work.

I will run the script to insert the data again (after work) and will report back tonight.



Didn't think of using that method but [DB name].[TableName] should have sorted out any ambiguity. Regardless, MSSQL surely knows which DB I'm referring to if it retrieves the correct column headers.

is dbo the owner of the table?

If you open up the list of tables on the left hand side, do you see 2 customer tables?

it's ****ing IMPOSSIBLE what you describe unless there is 2 tables and you're looking at the wrong one

 

[Bar0n]

it's ****ing IMPOSSIBLE what you describe unless there is 2 tables and you're looking at the wrong one

There is only one DB, with one "customer" table in it.

The fact that neither method returns data could be my issue (I'll check for errors), but the initial result where one method returned data and the other didn't was under ideal circumstances.

 

[xrapidx]

Just check don't have the same table name in two different schema's...

 

[|3ash3r]

watter BOOM rook jy boeta?

select top 100 * from table; <-- gives you the first 100 rows of ALL columns in the table specified.

LOL, was smoking lekker there

 

[Bar0n]

Solved it by recreating the DB and rerunning the value insertion script. All seems to be fine now. Still don't know wtf happened with the other issue.

 

[DominionZA]

This is a seriously odd issue.

Have you tried cleaning anything MS SQL off your system, reinstall SQL2012, and try again?

I run 2008 and 2012 side by side with no issue. I don't use default instance names though. The instance names are SQL2008 and SQL2012 respectively.
Staight after installation I assign each instance its own port (1343 for 2008 and 1344 for 2012) and disable dynamic ports.
Maybe try this?

Your issue is a really odd one, so would suggest trying anything.
Please update us when you figure it out.

 

[Bar0n]

This is a seriously odd issue.

Have you tried cleaning anything MS SQL off your system, reinstall SQL2012, and try again?

I run 2008 and 2012 side by side with no issue. I don't use default instance names though. The instance names are SQL2008 and SQL2012 respectively.
Staight after installation I assign each instance its own port (1343 for 2008 and 1344 for 2012) and disable dynamic ports.
Maybe try this?

Your issue is a really odd one, so would suggest trying anything.
Please update us when you figure it out.

Well currently the issue is solved. I uninstalled all instances of MSSQL, did a clean install of 2012, and in the end had to run the DB creation and data insertion script twice (had to redo after scrapping the first DB), and now it works. Still not sure about that first issue though. I don't see how one method works and the other one doesn't.

 

[guest2013-1]

I know what happened dude, you just can't confirm. 2 users created the table. One had data, the other did not. Depending on which user was executing the select, you'd either get data, or not.

Happened to me before until I logged in with admin credentials and saw 2 tables of the same name staring at me.

 

[xrapidx]

That's why I said he should check the database schema's....

 

[Bar0n]

I know what happened dude, you just can't confirm. 2 users created the table. One had data, the other did not. Depending on which user was executing the select, you'd either get data, or not.

Happened to me before until I logged in with admin credentials and saw 2 tables of the same name staring at me.

Plausible, but I was the only user on the system, logged in with Windows authentication.

SQL Server had no DBs on it until I ran the script with the same account.

 

[xrapidx]

You'll probably find one ended up as dbo.tablename, and the other as yourname.tablename.

When you right click and select top 1000 it would have: select top 1000 * from dbo.tablename...

Then when you typed it out as select top 1000 * from tablename, it selected from yourname.tablename.

 

[nfbs]

He probably queried a system database

 

[guest2013-1]

Plausible, but I was the only user on the system, logged in with Windows authentication.

SQL Server had no DBs on it until I ran the script with the same account.

You'll probably find one ended up as dbo.tablename, and the other as yourname.tablename.

When you right click and select top 1000 it would have: select top 1000 * from dbo.tablename...

Then when you typed it out as select top 1000 * from tablename, it selected from yourname.tablename.

Yea, well. I was the only user on the database too since it was on my local machine and I was going to be doing some dev work on a restored database.

Turns out the database has 2 tables for each table, one under dbo. and the other on the sql login name. NO idea how that happened (or why anyone would want something like that) but still, out of experience, this is what happened and took 2 days to debug until I finally realized WTF was going on