SQL Server 2012 :: Cursor Function And Drop User / Logon

Aug 28, 2015

I have a temptable with a list of user IDs that I want to drop so I created a script to do a cursor and run through my drop functions. The drops work by themselves and the ver check works with them but when I wrap them in the cursor all i get is an output for each user in the results window in ssms. why it's not setting the variable and instead outputting to results?

DECLARE @ver nvarchar(128);
DECLARE @UserName nvarchar(50);
DECLARE @UserD nvarchar(80);
DECLARE @LoginD nvarchar(80);
-- Initialize the variable.
SET @ver = CAST(serverproperty('ProductVersion') AS nvarchar)

[code]...

View 7 Replies


ADVERTISEMENT

SQL Server 2012 :: Removing Cursor In Table Valued Function

Oct 16, 2015

I need removing cursor in my table valued function with alternate code.

ALTER FUNCTION [dbo].[eufn_e5_eSM_SE_GetCurrentContentForContainer]
(
@containerSqlId SMALLINT,
@containerIncId INT
)
RETURNS @Results TABLE

[Code] ....

View 2 Replies View Related

SQL Server 2012 :: Using User Defined Function In View?

Jan 31, 2014

I have a function that accespts a string and a delimeter returns the results in a temp table. I am using the funtion for one of the columns in my view that needs be to split and display the column into different columns. The view takes for ever to run and finally it doesn't split and doesn't display in the column.

