Ok I have my login controls working perfectly, my roles function perfect now come time to upload to production server I find I need to convert this to either .sql or .csv file to allow for importation.
Is there a S/W Package available for doing this?
I need to get the clientid, last payment date, and last payment amount. I have tried using MAX but this does not work. Can anyone see what I'm doing wrong? I get all payments and all dates. I'm summing the payment amount incase they made two payments on the same day, not likely but...
SELECT dbo.tblLoan.Client_ID, MAX(dbo.tblPayments.PaymentDate) AS [Last Pay Date], SUM(dbo.tblPayments.AmountPaid) AS [Last Pay Amt]FROM dbo.tblLoan INNER JOIN dbo.tblPayments ON dbo.tblLoan.Loan_ID = dbo.tblPayments.Loan_IDGROUP BY dbo.tblLoan.Client_IDHAVING (dbo.tblLoan.Client_ID = @Client_ID)
All I am trying to do is use a SqlDataSource, Read that Data from the Session Variable, Do a RowFilter off the Data and then return the Data (Currently a DataSet but doesnt matter). But Since I dont use SqlDataSource that much not sure all the code I have is neccasry:
protected DataSet GetSearchAppData(string strWhere)
{
DataSet dsSearchAppTable = new DataSet("SearchAppTable");
SqlDataSource ss = new SqlDataSource();
DataView dv = new DataView();
DataSourceSelectArguments dsArgg = new DataSourceSelectArguments();
dsArgg = DataSourceSelectArguments.Empty;
ss = ((SqlDataSource)Session["SearchAppTable"]);
dv = (DataView)ss.Select(dsArgg);
dv.RowFilter = strWhere;
dsSearchAppTable.Tables.Add(dv.Table.Clone());
return dsSearchAppTable;
}
And to mention it, dsSearchAppTable returns a Table with 0 rows. Not sure what the heck I'm doing wrong.
J
I am trying to make a query that would list all the rows in a table called tickets. It should order the results based on the amount of rows another table has, called rating, but only the rows where the ticketid (primary key) matches and where the date is a required date or higher. It should also order them based on the osid which is located in a systems table (there is a relationship between tickets and systems). So: Order by rows in ratings table relating to ticketid that happened in last week (or another min date)Only display it where osid in systems matchs (easy enough)
View 2 Replies View Relatedwhere should be the calculations be made based on performance, in SQL or in front end?
thanks.
-Ron-
i need help on this one
View 8 Replies View RelatedHi all, this probably is a stupid question, but here I go.
I have read in some places that you can't set up an already existent column as IDENTITY, but in Enterprise Manager, you can do it. My question is, how EM can do this? Because I need to do this in a lot of databases, and I have to do this by SQL.
Kind Reggards
Dirceu
I want to create a page(using GridView) where it will detect changes made in the database and display it for Administrator observation. I've created a table name history for this purpose.
History
guid (uniqueidentifier)
dateCreated(datetime)
lastDateUpdated(datetime)
changesMade(varchar(50)) ---- eg; dropdownlist
oldValue(varchar(50)) ----- compaq
newValue(varchar(50)) -----dell
updateBy(varchar(10)) <------ username of registered user
comNo(int) = foreign key for Computer table
History data will insert data whenever a changes made. Could anyone advise and direct me how to do this function. I was thinking of using stored procedure to insert the data. Thanks in advance.
Someone please help me with this.
I'm trying to fire off an already created DTS package. This package is stored within SQL Server's -- underneith the Data Transformation Services / Local Packages section.
HOW CAN I FIRE THIS OFF FROM A VB .NET APPLICATION
I'm familiar with strored procedures and using them in vb.net so if somone could lead me down that road I would be very much appriciated.
Thanks in advance everyone,
RB
Here is the situation. I have an employee who is making changes but I can't prove it. He thinks he knows more than he does and he's mess'n everything up. I would like to know if SQL or some third party product has the capability to see the change and log what was changed with the persons username attached?
View 1 Replies View RelatedIs it possible to capture in a log, all users who connect to the database?
View 2 Replies View RelatedHi guys
Am new in SQL server. i use SQL server 2003 ans am trying to retieve the last three orders made. Can you please help me with my Select statement so that it will only select the last three coz @ the moment it selects everything
CODE:
SELECT dbo.Invoice.InvoiceNo, dbo.Sales.UnitsSold, dbo.Sales.QuantitySold, dbo.Sales.CostAmount, dbo.Item.ItemCode, dbo.Item.ItemDescription,
dbo.Item.Item, dbo.PurchaseOrder.OrderNumber, dbo.PurchaseOrder.OrderDate, dbo.PurchaseOrder.OrderStatus, dbo.PurchaseOrder.QuantityOrdered,
dbo.PurchaseOrder.QuantityReceived, dbo.Customer.CustomerCode
FROM dbo.Invoice INNER JOIN
dbo.Sales ON dbo.Invoice.InvoiceKey = dbo.Sales.InvoiceKey INNER JOIN
dbo.Item ON dbo.Sales.ItemKey = dbo.Item.ItemKey INNER JOIN
dbo.PurchaseOrder ON dbo.Sales.VendorKey = dbo.PurchaseOrder.VendorKey INNER JOIN
dbo.Customer ON dbo.Sales.CustomerKey = dbo.Customer.CustomerKey
Thanks
Noks
Hello there,Does anyone know of a way to track changes to an SQL Server database so thatI can easily run those changes at a later date?That is, I want to make schema changes, and record those changes so that Ican execute them 6 months later on a copy of the orignal database.Thank you kindly for any ideas anyone may haveJohn
View 20 Replies View RelatedI have an application that connects and edits a database's tables.My question is, what is the best way to save who did what changes tothis database? I need to be able to display this in the application.The best way I have thought of so far is, to create a new table with'user', 'date', 'table_name', 'primary_key', 'old field_value' and'new_field_value' fields. Then I can save which table row had beenchanged, by which user and on which date.I'm sure that there is a way to do this in SQL Server, but I'm notsure how.Any help would be appriciated.Jagdip
View 1 Replies View RelatedIn every database we have 'sysobjects' table storing names of all objects(including all keys) in our data base. How can I find all columns, that the key is made of? Sorry about my english :-/
View 1 Replies View RelatedHi, we are almost finished developing our database and we have a table we want to monitor because it is getting information deleted from it
and it has a delete trigger on it but we want to track the changes to
the table and were wondering how to track specific changes to a user database?
We want to see who is making the change, what the change they are making is and also what is the time they are making it. I have used
SELECT * FROM SYSPROCESSES
and I am running SQL TRACE with filter on MS SQLEW, and MS TRANS, and
Visual Basic with SQL statements on tblRoute,( the table that I want to monitor) and I want to know if there is any other way to monitor this table more closely?
Hi all, Hope everyone is well, my poor Steelers got chomped and spit, and I've been spending lots of time today eating my words *sigh*.
However, in between all the crow-chomping, I have run into a problem I think I am too closely involved with to see around.
I have two identical tables in two different databases. They contain (for simplicity's sake) a symbol, and a ranking for that symbol. The two tables should be the same, but are sometimes not, so I am trying to figure out a way to select from the tables in such a way that I can say:
"Symbol xxx is ranked nn in table t1, but is ranked mm in table t2"
Perhaps I am getting too fancy, but thought I could do it in a single select.
here's what I have so far:
CREATE TABLE tMyPicks (
sym VARCHAR(5) NOT NULL
, rank INT NOT NULL
)
CREATE TABLE tUrPicks (
sym VARCHAR(5) NOT NULL
, rank INT NOT NULL
)
INSERT INTO tMyPicks (sym, rank)
SELECT 'BFA', 1
UNION SELECT 'BFB', 2
UNION SELECT 'BFC', 3
UNION SELECT 'BFD', 4
UNION SELECT 'IMA', 5
UNION SELECT 'ICU', 6
UNION SELECT 'SOB', 7
UNION SELECT 'SORU', 8
UNION SELECT 'HERE', 9
INSERT INTO tUrPicks (sym, rank)
SELECT 'BFA', 1
UNION SELECT 'BFB', 2
UNION SELECT 'BFC', 3
UNION SELECT 'BFD', 5
UNION SELECT 'IMA', 7
UNION SELECT 'ICU', 6
UNION SELECT 'SOB', 8
UNION SELECT 'SORU', 4
UNION SELECT 'NHERE', 9
select sym, rank
from (select sym, rank from tMyPicks
union all
select sym, rank from tUrPicks) AS MyUnionTable
group by sym, rank
having count(*) <> 2
order by sym
DROP TABLE tMyPicks
DROP TABLE tUrPicks
This results in output that is a step away from what I want...that is, it at least identifes the individual symbols (and the associated ranking) that are NOT the same in the two tables.
IF there was a way to show which table the output of my union came from, I would be good to go, and thought I could add a literal to the select lists from each table in the UNION, but that destroys my group by clause.
Any thoughts? I suspect I will have to go away from my use of the UNION, but when I try using a join, I still have a problem with the grouping logic.
I suspect I am trying to be TOO dang "fancy" but at the moment I think I am too close to the forest to see the trees. :(
Is it possible to find out when a value was last changed/updated? I want to list all entries made after a specified date.
View 6 Replies View RelatedI have sql server 2000 Personal Edition with service pack 4 loaded on a laptop running Windows XP and I'm trying to install a program that automatically loads all the tables and files to the SQL Server.
I keep getting the following error:
Your connection could not be created. Check your settings. The error was: Login failed for user "FileArch"
The program had been previously loaded on this laptop and I had to rebuild the hard drive. When I check Enterprise manager I see in Databases that the FileArch database is in Master as opposed to its own database.
The database missing is called FileArch.
If anyone can provide me with help to solve this I'd be grateful as a new member.
Bob
COUNTING NUMBER OF SELECTS MADEtable mytable {id, data, hits}users view data from the table:SELECT data FROM mytable WHERE id=1 --for exampleSELECT data FROM mytable WHERE id=20 --for example....How do increment the hits column without replacing the above with the below?update mytable SET hits=hits+1 WHERE id=1;SELECT data FROM mytable WHERE id=1update mytable SET hits=hits+1 WHERE id=1;SELECT data FROM mytable WHERE id=20....I believe triggers can't be used as they only trigger on update/delete events.I'm using sql server 2000 (latest patches) with aspThanksAlex
View 3 Replies View RelatedHi All,This seems like a tricky question to me. I have a Stored Procedurethat encapsulates a number of updates to various tables within atransaction.However, at a later part of the transaction I need to be able toselect records changed by an update statement made earlier within thesame stored proc (and within the same transaction) and need for thatselect to reflect the changed values.My understanding, however, is that the records aren't actually changedby the update statement until the transaction is committed, andtherefore my later select statement won't return the expected recordsbecause the update is being held until the transaction is committed.Is this accurate? And, if so, is there a reasonable workaround thatstill leaves me able to rollback the entire transaction if I strike aproblem somewhere along the way?So, a pseudo code example would be:BEGIN TRANSACTIONUPDATE mytable SET myid = @yourid WHERE myid = @idSELECT * FROM mytable where myid = @idCOMMIT TRANSACTIONIn this above example, would the select statement return the recordsthat have a myid value of @id as before the update as after theupdate?Many, many thanks in advance!Much warmth,Murray
View 2 Replies View RelatedWhile attempting to deploy from within VS2005 running on 2003 server we get the following error:
TITLE: Microsoft Semantic Model Designer
------------------------------
A connection could not be made to the report server http://xxxxx:90/ReportServer.
Running sql2005 sp2 integrated w Sharepoint 2007
Thanks..
Hi !
I'm trying to figure out how can i get a list of the reports made with reportbuilder. I have create a folder in ReportManger and all my report are in this folder.
When i create report with ReportDesigner i get a .RDL file, but when i create a report using reportBuilder i can't file the file created. What i want to do is to create in our web application a Web page with a list of report that our client has create using ReportBuilder. I was thinking about doing a loop on the folder for each report File but i can't find those files.
Any idea ?
Thanks!
hi,
I got this error when I was trying to deploy the reporting into IIS..
TITLE: Microsoft Report Designer
------------------------------
A connection could not be made to the report server http://localhost/reportserver.
------------------------------
ADDITIONAL INFORMATION:
The server committed a protocol violation. Section=ResponseStatusLine (System.Web.Services)
------------------------------
BUTTONS:
OK
------------------------------
I am not able to solve it please help.
Thanks.
Hi,
I just ended a case with microsoft because I had an issue with Analysis services. The thing is, one of the solutions given by microsoft was to install cumulative update 5 for SQL Server 2005 SP2. Now SQL Server is using all the memory on the server which has windows 2003 server R2 Enterprise SP2 (8GB of Ram) /3GB and /PAE enabled. The weird thing is non of the sql server processes displayed in Task manager shows more than 200MB of memory. But, the server only has 500MB of memory available and 7GB of PF Usage. How did I find out that SQL Server was using the memory, well I shut down the service and 6.8 GB of memory available and the PF Usage went down to just 1.8GB. Turned on again and then the same situation.
well it seems SQL Server is reserving memory or something.
Any clues?
First, thank you all for the help so far.My new, broader Question is this: What is the most efficient (read easiest) way to code in an inset statement into a remoste SQL 2003 database from IIS 6 running asp.net 2.0?Here is the situation: I have a form that requires the user to be signed in. I then want to get the value of a text box TBOne(String), a dropdownlist DDOne(Text), another Textbox TBTwo (int) and insert these values in to a NEW row of a database (DBTest) into table TTest, with the UserName (String), and todaysdate (SmallDateTime).I am a JSP programmer, and it's fairly easy to do in JSP, but I am trying to leverage the SQL Datasource, to make my life easier.Is there a simple way to do this? Something like:String username=page.someintg.User.name;String TBOne=TBOne.text;...Insert into DBTest.TTest Values(username, TBOne, ...) ? TIA Dan
View 5 Replies View Relatedi am building a shopping cart. I want to update the UNIT_IN_STOCK column in database after order have been submitted. i want to subtract the quanity value from the order made from the UNIT_IN_STOCK column in database. how would the sql statement be like?? i tried this but it didnt work. any suggestions??
CREATE PROCEDURE update_Products_By_name
(
@ProductName varchar,
@UnitInStock int
)
AS
UPDATE Products
SET UnitInStock=(UnitInStock-@UnitInStock)
WHERE ProductName = @ProductName
GO
I have another question guys I need to send reports to different departments using sql. would this be the xp_sendmail function in sql. Can this be made into a stored procedure? What I'm going to do is make some reports which will constantly be updated with a query. These reports need to be emailed to differnt departments every 2 weeks. Is there a trigger or stored procedure I can setup through sql to do this? Does that make any sense??
View 8 Replies View RelatedHi,Sorry for the obscure title but I'm afraid I can't think of a betterway to describe what happened to one of my clerks last night. The guy wasworking late, made a series of changes (accross a number of tables with adependant relationship structure - i.e. a customer linked to an order linkedto an invoice linked to a payments etc.) Now when he came back this morningnone of the work he did last night was still there. I'm by no means asql-pro - but I've managed to make do so far. Here's what I know:1) All of our records in all of our tables have date/time stamped. Once whenthey go in and once when they're changed. So with a little work I can buildup a good picture of what usage our database gets at what times. I've pulledup a query and there is a big fat hole between 8pm-10pm - stuff thathappened before that is still there, stuff that happened after that is stillthere - but all the changes that were put in between then just aren't thereany more. So it's not just insertions but updates as well.2) There is no perceivable break in our identity columns. So despite thefact that I know he entered 7 new customers, I can go back through thecustomers table and look at where those customers should sit visa vie entrytimes - and it's just as though the customers he entered never existed -sql server just keeps incrementing sequentially and there's no break - sothe last customer entered at about 6:50 pm was something like number 11912and the customers entered this morning have numbers that carry on from11913.3) I'm running in a shared environment - the server is hosted by awebhosting company (who shall remain nameless unless I can prove it's theirfault!) based in the US - the server has 100's of other Users databasesrunning on it.I just don't know where to begin diagnosing this sort of problem. But it hasme really running scared. It's not the first time something like this hashappened to me (i've noticed it once before when I had to get a connectionkilled by the people who manage our server because of a long-running querythat seemed to have just got bunged up and was locking a key table) - butthat was just a few records changed by the user who's connection got killed.Nothing like this... but it's pretty scary - I've got no way of knowing thatI'm losing changes like this on a continuous basis. It's my worstnightmare - like a pipe leaking underneath a floor board - and you only findout when the water starts running down the stairs!Has anyone got any ideas? Any starting places? Anyone else had anything likethis happen to them before?ThanksNick
View 5 Replies View RelatedLooks like I'm up to no good as usual.
Basically due to a client spec, I had to create 115 datasets (one for each of my 115 tables).
After I created the 111th, I am getting the following error:
A connection cannot be made to the database. Timeout Expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.
How do I clear the pooled connections or increase the max pool size?
I suppose what happened is each time I create a dataset it opens a connection to the database and these connections aren't being terminated...
when paging down or across in a matrix based report, it would be nice for headings (row and column) to float (ie not disappear) with the position of the report. Is this possible in RS?
View 10 Replies View RelatedHello all, I was wondering. I am having trouble in my old age remembering to update my schema table when I make changes to the database. Is there a DB trigger or something that I could write to update this table for me when I edit or update a table structure, view, Trigger, or stored procedure?
Thank you in advance,
Jim
I just tried to install SP2 of the Sql Server Express but it wouldn't install.
The message was somthing like:
No effective changes to make.
Am I missing something here?
How do I check my version?