Runtime error 3704

Hi,

I've created an application that runs on several pc's. However, on one particular pc, when I run the following code to test the connection to the SQL server, it generates the error "Runtime error '3704'" and it happens only when it hits the Form1.Users.Movefirst section.

I've checked all the user permissions on the server and dont seem to find any difference there.

code:-

Option Explicit
Public Db As New ADODB.Connection
Public Users As New ADODB.Recordset

Private Sub Command1_Click()

If Not Form1.Users.BOF Then
Form1.Users.MoveFirst
End If

With Form1.Users
Do While Not Form1.Users.EOF
Debug.Print Form1.Users.Fields(0)
Form1.Users.MoveNext
Loop
End With

End Sub

Private Sub Form_Load()

On Error Resume Next
Set Db = Nothing

Db.Open _
"Provider=SQLOLEDB.1;Server=File;" & _
"Database=Cash Management;Trusted_Connection=Yes"

'Open the recordsets
With Form1.Users
.CursorLocation = adUseClient
.CursorType = adOpenDynamic
.LockType = adLockOptimistic
.ActiveConnection = Form1.Db
.Source = "Select * from Users order by userid"
.Open "SET NOCOUNT ON"
End With

End Sub

ThanksIt is very simple. You are running "SET NOCOUNT ON" only. What MDAC version do you use?|||Hi.. Thanks for your reply. Normally, my .open has nothing after it. Only after I searched on the net, did I add that "Set NoCount On", but it didn't help. Sorry to sound ignorant, but how do I tell what ver of MDAC I'm using ?

Carl|||Q1. What MDAC version am I using?
A1. Use regedit.exe, see branch "HKEY_LOCAL_MACHINE\Software\Microsoft\DataAccess" node "FullInstallVer"|||It looks like 2.70.7713.4.

Tks..|||You added "SET NOCOUNT ON" for test purposses only, remove it.
In your code, after the part where you try to open a connection,
add a check (+msgbox) to know if it is really connected.
It can be a domain security problem.|||Ok - I checked the registry on the pc that I was installing on and I see the version of MDAC is 2.53.6200.1. This is the same as another pc that is working.

If this is not the problem, I suspect that it is a permissions problem, but I have no idea where to check as this user has been registered exactly the same as the other users that work.

Thanks|||Run (for W9x)
command /K net config
from Start->Run commandline on the client machine and you will see his logon info (or ask him to do it and send results).|||My email address is alkemac@.hotmail.com. Can you please send an email and I will reply with screen shots of the different screens, including the SQL Svr user registration screen ?

Thanks|||It was a user default database problem. The user solved it by himself.|||What do you mean by "default database problem"? Can you explain how the user solved it himself? We have a similar problem with a VB application that opens an Access database. It runs on most computers, but not some. One gets the 3704 runtime error. Thanks.sql

RunningValue funtion doesn't seem to work with a chart data region

I am trying to build a chart where on of the data values represents a running
total. I also created a table data region to show the tablular
representation of the chart data.
The running value function requires a scope parameter representing the
group. I specify the name of the category group for the scope.
When I run the report the tablular data shows a correct running total.
However, the chart doesn't. I don't get any errors but the chart running
total values display as if a simple grouping and sum were done on the values.
Is this a known bug or am I doing something wrong.AS i know the RunningValue & RowNumber don't work on chart only on table...
"john" wrote:
> I am trying to build a chart where on of the data values represents a running
> total. I also created a table data region to show the tablular
> representation of the chart data.
> The running value function requires a scope parameter representing the
> group. I specify the name of the category group for the scope.
> When I run the report the tablular data shows a correct running total.
> However, the chart doesn't. I don't get any errors but the chart running
> total values display as if a simple grouping and sum were done on the values.
> Is this a known bug or am I doing something wrong.

Running Totals in a Report Builder Column Chart

I've created a Report Builder Column chart with months as the x-axis
(category) and revenue as the y-axis (value). This correctly displays
the revenue for each month, but I'd like to display a running total of
the revenue for the year.

I'm currently displaying:
Jan $2
Feb $1
Mar $4

I want to display:
Jan $2
Feb $3
Mar $7

I know this is possibly using the RunningTotal formula in Reporting
Services, but I'd like to do this in Report Builder so the users can
change and create their own graphs with running totals. Is there a way to do this in Report Builder?

Thanks.

I have been able to get the running total in a TABLE by right clicking on a field, selecting formula, and inserting the following string:

=Format(RunningValue(Fields!Count.Value,Sum,Nothing),"")

The format statement allows formatting "C" (at the end would produce currency (Dollar sign, etc.), not sure what "" (I didn't want currency).

I would also like to know how to do this in a chart....

Running Totals at group level

Hi,

I have a report that groups data by day - I have created a running value to

show cumulative sales for Monday, Monday+Tuesday, Monday+Tuesday+Wednesday etc.

I have a group below this level that expands out the customer. I

wish to create a cumulative sales value for that customer for that day of the

week. i.e. Customer A Monday value, Customer A Monday+Tuesday

value.

When you use RunningValue with scope Nothing, ie.

RunningValue(Fields!nett_value.Value, Sum, Nothing), the value returned on

Tuesday is Total Monday value plus each customer Tuesday value cumulative

adding. If you use scope at customer group level it cumulatively

adds that day’s customer totals.

I need to recreate:
Daily

Cumulative

Monday

100 100

Customer A 50

50

Customer B 25

25

Customer C 25

25

Tuesday

100 200

Customer A 50

100

Customer B 25

50

Customer C 25

50

Any ideas?

Thanks

While I am having fun trying to work this out...

Any help would be great!!!

Thanks!

Running SSIS/Maintenance packages using Proxies (SQL 2005)

I am trying to run SSIS and Maintenance Packages under a different username.
I have created the AD account, created a credential for that account, create
d
a proxy which uses that credential and given the principal access to the
proxy. However, when I try to run the package, it gives error "Unable to
start execution of step 1 (reason: Error authenticating proxy
Mydomain\Myaccount, system error: Logon failure: unknown user name or bad
password.). The step failed.".
In the sql log, I get this error "[298] SQLServer Error: 22046, Encrypti
on
error using CryptProtectData. [SQLSTATE 42000]"
I have even tried making the principle and the credential account sysadmins,
but it still gives this error. It seems the password for the credential is
stored using the database or server master key, which only the account
running the sql service has access to. I suspect since I am trying to run it
under a different account, it's not able to decrypt the password for the
credential.
The credential works fine when I use EXECUTE AS in sql backup statements, so
there's nothing wrong with the password.
Has anyone tried using a proxy/crendential to run SSIS packages ?PS: The AD account does have the 'Logon as a Batch Job' rights on the server
as per BOL.
"Pranil" wrote:

> I am trying to run SSIS and Maintenance Packages under a different usernam
e.
> I have created the AD account, created a credential for that account, crea
ted
> a proxy which uses that credential and given the principal access to the
> proxy. However, when I try to run the package, it gives error "Unable to
> start execution of step 1 (reason: Error authenticating proxy
> Mydomain\Myaccount, system error: Logon failure: unknown user name or bad
> password.). The step failed.".
> In the sql log, I get this error "[298] SQLServer Error: 22046, Encryp
tion
> error using CryptProtectData. [SQLSTATE 42000]"
> I have even tried making the principle and the credential account sysadmin
s,
> but it still gives this error. It seems the password for the credential is
> stored using the database or server master key, which only the account
> running the sql service has access to. I suspect since I am trying to run
it
> under a different account, it's not able to decrypt the password for the
> credential.
> The credential works fine when I use EXECUTE AS in sql backup statements,
so
> there's nothing wrong with the password.
> Has anyone tried using a proxy/crendential to run SSIS packages ?

Running SSIS package from asp.net application failed

I have an asp.net web application and a web service (both of them are created in VS 2005 - asp.net 2.0). They are located on the same web server. In both web.config files, I have set <authentication mode="Windows"/> and <identity impersonate="true"/>. Also, configured the IIS settings to use Integrated Windows Authentication and unchecked the Anonymous access (for both). The web service is called from the web app, so I have to pass credentials for authentication to the web service. The web service loads and executes a SSIS package. The package and all the other sql objects are located in the sql server 2005 (windows server 2003 - the same server as the web server).

When run the web service from develop environment (vs. 2005), I get whatever I expected. When call it from web application, however, the package failed (no error message).

In the SSIS package, there are three connection managers –

· A: Microsoft OLE DB Provider for Analysis Services 9.0 à connectionType=OleDbConnection

· B: .Net Providers \ SqlClient Data Provider à connection type=SqlConnection

· C: Native OLE DB \ Microsoft OLE DB Provider for SQL Server à connectionType=OLEDB

After ran the web application and check the sql database, I can tell that the package was reached and when through the first two steps (clear some records in table_1 and extract some records from table_2 ) which relate to the connection manager B – ADO.Net connection. The remaining steps failed which are related to the connection managers A & C.

From SSIS package log file, found that the user credentials (domain and username) were correctly passed from web service to sql server 2005 at the first two events, but the credentials (or operator) changed from domainABC\user123 to NT AUTHORITY\NETWORK SERVICE after packageStart. Then, it complains … either the user, domainABC\serverName$, does not have access to the database, or the database does not exist.

I think the credentials are passed ok but some setting related to the Analysis services are not correct - complaining start from there. Any clues?

Please help and thank you all!

FYI: The problem was solved.

What I did are list as below:

A. We created a new web app pool and pointed the both web application and web service to this web app pool (which was configured not use default identity - Predefined: Network Service but use Configurable - created a new use name).

B. Under SQL server , added this new user and assign the certain rights to it.

C. In the web application and web service, set impersonate=false (instead of true)

D. In the SSIS package, we also have a flat file destination connection which is to write the output to .txt file. I give the read & write rights to this new use. then, it works.

Running SQLServer and SQLServer Agent as Power User

We're trying to limit the number of user accounts with Admin level permissio
n
on our Win2K servers, especially SQL servers. We have created a domain level
account to run SQLServer and SQLAgent. We'd like to limit it to Power User
status instead of Admin status on the servers, but we cannot seem to start
and stop the services from SEM with only Power User status. We've checked
registry key permissions and everything seems to be configured properly. Is
this configuration even possible? Or does this account NEED to be local admi
n
on the server? Help would be appreciated. Thanks.The account that starts the services needs to have the "log on as a service"
right. Without this MSSQLServer and MS SQL Server Agent will not start.
It would also be more secure to use a domain account for this rather than a
local account as SQL then benefits from the integrated security of Windows
2000.
Also why would you want end users to have admin rights on the server at all?
This defeats the object of system security and resource accessibility. It
is best that they are Doman Users only then assign access rights to shares
on the servers.
HTH
Regards
Dazza
"gbledsoe" <gbledsoe@.discussions.microsoft.com> wrote in message
news:72CDD311-3C73-480E-9734-3E6F0E76DB09@.microsoft.com...
> We're trying to limit the number of user accounts with Admin level
> permission
> on our Win2K servers, especially SQL servers. We have created a domain
> level
> account to run SQLServer and SQLAgent. We'd like to limit it to Power User
> status instead of Admin status on the servers, but we cannot seem to start
> and stop the services from SEM with only Power User status. We've checked
> registry key permissions and everything seems to be configured properly.
> Is
> this configuration even possible? Or does this account NEED to be local
> admin
> on the server? Help would be appreciated. Thanks.|||We've following the instructions in MS article 283811 and ensured that the
account has all necessary extended user rights, such as act as part of
operating system, logon as batch job, logon as service. The fundamental
question is whether the account can run as Power User or does it need to be
Administrator? If it does not NEED to be Administrator, what other
configuration is necessary to let us use that account to stop and start the
SQLServer service, since Power User does not seem to have the rights. Thanks
.
"Dazza" wrote:

> The account that starts the services needs to have the "log on as a servic
e"
> right. Without this MSSQLServer and MS SQL Server Agent will not start.
> It would also be more secure to use a domain account for this rather than
a
> local account as SQL then benefits from the integrated security of Windows
> 2000.
> Also why would you want end users to have admin rights on the server at al
l?
> This defeats the object of system security and resource accessibility. It
> is best that they are Doman Users only then assign access rights to shares
> on the servers.
> HTH
> Regards
> Dazza
>
> "gbledsoe" <gbledsoe@.discussions.microsoft.com> wrote in message
> news:72CDD311-3C73-480E-9734-3E6F0E76DB09@.microsoft.com...
>
>|||gbledsoe wrote:
> We're trying to limit the number of user accounts with Admin level permiss
ion
> on our Win2K servers, especially SQL servers. We have created a domain lev
el
> account to run SQLServer and SQLAgent. We'd like to limit it to Power User
> status instead of Admin status on the servers, but we cannot seem to start
> and stop the services from SEM with only Power User status. We've checked
> registry key permissions and everything seems to be configured properly. I
s
> this configuration even possible? Or does this account NEED to be local ad
min
> on the server? Help would be appreciated. Thanks.
It should be, although not all sql feature are available. I am running
multiple instances with different plain domain user accounts. When you are
not sure about registry, user and ntfs permission change the account using
the enterprise manager.
When you need the proxy account to run scheduled dts packages create a
separate account for the sql agent service and make it local admin, unless
someone here can explain how to accomplice this without local admin rights.
have a look at this one:
http://support.microsoft.com/defaul...;283811&sd=tech
Hans

Running SQL Server job from Access

I rewrote several Access programs to run as a single SQL stored procedure on
SQL Server. A job created in DTS executes the SQL stored procedure. This
job is executed from a batch file as listed below:
.....
Dim RetVal
Dim strPathFile
strPathFile = "C:\BTExe.cmd"
RetVal = Shell(strPathFile,1)
......
As soon as the code is submitted it returns the return value. Is there any
way to have the job operate as if it was being run within Access ? For
example, display the hour glass until the job completes. Is there a
recommended procedure to run jobs from Access on SQL server, which prevents
the user from using the system until the job completes ?"rmcompute" <rmcompute@.discussions.microsoft.com> wrote in message
news:143795DE-B03D-418E-99D3-A946461A3016@.microsoft.com...
>I rewrote several Access programs to run as a single SQL stored procedure
>on
> SQL Server. A job created in DTS executes the SQL stored procedure. This
> job is executed from a batch file as listed below:
> .....
> Dim RetVal
> Dim strPathFile
> strPathFile = "C:\BTExe.cmd"
> RetVal = Shell(strPathFile,1)
> ......
> As soon as the code is submitted it returns the return value. Is there
> any
> way to have the job operate as if it was being run within Access ? For
> example, display the hour glass until the job completes. Is there a
> recommended procedure to run jobs from Access on SQL server, which
> prevents
> the user from using the system until the job completes ?
>
Why don't you just run the stored procedure directly from Access. You can
do this with a pass-through query or in code.
David|||Can you give an example of the code and would the procedure run the same wa
y
a procedure runs in Access in that the user cannot use the system until the
procedure is complete ?
"David Browne" wrote:

> "rmcompute" <rmcompute@.discussions.microsoft.com> wrote in message
> news:143795DE-B03D-418E-99D3-A946461A3016@.microsoft.com...
> Why don't you just run the stored procedure directly from Access. You ca
n
> do this with a pass-through query or in code.
> David
>|||Hi rmcompute
Here is a real live example. Access will stop when it hits cmd.execute and
not continue until the stored Procedure executes.
Sub DoSomething
dim cmd as ADODB.Command
dim conn as ADODB.Connection
Set cmd = New ADODB.Command
'Assuming I want to connection to the SQLAlerts database and my sa
password is "sapassword".
strconnect = GetConnectionString(strSQLServerName, "SQLAlerts",
"sa", "sapassword", False)
set conn = New ADODB.Connection
conn.ConnectionTimeout = 15
conn.Open strconnect
Set cmd.ActiveConnection = conn
cmd.CommandText = " record_sql_server_database_backup_histor
y" '
My Stored Procedure Name
cmd.CommandType = adCmdStoredProc
cmd.Parameters.Append cmd.CreateParameter("@.sql_server_database_id",
adInteger, adParamInput, , lngSqlServerDatabaseID)
cmd.Parameters.Append cmd.CreateParameter("@.run_date", adDBDate,
adParamInput, , Now)
cmd.Parameters.Append cmd.CreateParameter("@.last_full_backup",
adDBDate, adParamInput, , dtLastFullBackup)
cmd.Parameters.Append
cmd.CreateParameter("@.last_incremental_backup", adDBDate, adParamInput, ,
dtLastIncrementalBackup)
cmd.Parameters.Append
cmd.CreateParameter("@.last_transactionlog_backup", adDBDate, adParamInput, ,
dtLastTransactionLogBackup)
cmd.Execute
END SUB
Function GetConnectionString(strSQLServerName As String, strDatabaseName As
String, strUsername As String, strPassword As String, blnNTAuthentication As
Boolean)
If blnNTAuthentication Then
GetConnectionString = "Provider=SQLOLEDB.1;Integrated
Security=SSPI;Persist Security Info=False;Initial Catalog=" &
strDatabaseName & ";Data Source=" & strSQLServerName
Else
Dim strPassword 'As String
GetConnectionString = "Provider=SQLOLEDB.1;Password=" &
strPassword & ";Persist Security Info=True;User ID=" & strUsername &
";Initial Catalog=" & strDatabaseName & ";Data Source=" & strSQLServerName
End If
End Function
-Dick Christoph
"rmcompute" <rmcompute@.discussions.microsoft.com> wrote in message
news:06EB646F-DD8F-40C6-AEA2-B612E6D7A3AB@.microsoft.com...
> Can you give an example of the code and would the procedure run the same
> way
> a procedure runs in Access in that the user cannot use the system until
> the
> procedure is complete ?
>
> "David Browne" wrote:
>