|
I am writing an application where I am using strongly typed DataSets to define the DataTables and relations used in various parts of the application. I have code that takes these DataSets and converts the rows in the DataTables to objects which I then use in my application. The reason for using strongly typed DataSets is so if I change the database schema I only need to validate the DataSets to identify any mismatches at compilation time.
I obviously need to fill the DataSets which is currently done by creating SqlAdapters which call stored procedures to populate the DataTables within the DataSet. This means I have to keep the stored procedures in sync with the DataSets when the schema is changed. I am trying to minimise any potential bugs and so I am looking at ways to ensure the database schema, DataSets and stored procedures are kept in sync.
My current method is to use my own validation tool which performs tests on the DataSets to ensure the stored procedures correctly fill the DataSets.
Given the information contained within the DataSet I was thinking that it should be possible to "auto-generate" the SQL from the DataTables and DataRelations definitions. For a simple situation like Customers and Orders this is not difficult but I was wondering if there are any libraries or tools out there which generate SQL code from a DataSet definition given the complexity that may be involved with many tables and their relationships.
|
|
|
|
|
|
which is the easiest and most efficient means to debug stored procedures?
pls help
|
|
|
|
|
If you are talking about MS SQL Server:
Open SQL Query Analyzer, right click on your stored procedure, click on Debug. It will prompt you to input values for for the parameters, then you click Execute. It will let you single step through the stored proc, and has a window for current variable values.
If debugging is not enabled for your instance of SQL Server, you may have to install the option from cd. Also, it needs the SQL Server Agent service to be running.
To debug triggers, just write a stored proc which will cause a trigger to execute, then you can step into the trigger when debugging the stored proc.
|
|
|
|
|
thanks buddy 
cheers
|
|
|
|
|
why do i get this error msg when i try to compile the stored procedure?
[Microsoft][ODBC SQL Server Driver]Invalid character value for cast specification
pls help
|
|
|
|
|
Hi,
I am need of a query to retrieve a column by trimming in a particular format.
For example I have a field called UserName. The data is as follows
UserName
--------
user1 (user1@noreply.com)
user2 (user2@noreply.com)
user3 (user3@noreply.com)
Here I want a query to retrieve username data as
user1
user2
user3
I have to trim the content from '(' to ')'
Please Let me know the query.
Thanks in Advance,
Jaya
|
|
|
|
|
select substring(UserName,charindex('(',UserName), charindex(')',UserName)-charindex('(',UserName)) from UserTable
is the sybase syntax. The SQL Server syntax should be equivalent. Please check.
|
|
|
|
|
Hi alam,
Thank you.
I tried your query. Its working fine.
The Query can be written as follows.
select LEFT(UserName,CHARINDEX('(',UserName)-1) FROM UserTable
Regards,
Jaya
|
|
|
|
|
Hi!!!!!!!
SELECT LEFT(USERNAME,PATINDEX('%(%',USERNAME)-1)
Enjoy!!!!
Regards
Shashank
|
|
|
|
|
How to instanciate SQLXMLBulkLoad object in C#?
I have SQL Server 2000 installed and added Microsoft.Data.SqlXml reference to the project, but I can't find any SQLXMLBulkLoad object in it.
What have to be installed to have SQLXMLBulkLoad object?
|
|
|
|
|
Just to follow-up ...
If you are working in .NET C# for example you will need to add the reference to the SQLXML BulkLoad 3.0 Type Library.
(from the below refeences article)
http://www.dbazine.com/sql/sql-articles/cook3
... COM and the .NET Framework are two fundamentally different approaches to application development. COM type libraries must be converted to .NET assembly metadata to achieve COM interop in .NET. By using the Visual Studio .NET IDE, this can be done automatically by following the simple steps that follow. It is assumed that MSXML is already installed.
- Begin by going to the Visual Studio .NET menu bar and selecting Project, then Add Reference.
- From the Add Reference dialog box, select Microsoft SQLXML BulkLoad 3.0 Type Library from the list, click the Select button, and click OK to complete your selection.
- The necessary metadata is automatically generated. The COM object appears as a reference in the solution explorer.
- Use the Object Browser to see what’s available in the component. Go to the menu bar and select View, Other Windows, Object Browser.
- The Object Browser shows you the interfaces and members available.
The rest of the code would be as such ...
SQLXMLBULKLOADLib.SQLXMLBulkLoad3 bulkLoadObj = new SQLXMLBULKLOADLib.SQLXMLBulkLoad3Class();<br />
<br />
<br />
bulkLoadObj.ConnectionString = "Provider=SQLOLEDB;Data Source=localhost;" +<br />
"Integrated Security=SSPI;Initial Catalog=northwind";<br />
<br />
bulkLoadObj.Execute("C:/Locations.xsd", "C:/Locations.xml");
http://www.dbazine.com/sql/sql-articles/cook3
Peace and Love,
Chris Macgowan
|
|
|
|
|
Guys,
I have 3 records defined in the following fields
Company Date Type Amount
COA Sept-205 Cash 500.00
COB Oct-2005 Cash 20.00
COC Oct-2005 Cash 145.00
COD Oct-2005 Cash 0.0
I want to get the average amount per month.
SELECT date, avg(amount)
FROM COTABLE
GROUP BY date.
MY question is IF THE AMOUNT = 0.00 DO NOT INCLUDE IT IN GETTING THE AVERAGE PER MONTH.
In the scenario above, for the month of OCT: the AVERAGE is 55 becuase it's DIVIDED BY 3.
BUT It should be
20.00 + 145.00 = 165.00
165.00 / 2 = 82.5 SINCE the third amount is 0.0 it should not be included in the calculation.
Any help will do. Thanks
|
|
|
|
|
Add a WHERE clause before the GROUP BY statement:
SELECT date, avg(amount)
FROM COTABLE
WHERE amount>0
GROUP BY date;
I hope it helps,
Marc Soleda.
... she said you are the perfect stranger she said baby let's keep it like this... Dire Straits
|
|
|
|
|
thanks a lot for your answers.
Rgds
theepa
|
|
|
|
|
how do i generate cumulative aggregate queries in transact-sql
|
|
|
|
|
Hi!!!!
U can Enjoy with this.....
http://msdn.microsoft.com/library/default.asp?url=/library/en-s/tsqlref/ts_fa-fz_9yuk.asp
Regrads
Shashank
under2811@yahoo.co.in
|
|
|
|
|
Help!
Can anybody show me the connection string required or point me to a link regarding this matter.
I need the public to connect to a Data Acces Page on a Web Site where the database is stored on a seperate folder.
I believe this is 3 tier connection and probibly RDS?
The ISP has provided a DSN name for DSN connection as well.
Many thanks
|
|
|
|
|
kindkirk wrote: Can anybody show me the connection string required or point me to a link regarding this matter
You mean your going to open your SQL server for anyone to connect to? That’s a huge security risk, even if its password protected. 
Here is a link to ADO connection strings.
ADO Connection Strings[^]
DEBUGGING : Removing the needles from the haystack.
|
|
|
|
|
Try this, it's so cool!
http://www.connectionstrings.com[^]
I always go there to look for that SQL Server connection string I can never learn! 
-- LuisR
Luis Alonso Ramos
Intelectix - Chihuahua, Mexico
Not much here: My CP Blog!
The amount of sleep the average person needs is five more minutes. -- Vikram A Punathambekar, Aug. 11, 2005
|
|
|
|
|
Luis Alonso Ramos wrote: Try this, it's so cool!
http://www.connectionstrings.com[^]
Luis, thanks for the link! Very cool...
Just like your self I can never remember the connection strings even though most of the code I write is database concentric.
DEBUGGING : Removing the needles from the haystack.
|
|
|
|
|
Hi,
I'm working on a VB6 solution that's using two access databases. One "local" database that is always accessible, located in the same folder as the application and another "remote" database that could reside either on the same computer or on a network drive. At the start-up of my application I have to open the local database and read in some settings. It all works great but here's the problem: If I unplug the internet cable from the computer I get the following error trying to open the local database -- Error 3028: "Can't start your application. The system database is missing or opened exclusively another user." What is really puzzling is that the local database has nothing to do with the remote one (which may be located on a network drive that may be disconnected by removing the network cable) and yet it doesn't let me open it.
In my code I'm using DAO methods to open the database in non-exclusive mode such as:
<code>
Dim db as Database
db = OpenDatabase("",False,False,";DATABASE='C:\Work\MyProject\local.mdb'; PWD='passcode'")
</code>
This happens running on a Windows NT 4.0 Workstation.. does naybody have any ideas what might be causing this and if you do.. how can I get around it?
Thanks for any info you might have/share
Mikk
-- modified at 17:22 Monday 17th October, 2005
|
|
|
|
|
mikanu wrote: If I unplug the internet cable from the computer I get the following error trying to open the local database -- Error 3028: "Can't start your application. The system database is missing or opened exclusively another user." What is really puzzling is that the local database has nothing to do with the remote one (which may be located on a network drive that may be disconnected by removing the network cable) and yet it doesn't let me open it.
In my code I'm using DAO methods to open the database in non-exclusive mode such as:
Dim db as Databasedb = OpenDatabase("",False,False,";DATABASE='C:\Work\MyProject\local.mdb'; PWD='passcode'")
This happens running on a Windows NT 4.0 Workstation.. does naybody have any ideas what might be causing this and if you do.. how can I get around it?
Where is the system database located? This maybe causing the problem, if it’s on a network share and there is a loss of network connectivity. A quick search of the MSDN indicates this error code is security – system db related.
You might try specifying a local system database before opening the database.
DAO Error 3028[^]
DAO error 3028 may occur when you try to create a workspace DAO object [^]
DEBUGGING : Removing the needles from the haystack.
|
|
|
|
|
Thanks for the reply! Actually, I did search MSDN for this error but that didn't clear it anymore.
Your statement is intresting though:
sfdougl wrote: Where is the system database located? This maybe causing the problem, if it’s on a network share and there is a loss of network connectivity
yet I don't understand why it would be a problem: The system database thata I'm using is the default that access is using, that is located in C:\Windows\System32\system.mdw... which is not on a network share... at least is shouldn't be... it's local, on my hard drive. It's really weired.
So, if the system.mdw is in my windows folder... any ideas why I may be getting that 3028 error? (The file is there, it's not read only.. it doesn't appear to be opened by anybody... and MS Access itself is able to open the database without yelding an error) Any ideas will be greatly appreciated!!!
|
|
|
|
|
mikanu wrote: Any ideas will be greatly appreciated!!!
I know ADO fairly well and have forgotten just about everything related to DAO so not sure I can be of much help, but here goes.
Are you using Access permissions? Or just a plain simple database password?
I think the problem my be related to how your opening a connection to the db. Check out this MSDN link for opening a connection to the db. As memory servers if you don’t open the db correctly you will get flaky behavior.
OpenDatabase Method[^]
DEBUGGING : Removing the needles from the haystack.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
