|
Hi when i parse this query i am getting sucesfull command...,
When i execute the query getting error like "Syntax error converting character string to smalldatetime data type"...,
Thsi is my procedure:
declare
@mthinc as tinyint,
@years as smallint,
@mth as varchar(50),
@yr as varchar(50)
set @mthinc=6
set @mth='@mthinc'
set @years=2007
set @yr='@years'
begin
select empid,max(edate) as edate from tblsalarydetails
where edate < @mth + '/01/' + @yr group by empid
end
I think to assign the tinyint & smallint value to varchar variable place i am getting error...,
How to solve that...,
Plz hlp me...,
Advance thanks,
Regards,
Magi
|
|
|
|
|
Hmm - this seems to be a little bit overcomplicated. Drop the varchar fields and change your where condition to
where edate < cast(@mthinc as varchar) + '/01/' + cast(@years as varchar) I really should point out here that you would be better off explicitly converting the condition into a smalldatetime to give the query optimizer a better chance of using an index on the column.
-- modified at 14:44 Wednesday 1st August, 2007
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
I have Table called Customers with CustomerId as Primay key, FirstName and LastName. I also have a table called CustomerUpdates.
CustomerUpdates table has a foreign key of CustomerID. It also contains FirstName and LastName columns.
How can I write a query to update the FirstName and LastName in Customers table with
the FirstName and LastName in CustomerUpdates?
Please help.
|
|
|
|
|
Hi i think,
You must update one table at a time. You can update A while joining it with B to get the values you need.
ie:
UPDATE A
SET A.myField = B.newField
FROM A, B
WHERE A.primaryKey = B.foreignKey
Reply With Quote
OR try some thing like this
UPDATE CaveatsData
SET CaveatsData.Title = CavTitle.Title
FROM CavTitle, CaveatsData
where CavTitle.RecId = CaveatsData.RecId
Prakash Mishra(Banglore,India)
|
|
|
|
|
changing table name with code
hos
|
|
|
|
|
Sounds like fun.
I assume you regard this as a question. It's more like an appropriate subject line. What code ? What language ? T-SQL ? some other SQL dialect ? C# ? C++ ? LISP ? You get the idea.
Christian Graus - Microsoft MVP - C++
"I am working on a project that will convert a FORTRAN code to corresponding C++ code.I am not aware of FORTRAN syntax" ( spotted in the C++/CLI forum )
|
|
|
|
|
What database and version? What language?
_____________________________________________________________________
Our developers never release
code. Rather, it tends to escape, pillaging the countryside all around.
The Enlightenment Project (paraphrased comment)
Visit Me at GISDevCafe
|
|
|
|
|
If you are using SQL Server 2000, you would rename it using the sp_rename method. Suppose that you are renaming a table called MyTable to My_Table, you would run exec sp_rename 'MyTable', 'My_Table'.
It is important for you to be aware though that this is a very dangerous operation to undertake and you shouldn't do so lightly. When you rename an object like this you don't get any form of dependency checking and, as a result, anything that relies on the table being called MyTable will fail. Some areas that you would need to check include your stored procedures, views, functions. Don't forget to check your DTS packages as well - I've lost count of the number of times I've seen DTS packages failing because somebody renamed a table and didn't bother to update the mappings inside DTS.
Finally, make sure that you don't expose this functionality to the outside world. Do not, under any circumstance, allow any client code to do this - it WILL break things.
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
If I have a query like
select bkID, count(*) from SalesLog where SaleDate = '20070410' group by bkID compute sum (count(*))
and if I had called it via a vb application is there any way I can get the value corresponding the sum?
|
|
|
|
|
hi all,
i m using Access 2007. if i create an query which use replace function and execute directly then it execute successfully in database .but if i run same query through application then it give error "Undefined function replace in expression".
i don't know where i am wrong ?
query is following
UPDATE master SET titles = replace(titles,'Trainer','Rupesh');
any body have idea or some suggestion ?
thanks in advance.
Rupesh Kumar Swami
Software Engineer,
Integrated Solution,
Bikaner (India)
|
|
|
|
|
For security reasons, MS Access only permits a handful of functions to be called from an application outside of Access itself. I'm guessing that's the problem, but honestly, don't know if the "Replace()" function is on the forbidden-list.
Before I knew about this issue, I spent a lot of time writing VBA functions and using them within my queries, only to discover that none of the functions I wrote could be accessed from an outside application. Every query I called from the outside application that used one of my functions failed. I had to completely abandon the application and start over with an Access application.
Actually, I did do some googling looking for the list of functions allowed, but couldn't find it. Maybe some other reader knows where it is.
I don't understand how all this works, but try going to Access, Tools->Macro->Security, and select "Low" and see if that helps. If not, then I would restore the setting to it's previous setting.
David
|
|
|
|
|
Hi all
I am trying to use xp_SendMail from a stored procedure and it keeps failing with an error message about not specifying a parameter for @recipients when obviously I have specified it.
Sample
CREATE PROCEDURE<br />
...<br />
IF error GOTO ERROR<br />
...<br />
RETURN<br />
<br />
ERROR:<br />
exec master.dbo.xp_SendMail @recipients = "bla@here.com",...<br />
GO
If I move the xp_SendMail statement to between the Create procedure and Return statements (copy and paste) it works, it only does not work when it's in the 'ERROR:...GO' section. 
Anybody please...Deadlines are aproachin' 
Kiefie
The man with a plan. Now where'd that plan go?
|
|
|
|
|
So I have a table of teams, orgTeams, each of which has a primary key, teamID.
I have a table of users, orgUsers, each of which has field that describes what team the user is on, userTeamID.
If I want to get a list of all of the teams that have at least one person on them, it is easy:
SELECT DISTINCT teamID FROM orgTeams,orgUsers WHERE userTeamID=teamID
However, with people moving from team to team, sometimes teams end up "empty", i.e. with nobody at all on them. What query can I use to identify such "empty" teams?
|
|
|
|
|
To determine if a table has rows in it you can use
SELECT COUNT(*) FROM tableName
ie. ( I think)
SELECT DISTINCT teamID FROM orgTeams,orgUsers WHERE userTeamID=teamID and COUNT(orgTeams.Person) > 0
Kiefie
The man with a plan.
|
|
|
|
|
I understand the COUNT() function, but I don't understand what orgTeams.Person is supposed to refer to in your example. I don't have a "Person" field in my table orgTeams.
|
|
|
|
|
I don't know what fields you have in your table so I made it up. Could work with just about any field. I see there are more posts which will probably work better.
Kiefie
The man with a plan.
|
|
|
|
|
You could probably do this with:
select distinct teamId
from orgTeams
left join orgUsers
on teamID = userTeamId
group by userTeamID, teamID
having count(userTeamID) = 0
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
select teamId
from orgTeams
left join orgUsers
on teamID = userTeamId
where userTeamId IS NULL is equivalent and likely to execute quicker.
|
|
|
|
|
True - this is what happens when you code when tired. You end up overcomplicating things. Thanks for pointing this out to the OP.
I'd like to think that I would have spotted this, this morning.
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
Thank you! This worked perfectly.
|
|
|
|
|
I have 2 tables. I want to use info from the second table to update column values in the first. Consider:
T1:
id time1 time2
1 8:00 null
2 8:01 null
3 8:02 null
4 8:12 null
T2:
id time2
2 8:09
4 8:15
What I want is:
T1:
id time1 time2
1 8:00 null
2 8:01 8:09
3 8:02 null
4 8:12 8:15
Is there an sql command sequence that can achieve this? Its easy to think about it iteratively, but in the absense of explicit iterators in sql (to my understanding) I hope that there's another way.
Its clear that I can exec the join:
select T1.id, T1.time2 from T1, T2 where T1.id = T2.id
but using this as part of an update is broken (well, because I don't know how to do it):
(wrong
update T1
set time2 = ( select time2 from T1, T2 where T1.id = T2.id)
where id = ( select id from T1, T2 where T1.id = T2.id )
how can I write an <update> on T1.time2 that matches T1.id = T2.id and updates the T1.time2 null with the T2.time2 ?
|
|
|
|
|
UPDATE T1
SET time2 = T2.time2
FROM T2
WHERE T1.id = T2.id Tested with SQL Server 2000 SP4.
You basically have an implicit cross-join with the table you're updating and the tables and join statements listed in the FROM clause.
|
|
|
|
|
Hi,
I need to do a check on a date field in my table, I need to check if the entered year is in the publication date. Currently I have it like this:
SET ROWCOUNT 500;
SELECT *
FROM tblFullTextSearch
WHERE
fldPublicationDate LIKE (1995);
The reson why I used like is because the user can type any number into the date input textbok. The date in the table looks like:
1995-03-01 00:00:00.000
Please can someone help.
Regards
ma se
|
|
|
|
|
SET ROWCOUNT 500;
SELECT *
FROM tblFullTextSearch
WHERE
YEAR(fldPublicationDate) = 1995;
|
|
|
|
|
You can do that, but SQL Server cannot make use of any indexes on the fldPublicationDate column. If the year is what's wanted, you can do
SELECT *
FROM tblFullTextSearch
WHERE fldPublicationDate >= '19950101' AND fldPublicationDate < '19960101' Obviously this technique does not work if you want to find all records from March, or the first day of the month, for example.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
