I created a login in SQL Server 2005 for SQL Server Reporting Service training purpose. It seemed the login exceed the connection limit. There were 9 people in the training and some people got the connection errors. Sometimes the connection error went away after the user re-established the connection. Is there a default limit per login? If so, what is the default? Can I change it to unlimited? And how?
Thanks.
DanYeung
I created a login in SQL Server 2005 for SQL Server Reporting Service training purpose. It seemed the login exceed the connection limit. There were 9 people in the training and some people got the connection errors. Sometimes the connection error went away after the user re-established the connection. Is there a default limit per login? If so, what is the default? Can I change it to unlimited? And how?
Thanks.
DanYeung
Using System.Data.SqlClient is there a limit to the number of connections an application can have to a SQLMobile dB?
If
you have a look at the url below, it says "A device can only have a
small number of connections to an instance of SQL Server at any time"
Does this mean 2, or 8 or what?
Should a app try to use only 1 connection throughout, or can we get away with 2-3?
Is this the same on WM5.0/PPP2003?
Any advice in this area much appreciated!
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/sqlce/htm/_lce_sqlclient_705.asp
p.s. I'm not interested in connection pooling!
hi all,
I installed sql server developer edition on
xp box. created a new ssis project and
then when i right click on control flow
then click new connections
i see only limited connection available.
how can i install the other connection option
such as oledb. etc.
thanks
joey
Hello all,
I'm very new to this so excuse my naiveity...I have connected my desktop and laptop computer at home, on my desktop I have a series of db's under my default named instance <computername>/sqlexpress and I have created access logins using windows auth. When I connect to the desktop from the laptop using tcp:<computername>sqlexpressuserid I can only see the systemdb's...I am trying to view adventure works, just to see how this remote connection stuff works...any ideas?
Thanks,
Rob
I have connected my desktop and laptop computer at home, on my desktop I have a series of db's under my default named instance <computername>/sqlexpress and I have created access logins using windows auth. When I connect to the desktop from the laptop using tcp:<computername>sqlexpressuserid I can only see the system db's...I am trying to view adventure works, which I can see on the desktop fine using the same login...any ideas?
Thanks,
Rob
Hey guys,
I have created dtsx packages and i have one master package which would run couple of child packages.
1. I have all on my local machine.I want others who r working with me access these.So what do i need to do????for them to access...
2.what would be the best way in executing these packages.
the way it is set now is i had 3 connections set for each package.
a.Source connection
b.Destination Connection.
c.One more connection(which is used with in the process of execution)
Can i set connections in common for all the packages in one location.
3.i also want to set some logging settings to my master package too.and i want this package to run everyday automatically at a particular time how can this be done.
How can i do that.
please let me know.
I'm a newbie w/ SQL Server 2005.We will be connecting to SQL Server 2005 via JDBC. We have made thiswork by using an SQL Server account, rather than an Active Directory(AD) account, even though SQL Server IS in mixed authentication mode.Does mixed mode mean you can connect either way, but only using MS SQLServer interfaces (like Mgmt Studio), and not something like JDBC?Can we connect using JDBC w/ an AD login? If so, what form does thelogin take? Is the login not in 'DOMAIN/LOGIN' form?What is the best practice for this sort of thing? We would prefer touse AD logins (that a sysadmin creates) rather than having a separate(and probably equal) set of SQL Server logins.Any help appreciated.thanksaj
View 1 Replies View RelatedHi everyone!
Is it possible to control the number of simultaneous connections one login id can have?
I`d like to avoid my users to share their login ids and passwords to help them enter data into the system.
Thanks,
Mauricio
Hi,
Im getting this error when attempting to retrieve data from an sql database.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Cannot open database requested in login 'projectAllocations'. Login fails. Login failed for user 'sa'.
Source Error:
Line 13: objConn = New SqlConnection( "Server=LAB303-066NETSDK; Database=projectAllocations; User ID=sa;Password=mypassword")
Line 14: objCmd = New SqlCommand("SELECT * FROM project_descriptions", objConn)
Line 15: objConn.Open()
Line 16: objRdr = objCmd.ExecuteReader()
Line 17: While objRdr.Read()
Source File: C:finalyearproject2sample.aspx Line: 15
Please Help!! Im a beginner to this, so if anyone knows the answer, take baby steps when explaining. Thanks
Been looking through the forums for a solution to this problem.I already tried granting access through statements such as:exec sp_grantloginaccess N1'machineNameASPNET'But they don't seem to work.. i vaguely remember seeing somewhere a DOS command line statement that grants access to the ASPNET_WP and that fixed my problem before on another computer.. but this is a new computer and i forgot to write down the command.Can anyone help explain and propose a solution to my problem. Many thanxs.
View 9 Replies View RelatedI am using the MSDE to connect to my ASP.NET application. I get this error after clicking the login button of my login page. Anyone know why this would happen?
Thanks for any help,
Cannot open database requested in login 'DataSQL'. Login fails. Login failed for user 'serverASPNET'.
I have a search query on my page. However, since the resultset may be very large, I want to retreive only those results that are currently shown on the gridview page.Often a user won't browse through more than a couple of pages, so it's b-*** to send all the records to the client.I know that with a sp you can define that you want to retrieve the first or second x records, but I want to do this with SQL...is that possible?
View 1 Replies View RelatedI'd like to create a limited user on SQL Server 2005 to minimize any possible problems with my ASP.NET 2.0 website. My question is how limited can I make that user? What exactly does it need to be able to do?
(In case it makes a difference, my site utilizes membership, profiles, and roles, as well as some custom tables and stored procedures that I created.)
Thanks in advance for any advice! :-)
Dear all,
When I try to add the columns to 300,
But still can not over 300 column.
What's limited columns that MS SQL 7.0?
Thank you very much..
Gemmy.
hi all,
I have a field which name is Information
and it type is Varchar (8000),but some time data access than 8000 character, my client told me,make this field to store Unlimited data.
So how can i achive this task, i m using VS 2003 (ASP.NET with VB.NET) with SQL 2000.
Thanks
Shally
Hiya - this might be a bit of a simple question but please bear with me! I have looked reasonably hard for this but can not find an answer:
I have an MSSQL 2000 server running on PC-A and would like to limit certain users (e.g. admin) to be only able to logon when using the actual PC-A machine.
I am aware that you can acheive this in MySQL with the "insert into user (host, user, password) values (localhost, username, password);" command. Is there an equivalent way to do this in MSSQL?
Thanks for your help,
Alistair.
Hi All,
How can I give the user view rights only?
Hi,
I couldn't make a clear title without writing 2 lines.
My problem (I tried to find out through the archives):
this scenario will be used for several DBs on severals servers.
The remotes servers are not mine, I have only access to the backups files, I have no rights to setup a replication relationship.
I'm using a repository server with SQL 2005, and daily, I need to get the latest Full backup from an SQL2000 server, copy it to the repository server, restore it then delete the .bak file.
This is possible by using many different scripts (like .vbs to copy and rename the latest Full backups) then I use SQL job for daily restore.
Process is too long and the time estimated to start next task (and the backup keep growing).
Is there a way to do everything via SQL2005 script (job)?
Initially, the problem is that I have to do this with the Full backups every days (around 5 Dbs 8 Go in average). So if I can use the latest transaction log files (that would eliminate my first question).
The best way is to use log shipping as well but sql2005 is needed on both sides.
Hope I'm clear.
Thanks for any help!
I want to use TRIGGER_NESTLEVEL to test if a specific trigger is on the execution stack.
Normally, the table with the trigger is accessed by stored procedures and the user does not have any direct access to the table.
I am having problems doing this because I need to use OBJECT_ID to get the ID to pass to TRIGGER_NESTLEVEL. OBJECT_ID is unable to see the table when the stored procedure is run as that limited user account.
How can I use TRIGGER_NESTLEVEL in this scenario without granting this user permissions on the table?
Here is complete code to reproduce. The output is:
Nest level is: 1 (running as admin)
Nest level is: (null) (running as limited user)
The output that I want would have a 1 instead of (null). Hard coding the specific OBJECT_ID is not the solution that I am looking for, since I want to use identical database scripts on separate installs of my database.
Code Block
IF OBJECT_ID(N'Hello') IS NOT NULL DROP TABLE Hello
IF DATABASE_PRINCIPAL_ID(N'TestUser') IS NOT NULL DROP USER TestUser
IF OBJECT_ID(N'HelloProcedure') IS NOT NULL DROP PROCEDURE HelloProcedure
GO
CREATE TABLE Hello
(
ID INT PRIMARY KEY IDENTITY,
Data INT NULL
)
GO
CREATE TRIGGER HelloTrigger1
ON Hello
FOR INSERT
AS
SET NOCOUNT ON
UPDATE Hello SET Data = 1234 WHERE ID IN (SELECT ID FROM inserted)
GO
CREATE TRIGGER HelloTrigger2
ON Hello
FOR UPDATE
AS
SET NOCOUNT ON
PRINT N'Nest level is: ' + ISNULL(CAST(TRIGGER_NESTLEVEL(OBJECT_ID(N'HelloTrigger1')) AS NVARCHAR(128)), N'(null)')
GO
CREATE PROCEDURE HelloProcedure
AS
SET NOCOUNT ON
INSERT INTO Hello VALUES (0)
GO
CREATE USER TestUser WITHOUT LOGIN
GO
GRANT EXECUTE ON HelloProcedure TO TestUser
GO
EXEC (N'HelloProcedure') -- Prints: Nest level is: 1
GO
EXEC (N'HelloProcedure') AS USER = N'TestUser' -- Prints: Nest level is: (null)
GO
I got a report that seems to be limiting its displayed data to the first 8 records but not in the SQL statement itself. Although I have been working with SQL for many years I'm very new to Reporting Services so it may be something very simple, like perhaps a property that can be changed on the report for me to increase the amount of records the report is pulling from the database?
I don't seem to find any property set to 8 though.
Also the report is not pulling the records in the order the query pulls them, but in an apparent random order, I don't need to change this but it may help describe the report a bit better.
Hi all;
qrymillcgetbytype_app ( stored query in MS-Access) in my code :
MS-ACCESS Part:
query defination
PARAMETERS prmMillToolType Long;
SELECT MILLC.ID, MILLC.InsMillBdyID AS [Tool ID], MILLC.SubType, MILLC.Radius AS [End Radius], MILLC.CuttingDia AS [Tool Dia], MILLC.EffAxlCutLen AS [Effec Cut Length], MILLC.OverallLen AS [Overall Length], MILLC.HandOfCut AS [Hand Of Cut], MILLC.NoOfFlutes AS [No Of Flutes], MILLC.TmcID AS [Tool Class], MILLC.Comment, MILLC.Protrusion, MILLC.ShankDia, millC.ShoulderLen AS [Shoulder Length]
FROM MILLC
WHERE (((MILLC.[Mill Tool Type])=[prmMillToolType]) AND ((MILLC.[ON])=True))
ORDER BY MILLC.ID;
C++ PART
bstrSQL contain stored query name "qrymillcgetbytype_app"
hr = piRecordSet->put_Source(bstrSQL);
piRecordSet->Open(vNull, vNull, adOpenKeyset, adLockOptimistic, adCmdUnknown)
it opens fine
but when bstrSQL contain this query name " qrymillcgetbytype_app WHERE ID > -1 AND [Tool Dia] >= 0.000000 AND [Tool Dia] <= 5.000000 "
it displays the same output. Where clause won't work?
Pls help me out.
Hi there,
Here we have got a
asp.net application that was developed when database was
sitting on SQL server 6.5. Now client has moved all of their databases
to SQL server 2000. When the database was on 6.5 the previous
development team has used oledb connections all over. As the databases
have been moved to SQL server 2000 now i am in process of changing the
database connection part. As part of the process i have a login
authorization code.
Private Function Authenticate(ByVal username As String, ByVal password As String, ByRef results As NorisSetupLib.AuthorizationResult) As Boolean
Dim conn As IDbConnection = GetConnection()
Try
Dim cmd As IDbCommand = conn.CreateCommand()
Dim sql As String = "EDSConfirmUpdate" '"EDSConfirmUpdate""PswdConfirmation"
'Dim cmd As SqlCommand = New SqlCommand("sql", conn)
cmd.CommandText = sql
cmd.CommandType = CommandType.StoredProcedure
NorisHelpers.DBHelpers.AddParam(cmd, "@logon", username)
NorisHelpers.DBHelpers.AddParam(cmd, "@password", password)
conn.Open()
'Get string for return values
Dim ReturnValue As String = cmd.ExecuteScalar.ToString
'Split string into array
Dim Values() As String = ReturnValue.Split(";~".ToCharArray)
'If the return code is CONTINUE, all is well. Otherwise, collect the
'reason why the result failed and let the user know
If Values(0) = "CONTINUE" Then
Return True
Else
results.Result = Values(0)
'Make sure there is a message being returned
If Values.Length > 1 Then
results.Message = Values(2)
End If
Return False
End If
Catch ex As Exception
Throw ex
Finally
If (Not conn Is Nothing AndAlso conn.State = ConnectionState.Open) Then
conn.Close()
End If
End Try
End Function
''' -----------------------------------------------------------------------------
''' <summary>
''' Getting the Connection from the config file
''' </summary>
''' <returns>A connection object</returns>
''' <remarks>
''' This is the same for all of the data classes.
''' Reads a specific
connection string from the web.config file for the service, creates a
connection object and returns it as an IDbConnection.
''' </remarks>
''' -----------------------------------------------------------------------------
Private Function GetConnection() As IDbConnection
'Dim conn As IDbConnection = New System.Data.OleDb.OleDbConnection
Dim conn As IDbConnection = New System.Data.SqlClient.SqlConnection
conn.ConnectionString = NorisHelpers.DBHelpers.GetConnectionString(NorisHelpers.DBHelpers.COMMON)
Return conn
End Function
in the above GetConnection() method i
have commented out the .net dataprovider for oledb and changed it to
.net dataprovider for SQLconnection. this function works fine. But in
the authenticate method above at the line
Dim ReturnValue As String = cmd.ExecuteScalar.ToString
for some reason its throwing the below error.
Run-time exception thrown : System.Data.SqlClient.SqlException - @password is not a parameter for procedure EDSConfirmUpdate.
If i comment out the
Dim conn As IDbConnection = New System.Data.SqlClient.SqlConnection
and uncomment the .net oledb provider,
Dim conn As IDbConnection = New System.Data.OleDb.OleDbConnection
then it works fine.
I also have changed the webconfig file as below.
<!--<add
key="Common" value='User ID=**secret**;pwd=**secret**;Data
Source="ESMALLDB2K";Initial Catalog=cj_common;Auto
Translate=True;Persist Security Info=False;Provider="SQLOLEDB.1";'
/>-->
<add key="Common" value='User ID=**secret**;pwd=**secret**;Data Source="ESMALLDB2K";Initial Catalog=cj_common;' />
Please help. Thanks in advance.
Im using the Xquery:SELECT @xmlDoc.query('
for $item in (/Collection/Content)
where $item/Html/root/DocInfo/Webinar = "White Paper"
order by $item[1]/Html[1]/root[1]/DocInfo[1]/Title[1] ascending
return $item
');
I only want to return the TOP 5 nodes (not the entire nodelist). What is the FLOWR expression or simplest way to return the limited nodeset?
Thanks
I am using ORDER BY NEWID() to return random record from sql database. how do i go about returning only 5 random records instead of all records.
Thanks.
Hi, I am trying to create a SQL Server user which would only have write permissions. This account would not be able to read, drop, alter, delete etc. It would simply be used to write data.
Is there a way to configure a user like this without scripting the permissions for each table. The DB has some dynamically generated tables, so the users privillges would ideally extend to those newly added tables without having to rerun some kind of script. Is this possible?
Hi, I am trying to create a SQL Server user which would only have write permissions. This account would not be able to read, drop, alter, delete etc. It would simply be used to write data.
Is there a way to configure a user like this without scripting the permissions for each table. The DB has some dynamically generated tables, so the users privillges would ideally extend to those newly added tables without having to rerun some kind of script. Is this possible?
Hi, we have a generic way to call DTS
CREATE procedure dbo.run_our_dts @Our_dts_namevarchar(8000)
as
declare@retcodeint
declare @run_sqlvarchar(8000)
select @run_sql = 'dtsrun /S '+@@servername+' /E /N '+@Our_dts_name
print @run_sql
exec @retcode=master..xp_cmdshell @run_sql
if @retcode<>0
begin
Raiserror('DTS RUN ERROR',16,1)
end
it works fine but ,Only users with SysAdmin privileges can execute CmdExec and ActiveScripting job steps
we don't want to disable this setting
Does anyone now extended procedure
similar to xp_cmdshell, with abilty to
start dtsrun ?
Thank you
Alex
Hi
Does anyone here know why xp_fixeddrives with a limited user returns an empty result set on 2005?
In essence I want to create a report which shows disk space remaining, an upgrade of an old one. It works fine on 2000, but seems to need sysadmin rights on 2k5. I would appreciate it if someone can suggest how to get it working - or an alternative solution!
Cheers
someone recently told me that their bcp export of a table with over MAX_INT rows failed because BCP hit an overflow in the internal counter that bcp uses to tell you how many rows it's exported. You know how it write "rows copied to host file: 40000" to stdout? That number got to MAX_INT, then went negative, then BCP crashed. So they had to start all over and break things up with the -F, -L flags.
I was rather surprised that a program that claims to handle "bulk" data would use a 32 bit int to accumulate the count. I suppose the original sybase dev figured nobody would ever want to export more than 2b rows.
just wondering, has anyone seen this before?
I am about to kick off a similar large export and am planning to break it up so as not to hit this, but that also means I won't be able to verify that it is indeed a problem.
elsasoft.org
I am trying to insert records via ASP, with a user that has only writeaccess to the table (db_datawriter, db_denydatareader).That way, if the server is ever compromised, the access informationstored in the source code's connection string will not allow anybody toactually read the database.The problem is that I would like to use ADO methods to insert the data(to prevent SQL injections), but I can't seem to get the rightconnection. It works in plain SQL, but I'd rather not use it.My current code looks like this:connection="Provider=SQLOLEDB.1;User ID=DBwriter;Password=XXX;DataSource=MYSERVER;Initial Catalog=MYDB;"set conn=server.createobject("ADODB.Connection")conn.mode=2 ' adModeWriteconn.open connectionSet rs = Server.CreateObject ("ADODB.Recordset")rs.Open "MYTABLE", conn, adOpenKeySet, adLockPessimistic, adCmdTablers.AddNewrs.Fields("testfield") = "TESTDATA"rs.UpdateAnd the error I get is:Microsoft OLE DB Provider for SQL Server (0x80040E09)SELECT permission denied on object 'MYTABLE', database 'MYDB', owner'dbo'.(If I use a User with read privileges in the connection stringeverything works fine.)
View 3 Replies View RelatedI have a table with entries tied to a membership database. The problemis that I want to select a limit of sixteen entries per member, perday, where some members have 16+ entries per day.I have this so far ( which I've simplified for this post)SELECT dbo.members.firstname, dbo.members.lastname,dbo.entries.gameDayFROM dbo.members INNER JOINdbo.entries ON dbo.members.memberID =dbo.entries.memberIDIf it's day 5, each member should have 80 total.How can I change this to select only 16 entries for each member, makingsure it's 16 per day based on dbo.entries.gameDay?Thanks for your help.
View 2 Replies View RelatedHi all,
I have a problem just today after added some tasks. I can only get the "OnPrevalidate" logging info only in both sysdts90 and a text file. Have I messed up with some setting?
Thanks!