Switching In And Out Of Bulk Logged Mode
Jul 22, 2004
I sometimes have problems when running index rebuilds due to the tran log growing too big. MS suggest switching the db from full recovery mode to bulk-logged mode fur the duration of the rebuild. Question is, when you switch a db from full recovery to bulk logged, does that invalidate the tran log backups up to that point? When the db is switched back into full-recovery mode, are the following tran log backups still in sequence?
Clive
View 2 Replies
ADVERTISEMENT
Jan 9, 2006
Do you know of any resources in terms of Transact SQL in order to migrate from 6.5 to 2000?
View 1 Replies
View Related
Jun 18, 2007
Hi Guys,
I have an Intranet web application and I have to use windows authentication. They are 4 main users group to access the application, each with different set of permission on my store procedures.
I have created a windows group for each of those group in my windows environemnt. My DB Connection String in web.config is set to Integrated Windows Authentication ( Integrated Security=SSPI)
I have done all configuration based on this article: http://msdn2.microsoft.com/en-us/library/ms998292.aspx
I have 4 different database Roles in my database and 4 different database users. I also add NT AUTHORITYNETWORK SERVICE as a member of each of those database Roles.
Now I get connected, fine and set permission on the database roles.
1 thing I want to know, How Can I get the Windows Group name in the database, which has the same name as my SQL Login and Database Roles or User
I tried all SUSER_SNAME() and ... they all return NT AUTHORITYNETWORK SERVICE
I want to get AppPowerUser Role or windows group name. I think I should be able to because I am using windows authentication.
Any help is appreciated.
Mehdi
View 1 Replies
View Related
Jan 18, 2007
Hi,
Can any one please tell me how to get the complete error description for example when i dont Redirect Row for Error in OLEDB Source i get a detailed error message with column name as
[RCheck [385]] Error: There was an error with input column "CHECK_STATUS" (456) on input "OLE DB Destination Input" (398). The column status returned was: "The value could not be converted because of a potential loss of data.".
But when I set Redirect Row for error and use the Script component to log them into a Table with ErrorDescription based on ErrorColumnID it only gives me this.
The data value cannot be converted for reasons other than sign mismatch or data overflow.
Thanks
Sat
View 1 Replies
View Related
Dec 21, 2006
We have a sql 2005 x64 database (datawarehouse related), essentially a work area for us, that we truncate and re-populate via BCP weekly. (We don't backup the database at all) . From the perspective of data-import speed what is the best recovery model to use: Bulk-Logged or Simple? (I have read sql 2005 BOL and don't find it partcularly clear on this point.)
Barkingdog
P.S. Anyone know of an article listing "best practices" for high-speed data import?
View 1 Replies
View Related
Apr 18, 2007
Hi there,I have installed MS SQL Server 2005 on my machine with windows authentication. But now I want to switch the authentication mode to SQL Authentication. I am unable to switch, I can’t find the proper way to do so here in 2005.Could any one help me in doing this?Thank you,-Ahsan
View 1 Replies
View Related
Dec 23, 2014
is bulk logged recovery model support point in time recovery
View 9 Replies
View Related
Jan 18, 2007
Durning install I selected Window's Authentication only, but now it seems we may need to use a Mixed Mode with an SA account etc... is there anyway to switch SQL 2005 to use Mixed Mode after the fact?
View 1 Replies
View Related
Jul 25, 2014
We have reports in SharePoint integrated mode which are really slow when compared to native mode. I have been asked to research and give info on what exactly causes the delays.
Any articles which give me information as to what happens when a report is run from SharePoint server and where does it log.
View 1 Replies
View Related
Mar 9, 2000
Hello,everyone!!
There is a query which when executed in the grid mode(ctrl+d) takes approx 0.02 seconds(about 21,000
rows) But when I execute in the text mode, it takes about 0.40 seconds!!
Why is this difference?
Also, when the records from this table are read from a VB application, they are equally slow (as in the text mode!)
Why is it so slow on the text mode & relatively faster in the grid mode?
Has anyone got any idea on Firehose style cursor ?(which may speed up access of data in the VB application)
Rgds,
Adie
View 1 Replies
View Related
Jul 27, 2015
how to put sql server database in suspect mode intensely and get it out from suspect mode to normal mode.
i am using SQL server 2008 R2
View 5 Replies
View Related
Mar 26, 2005
Recently I read such statments 'When SQL Server is run in "lightweight pooling" mode (fiber mode) and the DTC service is started, unexpected behavior may occur.'
Can someone say anything about fibe mode?I am appreciated for it.:)
View 3 Replies
View Related
Jul 16, 2007
Hi,
Currently, our Report Builder is running on Local Intranet mode. I'm investigating what the security implications are in changing it to Internet mode. However, I've not been able to find any documentation on this.
Does anyone know of any documentation that addresses this issue or have experience that they can share with changing Report Builder security zone from Intranet mode to Internet mode?
Thanks very much.
View 1 Replies
View Related
Apr 1, 2006
At the moment my asp.net app is working ok and I can connect to the database using windows authentication, however i'm trying to use sql authentication on my local computer.i'm using sql server studio at the moment to manage the database. And tried numerious combinations of things to try and get it working and allow me to connect to the database using SQL authentication but still no luck :(can anyone give me some rough step by step instructions to setting up the sql username and password for forms authentication and activating it for a certain database?thanks
View 1 Replies
View Related
Oct 9, 2006
Hi
If I have a connection open which is in database master, can I switch to another database within that connection without having to close the connection and recreating it?
Thanks
Steve
View 1 Replies
View Related
May 13, 2002
Guys!
I'm using SQL Mail to send out Emails to Customers re: outstanding Invoices. I also use SQL Mail on the same box to send out emails to our Sales Reps with summaries of outstanding invoices. Now, I've set up multiple outlook profiles on the server. I tried using
xp_startmail @user = 'CreditControlDept'
xp_sendmail .... (to customers)
xp_stopmail @user = 'CreditControlDept'
xp_startmail @user = 'InvoiceTracker'
xp_sendmail...(to internal sales reps)
xp_stopmail @user = 'InvoiceTracker'
DOES NOT WORK!!! the profile does'nt switch at all. SQL Mail sends out all the emails using the profile that it started with. How can I use xp_startmail and xp_stopmail to switch between different user profiles.
Regards
Uday
View 1 Replies
View Related
Oct 19, 2000
Hi
If I dump a replicated Db and restore it onto another DB, how do I switch off replication from the restored DB. I believe there were some scripts developed for this in 6.5.
Any suggestions will be appreciated.
Rich
View 1 Replies
View Related
Apr 10, 2008
Could anyone help me to find out how to switch signs of "+" and "-" in matrix drilldown?
In columns drilldown i have wright (that means "+" for drilldown and "-" for drillup)
In rows I have it conversely, don't know why and don't where I can change this.
Thank you!
View 2 Replies
View Related
Jan 3, 2006
Hi folks,
I have a SQL 2005 OTC. CTP version running on Windows 2003 server.
I would like to find out how the SQL server option changed to Windows Authentication mode from Mixed mode over the weekend. From the SQL log, I don't see when it changed. I would like to see Date/Time and client IP. If I can see User ID (windows) that would be great. Where I can find these info in SQL server?
Thank you in advance...
SHJ
View 1 Replies
View Related
Aug 6, 2004
We have an active database that sits on SQL Server 2000 that currently has per seat license...but now want to switch to per processor.
If I go to ControlSQL Server 2000 License Set up...it doe not give me the option to choose per processor.
Do I need to un-install/re-install SQL Server 2000 and choose the per processor choice and then re-attach our database ?
It seems like a lot of work..please advise.
Thank you
View 7 Replies
View Related
Jan 28, 2015
I would like to know how I can switch from one database to another within the same script. I have a script that reads the header information from a SQL BAK file and loads the information into a Test database. Once the information is in the temp table (Test database) I run the following script to get the database name.
This part works fine.
INSERT INTO @HeaderInfo EXEC('RESTORE HEADERONLY
FROM DISK = N''I:TESTdatabase.bak''
WITH NOUNLOAD')
DECLARE @databasename varchar(128);
SET @databasename = (SELECT DatabaseName FROM @HeaderInfo);
The problem is when I try to run the following script nothing happens. The new database is never selected and the script is still on the test database.
EXEC ('USE '+ @databasename)
The goal is switch to the new database (USE NewDatabase) so that the other part of my script (DBCC CHECKDB) can run. This script checks the integrity of the database and saves the results to a temp table.
What am I doing wrong?
View 2 Replies
View Related
Jan 5, 2007
Hi
My C# application has a DataGridView which is bound to a DataSet. I used Visual Studio to add/configure the data source. Everything works fine on my computer. When I try to run the application on another computer I can't connect to the database, because it uses a wrong connection string.
How can I make a specific connection/connection string permanent for a specific application?
Thanks.
View 6 Replies
View Related
May 2, 2006
I've got a situation where I need to execute portions of a script against every database on a given instance. I don't know the name of all the databases beforehand so I need to scroll through them all and call the "use" command appropriately.
I need the correct syntax, the following won't work:
DECLARE DBS CURSOR FOR
SELECT dbname
FROM #helpdb
ORDER BY dbname
OPEN DBS
FETCH NEXT
FROM DBS
INTO
@dbname
WHILE @@FETCH_STATUS = 0
BEGIN
USE @dbname
The last line - the "USE" statement - is invalid. The following for example works:
USE master
But when supplied a declared variable a syntax error results for the use command because it expects an identifier.
So .. what is the correct syntax to pass a declared parameter to "USE", or is there another way to meet this requirement?
Thanks for your time.
View 6 Replies
View Related
Nov 6, 2013
i want to upload reports in ssrs , when i trying to configure reporting service configuration manager, getting error" there was an error while switching panels. The most likely cause is an error retrieving wmi properties. the exception details are access is denied . You must have an administrator on the report server computer to use this tool".
View 2 Replies
View Related
Apr 29, 2008
It is simple question, just slipped out of my mind at this time...... how do we change Security Context for 2005 version from network to local system.
thanks,
View 1 Replies
View Related
Jan 26, 2007
Hi,
One of our client application launches a execuatable on server by calling master..xp_cmdshell. We are migrating to SQL 2005 and I would like to use the Context Switching feature
We have sql login client? which is used by clinet application and has very least privelge and have only required access to requried database and tables.
xp_cmdshell is disabled by default on our server and I would like to enable xp_cmdshell, launch the executable on the server and disable xp_cmdshell.I have created another SQL Login Bob? and is member of sysadmin. The below SP is created by logging in as Bob?. My objective is to grant execute access on this SP and grant impersonate access to sql login client? . So that client application connecting to SQL server can launch the executable by calling the SP
create proc Bob.[Bob_enable_xp_cmdshell]
with execute as 'Bob'
as
EXEC sp_configure N'show advanced options', N'1' RECONFIGURE WITH OVERRIDE
EXEC sp_configure N'xp_cmdshell', N'1'
RECONFIGURE WITH OVERRIDE
EXEC sp_configure N'show advanced options', N'0' RECONFIGURE WITH OVERRIDE
-- launch the application using xp_cmdshell and disable the xp_command shell
When I try to execute (exec [Bob].[jtr_enable_xp_cmdshell]
) the above SP by logging in as Bob, Im getting the below error
Msg 15247, Level 16, State 1, Procedure sp_configure, Line 94
User does not have permission to perform this action.
Msg 5812, Level 14, State 1, Procedure jtr_enable_xp_cmdshell, Line 4
You do not have permission to run the RECONFIGURE statement.
Msg 15123, Level 16, State 1, Procedure sp_configure, Line 51
The configuration option 'xp_cmdshell' does not exist, or it may be an advanced option.
Msg 5812, Level 14, State 1, Procedure jtr_enable_xp_cmdshell, Line 6
You do not have permission to run the RECONFIGURE statement.
Msg 15247, Level 16, State 1, Procedure sp_configure, Line 94
User does not have permission to perform this action.
Msg 5812, Level 14, State 1, Procedure jtr_enable_xp_cmdshell, Line 7
You do not have permission to run the RECONFIGURE statement.
Please note the SQL Login Bob? is a member of sysadmin fixed role. Thank in advance for you help.
With regards
GK
View 10 Replies
View Related
Oct 4, 2007
Hi,
I need to allow only a specific group of users to use a linked server. Rather than granting each user access in the linked server I create and give access to a local SQL login "link_user" and then grant the group impersonate to "link_user". The folllowing statements then work fine:
EXECUTE AS LOGIN = 'link_user'
SELECT * FROM OPENQUERY(linked_server, 'SELECT * FROM ...)
But when I use the exact same statements in a stored procedure I get the folllowing error:
Msg 7416, Level 16, State 1, Procedure linktest2, Line 5
Access to the remote server is denied because no login-mapping exists.
I need to be able to work with the result set which is why I use OPENQUERY rather than EXECUTE... AS... AT which works fine also in stored procedures!?
Is there another workaround then to create all users in the linked server security?
Rgds
Bertrand
View 1 Replies
View Related
Feb 25, 2007
Hello,
I need to pull data from an Excel Workbook (DTS Excel Source) where the data is stored in worksheets that are each named one of the twelve months of the year. The package is fired by a job that runs once a month. Currently, I have to go into the select statement of the Excel source before the process runs and change the name of the Worksheet that the data is pulled from.
How can I set up the sql statement for the Excel source so that I can pull from the Worksheet that contains the name of the current month?
Thank you for your help!
CSDunn
View 2 Replies
View Related
Jul 8, 2007
I have a query that gets the tables, columns, indexes, index_columns for all tables in a data base.
But, I need to be able to select any data base on the server
and then drill down into the tables, columns, etc.
I looked at sys.databases but can't see how to relate that to the
sys.tables to extend my query.
Is there a way?
I've looked at the information_schema area and catalog views
but I don't see a solution yet.
View 7 Replies
View Related
Mar 8, 2012
I would like to run the same query on multiple tables. So say I have a list of tables
@tableList = a|b|c|d
And then I have my query looping through the tables
for (@table in tableList)
{
update from @table
set = ''
}
Is there a simple way to do this in an mssql query, if so how do I get to loop through the query switching the table name?
View 4 Replies
View Related
Jul 7, 2015
I have a table which has been partitioned on BIGINT column.
Partitioned_Table (ID BIGINT, Name VARCHAR(10), Gender VARCHAR(2))
I have a left range partition function on ID column.
CREATE PARTITION FUNCTION Partition_Function ( BIGINT )
AS RANGE LEFT
FOR VALUES ( '20150601000', '20150602000', '20150603000' );
That means the first partition is ID >= 20150601000 to ID < 20150602000.
I have to switch in a table into an empty partition.
Switching_In_Table(ID BIGINT, Name VARCHAR(10), Gender VARCHAR(2))
Before the switch in, I am creating a CHECK constraint on Switching_In_Table CHECK(ID LIKE '20150625%') Can I use like clause in this scenario?
View 6 Replies
View Related
Feb 20, 2007
Hi all--I used the following type of script to migrate tables, views and stored procedures from 'dbo' to 'hqmsuser':
alter authorization on dbo.PK_T_PQ_MASTER to HQMSUSER
alter schema HQMSUSER transfer dbo.PK_T_PQ_MASTER
go
This was per recommendations from the BOL to use instead of the 'sp_changeobjectowner' procedure. I returned the following error:
Msg 15346, Level 16, State 1, Line 3
Cannot change owner for an object that is owned by a parent object. Change the owner of the parent object instead.
Msg 15347, Level 16, State 1, Line 4
Cannot transfer an object that is owned by a parent object.
How do I find and change the parent object on this from 'dbo' to a non-dbo schema ownership?
View 1 Replies
View Related
Jul 28, 2015
I’m looking for clearity on partition switching. The idea is to use many BULK INSERT statements into table dbo.X_n in parallel and when BULK INSERT for table dbo.X_n is completed, switch dbo.X_n into dbo.bigdaddy. I think this is the fastest way to upload a couple hundred GB of data.
In learning about partition switching (in part) from The Data Loading Performance Guide under Partition SWITCH, I hear the instructions to say copy the main table exactly to become a target. But in that same step (#1), I read that we need to change the default file group of the target (dbo.X_n) from the default file group. Then it says I need to match indexes and lists the filegroup as something we need to match with the main table.
As an overview of the partition switching strategy, I think the whole point of BULK INSERT with partitioning is to have seperate files (in same group) to enable concurrent uploading where each table has its own file. Once the upload is completed to a table (dbo.X_n) then we do the partition switch into the main table (dbo.bigdaddy). The data we just uploaded doesn’t actually move, just the metadata for it.
When I read the instructions linked above, I hear “Don’t have the same filegroup on your target as the main table. You must have the same filegroup on your target as the main table.”
Where am I disconnected?
View 5 Replies
View Related
