|
Hi all, I am trying to display the results of a left outer join between two tables on my web app but there seems to be problems with my stored procedure. I have tried to put the results of the left outer join into a new table by using the following stored procedure:
USE [Payable]
CREATE PROCEDURE [dbo].[getEmployeeData]
AS
BEGIN
SELECT *
INTO NewTable
FROM Employees
LEFT OUTER JOIN Hourly_Employees
ON Employees.Employee_ID_PK = Hourly_Employees.Hourly_Employee_ID
END
Please point out why it does not work, thanks in advance.
|
|
|
|
|
What results are you expecting? What happen when you run the select statement on SQL Studio? If it were me, I will remove "INTO NewTable" statement, I assume the control on your web app will accept a DataDable or DataSet.
- If Employee_ID_PK and Hourly_Employee_ID are both Employee IDs and have correct DataTypes, then I see nothing wrong with the Stored Procedure. Run it on the SQL Studio and fix any error
I remain joe!
|
|
|
|
|
Assuming SQL Server
Take the select statement out of your proc into SSMS and confirm that your query returns results!
Why are you inserting the results into another table in the database when you want the results in your client.
Remove the INTO NewTable and the results will be returned to your calling command.
Explicitly return the columns you want rather than *.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
ASPnoob wrote: Please point out why it does not work
Because you are putting it into a table instead of returning it.
|
|
|
|
|
Hello All!
I am trying to debug someone else's stored procedure.
I see a RIGHT OUTER JOIN clause with no ON reference.
It goes in this form:
select
*
from
a inner join b on a.id=b.id
inner join c on a.id=c.id
right outer join e
inner join f on e.id = f.id
Strange part it is does not complain of syntax error.
When I try to re-write the same sql, it straight out errors out!
I don't know how the previous developer was able to compile the code!!
Thanks!!!
|
|
|
|
|
No idea, but which database system?
|
|
|
|
|
Assumption is that you are using SQL Server!
I often see this when using the query designer to create a view, I think it means e is not used in the data but is required get to f so the query builder simply does the join in this fashion.
I ALWAYS break this out and create the additional joins through to f, it is more understandable.
Caveat
This may be completely wrong (I never bothered to investigate the issue) and is probably technically incorrect but it is more readable.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Mycroft Holmes wrote: the query designer
Blech, filth.
|
|
|
|
|
Hi,
I have a stored procedure, in I want to get data from two different Servers. For example Server1 is the one in which Stored procedure is going to be executed. But the Server2 is different. And for the convenience of moving the Stored proc in different environments easily I want it to be changed dynamically. Like Server2 name can be different for Dev env, Test env and production env.
Without much changes I want it to be changed easily either by using a variable, parameter or alias name. I know these are the way I can implement that. If somebody can give me a link or a code snippet that would be really helpful.
Please help me, I need it urgent, I am also googling for it. But your support helps me a lot.
Thanks in advance. Sincerely
Thanks & Regards,
Abdul Aleem Mohammad
St Louis MO - USA
|
|
|
|
|
- Using the SQL Server Configuration Manager, create an alias[^] pointing to the correct Server2 for the current environment. The alias name should be the same in all environments.
- In SQL Server Management Studio, create a linked server[^] pointing to the alias you just set up.
- In your stored procedure, load the data from the linked server.
With this approach, your code doesn't need to change. It will always be querying a linked server with a fixed name, which is pointing to an alias with a fixed name. The only thing that changes between environments is where the alias is pointing.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Hi,
I have created the Alias for the server, then I looked for the linked server, it was already created in my Management Studio. But when I am trying to query using the Alias name, I am getting error message saying that the Server doesnt exist.
Can you please help me how to query using Alias name in SQL Server 2008 R2.
I am doing it in the following way.
select distinct xxid FROM CedarsLinkServer.CEDARS.dbo.SomeTestDemographic where recEndDate is null
I am getting the following error.
Msg 7202, Level 11, State 2, Line 1
Could not find server 'CedarsLinkServer' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.
Please help me in resolving it.
Thanks & Regards,
Abdul Aleem Mohammad
St Louis MO - USA
|
|
|
|
|
The linked server needs to point to the alias, and your query needs to reference the linked server by name. Judging by the error message, you don't have a linked server called CedarsLinkServer set up.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Ok I got you where I am missing. I will try with that, if I get any problems I will send you a message. Thanks for the help Richard.
Thanks & Regards,
Abdul Aleem Mohammad
St Louis MO - USA
|
|
|
|
|
As a prezzie here's a stored procedure to connect to and disconnect from a linked server:
CREATE PROC dbo.LinkedServerConnect_SP
(@connect as int)
AS
BEGIN
if @connect = 1
begin
if not exists(select name from sys.servers where name = 'yadayada')
begin
EXEC sp_addlinkedserver 'yadayada', 'MySQL', 'MSDASQL', Null, Null, 'Driver={MySQL ODBC 3.51 Driver};DB=yadayada;SERVER=mysql.yadayada.com;option=512;uid=yadayada;pwd=yadayada'
end
end
if @connect = 0
begin
if exists(select name from sys.servers where name = 'yadayada')
begin
EXEC sys.sp_dropserver @server = 'yadayada', @droplogins = 'droplogins';
end
end
end
GO
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
|
|
|
|
|
Hi,
This is my first attempt creating a SSIS package.
I am trying to imnport an excel spreadsheet into sql server table.
I am running BIDS, sql server 2008.
I specify an excel spreadsheet which is 2010 saved format, meaning a xlsx format. It keeps coming back with missing provider microsoft.ace.oledb.12.0
I have a 64 bit machine laptop from which I am creating this package. Do I still need to install the above?
Thanks!!
|
|
|
|
|
|
|
Chaps, I can't seem to find a definitive answer so maybe you can advise. Here's the scenario.
Let's say I have an update trigger on a table. If I have statement like (rough syntax) update mytable set x = 0 where somecolumn > 293 and it updates, say, 300 records. Would an update trigger occur on every row updated or just once for the batch of updates, as it were?
A long time back something I read/misread gave me the impression that some sql statements call the trigger for each row affected and another only does it once but I'm not sure what's fact and what's fiction.
If there is one thing more dangerous than getting between a bear and her cubs it's getting between my wife and her chocolate.
|
|
|
|
|
The trigger will file once for each statement. You should always write your triggers with the assumption that the inserted and deleted virtual tables will contain multiple rows.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Richard, thanks for that. I need to schedule some time to look into them. I know they're basically simple concepts but it's in the doing that you understand them.
If there is one thing more dangerous than getting between a bear and her cubs it's getting between my wife and her chocolate.
|
|
|
|
|
Triggers spit are a support nightmare, especially for a new developer. If you break a trigger (changing/removing a field) it can be extremely difficult for support to identify the problem (it can be bloody difficult for the silly bastard who wrote the trigger).
We do use the horrible things but only for audit purposes NEVER to implement business logic, that lives in a stored proc that does what it is told when it is told and everyone knows where to find it.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Mycroft Holmes wrote: that lives in a stored proc that does what it is told when it is told and everyone knows where to find it.
I wish our database at work was so obliging and obedient.
In a previous job I remember problems with a trigger when looking for differences in the before and after images. I never worked on a more convuluted POS in my life. It was the kind of trigger that had an unwritten rota as to which mug of the month got as a prize.
If there is one thing more dangerous than getting between a bear and her cubs it's getting between my wife and her chocolate.
|
|
|
|
|
I dictate the standards on our DBs (mostly) so triggers and underscores struggle to survive and are hunted down and eliminated whenever I get really bored. This does tend to piss off the offending Dev who ignored the conventions.
So today they ganged up on me and we will move to MVC instead of WPF for next years crop of apps- bastards!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Richard Deeming wrote: inserted
Richard Deeming wrote: deleted
Brings back old memories. A stupid bugger rejected me in an interview when I could't answer the question "what are magic tables?". I knew these tables and how to use them, it's just that he expected me to know that they're called magic tables (even after a decade, I haven't heard anyone else calling them by that name).
|
|
|
|
|
Well, by the highly scientific method of Google-fighting:
your interviewer was an idiot.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|