DECLARE @fullname nvarchar(50)
SET @fullname =
(SELECT (OriginalName + ContentType) AS Name
FROM Files_Dyn)
INSERT
INTO Files_Dyn(FullName)
VALUES (@fullname) where username = 'user_admin'
...what is the wrong with this query..it is giving 'Incorrect syntax near the keyword 'where'. Please correct me!
thanks in advance!
I’ve got a table called tblApplicant_Details with the following fields - Applicant_ID, Application_ID, Net_Income, Loans.
In this table I’ve got a list of people and their income details (Net_Income) and expenses (loans). In some cases there will be 2 applicants with the same application_ID.
What I need to do is select one applicant (Applicant_ID) per application (Application_ID). In the case of 2 applicants for an application, I need to select the person with the highest income (net_income - loans), if both of the applicants have the same income I want the one with the lowest Applicant_ID and if only one person applies then that person.
Below is the code I’ve been using. I’ve noticed that its not always selecting an applicant for each application. I know it’s also very long for what I am trying to do but I was hoping someone would be able to tell me how I can fix it and tidy it up a bit.
SELECT Application_ID, MIN(Applicant_ID) AS Applicant_ID FROM (SELECT DERIVEDTBL.Application_ID, dbo.tblAPPLICANT_DETAILS.Applicant_ID FROM (SELECT MAX(APPLICANT.Net_Income - APPLICANT.Loans) AS Income, APPLICATION.Application_ID FROM dbo.tblAPPLICANT_DETAILS APPLICANT INNER JOIN dbo.tblAPPLICATION_DETAILS APPLICATION ON APPLICANT.Application_ID = APPLICATION.Application_ID GROUP BY APPLICATION.Application_ID) DERIVEDTBL INNER JOIN dbo.tblAPPLICANT_DETAILS ON DERIVEDTBL.Application_ID = dbo.tblAPPLICANT_DETAILS.Application_ID AND DERIVEDTBL.Income = dbo.tblAPPLICANT_DETAILS.Net_Income - Loans) DERIVEDTBL GROUP BY Application_ID
select min(dbo.FS_ItemInventory.Bin) from (SELECT MIN(dbo.FS_LotTrace.LotNumber),dbo.FS_ItemInventory.Bin FROM dbo.FS_LotTrace RIGHT OUTER JOIN dbo.FS_MOHeader AS h INNER JOIN dbo.FS_MOLine AS l ON l.MOHeaderKey = h.MOHeaderKey INNER JOIN dbo.FS_Item AS i ON i.ItemKey = l.ItemKey INNER JOIN dbo.FS_MOLineData ON l.MOLineKey = dbo.FS_MOLineData.MOLineKey LEFT OUTER JOIN dbo.FS_DemandSupply AS ds ON l.MOLineKey = ds.TopLevelDemandSupplyKey LEFT OUTER JOIN dbo.FS_ItemInventory RIGHT OUTER JOIN dbo.FS_Item AS di ON dbo.FS_ItemInventory.ItemKey = di.ItemKey on ds.DemandItemKey = di.ItemKey on dbo.FS_LotTrace.LotTraceKey = dbo.FS_ItemInventory.LotTraceKey where (dbo.FS_ItemInventory.InventoryCategory!='H' or dbo.FS_ItemInventory.InventoryCategory is null) group by di.ItemNumber,dbo.FS_ItemInventory.Bin)
select min(dbo.FS_ItemInventory.Bin) from (SELECT MIN(dbo.FS_LotTrace.LotNumber),dbo.FS_ItemInventory.Bin FROM dbo.FS_LotTrace RIGHT OUTER JOIN dbo.FS_MOHeader AS h INNER JOIN dbo.FS_MOLine AS l ON l.MOHeaderKey = h.MOHeaderKey INNER JOIN dbo.FS_Item AS i ON i.ItemKey = l.ItemKey INNER JOIN dbo.FS_MOLineData ON l.MOLineKey = dbo.FS_MOLineData.MOLineKey LEFT OUTER JOIN dbo.FS_DemandSupply AS ds ON l.MOLineKey = ds.TopLevelDemandSupplyKey LEFT OUTER JOIN dbo.FS_ItemInventory RIGHT OUTER JOIN dbo.FS_Item AS di ON dbo.FS_ItemInventory.ItemKey = di.ItemKey on ds.DemandItemKey = di.ItemKey on dbo.FS_LotTrace.LotTraceKey = dbo.FS_ItemInventory.LotTraceKey where (dbo.FS_ItemInventory.InventoryCategory!='H' or dbo.FS_ItemInventory.InventoryCategory is null) group by di.ItemNumber,dbo.FS_ItemInventory.Bin)
I am new at SQL and am using SQL server express edition and im a bit stuck! I am using ASP.NET and C# in my website which is using sql database back end.
String SQLroom = "SELECT DISTINCT RoomName FROM Room INNER JOIN RoomCalendar ON Room.RoomID = RoomCalendar.RoomID WHERE Capacity = '" + reqCapacity + "' " + " AND NOT ('" + newRoomEnd + "' <= roomStartDateTime OR '" + newRoomStart + "' >= roomEndDateTime) AND (OHP = '" + ohpYesNo + "' AND AV = '" + avYesNo + "') ";
This is my SQL string... what it is trying to do is:
find the room where the capacity is the reqcapacity entered by user and the startdatetime and enddatetime entered by the user are not present in the table for that room capacity and then look at whether the user requires OHP or AV facilities, which are stored in the database as either yes or no values. The problem i am having is with the condition in the sql query... because the user may require an OHP and not AV, but then the room returned "`could" have AV facilities, as it wouldnt make a difference to them if it was there or not, basically, the yes condition has to be satisfied.
Not sure whether i should be using AND, or OR? or a combination.
1 SELECT distinct A.EAIndex, 2 A.ChainCode AS ChainCode, 3 A.JobDate, 4 C.BillerName, 5 B.BusinessCode, 6 D.ChainName, 7 A.BillBegDate, 8 A.BillEndDate, 9 A.PostDate, 10 A.BillCount, 11 A.ChAmount, 12 A.PostAmount, 13 A.ProChargeFee, 14 A.BalanceStatus AS BalanceStatus, 15 B.BillerInfoCode AS BillerInfoCode , 16 CONVERT(varchar(10), A.BalanceDate, 112)BalanceDate, 17 A.AdjustDate, 18 C.BillerCode , 19 (a.ChAmount- a.ProChargeFee) AS Fee, 20 E.EABillerCode, E.RelAdjustDate 21 FROM ZT_EAccount A 22 Inner Join ZT_BillerInfo B On Right('00000'+Rtrim(Ltrim(A.BillerCode)),5) = Right('00000'+Rtrim(Ltrim(B.BillerInfoCode)),5) 23 Inner Join ZT_Biller C ON B.ParentCode = C.BillerCode 24 Inner Join ZT_Chain D On A.ChainCode = D.ChainCode 25 Inner Join ZT_EAccountAdjust E ON a.EAIndex=E.EAIndex and a.BillerCode=E.BusCode 26 Where A.JobDate BETWEEN '20071001' AND '20071005' AND C.CompanyCode='533' In line 13 I want to add a Query to make sure if ZT_BillerInfo.Rmflag = 1, if it's =1 then ProChargeFee =0if ZT_BillerInfo.RmFlag =0 , then ProChargeFee = a.ProChargeFee.I add this line �Case B.RmFlag = '1' then 0 else A.ProChargeFee 】instead of Line 13 (A.ProChargeFee)but I execute Sql I got error message on the line I have jsut modified..can you please help me to know why and how to correct it? thank you very much
Hi, I am using SqlCacheDependency with the query notifications of SQL server 2005. It is working nicely with the dbo.owner account but not with my web user account, so I am thinking something is wrong with the permissions. After searching around, it seems that the main permissions to enable are these: GRANT SUBSCRIBE QUERY NOTIFICATIONS TO theAccount GRANT RECEIVE ON QueryNotificationErrorsQueue TO theAccount GRANT REFERENCES on CONTRACT::[http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification] to theAccount GRANT SELECT TO theAccount.
I enabled these and it still doesn't work. When I change my table, my cache is not getting invalidations. Does anyone know what could be going on? Or perhaps how can I trace the problem and get some clues? thanks so much, -tajmahal
I've created C#.net program (behind code style). when I run it in Internet explorer, the following error occurs in IE window. pls instruct me how to handle and correct this error. And how to initialize the connectionstring... Great thank!
Server Error in '/' Application. -------------------------------------------------------------------------------- The ConnectionString property has not been initialized. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.InvalidOperationException: The ConnectionString property has not been initialized. Source Error: An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below. Stack Trace: [InvalidOperationException: The ConnectionString property has not been initialized.] System.Data.SqlClient.SqlConnection.Open() +809 CodeBox.BehindCode.getSubject() +80 CodeBox.BehindCode.Page_Load(Object sender, EventArgs e) +31 System.Web.UI.Control.OnLoad(EventArgs e) +67 System.Web.UI.Control.LoadRecursive() +29 System.Web.UI.Page.ProcessRequestMain() +724 -------------------------------------------------------------------------------- Version Information: Microsoft .NET Framework Version:1.0.3705.0; ASP.NET Version:1.0.3705.0
I have a simple while process to use with a trigger to insert values into another table. IN VB this was simple but the while in TSQL seems a little different. If anyone can point out my flaw greatly appreciated.
while @cnter < @nodays --insert values insert into table values (value1, value2) --then increment counters and repeat set @sdate = @sdate + 1 Set @cnter = @cnter + 1 How or what is the best way to loop back?
If you start receiving continuous error messages by e-mail indicating that the transaction log is full. After 2 days, the messages suddenly stopped. What could be the reason?
Windows NT App log is full or SQL Server Agent stopped
I think SQL Server Agent stopped How do you think..and why ?
If you start receiving continuous error messages by e-mail indicating that the transaction log is full. After 2 days, the messages suddenly stopped. What could be the reason?
Windows NT App log is full or SQL Server Agent stopped
I think SQL Server Agent stopped How do you think..and why ?
Im new to SQL so please bear with me & help me as to why Im not getting the desired results.
I want to find the difference between two sets of tables that reside in different databases but contain the same data. I ONLY WANT a. records that are only in A but not in B b. records that are only in B but not in A ______________________________________________________________________
Here is what I wrote using something that I found in this forum -
CREATE PROCEDURE RPT_DETAILS AS BEGIN DECLARE @Rowcount AS INT DECLARE @First_Name AS VARCHAR(50) DECLARE @Last_Name AS VARCHAR(50) DECLARE @Id AS INT
CREATE TABLE #Prowess(ID INT NOT NULL, First_Name VARCHAR(50), Last_Name VARCHAR(50)) CREATE TABLE #SDK(ID INT NOT NULL, First_Name VARCHAR(50), Last_Name VARCHAR(50))
INSERT INTO #Prowess SELECT bb.beenumber, be.FirstName, be.LastName FROM beebusiness bb join beeentity be on bb.beebusinessguid = bb.beebusinessguid
INSERT INTO #SDK SELECT cast(sa_ss as INT), first_name, last_name from ml
SELECT @ROWCOUNT = MAX(ID) FROM #SDK PRINT '------------------------------------------------------------------------------------------' PRINT '------------------------COMPARISION REPORT Between Prowess & SDK--------------------------' PRINT '------------------------------------------------------------------------------------------'
PRINT 'TOTAL Difference ('+ + CAST(@ROWCOUNT AS VARCHAR(50))
WHILE @ROWCOUNT > 0 BEGIN SELECT @First_Name = First_name, @Last_Name = Last_name, @ID = ID FROM #Prowess WHERE ID = @ROWCOUNT PRINT ' * '+@First_Name+@Last_Name SET @ROWCOUNT = @ROWCOUNT - 1 END
SELECT @ROWCOUNT = MAX(ID) FROM #Sdk
PRINT 'TOTAL Difference ('+ + CAST(@ROWCOUNT AS VARCHAR(50))
WHILE @ROWCOUNT > 0 BEGIN SELECT @First_Name = First_name, @Last_Name = Last_name, @ID = ID FROM #Sdk WHERE ID = @ROWCOUNT PRINT ' * '+@First_Name+@Last_Name SET @ROWCOUNT = @ROWCOUNT - 1 END
declare @var varchar(50) set @var= 'COLUMNNAME' select ID, a.@var , b.@var from rooper a join jim_rooper b on b.id = a.id join b_rooper bb on bb.id = a.id where a.@var != b.@var
All that Im trying to do here is instead of using a columnname, Im trying to substitute it with a variable so that it can be referenced at multiple places...
Is it correct way to register my CLR library instead of having T-SQL codes (eg, Strored Procedure, Functions and Triggers) in the database in the following case:
Code security: If my Application (in .NET 2.0) and SQL Server Express in same PC and I have to give Windows-Administrator password to my application-user (to install/unistall some other softwares)
HelloI have having trouble displaying some simple columns in ascending order.I know that the database is populated and I can get the more complex code to work if I display like this: SELECT FName, LName, Town, '<a href="' + url + '">' + Site + '</a>' as LinkFROM Names_DBWHERE FName = 'Tom' And url like 'http:%'ORDER BY LName ASCBut I need a simpler view but I can't get it to workI have tried this:SELECT FName, LNameFROM Names_DBORDER BY LName ASCAnd thisSELECT FName, LNameFROM Names_DBORDER BY LName ASC; And This:SELECT FName, LNameFROM Names_DBORDER BY LName 'ASC' What is wrong with this syntax?ThanksLynn
I'm trying to get a year count and a year amount of payments made by a client. Below is my statement, it is not giving a correct count. If a client made more than one payment on the same day it counts it as 1. What can I do to this statement to get correct totals? SELECT DISTINCT Client_ID, DATEPART(year,PaymentDate) AS 'Year', SUM(AmountPaid) AS 'TotalYearlyPayments', COUNT(DISTINCT Payment_ID) AS 'YearlyPaymentCount'FROM tblPaymentsWHERE Client_ID = @ClientIDGROUP BY Client_ID, DATEPART(year, PaymentDate)ORDER BY Client_ID, Year
Hi, I'm building a web application in which I want to prevent SQL injection. I'm using stored procedures, and using queries on my app like this:in my database...create proc createStudy@title varchar(200),@text textasinsert into studies values(@title,@text)goand in my web app...query="createStudy '"+titleBox.Text+"','"+textBox.Text+"'"; //title and text boxes are textboxes, createStudy is a stored procedure in my databaseodmccommand cmd = new odbccommand(query,con);con.Open();cmd.ExecuteNonQuery();But before this I do this code:if (titleBox.Text.Contains("Drop") || titleBox.Text.Contains("Delete")) messageLabel.Text="No permissions to do that";else(...my code)Is this ok to prevent SQL injection?!?
I am getting my source data from another system am storing the SaleAmount of each product in a field the data type of which is [decimal](12, 2).
For some products I am getting an exact match (upto 2 decimal places) as compared with my source data BUT for some other products the value before the decimal places is correct but the 2 digits after the decimal place does not match with the source data :confused:
Even if this sounds stupid, can you please guide me. Am i missing some very basic and common sense thing?
I have a query and I need to check to see if a field is occupied, i.e., it can have anything in it, i just want to see if something is there... this is what I want, but of course, anything isn't the right word here...
I have two tables: 1) Table that holds all available ports. 2) Table that holds users for each port.
There may be times where one user is getting more than one port at a time.
I've built up an ASP .NET page that will display each user its port/s in one table. On another table I want to display all the other available ports which the user doesn't posses and can buy to own.
My problem is where I try to build up the query. I just can't get all the other ports in normal display.
For example, this is what I need:
Ports table: 1/1 1/2 1/3 1/4
Users table: User A , 1/1 User A , 1/2
ASP page display:
------------ User A Holds: 1/1 1/2 ------------
Available: 1/3 1/4 ------------
Of course the Available option is derived from the User Holds query, and just getting the opposit not equal ports, but I just can't get it ! I've tried all kinds of Joins and nesting SELECT queries with no luck.
SELECT Countries.Name, Companies.ShortName, Persons.FirstName, Persons.LastName, PersonSkills.Skills FROM PersonSkills INNER JOIN....
How can I choose a certain value from PersonSkills.Skills?
for example, I would like to choose a value from PersonSkills.Skills that matches a certain product. I can do the Max/Min value but the selected value needs to match the product.
The tables that I have are: PersonSkills: id, ProductID, Skills
Product: id, ProductName
Ive been reading and playing around with this without success.
Hi friends, I've created one procedure.I'm trying to execute that i got the error message like 'Must declare the scalar variable @series'. but i declared it already.Table name starts with SI,dont have the fields like series and hono.I dont know how to correct this error.Please help me out.Here is my procedure.
alter proc procinsertAllFields as begin declare @series varchar(10) declare @hono varchar(5) declare @tabname varchar(8) declare @sql nvarchar(500) if exists (select * from sysobjects where name=ltrim(rtrim('ccno_dir1'))) drop table ccno_dir1 set @sql='create table ccno_dir1(cc_no varchar(20),series varchar(1), hono varchar(10),denom_code varchar(10),i_date datetime,d_date datetime, locked varchar(10),csd_no varchar(10),invoice_no int,invoice_date datetime)'--print @sql exec sp_executesql @sql
declare c cursor for select series=substring(name,3,1),hono =substring(name,4,5),name from sysobjects where name like 'si[1-3]_____' open c fetch next from c into @series,@hono,@tabname while @@fetch_status=0 begin print 'begin' fetch next from c into @series,@hono,@tabname set @sql='insert into ccno_dir1(cc_no,series,hono,denom_code,i_date,d_date,locked,csd_no,invoice_no,invoice_date) select cc_no,series=@series,hono=@hono,denom_code,i_date,d_date,locked,csd_no,invoice_no, invoice_date from '+@tabname print @sql exec sp_executesql @sql
USE CHEC SELECT [DATE_CONVERSION_TABLE_NEW].MONTH, DAY([DATE_CONVERSION_TABLE_NEW].[DISBURSEMENT DATE]) AS DayofMonth, DAT01.[_@550] AS LoanType, DAT01.[_@051] AS Branch, DAT01.[_@TP] AS ProdTypeDescr, SMT_Branches.[BranchTranType] AS TranType, --SMT_Branches.[AUCode] AS AuCode, Count(*) AS Totals FROM DAT01 INNER JOIN [DATE_CONVERSION_TABLE_NEW] --ON DAT01.[_@040] = [DATE_CONVERSION_TABLE_NEW].[DISBURSEMENT DATE] ON DAT01.[_@040] = [_@040] INNER JOIN SMT_BRANCHES ON SMT_Branches.[BranchTranType] = SMT_BRANCHES.[BranchTranType] WHERE DAT01.[_@040] Between '06/01/2006' And '06/30/2006' And SMT_BRANCHES.[BranchTranType] = 'RETAIL' AND DAT01.[_@051] = '540' --And SMT_Branches.[AUCode] = '1882' And DAT01.[_@TP] = '115' And DAT01.[_@550] = '3' GROUP BY DAT01.[_@051], DAT01.[_@550], DAT01.[_@TP], SMT_Branches.[BranchTranType], SMT_Branches.[AUCode], [DATE_CONVERSION_TABLE_NEW].MONTH, DAY([DATE_CONVERSION_TABLE_NEW].[DISBURSEMENT DATE]) ORDER BY [DATE_CONVERSION_TABLE_NEW].MONTH, DAT01.[_@051], DayofMonth ASC, SMT_Branches.[AUCode] ASC --COMPUTE sum(count(*))
This is a Stored proc I am working on I dont know much about stored procs. This is what I am trying to achieve The proc takes two input parameter and returns one outparameter.
This is what I have
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO
CREATE PROCEDURE [dbo].[GetNextAction]
( @Code char(10), @Track varchar(30), @NextAction ntext output ) AS BEGIN
SET NOCOUNT ON; Declare @ID int; @ID = Select Sequence from [dbo].[Track] where Code=@Code; Declare @nextCode varchar; @nextCode= Select Code from [dbo].[Track] where sequence =(@ID+1); @NextAction= Select nextAction from [dbo].[CaseStage] where Code=@nextCode; return @NextAction;
END GO
I guess you can understand what I am trying in the proc but the rules and syntax are wrong. Please help me with this. Thanks
