I am trying to enable AD HOC queries in SQL Server 2000. I have tried running the following code:
Code Block
sp_configure 'show advanced options',1
reconfigure with override
go
sp_configure 'Ad Hoc Distributed Queries',1
reconfigure with override
go
but I get the following error message:
'DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install.
Server: Msg 15123, Level 16, State 1, Procedure sp_configure, Line 78
The configuration option 'Ad Hoc Distributed Queries' does not exist, or it may be an advanced option.
Hi guys I have a program which imports data into sql server from csv files. This program uses the something like this
CREATE PROCEDURE MyImport ( @FileName AS VARCHAR(200) ) AS SET NOCOUNT ON DECLARE @SQL VARCHAR(4000) SET @SQL = ' SELECT * FROM OpenRowset(''MSDASQL'', ''Driver={Microsoft Text Driver (*.txt; *.csv)}; DefaultDir=C:;'', ''SELECT * FROM ' + QUOTENAME(@FileName) + '' + ''') as t ' EXEC (@SQL)
when i excute this i get a permission error saying the account i specified in the web config doesn't have rights to run these type of scripts. I tried using the sa account which works. But its not recommended to setup applications to access the database using the sa account. i did some research and i found out that yon can actually enable that by change a registry key. But i cant find the MSDASQL datasource under ms sql server providers in the registry. Any other ways i can enable this?
Hi guys Apologies if this is the wrong place to be posting this but I have posted on a few forums and have not had any usable answers to this question, so I am hoping you guys will be able to help me. I have 2 servers that are on the same domain. My ASP.NET application is running off the first which has .NET 2.0 installed, and I have an SQL Server 2000 db on the other. Whenever I try to connect I get the following error: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) The connection works when I run the project in VS Development Server on my work computer, but as soon as I upload to the server I get this error. I have scoured Google but all of the sites I come across are for SQL Server 2005 and are just not suitable for solving the issue on 2000. Any help would be greatly appreciated as I cannot continue my current project until this issue is resolved. Hermiod
i am working on upgrading the clients and server computers from 2000 MSDE to 2005 SQL Express Adv. Plan to upgrade using the template.ini.
i can find the version of sql server running on th server by @@version but how do i find the version of client connctivity components the computer has installed in order to ugrade?
1. somehow i need to know whether to upgrade a client computer CONNECTIVITY COMPONENTS from 2000 to 2005?
2. is there a way to enable tcp/ip in installation script of SQL Express?
Hi,Does anyone know if MS SQL Server 2000, will run stability on a WindowsXP based O/S, using one of the new Dual Core chips, or HT chips?Especially given that when you install it you get compatabilitywarnings?ThanksDavid
We recently upgraded our SQL 7 servers to 2000, and we have a gooddeal of distributed queries in the form of Stored Procedures that runbetween them throughout the day. After the upgrade, I began gettingthe "CONNECTION BUSY PROCESSING RESULTS FROM ANOTHER COMMAND" error.This is intermittent but is wreaking havoc on our operations. The samequery ran fine on the 7 server setup. I have exhausted all optionsthat I know of. All of the postings I have seen on these boardsrelate to recordsets and ADO and having cursorlocations setincorrectly, our stored procedure simply selects a count(*) from atable on a remote server. Doesn't seem to make sense. Anyone seenthis before or know a remedy? Thanks in advance.Dave
We have recently upgraded our production server from a dual pentium II 400mhz server with 384Mb of RAM to a triple 700mhz Pentium III system with 2gb of RAM . However, since switching over, all of our Access queries on the SQL7 databases are either running extremely slowly or not at all despite the DSN's being set up correctly . Does anyone have any ideas why, and more importantly, how I can resolve this???
Thanks
Pete Burton (IT Support) Durham Aged Mineworkers Homes Association
I am not very good in queries. Could you please suggest me some web site/Tutorial/Artical where i can get Study Material for complex and real life queries. I know the syntexes, I just need to practice queries to enhance my skills
I changed from Access97 to AccessXP and I have immense performanceproblems.Details:- Access XP MDB with Jet 4.0 ( no ADP-Project )- Linked Tables to SQL-Server 2000 over ODBCI used the SQL Profile to watch the T-SQL-Command which Access ( whocreates the commands?) creates and noticed:1) some Jet-SQL commands with JOINS and Where-Statements aretranslated very well, using sp_prepexe and sp_execute, including thesimilar SQL-Statement as in JET.2) other Jet-SQL commands with JOINS and Where-Statements aretranslated very bad, because the Join wasn´t sent as a join, Accesscollects the data of the individual tables seperately.Access sends much to much data over the network, it is a disaster!3) in Access97 the same command was interpreted wellCould it be possible the Access uses a wrong protocol-stack, perhapsJet to OLEDB, OLEDB to ODBC, ODBC to SQL-Server orJet to ODBC, ODBC to OLEDB and OLEDB to SQL-Server instead ofJet to ODBC and ODBC direct to SQL-ServerDoes anyone knows anything about:- Command-Interpreter of JetODBC, Parameters, how to influence thecommand-interpreter- Protocol-Stack of a Jet4.0 / ODBC / SQL-Server applicationThanks , Andreas
I have been trying to export an sql2000 database to sql2005 with no luck. I continuosly get the error
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
I have looked upon all support material and have installed the latest sql2005 service pack, but this has not fixed the problem. a simple query as:
select * from [transaction] ta,transactionentry tr
where tr.transactionnumber=ta.transactionnumber and
glacctid=6 and ta.[time]> '3/01/2006'
would produce this fatal error.
the current version I am using is:
Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86) Oct 14 2005 00:33:37 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
This problem is happening on some of the most important tables in the database.
I need to pull data using input from one table in sql server 2005. I have to query against the sql server 2000 database and pull data into sql server 2005. I have a list of ids that I have to pass to a query to get the desired data. What is the best practice for this. Can I use SSIS or do I need to build an app in C#? Can somebody please reply back?
I have a SQL 2005 database and am running the 2.0 framework. I cannot seem to enable SQL caching on the db. I'm using the command line tool as follows: C:WINDOWSMicrosoft.NETFrameworkv2.0.50727>aspnet_regsql -S 99.99.999.999 -Uwebteam -P sa -t tblname -d dbname -et Enabling the table for SQL cache dependency. .An error has happened. Details of the exception:The database 'dbname' is not enabled for SQL cache notification. To enable a database for SQL cache notification, please use the System.Web.Caching.SqlCacheDependencyAdmin.EnableNotifications method, or the command line toolaspnet_regsql. To use the tool, please run 'aspnet_regsql.exe -?' for more information. C:WINDOWSMicrosoft.NETFrameworkv2.0.50727>
I've replaced the ip address, dbname and tblname with ficticious names for obvious reasons here in the post. What command can I enter to enable the database for caching?
I have two databases. one is bayan and other is clearance. clearance was already there. i created new bayan. both database having same things. such table ,views. both contains arabic and english records.
when i try to fetch records from clearance table as arabic it is working the query is like: select * from customers where cust_name_araibc='here arabic text'
it is working fine
but same query i try with bayan databses it runs but it doesnt display any records.
So is there anything like to enable languages in sql server databses.
All, I am using SSIS in sqlserver. I wanted to enable logging in SSIS. I just want to log all the activities in the log file. How can i enable logging in SSIS? I would appreicate if any one could answer for this. thanks in Advance.
Hi!I know that you can enable/disable a trigger through the altertable command. Once a trigger is disabled, is there a wayto determine whether it is disabled or enabled? I see there is astatus column in sysobjects ...is that what I should be using?thanks in advance for any helpKaren
I understand that SQL2005x64 bits ignore AWE enable in SQL because it can address all the memory without having to use AWE. How about 32 bits SQL2005, does it support AWE enabled??
I was testing my error handling and purposefully failed some messages. Automatic posion message detection kicked in and disabled my queue. I tried the following, one at a time to enable it again but it doesn't work:
ALTER QUEUE MigrationQueue WITH STATUS = ON;
ALTER QUEUE MigrationQueue WITH STATUS = ON, ACTIVATION (STATUS = ON);
I would have thought the first line would've worked but I get the following when trying to receive...
The service queue "MigrationQueue" is currently disabled.
I have a problem on creating a report using Microsoft SQL Server Business Intelligence Development Studio. I am new to this environment, anybody here can help me on how to enable the "DrillDown" on the table?
When I am using the wizard, the option to enable the drill down will appear, but when I am using "Report Server Project", I really don't know how to enable it, and theres was no table properties on how to enabled it.
Hi, I am using DMO + VBScript to enable the xp_cmdshell feature in SQL Server 2005 Express. But I get error "Item cannot be found in the collection corrosponding to the requested name or ordinal". Here is the script which I am using:
Dim excmd: Set excmd = CreateObject("ADODB.Command") Set excmd.ActiveConnection = connx ' This was created earlier using sa login and connected to master database
excmd.CommandType = 4 excmd.CommandText = "sys.sp_configure" excmd.Parameters.Refresh excmd.Parameters(1).Value = "show advanced options" excmd.Parameters(2).Value = 1 excmd.Parameters(3).Value = Null excmd.Execute () ' This line failes If (Err.Number <> 0) Then LogText "Unable to execute stored procedure. " & Err.Number & " " & Err.Description Exit Sub End If
excmd.CommandText = "RECONFIGURE WITH OVERRIDE" excmd.Parameters.Refresh excmd.Execute () If (Err.Number <> 0) Then LogText "Unable to Refresh. " & Err.Number & " " & Err.Description Exit Sub End If
If (Err.Number <> 0) Then LogText "Unable to execute sp xp_cmdshell. " & Err.Number & " " & Err.Description Exit Sub End If
excmd.CommandText = "RECONFIGURE" excmd.Parameters.Refresh excmd.Execute () If (Err.Number <> 0) Then LogText "Unable to Refresh after running sp. " & Err.Number & " " & Err.Description Exit Sub End If
I have a date parameter that users can not choose because it is grayed out. The Prompt User property is checked in Report Manager. What else do I need to do to enable this functionality. I'm using SSRS 2005
I have a number of SqlDataSource objects in my application, which don't have Optimistic Concurrency option enabled. The SDS objects use custom Sql statements so I can no longer select the Advance button to enable Concurrent Concurrency. How can I enable this option? Is there a designt ime property, and even a run time property that can be set? The only method we have so far is to create a new SDS, with Optimistic Concurrency switched on, then copy and paste my custom Sql into it and rebind my components.. Any help on this matter is appreciated. Regards, Steven
I have to admit I'm pretty new to ASP.net. I'm trying to insert a row of data into my SQLExpress database table "Prets" I'm getting the user_ID from the table "Membres" Here's the code I have: Imports System.Web.UI.PageImports System.Security.PrincipalImports WiseNetTableAdaptersPartial Class membre_sec_Pret_demande_pret Inherits System.Web.UI.Page Protected Sub submit_btn_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles submit_btn.Click Dim User As String = My.User.Name Dim pretAdapter As New PretsTableAdapter Dim membreAdapter As New MembresTableAdapter pretAdapter.AjouterPret(Convert.ToInt32(membreAdapter.GetUserIDbyName(User)), Convert.ToDecimal(montant_txt.Text), Convert.ToString(raison_txt.Text)) End SubEnd Class And here's the error I get at runtime: Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints. 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.ConstraintException: Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.Source Error:
Line 1610: } Line 1611: WiseNet.MembresDataTable dataTable = new WiseNet.MembresDataTable(); Line 1612: this.Adapter.Fill(dataTable); Line 1613: return dataTable; Line 1614: }Source File: c:WINDOWSMicrosoft.NETFrameworkv2.0.50727Temporary ASP.NET Fileswisenetindustriesafc388c716067f12App_Code.tyietv41.0.cs Line: 1612 Stack Trace:
Hello there, I have a table under SQL2005 express with 8 fields with a primaryKey defined.In my BLL in the table adapter there is one "SELECT DISTINCT prodCAT from prods" The prodCAT is a nvarchar(50) and it is not part of an index or primaryKey.When I preview the data from my BLL I get the correct data.I defined a new web form (aspx) and put a ddl (dropDownList) with an objectdataSource that relates to the above sql function/command and I get a"Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints." BTW when I defined the above SQL in the BLL I got a "The new command text makes the function return data with schema different from the schema of the main query" Any suggestions???
Hi. I right click the (root) Solution Explorer to add an SQL object (something.mdf) but I get an error message that "Generating user instances in SQL Server is disabled. Use sp_configure 'user_instances enabled' " WHERE DO I WRITE THE SP_CONFIGURE CODE IN VISUAL WEB DEVELOPER ? (I don't see anything like new query of server and the one in the SQL SERVER management studio is already done). 10x
Hi.,please help.,i get this error.,i made my web and deploy it in a different system which is winserver03, we have in the same network.how will i enable SQL remote access: 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: 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:
does anyone had any idea on the following codes that i'm facing?i have no ideas what is constraints and i hardly can view my page. In addition to that, i also faced the same problems when i'm trying to change my user password. it eventually managed to change the password but then the changes leads to the failed to enable constraints too. however, the following codes is not related to the change password but the purchase item. Hope someone can help me up! thanks!
Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints. 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.ConstraintException: Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.
Source Error:
Line 223: SqlDataAdapter2.SelectCommand.ExecuteNonQuery() Line 224: SqlConnection1.Close() Line 225: SqlDataAdapter2.Fill(BuyData2) Line 226: i = BuyData2.BuyView.Count Line 227: For n = 0 To i - 1