Function:
-----------------------------------
ALTER FUNCTION [dbo].[func_Split]
(
@DelimitedString varchar(8000),

[Code].....

Not sure what I am missing in the above view why it doesn't split the string.

View 8 Replies View Related

SQL 2012 :: Unable To Drop Database User

May 7, 2014

We are unable to drop a database user with the following error "Msg 15284, Level 16, State 1, Line 1

The database principal has granted or denied permissions to objects in the database and cannot be dropped."

On checking the database permissions we have the following permissions assigned to the user

classclass_descmajor_idminor_idgrantee_principal_idgrantor_principal_idtypepermission_namestatestate_desc
17SERVICE655360517SN SENDGGRANT
17SERVICE655370517SN SENDGGRANT
17SERVICE655380517SN SENDGGRANT
17SERVICE655390517SN SENDGGRANT
17SERVICE655400517SN SENDGGRANT

[Code] ....

How we can revoke the SEND permissions?

View 3 Replies View Related

SQL Server 2012 :: Convert Stored Procedure To User Defined Function?

Feb 23, 2015

I have created a store procedure, but the requirement is function because by using this function we need to add columns in a table with SSIS.

I have tried to create function, but the error I am facing is select statement can not return data.

CREATE PROCEDURE SP_STAT_CURR
(
@I_NET_AMOUNT NUMERIC(10,3),
@I_DOCUMENT_CURR VARCHAR(3),
@I_TARGET_CURR VARCHAR(3)

[code]....

View 9 Replies View Related

SQL 2012 :: User Impact To Drop A Noncluster Index On Table While In Use?

May 13, 2014

What is the impact on the users to drop an index on a table while in use? I will recreate the index afterwards. The table is used constantly by a three of processes/users at all times.

View 3 Replies View Related

SQL 2012 :: Using Linked-server During (logon Trigger)

Oct 8, 2015

Used linked-server during logon trigger? is it possible? i know we can access tables, SPs, etc with in that server, if i want to check something in other server during logon trigger, can i do it?

View 0 Replies View Related

SQL Server 2012 :: Trigger To Prevent Logon To A Database

Mar 19, 2014

OK, I know about this: [URL] ....

But the script has "ALL SERVER".

What I want is a trigger that is specific to my DB called "JunkStuff". I only want to block a servername from connect to my super dooper DB "JunkStuff".

View 1 Replies View Related

Login Failed For User 'NT AUTHORITYANONYMOUS LOGON' (but User.identity.name Is Correct)

May 1, 2007

I need help.  The security principal is the correct DomainUser, but the error message says I am not authenticated.
So here is my error Message:
Login failed for user 'NT AUTHORITYANONYMOUS LOGON'. Me.User.Identity.Name=CompanyDomainRyan; System.Security.Principal.WindowsIdentity.GetCurrent=CompanyDomainRyan; HttpContext.Current.User.Identity.Name=CompanyRyan
Generated by:
Dim secPrinc As System.Security.Principal.WindowsIdentity = System.Security.Principal.WindowsIdentity.GetCurrent
Me.lblError.Text = ex.Message & vbCrLf & vbCrLf & "Me.User.Identity.Name=" & Me.User.Identity.Name & "; System.Security.Principal.WindowsIdentity.GetCurrent=" & secPrinc.Name & "; HttpContext.Current.User.Identity.Name=" & HttpContext.Current.User.Identity.Name
Here is the Connection String:
Private ConnString As String = "Server=10.144.162.111;Database=DNS;Trusted_Connection=True"
 
IIS has 4 websites hosted on it.  The parent level allows anonymous with windows auth off, but the website level has anonymous off with widnows auth and digest.
In IIS, is there a problem with having the parent level anonymous and website level windows auth?

View 1 Replies View Related

Login Failed For User 'NT AUTHORITYANONYMOUS LOGON' For Insert Reference Using New Linked Server

Mar 23, 2006

I get this error when trying to alter a stored procedure that has an insert statement referencing a new linked server I created:

INSERT INTO [servername].databasename.dbo.DirectReport
...


Msg 18456, Level 14, State 1, Procedure Get_Direct_Pay_Move_Data, Line 17
Login failed for user 'NT AUTHORITYANONYMOUS LOGON'.

I added Administrators and my logon to the permissions of my linked server but still get this error when it tries to save my stored proc...the one which has that insert.

View 4 Replies View Related

Cannot Logon With Authenticated User When User Database Is Removed

Dec 12, 2007



Hello,

We have a new production server with SQLserver 2005.
Our department responsible ofr the installation made it as secure as possible, however.
I have to put a new backup on to the server from the old production server.
We ran into a problem when we removed the old database.
I could not longer logon to the server with an authenticated user.

"A connection was successfully established with the server, but an error occured during the login process"
"Shared memory provider : error 0 : No process is on the other end of the pipe"

Strange thing is when i create a new database with the same name. The user can logon to the server again.

Any idea's ?


Jurgen

View 1 Replies View Related

SQL 2012 :: Way To Prevent A User From Using SERVERPROPERTY Function?

Jun 2, 2015

I manage a hosted environment - several actually. Meaning my company is the host. We provide some of our customers with a dedicated instance of SQL Server that they can use in read-only mode. Unfortunately, I have some very inquisitive customers who probably should be hosting their own SQL. They like to poke into the inner workings of our environment in ways that make me uncomfortable. What I originally found was that they were reading our server registry. I've disabled that. The next thing that bothers me is the amount of information available to them through SERVERPROPERTY. They don't need to know things like physical node of a cluster their instance is on, or the last time we updated SQL, or our licensing choices.

Is there any way to prevent a user from using the SERVERPROPERTY function? I've done a lot of digging and can't seem to find a way to do it.

View 1 Replies View Related

Drop All Indexes In A Table, How To Drop All For User Tables In Database

Oct 9, 2006

Hi,I found this SQL in the news group to drop indexs in a table. I need ascript that will drop all indexes in all user tables of a givendatabase:DECLARE @indexName NVARCHAR(128)DECLARE @dropIndexSql NVARCHAR(4000)DECLARE tableIndexes CURSOR FORSELECT name FROM sysindexesWHERE id = OBJECT_ID(N'F_BI_Registration_Tracking_Summary')AND indid 0AND indid < 255AND INDEXPROPERTY(id, name, 'IsStatistics') = 0OPEN tableIndexesFETCH NEXT FROM tableIndexes INTO @indexNameWHILE @@fetch_status = 0BEGINSET @dropIndexSql = N' DROP INDEXF_BI_Registration_Tracking_Summary.' + @indexNameEXEC sp_executesql @dropIndexSqlFETCH NEXT FROM tableIndexes INTO @indexNameENDCLOSE tableIndexesDEALLOCATE tableIndexesTIARob

View 2 Replies View Related

How To Move Database User And Logon

Sep 19, 2013

I have one old sql 2000 server and I backup the database and restore it to SQL 2008 R2 database. I can see all the tables and users but I do not know, how to move database user and logon's.

View 3 Replies View Related

Drop And Add A Sql Server Login Account To Each User Database

Dec 6, 2007



I would like to write code to delete and add a SQL Login to every User database on my development server.
Whenever I restore databases to dev using production backups the SQl Server logins are invalid and I need to delete them from the user database and add them again.

I've already hard-coded a sql server job with n steps... a step for each user database to drop and add this sql user to each database. This isn't optimal since I have to add or delete a step everytime a user database is added or deleted.

Does anyone know how to write a loop or cursor that does this dynamically?
I am doing something syntactically wrong related to the GO statement.




declare @db varchar(100)

declare @message varchar(3000)

DECLARE user_cursor CURSOR FOR

SELECT top 1 name

FROM master.sys.databases

where name not in

('master','tempdb','model','msdb')

OPEN user_cursor

FETCH NEXT FROM user_cursor INTO @db



WHILE @@FETCH_STATUS = 0

BEGIN

SELECT @message = 'use '+@db + '

GO'

+'DROP USER [SQLLogin.DataEntry]

CREATE USER [SQLLogin.DataEntry] FOR LOGIN [SQLLogin.DataEntry]

EXEC sp_addrolemember N''db_datareader'', N''SQLLogin.DataEntry''

GO

'

execute @message

print @message

FETCH NEXT FROM user_cursor INTO @db

END

CLOSE user_cursor

DEALLOCATE user_cursor





View 3 Replies View Related

Login Failed For User 'NT AUTHORITYANONYMOUS LOGON'.

Jan 29, 2007

get the following error when a user tries to logon from work station.The website is hosted on IIS on the same server as sql server 2005website is asp.net 2.0SqlException (0x80131904): Login failed for user 'NT AUTHORITYANONYMOUS LOGON'.]   System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +734883   System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188   System.Data.SqlClient.TdsParser.Run(RunBehavior
runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream,
BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
+1838   System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK) +33   System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) +628   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) +359   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.Security.SqlMembershipProvider.GetPasswordWithFormat(String
username, Boolean updateLastLoginActivityDate, Int32& status,
String& password, Int32& passwordFormat, String&
passwordSalt, Int32& failedPasswordAttemptCount, Int32&
failedPasswordAnswerAttemptCount, Boolean& isApproved,
DateTime& lastLoginDate, DateTime& lastActivityDate) +1121   System.Web.Security.SqlMembershipProvider.CheckPassword(String
username, String password, Boolean updateLastLoginActivityDate, Boolean
failIfNotApproved, String& salt, Int32& passwordFormat) +105   System.Web.Security.SqlMembershipProvider.CheckPassword(String username, String password, Boolean updateLastLoginActivityDate, Boolean failIfNotApproved) +42   System.Web.Security.SqlMembershipProvider.ValidateUser(String username, String password) +83   System.Web.UI.WebControls.Login.OnAuthenticate(AuthenticateEventArgs e) +160   System.Web.UI.WebControls.Login.AttemptLogin() +105   System.Web.UI.WebControls.Login.OnBubbleEvent(Object source, EventArgs e) +99   System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +35   System.Web.UI.WebControls.Button.OnCommand(CommandEventArgs e) +115   System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +163   System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7   System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11   System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5102

