|
I initially created a cursor to create the a SQL statements as follows. Example:
SELECT tp.PropertyTestID, tp.PropertyTestGroupID, tp.PropertyTestName,
SUM(CASE WHEN S.SampleID = 122 THEN ResultDecimal ELSE NULL END) AS [N/A122 05/02/2004],
SUM(CASE WHEN S.SampleID = 109 THEN ResultDecimal ELSE NULL END) AS [N/A109 03/31/2004],
SUM(CASE WHEN S.SampleID = 108 THEN ResultDecimal ELSE NULL END) AS [N/A108 03/30/2004],
SUM(CASE WHEN S.SampleID = 107 THEN ResultDecimal ELSE NULL END) AS [N/A107 03/17/2004],
SUM(CASE WHEN S.SampleID = 106 THEN ResultDecimal ELSE NULL END) AS [N/A106 03/01/2004],
SUM(CASE WHEN S.SampleID = 105 THEN ResultDecimal ELSE NULL END) AS [N/A105 01/05/2004],
SUM(CASE WHEN S.SampleID = 104 THEN ResultDecimal ELSE NULL END) AS [N/A104 11/17/2003]
FROM tblSamplesPropertyTest tp,
tblSamplesSample s,
tblSamplesSampleResult sr,
tblSamplesCustomerProductReportPropertyTest prpt,
tblSamplesProductPropertyTest ppt
WHERE tp.PropertyTestID = ppt.PropertyTestID
AND ppt.PropertyTestID = prpt.PropertyTestID
AND sr.CustomerProductReportPropertyTestID = prpt.CustomerProductReportPropertyTestID
AND s.SampleID =* sr.SampleID
AND s.SampleID in (122,109,108,107,106,105,104)
AND tp.ResultTypeID = 1
AND (tp.Deleted = 0 or tp.Deleted Is Null)
GROUP BY tp.PropertyTestID, tp.PropertyTestGroupID, tp.PropertyTestNameAfter creating the statement I executed exec(@SQL) in the procedure.
However, I found this site that was quite helpful. Once I figured out what the crosstab stored procedure was doing in the article. Now any new crosstab/pivot tables are created using this method.
http://www.sqlteam.com/item.asp?itemID=2955
The easiest route, but not the best, may be to create an ODBC link to SQL server. Create an access database that links the necessary tables for the reports. Then simply create the reports in Access.
Good Luck,
Michael
I firmly believe that any man's finest hour, the greatest fulfillment of all that he holds dear, is that moment when he has worked his heart out in a good cause and lies exhausted on the field of battle - victorious.
Vince Lombardi (1913-1970)
|
|
|
|
|
Thanks I'll give it a try and see how it goes.
|
|
|
|
|
We have a UDF (defined below that we are rtying to call from a column in the databse table. We are using the formua field in SQL enterprise manager to enter the call to the UDF.
Both columns are defined as datetime as well as the column we are trying to get the UDF to work in.
I have tried to use the following:
database.dbo.RuntimeSort(database.dbo.table.columnname, database.dbo.table.columnname)
RuntimeSort(columnname,columnname)
database.dbo.RuntimeSort(columnname, columnname)
RuntimeSort(database.dbo.table.columnname, database.dbo.table.columnname)
UDF:
CREATE FUNCTION dbo.RuntimeSort (@DateProduction datetime, @TimeStart datetime)
RETURNS datetime
BEGIN
DECLARE @Date datetime
DECLARE @DateReturn datetime
SET @Date = dateadd(day, 1, @DateProduction)
SET @DateReturn = datepart(mm, @Date) + '/' + datepart(dd, @Date) + '/' + datepart(yyyy, @Date) + ' ' + datepart(hour, @TimeStart) + ':' + datepart(minute, @TimeStart) + ':' + datepart(second, @TimeStart)
RETURN @DateReturn
END
I am at a loss here  and any help would be appreciated.
Regards
Eric C. Tomlinson
No comment, Mr. Senator<pre>
|
|
|
|
|
If that function is a direct cut-and-paste from Enterprise manager, there's a bug in it:
"Server: Msg 245, Level 16, State 1, Procedure RuntimeSort, Line 10
Syntax error converting the varchar value '/' to a column of data type int."
**Edit**
I see the problem. You have DATEPART() + '/'
DATEPART returns an int, which you are NOT casting to a varchar. So, when you try to add '/' to an int, it tries to auto-cast the slash into an int, and the function bombs.
To answer your original question, however, the proper method of defining the formula is dbo.RuntimeSort(date1, date2). You MUST provide the object owner, as well as the object name, and ALL parameters. UDF's on SQL Server 2000 don't support default parameter values like stored procs do.
Grim (aka Toby) MCDBA, MCSD, MCP+SB
|
|
|
|
|
In this question I have Form1, myDataAccess class, Customer class.
In Form1 I have ListBox1 and a ButtonFill. Can you show me how do I get the details of customer(Firstname and Lastname) to fill in the ListBox1 by click on the ButtonFill. The 2 classes are coded below. The code in Customer class has some error. I am appreciate if you give me an example code. Thanks
'--------myDataAccess class---------------
Imports System.Data.SqlClient
Public Class myDataAccess
Public Function GetDataSet() As DataSet
Dim connection As SqlConnection
Dim command As New SqlCommand
If command.Connection Is Nothing Then
connection = New SqlConnection("workstation id=WHITE;packet size=4096;integrated security=SSPI;data source=WHITE;persist security info=False;initial catalog=ABC")
connection.Open()
command.Connection = connection
End If
Dim dataset As New DataSet
Dim adapter As New SqlDataAdapter(command)
adapter.Fill(dataset)
adapter.Dispose()
If Not connection Is Nothing Then
connection.Close()
End If
Return dataset
End Function
End Class
-----------------------------------
Imports System.Data.SqlClient
Public Class CustomerClass
Inherits myDataAccess
Public Function GetCustomerByname(ByVal Firstname As String, ByVal Lastname As String) As DataSet
Dim myQuery As String = "select * from CustomerTable"
GetDataSet()
Dim dataset As New DataSet
Dim adapter As New SqlDataAdapter
adapter.SelectCommand = New SqlCommand(myQuery)
adapter.Fill(dataset, "CustomerTable")
Return dataset
End Function
End Class
-----------------------------------
|
|
|
|
|
Hi,
i m getting problem in a query ,
==> Order_Recieved_Detail
ordID | ordQuantity | ordCost | productID | orID
=================================================
1_____|___10________|_2500____|____1______|___1
2_____|___10________|_3000____|____2______|___1
3_____|__ 10 _______|_3000____|____3______|___1
4_____|___10 _______|_2500____|____1______|___2
5_____|___10 _______|_3000____|____2______|___2
now i m getting problem in calculation of getting sum of ordQuantity of productID 1 of orID 1 and 2 to get 20
i want to get the following result
ordQuantity | productID
==========================
___20_______|____1______
___20_______|____2______
__ 10 ______|____3______
the query which i am using just sum all the itmes in the orderquantity column and not giving the sum of productID 1 and 2
waiting for any one to help me out....
"Winner's don't do different things , they do things differently "
|
|
|
|
|
Try this:
SELECT SUM(ordQuantity) AS ordQuantity, productID
FROM Order_Received_Detail --(is your table name Order_Recieved_Detail or Order_Received_Detail?)
GROUP BY productID
if you need them to be only for orID 1 and 2 then put the following line before the GROUP BY
WHERE orID IN (1,2)
Hope it helps!
Edbert P.
Sydney, Australia.
|
|
|
|
|
Hi Ebbert:
it works .... thanks..
its really nice to have people like you how always help out people like me which are new comers in this computer field ....
thanks again Edbert P.
|
|
|
|
|
right now i am getting data thru SQLDataReader,and binding it to a data grid.Here i am hve 2 problems
1> how do i change the name of the headers of columns
2> how do i add a check box column. And then get the id from the checked column(s)
|
|
|
|
|
1. Right click on the grid and choose Property Builder from the menu then choose columns.
2. Again from the Property Builder dialog choose columns. Then create a template column for the checkbox column. Close out the dialog and right click on the grid select edit template.
Also, stay away from the dataReader if possible. Try DataAdapter.Fill(DataSet) instead.
Michael
I firmly believe that any man's finest hour, the greatest fulfillment of all that he holds dear, is that moment when he has worked his heart out in a good cause and lies exhausted on the field of battle - victorious.
Vince Lombardi (1913-1970)
|
|
|
|
|
thank you for the reply but the problem is:
well right now i am retriving data from several tables of the data base and with several rows of results so have to use sqlDataReader
thi have made a template column but from where to set it as a check box and how to access the particular row to get the info of the whole row (like registration ID's of the checked columns)
Zainab
|
|
|
|
|
The DataAdapter.Fill(DataSet) will fill multiple resultset back to the client without holding a connection. When using a datareader, the connection cannot be pooled or used by any other user until the datareader.close() method is executed.
You can set the value in grid's ItemDataBound event or you the DataBinder.Eval in the html.
e.g.
value='<%# DataBinder.Eval(Container, "DataItem.CheckBoxColumn") %>'
Michael
I firmly believe that any man's finest hour, the greatest fulfillment of all that he holds dear, is that moment when he has worked his heart out in a good cause and lies exhausted on the field of battle - victorious.
Vince Lombardi (1913-1970)
|
|
|
|
|
Hello,
My application is required to monitor a database in MS SQL. Can I use C#/ADO.NET to develop the application that is to detect a new record entered or updated in MS SQL?
I appreciate your helps.
|
|
|
|
|
Vu Truong wrote:
My application is required to monitor a database in MS SQL. Can I use C#/ADO.NET to develop the application that is to detect a new record entered or updated in MS SQL?
Off the top of my head (I've never tried this, so YMMV), you could add a trigger to the database which on the relevant changes which calls into an extended stored procedure (or, if all you're doing is say, logging changes to a table back into another table for audit purposes, just use the trigger itself to update the log table)
An extended stored procedure is an exported function from a DLL (C-style), so you'll probably need to expose your C# code in a manner that SQL Server can understand, or write a C++ wrapper that can interop with your code.
Ian Darling
"If we've learned anything from history, it's that those who feed trolls are condemned to repetitive conversations. Or something like that." - Eric Lippert
|
|
|
|
|
Ian Darling wrote:
Off the top of my head (I've never tried this, so YMMV), you could add a trigger to the database which on the relevant changes which calls into an extended stored procedure
I recently did just that.
I wrote several XP's to send messages to a specified server and port.
Of course there is an issue of protocol for the message format.
In general to write an XP you need the following :
1. Make sure you use the right srv header :
i.e. place
C:\PROGRAM FILES\MICROSOFT SQL SERVER\80\TOOLS\DEVTOOLS\INCLUDE
before
C:\Program Files\Microsoft Visual Studio\VC98\INCLUDE
in the list of include directories.
#include <srv.h>
2. Make sure you link with the OpenDS library :
#pragma comment(lib, "opends60") // MSSQL srv_*() functions
3. Make sure all your XP's are compiled as C not C++.
i.e. wrap with
#ifdef __cplusplus
extern "C" {
#endif
...
#ifdef __cplusplus
}
#endif if functions are in a cpp file.
4. Provide a __GetXpVersion() function in your library :
__declspec(dllexport) ULONG __GetXpVersion( void )
{
return(ODS_VERSION);
}
I ended up writing two sets of functions :
CMKMSSQL_API SRVRETCODE xp_cmkInitSvcMsg ( SRV_PROC *SP );
CMKMSSQL_API SRVRETCODE xp_cmkTermSvcMsg ( SRV_PROC *SP );
CMKMSSQL_API SRVRETCODE xp_cmkSendSvcMsg ( SRV_PROC *SP );
CMKMSSQL_API SRVRETCODE xp_cmkBroadcastSvcMsg ( SRV_PROC *SP );
and
CMKMSSQL_API SRVRETCODE xp_cmkInitGisMsg ( SRV_PROC *SP );
CMKMSSQL_API SRVRETCODE xp_cmkTermGisMsg ( SRV_PROC *SP );
CMKMSSQL_API SRVRETCODE xp_cmkSendGisMsg ( SRV_PROC *SP );
CMKMSSQL_API SRVRETCODE xp_cmkBroadcastGisMsg ( SRV_PROC *SP );
The *SvcMsg ones just send raw data to the specified server:port.
The *GisMsg ones send protocol formated messages to one of my GIS servers.
xp_cmkInit*Msg is called to create a connection to a specified server:port
Usage : EXEC xp_cmkInitSvcMsg <@server> <@port> [@handle OUTPUT]
The output handle is a long and is used to by the Term/Send functions.
xp_cmkTerm*Msg is called to close a connection.
Usage : EXEC xp_cmkTermSvcMsg <@handle>
xp_cmkSend*Msg sends a blob of data using the connection associated with the specified handle.
Usage : EXEC xp_cmkSendSvcMsg <@handle> <@msg>
Because the message is sent using
sock->Send( (byte*)srv_paramdata(SP, 2), srv_paramlen(SP, 2) );
The message can be any data type.
xp_cmkBroadcast*Msg sends a message to all connections.
Usage : EXEC xp_cmkBroadcastSvcMsg <@msg>
This is the one i call in the triggers.
...cmk
Save the whales - collect the whole set
|
|
|
|
|
what is wrong with this code.
string commandString = "SELECT * FROM Company WHERE (Country = 'Brazil')";
System.Data.OleDb.OleDbDataAdapter DataAdpter9 = new System.Data.OleDb.OleDbDataAdapter(commandString, oleDbConnection1);
DataAdpter9.Fill(dataSet4);
dataGrid1.DataSource = dataSet4.Tables["Company"].DefaultView;
It gives me this error using try/catch:
"Object reference not set to an instance of an object"
DataSet and mispelled DataAdapter are all declared in the form_load.
|
|
|
|
|
You probably forgot to declare the dataset4 as new dataset before trying to fill it.
Edbert P.
Sydney, Australia.
|
|
|
|
|
Hi,
There could be several possible reasons for this kind of error.. It would have been easier to tell if oyu would have debugged the application and told exactly which line is throwing this exception. Anyway... the possibilities are...
a) You haven't instantiated a connection object.
b) You haven't instantiate a dataset object
c) Form Load event is not attached...(check in InitializeComponent() for the event)
Hope this info solves your prob.
regards,
Aryadip.
Cheers !! and have a Funky day !!
|
|
|
|
|
Dear all,
I get a stupid error while executing this update procedure and can't find the mistake.
The SQL statement works fine in the Enterprise Manager:
"UPDATE tb_cont_SmartSell_orders SET OR_state = N'printing' WHERE (OR_state = N'E-Mail')"
But when I'm using the procedure I get the error message:
..ADODB.Recordset (0x800A0E78)
Operation is not allowed when the object is closed....
If I remove "rs1.close" from the procedure it works but I don't understand why.
I call the procedure with "UpdateOrderState "E-Mail", "printing"
======
Sub UpdateOrderState(ByVal strFirstState, ByVal strNextState)
Dim strUpdate, rs1, rsconnect1
Set rsConnect1 = Server.CreateObject("ADODB.Connection")
Set rs1 = Server.CreateObject("ADODB.Recordset")
'rs1.CursorLocation = adUseServer
'rs1.CursorType = adOpenKeyset
'rs1.LockType = adLockOptimistic
strUpdate = "UPDATE tb_cont_SmartSell_orders SET OR_state = N'" & strNextState & "' WHERE (OR_state = N'" & strFirstState & "')"
rs1.open strUpdate, gDBCONSTRING
rs1.close
set rs1 = nothing
set rsconnect1 = nothing
end Sub
======
Many Thanks in advance!
Pete
|
|
|
|
|
The 'problem' is you do not open a recordset, but you are executing a command. Therefore you cannot close the recordset...
What you can (should) do is this:
<br />
Sub UpdateOrderState(ByVal strFirstState, ByVal strNextState)<br />
<div style="margin-left:3em"><br />
Dim strUpdate, cn<br />
Set cn = Server.CreateObject("ADODB.Connection")<br />
cn.Open gDBCONSTRING<br />
<br />
strUpdate = "UPDATE tb_cont_SmartSell_orders SET OR_state = N'" & strNextState & "' WHERE (OR_state = N'" & strFirstState & "')"<br />
<br />
cn.Execute strUpdate<br />
cn.Close <br />
set Cn = nothing<br />
</div><br />
end Sub<br />
Wout Louwers
|
|
|
|
|
Many thanks Wout!
It's working fine now.
br Pete
|
|
|
|
|
Hi,
Can i use activexscript in transform data task to determine whether the transformation of current table field failed or succeed? This is because i need to insert the failed field to another table. I don't want to validate the dtssource to do this because the transformation still can be failed.
This is the program logic :
Function Main
DTSDestination("Destination") = DTSSource("Source")
Main = DTSTransformStat_OK
(Validate current transformation here)
IF (TRANSFORMATION FAILED) THEN
(INSERT FAILED FIELD VALUE TO A TABLE)
END IF
END FUNCTION
Can i do this in the script? Or i need to use other task like data driven task, activex task, etc.
Thanks for the help.
|
|
|
|
|
i was some how able to get a resultset wanted by my boss. But can this query be optimized further.
<br />
DECLARE @name nvarchar(50)<br />
<br />
DECLARE cur_name CURSOR FOR<br />
SELECT url <br />
FROM urltable<br />
<br />
OPEN cur_url<br />
FETCH NEXT FROM cur_name INTO @name<br />
<br />
WHILE @@FETCH_STATUS = 0<br />
BEGIN <br />
select name from mytable where name like '%' + @name+ '%'<br />
<br />
FETCH NEXT FROM cur_name INTO @name<br />
END<br />
CLOSE cur_name <br />
DEALLOCATE cur_name
It's not a bug, it's an undocumented feature.
suhredayan@omniquad.com
messenger :suhredayan@hotmail.com
|
|
|
|
|
You could try
<br />
Select name<br />
From MyTable<br />
Inner Join urlTable on MyTable.name like ''% + urlTable.url + '%'<br />
but I'm not sure if it will work...
Wout Louwers
|
|
|
|
|
thx, but query is verry resource and time consuming. any suggestion to optimize the resource usage also..
It's not a bug, it's an undocumented feature.
suhredayan@omniquad.com
messenger :suhredayan@hotmail.com
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
