Error 87 from GetProxyAccount , SysAdmin

I work as ERP Manager in one of the leading company of Nepal. Recently, I came across a problem. The problem was that one of the customized process to upload was not running from the application. The process runs a DTS package with command (xp_cmdshell). So, IT people have to run it from back end by logging is SQL Server with username sa.

 

This was troublesome as IT people have to be there whenever user requires to run that process. The problem was also that, the front-end showed only a message like " Execution failed" and then stops. And from back-end no error comes. For months, the same method continued. And also we were not able to login SQL Query Analyzer with other logins as the password was encrypted by front-end application.

 

After some research, I was able to decrypt password. For that, I just changed password to same password from Enterprise Manager. After that, I was able to login with other logins or usernames besides sa. When I try to run from there, it showed some error message like this.

 

Msg 50001, Level 1, State 50001

xpsql.cpp: Error 87 from GetProxyAccount on line 604

 

Now, I am able to google in internet for error message. After some research, I found solution in Microsoft Support site. The error was due to the fact that we donot login application with username sa and other users are not assigned to role of system administrator in SQL Server and database owner for concerned database. So, I assigned system administrator role and database owner to the user of the application. Finally, users were able to run the process from front-end.

 

The error message shown is genuine. Whenever we try to run shell commands, which have to run in command prompt, it should be run as local system or windows account. Only the users mapped to system administrator role of SQL Server can get access as windows local administrator and run the shell command. As the user of application was not mapped to that role, it will then search for any proxy windows account. As proxy account was also not found, the message was displayed.

 

From this, it is clear that the users which are not system administrator of SQL Server, can also get access to DTS package by setting up proxy account for that in SQL Server.

For that, go to SQL Server agent, Properties, Job System, uncheck the "Only users with SysAdmin privileges can execute CmdExec and ActiveScripting job steps" and enter the proper username, password and domain of windows. This is also a good method. But it gives access to all users to run DTS package, which may not be appropriate.

 

[Microsoft][ODBC SQL Server Driver]Syntax error or access violation

This error came when I compile a procedure.
It is related to SQL Server 2000.

The error is due to use of some ascii character in procedures.
Last time, when I used '{' mistakely in a procedure and tried to compile it, then this error come.
So, check your procedure properly and you will be able to fix the problem.

SQL Server problem: Cannot resolve collation conflict for equal to operation.

Cannot resolve collation conflict for equal to operation.

The error comes when the collation type of the columns to be compared does not match or conflict.

For example if this query gives above error.

select        *
from        usertable
where        loginname = cust_no

To resolve this problem,  collate database_default  needs can be added arround "=" operator. Like below.

select        *
from        usertable
where        loginname collate database_default = cust_no collate database_default

How to get version of SQL Server?

To get version of SQL Server.
Open the SQL Query Analyzer. You can open query analyzer by going to run and typing isqlw.

Type the following and press F5.
select @@version.

You will get the result like.
Microsoft SQL Server  2000 - 8.00.760 (Intel X86)   Dec 17 2002 14:22:05   Copyright (c) 1988-2003 Microsoft Corporation  Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 3)

Undocumented SQL Server Iteration Procedures

I rarely encourage the use of iteration when database programming because looping constructs in the database world tend to be harder to manage and much slower than set-based constructs. However, there are times when using iteration inside the database engine is useful. I'll present a few examples using two undocumented system stored procedures provided by Microsoft. (Note: The examples in this article work in SQL Server 2000 and SQL Server 2005.) Sp_msforeachdb and sp_msforeachtable allow you to pass TSQL statements that will be executed in a FOR…EACH loop fashion for each database on the instance or for each table in a given database. These procedures are very handy when you're performing sets of operations for everything in your system, such as backing up all user databases or capturing the sizes of each individual table.

sp_msforeachdb

The sp_msforeachdb system stored procedure accepts a TSQL string to be executed against each database that resides on your SQL Server instance. This procedure is especially useful when you're performing database administration and maintenance tasks, such as backup operations. This example loops through each database on the server and prints out the database name:

EXECUTE sp_msforeachdb 'USE ? PRINT DB_NAME()'

The code snippet may be a bit confusing if you haven't seen this in practice. Notice the use of the question mark (?); this character represents the name of the database returned at each iteration of the internal loop. I can use the question mark anywhere in the script that I would normally use the name of the database.
With just a bit of tweaking, I can change the above code into a statement that will create a full backup of all the user databases on the current server instance. For example:

EXECUTE sp_msforeachdb 'USE ? IF DB_NAME() 

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

BACKUP DATABASE ? TO DISK = ''G:?.bak, WITH INIT'''

Notice the use of double tick (') marks; these marks are used frequently in dynamically built TSQL code and represent a single tick mark. Single tick marks are commonly used to mark the beginning or ending of string literal statements.

sp_msforeachtable

The sp_msforeachtable system stored procedure is very similar to the sp_msforeachdb procedure except that it loops through all of the tables contained in the current database. This procedure is great for operations such as gathering statistics and bulk operations on sets of tables. In the following example, I use sp_msforeachtable to invoke the stored procedure sp_spaceused and pass the table name.

CREATE TABLE #TableSizes (

TableName NVARCHAR(255),             

TableRows INT,             

ReservedSpaceKB VARCHAR(20),             

DataSpaceKB VARCHAR(20),             

IndexSizeKB VARCHAR(20),             

UnusedSpaceKB VARCHAR(20) )

INSERT INTO #TableSizes EXEC sp_msforeachtable 'sp_spaceused ''?'''

SELECT * FROM #TableSizes ORDER BY TableRows DESC

One of the most useful aspects of the code is that it inserts the results from sp_spaceused into a table. Note that I am not calling the sp_spaceused procedure itself; I'm calling it dynamically inside of the loop. Even through this method of code execution, I am able to capture the results and store them in a table for later use.

Connect to SQL Server database using VB

 
To connect to SQL Server Database, the Steps involved are
 
1. Define A Connection
 
Public cnn As ADODB.Connection
Set cnn = New ADODB.Connection
cnn.ConnectionString = "Data Source=(name of database server);User id=sa;password=sa;Initial Catalog=<databasename>"
cnn.Open
 
2. Define a RecordSet
 
Public rs As ADODB.Recordset
Set rs = New ADODB.Recordset
 
3. Now, use the recordset to connect to database using connection object
 
rs.CursorLocation = adUseClient
rs.Open "Select field1, field2 from table1", cnn
 
4. You can use the data returned by query in recordset like this
 
while rs.Eof = false and rs.bof = false
         me.txtField1 = rs("field1")
         rs.movenext
wend
 
Note: Do not forget to use movenext function to move the recordset to next record
otherwise, it will result endless loop.
         The recordset can also be moved backward, moved to first and last at once.
 
5. Finally, do not forget to close the recordset and connection.
But, they are automatically dropped after the application is closed.
 
rs.close
cnn.close
 
set cnn = Nothing
 

How to truncate log file in SQL Server 2000

 

BACKUP LOG <database name> WITH TRUNCATE_ONLY
USE <database name>
DBCC SHRINKFILE (2, 20) 

 

-- 2, file id of log file

-- where 20 = 20 MB , target size of log file

 

How to truncate log file in SQL Server 2005

 

SQL Server 2005 is quite different from SQL Server 2000. To truncate log file is one thing which is different from SQL Server 2000. In SQL Server 2000, you just use Shrink to whatever file size you like. In SQL Server 2005, sometime I cannot shrink the log file at all.

 

Here I want to describe some tricks to truncate log file for a database in SQL Server 2005. The work environment is MS SQL Server Management Studio.

 

I.  Shrink the log file size at the right time

I found out this trick:

 Immediately after I use the SSIS package or Import the data to the database ( highlight the database->Tasks->Import data … ), or Export the data from the database ( highlight the database->Tasks->Export data … ),  I can shrink the log file to the desired size, for example, 1MB.  That is, highlight the database->Tasks->Shrink->Files

 set the file size, say, 1MB.

Then, click OK and you are done.

 

 

II. Eliminate the log file completely

Sometimes we just do not need the big log file. For example, I have 40GB log file. I  am sure I do not need this log file and want to get rid of it completely to free up the hard drive space. The logic is

a. Detach the database

b. Rename the log file

c. Attach the database without the log file

d. Delete the log file

 

Let's say, the database name is testDev. In the SQL Server Management Studio,

1. Highlight the database-> Tasks->Detach..-> Click OK
2. Go to log file folder -> rename the testDev_log.ldf to be like testDev_log-aa.ldf,
3. Highlight Databases->Attach…-> Click Add -> add the database testDev, highlight the log file and click the 'Remove' button. This means you only attach testDev.mdf
4. After this is done, you can verify the contents of the attached database and then delete the log file.

 

This way we can safely delete the log file and free up the space.

 

Export to Excel from SQL Database using VB Application

Public cnn As ADODB.Connection

Public rs As ADODB.Recordset

 

Set cnn = New ADODB.Connection

Set rs = New ADODB.Recordset

cnn.ConnectionString = "Data Source=tt;User id=sa;password=sa;Initial Catalog=tocsdb"

cnn.Open

 

 

 

   Dim Wrk As Object

    Dim Sht As Object

    Dim Xls As Object

    Dim rsDisplayProduct As ADODB.Recordset

    Dim excel_row As Integer

   

    Set rsDisplayProduct = New ADODB.Recordset

    Set Xls = CreateObject("Excel.Application")

    Xls.Caption = "TOCS Sales Report"

    Set Wrk = Xls.Workbooks.Add

    Set Sht = Wrk.Worksheets.Add

    Sht.Name = "TOCS Sales Report"

   

    Sht.Columns(1).ColumnWidth = 4

    Sht.Columns(2).ColumnWidth = 22

    Sht.Columns(3).ColumnWidth = 10

    Sht.Columns(4).ColumnWidth = 6

   

    excel_row = 2

    Sht.Cells(excel_row, 1) = "Sales Report (Prelim Vs Final)"

    Sht.Cells(excel_row, 1).Font.Bold = True

   

  

    excel_row = 4

    Sht.Cells(excel_row, 1) = "Final Order No"

    Sht.Cells(excel_row, 2) = "Prelim Order No"

    Sht.Cells(excel_row, 3) = "Order Dt"

    Sht.Cells(excel_row, 4) = "Retailercode"

    Sht.Cells(excel_row, 5) = "RetailerName"

    Sht.Cells(excel_row, 6) = "CSA"

    Sht.Cells(excel_row, 7) = "Product"

    Sht.Cells(excel_row, 8) = "Design"

    Sht.Cells(excel_row, 9) = "Style"

    Sht.Cells(excel_row, 10) = "Size"

    Sht.Cells(excel_row, 11) = "Ordered Qty"

    Sht.Cells(excel_row, 12) = "Confirmed Qty"

    Sht.Cells(excel_row, 13) = "MRP"

    Sht.Cells(excel_row, 14) = "Cost to Retailer"

   

    Sht.Range(Sht.Cells(excel_row, 1), Sht.Cells(excel_row, 14)).Font.Bold = True

    Sht.Range(Sht.Cells(excel_row, 1), Sht.Cells(excel_row, 14)).Borders.ColorIndex = 1

   

    smt = "exec toc_Sales_Report '" & FromDate & "','" & ToDate & "'"

'you can use sql query and put in smt

    If rs.State = 1 Then rs.Close

    rs.CursorLocation = adUseClient

    rs.Open smt, cnn

   

    While rs.EOF = False And rs.BOF = False

        excel_row = excel_row + 1

        Sht.Cells(excel_row, 1) = rs!FinalOrderNum

        Sht.Cells(excel_row, 2) = rs!PrelimOrderNum

        Sht.Cells(excel_row, 3) = rs!OrderDt

        Sht.Cells(excel_row, 4) = rs!RetailerCode

        Sht.Cells(excel_row, 5) = rs!RetailerName

        Sht.Cells(excel_row, 6) = rs!CSA

        Sht.Cells(excel_row, 7) = rs!Product

        Sht.Cells(excel_row, 8) = rs!Design

        Sht.Cells(excel_row, 9) = rs!Style

        Sht.Cells(excel_row, 10) = rs!Size

        Sht.Cells(excel_row, 11) = rs!Ordered_Qty

        Sht.Cells(excel_row, 12) = rs!Confirmed_Qty

        Sht.Cells(excel_row, 13) = rs!MRP

        Sht.Cells(excel_row, 14) = rs!CostToRet

        rs.MoveNext

    Wend

       

    Xls.Visible = True

   

    Set Wrk = Nothing

    Set Sht = Nothing

    Set Xls = Nothing

>How to import data from MS Excel to SQL database by ASP?

1. upload your excel file
2. make the connection to the file

Set fso = Server.CreateObject("Scripting.FileSystemObject")
psFilePath = "file.xls"
sPath = server.MapPath("\")
psFilePath = fso.BuildPath(sPath,psFilePath)

'Response.Write psFilePath
' Response.End

Set objConn = Server.CreateObject("ADODB.Connection")
strCnxn = "DRIVER=Driver do Microsoft Excel(*.xls);UID=admin;UserCommitSync=Yes;Threads=3;SafeTransactions=0;ReadOnly=1;PageTimeout=5;MaxScanRows=8;MaxBufferSize=2048;FIL=excel 8.0;DriverId=790;DBQ=" & psFilePath & ""
objConn.Open strCnxn

Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.ActiveConnection = objConn
objRS.CursorType = 3 'Static cursor.
objRS.LockType = 2 'Pessimistic Lock.



3. you geting out the cells you want, here its Cell1_name. (todo this you have to define the area in excel file (Excel: Insert->Name->Define)

sql = "Select " & periode & " from Cell1_name"
objRS.Source = sql
objRS.Open
objRS.MoveFirst
If objRS.Fields.Item(0).Value <> "" Then
lev_prosent = round((objRS.Fields.Item(0).Value * 100),1)
Else
lev_prosent = 0
End if

objRS.Close

4. next is to put it into sql database:

if update = "true" Then

' ====
' UPDATE
set g_conn = open_db()

set objRegExp = New RegExp
objRegExp.Pattern = ","
objRegExp.Global = true
Cell1_name= round(cell1_name,1)
set rs = Server.CreateObject("ADODB.Command")
set rs.ActiveConnection = a_conn

rs.CommandText = stSQL
rs.CommandType = 1
rs.Execute intNoOfRecords
'rs.close

5. Close the connection to the sql...

Set objRS = Nothing
objConn.Close
Set objConn = Nothing

transaction can't enlist in specified coordinator

Problem

Server: Msg 7391, Level 16, State 1, Line 2 The operation could not be performed
because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in the specified
transaction coordinator. ]

The remote server has been added as a linked server, and the privs are set correctly.
MSDTC is started and running on the local and remote servers.

Solution
This error is most probably due to one of the following reasons
1) If firewall is present between the client and server, port 135 is not open. MSDTC
uses RPC which requires port 135 to be open.
2) Run sp_helpserver on boht of your machines and make sure that you have RPC, RPC
OUT showing up. If you added the dest server as a linked server, you should also
be seeing Data Access and other collation related information. If this is not
properly configured, errors could occur.
3) From sp_configure output, check what you have set for "Remote proc trans" option.
More information on what this option does is in SQL BOL.
4) Bad or altered MSDTC install. This is fixed by uninstalling and
reinstalling MSDTC.

Test:
5) If you run just "begin distributed tran" from Query analyzer, see if it returns
completed succesfully. Also check versions of MSDTC On the client and remote
server. Please refer the following KB's for additional reference.        

MSDTC on server is not available

On the Database server , you need to turn the MSDTC service on. You can this by clicking START > SETTINGS > CONTROL PANEL > ADMINISTRATIVE TOOLS > SERVICES. Find the service called 'Distributed Transaction Coordinator' and RIGHT CLICK (on it and select) > Start.