|
If you are expecting one (or no) value why not use a user defined function? The function could return NULL for no value
|
|
|
|
|
Could you give me an example how to use a function in my query? I have never used functions in sql server, only views/stored procedures and triggers. Converting my stored procedure to a function would not be the biggest problem, i think.
|
|
|
|
|
A function is very similar to a stored procedure. If your stored procedure is db1.dbo.SP_Calculate(db1.dbo.table1.ID) the your function could be db1.dbo.udf_Calculate(db1.dbo.table1.ID)
See BOL for a complete explanation of CREATE FUNCTION (scalar).
USE DB1
GO
CREATE FUNCTION dbo.udf_Calculate ( @inID VARCHAR(10))
RETURNS money
AS
DECLARE @myout MONEY;
SELECT @myout = amount from mytable where ID = @inID;
RETURN(@myout);
This is a quick of the cuff example.
Good luck
|
|
|
|
|
Hi,
I already got the solution, thanks to djj55:
I converted the Stored Procedure into a Database Function, now i can use it in the query like this:
SELECT
db1.dbo.table1.ID as ID,
db1.dbo.table1.Field1 as Field1,
db2.dbo.table2.Field2 as Field2,
db1.dbo.Calculate(db1.dbo.table1.ID) As Amount
FROM db1.dbo.table1
INNER JOIN db2.dbo.table2
ON db1.dbo.table1.ID = db2.dbo.table2.ID
|
|
|
|
|
Glad you got it. It took me a while to type my last replay as I am at work.
|
|
|
|
|
This is a SQL Server query (I am using SQL Server 2008).
I have an application which runs jobs. I have a JOB table with columns JOB_ID, JOB_STEP etc. When a job is started, an entry is made in this table. When each step is completed, this entry is updated to reflect the next step in JOB_STEP column.
The application is a multi processing application, ie, there are multiple instances of the same application running on multiple servers. All of them read, insert and update into the same database table. (There is only one instance of the DB).
Now, I have a business requirement that no two jobs should be on step 20 (or step 100) together. Ie, if any one job is already on step 20 or 100, all other jobs should wait till that job step is completed.
I have a stored procedure which the application calls to update the step info. I tried something like
update JOB <br />
set JOB_STEP = @nextStep<br />
where<br />
(<br />
(@nextStep <> '20' AND @nextStep <> '100')<br />
OR<br />
(<br />
(@nextStep = '20' OR @nextStep = '100')<br />
AND<br />
NOT EXISTS<br />
(<br />
select * from LAUNCH_STATISTICS<br />
where <br />
(JOB_STEP = '20' OR JOB_STEP = '100')<br />
and JOB_ID <> @jobId<br />
)<br />
)<br />
)<br />
and JOB_ID = @jobId;
But it didn’t work. If jobs try to change steps one after the other, this works. But if I call this SP from two different processes at the same time, both of them manage to end up with Step 20 (or 100) sometimes.
Please help me in finding out what I am missing.
I tried with different TRANSACTION_ISOLATION levels but it didn't help.
|
|
|
|
|
In short,
Is UPDATE-WHERE-EXISTS able to handle concurrency? If not, then is there any way to get that done?
|
|
|
|
|
I have:
configMap =
new ExeConfigurationFileMap { ExeConfigFilename = "ToolHistoryServer.exe.config" };
myCfg =
ConfigurationManager.OpenMappedExeConfiguration(configMap, ConfigurationUserLevel.None);
dataSource = myCfg.AppSettings.Settings["DataSource"].Value;
cnStr = "Data Source='" + dataSource + "';" +
"Trusted_Connection=true;" +
"Database=ToolHistory";
Config file:
<?xml version="1.0" encoding="utf-8"?>
<configuration>
<appSettings>
<add key="DataSource" value="NIGEL-PC\\SQLEXPRESS" />
</appSettings>
<system.runtime.remoting>
<application>
<channels>
<channel ref="tcp" port="50050" />
</channels>
</application>
</system.runtime.remoting>
</configuration>
I have tried cnStr with and without the single quotes, but neither work. Something is wrong with my syntax.
It compiles and runs, but data access fails. Can't debug because it is on the server side.
|
|
|
|
|
Doesn't that give a connectionstring like below?
Data Source='NIGEL-PC\SQLEXPRESS';
Trusted_Connection = true;
Database = ToolHistory;
What happens when you remove the quotes around the name of the datasource?
I are Troll
|
|
|
|
|
I would have thought so. I have just written test code to return cnStr, and I find that dataSource is NIGELPC\\\\SQLEXPRESS!
Wrote a snippet to remove the extra \\ and now it works.
|
|
|
|
|
Hi,
I'm trying to register SQL Server Express edition engine into Network. I've checked everything, but I'm not able to find it in the servers list. What are the things I should check more?
I check TCP/IP & Named pipes connections,
Enabled Remote connections,
*Restarted the service*
**Restarted the machine**!
But nothing works. May be that's what you call Express edition?
|
|
|
|
|
Was it a default instance?
Should be ServerName\SQLEXPRESS from memory.
I don't speak Idiot - please talk slowly and clearly
'This space for rent'
Driven to the arms of Heineken by the wife
|
|
|
|
|
Yes this is what is shown when I browse for SQL servers in the n/w through Management studio.
It gets displayed in the servers list in the local Mgmt studio. But when I open the studio from another machine, I'm not able to see. And it's vice-versa for the DB server that's hosted in the other machine. I'm not able to see it from my mgmt studio. But it gets displayed on his own mgmgt studio. You get it?
|
|
|
|
|
ConquistadorEx wrote: I'm trying to register SQL Server Express edition engine into Network.
You'd have to explain that a bit. Once installed, all should be fine. Where are you registering it? What application?
ConquistadorEx wrote: What are the things I should check more?
Whether the SQL Browser service is running. Check MSDN[^]. Also, check your firewalls, and try to connect to the database using SQL Management Studio or ISql.exe.
I are Troll
|
|
|
|
|
Please check my reply above. I'm just right clicking on the server instance displayed in the Mgmt Studio then selecting "Register".
|
|
|
|
|
Usually it's just a matter of installing, enabling remote connections if at all required, and done
Is your Windows-account allowed to see the other server? Are you on the same domain?
I are Troll
|
|
|
|
|
i am working on a project and one section of the program is password prohibitied, i am using visual studio 2010 coding in C# and using SQL express database, the project is working perfectly the only i can not seem to figure out is how to create the login form ... ex. you open the project then click on personal information, window pops up asking for username and password, if the information you neter matches the information that is in the database you will be able to continue if the information is wrong a messagebox will show up saying invalid entry
been looking all over the net for the problem im having but not having any luck with this issue
here is what i have so far
<code>
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
namespace EasyStorePro
{
public partial class LoginForm : Form
{
public LoginForm()
{
InitializeComponent();
}
private void btnLogin_Click(object sender, EventArgs e)
{
System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection();
conn.ConnectionString = "Data Source=KADAEN-PC\\SQLEXPRESS;Initial Catalog=EasyStorePro;Persist Security Info=True;User ID=************;Password=**********";
string str = ("SELECT username,password FROM dbo.Login WHERE username='"+ tbUsername + "' and password='"+ mtbPassword +"'");
System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(str, conn);
conn.Open();
DataTable dt = new DataTable();
System.Data.SqlClient.SqlDataAdapter da = new System.Data.SqlClient.SqlDataAdapter(cmd);
da.Fill(dt);
if (dt.Rows.Count > 0)
{
MessageBox.Show("Welcome");
}
else
{
MessageBox.Show("Invalid Entry");
}
conn.Close();
}
}
}
</code>
Thank you for your time
|
|
|
|
|
How this is a database related question?
BTW, you can show the Login form as dialog using Form_Name.ShowDialog().
It'll open up as a popup dialog.
|
|
|
|
|
I figured it was a database question, the problem lies in my program connecting and verifying data entered by the user to the information that is the database, I don’t have any problems bringing up the login form, the only complications I am having is with the username & password being verified with the information in the database and allowing the user to continue to their destination as long as all information entered is correct.
|
|
|
|
|
ok, are you getting any error?
Exactly what problem are you facing?
|
|
|
|
|
when I load up the program and open the login form, I input the username and password no error pops up just the messagebox, that says invalid entry, but the information i entered is the exact same information I put into the database itself. i am thinking my problem lies within the if statement but i could be wrong
if (dt.Rows.Count > 0)
{
MessageBox.Show("Welcome");
}
else
{
MessageBox.Show("Invalid Entry");
}
i should be getting the messagebox that states Welcome, but im not.
|
|
|
|
|
Try using this
string str = "select count(*)from Login where username='"+ tbUsername + "' and password='"+ mtbPassword +"'");
System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(str, conn);
conn.Open();
int rows = cmd.ExecuteScalar();
if (row > 0)
{
MessageBox.Show"Welcome");
}
else
{
MessageBox.Show("Invalid Entry");
}
conn.Close();
|
|
|
|
|
i get the following error when trying to run the program
Error 1 Cannot implicitly convert type 'object' to 'int'. An explicit conversion exists (are you missing a cast?)
|
|
|
|
|
Ofcourse you have to cast it to int, i missed it and you too.
int row = Convert.ToInt32(cmd.ExecuteScalar());
|
|
|
|
|
lol yeah i just converted it then seen your reply, ok the program runs now with no errors
but i still get the Invalid Entry MessageBox when inputting the username and password, i even added a new entry to the database and still get the same thing =/ .. this has had be stumped for the past few days
|
|
|
|