View 7 Replies View Related

Login Failed For User 'NT AUTHORITYANONYMOUS LOGON'.

May 15, 2008

I just upgraded to Vista and can not run the website anymore. Getting: Login failed for user 'NT AUTHORITYANONYMOUS LOGON'. error
 I added that user to all databases as db_owner, but that did not help..

View 1 Replies View Related

Login Failed For User 'NT AUTHORITYANONYMOUS LOGON'

Mar 21, 2005

Hello everyone,

I am receiving the above error when using a workstation to connect to my webfiles.

I am using windows authentication (whcih I have selected as my only option on the IIS).
Impersonate is set to true in the webconfig file, with authentication set to Windows.

My connection string is: SQLConnection1 = "initial catalog=SIMS;data source=spacesql;integrated security=SSPI"

It runs no problem when I use the localhost.
It allows me to use other aspx pages that do not connect to the SQL server.
It only gives me this error when I am using a page that tries to connect to the SQL server.

The SQL files reside on a separate server to the IIS and I would like it to remain this way.

Can anyone help me wit this please

Thank you,

Rebecca

View 3 Replies View Related

Logon,Unknown,Login Failed For User

May 11, 2008

hi

i'm a software devoloper , i writed a web application and use with this application sql server 2005

my application work very good
but if i try to open the application using domanuser the application failed and write this error :
Logon,Unknown,Login failed for user 'EPSDOMAINabeel'. [CLIENT: 192.168.0.132]

