Database

Re: List user tables in a database??

29-Aug-02 8:41

Try selecting it from the INFORMATION_SCHEMA views directly.

SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME <> 'dtproperties' AND
TABLE_TYPE = 'BASE TABLE'
ORDER BY 1

Rein Hillmann13-Sep-02 6:19

Rein Hillmann

13-Sep-02 6:19

Try this (I'm not at a SQL box at the moment so it might not work 100%)

<br />
SELECT *<br />
FROM INFORMATION_SCHEMA.TABLES<br />
WHERE <br />
OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0<br />
AND TABLE_TYPE = 'BASE TABLE'<br />

Import/Export problem

Shaun Wilde27-Aug-02 6:31

27-Aug-02 6:31

Hi all I am hoping someone here can help me.

I am sufferering from the problem detailed in http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q325681[^]. However when I try to resolve it by using the first resolution I keep getting a failure with the message

'Failed to copy objects from Microsoft SQL Server to Microsoft SQL Server'

and when I click on the error

'[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name dbo.Locations'

now Locations is a view - how can it be invalid? It works when I run it.

Strange thing is even if I delete the view - it still complains about the same thing - now that is confusing. Please help.

Stupidity dies.
The end of future offspring.
Evolution wins.
- A Darwin Awards Haiku

notadood27-Aug-02 8:24

27-Aug-02 8:24

Can you explain what you are trying to do? Maybe the view is based on tables that do not exist in the location you are trying to create the view in.

Shaun Wilde27-Aug-02 8:52

27-Aug-02 8:52

I am trying to export a database from X -> Y where Y has a collation set to 'server default' and had only just been created. I have tried it where the collation was the same - alas same error.

I need to move the collation to be ther server default so that I can use it properly within Visual Studio.Net .

Maybe I'll have to try move my database from X -> Y in another fashion and then copy the data - via scripts perhaps - how would I do that?

Or maybe try moving the database a piece at a time eg tables then views then SPs - what do you think?

Stupidity dies.
The end of future offspring.
Evolution wins.
- A Darwin Awards Haiku

notadood27-Aug-02 10:22

27-Aug-02 10:22

Yes, I would just do it a bit at a time. Since you are having problems with the views do the tables and data first. Once they are there it is easy enough just to go back and script the views and procedures.

Also see:
http://www.microsoft.com/sql/techinfo/tips/administration/changingcodepage.asp

Shaun Wilde27-Aug-02 21:08

27-Aug-02 21:08

Thanks - I'll give it a go.

Stupidity dies.
The end of future offspring.
Evolution wins.
- A Darwin Awards Haiku

Shaun Wilde28-Aug-02 8:47

28-Aug-02 8:47

eventually finshed it - 6 hours later - thank god the database wasn't any bigger.

I was the only developer (and the new boy) who really wanted to use VS.NET to see what was going on rather than keep switching between Enterprise Manager, Query Analyzer and Visual Studio. So it was down to me to do the update.

I followed the steps in the KB article - but it never worked cleanly so....

steps to export + problems I found at each stage
i) tables + defaults + rules + datatypes

P1) When exporting tables any Foreign Keys that constrain on the same table (I hope you understand what I mean) failed - so I had to remove them for the export and then re-establish them after

ii) views

P2) Some of the views didn't work on the original database so I had to fix them first - I really hate some people.

P3) Some of the views failed due to the collation still being different and on the old collation. I had to walk though every table and change any collation to 'server default'. Eventually I got the last few views to go through.

iii) stored procedure + functions

iv) copied the diagrams over by messing with the dtproperties table

Stupidity dies.
The end of future offspring.
Evolution wins.
- A Darwin Awards Haiku

SQL Debugging

Shaun Wilde27-Aug-02 4:42

27-Aug-02 4:42

How?

This is my first real venture into SQL (other than SELECT * from XYZ) and I am having trouble with a stored procedure - all the data in looks okay but... it doesn't return the right answer. Is there a way to debug a SP so that when called I can see what is going on.

Stupidity dies.
The end of future offspring.
Evolution wins.
- A Darwin Awards Haiku

notadood27-Aug-02 4:44

27-Aug-02 4:44

The method I use to debug a procedure is to put print statements throughout the procedure so you can see what the values you are getting really are. It's a clunky method but usually works well. Most procedures are not that complicated. You have to do this in Query Analyzer.

If you have specific statements you are having problems with paste them here.

AndyG28-Aug-02 6:45

AndyG

28-Aug-02 6:45

Starting the Debugger[^]

Andy Gaskell, MCSD MCDBA

Shaun Wilde28-Aug-02 8:28

28-Aug-02 8:28

thanks - that'll help debug the obvious

I was hoping I could wait until it was called and see what values were being passed in and the step through.

Could I use the profiler for this at all?

Stupidity dies.
The end of future offspring.
Evolution wins.
- A Darwin Awards Haiku

notadood28-Aug-02 8:57

Retrieving QTY of records on a SQLClient

28-Aug-02 8:57

Profiler wouldn't really be much help for debugging. It is mainly good for finding performance bottlenecks & problems.

I have had good success on debugging in Query Analyzer using the method described above. You can run the procedure straight out of QA and put exit points at various points if necessary - using the RETURN statement. Once you get used to it you can debug relatively quickly.

ODBC.NET and Excel

Knappen27-Aug-02 1:30

Knappen

27-Aug-02 1:30

Hello
I want to open a Excel-file in ADO.NET as a DataSet.

I downloaded the ODBC.NET. and made a DSN-file pointing to the excel-file.

Then I made a connection to the DSN-file in the Server-explorer.

So far so good.

But when I try to use the generated connectionString opening a OleDbConnection. I get the following error:

The .Net Data Ole DB Provider(System.Data.OleDb) does not support the MSDASQL Provider, Microsoft OLE DB Provider for ODBC Drivers.

-----
My connectionString looks like this:

Provider=MSDASQL.1;Persist Security Info=False;Extended Properties="DBQ=G:\costumers\foretag.xls;DefaultDir=G:\costumers;Driver={Driver do Microsoft Excel(*.xls)};DriverId=790;FIL=excel 8.0;FILEDSN=G:\costumers\foretag.dsn;MaxBufferSize=2048;MaxScanRows=8;PageTimeout=5;ReadOnly=0;SafeTransactions=0;Threads=3;UID=admin;UserCommitSync=Yes;"

How do I do this?

Re: ODBC.NET and Excel

Paul Riley27-Aug-02 4:55

Paul Riley

27-Aug-02 4:55

The ODBC.NET installation includes a new namespace: Microsoft.Data.Odbc as opposed to System.Data.OleDb. You need to be opening an OdbcConnection instead of an OleDbConnection.

If you want to add this to your toolbox, right-click the Data toolbox, select "Customize Toolbox", go to the .NET Framework Components tab and check the boxes next to the Odbc... objects.

Paul

toki24-Aug-02 11:30

toki

24-Aug-02 11:30

Any way to get the quantity of records on a SqlDataReader object ?

<br />
SqlDataReader reader = sqlcommand.ExecuteReader();<br />
String[,] array = new String[  qty_of_rows_in_Select_Statement ,4];

Re: Retrieving QTY of records on a SQLClient

James T. Johnson24-Aug-02 13:50

James T. Johnson

24-Aug-02 13:50

Not until you've gone through all the rows returned by the reader.

This is because you are working with data as it is coming from SQL server and it doesn't know how many results are returned until it has sent them all to you.

James
"And we are all men; apart from the females." - Colin Davies

Re: Retrieving QTY of records on a SQLClient

Paul Ingles25-Aug-02 4:24

Paul Ingles

25-Aug-02 4:24

You can always execute a SELECT COUNT statement before the main statement, that will return the number of rows.

i.e. if your usual where clause is something like

SELECT * FROM OrderDetails WHERE CustomerID=59

You can get the number of rows by executing

SELECT COUNT(OrderID) FROM OrderDetails WHERE CustomerID=59

If you execute that using ExecuteScalar, you'll get the number of rows that will be returned by that query.

Incidentally, if there's a chance that this number could change in-between executing the two queries, or you just want to be extra careful, you'll want to execute both queries as part of the same transaction, ensuring that the number of records doesn't change as a result of someone else inserting/deleting records from the table.

Alternatively, you could consider taking the data to a DataSet first, since that's held in-memory you only execute the query once, and I'm pretty sure there must be a way to obtain the number of rows. This will have the usual advantages/disadvantages of using a DataSet as opposed to a DataReader.

--
Paul
"I need the secure packaging of Jockeys. My boys need a house!"
- Kramer, in "The Chinese Woman" episode of Seinfeld

MS Messenger: paul@oobaloo.co.uk
Sonork: 100.22446

Re: Retrieving QTY of records on a SQLClient

toki28-Aug-02 16:06

toki

28-Aug-02 16:06

Thanks, i solve the issue doing the following:

System.Data.SqlClient.SqlDataAdapter myDA = new System.Data.SqlClient.SqlDataAdapter(sqlcommand);<br />
System.Data.DataSet myDS = new System.Data.DataSet();<br />
String[,] array = new String[myDA.Fill(myDS),4]<br />
reader = sqlcommand.ExecuteReader();

but, i think that "select count" is a better idea because doesn't create the adapter and dataset and consume less processor.

Whats wrong with this picture?

Robby22-Aug-02 3:07

Robby

22-Aug-02 3:07

I am attempting to query every table in my database and put the results in a record set, format the results to a cell string in html and print it out. The problem is that it is displaying only the results from the first table it matches to. Am I mistaken in thinking that it combines all results into one RS? If not, how do I accomplish what I want to do? The premise is this: I have several user request forms that output to their respective tables in my SQL Sevrver 2k DB. All the forms have the columns listed in the SELECT stametment below. I want to query all these tables for anything matching STATUS='OPEN' and display the results in a user queue. I have eliminated the HTML from the user queue page just to show what function I am trying to accomplish. Any help is GREATLY appreciated! (I aplogize for the sloppyness of the CELLSTRING & "<>" has been replaced with "[]")

============================CODE===========================

Dim objRS
Dim strSQL

Set objRS = Server.CreateObject("ADODB.Recordset")

strSQL = "sp_MSforeachtable 'select submitDate, requestorLastName, requestorFirstName, urlToForm, nof, status from ?'"
objRS.Open strSQL, MyConn

if objRS.eof then
response.redirect "noQueue.asp"

else
do until objRS.eof

cellString = "[tr style='padding: 3'][td][font face='Arial' size='2' color='#CCCCCC'][center]" & objRS["submitDate"] & "[/center][td][font face='Arial' size='2' color='#FFFFFF'][center]" & objRS("requestorFirstName") & " " & objRS("requestorLastName") & "[/center][td][font face='Arial' size='2' color='#FFFFFF'][center]" & "[a href='" & objRS("urlToForm") & "'][font color='#66CCFF']" & objRS("nof") & "[/a]" & "[/center][/td][td][font face='Arial' size='2' color='#FFFFFF'][center]" & objRS("status") & "[/tr]"

response.write(cellString)
objRS.movenext
loop

end if

Re: Whats wrong with this picture?

notadood22-Aug-02 3:12