|
I'm trying to create a query that counts records based on time periods. I'm querying two tables, one containing SubmittedDate, the other CompletedDate. I'm using DateDiff to calculate the time difference between the two. What I want to do is end up w/ a count for mulitple periods of time like....
< 2 Hours 3
2 - 4 Hours 6
4-8 Hours 10
1 Day 12
1 - 3 Days 8
5 Days 4
> 5 Days 2
Any ideas?
Jeff Martin
My Blog
|
|
|
|
|
|
Please tell me! how can i get all tables name in access file (.mdb) ?
Thanks!
xyz
|
|
|
|
|
I will assume that you do not have Access and/or can not use it.
When you use the Data Adapter Configuration Wizard, it will show the tables available and after you have choosen the one(s) you want, it will show you the fields in the table.
|
|
|
|
|
Try this, this is working on SQL Server.
Create Sample That Lists Tables in a Database
The following sample lists tables in the SQL Server Northwind database.
OleDbSchemaGuid.Tables returns those tables (including views) that are accessible to a given log on. If you specify an Object array of {Nothing, Nothing, Nothing, "TABLE"}, you filter to include only a TABLE_TYPE of TABLE. You then list the table name (TABLE_NAME) of each row in the returned schema table. 1. Start Visual Studio .NET.
2. Create a new Visual Basic Console Application project. Module1.vb is added to the project by default.
3. Open the Code window for Module1. Paste the following code into the top of the Code window, above the Module declaration: Imports System.Data
Imports System.Data.OleDb
4. In the Code window, paste the following code into the Sub Main procedure.
Note You must change User ID <username> and password = to the correct values before you run this code. Make sure that User ID has the appropriate permissions to perform this operation on the database.
Dim cn As New OleDbConnection()
Dim schemaTable As DataTable
Dim i As Integer
'Connect to the Northwind database in SQL Server.
'Be sure to use an account that has permission to list tables.
cn.ConnectionString = "Provider=SQLOLEDB;Data Source=server;User ID=<username>;Password=;Initial Catalog=Northwind"
cn.Open()
'Retrieve schema information about tables.
'Because tables include tables, views, and other objects,
'restrict to just TABLE in the Object array of restrictions.
schemaTable = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, _
New Object() {Nothing, Nothing, Nothing, "TABLE"})
'List the table name from each row in the schema table.
For i = 0 To schemaTable.Rows.Count - 1
Console.WriteLine(schemaTable.Rows(i)!TABLE_NAME.ToString)
Next i
'Explicitly close - don't wait on garbage collection.
cn.Close()
'Pause
Console.ReadLine()
|
|
|
|
|
Hi all.
I have a program running from Start > Programs > Startup.
When I start Winows, the program starts automatically, and I get SqlException (Sql Server Express 2005).
If I start the program manually, It starts normally.
I think that the program is loaded before the SQL service.
How can I check or change this?
Thanks,
Yaakov
|
|
|
|
|
You can wait until the service is started. Check This[^]. Remember to start the service automatically when you start your OS.
<italic>Work hard, Work effectively and a bit of luck is the key to success.
|
|
|
|
|
Wow, thanks a lot.
I didn't know that.
|
|
|
|
|
Hi, I'm choosing between transaction on stored proc level and transaction on application level. You see:
<br />
DECLARE @ValidationCode INTEGER<br />
BEGIN TRAN<br />
SET @ValidationCode = 0<br />
EXEC sp_CreateSupplier 1, 'M', 1, 'Golden Lucky and Associates', 'Golden Lucky and Associates', NULL, NULL, 'A', 3, 8, @ValidationCode OUTPUT<br />
PRINT 'Validation code: ' + CAST (@ValidationCode AS VARCHAR(10))<br />
<br />
... do other stuff...<br />
<br />
COMMIT TRAN<br />
<br />
CREATE PROCEDURE sp_CreateSupplier(<br />
@supplier_no INTEGER,<br />
@type VARCHAR(8),<br />
@group_no INTEGER = NULL,<br />
@short_name VARCHAR(50) = NULL,<br />
@full_name VARCHAR(100) = NULL,<br />
@address VARCHAR(100) = NULL,<br />
@country_code VARCHAR(10) = NULL,<br />
@status VARCHAR(8) = NULL,<br />
@createBy INTEGER,<br />
@ownerGroup INTEGER,<br />
@ValidationCode INTEGER OUTPUT<br />
)<br />
AS<br />
BEGIN<br />
<br />
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE<br />
BEGIN TRANSACTION<br />
<br />
-- STEP 1: Validation<br />
SET @ValidationCode = 0<br />
IF EXISTS(SELECT * FROM fe_supplier WHERE supplier_no=@supplier_no)<br />
BEGIN<br />
SET @ValidationCode = -10<br />
ROLLBACK TRANSACTION<br />
RETURN<br />
END<br />
...<br />
COMMIT TRAN<br />
END<br />
The above snippet will result in:
Server: Msg 266, Level 16, State 2, Procedure sp_CreateSupplier, Line 26<br />
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 0.
Now, what I'm really doing in my code is I called the stored proc from a method in application level - And this method is executed in context of a transaction (Which wraps around the stored proc with its own transaction):
Public Function Create(ByVal sup As vendormanagement.to.Supplier, ByVal tran As IDbTransaction) As vendormanagement.to.Supplier<br />
<br />
Dim sql As String = "sp_CreateSupplier"<br />
Dim cmd As IDbCommand<br />
Dim rdr As IDataReader<br />
Dim param As IDataParameter<br />
Dim factory As IDbProviderFactory<br />
Dim validationCode As Int32 = 0<br />
<br />
Try<br />
If _conn.State <> ConnectionState.Open Then<br />
_conn.Open()<br />
End If<br />
<br />
factory = apputil.GlobalVariables.ProviderFactory<br />
cmd = factory.CreateCommand(sql, Conn)<br />
cmd.CommandType = CommandType.StoredProcedure<br />
cmd.Transaction = tran<br />
<br />
...<br />
rdr = cmd.ExecuteReader()<br />
...<br />
Catch<br />
...<br />
Finally<br />
...<br />
End Try<br />
End Function
What I wish to do is to MAINTAIN transaction in stored proc level - which suggest that I must remove application level transaction. BUT I can't do that because:
<br />
vconn = vFactory.CreateConnection(...)<br />
tran = vconn.BeginTransaction(...serializable...)<br />
SupplierBean.Create(..., tran)<br />
AnotherBean.Update(..., tran)<br />
tran.Commit()<br />
So, my question is, how can I reconcile the two: Application Vs Stored proc transaction. The ONLY way to just recitify the problem seems to be removing stored proc level transaction.... Am I right?
Thanks in advance!
Norman Fung
|
|
|
|
|
declare @intnewmoviecode int
declare @vcmovieplot varchar(400)
declare @intreturn int
set @vcmovieplot=
'although the fellowship is shattered,war continues'+
'to fill the lands of middle earth.frodo heads toward'+
'mordor iwth sam,while in the west,' +
'saruman continues his plot against rohan.'
exec @intreturn=
spinsertdata'the two towers',2002,
@vcmovieplot,200,@intnewmoviecode output
select * from movies where moviecode=@intnewmoviecode
select @intreturn
declare @intreturn int
declare @intnewmoviecode int
exec @intreturn=
spinsertdata 'An old movie',1940,
'this movie won''t be added',200,
@intnewmoviecode output
select @intreturn
Error converting data type varchar to int.
aaffi
|
|
|
|
|
You are probably trying to insert varchar (string) data where int is required.
Check your spinsertdata and the input variables declared.
BTW, do remember not to name your stored procedure as sp_*** as this will incur a performance penalty.
Hope it helps,
Edbert
|
|
|
|
|
 declare @intnewmoviecode int