i know that i have a problem with permmison at the database, how can i solve this problem.

nabeel
thanksssssssss

View 2 Replies View Related

Logon,Unknown,Login Failed For User

May 11, 2008

hi

i'm a software devoloper , i writed a web application and use with this application sql server 2005

my application work very good
but if i try to open the application using domanuser the application failed and write this error :
Logon,Unknown,Login failed for user 'EPSDOMAINabeel'. [CLIENT: 192.168.0.132]

i know that i have a problem with permmison at the database, how can i solve this problem.

nabeel
thanksssssssss

View 1 Replies View Related

Login Failed For User 'NT AUTHORITYANONYMOUS LOGON'

Mar 14, 2007

I get the following error message when I select a report:


An error has occurred during report processing.



Cannot create a connection to data source 'PSS5'.

Login failed for user 'NT AUTHORITYANONYMOUS LOGON'.

My Data source is set to Windows Integrated Security when I get this error message. If I change my data source to be "Credential supplied by the user running the report", the report generates fine. I am running SQL 2005 SP2. I have SQL reporting services installed on different server than my database services. Both servers are W2K3 R2 x64.

My IIS website and virtual directories are set up for Integrated Windows Authentication. My IIS application pool is set up to use an AD service account. I have followed the step/procedures outlined at: http://www.microsoft.com/technet/prodtechnol/windowsserver2003/technologies/security/tkerbdel.mspx

http://blogs.msdn.com/sql_protocols/archive/2005/10/12/479871.aspx
http://blogs.msdn.com/sql_protocols/archive/2006/08/10/694657.aspx

but to no avail. I am sure that I am missing something, but I can't figure out what. I would like to use Integrated Authentication, any ideas?

View 19 Replies View Related

Login Failed For User 'NT AUTHORITYANONYMOUS LOGON'

Feb 27, 2007

Hello all -

I'm trying to connect to a Sql Server 2005 database from my local development machine, which is running IIS 5.1 (Windows XP Pro). The web application is not using impersonation and has anonymous access enabled. In my system's machine.config file, I've set the processModel section to look like:

<processModel userName="System" password="AutoGenerate" />

This has been working fine for us connecting to SQL Server 2000 databases (running on Windows 2003). When we connect, I access Sql 2000 databases under the context of "OurDomainMyMachineName$". However, we've recently installed Sql Server 2005 on a new Windows 2003 server & when I connect to that server, I receive the error:

System.Data.SqlClient.SqlException: Login failed for user 'NT AUTHORITYANONYMOUS LOGON'

when trying to call the SqlConnection.Open() method. Our connection string is very basic:

Data Source=sql-dev2; Initial Catalog=TestDatabase; Integrated Security=True; Application Name=PrototypeWebservice;

In the Sql Server logins, we've granted the computer account "OurDomainMyMachineName$" db_owner access to every database (in both Sql Server 2000 & 2005).

I am able to connect to the Sql Server 2005 databases if I change my section in machine.config to <processModel userName="OurDominamyPersonalAccount" password="myPersonalPwd"/>, or if I move the application to an IIS6 server & set the application pool identity to NETWORK SERVICE, but we'd like to continue accessing our Sql Server 2005 databases (from our development machines running IIS5.1) as we currently access our Sql Server 2000 databases; under the context of "OurDomainMyMachineName$".

Would anyone have any ideas why this would be happening or how to resolve? Thanks in advance.

View 4 Replies View Related

Login Failed For User 'NT AUTHORITYANONYMOUS LOGON'

Dec 11, 2007

Report review normal in dev environment, however it does not work on webpage after deployed,prompted
"

Cannot create a connection to data source

Login failed for user 'NT AUTHORITYANONYMOUS LOGON'"
I checked the web.config,it showed

<pages validateRequest="false" />
<authentication mode="Windows" />
<identity impersonate="true" />
<compilation defaultLanguage="c#" debug="false">
<assemblies>
<clear />
<add assembly="ReportingServicesWebServer" />
</assemblies>
</compilation>

Any idea about this? Thanks

View 3 Replies View Related

Question About RS2k User Logon Window

Oct 24, 2006

When I'm trying to logon the reporting service main page, the user logon dialogue box will ask you to input the authorized user id and password(It's the account created in windows system). But when I try to open certain report!on my reporting server, this logon dialogue box will be displayed again and I have to input the account and password again. All the reports can be directly opened after second entry.

So my quesition is how to setup reporting service so that I can open all the reports directly just after the first entry.That will bother users to browse report!freely only after inputing authority information twice.



PS: My platform is Windows 2K Server SP4 + SQL Server 2K SP4.

View 6 Replies View Related

Login Failed For User 'NT AUTHORITYANONYMOUS LOGON

Jul 6, 2007

Hello Guys,

I have two machines that i want to use for merge replication :


Laptop ( Publisher + Distributor )

VPS90 ( Subscriber )

Both connected via the internet

NO DOMAIN

Need to implement merge replication ,after initializing the snapshot I get the following error on both machines :


Login failed for user 'NT AUTHORITYANONYMOUS LOGON'. [CLIENT: 192.168.1.1]



I created a new user "Repuser" (admin)with the same password on both machines and configured the Agent proccess to run under that account ,now i get this error :
Login failed for user ''. The user is not associated with a trusted SQL Server connection. [CLIENT: 192.168.1.1]





Please note that :


· Mixed mode authentication IS ENABLED ON BOTH MACHINES
· BOTH MACHINES CAN PING AND ACCESS EACH OTHER ,BOTH COMPLEETELY VISIBLE.
· CONFIGURED ALIASES ON BOTH MACHINES.
· Both configured to use Sa account .
* I have NO DOMAIN


what does this mean?

It simply means that the SQL Server Agent process account is the one that€™s being authenticated to connect to the other machine and NOT the Sa account ,WHY?



do i have to use Web Synch in my case?

View 9 Replies View Related

Execute Package Logon Failed For User....

Sep 27, 2006

Fairly new to SSIS.I have created a package in BIDS, and am trying to execute it so i can test/debug the flow and view the data in the data viewers. I continually get a logon failed for user... its the user id i am using to connect to my SourceConnection OLEDB. I have been struggling with this for a while now, and getting frustrated. I tried Windows Authentication also, still i get an error message logon failed for user......

I have tried all of the security settings, from Do not save sensitive to Encrypt all with password, entered the password, and then try to Execute Package from the solution explorer and still get error message. Thanks for your help!

Here is the error message

An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E4D Description: "Login failed for user 'UserName'.".

View 6 Replies View Related

SQL Server 2012 :: Drop And Update Column?

Feb 16, 2015

I am looking for standard sql code for below 2 concern.

1- I want to drop the column Rowchecksum to all the table where table name start with ArchiveBbx

2-I want to update all the table where table name start with ArchiveBbx

example:-

Update table Archivebbxfbcc
set Rowchecksum=HASHBYTES('MD5', CAST(CHECKSUM(Col001, Col002, Col003, Col004) AS varchar(max)))

View 3 Replies View Related

SQL Server Express 2005 - How To Delete/drop A User Instance

May 23, 2008

Hi,

I have been working around with SQL Server Express 2005 on VISTA. I created a small C++2008 application which interface with SSE 2005. When running my application, it automatically created a user instance which I would like to get rid of. I just forgot from start to set "User Instance = False" in my connectionstring. (oups...)

I'm aware, from readings I did, that a couple of tables as well as log files, etc. are created under directory "utilisateurs...AppDataLocalMicrosoftMicrosoft SQL Server DataSQLEXPRESS". SQLEXPRESS is the instance name chosen during install of SSE2005.

Also using following SQL Statement:

select owning_principal_name, instance_pipe_name, heart_beat
from sys.dm_os_child_instances
I got following result:
owning_principal_name: PC-DE-STEPHANEStéphane
instance_pipe_name: \.pipe6172F4E8-622E-4A sqlquery
heart_beat: dead (at this moment...)

==> How can I make a good cleanup of all this as well as get rid of the user instance itself?


Thanks in advance for any help.

Stéphane

View 7 Replies View Related

Analysis :: Logon Failure - Unknown User Name Or Bad Password

Jan 26, 2015

I am trying to connect Sharepoint 2013 services (performancepoint , excel services) to SSAS 2012 using the EffectiveUserName.

I was getting a error message regarding conencting the datasource and when checking profiler it was saying Logon failure: unknown user name or bad password.

On the SSAS server i tried using EffectiveUserName in the additional Connection Parameters and it failed for the above reason. I also tried my username as well as the sharepoint service accountthe sharepoint service account and my username are both server admins.

When I try using the EffectiveUserName in the additional Connection Parameters on another server I can connect with no problems.

What seems to be the different with server where I am getting the error message?

View 5 Replies View Related

SQL Server 2012 :: IF Statement Within A Cursor?

Oct 19, 2015

adding a if statement within my cursor.

Use master
GO
DECLARE @dbname VARCHAR(50)
DECLARE @statement NVARCHAR(max)
DECLARE db_cursor CURSOR
LOCAL FAST_FORWARD

[code]....

The cursor should only grant access to a particular database. If the user exists within the database it should not execute the script.

View 0 Replies View Related

SQL Server 2012 :: How To Drop And Create Synonym Without Client Error

Apr 23, 2015

A heavily-selected database will be in an inconsistent state for several hours during a batch process. For that time, a database snapshot is created and accessed instead. To allow constant client read access to the database, a database that only contains synonyms exists. Those synonyms point to the main database except during the batch process, at which time they point to the database snapshot.

To switch the synonyms, each synonym is dropped and then created pointing to the database snapshot (after its creation, of course). The drop/create occurs inside a transaction. Roughly, the SQL looks like this:

SET XACT_ABORT ON;
BEGIN TRANSACTION;
DROP SYNONYM [dbo].[some_proc];
CREATE SYNONYM [dbo].[some_proc] FOR [snapshot_db].[dbo].[some_proc];
GRANT EXECUTE, SELECT ON [dbo].[some_proc] TO public;
COMMIT TRANSACTION;

When the batch update is completed, the process is reversed with "snapshot_db" replaced with "regular_db". The SQL snippet above is dynamic SQL. What I've pasted is the dynamic SQL that is executed as a single batch.

While this switch is happening, clients are accessing the procedures through the synonyms, potentially at a high request rate. Testing reveals that clients can get the error:

Error=-2147217900, Id=0, Meaning=IDispatch error #3092,
Source=Microsoft OLE DB Provider for ODBC Drivers,
Description=[Microsoft][ODBC SQL Server Driver][SQL Server]Could not find stored procedure 'dbo.some_proc'.

This error only occurs once. If the same SPID retries its request and the transaction has not completed (for testing, a delay was added), then it blocks until the transaction completes.

Any way to prevent it besides a client-side retry?

View 2 Replies View Related

SQL Server 2012 :: Query To Find User Who Last Modified User Roles / Access?

Dec 6, 2013

I would like to know if there is a way to find out who changed a users roles/access WITHOUT using the audit function. For example, if a user account was created and given SA access then changed to read only, how can I find out who made that change? I tried searching for an answer, but kept getting no results. I'm thinking this may tie into the sys.sysusers view?

View 3 Replies View Related

SQL Server 2012 :: While Loop In Place Of Cursor

Feb 16, 2014

I have a table called Table1 where I have five fields like Tableid, Processigndate, Amount, remainingCollectonCount and Frequency. All total I have more than 5Lacs records.

Now I need to fill up another table Called FutuecashFlow taking the records from Table1. There will be also five Columns like FutureCashflowid, Table1id, Processigndate, Amount.

Now the condition is that if the remainingCollectonCount =6 and the frequency is 12 then there will be the 6 entries in the futurecasflow table where the prcessign datae wille be addeed by 1 month.

For example Table1
Tableid, Processigndate Amount remainingCollectonCount Frequency
1 2014-02-15 48 8 12

the future cash flow table the prcessing date column will be shown in the following way

Processigndate
2014-03-15
2014-04-15
2014-05-15
2014-06-15
2014-07-15
2014-09-15
2014-10-15

I do not to want to use cursor....

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved