|
Dear Web Owner,
My apologies for repeating the same thread on another section. As i am confused who are the best people can ans this ASP or SQL...
Thanks!!
Error Details...
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
I have checked my conn string, Surface config set to local/remote, created alias aslo........WHAT NEXT PLEASE HELP
Thanks for your time...
Amit
Amit
|
|
|
|
|
I have answered it in the ASP.NET Forum. Pls check it out there.
Regards,
Satips.
|
|
|
|
|
Go to Configuration Manager for SQL Server 2005.
Check if Shared Memory, Named Pipes & TCp/IP for the instance are enabled or not.
Hopefully this should resolve ur error.
Regards
SG (sgg245@yahoo.co.in)
|
|
|
|
|
I'm feeling rather dim here--but how did Hibernate get its name? Why 'Hibernate'?
David Veeneman
www.veeneman.com
|
|
|
|
|
I assume your talking about the data access stuff here?
It's called hibernate because it provides persistance i.e. when your computer goes to sleep/turns off, it'll remember stuff when it wakes up.
|
|
|
|
|
I have a number of triggers in an SQL Server 2000 database and I want to generate scripts for them. Using the mouse to select each and clicking "Script trigger as..." would be a serious pain in the wrist. Are there DDL or similar commands or a utility to do this?
|
|
|
|
|
You can right-click the database in Enterprise Manager, select All Tasks, Generate SQL Script. From there, click Show All. Then select the tables and views that the triggers are attached to and click Add to move them to the 'Objects to be scripted' box. On the Options tab, check Script Triggers.
That will get you the table definitions as well as the triggers. I don't know of a way to just get the triggers.
The contents of stored procedures, triggers etc are stored in the syscomments table.
|
|
|
|
|
I'll take a look at syscomment s, thanks
|
|
|
|
|
Hi,
Can anybody tell what are the mail difference or advantages of sql server 2005 over sql server 2000.
regards
alex.
|
|
|
|
|
|
Have you heard of Google? There only about 1mil returns on the subject...
______________________
stuff + cats = awesome
|
|
|
|
|
I am having issues with the following code ever since I added a subquery to it. The error tells me that more than one value was returned from the subquery. The query should in fact retrieve more than one value in most instances so this is fine. Thing is it stops the query from working and I don't know why. My code is as follows in it's entirety. Thanks for any help
SELECT<br />
IH.InvoiceDate AS HistoryDate,<br />
'Part of Invoice' AS HistoryType,<br />
SI.SiteName AS HistoryBranch,<br />
('Invoice Number - ' + IH.InvoiceNumber + ' - ' + C.CustomerForeName + ' ' + C.CustomerSurname) AS HistoryReference,<br />
(<br />
SELECT TIP.InvoicePhoneIMEI <br />
FROM tblInvoicePhones TIP <br />
INNER JOIN tblInvoiceItems TII <br />
ON TIP.InvoiceItemID = TII.InvoiceItemID <br />
WHERE TII.InvoiceID = IH.InvoiceID<br />
) As HistoryIMEIs,<br />
(ST.StaffForeName + ' ' + ST.StaffSurname) AS HistoryUser<br />
FROM tblInvoiceHeader IH<br />
INNER JOIN tblInvoiceItems II<br />
ON II.InvoiceID = IH.InvoiceID<br />
INNER JOIN tblSites SI<br />
ON IH.SiteID = SI.SiteID<br />
INNER JOIN tblStaff ST<br />
ON IH.StaffID = ST.StaffID<br />
LEFT JOIN tblCustomers C<br />
ON IH.CustomerID = C.CustomerID<br />
WHERE II.StockID = '1750'<br />
AND IH.InvoiceDate >= 'January 1, 2007'<br />
AND IH.InvoiceDate <= 'July 2, 2007'<br />
AND IH.SiteID = '10'<br />
AND IH.InvoiceProForma = 0
|
|
|
|
|
The message relates to the subquery. In this example the subquery can only return one row. I would run the subquery only and see how many rows it is returning. If it returns more than one then you have a problem
Jon
|
|
|
|
|
As i mentioned in my original post the subquery should and does return more than one rows. I want for example the following to be returned.
12/10/2007
Sale
967676689768879, 23987298739872, 39278387298372, 3927328739872973
24/12/2007
Sale
8768676876876876, 87687678876876786, 9876987865765678
Where the large numbers are collected from the subquery. Is this not possible? How else can I go about getting information in this way?
|
|
|
|
|
No, You cant do it that way. The way your SQL is structured the subquery is in effect a single field. you cant just put several values into one field.
I see what you are trying to do but i dont think there is a way to do it with a single SQL statement (although i may be wrong about that).
|
|
|
|
|
Ok so you can't merge the fields in anyway. Does anyone else know?
|
|
|
|
|
Hi
You can write your sub-query in a separate function. (table-valued function). Using this function, you can join with the main query as given below.
Select Col1, Col2, dbo.Fn_SubQuery(param1,param2)
from Table1
Hope this helps.
See the example below
<br />
use northwind<br />
go<br />
create function dbo.Fn_OrdersProductID(@OrderID int)<br />
returns nvarchar(4000)<br />
as <br />
begin<br />
DECLARE @ProductID nvarchar(4000)<br />
set @ProductID = null <br />
SELECT @ProductID = COALESCE(@ProductID + ',','') + cast(ProductID as nvarchar)<br />
FROM [Order Details]<br />
WHERE OrderID = @OrderID<br />
<br />
RETURN @ProductID<br />
<br />
end<br />
<br />
-- run the foll. stmt after executing the above function<br />
-- SELECT OrderID,dbo.Fn_OrdersProductID(OrderID) from Orders
Apply the same for your query
-- modified at 7:08 Tuesday 3rd July, 2007
-- modified at 7:11 Tuesday 3rd July, 2007
Harini
|
|
|
|
|
what is the need of COALESCE
|
|
|
|
|
Harini is right I believe.
Something like:
SELECT DISTINCT
IH.InvoiceDate AS HistoryDate
, 'Part of Invoice' AS HistoryType
, SI.SiteName AS HistoryBranch
, ('Invoice Number - ' + IH.InvoiceNumber + ' - ' + C.CustomerForeName + ' ' + C.CustomerSurname) AS HistoryReference
, getInvoicePhoneIMEIs(IH.InvoiceID) AS HistoryIMEIs
FROM tblInvoiceHeader IH
INNER JOIN tblInvoiceItems II ON II.InvoiceID = IH.InvoiceID
INNER JOIN tblSites SI ON IH.SiteID = SI.SiteID
INNER JOIN tblStaff ST ON IH.StaffID = ST.StaffID
LEFT JOIN tblCustomers C ON IH.CustomerID= C.CustomerID
WHERE II.StockID = '1750'
AND IH.InvoiceDate >= 'January 1, 2007'
AND IH.InvoiceDate <= 'July 2, 2007'
AND IH.SiteID = '10'
AND IH.InvoiceProForma = 0
where getInvoicePhoneIMEIs(IH.InvoiceID) uses a cursor based on your subselect to build a string.
http://www.sqlteam.com/article/using-coalesce-to-build-comma-delimited-string[^]
Be sure to include the DISTINCT keyword or you'll get a result row for every InvoicePhoneIMEI value.
David
|
|
|
|
|
I want to disable the dbo schema in my Database.
I want to disable the windows authontication users
merwa
|
|
|
|
|
Great. Now where is your code for us to review?
______________________
stuff + cats = awesome
|
|
|
|
|
You really cannot disable or rename the dbo schema. You cannot disable Windows Authentication entirely - the options are either Windows Authentication, or Mixed Mode which is both Windows Authentication and SQL Server Authentication.
You can delete the BUILTIN\Administrators login (I think, but I'm not willing to try it - back up your master database first and make sure you have another login in the sysadmin role, or have SQL Server authentication enabled and know your sa password). You can certainly change that login from a GRANT to a DENY, but I think that will have the effect of denying access to anyone in the Administrators group even if they would be GRANTed access through another login.
However, Windows Authentication is generally considered Best Practice as the account details are not stored in the SQL Server database server itself (SQL Server login passwords are stored in master ), and therefore cannot be compromised through SQL Server itself. Also, before SQL Server 2005, SQL Server passwords were passed in the clear - anyone with a network sniffer can see the passwords. SQL Server 2005 Native Client and .NET Framework 2.0 support encrypted connections, IIRC.
If you're not talking about allowing Administrators to have access you will find any other logins under Security\Logins in Enterprise Manager or Management Studio.
|
|
|
|
|
I have a table Tab1 with fields Name and Dt( data type Date/Time).
the col Dt have values
2/2/2007
3/3/2007
5/5/2008
i want to display names with date before 1/1/2008
i wrote the folowing query
"SELECT Name from tab1 where dt < 1/1/2008"
but this returns nothing..
iam using msaccess 2003.
thanks
|
|
|
|
|
I think in Access you have to surround date/time literals with the # sign. Try:
"SELECT Name from tab1 where dt < #1/1/2008#"
|
|
|
|
|