declare @vcmovieplot varchar(400)
declare @intreturn int
set @vcmovieplot='although the fellowship is shattered,war continues'+
'to fill the lands of middle earth.frodo heads toward'+
'mordor iwth sam,while in the west,' +
'saruman continues his plot against rohan.'
exec @intreturn=
spinsertdata'the two towers',2002,
@vcmovieplot,200,@intnewmoviecode output
select * from movies where moviecode=@intnewmoviecode
select @intreturn
aaffi
|
|
|
|
|
|
Like the previous post suggests, it would be very helpful if you would say what kind of trouble you're having with the code provided.
The most exciting phrase to hear in science, the one that heralds the most discoveries, is not 'Eureka!' ('I found it!') but 'That's funny...’
|
|
|
|
|
Hello,
I make a Database in SQL SERVER which has 9 tables. I want to copy that database to another computer, how can i do this.
kindly tell me the procedures, so that i can transfer database to another computer, along with its inserted data
EsHbAn BaHaDuR
EsHbAn BaHaDuR
|
|
|
|
|
|
Hi I'm using some DevExpress components in C#. Using sqlConnection, sqlDataAdapter, dataSet, gridControl (a Datagrid) and controlNavigator (to choose insert record, edit record etc.).
When I make my program I can see all the data in the table. Using the controlNavigator I can add records, delete them etc.
I use Microsoft SQL server 2000.
I understand that my program loads a local copy of the database and at the end I have to let the program know that the real table now needs to be updated.
I made a button under which I can test some commands.
I tried sqlDataAdapter1.Update(dataset11) and sqlDataAdapter1.Update(DataSet11,"films") (where films is the name of the table) also I tried dataSet11.acceptchanges().
No matter what I try each time when I quit the program and restart the program my table is empty.
What do I need to do to effectivly change/update the real table??
I would like a solution where I can input/change data in the datagrid using the controlNavigator and then by adding somewhere some extra code make the changes permanent.
I hope someone can help
Kind regards,
Clem
|
|
|
|
|
I am new to ADO, and Can I have a sample link to understand the need and purpose of ADO events with an example? Thanks.
|
|
|
|
|
http://www.support.microsoft.com/ph/6349
|
|
|
|
|
I am also want to know thx!
|
|
|
|
|
How can I Insepct / analyze the contents of a given *.ldf transaction log file?
The demand is for an analysis / inspection which is independent from any
database connection. Therefore - DBCC LOG and alike are not applicable.
In short - what is the internal structure of the transaction log?
How can one access thier contents?
Thanx
Hillel
|
|
|
|
|
The internal structure of the transaction log is officially undocumented. You won't be able to open it while the database is running. SQL Server uses the transaction log to record operations that will be performed before making the appropriate changes to the data pages; this allows transactions to be either recovered in full or rolled back in full if recovery is required.
You can find some information in Inside SQL Server 2000.
If you want to audit access to your database, I suggest reading SQL Server 2000 Auditing[^].
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
using(OleDbConnection _Connection = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=dBASE IV;User ID=;Password=;Data Source=c:\\CT"))
{
_Connection.Open();
_Connection.Close();
}
The 3 lines code above is causing me to lose Handles (on the Task Manager). So that my resource (the Handles) is not being released.
Code executes properly if I have no BDE (Borland Database Engine) installed on my machine. But I cannot live without BDE since it is needed by other applications.
|
|
|
|
|
Sounds like you need to talk to Borland...
Anger is the most impotent of passions. It effects nothing it goes about, and hurts the one who is possessed by it more than the one against whom it is directed.
Carl Sandburg
|
|
|
|
|
why is it important to use views in sql using transact tsql language
aaffi
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
