|
I am using a stored procedure and returning rows to display in my report. I am grouping by a SOURCE field in the stored procedure. When the rows for this source are displayed on separate lines, I suppress the printing of the source on the subsequent lines by using the Previous function. In other words, if I have 5 rows, I print the source on the 1st row (occurrence) but not on rows 2-5. That works great. What I want to do now is sum the data columns for that source by adding a total line. I'm using the Previous function to check when the source changes, but I'm printing the total line on the first occurrence of the new source instead of the last (after row 1 instead of after row 5). I basically need to do a look ahead, determine how many rows I have, and then when I get the last row, print the total line. Is there a way of doing this within Reporting Services?
|
|
|
|
|
I have a table in an access database with a DateTime column. I have a query that is supposed to look for all rows with a specific date in the date column. The problem is, for two Date/Time objects to be equal in Access the time part must also match. I only want it to match on the date part (I use the time for something else so I can leave that out all together). I have tried this:
<br />
SELECT Log.id, Log.LibraryID, Log.Action, Log.Date<br />
FROM Log<br />
WHERE (((Log.LibraryID)=[libid]) AND ((DatePart("mm-dd-yyyy",Log.Date))=DatePart("mm-dd-yyyy",[checkdate])));<br />
But I get an error about how its typed incorrectly or is too complex to be evaluated. How due I match on only the Date?
|
|
|
|
|
DatePart("mm",Log.Date)+"-"+DatePart("dd",Log.Date)+"-"+DatePart("yyyy",Log.Date)
or use format function
|
|
|
|
|
I tried that and still got the too complex error:
<br />
SELECT Log.id, Log.LibraryID, Log.Action, Log.Date<br />
FROM Log<br />
WHERE (((Log.LibraryID)=[libid]) AND (DatePart("mm",Log.Date)+"-"+DatePart("dd",Log.Date)+"-"+DatePart("yyyy",Log.Date))=( <br />
DatePart("mm",[checkdate])+"-"+DatePart("dd",[checkdate])+"-"+DatePart("yyyy",[checkdate])));<br />
|
|
|
|
|
SELECT str(datepart('d',now()))+'-'+str(datepart('m',now()))+'-'+ str(datepart('yyyy',now()))
This worked for me. You can try format function as well.
|
|
|
|
|
ello,
I have a database that will need to be modified, i.e. adding columns, stored procedures, etc.
I can't create a backup as the database is live and will be continue to be updated with customer data.
My colleges what me to create the updates in code which I have done. However, my real problem is how do I know if the database has been updated and is current version.
I will not be updating the data that has been entered, only doing things like:
Alter [tableName] add Salary money null
create procedure dbo.insertPayment .....
example
if(currentVersion) then
'No need to update
Else
'Updates are available
'Add column
'Drop column
'create procedure
'etc
Is there any method that I could use to detect if the database is current or not.
I hope I have been clear on this.
Using VS 2005 & SQL Server 2005
Many thanks in advance,
Steve
|
|
|
|
|
steve_rm wrote: Is there any method that I could use to detect if the database is current or not.
That really depends on your idea of "current".
Before each ALTER statement I'd have an
IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'MyTableName'
AND COLUMN_NAME = 'ColumnName')
BEGIN
-- Add the column we checked for
ALTER TABLE ....
END
(The above code is for SQL Server 2000, there will be a similar equivalent in SQL Server 2005, but I don't know what it is)
You may also like to take a copy of your live database and practice your scripts on the copy first.
|
|
|
|
|
Hi,
I've a problem when using Carlos Antollini ADO class in a "realtime" application. What I do
is, that I call a thread that does all the database operations once every 10 minutes
or so. But it seems like the database operations are not called asyncron, because
the other threads within the application get freezed!?
Would be great if you could give advice, otherwise I've to rewrite the whole DB part 
Thanks
Mario
m_hThread = (HANDLE) _beginthreadex(NULL,0,_ThreadWriteToDB,this, 0,&nDummy);
UINT WINAPI CDBManager::_ThreadWriteToDB(LPVOID pParam)
{
ASSERT(pParam);
CDBManager *pDBMan = reinterpret_cast<cdbmanager *=""> (pParam);
DosSetPriority(PRTYS_THREAD, PRTYC_IDLETIME, THREAD_PRIORITY_IDLE, 0);
pDBMan->m_Con.BeginTransaction();
...
...
...
pDBMan->m_Con.CommitTransaction();
// this thread has finished its work so we close the handle
CloseHandle(pDBMan->m_hThread);
}
|
|
|
|
|
hai,
in my application i want to create one trigger.
the situation is i 've two tables T1 and T2.
when i update T1 i want to insert the data before updated ie old values to table T2.
i used one trigger with 'instead of', but the pblm is T1 is not updated but values inserted to T2 perfromed fine.
any help is appreciated!!
All I ever wanted is what others have....
CrazySanker
|
|
|
|
|
crazysanker wrote: i used one trigger with 'instead of', but the pblm is T1 is not updated but values inserted to T2 perfromed fine.
Try a regular trigger, and not an instead of trigger.
--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
can u explain something in detail
All I ever wanted is what others have....
CrazySanker
|
|
|
|
|
If you do not explicitly insert the values into the table within an Instead Of trigger, they will not be inserted. You have it working where you put the old values into your T2 table...but the new values aren't being put into the T1 table. This is because you are using an Instead Of trigger, and you aren't inserting the inserted table values into T1. You could continue to use an Instead Of trigger (which doesn't make much sense), but you would need to do something like this:
INSERT INTO T1
SELECT Field1,Field2,Field3
FROM inserted
However, you could just use a regular trigger, and NOT an Instead Of trigger. Rather than this:
CREATE TRIGGER Trig_InsteadOfINS_T1 ON T1
INSTEAD OF INSERT
AS
--etc, etc.
Do this:
CREATE TRIGGER Trig_INS_T1 ON T1
AS
--etc, etc.
--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
I am following this article to import data from CSV file into my database table:
http://www.codeproject.com/aspnet/ImportExportCSV.asp[^]
The problem I am facing is that one of my table contains a money data-type column. Therefore, my application gives me Conversion error.
When I try to import data from csv file into this table, I get:
Disallowed implicit conversion from data type varchar to data type money, table 'MyTable', column 'Total'. Use the CONVERT function to run this query.
The above article that I am following, puts all the data values from the csv file into quotes, such as: Insert into MyTable...... Values ('value 1','23.34'...). Thus SQL cannot convert 23.34 into money.
Any way to solve this problem?
Thanks
|
|
|
|
|
If you are having a problem with a specific article, you should contact the article author. However, from the way you've described the problem the article isn't all that great because it looks like there is the opportunity for SQL Injection Attacks to take place via the CSV file.
|
|
|
|
|
Hi friends,
I am working in .net 2005 and sql server 2000 use as back end.
My problem is how to insert jpeg file into database and how to retrieve it into .net form.
|
|
|
|
|
Store it into an image column. Information sent back and forth will be using byte arrays. You can obtain a byte array representation of an image from the Bitmap class, you can also instantiate an instance of a bitmap class using a byte array.
|
|
|
|
|
Hi All
What is actual use of inserted and deleted temporary system table in triggers in sql server2000?
what is the importance of using it?
Is there any kind of special situation where we need to use inserted and deleted temporary system table in sql server?
Thanks
monika
|
|
|
|
|
|
What is actual use of inserted and deleted temporary system table in triggers in sql server2000?
In trigger fired, you have to know what changes were made as part of the data modification. You can find this information in the inserted and deleted tables. For the AFTER trigger, these tables are actually views of the rows in the transaction log that were modified by the statement.
what is the importance of using it?
In case of Database Rollbacking....!!
Cheer
Pavan Pareta
|
|
|
|
|
Suppose i have a data table with 1 lakh records.My table have 25 columns. i want to search data table based on 10 columns. I may or may not provide input for 10 columns but i have to built query. How can i built query.
|
|
|
|
|
hi
you can also do this type
SELECT Column_1, Column_2, Column_3, Column_4, Column_5, Column_6, Column_7, Column_8, Column_9, Column_10 FROM tblName
Try
Pavan Pareta
|
|
|
|
|
can somebody throw some light on what data ware housing is.
|
|
|
|
|
|
Hi,
I am having problems logging into my reportserver on our 64bit sql server 2005, which runs on Windows 2003 server. It works 100%on the test machine, which is a 32bit normal WinXP pc, with sql server 2005 enterprise.
I noticed that if I browse to the reports (localhost/reports) on the 64bit machine via IE, I cant open the page - get "page cannot be displayed" error. I also get an error in the event log that says "Login failed for user 'NT AUTHORITY\SYSTEM'. [CLIENT: <local machine="">]". My Reporting Services Configuration tool says everything is ok, and working.
I am very new to reporting services, and dont know where to start debugging, or look for help.
|
|
|
|
|
Hi,
How can i concatenate two rows together? I have a table with id and three columns. I want to display all the colunms with same id in a row.
Original Table:
id column1 column2 column3
1 a b c
1 d e f
2 g h i
2 k l m
Desired Result:
id column1 column2 column3 column4 column5 column6
1 a b c d e f
2 g h i k l m
So rows are shifted up where ID is same. Is it possible to get this result through query? I'll very much appreciate any help.
Thanks
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
