Monday, May 20, 2013

[SQL Server 2008 issues] Attaching a database in SSMS without sysadmin role gives error

[SQL Server 2008 issues] Attaching a database in SSMS without sysadmin role gives error

Attaching a database in SSMS without sysadmin role gives error

Posted: 19 May 2013 07:13 PM PDT

I am accessing my sql server 2008 r2 express server using SSMS. I have a user which has got db_owner permission for all database and all permissions in server role other than sysadmin.When this user tries to attach a database by using ssms(by right clicking on database node and then clicking on attach) the error message comes like TITLE: Microsoft SQL Server Management Studio------------------------------Cannot show requested dialog.------------------------------ADDITIONAL INFORMATION:Parameter name: nColIndexActual value was -1. (Microsoft.SqlServer.GridControl)------------------------------BUTTONS:OK------------------------------If the same user uses t-sql command such as below to attach database then it works fine.USE [master]GOCREATE DATABASE [hgnx] ON ( FILENAME = N'E:\db\hgnx.mdf' ),( FILENAME = N'E:\db\hgnx_log.ldf' ) FOR ATTACHGOSo what is the error causing me while attaching database in SSMS? How to solve it?

How to select this

Posted: 19 May 2013 05:05 PM PDT

Below are sample tables and sample date, please help--create table c (c_id integer, p_id varchar(10))--create table f (c_id integer,f_id integer,e_dt date,amt_gross integer,amt_matched integer)--create table bf(bf_id integer,actual_dt date,c_id integer,p_id varchar(10))--create table bfm(bfm_id integer, bf_id integer,f_id integer,amt integer,c_id integer,alloc_dt date)/*insert into c values(1,'A')insert into c values(2,'B')insert into c values(3,'A')insert into c values(4,'C')*//*insert into f values(1,1,'1/1/2013',100,100)insert into f values(1,2,'2/1/2013',100,0)insert into f values(1,3,'3/1/2013',200,0)insert into f values(1,4,'4/1/2013',50,0)insert into f values(2,5,'1/10/2013',400,400)insert into f values(2,6,'2/10/2013',500,0)insert into f values(2,7,'3/10/2013',100,0)insert into f values(3,8,'5/1/2013',100,0)insert into f values(3,9,'6/1/2013',300,0)*//*insert into bf values (1,'1/10/2013',1,'A')insert into bf values (2,'1/25/2013',1,'A')insert into bf values (3,'3/01/2013',1,'A')insert into bf values (5,'01/20/2013',2,'B')*//*insert into bfm values(1,1,1,25,1,'1/10/2013')insert into bfm values(2,2,1,25,1,'1/27/2013')insert into bfm values(3,3,1,50,1,'3/01/2013')insert into bfm values(5,5,5,400,2,'01/20/2013')*/Below is query that I am trying but want to select different data:declare @runDate dateset @runDate = '6/11/2013'SELECT c.c_id, f.e_dt,@runDate as RunDate,bfm.alloc_dt,bfm.amt as match,f.amt_gross as gross,case when bfm.amt is not null then datediff(day,bf.actual_dt,@runDate) elsedatediff(day,f.e_dt,@runDate) end as Daysfrom c cinner join f f on c.c_id = f.c_idleft join bfm bfm on bfm.f_id = f.F_idleft join bf bf on bf.bf_id = bfm.bf_idwhere f.e_Dt <= @runDateorder by c_id, days descso for c_id = 1 none of rows having MATCH and GROSS equal, so minimum e_dt record should come.for c_id = 2, 400 amount row is matching so, I want to select next minimum e_dt for c_id = 2 and ignore row with 400 amount.for c_id = 3, there are no rows having MATCH and GROSS same, so minimum e_dt record should comecurrent result is like this:1 2013-01-01 2013-06-11 2013-01-10 25 100 152 --- this one should come1 2013-01-01 2013-06-11 2013-01-27 25 100 1371 2013-02-01 2013-06-11 NULL NULL 100 1301 2013-03-01 2013-06-11 NULL NULL 200 1021 2013-01-01 2013-06-11 2013-03-01 50 100 1021 2013-04-01 2013-06-11 NULL NULL 50 712 2013-01-10 2013-06-11 2013-01-20 400 400 1422 2013-02-10 2013-06-11 NULL NULL 500 121--- this one should come2 2013-03-10 2013-06-11 NULL NULL 100 933 2013-05-01 2013-06-11 NULL NULL 100 41--- this one should come3 2013-06-01 2013-06-11 NULL NULL 300 10I want only highlighted records as explained above.Any help or suggestions are highly appreciable.TIA,Surinder

How to select max based on condition.

Posted: 19 May 2013 12:10 PM PDT

Hi Friends,I want to select maximum date data based on some condition i.e.if alloc_dt column has value for c_id, then I want to select maximum date row for that particular c_idand if all rows have NULL for c_id, then I want to select maximum DAYS row for that particular c_id.Below are sample tables and sample date, please help--create table c (c_id integer, p_id varchar(10))--create table f (c_id integer,f_id integer,e_dt date,amt integer)--create table bf(bf_id integer,actual_dt date,c_id integer,p_id varchar(10))--create table bfm(bfm_id integer, bf_id integer,f_id integer,amt integer,c_id integer,alloc_dt date)/*insert into c values(1,'A')insert into c values(2,'B')insert into c values(3,'A')insert into c values(4,'C')*//*insert into f values(1,1,'1/1/2013',100)insert into f values(1,2,'2/1/2013',100)insert into f values(1,3,'3/1/2013',200)insert into f values(1,4,'4/1/2013',50)insert into f values(2,5,'1/10/2013',400)insert into f values(2,6,'2/10/2013',500)insert into f values(2,7,'3/10/2013',100)insert into f values(3,8,'5/1/2013',100)insert into f values(3,9,'6/1/2013',300)*//*insert into bf values (1,'1/10/2013',1,'A')insert into bf values (2,'1/25/2013',1,'A')insert into bf values (3,'3/01/2013',1,'A')--insert into bf values (4,'3/01/2013',3,'A')insert into bf values (5,'01/20/2013',2,'B')*//*insert into bfm values(1,1,1,25,1,'1/10/2013')insert into bfm values(2,2,1,25,1,'1/27/2013')insert into bfm values(3,3,1,50,1,'3/01/2013')--insert into bfm values(4,4,8,50,3,'3/01/2013')insert into bfm values(5,5,5,400,2,'01/20/2013')*/--select * from f--select * from bfm--select * from bfdeclare @runDate dateset @runDate = '6/11/2013';select c.c_id,c.p_id, f.e_dt,@runDate as RunDate,f.f_id,f.amt, bf.actual_dt,bf.bf_id,bf.c_id as bfC_id, bfm.bfm_id,bfm.f_id as bfmF_id,bfm.amt,bfm.alloc_dt, case when bfm.amt is not null then datediff(day,bf.actual_dt,@runDate) else datediff(day,f.e_dt,@runDate) end as Days from c inner join f f on c.c_id = f.c_id left join bfm on bfm.f_id = f.F_id left join bf on bf.bf_id = bfm.bf_id where f.e_Dt <= @runDate order by c.c_id, days descso for c_id =1 row with alloc_dt =2013-03-01 should be selected becoz that is maximum date for that c_idfor c_id = 2, row with alloc_dt = 2013-01-20 should be selected becoz that is maximumfor c_id = 3, row with maximum days i..e 41 should be selected because all alloc_dt rows are NULL, so I want maximum days row in this case.any comments/suggestions are highly appreciable.TIA,Surinder Singh

msdb acronym?

Posted: 19 May 2013 10:57 AM PDT

Seeking pointless trivia & failing to find it; is the acronym of msdb known? I can't recall seeing/hearing it in books/videos I've seen & Google comes up short.The only thing I found on Google was "Multi Source Database" although this seems a little strange & might be a non-MSSQL answer. Personally I feel "Management Services/Scheduling Database" would be more fitting~ Hopefully I'll be able to sleep without knowing the answer tonight >_<Dird

Could not allocate space for object because the 'PRIMARY' filegroup is full

Posted: 28 Nov 2010 08:17 PM PST

I have a SQL Server 2008 running on Windows 2008. The main database is located on the D: drive and the total capacity of the disk drive is 435GB. I have started to receive the following messages in the error log file:"Could not allocate space for object 'dbo.WebProxyLog'.'IX_WebProxyLog_DateTime' in database 'w3proxy' because the 'PRIMARY' filegroup is full".The database "w3proxy" is totally filled the D: dirve and there nothing else to delete on the D: drive to free some disk space. At the moment I am unable to get an extra disk drive which I believe would solve the disk space problem.But can anyone suggest any other way to free up the disk space until I can get an extra disk drive?Thank you.

snapshot agent is not running

Posted: 19 May 2013 04:41 AM PDT

Hi,Friends I am working on sql server 2008 r2 and I want to replicate data by using snapshot replication, when i create subscription and publication after being successful, my data didnot distributed to the table. when i check for problem, i found that my snapshot agent is not running on the subscription side, what should i do?

Questions about SSRS in 2008

Posted: 17 Nov 2009 09:22 AM PST

Hi guys,Here are my questions about SSRS 2008?1-Is it possbile to add Notification email to the reports that has been created in SSRS. Example:We have set up a report which will be sent to the client through email,Now we want to set a notification email,so if something happened(Successfully run or Any failure) the DBA would be notified.2-We have setup a report .We deliver this report as a file attachment.Is it possible to re-name the name of this file?Because as default the "reportname+ExecutionDateTime" will be the name of the report.3-Is it possible to enable/Disable a created report.(The only thing that I found was removing all the subscriptions related to the report).Thanks

SQL Alert question

Posted: 10 Nov 2010 07:07 PM PST

I thought the SQL alert are trigged when SQL Agent found error message in SQL Error log.But when i diabled SQL Server log event on Windows event log, i couldn't receive the alert. It seems SQL Agent read error from Event log then trigged the alert. Currently i am confused. Could anybody told me how SQL alrert are tiggered. Thanks in advance.
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog