Tuesday, May 28, 2013

[SQL Server 2008 issues] How to Allocate the recieved amount to different expenses

[SQL Server 2008 issues] How to Allocate the recieved amount to different expenses

How to Allocate the recieved amount to different expenses

Posted: 27 May 2013 05:06 PM PDT

Create Table #Temp( Number Int, Totalcoll Numeric(12,2), Maintainance_Due Numeric(12,2), Maintainance_Coll Numeric(12,2), Expense_Due Numeric(12,2), Expense_Coll Numeric(12,2))Insert Into #TempValues (1,0,500,0,400,0), (1,900,0,0,0,0), (1,0,200,0,800,0), (1,1000,0,0,0,0) Select * From #Temp /* With reference to above data, my Requirement is, For 1st row, there is Maintainance_Due = 500 and Expense_Due = 400 then In Second row,I want Under Maintainance_Coll should be 500 and Expense_Coll = 400 (This is TotalColl of 900 is allcated to Maintainance_Coll since Maintainance_Due = 500 and Expense_Due = 400) Same as this in 3rd row there is Maintainance_Due = 200 and Expense_Due = 800 So,In fourth row,I want Under Maintainance_Coll should be 200 and Expense_Coll = 800 (This is TotalColl of 100 is allcated to Maintainance_Coll since Maintainance_Due = 200 and Expense_Due = 800) */Please Help in this rewuirement as I required this in urgent and I am stucked in this.Please.........Thanks in Advance!!!!!!!!!!!!!!

alert configuration

Posted: 27 May 2013 05:48 PM PDT

i need to setup alert when the sql server is shutdown and server is up , how to achieve this

Sql Programme for allocation of amount in accounting

Posted: 26 May 2013 09:29 PM PDT

Hi, I want to build the logic for "allocation of overhead amount in accounting"in sql server 2008.Please help me how to write sql programme.Please tell if u want more details.Please help me .Thanks in Advance!

Kitchen Preston Lanes

Posted: 27 May 2013 12:24 PM PDT

Kitchen Preston Lanes www.solidwoodkitchen.co.uk. Full kitchen with appliances only £595 in Preston Lanes. Cheapest kitchen sale in Preston Lanes, Tel 01616-694785. Kitchen Preston Lanes.

backup log

Posted: 27 May 2013 05:01 PM PDT

is it possible to take log backup in a restored database ?

Code review rant...

Posted: 27 May 2013 12:12 PM PDT

During an online code review:Me: Your trigger is not set based. It will only process a single row.SQL Dev (w/ 6 years exp): OK. I have created a cursor inside the trigger that will now process every row.Me being very diplomatic: No, no, no... don't use a cursor inside a triggerAnd I wonder why I am losing my hair. I had to tell someone.j.a.c.

Using case statement in where clause to fetch rows whose column values are null

Posted: 27 May 2013 05:47 AM PDT

Hi,I need to fetch rows from a table in SP depending on input parameter value.@var is an input parameter which i am passing if @var = no owner assigned' then fetch where Name is null or Email is Null if @var = 'all' then return all the rows if @var = some name then return rows matching the parameter valuefor example:AND ((Name = CASE WHEN @var = 'no owner assigned' THEN null WHEN @var = 'all' THEN Name ELSE @var END) OR (Email = CASE WHEN @var = 'no owner assigned' THEN '' END))but the above query is not working for me. Please help!!

Backup failure History....

Posted: 28 Dec 2011 11:44 AM PST

Hi Team,I need to find out the backup failure history..... Till date how many backup are failed in sql server how can I find that...please help...

Execution SSIS-package from SQL Server-Agent fails

Posted: 26 May 2013 10:17 PM PDT

Hi,there is a SSIS-package, that runs properly in Visual Studio. It has a for-each-container wich loops a file-directory within flat-files.There is a variable "fullpathsource" for a flatfile-connection, that is combined of (static) path and file-name from for-each-container.Before the for-each runs, variable "fullpathsource" contains only the path.That's what the 2012 Agent remarks: flat-file-connection is not valid.Thanks in advance for any hint.RegardsMatze

SSIS change destination to named instance

Posted: 27 May 2013 05:20 AM PDT

HiWe have several SSIS packages where destination is 'SQL Server Destination'. Now they are moved to another server with a named instance, so the server name is not equal the server\instance name. The 'SQL Server Destination' does not accept it "the selected datasource is on a remote computer", the 'OLE DB Destination' should be used. The destination is not on a remote computer. Can we avoid changing all package destinations to OLE?Thanks for helpJan

Databas Shrink

Posted: 23 May 2013 08:56 PM PDT

Hello there,I have a database where we have archived and purged 95% of the data, leaving a 230GB file where only 7GB of it is in use by the data. As part of the purge script I have written, we need to conduct a shrink on the data file down to 50GB, leaving room for growth.The collection of dbcc shrinkfile commands on my workstation, a quad core, with 12GB, takes around 5 minutes to complete. On our test kit, which has it's own dedicated SAN with SSDs, oodles of memory and 80+ cores takes over an hour.I also tried creating a new filegroup, moving all the data across to the new filegroup, and shrinking the now empty primary file, and it still takes over an hour.I know that database shrinking is bad, but under these circumstances it is warranted.I'm at a loss as to why this may be. The SQL log show's nothing out of the ordinary. Does anyone have any suggestions ?

