My SQL broker was not enabled. So I ran the query >> ALTER DATABASE OperationsManager SET ENABLE_BROKER
The query has been running for several hours and is still not complete. I let it run for over 24 hours and the broker never gets enabled.
Thanks,
Wesley Jones
I am trying to execute the following query , in Management Studio. But it takes forever. Can someone tell me why is this happening? I am running the query in 'NorthWind' database.The windows account under which I am logged into WinXP (windows authentication is enabled for the SQL Server database) is the database owner for NorthWind database.
alter database NorthWind SET ENABLE_BROKER
I am try to start with SQL BROKER service,
When I lunch from sql Management studio the following query, this don't finish never.
ALTER DATATABLE dbname SET ENABLE_BROKER
Where I am mistaking ?
I've got an issue where a join takes about 3 minutes to run on a development server (2 X 1.4 GHz/ 2GB / Win2K3SP1 / SQL2KSP4 (8.00.2187) standard) and does not finish (even after 10 Hours!) on a monster (8 X ? GHz/ 12GB / Win2K3SP1 / SQL2KSP4 (8.00.2187) clustered Enterprise) data center test machine.
This is a join between a table with 360K rows and another with 5.6 Mil rows. Identical data, schema, indexes, auto-stats, etc. between the two boxes. I see no CXPackets, so the parallel processing issue doesn't seem to apply.
Any ideas what to look for?
I have an issue with Sql Server 2012 and Datastage which extracts the table(just select * from table) and loads into one file without any transformation in between. Source table has around 45million rows. Datastage Process extracts upto 22million rows and then it stalls forever(running state). I had investigated into the Database and i could see the transaction runs on database for some time(runnable and suspended state) for sometime and it disappears suddenly. I am surprised to see Datastage Process still shows rows are being extracted even after transaction is disappears and Datastage process up to 22million rows and then do nothing.
View 3 Replies View Relatedi'm using sqlexpress edition for my application and i'm wondering whether i will be able to execute the below sql statement
use masteralter database <dbname> SET ENABLE_BROKER
when i execute the above two lines its executing from last 30+ minutes...
i'm wondering will that support in sqlexpress edition ?
also, in order to use sqldependency do i have to enable broker service?
thanks.
I want a database user to be able to alter login, database user and database role from my application. so, i assigned that user to sccurityadmin server role, db_accessadmin and db_securityadmin database roles....By now, the user can add or remove login and database user. However, the user cannot add or remove any database role membership. What am I missing here?? What should I do so that the user can create, and alter database roles in the database??
View 1 Replies View RelatedI have a script that automates some db drop/restore operations and bringing the database to single user mode is part of it: ALTER DATABASE ... SET SINGLE_USER WITH ROLLBACK IMMEDIATE...I want this to executes under a login, that has restricted privileges, so I've created a login and granted it a dbcreator role + ALTER ANY DATABASE privileges.
Problem: When I run the script against a database with an active/sleeping connection:It fails when using the restricted login: "Msg 5061...ALTER DATA BASE failed because a lock could not be placed on database ..."It completes successfully when using a sysadmin login According to stackoverflow.com the solution is to kill the active/sleeping connections to the database, before ALTER-ing it, which works fine, but the question is....
Questions: Why the "ALTER DATABASE..." statement works under the sysadmin login, but not under a dbcreator one?Does this mean the sysadmin login kills the connections to the target database in the background?Is it possible to grant additional privileges to the restricted login, so the "ALTER DATABASE..." statement won't need preventive killing of the connections?
Hi,
View 6 Replies View RelatedI have an FTP task that once the file is loaded a script task is activated and then a data flow process starts. I can connect to the FTP server with the Test connection and I can see the file in the remote directory "/HRData.txt" however even with the Delay validation set to true it does not download the file.
View 2 Replies View RelatedHi,
What is the equivalent for below Oracle's ALTER SESSION query :
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:mi:ss'
Thanks,
Sam
Hi, I just want to know to turn this:CREATE TABLE [dbo].[tblTierCs] ([idTierC] [int] NOT NULL ,[txtNoEmploye] [varchar] (50) COLLATE French_CI_AS NULL ,[noSubDomain] [int] NOT NULL ,[txtNameTierC] [varchar] (50) COLLATE French_CI_AS NOT NULL ,[noOldTierC] [int] NULL ,[noRSDTierC] [int] NULL) ON [PRIMARY]into this:CREATE TABLE [dbo].[tblTierCs] ([idTierC] [int] IDENTITY (1, 1) NOT NULL ,[txtNoEmploye] [varchar] (50) COLLATE French_CI_AS NULL ,[noSubDomain] [int] NOT NULL ,[txtNameTierC] [varchar] (50) COLLATE French_CI_AS NOT NULL ,[noOldTierC] [int] NULL ,[noRSDTierC] [int] NULL) ON [PRIMARY]using an ALTER TABLE query. I tried using:ALTER TABLE [dbo].[tblTierCs] ALTER COLUMN [idTierC] [int] IDENTITY(1, 1) NOT NULL but it's not working. Anyone has any idea how I coulddo it? Thanks.
View 2 Replies View RelatedI am using SQL 6.5. Can I create a database and Alter the same
database in one SQL Script?
Thanks in advance.
I have a package that has three control flow items - an Execute SQL task, a Data Flow task and another Execute SQL task - linked in sequence by On Success constraints and contained by a Sequence container. I want any of the three contained tasks that fails to cause the Seuqence container to be rolled back and write a checkpoint.
I've configured the package to use Checkpoints, set the TransactionOption to Required and the FailPackageOnFailure to True ion the Sequence container and set the TransactionOption to Supported and the FailParentOnFailure to True on each of the three contained tasks.
When I attempt to run the package in debug mode, the first Execute SQL task executes fine but validation of the Data Flow task never finishes. If I switch the TransactionOption of the Sequence container to Supported instead of Required, the package executes fine.
I have had problems in the past where MSDTC was not running but that causes an error and I've checked that it's running and it is.
Any suggestions gratefully received...
Hi people,I?m trying to alter a integer field to a decimal(12,4) field in MSACCESS 2K.Example:table : item_nota_fiscal_forn_setor_publicofield : qtd_mercadoria integer NOT NULLALTER TABLE item_nota_fiscal_forn_setor_publicoALTER COLUMN qtd_mercadoria decimal(12,4) NOT NULLBut, It doesn't work. A sintax error rises.I need to change that field in a Visual Basic aplication, dinamically.How can I do it? How can I create a decimal(12,4) field via script in MSACCESS?Thanks,Euler Almeida--Message posted via http://www.sqlmonster.com
View 1 Replies View RelatedI am trying to move one of my database log to different location but I am geeting an error when I try to do this.
I am moving Registration DB log file from 'c:mssql7
egistrstion_log.ldf'
to 'e:sql
egistration_log.ldf'
Here the syntax I am using to do this:
alter database registration modify file (name='registration_log',filename= 'e:sql
egistration_log.ldf')
But I get the following error:
Server: Msg 5037, Level 16, State 1, Line 2
MODIFY FILE failed. Do not specify physical name.
Thank You,
John
Hi all this is my first of most likely many posts.
I am writting a stored proc that will be used on many diffrent named databases and i am setting the database name with a variable. I would like to use this variable in the following situation any clues on this would be greatly appreciated.
DECLARE @DBName varchar(50)
SET @DBName='Database'
alter database @DBName set recovery full
regards
Phil
Hey,
I am trying to give a user permission to change the name of a database.
They are a member of the server role 'secuirtyadmin'
They are a member of the database role 'db_owner'
They have been granted ALTER permission on the database.
But every time I try and run the command
ALTER DATABASE <dbname> MODIFY NAME = <newdbname>
I get an error saying the database doesn't exist or you don't have permissions.
What am I missing?
Hi!
I hope to get some help with a stored procedure that never stops executing when run from Reporting Services development environment.
The server: SQL Server 2005 SP2 x86, 2 gig ram, 2 processor cores.
The stored procedure I execute is simple, it declares and sets some variables and then a executes a select statement
with 6 joins.
If I execute the stored procedure from Management Studio the query runs and returns about 50 000 rows in ~2 seconds. No problems!
The problems arises when I execute the stored procedure from visual studio (testing my reporting services report). The query will never complete its execution!?
I checked the activity monitor and can verify I have NO processses with any information in the blocked by or blocking column.
The strange thing is I got 3 rows for process id 64, only one row (row 1) printing the executing user.
Row 1: Stutus suspenden, Wait Type CXPACKET
Row 2 and 3 are pending between runnable and suspended and wait typoe pageiolatch_ex and no wait type at all when I refresh. These are probably parallell threads executed in the query.
The strange thing here is that this process never completes and if I do a trace the last command executed is select statement. I don´t understand what the problem is, sql server bug? Parellelism problem (in this case, why?)
Any help is appreciated
Regards
Per
Hi all,
I need to change the default member of my Time dimension according to a parameter received by a report in Reporting Services. When I code the MDX query I can't seem to be able to begin my statement with an ALTER CUBE to set the default dimension. It seems I can only do a SELECT..FROM..WHERE query.
Does anyone know how to set the default member of a dimension in a report ??
Many thanks,
John
Hello,
I have Test database with Log file property Automatically grow the file, option Unrestricted file growth, I wanted to alter it with Restrict file growth upto 200 MB. I'm getting an error that you cannot modify as file doesnot exist
But the file does exist. I cannot figure out what is giving the problem.
below is the sql statements
select name from sysfiles
go
Test_Data
Test_Log
USE master
go
ALTER DATABASE Test MODIFY FILE
( NAME='Test_Log',
MAXSIZE=200MB )
go
ERROR
Server: Msg 5041, Level 16, State 1, Line 1
MODIFY FILE failed. File 'Test_Log' does not exist.
Any help is appreciated.
Thanks
Sejal
Hi
I got the following error
Error: 823, Severity: 24, State: 4
I/O error 33(The process cannot access the file because another process has locked a portion of the file.) detected during write at offset
0x0000000a796000 in file xxxxxxxxx.ndf'.
and the respective database could not be brought online - this was just due to a problem with a .ndf file containing only indexes...is there any way to connect to/alter a database while it is in this transitional state? (it would be no loss if i could just remove the file & its filegroup)
(i tried starting with -f -c, but no go)
thanks in advance
des
I have a *.sql script that creates database tables, and I need to modify the database to enable the service broker. In addition, the actual name of the database is not known in advance - it is set per instance of the application.
I know I need to do:
ALTER DATABASE dbname SET ENABLE_BROKER
But I must avoid including the name of the database in the script. I did wonder if this would work:
DECLARE @DB varchar(50)
SELECT @DB = DB_NAME()
ALTER DATABASE @DB SET ENABLE_BROKER
But I just get a syntax error. Presumably this also rules out setting the database name as a parameter to the script (SqlParameter stuff)
The only option I can think of is dynamically creating the statement, either in T-SQL or in the calling .NET environment.
Any thoughts?
Ruth
I've read that when this run's, it removes all db mirroring information on that db. What exactly does it remove?
Here's my senario:
We are using SQL 2005€™s db mirroring process. We are using the certificate method of authentication between the principle and the mirror db€™s.
My question is that when the ALTER DATABASE dbname SET PARTNER OFF is run, does it remove these certificate settings as well? In other words when I want to enable the db mirroring, will I need to recreate these certificates or just recreate the endpoints to use these certificates?
I am getting an 'Access is denied' error:
Error 0x80070005 while loading package file "Package.dtsx". Access is denied. .
The package is executed from the main package via an 'Execute Package Task'. The strange thing is that the error comes after 'Package.dtsx' has run successfully. I am logging the PackageStart/PackageEnd and error events and I see that Package.dtsx ends successfully and then the "Access is denied" error occurs. The main package is launched by executing dtsexec via a SQL Server Agent Job. The packages have 'SaveCheckpoints' set to True. Any ideas are welcome. Thanks.
I was just debugging a stored procedure visual studio and I was surprised with what I was watching.
I'm running SQL 2005 and visual studio 2005. I have a set of nested try/catch blocks.
It fails on the first try and drops into the catch block just as it should. Within the catch block a dynamic sql statement is created. A try block begins and attempts to exec(@sql). It then drops to the catch block. However, I copy the statement into SQL Management Studio and it runs without error everytime.
Why is it dropping into the catch block? Logically it should just complete executing the statement continue without going into the catch block.
Do I have to reset the raiserror values between try blocks or something?
--Thanks--
How can I do an alter table in some table that replicated database ?
I got the error message when I try !
Conrad writes "I'm currently working on Table Partitioning. I have done everything succesfull for partitioning, what I'm struggling with is to use
" SELECT MAX(NAME) from sys.filegroups WHERE NAME NOT LIKE 'PRIMARY' " to get the last used FileGroup. Now this works just fine, but when I run the following script:
--Decalre variables
DECLARE @LastFilegroupName VARCHAR(50)
DECLARE @FilegroupName VARCHAR(50)
--Retuns the next FileGroup to be used
SET @LastFilegroupName = (select MAX(NAME) from sys.filegroups WHERE NAME NOT LIKE 'PRIMARY')
SET @LastFilegroupName = Replace(@LastFilegroupName,'FileGrp','')
SET @FilegroupName = 'FileGrp' + CAST((@LastFilegroupName + 1) as varchar(10))
--Alter database statement
ALTER DATABASE VadivelTesting
ADD FILEGROUP @NewFG_Name
This script gives the following error "Incorrect syntax near '@NewFG_Name'."
When I give it a static name it works fine, but not with the variable.
Please can someone help me, I'm in struggeling with this one."
We're upgrading to SQL Server 2005, and we've restored databases from SQL Server 2000 to the development server (SQL Server 2005). Part of what we need to do is alter the database collation to SQL_Latin1_General_CP1_CI_AS; our SQL Server 2000 server is case-sensitive.
My initial attempts to alter the database;
alter database RGDWDEV collate SQL_Latin1_General_CP1_CI_AS
failed because of the presence of computed columns and user functions. So, I wrote scripts to save the definitions of the computed columns and functions, and wrote scripts to drop them. Now that they are gone, I expected to be able to successfully execute the above 'alter database' statement, but I am now getting this error;
Msg 1505, Level 16, State 1, Line 1
CREATE UNIQUE INDEX terminated because a duplicate key was found for object name 'sys.sysschobjs' and index name 'nc1'. The duplicate key value is (0, 1, DISB_ADJ).
Msg 5072, Level 16, State 1, Line 1
ALTER DATABASE failed. The default collation of database 'RGDWDEV' cannot be set to SQL_Latin1_General_CP1_CI_AS.
I do not know how to fix this.
Can anyone help me?
thank you, beth
I have created a stored procedure in msdb that will accept a database name and then rebuild non-clustered indexes in that database.
I placed the sp in msdb since msdb is where most job-related information is stored.
The sp identifies non-clustered indexes whose avg fragmentation exceeds a pre-determined threshold, and then runs the following statement for each index in the resulting subset:
SET @str_sql = 'ALTER INDEX ' + @str_index + ' ON ' + @str_object + ' REBUILD '
EXEC sp_executesql @str_sql
where @str_index is the name of the index and @str_object is the name of the object (view, table).
Clearly, I need to revise the statement so that it refers to the appropriate database.
Is there a way to execute 'alter index ... rebuild' so that it points to a database other than the current?
Thanks.
If I execute the command ALTER DATASE SET MULTI_USER WITH ROLLBACK IMMEDIATE and there are any connections to the database, the command fails with a "Lock request time out period exceeded." message. If I use SET RESTRICTED_USER, the command succeeds with the following message: "Nonqualified transactions are being rolled back. Estimated rollback completion: 100%." This seems to be a bug.
View 5 Replies View RelatedHi.
I'm very new to DB stuff. I've created a SQL Server CE database and I'm trying to implement a query notification using a SqlDependency object, as per the instructions at:
http://www.codeproject.com/cs/database/chatter.asp
I've connected to my DB using SQL Server Management Studio Express, and I'm getting the following error when trying to execute an ALTER DATABASE command:
I have no clue what I could be doing wrong. Is this operation supported using CE? Is there really something wrong with my syntax?
Major Error 0x80040E14, Minor Error 25501
> alter database GESim.Diagnostics set ENABLE_BROKER
There was an error parsing the query. [ Token line number = 1,Token line offset = 7,Token in error = database ]
Help appreciated...
Just Mike
Hi,
I'm trying to change the file extension of one of the database files of a big database that contains 10 years of EPOS data.
When this file was created, at the beginning of 2008, with the following script:
USE [Live]
ALTER DATABASE [Live] ADD FILE (NAME = 'Live_2008',
FILENAME = €˜E:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDataLive_2008') TO FILEGROUP [2008]
the .ndf file extension was not added to the filename. So the file now has a file type of "File", instead of "SQL Server Secondary Data File"
I tried, in a test environment, to bring the database offline, change the filename to "Live_2008.ndf" and bring the database back online, but it gives me an error message saying that "cannot open the Live_2008 file", i'm guessing because it's looking for "Live_2008" and it finds "Live_2008.ndf" instead.
This file is quite big (5Gb) and I cannot afford to risk to lose any data.
The situation is complicated even further by the fact that it seems that a partition for 2008 does not exist.
How can I find out what data this file contains?
Is it going to be an issue if I leave the file like that?
Thanks