Thursday, June 20, 2013

[SQL Server 2008 issues] How to find when my SSRS service was last restarted ?

[SQL Server 2008 issues] How to find when my SSRS service was last restarted ?

How to find when my SSRS service was last restarted ?

Posted: 19 Jun 2013 06:38 PM PDT

Expertrs,How to find when my SSRS service was last restarted ? Not the DB Engine.. Only Reporting services..Thanks in advance..Smith.

How to get user position in table i.e 1st 2nd, 3rd

Posted: 19 Jun 2013 07:09 PM PDT

hello,Im having a bit of trouble trying to return the current users position, iv been looking at this for the past 3 days browsed many forums tried many situations but with still no luck.I have a table called prospectlead which has a column called ReviewedBy this gets populated when the user checks a record etcthe desired output would be 1st John2nd Me3rd Sarah This will obviously change throughout the day depending on how the individuals get on, so mid morning i could go from 2nd position down to 10th but i need to return my position plus the person above me and the person below me, the way im getting the amount of records checked is by using a COUNT, the parameter passed in to the stored procedure is the ShortAbbr which would be 'D13' (again thats hard coded to get it working, it would actaully be @ShortAbbr passed in from the front end)Can any one help me on this please?[code="sql"]Select ROW_NUMBER() over(order by u.UserID) as RowNumber, count(p.ID) as TotalCount, u.Firstname + ' ' + u.Surname as DataCheckerfrom ProspectLead p join UserAccount u on p.reviewedby = u.ShortAbbrwhere p.ReviewedBy is not null and convert(date, p.ReviewedDate) = convert(date, SYSDATETIME())and u.Responsibility = 16and u.ShortAbbr = 'D13'group by u.Firstname, u.Surname, u.UserID order by TotalCount desc[/code]

How to print a file using a static ip and a printer name

Posted: 19 Jun 2013 05:21 PM PDT

How to print a file using a static ip and a printer name.Plz help me

Rows to Columns (pivot or anyother way)

Posted: 19 Jun 2013 07:07 AM PDT

Hi am trying to change row values to column based on ControlNO & Seq column. (Please see @currenttable)InspInterval --> int0,int1,int2,int3ChkProcedureKey --> p_chkproc0,p_chkproc1,p_chkproc2,p_chkproc3IntUnit --> intunit0,intunit1,intunit2,intunit3I did try to do pivot but was unsuccessful :-(Included is the sql of what the table looks like currently and how I has to be converted.. FYI, they are 208503 records in the table if that matters on the method we look into.[code="sql"]Declare @CurrentTable Table([ControlNo] [nvarchar](15),[MODELKEY] [int],[SEQ] [bigint],[InspInterval] [int],[ChkProcedureKey] [int],[IntUnit] [nvarchar](1)) INSERT INTO @CurrentTableselect '020468','7996','1','2','99','Y' UNIONselect '020468','7996','2','6','26','M' UNIONselect '020468','7996','3','6','27','M' UNIONselect '020468','7996','4','12','28','M'UNIONselect '03020/51001048','12307','1','0','99','M' UNIONselect '03020/51001048','12307','2','0','363','M'UNIONselect '03020/51001048','12307','3','0','364','M'SELECT * FROM @CurrentTableDeclare @RequiredTable Table ([ControlNo] [nvarchar](15),[MODELKEY] [int],[int0] [int],[int1] [int],[int2] [int],[int3] [int],[p_chkproc0] [int],[p_chkproc1] [int],[p_chkproc2] [int],[p_chkproc3] [int],[intunit0] [nvarchar](1),[intunit1] [nvarchar](1),[intunit2] [nvarchar](1),[intunit3] [nvarchar](1)) INSERT INTO @RequiredTableselect '020468','7996','2','6','6','12','99','26','27','28','Y','M','M','M' UNIONselect '03020/51001048','12307','0','0','0','','99','363','364','','M','M','M',''SELECT * FROM @RequiredTable[/code]

Running Totals

Posted: 19 Jun 2013 10:37 AM PDT

I am having problems figuring out how to add a running total. I initially tried to get my TempTable to show the different categories of transactions by reporting 1, 2, or 3. I could not get my program to do this. I ultimately want curItem compared to prevItem and if there is not a match, to populate the Trnd field with 1. If the curItem and prevItem match, I need the program to incrementally sum from the first mis-match to the end of the matched fields. [code="sql"]--===== If the test table already exists, drop it IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL DROP TABLE #mytable--===== Create the test table with CREATE TABLE #mytable ( curItem char, prevItem char, Trnd int ) INSERT INTO #mytable (curItem, prevItem, Trnd)SELECT 'D', 'U','' UNION ALLSELECT 'D', 'U','' UNION ALLSELECT 'U', 'D','' UNION ALLSELECT 'U', 'U','' UNION ALLSELECT 'U', 'U','' SELECT *FROM #mytableDECLARE @TempTable TABLE (curItem char(2), prevItem char(2), Trnd int);DECLARE @curItem char(2), @prevItem char(2)INSERT INTO @TempTable(curItem, prevItem, Trnd)SELECT curItem, prevItem, TrndFROM #mytableBEGIN SELECT @curItem = curItem, @prevItem = prevItem FROM @TempTable IF @curItem = @prevItem BEGIN UPDATE EURUSD#1A SET Trnd = 1; END ELSE IF @curItem <> @prevItem BEGIN UPDATE EURUSD#1A SET Trnd = 2; END ELSE BEGIN UPDATE EURUSD#1A SET Trnd = 3; END SELECT * FROM @TempTableEND [/code]

How you take the breakup by each component in Multi_Pages ( Otherwise MTL/NonBpool )

Posted: 19 Jun 2013 02:34 PM PDT

Question : How you take the breakup by each component in Multi_Pages ( Otherwise MTL/NonBpool )like: SQLMailOle ComponentsLinked ServerThird Party DLL'sExtended SP'sCLR

Security Material.

Posted: 19 Jun 2013 04:09 PM PDT

Hi all, Can any one refer some web pages or books related to security part of SQL. I am struggling to understand the concept of schema,principles,permissions,users having schema and etc.......giving permissions to particular table.

SQL server agent SSIS error

Posted: 19 Jun 2013 11:39 AM PDT

I get the following error when I execute my package as a SQL server agent job.It is an SSIS 2008 package running on a SQL Server 2008 instance. My package security is DontSaveSensitive.I don't even know how to begin fixing this error.Where should I check first? Date a value of timeLog Job History (MyJob)Step ID 1Server PCTSQL004Job Name MyJobStep Name Job_1Duration 00:00:00Sql Severity 0Sql Message ID 0Operator Emailed Operator Net sent Operator Paged Retries Attempted 0MessageExecuted as user: CS\DmcSysManager. The process could not be created for step 1 of job 0x63BB5A86DB23F947866D2A806BE4CC6B (reason: A required privilege is not held by the client). The step failed.

Run inserts from file with many records

Posted: 19 Jun 2013 06:54 AM PDT

Hi!I need to run inserts from file with many records/rows: about 500.000.My Management Studio is not supporting many records, there is a memory error.Is there any way to break up the files into parts , or [b]read via cmd[/b] ?Thanks!Jose Anchieta C. Jr

Restoring from the backup after encryption of datbase

Posted: 19 Jun 2013 03:07 AM PDT

Hi friends,I used the following query to restore the encrypted database.USE master;GOCREATE DATABASE Encry2ON ( NAME = Encry2_dat, FILENAME = 'D:\SQL2008R2DB\MSSQL10_50.SQL2008R2\MSSQL\Backup\TDEPost.mdf', SIZE = 3, MAXSIZE = 5, FILEGROWTH = 1 )LOG ON( NAME = Encry2_log, FILENAME = 'D:\SQL2008R2DB\MSSQL10_50.SQL2008R2\MSSQL\Backup\TDEPost2Log.ldf', SIZE = 1MB, MAXSIZE = 5MB, FILEGROWTH = 1MB ) ;GOopen MASTER KEY DECRYPTION BY PASSWORD = 'password';RESTORE DATABASE Encry2 FROM DISK = N'D:\SQL2008R2DB\MSSQL10_50.SQL2008R2\MSSQL\Backup\TDEPost.bak' WITH FILE = 1, MOVE N'mydb' TO N'D:\SQL2008R2DB\MSSQL10_50.SQL2008R2\MSSQL\Backup\TDEPost.mdf', MOVE N'mydb_log' TO N'D:\SQL2008R2DB\MSSQL10_50.SQL2008R2\MSSQL\Backup\TDEPost.ldf', NOUNLOAD, REPLACE, STATS = 10CLOSE MASTER KEYIt gives me following error message[b]Msg 3234, Level 16, State 2, Line 2Logical file 'mydb' is not part of database 'Encry2'. Use RESTORE FILELISTONLY to list the logical file names.Msg 3013, Level 16, State 1, Line 2RESTORE DATABASE is terminating abnormally.[/b]My concern is I want to show that , I encrypted the database.I already show that both the backup files , one gereted before the backup and one after are totally different in terms of size and also the content.Now, I need to show what kind of problem one can get if he/she restore the backup file which I have created after encryption.!!do they need password of master key.?how one can restore the encrypted database backup file other than me ?please help.thanks.

SQL grouping question

Posted: 19 Jun 2013 04:35 AM PDT

sorry for the nature of the question, I just don't know how to write this query. I have 2 tables, one is a user table and the other is phone data from my phone system, I am just trying to write a query to join the 2 tables together where I can get the user (fullname) from the User_data table to join each users from the phone column to both the callingparty and calledparty field from the CDR_Data table. Basically all the records from the CDR_Data table where both the callingparty field and the calledparty field are joined to the phone column on the User_data table so I can see both types of calls for each user. Any help is appreciated, thanks! My 2 tables are as follows:CDR_DataIDDatetimeorigination (datetime)callingparty (int)calledparty (int)datetimeconnect (datetime)datetimedisconnect (datetime)duration (int)User_datafullname (char)phone (int)

how to get person with multiple rates

Posted: 19 Jun 2013 05:50 AM PDT

I have a scenario where a single client can have multiple workers with either all workers having same rate or each worker having different rates for the same participant,sample data is as belowworker client ratefrank derek 0.55sandra derek 0.55saeed haleema 0.555curtis julia 0.555marilyn julia 0.55jane william 0.555adam william 0.55lisa anderson 0.555marketa pamela 0.55Now i need to get the list of clients and workers who have different rates like for example result should be curtis julia 0.555marilyn julia 0.55jane william 0.555adam william 0.55

How will work with(nolock) option in select queries

Posted: 19 Jun 2013 03:10 AM PDT

Hi,We have few transaction tables having more than 20 million rows and using those table when we select tables are blocking and we are getting deadlocks, those tables are well indexed and well maintained. can we use WITH(NOLOCK) option in select queries for these tables. how it will work. please do me needful.Regards,BSL

Need column name if condition fails

Posted: 19 Jun 2013 02:42 AM PDT

Hi All,I need to write a code in SQL Server 2008 in which I have 4 columns(First Name (2 columns) and Last Name (2 columns)).I have a 5th column name 'Status'.I have set a condition that if value in 1st and 3rd column [First Name]and 2nd & 4th column [Last Name] matches the status is shown TRUE else FALSE.But I have been told to write Column name where discrepancy is present, instead TRUE or FALSE.Can anyone guide me regarding this?I am just a beginner in this field.

Need column name in Status column

Posted: 19 Jun 2013 02:45 AM PDT

Hi All,I need to write a code in SQL Server 2008 in which I have 4 columns(First Name (2 columns) and Last Name (2 columns)).I have a 5th column name 'Status'.I have set a condition that if value in 1st and 3rd column [First Name]and 2nd & 4th column [Last Name] matches the status is shown TRUE else FALSE.But now I have been told to write Column name even if the data is matching or if it has discrepancy, instead TRUE or FALSE.Can anyone guide me regarding this?I am just a beginner in this field.

SSIS Using XML Source and max size limit of nvarchar(4000)

Posted: 19 Jun 2013 01:43 AM PDT

I have xml files that I'm trying to import into SQL Server using SSIS XMLSource and the xml file has a field that contains more that 4000 bytes. I think the maximum is 4000. I'm defining this in the external and output column area of the XML Source Show Advance Source Editor. Any ideas how to get around this problem?

SSIS Package Fails - Unhelpful Error message

Posted: 19 Jun 2013 02:36 AM PDT

We have a job that gets data off a website and imports into SQL. It runs for a couple hours, then fails with the following message. Not very helpful.".. This error occurs when the server is experiencing problems..."Any thoughts, or am I missing something ? (not the first time)[code="plain"]Microsoft (R) SQL Server Execute Package UtilityVersion 10.0.2520.0 for 32-bitCopyright (C) Microsoft Corp 1984-2005. All rights reserved.NULLStarted: 07:00:42Error: 2013-06-19 09:36:35.18 Code: 0xC001600E Source: MRIFeed Connection manager "Job" Description: Server returned status code - 500 : Internal Server Error. This error occurs when the server is experiencing problems.End ErrorError: 2013-06-19 09:36:35.23 Code: 0x00000001 Source: Download job XML Description: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> Microsoft.SqlServer.Dts.Runtime.DtsRuntimeException: Server returned status code - 500 : Internal Server Error. This error occurs when the server is experiencing problems. ---> System.Runtime.InteropServices.COMException (0xC001600E): Server returned status code - 500 : Internal Server Error. This error occurs when the server is experiencing problems.NULL at Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSHttpClientConnection100.DownloadData() at Microsoft.SqlServer.Dts.Runtime.HttpClientConnection.DownloadData() --- End of inner exception stack trace --- at Microsoft.SqlServer.Dts.Runtime.HttpClientConnection.DownloadData() at ST_986b2264c6724ec5b4336f7799acb425.vbproj.ScriptMain.Main() --- End of inner exception stack trace --- at System.RuntimeMethodHandle._InvokeMethodFast(Object target, Object[] arguments, SignatureStruct& sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner) at System.RuntimeMethodHandle.InvokeMethodFast(Object target, Object[] arguments, Signature sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner) at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture, Boolean skipVisibilityChecks) at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture) at System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object[] providedArgs, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParams) at System.Type.InvokeMember(String name, BindingFlags invokeAttr, Binder binder, Object target, Object[] args, CultureInfo culture) at Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTATaskScriptingEngine.ExecuteScript()End ErrorDTExec: The package execution returned DTSER_FAILURE (1).Started: 07:00:42Finished: 09:36:35Elapsed: 9352.84 secondsNULL[/code]

Transaction log and Full backups are not happening on secondary & DR

Posted: 19 Jun 2013 02:05 AM PDT

Hi every one,My title shall give you all information.I am not able to see any kind of logs getting recorded when i perform them by using a scheduled job.Even though the logs are not recorded. I couldnot see any kind of error msg inspite shows success.But if i do them manually they does get recorded.Is it normal.. What am i supposed to do.???NOTE: ROOKIE HERE :hehe:

Tempdb

Posted: 19 Jun 2013 12:07 AM PDT

Hi I need some clarification on this topic.what is tempdb mdf files used for vs ldf files used for?I understand tempdb mdf needing space and ldf not being used.In the event of ldf being used, what would this be used for?

Hai all

Posted: 19 Jun 2013 01:44 AM PDT

TITLE: Microsoft SQL Server Management Studio------------------------------Restore failed for Server 'RAJESH-PC\NAIDU'. (Microsoft.SqlServer.SmoExtended)For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1617.0+((KJ_RTM_GDR).110422-1125+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Restore+Server&LinkId=20476------------------------------ADDITIONAL INFORMATION:System.Data.SqlClient.SqlError: The operating system returned the error '5(Access is denied.)' while attempting 'RestoreContainer::ValidateTargetForCreation' on 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\AdventureWorksDW.mdf'. (Microsoft.SqlServer.Smo)For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1617.0+((KJ_RTM_GDR).110422-1125+)&LinkId=20476------------------------------BUTTONS:OK------------------------------what i have done so far is, fullbackup and transactional backup to disk 'D' and has given administrative permission to that folder and service account is NT AUTHORITY.Can anybody please help me out.Thanks in advance

Find the correct answer in the following query

Posted: 18 Jun 2013 10:48 PM PDT

Hi,I have two tables named customer and salesorder.In the customer table i have 1000 customers,Of which 900 customers have orders in the salesorder table.i execute the following query to list all customer sthat have had at least one sale.Select * from customer where customer.CustomerID in (Select Customer.CustomerID from salesorder)you need to identify the result of the query? which result will the query return?1) No rows2) A Warning message3) The 100 rows in the customer table4 The 900 rows in the customer table with matching rows in the salesorder table.I am thinking the answer is 4 but some are telling that the answer is 3.So can you plese tell me the correct answer with explanation.Thank you

Leave

Posted: 18 Jun 2013 09:49 PM PDT

i use this syntax in sql serverwhen t.timein is null and l.date is not null then u.description and make join like thisFROM attend_log) tleft join leaveinformation l on t.eid = l.eid and t.date = l.dateleft join leavedescription u on l.lid = u.lidbut its not giving me the desired resultits giving me that resultdate-----------------------------eid---------timein-----timeout---spendtime---remarks--2013-01-07 00:00:00.000--17074---------NULL-------NULL--------NULL------ABSENT i want this type of resultdate-----------------------------eid---------timein-----timeout---spendtime---remarks--2013-01-07 00:00:00.000--17074---------NULL-------NULL--------NULL------casual leave i join these tables[LeaveDescription]([LID] [int], <---------leave id[Description] [varchar](50) <------------leave description) [LeaveInformation]([CID] [int] NULL, <-----------company id[BID] [int] NULL, <------------branch id[EID] [int] NULL, <------------employee id[Date] [datetime] NULL,[LID] [int] NULL <-------------leave id) [ATTEND_LOG]([EID] [int] NULL,<------------employeeid[date] [datetime] NULL,[timein] [datetime] NULL,[timeout] [datetime] NULL,[BID] [int] NULL, <------------------branch id[EBID] [int] NULL,<--------------------employee branch id[spendtime] [datetime] NULL,[excessshort] [datetime] NULL,[excess] [nvarchar](50) NULL)if there is a data in leave information table then it shows like thisdate-----------------------------eid---------timein-----timeout---spendtime---remarks--2013-01-07 00:00:00.000--17074---------NULL-------NULL--------NULL------casual leave other wise shows like thisdate-----------------------------eid---------timein-----timeout---spendtime---remarks--2013-01-07 00:00:00.000--17074---------NULL-------NULL--------NULL------ABSENT

EMERGENCY MODE

Posted: 18 Jun 2013 10:05 PM PDT

HI ALL, I am experimenting with one of my test databases . I intentionally set the database to emergency mode.But now it is not coming out from that mode. I tried dbcc but it doesn't print any errors. Any one has any alternatives solution for this.

Restore Database if Job fails

Posted: 18 Jun 2013 09:44 PM PDT

HiIs there a way of starting a database restore if an agent job fails?Thanks

SQL Server 2008 view Query

Posted: 18 Jun 2013 08:53 PM PDT

Kindly help on sql query to create a view that merges data from two tables with same columns but second table (sales) has missing rows that correspond first table(Inventory) In essense: I need those items that are in Inventory table but don't have entries in sales table to be included in my sales view with (0) quantities. Inventory ItemID ItemName Qty 1 Bread 5 2 Soda 10 3 Cocoa 4 4 Blueband 15 5 Omo 20 6 Biscuits 30 7 Pens 50 8 Note book 5 Sales ItemID ItemName Qty 1 Bread 3 2 Soda 5 5 Omo 10 6 Biscuits 15 8 Note book 2 I want my sales view to look like below Sales View ItemID ItemName Qty 1 Bread 3 2 Soda 5 3 Cocoa 0 4 Blueband 0 5 Omo 10 6 Biscuits 15 7 Pens 0 8 Note book 5

log shipping version.

Posted: 18 Jun 2013 08:13 PM PDT

Hello All,Please let me know is it possible to create log shipping between two instances with different sql server version.Regards
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog