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
Hi All,
How can I give the user view rights only?
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
I cannot create a measure that returns results for dates that do not exist in the fact table despite the fact that the components included in the measure contain valid results for these same dates.Creature a measure that counts the number of days where the "stock qty" is below the "avg monthly sales qty for the last 12 months" (rolling measure).Here is the DAX code I have tried for the measure (note that filter explicitly refers to the date table (called Calendar) and not the fact table):
StkOutCnt:=CALCULATE (
COUNTROWS ( VALUES ( Calendar[DateKey] ) ),
FILTER (
Calendar,
[Stock qty] < [Avg Monthly Sales Qty L12M@SKU]
)
)
Below you can see the sub measures (circled in red) are giving results for all days in the calendar.Highlighted in yellow are dates for which the StkOutCnt measure is not returning a result. Having investigated these blank dates, I am pretty confident that they are dates for which there are no transactions in the fact table (weekends, public holidays etc...).why I am getting an "inner join" with my fact table dates despite the fact that this is not requested anywhere in the dax code and that the two sub measures are behaving normally?
The DBA at our location is demanding local admin (windows) right's to the box so he can function. Right now when he logs in i have given him right's to the inetpub directory, sql directory, i have set him as a sysadmin on sql2005 and gone into the http:\localhost
eports and set him up as a system manager and under site priveledges set him as a sys admin. When he tries to login and configure the report server he gets the following error:
Title-Reporting services configuration manager
Error-There was an error refreshing the UI. bla bla bla
A WMI error has occurred and no additional error information is availiable
Title-Reporting services configuration manager
Error-There was an error while switching panels. The most likely cause is an error retrieving WMI properties. bla bla bla
A WMI error has occurred and no additional error information is availiable
then when he's in sql server 2005 surface area configuation
Title-Surface Area Configuration
Error-Access denied (system.management)
Is there any documentation or anythign anyone can tell me that i can do to give this DBA full access to configure and admin the SQL portion of his system without giving him admin rights to the OS???
Please help!!
Thanks for any time anyone has taken to review this thread!!
Hi everybody,
Does anyone knows the alternative for xp_fixeddrives or what privilege should i grant to a normal user to execute xp_fixeddrives which returns the disc space details in sql server 2005.
Thanks in advance.
hi,
xp_fixeddrives return 2 set of records. How do i store this result into a table?
Thanks
I have a monitoring site setup that looks at different aspects of the database and the health, and the latest addition is checking drive space.
I know you can use master..xp_fixeddrives to return all the drives and space that is available. But when I run this through an OpenQuery it errors out:
"select * from openquery (test_server,'exec master..xp_fixeddrives')"
"Cannot process the object "exec master..xp_fixeddrives". The OLE DB provider "SQLNCLI" for linked server "test_server" indicates that either the object has no columns or the current user does not have permissions on that object."
Any suggestions??
Thanks.
Hi,
My production server is win2000 sql 2000 sp2.
Couple of days back when I tried to take the backup of the main database through EM (right click on database, all tasks--> backup database ), it hung at the point when I tried to select the drive for the device. However, I was able to take backup through scheduled task at that point.
Anyway, I still see the open connection (after two weeks) for that in the current activity window.
Here is the detail about the process.
'EXECUTE master.dbo.xp_fixeddrives 2'
I have read about it that it is a known sql bug since sql 7.0. But what is the solution to work around it? is it ok to kill this process? what are the possible consequences? Or how can I get rid of this SPID?
The next planned reboot for the server is 31st January. So rebooting server is not an option.
Thanks,
Shaili
hi,
sp_spaceused return 2 set of results. How do i store this result into a table?
Thanks
We are on Windows 2000, SQL Server 2000, and a SAN. Our data and backups are located on the Storage Area Network.
I am attempting to restore a database with a backup on the SAN through Enterprise Manager. I selected 'From Device', 'Selected Devices'. SQL appears to hang. After looking at Current Activity, I have identified that it is running 'EXECUTE master.dbo.xp_fixeddrives 2'.
Are there any known problems with SQL Server 2000 and SAN? Has anyone encountered this error?
Thanks
Kim
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,
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 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 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 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 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.
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?
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 RelatedUsing 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
Hi 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!
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