Using fetch and cursor in a while loop

Posted: 26 May 2013 08:37 PM PDT

HiI have a master table that has truck,lisens and startdate.Now I need to run this, one record at the time and use the trucK and lisens to get the stop date from another table.I have written the code below, but it does not print my truck id in the query section of the while.I will replace the print with my actual query of course, but I ran the code once with out any output and now it seems that the reason is because I do not get any truck and lisens from the id_cursorAny comments on what I am doing wrong?******START******-- Declare the variables to store the values returned by FETCH.DECLARE @truck varchar(3), @min_lic varchar(12); DECLARE id_cursor CURSOR FORSELECT truck,min_lic FROM KPIPlukkTidStart;OPEN id_cursor;-- Check @@FETCH_STATUS to see if there are any more rows to fetch.WHILE @@FETCH_STATUS = 0BEGIN--Query --Try to print the parameters PRINT 'The truck : ' + @truck FETCH NEXT FROM id_cursor INTO @truck, @min_lic;ENDCLOSE id_cursor;DEALLOCATE id_cursor;*****STOP*******

Service Broker: grant send to public. Why?

Posted: 26 May 2013 11:21 PM PDT

hi there,I try to learn configuring Service Broker between two SQL 2008 R2 Instances.I already managed to send messages in both directions.[u]Configuration overview:[/u]I don't use encryption and I have no certificates.All service broker objects (services, contracts, queues...) were created using "authentification dbo".I use a windows login for beginning a dialog and sending messages.This user were granted the send and receive rights on the services, the queues and the references-permission on the contract.[u]Question:[/u]The public role requires the send permission on the target service. If I revoke the rights I get this error (using ssbdiagnose or sql-profiler): [i]User public does not have SEND permission on service <myTargetServiceName>[/i][b]Why[/b] does the public role need the send-rights on the target database? I don't want this permission on the public role.It's needed even if I begin the dialog with a user that is member of the db_owner-role.Thank's a lot,Wolf

Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.

Posted: 26 May 2013 10:01 PM PDT

Dear All,From morning onward in my sql server 2008 i am getting following error.Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reachedin my connection i have set unlimited connection but even though i am getting this error.Pls suggest

Send mail when SQL Server stop

Posted: 23 May 2013 07:20 PM PDT

Hi,I need your help to get information about sql service is stopped via mail.

Unable to see the tables

Posted: 26 May 2013 09:10 PM PDT

Hi All,We have a Database and a user with Db_owner privileges.Everything was fine till yesterday. from Yesterday that user is not able access any of the tables from GUI also through query.only Sysadmin are able to access the tables. What might be the issue and how to resolve this ?Please help me in this. Below is the error, I am getting:TITLE: Microsoft SQL Server Management Studio------------------------------Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476------------------------------ADDITIONAL INFORMATION:An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)------------------------------The SELECT permission was denied on the object 'extended_properties', database 'mssqlsystemresource', schema 'sys'. (Microsoft SQL Server, Error: 229)For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.00.1600&EvtSrc=MSSQLServer&EvtID=229&LinkId=20476------------------------------BUTTONS:OK------------------------------TITLE: Microsoft SQL Server Management Studio------------------------------Cannot show requested dialog.------------------------------ADDITIONAL INFORMATION:Cannot show requested dialog. (SqlMgmt)------------------------------An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)------------------------------The SELECT permission was denied on the object 'system_objects', database 'mssqlsystemresource', schema 'sys'.The SELECT permission was denied on the object 'fulltext_catalogs', database 'mssqlsystemresource', schema 'sys'. (Microsoft SQL Server, Error: 229)For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.00.1600&EvtSrc=MSSQLServer&EvtID=229&LinkId=20476------------------------------BUTTONS:OK------------------------------

Unable to uncheck Parameter values in SSRS

Posted: 26 May 2013 09:00 PM PDT

Friends,I am working on a Report developed using SSRS. I have around 10 parameters in the report and most of these parameters take values from the Dataset. I am having an issue in the parameters. I am taking available values and default values for some of these parameters from existing datasets in the report. So when I click on a parameter it gives me the list of available values with Select ALL option as well. however, when I try to uncheck all the parameter values the report automatically sets it back to the parameter values selected last time. I am not really sure why it is working like this. I am just trying to uncheck all the values in a parameter and I am not able to do it.Is there a way to come over this issue.

How to read each row and update table

Posted: 26 May 2013 07:58 PM PDT

Arrears Credit Line Dates AvailableAmount Principal Interests Principal Interests 0015321-01 31/01/2003 220,000 40,000 30,000 - - 0015321-01 28/02/2003 - 40,000 30,000 - - 0015321-01 31/03/2003 - 40,000 30,000 - - 0015321-01 30/04/2003 - 40,000 30,000 30,000 30,000 How can I fill Arrears(Principal and Interests) as shown above using sql server 2008. Available amount is used to pay for principal and Interests when the amount is over, arrears (Unpaid amount) is recorded. In this case 220 000 can pay (40 000+40 000+40 000+10 000 Principal) and (30 000+30 000+30 000 Interests) so principal for 30/04/2013 only 10 000 is paid and 30 000 becomes unpaid and 30 000 interests for 30/04/2013 is wholly unpaid. Because 220 000 can not pay all.
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog