|
Using scripts from SQLServer is all very well, but we have to support ALL databases; SQLServer, Oracle, FoxPro, DBase, MySQL, MS Access, etc., (including any databases not yet written)
CREATE TABLE syntax is similar between different DB vendors, but not identicle. Therefore we need a vendor-agnostic method. ADOX supplied this, as the driver created the actual SQL statement to create the table from a structure you created in code. I was hoping ADO.NET would have something similar, but it doesn't look promising.
One option would be to get SQL Server to dump out table structures as XML, then use different XSLT files to transform the XML into database specific script files, but I was hoping to avoid any of that.
Ho-hum, better dig that 'Learn XSLT in one easy lesson' book out.
Dr Herbie.
Remember, half the people out there have below average IQs.
|
|
|
|
|
I'm looking for a tool that can take in a SQL statement and dump the data to an excel spreadsheet; preferably something that has some type of template system so the layout doesn’t have to be manually formatted each time a spreadsheet is created.
I realize it’s not that hard to this with .NET, but it just seems like this should be a pretty common problem people have. Lots of companies have data, and lots of companies use excel sheets. I seem to keep rewriting small tools every time someone needs to generate a new spreadsheet. I'm looking for a more robust solution.
Thanks for any help
Dave Ratti
|
|
|
|
|
Why not extract the data to XML and then use a style sheet to format it as HTML. If I recall you can take an HTML document and change its extension to cause Excel to be associated with it and Excel will happily load it into a worksheet.
This also gives you the flexibility to choose what output format you want. One stylesheet for XML, one for CSV, one for XLS.
.
|
|
|
|
|
Thanks for the response. Actually I think I've found the tool I was looking for:
http://www.oraxcel.com/projects/sqlxl/
I thought about writing my own tools that use XML/XLS but the problem is that although that is a very flexible solution, its not exactly the most user friendly option. The people here that will utlimately be using this tool wont be able to learn xml or xls. I could write a tool to easily create templates for what they want, but that takes time and the above tool is pretty much just what we are looking for.
We currently write scripts to export the data in CSV format and load that in excel and manually format the data. Going to xml->xls would be a much better approach but it still has 2 or 3 steps that just dont seem like they should be there, at least not to our people using it.
Thanks for the response
Dave Ratti
|
|
|
|
|
In SQL Server 2000 Enterprise Manager is there a way to add some type of organizer to stored procedures within a single database. Example I have 30 stored procedures that deal with reports, 30 that deal with updates and 30 that deal with getting data. I would like to divide these into their respective categories so I can see only report stored procedures when that’s what I want. Can this be done?
Thanks,
Jason W.
|
|
|
|
|
Nope... Enterprise Manager doesn't have any fancy organizing capabilities other than sorting the names.
Perhaps you could give your sp's a prefix that to do this?
|
|
|
|
|
I’m already using a prefix I was just hoping to organize a little better. Thanks for the help.
Jason W.
|
|
|
|
|
I am building 2 classes using C#. Their relationship is 1 ~ n. And each one has the corresponding table in database.
for example :
CompanyClass & EmployeeClass, table name in Database is Company & Employee
I think I should use collection to build CompanyClass, but I am not sure how to do it. There're many different types of collection in .Net.
Can anyone help me please?
Thanks in advance!
YYF
|
|
|
|
|
Shameless self-promotion...
Using CollectionBase and DictionaryBase
Will CompanyClass be a collection of Employees? Inheriting from CollectionBase is a little easier than implementing ICollection.
|
|
|
|
|
Hi
I am not entirely sure if this is a bug or whether it's a limitation with ADO.Net.
1) run a query that requires long time to run on a background thread. eg: select * from users
2) Try cancelling the query by doing oledbcommand.cancel() and then abort the thread.
I use SQL Server 2000.
Here is what happens:
1) Cancel returns right after u call it.(it's ok)
2) Thread.Abort() runs for a while, after it returns, the thread doesn't abort until the query the cancel is done.(so the abort doesn't really abort the thread? what is thread.Abort() doing )
3) The cancel does cancel the running of the query, but it takes about 10 sec to stop it.
Has anyone come accross this problem? Can someone answer these questions please.
Thanks in advance
YYF
|
|
|
|
|
Is there any specific software one has to use to be able to use SQL in Visual Basic .NET?
I have Visual Basic .NET and Access 2000. Do I need anything else?
//OT
|
|
|
|
|
Nope, you have all you need. See the documentation on OleDbConnection, OleDbCommand, OleDbDataAdapter, OleDbDataReader etc.
Note that the .NET Framework requires a minimum of MDAC 2.6 for the SQL Server data provider, but this isn't applicable in your case. It's probably not a bad idea to use that version, though.
|
|
|
|
|
Thanks!
What is MDAC?
//OT
|
|
|
|
|
MDAC is Microsoft Data Access Components.
Yo can find it at http://msdn.microsoft.com/library/default.asp?url=/nhp/Default.asp?contentid=28001860
For accessing MS Access databases, I have used the ODBC Data provider, which is faster.
You can find more information at:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconadonetproviders.asp
Teo
|
|
|
|
|
If I have the code in my stored proc:
print 'Hello'
Is the string hello retrieved? in my case, does my asp.net code acquire the value 'Hello' when it calls the procedure?
'Hello World'
|
|
|
|
|
My handy reference Inside SQL Server 2000 says that a PRINT statement is returned as a message with severity 0. It looks like you can trap these by handling the SqlConnection's InfoMessage event. Note: not tested.
If you need to return a string as a result set, use SELECT instead.
|
|
|
|
|
I have been working on a simple expression and can't get a good map of it.
I have a table, ASDATA1, with 6 fields that represent portions of an address that I need to all look like 1 field in the view. How can I make 6 fields appear to be one without changing the underlying stucture of the database, as this is updated quarterly by another process.
I have this as my starting point,
SELECT OWNER_NUMBER, OWNER_SUBNUMBER, OWNER_DIRECTION, OWNER_STREET, OWNER_STREET_TYPE, OWNER_DIRECTION_2, OWNER_APARTMENT
FROM ASDATA1
Thanks to all of the guru's out there!
You are only as good as your last project.
|
|
|
|
|
This is T-SQL, not sure if it works on other DBs...
select
(OWNER_NUMBER + ' ' + OWNER_SUBNUMBER + ' ' + OWNER_DIRECTION + ' ' +
OWNER_STREET + ' ' + OWNER_STREET_TYPE + ' ' + OWNER_DIRECTION_2 + ' ' +
OWNER_APARTMENT) as Owner_Address
from ASDATA1
Just concatenate all the fields together using the + operator and put spaces between them. You can use any character to seperate them, doesn't have to be spaces. Use the "as" keyword to rename the resulting field to whatever you want the field to be.
|
|
|
|
|
I just did try this under my SQL2000 and got all null responses. I should have mentioned my platorm, as I am sure Microsoft has there own little tricks inside the platform.
Anyone else have a suggestion?
You are only as good as your last project.
|
|
|
|
|
Sorry, should have mentioned that if you have any null fields, you will get null results. If you concatenate to a null field, it makes the result null (no matter what is in the other fields). Are any of the fields not nullable? You can either...
A. Add a where clause to get the records where nullable fields are not null. i.e.
where OWNER_DIRECTION_2 is not null
The problem with this is you won't get records that you should get.
B. Use a case statement inside the query. This seems like a lot of typing, but it should work.
select
( case when OWNER_DIRECTION_2 is null then '' else OWNER_DIRECTION_2 end )
Do this for each field that is nullable. What this does is if the field is null, it returns an empty string (the ''), otherwise it returns the field's value.
To put it together...
select
(
case when OWNER_NUMBER is null then '' else OWNER_NUMBER end + ' ' +
case when OWNER_SUBNUMBER is null then '' else OWNERSUBNUMER end + ' ' +...
I'll let you finish it.
Good luck
|
|
|
|
|
Or even better: (From MS SQL2K BOL)
COALESCE
Returns the first nonnull expression among its arguments. Use like this:
<br />
SELECT COALESCE(OWNER_NUMBER, '') + ' ' + COALESCE(OWNER_SUBNUMBER, '') + ...<br />
I'll also let you finish it.
|
|
|
|
|
Probably a problem with some of the values beeing NULL. Any string that is added to NULL (and vice versa) will give the answer NULL, if not otherwise set up in either database options or connection specific options.
You can decide the behaviour of SQL Server when adding strings with NULL. The settings for this come in two forms. One for setting it for one specific connection:
SET CONCAT_NULL_YIELDS_NULL OFF
And one for setting the default for the database as a whole, and all connections to it (unless a connection specific setting reverses this):
EXEC sp_dboption 'some_database', 'concat null yields null', 'FALSE'
The two examples above, both set up SQL Server to treat NULL as an empty string when adding to other strings.
See MS SQL2K BOL for more info on this ("CONCAT NULL YIELDS NULL")...
|
|
|
|
|
Hi,
I'm trying to create an extension dll that references a
database via ADO,
using VC 6(SP 5). The problem comes when the application
is exited and
it prints the following error:
'Unhandled exception in <application> (MSDART.DLL)
0xC0000005: Access Violation'
This happened two times. Once when i try to
release 'Connection' or 'Recordet',
second after my dll's db's class destructor, if i comment
Releasing.
I was trying with three Mdacs. 2.7,2.7SP1, and 2.8. with 2
OSs. 2k(SP4) and XP(SP1)
but none of them resolved my problem.
I'm Sure that 'CoUninitialize' wasn't called before releasing, becouse
if I call "CoInitialize" ,before releasing, the return value is "S_FALSE", which mean "The COM library is already initialized on this apartment".
I'll be glad to see any ideas and pointers.
Best Regards:
KalliMan.
a
|
|
|
|
|
I'm sure there's something in the MSKB about this or related issues; something to do with what you can and can't call from the DLL termination function. It sounds like a dependency problem, trying to unload yours after MSDART unloads or something similar...
Steve S
|
|
|
|
|
Hi Steve,
Thanks for your advice, but after 3 days search on net I could not find anything which may help me. I found that I'm not the first with this problem, too.
see 'http://forums.devshed.com/t36005/s.html'. Same problem, but no answer.
I cannot unload "MSDART.dll" before mine, because it's a SYSTEM dll.I have no access. (I get only ACCESS Violation )) ). I just know its description: "Microsoft Data Access - OLE DB Runtime Routines". bla bla...
Has anybody some other Ideas?
a
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
