I used Enterprise Manager to change the data type of one table column
from one thing to another. When I attempted to save the change, I was
warned that "one or more existing columns have ANSI_PADDING 'off' and
will be re-created with ANSI_PADDING 'on'". I didn't expect to see
this message. I verified that ANSI_PADDING is 'off' at the database
level by running the following statement:
Further, the ANSI padding option on the Connections tab of the SQL
Server Properties window is not selected. If ANSI_PADDING is 'off' at
the database level, why does SQL Server assume I want to re-create a
column with ANSI_PADDING 'on'?
A DTS Package is loading a flat file into a table in SQL 6.5 database. Table against which DTS package is loading has a FK constraint against another table. DTS Package completes successfully, but loads data violating the FK constraint.
Same DTS Package run against the same database on 7.0 fails due to violation of the FK constraint for that table(works the way it is supposed to work). Has anyone faced the same problem? Am I missing someting?
In my ASP.NET app, I'm executing a stored procedure via a SQLCommand the searches a customer database. I believe the default timeout is 90 seconds. I'm curious of what happens to the SQL Server Stored Procedure after timing out from the ASP.NET application. Does it timeout at the same time or do you have to set up a value in SQL Server?
I have a Sybase Adaptive Server Enterprise server which I need to set up as a linked server in SQL Server 2005. The Sybase server is version 12.5.2, and the Sybase ODBC driver version is 4.20.00.67. I have already installed the Sybase client software on the server.
I also created a SystemDSN on the SQL Server to connect to the Sybase server. I tested the connection and it was able to connect.
I ran the following code to create the linked server:
I then ran sp_tables_ex to make sure I could view the tables in the Sybase database. Here is the error message I get:
<code>
OLE DB provider "MSDASQL" for linked server "LinkedServerName" returned message "[DataDirect][ODBC Sybase Wire Protocol driver]Error parsing connect string at offset 13. ".
Msg 7303, Level 16, State 1, Procedure sp_tables_ex, Line 41
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "LinkedServerName".
I have a setup where I need to replicate the database which is actually subscribing from another database. The current setup is all in SQL Server 2000. I need to now setup a Distrbutor on a SQL server 2005 and publish the database using this distributor to another server on SQL server 2000.
Has anybody done this before. If yes what will I need to check. Can you please let me know :-
1) SQL Server 2000 which SP should be installed to support this enviroment.
2) SQL Server 2005 which SP should be installed to support this environment.
I am having a nightmare of a time with my Sql Server 2005 Express. My computer crashed on Friday the 13th - of course. I saved all my files onto another drive and wiped out C: I brought everything back in but I started a new file for my Visual Basic projects. After asking questions I found this was a major no-no So I brought my original folder back in. This solved some of my problems I was having with Visual Studio and Visual Web, but I am continuing to have problems with Sql Server. I will explain these two problem they may be related, I don't know. When I run my Visual Website on a server I get this message:
An attempt to attach an auto-named database for file(then my file name)failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.
And my second problem is when I attempt to run Sql Server Management Studio Express I get this error message:
There is no editor available for (then my file name) .mdf
Make sure the application for file type(.mdf)is installed.
Hello, Iam new at this SQL stuff and want to setup an SQL Server at work and need to know what do i need to use this software. I have a EVAL version of 6.5 and was told you need MSACCES or Visual Basic. Is there anything else that i need to use this? Thanks!!
What is the best way to set up a sql server db that will be accessed via web(asp/ado) and windows (vb/ado) environemnts. I am concerned with security and authentication.
I am working with 3 sql 7.0 servers (Servers A, B, C) and am trying to create a trace via profiler to run against server B.
Every trace that I create keeps pointing to server A even though I have server B defined in the properties of the trace - (when I edit the trace properties it REALLY does show server B). I have server B defined in the screen where you 'Select the SQL Server to run the trace on'. I even used the wizard to run one of the canned traces and it still points to server A. Every trace I set up runs against server A. Can anyone think of what the heck I'm doin' wrong? Is is a configuration setting somewhere?
Does anyone know the best way to set up NT security. It seems a little confusing as to how to set up NT groups and assign permissions (where do the roles come into it?).
There has been a lot of discussion about moving single databases or devices in same server. Can someone suggest the most simplest method to set up a identical server with all the logins, permissions and password etc?
Building a new server with sp_help_revdatabase and dumps/loads is relatively easy but moving the master is confusing to me. I should run the new server and databases for a little testing period (with diffrent server name, of course) and then go live by dropping the old and by adding the new. I remember someone said that this would be as easy as copying master.dat to new machine (when servers are identical), any experience or other suggestions?
:eek: I am having trouble setting my SQL databases up as analysis services databases in SQL Server '05. Does anyone know how to help me???? I have lots of cubes to build, but I can't get the datasources there. Thanks much for any advice. mbunch@bighammer.com
How do I find out what port my server is listening on? I'm trying to connect to my database locally to install Vignette Portal. And no matter what I try, nothing works. I think it is listening on port 1433. But there is no way to be sure. Should something come up if I hit localhost:1433 in my browser? Because, when I do that, nothing comes up.
I am installing my application thru Installshield Professional 5. Howto know whether MS SQL Server is installed or not on the machine onwhich i am installing my application. What is the registry key thruwhich we can find this.
I am soon to be setting up a new server that is to have SQL Server 2005 on it.
The server has 2 x Quad Core Xeon CPUs, 16Gb of RAM and 2x73Gb HDDs in RAID1 configuration - the whole running under Windows Server 2003 x64. (The data will be on an external RAID disk array, so the 73Gb is just for the O/S and utils.)
I was reading that Windows will, by default, create a page file of (RAM size) + 1Mb, though my old-school education says use a page file of 1.5 times the RAM size. Then again, 16Gb is a whole pile of RAM and do I really want a page file of 24Gb when I only have 73Gb of HD space..? Is it worth partitioning the HDD into two or just leave it whole?
I'm looking for guidance from knowledgeable types (that's you chaps) who could offer advice on how best to implement SQL Server on a system of this spec.
I've also thought about assigning different processor affinitie to Analysis Services and the Database Engine - does anyone have any strong thoughts either way on the benefits of this?
I have a database on my computer with a series of tables in it. I have sql server management studio express 2005 handling them. How would I make it so I can access the database remotely, over the internet (a non-local network)
How does SQL Server respond when the NT Server's clock is set to a time that is earlier than the current time? Someone on my team seems to think that this is a problem for SQL Server, but I am not sure.
How do you set column privileges in the SQL Server 7.0’s Enterprise Manager. It was so easy in 6.5’s but now it seems that the only way to do it is through the stored procedure.
Iam not sure why Iam not able to successfully set up sql mail. here are the steps I follow:
1. I Created a user account and password to be used for sql server and sql agent as a service account.( Domain Namesqlservice ) 2. Logged off window 2000 then log in with the same account I created in step one. 3. double clicked mail icon in control panel to set up a mail account by specifying the following: pop :pop.erols.com smtp: smtp.erols.com I choose POP instead of IMAP to set up the mail account 4.tested the account by opening Outlook and was able to send and recieve mail via Outlook.
So far so good I assume that I have already (a)created a service account to be used for sql server and sql agen mail account (b) created a mail account (c)tested the mail. 5. I opened sql server and clicked on the mail in the management console to test the sql mail by typing the same mail account in the text box, then clicking test.... I got an error message the MAPI is not set up and it is not connecting to any profile.
another error I got was Error :xp_mapi profile : failed with mail error 0x80040106 Here are some questions that I am not sure about. Q1. Do I need to set up smtp service in service tab to use same account as sql server/ sql agent. It is using local system account now Q2. the above steps are correct, I guess so, so what is the reason not being able to set up the mail profile to be used by sql server mail
I am trying to set up a linked server in MSSQL to an IBM AS400 using the IBM AS400 OLE DB Provider. The New Linked Server dialog box asks for properties Product Name and Data Source. I would like to know from someone who has successfully set up a linked server to an AS400 what the specific syntax in the properties needs to be. I have reviewed the OLB section on sp_addlinkedserver, and while this is helpful and gives many specific examples, I need an example for an AS400. Thank you to anyone who can help!
Hi, I'm new to the whole MS SQL server thing, have been using Access in past developments via the development platform in Dreamweaver.
Now I'm making the leap to MS SQL and still want to use Dreamweaver to handle some of the development aspects, does anyone know how or what i would need to do to have a direct connection with the server in dreamweaver?
I've setup an ODBC connection with my hosting providers already.
I have an issue with my production server regarding memory usage (Memory Utilization is above 95%.). Memory is : 12 GB and the service that is consuming the majority of memory 88%/10.5GB is sqlserver.exe. So it would appear that MSSQL is not set to restrict the amount of memory it uses ? How much should I set for min and max memory ? the defauld is min memory : 0 and max : 2 TB
What considerations do I need to take into account if trying to enlist a server into a master server when (a) both instances are on different physical servers and the SQL Server and SQL Server Agent services run under different domain accounts?
The reason I ask is when attempting to create a master server through the SSMS wizard, I receive these errors:
Enlist TSX Progress
- Create MSXOperator (Success) * Checking for an existing MSXOperator. * Updating existing MSXOperator. * Successfully updated MSXOperator.
- Make sure the Agent service for 'MYMSXINSTANCE' is running (Error) Messages * An exception occurred in SMO while trying to manage a service. (Microsoft.SqlServer.Smo) For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.3042.00&LinkId=20476 ------------------------------ ADDITIONAL INFORMATION: Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED)) (mscorlib)
- Ensure the agent startup account for 'MYTSXINSTANCE' has rights to login as a target server (Error) * Checking to see if the starup account for 'MYTSXINSTANCE' already exists. Messages * An exception occurred in SMO while trying to manage a service. (Microsoft.SqlServer.Smo) For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.3042.00&LinkId=20476 ------------------------------ ADDITIONAL INFORMATION: Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED)) (mscorlib)
- Enlist 'MYTSXINSTANCE' into 'MYMSXINSTANCE' (Error) * Enlisting target server 'MYTSXINSTANCE' with master server 'MYMSXINSTANCE'. * Using new enlistment method. Messages * MSX enlist failed for JobServer 'MYTSXINSTANCE'. (Microsoft.SqlServer.Smo) For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.3042.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=MSX+enlist+JobServer&LinkId=20476 ------------------------------ ADDITIONAL INFORMATION: An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo) ------------------------------ The enlist operation failed (reason: SQLServerAgent Error: Unable to connect to MSX 'MYMSXINSTANCE'.) (Microsoft SQL Server, Error: 22026) For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3042&EvtSrc=MSSQLServer&EvtID=22026&LinkId=20476
Now, the first two errors are what are puzzling me. I'm certain I can address the third and last error by making the accounts the SQL Server Agent services run as the necessary SQL permissions on each instance.
I've tried making NT AuthoritySYSTEM accounts on both servers, no luck. Any suggestions?
can someone explain or link me to some reading material regarding best practice for setting up a sql server (solomon dynamics server)... we are using windows server 2003 R2 and SQL Server 2005. We have partitioned two drives... one 36GB for solomon Log files and 36GB for solomon Data files... our actual solomon database will be hosted on a huge MSA1000 device. Just wondering if this is the best practice for setting it up
I am running SQL 2005 Standard under Windows 2003 Standard with 8 GB of RAM in one of our 64 bit servers; I noticed min server memory was set for some reason to 128 MB and max server memory to 6114 MB by the previous DBA. We plan to upgrade it to 32 GB. What is the best approach when changing those values ? Should I change those values to the defaults (0 - 2147483647) or create min and max boundaries? . This box is only utilzed by SQL.
Also I know that those changes are reconfigured dynamically so no need to reboot, but would this place any performance hit in the server and should I use Management studio or sp_configure ? (for some reason I don't trust Management studio for these type of changes :))
Hi,I am having a hard time setting up an SQL Server 2000 DeveloperEdition instance for remote clients. The instance is running well andI can connect to it on the local computer. When I try connecting fromanother computer using Enterprise Manager with the server's IP as theinstance name, I always get the usual "Specified SQL Server not found"error. I have used Enterprise Manager many times to connect to localand remote servers (which were set up by someone else obviously).I used the client network utility on the client to make an alias forthis server and have it use the TCP/IP library for port 1433.I understand that SQL Server listens on port 1433 for connections. Ihave a firewall on the server and setup 1433 forwarding. But when Iuse Enterprise Manager to try to register the server, the firewalldoesn't even show a connection attempt on port 1433 (it logs allconnection attempts).When I use telnet to port 1433, the firewall logs the attempt butthere is still no connection, as if the server isn't actuallylistening on 1433 at all. I can't seem to find any help on getting theserver listening, as if it supposed to be easy to set up so I don'tknow what I'm missing. I guess I have the following questions:1) Does anything have to be done on the server side to set it up tolisten for remote client connections?2) How do I set the port on which the server listens in case I want tochange from 1433?3) Does anything have to be set up on the client side other than usingthe client network utility?4) Why does Enterprise Manager not seem to even attempt a connectionon port 1433 when I try to register this server?Any help is appreciated.
