|
You are close make your query look like this;
SELECT TeamA FROM Under11s.
UNION ALL
SELECT TeamB FROM Under11s
This should work.
|
|
|
|
|
SELECT TeamA,1 as SortCol FROM Under11s<br />
UNION<br />
SELECT TeamB,2 as SortCol FROM Under11s<br />
order by SortCol,TeamB
--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
|
|
|
|
|
how to convert date without 0 in sql
like 1/1/2006 , insted of 01/01/2006
|
|
|
|
|
ypki wrote: how to convert date without 0 in sql
I didn't find any built-in or custom format to cnvert date without leading zero.
well, Have a look to this[^]
wish you all the best.
_____________________________
Success is not something to wait for, its something to work for.
|
|
|
|
|
I'm learning Adminitrator SQL server 2000.
I don't known write code or establish timer backup database.
Can you help me.
thanks.
Quynh Nhi
|
|
|
|
|
Look at the database maintenance plan wizard tool in Enterprise manager. Open the local server node in the tree, then open the management folder. right click on the database maintenance plans icon and frome the menu choos new maintenance plan. Enjoy.
In Sql Server Books Online, search for "Database Maintenance plan wizard" for more guidance on using this.
|
|
|
|
|
Hi All,
we are calling a db2 storeProc(deleting data) from the asp.net App.(Asp.Net 2.0 & C#)
i have a situation, sometimes datareader returns saying "Unsuccessful execution caused by deadlock".
Do any one have any idea what might be the reason. i talked to one db person, he said i need to commit the transaction after i call the storeProc.
i haven't used commit from Application till now. Can some one tell me how to commit a transaction from the App?
(one of my friend told, he commits after calling storeProc update from his java App. looking similar to java one.)
|
|
|
|
|
Hi,
Have the following case statement:
select
case
when upper(code) = 'TWASH' then (@t_wash = count(ISNULL(code,0)))
when upper(s5.code) = 'TT-WASH' then (@tt_wash = count(ISNULL(s5.code,0)))
end
from table
for some reason it keep coming back with "Incorrect syntax near '='." that would be the first "when" statement. Can we assign variables in case-statement?
Thanks.
|
|
|
|
|
Hi - solved this using another way. Looks like you cannot assign values in then part of statement.
select @t_wash = case when upper(code) = 'TWASH' then count(ISNULL(code,0))
same for other - you get the basic idea.
Thanks.
|
|
|
|
|
hi,
try the following links, hope you will get some idea to solve the problem.
1 [^]
2 [^]
best regards.
_____________________________
Success is not something to wait for, its something to work for.
|
|
|
|
|
I believe you're missing a select or set before the variable:
VK-Cadec wrote: when upper(code) = 'TWASH' then (set @t_wash = count(ISNULL(code,0)))
|
|
|
|
|
I did try that - it did not help.
|
|
|
|
|
Hello,
I'm trying to use NHibernate, but I have several problems.
First of all, I am not sure that my App.config file works. Here it is:
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<configSections>
<section
name="nhibernate"
type="System.Configuration.NameValueSectionHandler, System, Version=1.0.5000.0,Culture=neutral, PublicKeyToken=b77a5c561934e089"
/>
</configSections>
<nhibernate>
<add
key="hibernate.connection.provider"
value="NHibernate.Connection.DriverConnectionProvider"
/>
<add
key="hibernate.dialect"
value="NHibernate.Dialect.MsSql2005Dialect"
/>
<add
key="hibernate.connection.driver_class"
value="NHibernate.Driver.SqlClientDriver"
/>
<add
key="hibernate.connection.connection_string"
value="Server=localhost;initial catalog=pruebaNHibernate;Integrated Security=SSPI"
/>
</nhibernate>
</configuration>
I'm trying to connect to a SQL Server 2005 database, but when I run my application, I get this error message:
Unhandled Exception: NHibernate.MappingException: Could not instantiate dialect
class NHibernate.Dialect.MsSql2005Dialect ---> NHibernate.HibernateException: Co
uld not instantiate dialect class NHibernate.Dialect.MsSql2005Dialect ---> Syste
m.TypeLoadException: Could not load type 'NHibernate.Dialect.MsSql2005Dialect, N
Hibernate, Version=1.0.2.0, Culture=neutral, PublicKeyToken=154fdcb44c4484fc', c
heck that type and assembly names are correct
I think that it is cause the value of the key hibernate.dialect isn´t correct. Does anyone know what I have to put there? I found the dialect for almost any type of db except SQL Server 2005.
Thank you for your help!
|
|
|
|
|
Hi
I am running my database on a shared 3rd party server.
Recently, when I try to connect to the DB from my website, Asp.net gives me the following error:
"SQL Server does not exist or access denied."
[SqlException: SQL Server does not exist or access denied.]
System.Data.SqlClient.ConnectionPool.GetConnection(Boolean& isInTransaction) +474
System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnectionString options, Boolean& isInTransaction) +372
System.Data.SqlClient.SqlConnection.Open() +384
MyCompany.WebModules.Data.DbObject.RunProcedure(String storedProcName, IDataParameter[] parameters, String tableName)
.....
....
For instance, this morning the website was working fine, but now suddenly, I am getting the above error. The same problem happened on other days, as well.
Do you think, there is a problem with my code, or is this SQL Server problem?
|
|
|
|
|
Hi hasanali00
Did you try pinging the server when you got the error message in order to confirm that there were no connectivity issues?
Also place code so that yuo can catch the exception where it is being thrown and output the error message. That way you will be more clear on the part of code is throwing the exception and what the error message is saying.
Thanks and Regards,
Aby
|
|
|
|
|
the problem was that the log file was full:
run this command to delete the log:
dump transaction DATABASE_NAME with no_log
|
|
|
|
|
Hello friends,
I'm developing a .NET application using VS2005 and I want to use NHibernate to implement the persistence.
By the moment, in the hibernate web page, there are only two versions, the 1.0.2 version and the 1.2.0 version, which at this moment is only a beta version.
My question is: Are both versions compatible with .NET 2.0 and VS2005? Wich one should I use?
|
|
|
|
|
How can I back up data from 19 division servers to one main sql server once a day which are connected by VPN?
I read some articles about Replication/Publication function. But I never being use that function and not sure does it work in the way that I want????
Any one can help????
|
|
|
|
|
Hi,
I have a stored proc(below) which assigns tasks to an employee depending on which division they are in.
This is working fine, but is only selecting the first name in the database it comes to.
In order for the workload of task assigning to be spread evenly I need to come up with some way as to "sequentially" select and employee??
ie. each time a do a select I want to pick a different employee from the database until I get to the last employee in which I repeat the process.
I thought of doing a random generator function first but this would probably be unfair as some employees MAY get assigned more tasks than others.
Any ideas??
CREATE PROCEDURE sp_GetAssignedDetails<br />
@roleName nVarChar(50), <br />
@division nVarChar(50),<br />
@actorName nVarChar(50) OUTPUT,<br />
@actorLogon nVarChar(5) OUTPUT<br />
AS<br />
<br />
IF @division = 'North'<br />
BEGIN<br />
--Get ActorName and ActorLogon if division is North<br />
SELECT @ActorName = dbo.Actor.ActorName<br />
FROM dbo.Actor INNER JOIN<br />
dbo.ActorRole ON dbo.Actor.Id = dbo.ActorRole.Id<br />
WHERE dbo.ActorRole.RoleName = @roleName<br />
and dbo.ActorRole.North = '1'<br />
<br />
SELECT @actorLogon = dbo.Actor.ActorLogon<br />
FROM dbo.Actor INNER JOIN<br />
dbo.ActorRole ON dbo.Actor.Id = dbo.ActorRole.Id<br />
WHERE dbo.ActorRole.RoleName = @roleName<br />
and dbo.ActorRole.North = '1'<br />
END<br />
--Get ActorName and ActorLogon if division is South<br />
ELSE IF @division = 'South' <br />
BEGIN<br />
SELECT @actorName = dbo.Actor.ActorName<br />
FROM dbo.Actor INNER JOIN<br />
dbo.ActorRole ON dbo.Actor.Id = dbo.ActorRole.Id<br />
WHERE dbo.ActorRole.RoleName = @roleName<br />
and dbo.ActorRole.South = '1'<br />
<br />
SELECT @actorLogon = dbo.Actor.ActorLogon<br />
FROM dbo.Actor INNER JOIN<br />
dbo.ActorRole ON dbo.Actor.Id = dbo.ActorRole.Id<br />
WHERE dbo.ActorRole.RoleName = @roleName<br />
and dbo.ActorRole.South = '1'<br />
END<br />
--Get ActorName and ActorLogon if division is West<br />
ELSE IF @division = 'West' <br />
BEGIN<br />
SELECT @actorName = dbo.Actor.ActorName<br />
FROM dbo.Actor INNER JOIN<br />
dbo.ActorRole ON dbo.Actor.Id = dbo.ActorRole.Id<br />
WHERE dbo.ActorRole.RoleName = @roleName <br />
and dbo.ActorRole.West = '1'<br />
<br />
SELECT @actorLogon = dbo.Actor.ActorLogon<br />
FROM dbo.Actor INNER JOIN<br />
dbo.ActorRole ON dbo.Actor.Id = dbo.ActorRole.Id<br />
WHERE dbo.ActorRole.RoleName = @roleName <br />
and dbo.ActorRole.West = '1'<br />
END
-- modified at 16:05 Tuesday 31st October, 2006
|
|
|
|
|
I've had an idea (off the top of my head so bear with me  ).
What if I create a temp table.
Then everytime I do a select on the main table I insert the actor to the temp table then everytime I go to assign details I do a check
if actor exists in temp table
get next person from main table
repeat this until we get to the end of the table and do somekind of compare count on both tables to establish we have gone through everyone. Then drop the temp table, re-create it and start it all again?? Clear as mud!!
Anyone know best way to compare the count on the two tables so I can then clear the temp table and start over again.
Or if anyone has a better idea please let me know
|
|
|
|
|
I'm not sure I understand what you are trying to do regarding 'assigning tasks to an employee depending on which division they are in', but this code does the same thing as that which you provided...only it is a bit shorter.
CREATE PROCEDURE sp_GetAssignedDetails
@roleName nVarChar(50),
@division nVarChar(50),
@actorName nVarChar(50) OUTPUT,
@actorLogon nVarChar(5) OUTPUT
AS
SELECT @ActorName = dbo.Actor.ActorName,
@actorLogon = dbo.Actor.ActorLogon
FROM dbo.Actor INNER JOIN
dbo.ActorRole ON dbo.Actor.Id = dbo.ActorRole.Id
WHERE dbo.ActorRole.RoleName = @roleName
and '1' = case when @division = 'North' then dbo.ActorRole.North
when @division = 'South' then dbo.ActorRole.South
when @division = 'West' then dbo.ActorRole.West end
--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
|
|
|
|
|
Thanks for that EricDV.
Basically what I'm trying to do is......
If I have 20 employees in my Actor Table I need to select one that hasn't been selected before each time I go to the Actor Table.
This is required so that a different employee is assigned a task every time I call the sp_GetAssignedDetails from my application.
Once I get to the last employee and assign him/her a task, all employees have now had a task assigned so I then want to go back to employee No.1 and start all over again.
Any ideas?
|
|
|
|
|
You could have a datetime field (LastAssignment) in the Actor table. Then, assign the next task to the oldest LastAssignment Actor, and update their LastAssignment.
--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
|
|
|
|
|
Here is an idea:
- Create a new table PickOrder with 2 columns - division and chosenName.
- Iniitialize PickOrder with 4 records -
('North', 'a1')
('South', 'a1')
('East', 'a1')
('West, 'a1')
Now, in sp_GetAssignedDetails,
1- Get the chosenName value (cn) from the PickOrder table for the requested division.
2- Using your query, select the top 1 record WHERE actorName > cn ... ORDER BY actorName
3- if no record is found in step 2 and there are existing records for that division (i.e., we have exhuasted the list), set the chosenName to 'a1' and repeat step 2 to start with the first name.
4- Update the chosenName column value in PickOrder table with the actorName found in step 2, so that next time we can pick the next name.
-- modified (added ORDER BY clause) at 22:08 Thursday 2nd November, 2006
|
|
|
|
|
Hi
I have to insert new rows from a dataset wich has many tables (8), with the same schema, to an Sql server database. The dataset is filled from an xml file that is quite a bit large.
My question is which is the best solution to insert these new rows from the dataset with a transaction (to be updated all the tables or in case of fail rollback), to the Sql database?
Any ideea are welcome.
biglewy
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
