What the difference is, if any between the following two ways of creating a table with a column that has a default value (see the 'Deleted' column):
CREATE TABLE [dbo].[Customer] (
[ID] INT IDENTITY(1,1) NOT NULL,
[Name] NVARCHAR(100) NOT NULL,
[DELETED] BIT NOT NULL DEFAULT 0)And
CREATE TABLE [dbo].[Customer] (
[ID] INT IDENTITY(1,1) NOT NULL,
[Name] NVARCHAR(100) NOT NULL,
[DELETED] BIT CONSTRAINT [DF_Customer_Deleted] DEFAULT((0)) NOT NULL)
And is there any argument for doing one over the other?
I have a function that accepts a date parameter and uses getdate() as its default value. If a date is passed in, I'm going to have to find records using the datediff method based on input. If no date is passed, I am going to bypass the datediff logic and search for records based on a column called "is_current" which will reduce the query time.
However, I don't know how to tell if the date value in the function came from an input or was the default.
I have got a stored procedure with a parameter on a boolean field. When the parameter is passed down I must retrieve records according to the boolean value. That is if it is true retrieving records with the true in that field, if it is false retrieving records with false. And if no parameters is passed down just retrieve all records either with true or false. I have done something similar with integer fields and it works but in that case I wasn't able to make it working.
See at the following sample I am expecting when executing the 3rd time my below to return 4 and it returns 0
CREATE TABLE #temp ( Id int, Name char(30), YesNo bit ) INSERT INTO #temp (Id, Name,YesNo) Select 1, 'a', 0 INSERT INTO #temp (Id, Name,YesNo)
I want to set the default parameters for a function. I;d like to set the date start date to current date and end date for the last 90 days. how to make this work?
Create Function HR.Equipment ( @startdate Date =(Convert(Date,DATEADD(DAY,-1,GETDATE())), @enddate Date = (Convert(Date,@StartDate-90) ) RETURNS TABLE AS RETURN ( SELECT EquipID, EmpName, IssueDate FROM HR.Equipment WHERE IssueDate <=@StartDate and IssueDate >=@EndDate ) GO
What are the tables are having the Default Constraint those table's Script the User can't generate but remaining tables He/She can generate,If i want DENY he/she to do not generate the script those are not having the DF Constraints what should i do.....
I have a Nullable columns. I want the value to be zero if NULL is passed.
1) having a view for every such table, 2) using ISNULL for needed columns in that view 3) using those view for inserts instead of using tables directly.
This way i can reduce the number of places to use ISNULL in all my SPs.I dont want to make it to NOT NULL, as my application impact will be high. And Default constraint does not work here.
Microsoft SQL Server 2012 - 11.0.2100.60 (X64) Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
we are using the default of newsequentialID() as the key on some tables and getting the error Error validating the default for column xxx
When first saving the table if the value is entered as newsequentialid() the save works but on any subsequent design change I get the error.
If you click Yes to continue it works fine.
The value that is in there once its saved shows up as (newsequentialid()) if you change it back to not have the outer parenthesis it saves without issue.
CREATE TABLE EDI_data_proc_log( ID int IDENTITY(1,1), comment VARCHAR(3000), time_recorded DATETIME DEFAULT GETDATE(), run_by varchar(100), duration int );
When a record is inserted I like the duration column to be computed.This should happen only after the first record to the table has gotten inserted.You might say a trigger would be the best.. Ok then, show me the syntax.
Or I am thinking can we write a user defined function that will compute the value for the duration column.
--By default, I would like to update the duration column as follows:
--It should record the number of seconds between the last insertion ( You can get that time from the time_recorded column from the previous record and the current time can be obtained from the getdate() function )
i have installed windows 2008 evaluation, during installation i was not asked for username and pssword but when i try and start up its asking for administrator password.
is there a way to change SSMS 2012 default query save location?
Scenario:
1. Open SSMS 2. Create new query 3. Click Save
I see "DocumentsSQL Server Management Studio" folder, but I want to change it to be "d:". How do I do this? I tried:
1. [URL]- in folder "DocumentsSQL Server Management StudioSettingsSQL Server Management Studio" there is a file NewSettings.vssettings, setting it to "d:" or "d:" didn't work.
2. Changing HKEY_CURRENT_USERSoftwareMicrosoftMicrosoft SQL Server100ToolsShell VisualStudioProjectsLocation didn't work too. There is no "Shell" under "110Tools"
Did I do something wrong, or is there another way?
There was a 2008 SSRS service running on server "abc".
I have a .Net UI that uses the wsdl at [URL] ....
They deployed SSRS 2012 service on a new server "xyz".
DNS points abc to xyz. Nslookup looks right shows both xyz and abc alias to the same IP.
when I try to go to [URL] .... it redirects to xyz as expected for the host name but the HTTP GET /ReportServer is appended with GET /Reports/ReportServer which doesn't exist and I get an 404 error.
I have no admin rights to see anything and I have no idea if it was an inplace upgrade or a new server. I assume it's new since the actual server name changed in nslookup. Why would it append the default virtual directory when it makes the hop to server "xyz"?
I have a multi value parameter called "Location" and this depends on another multi valued parameter value. The default value for the parameter "Location" comes from the another another multi valued parameter. Now say when the default value is set for the parameter Location like the below:
The Location parameter data set has values from the Query and default values has been set as shown below:
I designed the AlwaysOn wrong, but every time we fail over from primary server to another server, my applications cannot connect because the sql logins cannot connect to their default databases. Once I run the command to link the login with the user in the default database then the users are able to connect. Did I do something wrong when designing AlwaysOn?
My site works fine in VWD2008 express, but I get this error when I try to use it on my live website. 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. According to this article: http://support.microsoft.com/kb/914277 I am supposed to:
1. Click Start, point to Programs, point to Microsoft SQL Server 2005, point to Configuration Tools, and then click SQL Server Surface Area Configuration. Ok, there is no such program in this folder. The only thing in there is "SQL Server Error and Usage Reporting"... The other thing I am greatly concerned with is this: All is want is for my webpages to be able to access my database for user authentication. I DO NOT want to grant the internet rights to remote connect to my database.
I get the following error and have been trying to figure out why I keep getting it. Initially, I had placed my project under wwwroot folder and ran it under IIS and it gave this error. Then I moved it to my local C drive and same thing. I am sharing this project with two other co-workers and all our config files and code files are same...they don't get this error but I do. I checked that SQL Server Client Network Utility has TCP/IP and the 'Named Pipes' enabled. I thought maybe I have setting in the Visual Studio 2005 that I'm not aware of that's causing this problem...it can't be the server since my co-workers aren't having this error and can't be anything in the code since all of us are sharing this project through vss. I dont' think using different version of .net framework can create this error. I changed the version from 2.0 to use 1.1x and it gave same error... Any help would be greatly appreciated. Thanks in advance.
Server Error in '/RBOdev' Application.
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: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: 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: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below. Stack Trace:
[SqlException (0x80131904): 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: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)] System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +739123 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188 System.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject) +685966 System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject) +109 System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart) +383 System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) +181 System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) +170 System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) +130 System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) +28 System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) +424 System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) +66 System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) +496 System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +82 System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +105 System.Data.SqlClient.SqlConnection.Open() +111 System.Web.DataAccess.SqlConnectionHolder.Open(HttpContext context, Boolean revertImpersonate) +84 System.Web.DataAccess.SqlConnectionHelper.GetConnection(String connectionString, Boolean revertImpersonation) +197 System.Web.UI.WebControls.WebParts.SqlPersonalizationProvider.GetConnectionHolder() +16 System.Web.UI.WebControls.WebParts.SqlPersonalizationProvider.LoadPersonalizationBlobs(WebPartManager webPartManager, String path, String userName, Byte[]& sharedDataBlob, Byte[]& userDataBlob) +195 System.Web.UI.WebControls.WebParts.PersonalizationProvider.LoadPersonalizationState(WebPartManager webPartManager, Boolean ignoreCurrentUser) +95 System.Web.UI.WebControls.WebParts.WebPartPersonalization.Load() +105 System.Web.UI.WebControls.WebParts.WebPartManager.OnInit(EventArgs e) +497 System.Web.UI.Control.InitRecursive(Control namingContainer) +321 System.Web.UI.Control.InitRecursive(Control namingContainer) +198 System.Web.UI.Control.InitRecursive(Control namingContainer) +198 System.Web.UI.Control.InitRecursive(Control namingContainer) +198 System.Web.UI.Control.InitRecursive(Control namingContainer) +198 System.Web.UI.Control.InitRecursive(Control namingContainer) +198 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +692
Version Information: Microsoft .NET Framework Version:2.0.50727.832; ASP.NET Version:2.0.50727.832
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,
Hi,I have SQL Server Express Edition. I tried working out some ASP.NET Labs in my local system. Here is the link of the Virtual Lab which I tried. http://msevents.microsoft.com/CUI/WebCastEventDetails.aspx?EventID=1032286906&EventCategory=3&culture=en-US&CountryCode=USI recieve this error in my local system. 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 tried working out solutions from various websites. But the no solution is effective. Could anyone help me in solving this issue.
I'm about to go absolutely nuts. I've tried so many things including:
1) Of course making sure Remote connections and both TCP/IP and named pipes are enabled for SQL 2005 2) Verified my connection string works. I even tested it in Enterprise Library using the tool and it connects successfully via windows authentication 3) Made sure of course my logged in user is a member of the administrator group and that my user in SQL 2005 logins has db_owner and full rights to the DB I am connecting to. I also made sure the NT AUTHORITY as well as the 3 sql service users have db_owner rights as well as the adminstrator in the SQL Server logins all have db_owner to the DB 4) Started the SQL Server Browser service, SQL Server DB engine, and SQL Server Agent 5) Made sure my site is setup right in IIS 7. I checked the connection string in there as well as it shows up in IIS7 in one of the sections. Using Windows authentication and it is enabled for my site 6) Tried running the .net site using both localhost (IIS) and also tried using the Visual Studio built-in web server. Both give this message still
Error Details:
Server Error in '/' Application.
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) Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: 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)
Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below. Stack Trace:
[SqlException (0x80131904): 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)] System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +800131 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +186 System.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject) +737554 System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject) +114 System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart) +381 System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) +181 System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) +173 System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) +357 System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) +30 System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) +424 System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) +66 System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) +494 System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +82 System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +105 System.Data.SqlClient.SqlConnection.Open() +111 System.Web.SessionState.SqlStateConnection..ctor(SqlPartitionInfo sqlPartitionInfo) +79
When my default.aspx page loads I get this error for the connection to my SQL 2005 DB. 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 already gone to Surface Area Configuration to ensure that Remoting with both Named Pipes or TCP/IP is enabled and it's set to both for remoting.I verified that my local account has access to the DB.Not sure what else to check.
We have replication set up. Publisher and distributor are on the same server, subscriber is in different server.
Suddenly the replication started failing and continued more than 12 hrs. When I tried to connect to the subscriber server, I am able to connect to the server but not able to run any queries or not able to open the error log from SSMS.
Is there any way to find from the default trace why the replication failed and the server is not responded?
Using EM, one can click on the server (left pane) and then select propertities from the dropdown.
From the popup you can click on the Database Settings tab.
There is a frame within the Database Settings tab which allows you to enter the default location for a new database. In the same frame you can specify a location for the new database's log file.
Hi, Does anyone have a workaround or know of a fix to this problem: Default value set to 'date pick' from date currently within field by setting value equal to that field . ie if date is 01/01/2010 date picker opens in Jan 2010 - works ok. However, once published to Sharepoint and run through browser the Date Picker ignores the default value and the date picker opens for today. ie April 2008.
I installed SQL Server 2005 Developer edition on my machine a couple months ago, and now I can't remember how to log in. I don't remember setting up any log in accounts, and whatever I might have set I apparently didn't write down. Are there default user names and passwords that I could use to get in? I'm a SQL Server newbie.
