|
|
basically there is no advantages of having sub queries...
|
|
|
|
|
I have made an SQL Server 2005 instance (SERVER2005) be a linked server in an MSDE 2000 instance (MSDE2000). Everything is fine with the setup and I can run queries on tables in a database in Server 2005 instance from the MSDE 2000 instance as follows:
SELECT * FROM [SERVER2005].[ContactsDB].[dbo].[Contacts]
This is how I added the linked server to MSDE2000 instance:
USE master;
GO
EXEC sp_addlinkedserver 'SERVER2005', N'SQL Server'
GO
The problem is that if I execute a stored procedure as follows:
DECLARE @ContactID INT
DECLARE @ContactName NVARCHAR(36)
SET @ContactID = 1
EXEC [SERVER2005].[ContactsDB].[dbo].[dalsp_GetContactName] @ContactID, @ContactName OUTPUT
the execution of the stored procedure is not error-free and the following error message accompanies the results: 'A severe error occurred on the current command. The results, if any, should be discarded.'
If I link two SQL Server 2005 instances the execution is smooth. What can the problem be? Is it the provider? Why does executing a SELECT statement not bring and error yet executing a stored procedure bring an error?
|
|
|
|
|
Did some research myself and found the cause. Thought I should share it with you.
In the event that you add an SQL Server 2005 instance as a linked server to an MSDE 2000 instance, any stored procedure in SQL Server 2005 instance you choose to execute from the MSDE 2000 instance should not have a PRINT statement.
This applies to even stored procedures called by the stored procedure you are calling directly.
If you have statements like:
PRINT N’Hello’
, then error, "A severe error occurred on the current command. The results, if any, should be discarded" will be returned.
Have a nice day.
|
|
|
|
|
I have got three tables...Departments, Branch, and Employees
my table branch is having empid field only..but i have to get empname from employees table
I wanna write a stored procedure to get brid, branchname and empname..?
Branch table and Employees table have empid as common field...!!
Please anyone help me in writing a sql query to overcome this....
Thanks in advance
SAJAN A PILLAI
C#.NET Programmer
TELESOFT INDIA PVT LTD...
BANGALORE
"Winners don't do different things. They do things differently. ...
|
|
|
|
|
|
hi hope this works on
select b.id,b.branchname,e.empname
from branch b inner join employees e on b.empid=e.id
the above query gives to the branchid,branchname and empname where empid is matching with branch table empid.
get back for any queries.
|
|
|
|
|
I have a application based on an access database. I want to be able to distribute the data in the database on one unit and drop it into another unit installed on another machine. I figured i could export the database to xml and then import the file on the other machine? If this is what to do how do i do it? Any other ideas appreciated
mike
|
|
|
|
|
Hi, I'm programming an application using a typed DataSet and a Sql database. I was wondering if there was a way of programmatically creating a database on the server based on the DataSet.
What I want to do is during the software installation, the user enters the connection parameters of their Sql server and then the installation program automatically creates the schema/database the software needs on their database server. I thought the simplest way would be to create it from the DataSet. Is there any better way?
Thnaks a lot!
|
|
|
|
|
You can't do it from a DataSet. The only way you can do this is one of two ways.
One, execute an SQL Script to create the database, setup tables and permissions, and any custom functions, stored procedures, initial data, ...
Two, execute the same script commands from a custom action in your installer.
|
|
|
|
|
in the database i want to store images for each employee ,to do this in sqk server 2005 there is a data type called image which is defined under binary ,How ever i want to know the way to store an image in the data base and retreive it from the data base ...
|
|
|
|
|
Convert the images to bytes and send the bytes to your database!
Steps
Create a stream object
Assign your image to the stream
Copy the contents of the stream to byte array
Send the byte array to the database
gauthee
|
|
|
|
|
thank you for your kind reply ,could you please explian it bit more , i'm using c# and do i have to do this from the application or when inserting valued into the table in sql server .
|
|
|
|
|
Convert the data into bytes in your application and then pass the byte stream across to SQL Server.
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
|
hi here is a cool link for storing the images in sqlserver database.
this is a combination of sqlserver and .net application
i check it its working fine we need to modify a little bit accoridng to our requirement.
plesase get back for any clarifications.
here is the link.
http://www.aspdotnetcodes.com/Insert_Images_Database.aspx[^]
|
|
|
|
|
Hi,
How to calculate no of sundays in between two dates in sql.
Thanks in advance.
|
|
|
|
|
There is no system defined function for this task!
For this you have to write a userdefined function/stored procedure
For the function/store procedure send the start and end dates as input parameters
Declare a variable with value as 0 for count purposes
check the day of the start day using the datepart function
if weekday is sunday increase the count by 1
Continue the steps till the end date
return your count
gauthee
|
|
|
|
|
-- modified at 4:20 Monday 3rd September, 2007
sorry guys,
the query doesn't work....
Regards
J O H N ![Rose | [Rose]](https://codeproject.global.ssl.fastly.net/script/Forums/Images/rose.gif)
"Even eagles need a push." David McNally
|
|
|
|
|
hi i check it this is not working fine here is the example.
declare @startdate varchar(10)
declare @enddate varchar(10)
set @startdate='08/01/2007'
set @enddate='08/31/2007'
SELECT (DATEDIFF(day, @StartDate, @EndDate)/7) + (SELECT CASE WHEN (DATEDIFF(day, @StartDate, @EndDate)%7) > 0 THEN 1 ELSE 0 END)
This returns me 5 instead of 4.
|
|
|
|
|
hi here is the code for calculating the no of sudays between two given sqldates.
create PROCEDURE pr_displaysundays
-- Add the parameters for the stored procedure here
@fromdate varchar(10),
@todate varchar(10)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements.
SET NOCOUNT ON;
declare @diffDay int
declare @Cnt int
declare @Nos int
select @Diffday=datediff(day,@fromdate,@todate)
set @cnt=0
set @Nos=0
while @cnt<@diffday
begin
if datepart(dw,cast(convert(varchar(10),@fromdate,101) as datetime)+@cnt)=1
begin
set @Nos=@Nos+1
end
set @cnt=@cnt+1
end
print @Nos
END
|
|
|
|
|
I answered this same question back in April. For a psuedo-code solution, check out http://www.codeproject.com/script/comments/forums.asp?msg=1993362&forumid=1725&mode=all&userid=3403440#xx1993362xx[^]
For a SQL solution, see http://www.codeproject.com/script/comments/forums.asp?forumid=1725&mode=all&userid=3403440&select=1993823&df=100&mpp=50&fr=4038#xx1993823xx[^]
Here is some of my personal code I use within Access. I have tested it, and use it within my projects.
CREATE PROCEDURE numCertainDays(date1 DATE, date2 DATE, wkdy INTEGER) AS
SELECT CINT(@date2 - @date1) \ 7
+ IIF( WEEKDAY(@date2) < WEEKDAY(@date1), 1,
IIF( WEEKDAY(@date2) = @wkdy OR WEEKDAY(@date1) = @wkdy, 1, 0 ))
;
To get the number of Sundays, you would call it with wkdy=1.
David
|
|
|
|
|
from amployee table i want to get 10 employees who get the heigest 10 salaries . To do this how can i use the max function , or is there any other way to do this .
|
|
|
|
|
Use the order by on the salaries instead. If you order by descending, then the highest salaries will be first. If you don't care about duplicate salaries this is the way to do it.
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
thank you for the reply, but i want to get the first ten max salaries and don't care about duplication ..
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
