How Do I Figure Out The Database_Host And Database_Name For My Sharepoint SQL Server?

Dec 8, 2007

I'm trying to access my sharepoint sql server through php, and normally I would set the connection like this:

define('DB_USER', 'user');
define('DB_PASSWORD', 'pass');
define('DB_HOST', 'database host');
define('DB_NAME', 'database name');

$dbc = mssql_connect(DB_HOST, DB_USER, DB_PASSWORD) or die('coudlnt connect to mssql server' .mssql_error());
mssql_select_db(DB_NAME) or die('couldnt connect to that database:' .mssql_error());

How do I figure out what the DB_HOST and DB_NAME is?

And I'm assuming my normal username and password to the server would work, right?

View 5 Replies


ADVERTISEMENT

Real Figure Instead Of Exponential Figure Needed

May 29, 2008

Hello,

I use OPENROWSET to read values from Excel and store them in a SQL Server table. In the Excel file I have a row having format 'Number' with two decimal places.

Example: 1225000.00

When I select this value using SSMS I get the correct value:

1225000

Strange enough, I cannot see the decimals anymore. However, when I now store this value into my table and then select it from there I get: (the datatype in the table is VARCHAR(max))

1.225e+006

I would not care if I could convert this back to a numeric datatype but this seems not to work: CAST('1.225e+006' as INT) throws an exception. Obviously OPENROWSET sends the data strictly as a character string. Storing this into varchar(max) works for small figures but it starts to use exp values for big figures.

Does anybody has an idea how to bring huge Excel based figures safely into a MS SQL Table ?

Thanks: Peter

View 5 Replies View Related

Including MTD And YTD Figure With A Daily Figure

Feb 4, 2008

Hello,
I have a query that returns a daily revenue figure. The query is as follows:

SELECT top 1000
ds.AcctCode,
ds.TxnDate,
SUM(isnull(ds.FuelFee,0)) + SUM(isnull(ds.CashFee,0)) + SUM(isnull(ds.ScFee,0)) AS TotalDailyRevenue,
--"MTD" = ?,
--"YDT" = ?,
ps.TC,
CASE
WHEN ps.Proj = 100 THEN 'New Account'
WHEN ps.Proj = 200 THEN 'Current Account'
END AS ProjStatus,
ps.FSR,
ps.SubmitRep1

FROM
TxnRptg.dbo.tbl_DailySummary ds
INNER JOIN SalesData.dbo.tbl_CYProcessedSales ps
ON ds.AcctCode = ps.Acct

WHERE
MONTH(ds.TxnDate) = 1
AND
Proj IN (100,200)
AND TC = 'HV'

GROUP BY
ds.AcctCode, ds.TxnDate, ps.TC, ps.Proj, ps.FSR, ps.SubmitRep1

ORDER BY
ds.AcctCode, ds.TxnDate

--*********************************

TxnDate represents a single day of the month. How can I include MTD so that the dates for the revenue total are from DATEADD(mm, DATEDIFF(mm,0,getdate()), 0) (beginning of current month) to TxnDate, and YTD so that the revenue totals are from DATEADD(yy, DATEDIFF(yy,0,getdate()), 0) (beginning of the current year) to TxnDate?


Thank you for your help!


cdun2

View 5 Replies View Related

Database_name.table_name In Create Table ?

Jun 8, 2004

Hi,

In create table statements we use database_name.table_name.

Eg :

create table my_database.test_table(sno int .....)

What is the meaning of this ? Is that, the table is created in the specified database_name ?

Please clarify.

Thanks,
Sam

View 3 Replies View Related

SQL Server 2012 :: If Minus Figure Pull Back 0

Feb 6, 2015

Have the following in my SELECT Stataement

CASE WHEN com.completion_date IS NOT NULL AND dim.DayName <> 'Saturday'
THEN DATEDIFF(d, com.current_task_target_date,com.completion_date) - non1.NoWorkDays
WHEN com.completion_date IS NOT NULL AND dim.DayName = 'Saturday'
THEN DATEDIFF(d, com.current_task_target_date,com.completion_date)
ELSE NULL
END AS 'DaysOverTarget'

Some of the figures coming back are minus figures. How could I get the minus figures reported to be 0.00?

Below is the full TSQL

SELECT DISTINCT com.comm_reference AS 'Referance'
,com.crt_date AS 'CreatedDate'
,com.current_task_target_date AS 'TargetDate'
,com.completion_date AS 'CompletionDate'
,CASE WHEN com.completion_date IS NOT NULL AND dim.DayName <> 'Saturday'
THEN DATEDIFF(d, com.crt_date,com.completion_date) - non.NoWorkDays

[Code] .....

View 4 Replies View Related

Still Trying To Figure Out Identification Of SQL Server Stored Proc. Permissions Via VB

Jul 20, 2005

I've looked through many suggestions and partial examples all overthis newsgroup and still am not coming up with anything that doesspecifically what I'm wanting to accomplish.I'm writing a VB 6.0 application which uses SQL Server as theback-end.Here's an example of what I'm wanting to do...A user accessing the VB GUI attempts to open a certain form. Coderuns behind the scenes in VB that checks the user's "Execute"permissions on the stored procedure that retrives the data into theform. If the user is determined to have "Execute" permissions to thatstored procedure, the user is allowed to proceed with opening theform. If they don't have permissions, they are informed of this andaren't allowed to open the form and view the data.Let's say that we initially determine through the VB code that theuser has the ability to "Execute" the stored procedure that allows forviewing of the data. We go ahead and let them open the form. Oncethe form is being opened though, we run VB code to check for theirpermissions on other stored procedure that can be used in UPDATING /INSERTING / DELETING the records being displayed in the form. If theydon't have "Execute" permissions on these particularUpdate/Insert/Delete stored procedures, then I'll have a text box onthe form show as visible to advise them while they're looking at thisdata that they can't do anything to this data other than view it...they can't insert new records, they can't delete any records, and theycan't update any records.I have yet to find specific code that tells me how to checkpermissions on a storedc procedure for the user that's logged-in, whatthe resulting codes (i.e. 12291, etc.) mean as far as identificationof their permissions, etc.If anyone can help me out here, I'd greatly appreciate it. I'd hateto just have to resort to trying to allow the user to run the storedprocedures and just trap the error codes that may arise once eachstored procedure is executed and inform the user on the tail-end ofthe process that they can't run the stored procedure. I'd like toidentify their permissions to the stored procedures on the front-endof the process either before each form is opened or just as it'sopening to advise them early as to what they can/can't do with thedata being displayed in the form.Thanks in advance for any examples/information!Sincerely,Brad H. McCollumJoin Bytes!

View 1 Replies View Related

Reporting Services And Sharepoint Integration - Sharepoint Alternate Access Mappings (Zones)

Mar 2, 2007

I get the following error when I try to navigate to a report/model/data source, stored in a Sharepoint Document Library using a Sharepoint URL based on an Extranet or Intranet zone - but it works OK using the url for the Default zone:

System.Web.Services.Protocols.SoapException: The specified path refers to a SharePoint zone that is not supported. The default zone path must be used. ---> Microsoft.ReportingServices.Diagnostics.Utilities.SecurityZoneNotSupportedException: The specified path refers to a SharePoint zone that is not supported. The default zone path must be used.


Is this a limitation of the SQLRS-WSS3 integration or is there a workaround?

Many thanks to anyone who can help!





View 22 Replies View Related

Sharepoint Integration Microsoft.ReportingServices.SharePoint.UI.WebParts.dll

Feb 2, 2007

Does anyone know the location of Microsoft.ReportingServices.SharePoint.UI.WebParts.dll after you install the web part? I am wondering if I can inherit from this web part to wrap some other functionality into it. I know that i see it in the GAC buyt can't seem to find it so I can look at the assembly using reflector.

Also, how easy is it to pass in parameters to the Report Viewer?

Thanks!

Erik



View 6 Replies View Related

Problems With SharePoint Integration And Reporting On SharePoint With SSRS

Jan 17, 2008

Since I wasn't getting any replies with answers or suggestions to my initial posting, I figured I would continue to list the problems and solutions I've encountered while trying to setup and utilize SharePoint integration mode, and read data from SharePoint with SSRS reports (developed in VS2005). Hopefully this is helpful to anyone that is also trying to learn all this stuff and figure it out.

(Single server - W2K3R2_SP2 as: DC / SQL2005 Standard / MOSS2007 Enterprise - see the bottom of this posting for the steps I took to set that up)

Note: After I get this to work, I intend to post my experiences for setting up SharePoint Integration with SQL and SP on separate boxes.

1. In the new reporting section under SharePoint application management in the Central Admin Console, the Grant database AND Set Server Defaults work when using the domain admin account, but not when using the SQLSPS service account I used to setup both SQL and SharePoint. Why can't I use the service account? Is the domain admin account now going to be used as a service account for accessing the sharepoint database?


This question remains unanswered as of yet.

2. Reporting Services configuration tool shows Sharepoint integration as red x when I create a new database in integration mode. (the service account SQLSPS and the Server object are members of the group WSS_WPG). If I switch back to native mode (the original reportserver database), the red x goes to a blue exclamation instead. Using Service Credentials or Windows credentials (as either the service account SQLSPS or as domain admin) doesn't change anything.


RESOLVED by using a domain user account for the ReportServer WebApp. Even though everything is installed on the same machine, it seems using the default of NETWORK SERVICE wasn't acceptable. Possibly because SharePoint is configured to allow additional machines in the farm?

3. http://servername:8080/reportserver will display Report Server information as it did before the integration on port 80, but http://servername/reportserver doesn't display anything through SharePoint.


Resolved. I needed to define a repository before I could view it (go figure). When I created a sharepoint site based on a report center template, created a data connection library and report library, and then referenced that by http://servername/sites/myReportCenterSite/myReportLibrary, I was able to see the equivalent of http://servername/reports (which is now disabled due to integration mode).

4. Unable to deploy to new report center sharepoint site I mention above using Visual Studio. I kept getting "A connection could not be made to the report server http://servername/sites/ReportCenter. Server was unable to process request. The request failed with HTTP status 503: Service unavailable (System.Web.Services). In Visual Studio, the project properties were: TargetDataSourceFolder = http://servername/sites/myReportCenterSite/myDataConnectionLibrary, TargetReportFolder = http://servername/sites/myReportCenterSite/myReportLibrary, TargetServerURL = http://servername/sites/myReportCenterSite/


Found these errors in the event logs on the server.

Event Type: Warning
Event Source: W3SVC
Event ID: 1021
Description:
The identity of application pool, 'ReportServer' is invalid. If it remains invalid when the first request for the application pool is processed, the application pool will be disabled. The data field contains the error number.

Event Type: Warning
Event Source: W3SVC
Event ID: 1057
Description:
The identity of application pool 'ReportServer' is invalid, so the World Wide Web Publishing Service can not create a worker process to serve the application pool. Therefore, the application pool has been disabled.

Event Type: Error
Event Source: W3SVC
Event ID: 1059
Description:
A failure was encountered while launching the process serving application pool 'ReportServer'. The application pool has been disabled.

RESOLVED the issue by changing the Application Pool used by reporting services from ReportServer to DefaultAppPool and changing the account DefaultAppPool used from NETWORK SERVICE to my service account SQLSPS. I have no idea why ReportServer is invalid, it was set to use the service account SQLSPS the same as DefaultAppPool is. Perhaps the reporting services add-on makes a change to invalidate this?

5. Attempting to read a SharePoint list using a SSRS report. This should be fun.

First, created a new custom list in the ReportCenter SharePoint sites I've been using and called it myCustomList. I added one item with a title of myCustomListItem1. I don't know if I need to do more than that for a simple test of reporting or not yet.

Second, configured new shared data source. Name = myCustomList1DataSource, type=XML, Connection String = http://servername/_vti_bin/lists.asmx (this URL is viewable in IE), Using Windows authenticaion.

Third, in VS2005 on a workstation in the domain, create new report item (report1.rdl). New dataset for report; Name=Report1DataSet, Data Source=myCustomList1DataSource (Shared), Command type = text, Query String = [See Queries and associated errors below]:

Initial query obtained from: Connecting SQL Reporting Services to a SharePoint List (David Wise)
http://www.sharepointblogs.com/dwise/archive/2007/11/28/connecting-sql-reporting-services-to-a-sharepoint-list-redux.aspx

Note: Queries when executing the dataset (which errors out) and when executing a query in CAML Viewer for the same information (which does work just fine) both initially return 401.1 and 401.2 errors (as viewed in Fiddler). Since the CAML query works, I have not tracked this down yet.


A. Original format from posting



<Query>
<Method Namespace=http://schemas.microsoft.com/sharepoint/soap/ Name="GetListItems"/>
<SoapAction>
http://schemas.microsoft.com/sharepoint/soap/GetListItems
</SoapAction>
</Query>


Parameter; Name => listName, Value => =myCustomList

Was NOT prompted to define the query parameters


- Gets this error: "An error occurred while setting the command text property of the data extension command. The XmlDP query is invalid. (Microsoft.ReportingServices.DataExtensions)."


- Advanced information from the error => 'http' is an unexpected token. The expected token is '"' or '''. Line 2, position 19. (System.Xml)

B. Changed: Placed the namespace URL in quotes (also tried with and without / in namespace after /soap)

<Query>
<Method Namespace="http://schemas.microsoft.com/sharepoint/soap/" Name="GetListItems"/>
<SoapAction>http://schemas.microsoft.com/sharepoint/soap/GetListItems
</SoapAction>
</Query>

Parameter; Name => listName, Value => =myCustomList (also tried Value => myCustomList) (no equal sign)

Clicked "!" to execute, and got prompted to define the query parameters
Entered Name => listName, Value => myCustomList

- Gets this error: "failed to execute web request for the specified URL (Microsoft.ReportingServices.DataExtensions)."

- Advanced information: <faultstring>Server did not recognize the value of HTTP Header SOAPAction: http://schemas.microsoft.com/sharepoint/soap/GetListItems/.</faultstring>


C. Try the query string that seems to work for Stramit CAML Viewer to read myCustomList

Stramit CAML query:
<Query xmlns="http://schemas.microsoft.com/sharepoint/soap/">
<OrderBy>
<FieldRef Name="ID" />
</OrderBy>
</Query>

Also tried
<Query xmlns="http://schemas.microsoft.com/sharepoint/soap/">
</Query>

And tried entering no query...

Parameter; Name => listName, Value => =myCustomList (also tried Value => myCustomList) (no equal sign)

Clicked "!" to execute, and got prompted to define the query parameters
Entered Name => listName, Value => myCustomList

- Gets this error: "failed to execute web request for the specified URL (Microsoft.ReportingServices.DataExtensions)."



- Advanced information: <soap:Fault><soap:Code><soap:Value>soap:Receiver</soap:Value></soap:Code><soap:Reason><soap:Text xml:lang="en">Server was unable to process request. ---&gt; Data at the root level is invalid. Line 1, position 1.</soap:Text></soap:Reason><soap: Detail /></soap:Fault>

RESOLVED. Went back to the query used in B above, and mysteriously it works now. This could be the "funny things happen" symptom I've been reading about. We'll see if it continues to function.

Here's what I've learned so far on Issue #5:

1. XML is confusing. XmlDP is even more confusing...whatever that is. Apparently it's new to/with Reporting Services in SharePoint integration mode?
2. There is far too little documentation regarding XmlDP and querying SharePoint lists from SQL Reporting Services.
3. According to http://msdn2.microsoft.com/en-us/library/ms159741.aspx: You must use the generic query designer to create the query. The query is not analyzed to identify parameters; therefore you must create parameters through the Parameter tab on the Dataset dialog box.

Tried this query:

<Query>
<SoapAction>http://schemas.microsoft.com/sharepoint/soap/GetListItems</SoapAction>
<Method Namespace="http://schemas.microsoft.com/sharepoint/soap/" Name="GetListItems" />
<Parameters>
<Parameter Name="listName">
<DefaultValue>myCustomReport2</DefaultValue>
</Parameter>
<OrderBy>
<FieldRef name="ows_Date" />
</OrderBy>
</Parameters>
</Query>
I was still prompted for the listName and the list returned with ows_Date still out of order. So maybe that proves this statement is true?
<translation: why make it convenient when it's already [somewhat] functional?>
4. When building the dataset, anything I place in the "Query string" box has to be enclosed in <query></query> tags or it errors out. I am trying to understand how to translate the CAML viewer query (which does work but isn't wrapped in query tags) into something that fits between query tags, and it hasn't gone well so far.
<translation: To be at one with your reports Grasshopper, you must understand xml>
5. According to http://srv1/sites/ReportCenter/_vti_bin/lists.asmx, "GetListItems" operation is supported
<translation: you're getting warmer...>
6. According to the soap output of my site http://srv1/sites/ReportCenter/_vti_bin/lists.asmx?op=GetListItems, I am only allowed to specify the following parameters: listName, viewName, query, viewFields, rowLimit, queryOptions and webID.
<translation: that and $3.00 will get you a cup of coffee>
7. According to http://www.sharepointblogs.com/dwise/archive/2007/11/28/connecting-sql-reporting-services-to-a-sharepoint-list-redux.aspx, only listName, viewName and rowLimit parameters are usable(?) (query and queryOptions may not be working properly with SSRS).
<translation: that kind of sucks>
8. When this started mysteriously working again, I tested using the "/" at the end of the namespace (wouldn't work without) and the "=" sign in front of the parameter value as defined on the parameter tab of the dataset (turns out to be optional).

As long as I can query SharePoint and get my list information back, I'm going to consider this issue closed and move on to actually using this information in a report. I'm sure that won't work right either, but why stop now?!

6. Ok, now that I can read SharePoint list data with SSRS, I need to try and actually do something with it.


A. Begin by setting up new data to report on


1. Create a new custom list (myCustomList2) and add a test record. This list will simulate the data we intend to submit to SharePoint using InfoPath, so it has a number of custom/created columns in it


2. Modify the working information from #5 above; add a new shared data source (myCustomList2DataSource), tell the dataset to use the new shared data source, change the information set in the parameter tab (Name => listName, Value => =myCustomList2) and test the dataset. Lucky me, it actually returned the record I entered on the first try.

B. Begin to build the report with the dataset returned

1. So, as I'm feeling good that it's actually reading the list in Step A without me threatening to destroy the machine, I look to the left in the Dataset results navigation pane in VS2005 and realize there are NO fields for me to choose to report on.
2. At this point, I have no clue what to do about getting this XML data into a report. Not that I have a large clue about SSRS otherwise, but I have at least been able to build SSRS reports when using a SQL database as the datasource. Fyi, http://msdn2.microsoft.com/en-us/library/ms159741.aspx states: "Reports can use data from XML documents and Web services, or embed XML in the query. There is no built-in support for retrieving XML documents from a SQL Server database.", in case anyone is trying to do that....

RESOLVED. Since the fields were not appearing automatically, I had started to add new fields to the dataset manually (if it doesn't work, beat it with a hammer, right?). I entered half a dozen or so fields, each one with the exact name as displayed in the query results, and then decided it would be fun to hit the refresh fields icon. As soon as I did that, all the fields shown in the query immediately appeared in the dataset and I was able to add them to a report. I don't know if I hit the refresh fields when working in issue #5, so I can't say if they are there now because of that, or because of adding some fields manually first, a combination of the two, or if it's just one of those mystery items I'm reading about with sharepoint reporting.

7. Rows are being returned even though they have NULL values (so far anyway), and most data is returning nicely for the bulk of the columns I created in the list I'm reporting on. Here are the current issues:


A. I have one column in my SharePoint list that is multi-lined text. If the text type is either rich text or enhanced rich text, the value returned in the query is formatted like this: "<div class=ExternalClassyadayada<div>My notes display here". If I set the column to be plain text, this doesn't happen. I will need to figure out how to filter that for my report.


RESOLVED. David Wise had some code listed at http://www.sharepointblogs.com/dwise/archive/2007/11/28/connecting-sql-reporting-services-to-a-sharepoint-list-redux.aspx that addressed this issue somewhat. I modified it to match my circumstances. Disclaimer: I'm not a programmer, so use this at your own risk.


function ExtractMultiLineText(myField as string) as string
dim strBegin as string
dim strEnd as string
dim myBegin as integer
dim myEnd as integer
dim myTextLength as integer
strBegin = "<div>"
strEnd = "</div>"
if myField = nothing then return ""
if myField = "" then return ""
myBegin = Instr(myField, strBegin) + Len(strBegin)
myEnd = Instr(myField, strEnd)
myTextLength = myEnd - myBegin

if myBegin < 1 then return myField
return mid(myField, myBegin, myTextLength)
end function

B. I have two columns in the SharePoint list that are set as number values with 0 decimal places. When I query those however in SSRS, they return with the number plus like 12 zeroes behind the decimal. Not a major issue, but I'll put it on the list anyway.


RESOLVED. Used the Int() function; =Int(Fields!ows_myProblemNumber)

C. If I go to "/_vti_bin/lists.asmx?op=GetListItems", it appears from the sample soap output that I am allowed to ask GetListItems for is: listName, viewName, query, viewFields, rowLimit, queryOptions and webID. As I mentioned in issue #5, according to http://www.sharepointblogs.com/dwise/archive/2007/11/28/connecting-sql-reporting-services-to-a-sharepoint-list-redux.aspx, only listName, viewName and rowLimit parameters are usable(?) and query and queryOptions may not be working properly with SSRS.

So, if I can't put anything other than this in my query string;

<Query>
<Method Namespace="http://schemas.microsoft.com/sharepoint/soap/" Name="GetListItems"/>
<SoapAction>http://schemas.microsoft.com/sharepoint/soap/GetListItems</SoapAction>
</Query>
and I only have a couple parameters to pass on top of that, then it appears to me that my options for filtering the amount of data returned from the SharePoint list is to figure out a way to do it with expressions in the parameters or in the report itself. Am I missing something? Is this really all I get to work with out-of-the-box?


More to come...


==================================
Installation steps taken to build the server VM:
==================================

(Using VirtualPC 2007)
1. Build OS (2003R2SP2) and promote to DC

1a. Create domain user service account SQLSPS
2. Prep for SQL

2a. Install ASP.NET, IIS (www) and .NET 2.0
3. Install SQL/SSRS 2005 Std

3a. Verify SSRS works
3b. RS config tool shows SP integration as blue exclamation
3c. Use service account SQLSPS for anything requiring Windows credentials
4. Install SQL2005 SP2

4a. Verify SSRS works
4b. RS config tool shows SP integration as blue exclamation
5. Prep for SharePoint

5a. Install .NET 3.0
6. Install MOSS2007 Enterprise and configure

6a. Webapps, SSP and anything else use svc acct SQLSPS
6b. Move Default website to port 8080 and re-activate (SharePoint stopped the site)
6c. Verify SSRS works on 8080
7. Install Reporting Services Add-in
8. Create new RS database in integration mode (with service credentials)

8a. RS config tool shows SP integration as red x
9. SP central admin /application management / manage integration settings

9a. reports server = http://srv1:8080/reportserver (since default port has been moved to 8080)
10. SP central admin /application management/grant database access

10a. using the FQDN, confirm servername and default instance - click OK
10b. Prompted for an account to use to retrieve information; svc acct SQLSPS did not work, domain admin did though
11. SP central admin / application management/set server defaults

11a. Accepted all defaults
12. Verify reportserver answers at http://srv1:8080/reportserver (via reportserver AppPool and DefaultAppPool)
13. Changed the application pool identity for the ReportServer AppPool from Network Service to the domain user service account I used for all SQL and SharePoint configurations (SQLSPS) and rebooted. The red x for sharepoint integration in the RS configuration tool is now a green checkmark.

View 7 Replies View Related

Server SQL-Server 2005 Crash While Indexing By The Server SharePoint 2007

Feb 7, 2008

Hello,


I have an intranet environment consists of two servers:

- An application server windows 2003 server with 64-bit server Sharepoint 2007 SP1 (Standard version for search)
- A database server windows 2003 server with 64-bit SQL Server 2005 SP2 64-bit (Standard - 9.0.3054)


It happens regularly that the database server crash (frozen black screen) while the application server indexes (crawl) the content of the intranet site based on sharepoint. There is no alert / error in the observer of events, nor in the sql logs.


The crash is uncertain: dice once all goes well, soon after that crash. When i set the parameter search service (service management research), I can define a regulatory impact of the robot to change the number of documents at the same time : "Request 2 documents at the same time, the crash is more rare, "ask 64 documents at a time, the crash is more common.


My Intranet under sharepoint is composed of a collection site with a dozen sub-sites. There are 3 large library of PDF document. All done in the 4 go in terms of volume.


My problem for about 3-4 months. (Maybe more. Before, indexing was not yet in place).

Someone could help me? Thank you

View 2 Replies View Related

HELP! Can't Figure This One Out!

Jan 19, 2004

I'm in desperate need of help. I'm setting up an intranet portal using DNN. I added an event calendar module, but whenever I try to add events to it, the system rejects it with a nasty Sql exception saying the conversion from char to datetime produced an out of bounds result.

The string the table uses to convert to datetime is (I have not modified it, the module is exactly as it came when i downloaded)

(convert(varchar,getdate(),101))

The whole stack trace for the error is:

Stack Trace:


[SqlException: La conversión del tipo de datos char a datetime produjo un valor datetime fuera de intervalo.]
System.Data.SqlClient.SqlCommand.ExecuteReader(Com mandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream) +642
System.Data.SqlClient.SqlCommand.ExecuteReader() +11
DotNetNuke.AVCalendarDB.Save() +1067
DotNetNuke.AVCalendarEdit.updateButton_Click(Objec t sender, EventArgs e) +3367
System.Web.UI.WebControls.LinkButton.OnClick(Event Args e) +108
System.Web.UI.WebControls.LinkButton.System.Web.UI .IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +57
System.Web.UI.Page.RaisePostBackEvent(IPostBackEve ntHandler sourceControl, String eventArgument) +18
System.Web.UI.Page.RaisePostBackEvent(NameValueCol lection postData) +138
System.Web.UI.Page.ProcessRequestMain() +1263

At first I thought it could be a language issue (DNN and the module are in english and my system runs XP Pro in Spanish) but I discarded it since it didn't work when I installed XP Pro in english

Any ideas?? I would really appreciate your help
Best regards from Chile
Javier L.

View 5 Replies View Related

Please Help Me Figure This One Out.

May 1, 2008



Hi, I am somewhat new to t-sql and what I do know is from trial & error and help from this forum. What I need to do is add a column of counts for id numbers that are not unique.

This is my code:

ALTER procedure [dbo].[rptMgmtRpt]


@RptMonthBegin datetime,

@RptMonthEnd datetime


as




SELECT Summons.ID, Convert(varchar, Venire.VenireDateTime,101)as VenireDateTime, Venire.VenireStatusID, Summons.SummonsStatusID, Summons.ExcusalCodeID,

Venire.ID AS VenireID, { fn WEEK(Venire.VenireDateTime) } AS wkNum, { fn DAYOFWEEK(Venire.VenireDateTime) } AS wkDay, Venire.JuryTypeID,

Venire.VenireLocationID, summons.compensationTypeID

,{fn IFNULL(AccountingTransactionRequest.PaymentAmount,0)} AS payAmt

,Convert(varchar,AccountingTransactionRequest.ServiceDate,101) as AcctServDate

,{fn count(summons.id)}

FROM Venire INNER JOIN

Summons ON Venire.ID = Summons.VenireID

LEFT outer JOIN

AccountingTransactionRequest ON Summons.ID = AccountingTransactionRequest.SummonsID

Where Venire.VenireLocationID <> 16 and (VenireDateTime BETWEEN @RptMonthBegin AND @RptMonthEnd)

ORDER BY wknum, wkday , Summons.ID

This is a sample of what I get for results:

640404 11/5/2007 128 144 2866 45 2 8 0 11/5/2007
640888 11/5/2007 128 32 2866 45 2 8 0 11/5/2007
640979 11/5/2007 32 2 2866 45 2 112 15 11/5/2007
640979 11/5/2007 32 2 2866 45 2 112 15 11/6/2007
640979 11/5/2007 32 2 2866 45 2 112 15 11/7/2007
641533 11/5/2007 128 32 2866 45 2 8 0 11/5/2007
641762 11/5/2007 128 144 2866 45 2 8 0 11/5/2007
641851 11/5/2007 1024 2 2866 45 2 8 0 11/5/2007




What I need are results that look like this:

640404 11/5/2007 128 144 2866 45 2 8 0 11/5/2007 1
640888 11/5/2007 128 32 2866 45 2 8 0 11/5/2007 1
640979 11/5/2007 32 2 2866 45 2 112 15 11/5/2007 1
640979 11/5/2007 32 2 2866 45 2 112 15 11/6/2007 2
640979 11/5/2007 32 2 2866 45 2 112 15 11/7/2007 3
641533 11/5/2007 128 32 2866 45 2 8 0 11/5/2007 1
641762 11/5/2007 128 144 2866 45 2 8 0 11/5/2007 1
641851 11/5/2007 1024 2 2866 45 2 8 0 11/5/2007 1


In the first column, 640979 appears three times.
How do I get that count in the last column of my results ?

Thanks so much for your help!!!

View 4 Replies View Related

Sharepoint SQL Server Databases

Apr 30, 2007

Hi:

I have several sharepoint sql server databases that have a double backslash in their

path name which is causing the backup exec software to fail. The log file path is:

C:logs\DBName.ldf

Whats the best way to fix this? Can I detach and reattach the database or run some alter command to correct this?



Thanks



View 3 Replies View Related

SharePoint Set Server Defaults

Feb 22, 2007

Hi guys,

I have a problem when configuring SharePoint and RS on two boxes. (If install everything in one box is fine)

I have SQL SP2 + RS in one server and SharePoint on the other server. I've followed every step in the SQL 2005 Online Book to configure the RS and SharePoint including:

How to: Install the Windows SharePoint Services Object Model on a Report Server Computer. (Done)
How to: Configure Service Accounts (Reporting Services Configuration). (Done)

However, I got an error when click "Set server defaults" in SharePoint Central Admin:
An unexpected error occurred while connecting to the report server. Verify that the report server is available and configured for SharePoint integrated mode.

In ReadMe of SQL SP2, see below: http://download.microsoft.com/download/f/d/c/fdcb3a53-0cb1-4e67-a1b6-45b89b3c59cf/readme_rsaddin.htm#_rsshare_known_problems_7

---
6.4 Service account requirements for Reporting Services


Restrictions on using built-in accounts apply to some deployment topologies of Reporting Services that include a report server running in SharePoint integrated mode. The following combination of factors will result in service account requirements:

The report server is integrated with a SharePoint farm comprising more than one computer.


The report server and SharePoint Central Administration Web site run on separate computers.



In this scenario, if either the Report Server Web service or Windows service runs under a built-in account such as NetworkService, the Grant database access option in SharePoint Central Administration will not work correctly. Consequently, accessing any Reporting Services feature through a SharePoint site will result in the following error:

"An unexpected error occurred while connecting to the report server. Verify that the report server is available and configured for SharePoint integrated mode. --> Server was unable to process request. --> Client found response content type of 'text/html; charset=utf-8', but expected 'text.xml'."

To avoid this error, choose one of the following approaches:

On the computer that hosts the report server, continue to run the Report Server Web service as NetworkService and add the built-in account, such as NT_AUTHORITYNetworkService to the WSS_WPG Windows group.


Configure the service accounts to run under a domain user account as follows:



Start the Reporting Services Configuration tool and connect to the report server.


Click Windows Service Identity, click Windows Account, type a domain user account, and click Apply.


Click Web Service Identity, for Report Server, click New, type an application pool name, click Windows Account, type a domain user account, and click Apply.


Reset IIS.


Restart the Windows service.

---

I did try it out but still get the error when clicking "Set server defaults" link in SharePoint Central Admin.

Is there something wrong with the two boxes installation???

Hope the MS guys can help.

Paul

View 8 Replies View Related

SQL Server 2005 And SharePoint Portal Server 2003

Nov 3, 2005

Hello all,

View 1 Replies View Related

Cannot Figure Query Out

May 2, 2008

I have a small database in which Employee's are linked to "Tags".   These tags can be red or yellow.  So,  I have 3 tables.
Tag_Colors :    tag_color_ID   ,    tag_colors
Employee_Table:  employeeID    ,      employee_name  
Tag_Table:      tagID,    tag_color_ID,   employeeID,   tag_notes
 
I need to be able to perform a query, where I can list employee's by the number of tags they have.
I don't even know how to get started on this  --     could anybody point me in the right direction?
After I can et a query working,  I think I can take it from there  -- and get it to display on a web page.
 
Thanks in advance. 
 
 
 
 
 
 
 
 

View 4 Replies View Related

Who's Smart Enough To Figure This Out?

Mar 9, 2004

Hello All,

I've been trying to get a range of values out of my SQL Server 2000 db without sucess. The field in question has the data type of char(8) and looks like this:

House_numbr_pub (leading spaces in front of each value)
140A
140
141
142
143
144
145
146
147
148
149
150
151
.
.
.
14500
.
.
.

Does anyone know how write a sql statement that will return 140-150, but excluding the ' 14500' and 100-1000. I tried the following where clause to return a range between 100-1000.

WHERE (cook.STREET_PUB LIKE 'lincoln%') AND (LEN(LTRIM(cook.HOUSE_NUMBR_PUB)) BETWEEN 3 AND 4) AND (
(LTRIM(cook.HOUSE_NUMBR_PUB) >= '100') and (LTRIM(cook.HOUSE_NUMBR_PUB) <= '1000') )

This where clause only return two records (100 and 1000). I want it to return 100-1000.

I also tried the following where clause:

WHERE LTrim(cook.HOUSE_NUMBR_PUB) like '1[45][0-9]'
OR
LTrim(cook.HOUSE_NUMBR_PUB) like '1[45][0-9]'

However, building this on the fly with .net will take some effort if someone is trying to search range 1-10000000.


Please Help,

James

View 3 Replies View Related

Can't Figure Out This Query

Feb 21, 2007

I have a table with multiple records.

Some have the same value in the 'subkey' field.
I want to select all the records from the table that have their highest MAINKEY.

So say there were 4 records in the table that has 3 fields (id, subkey and mainkey)

Each record has a unique id field but the subkeys are the same for the first two and the sub keys are the same for the last two while the Mainkey can be different.

So the tables looks sort of lLike this:

ID SK MK
1 10 2
2 10 3
3 25 2
4 25 3

I want to query and select one record for each subkey, but I want it to be record that has the highest mainkey. In this case, it would be records with ID 2 and 4.

I can not figure this out. :eek:

Any help would be GREATLY appreciated.

View 3 Replies View Related

Figure Is Rounding Up

Mar 13, 2007

I have the following sp which is appending records into my table. However the values appended are being round up eg

SC_PrimaryPupilPrice is 1.5

but when it is inserted into the sql table it is 2

The field in the sql table is numeric.

CREATE PROCEDURE spSM_AddWeeksandMealPrices

@dteWeekEnding datetime

as

INSERT INTO tblSM_Meals
(ML_Id,
ML_WeekEnding,
ML_SchoolNumber,
ML_PupilMealPrice,
ML_AdultMealPrice,
ML_SpillagesMealPrice,
ML_AdultRechargeMealPrice,
ML_ReservedMealPrice)

select convert(varchar,@dteWeekEnding ,103) + '*' + cast(SC_SchoolNumber as varchar(10)) , convert(datetime,@dteWeekEnding ,106),
SC_SchoolNumber,
SC_PrimaryPupilPrice,
SC_PrimaryAdultPrice,
SC_PrimaryPupilPrice,
SC_PrimaryAdultPrice,
SC_PrimaryAdultPrice
from tblSM_Schools
GO

View 4 Replies View Related

Can't Figure Out This Error!

Jun 8, 2007

Here is my code below. When I attempt to run the data flow task that calls this script, I get this error:



"Index was outside the bounds of the array."



I honestly do not know what the problem is here. There are definitely 6 columns in the file. In fact, even if comment out everything except the first line (myCol1), I still get the "Index was outside the bounds of the array." error.



Any ideas??? Need help.



Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

Dim rowValues As String()

rowValues = Row.Line.Split(columnDelimiter) 'parse row by comma

If Row.Line Like "*OPENING BALANCE*" Then

Row.AccountNumber = Nothing 'set to null for conditional split

Row.OpeningBalance = CDec(rowValues(3)) 'get the 4th value



ElseIf Row.Line Like "*CLOSING BALANCE*" Then

Row.AccountNumber = Nothing 'set to null for conditional split

Row.ClosingBalance = CDec(rowValues(2)) 'get the 3rd value



Else

Row.myCol1 = CStr(rowValues(0)).Replace("""", String.Empty)

Row.myCol2 = CStr(rowValues(1)).Replace("""", String.Empty)

Row.myCol3 = CStr(rowValues(2)).Replace("""", String.Empty)

Row.myCol4 = CStr(rowValues(3)).Replace("""", String.Empty)

Row.myCol5 = CStr(rowValues(4)).Replace("""", String.Empty)

Row.myCol6 = CDec(rowValues(5))



End If

End Sub

View 10 Replies View Related

SQL Server Express And Sharepoint SQL Version

Oct 10, 2006

I created an ASP application using Web Developer Express and SQL Server Express on my workstation. Everything works great. I copied the application and all files to my intranet server which is a 2003 server running Sharepoint. When I attempt to run the application from the server.Access to the ADO.net Managed Provider 'SqlClientFactory' was denied in the data source with ID 'SqlDataSource' because of security settingsIs there something in the web config that I must change or settings on the server that must be changed for the application to run?

View 2 Replies View Related

SQL Server Certified For Vista And MS SharePoint

Oct 29, 2007

Hello,

I have (2) questions about SQL versions and (1) question about "Standard Log Sizing" for SharePoint directory. I am pretty sure about SQL 2000 versions "Not being Certified".

1) Which versions of MS SQL Server 2000 and 2005 are certified to run on Vista?

2) Which versions of MS SQL Server 2000 and 2005 are certified to run on Internet Explorer 7 (IE7)?

3) What should the "Standard Log Sizing" be for MS SharePoint Server drive be? Or is there an actual standard?

Thank you.

View 4 Replies View Related

SQL Server Compatibility With SharePoint Workflows

Dec 7, 2007

I am a beginner with SQL Server and SharePoint. I am trying to migratethe database to other servers that may be running differentplatforms. I know that the other servers have SharePoint so they arerunning SQL Server 2005, SQL Server 2003, and Access. Is there a way Icould get data from my system (with SQL Server 2005) to another system(that may be running 2005, 2003, or Access) without affecting my dataor SharePoint workflow? I want the people running the other servers tobe able to access the same data and see the same workflow I do. Thanksin advance!

View 1 Replies View Related

SQL Server 2005 Output To Sharepoint

Mar 3, 2008

Can SQL Server 2005 query results be output to a list or document library in SharePoint?

View 1 Replies View Related

Sharepoint Integration With Report Server

Dec 22, 2006

Hi,

I am trying to integrate SQL SERVER 2005 ( Version 9.00.3033.00) with sharepoint server using the Add-in. In Add Application Management -- > Reporting Services Trusted Accounts, When i provide the credentials it is giving a message





Some or all identity references could not be translated.

I have installed the latest SQL SERVER 2005 from http://www.microsoft.com/downloads/details.aspx?FamilyID=4E50BE6E-3F92-4552-A78C-B3BE1D94D5DA&displaylang=en

Am i missing something ?

Thanks & Regards

Kiran Kumar

View 2 Replies View Related

New On Asp.net 2.0 Trying To Figure Out How To Connect To A Database

Mar 22, 2007

Right now I'm just looking at websites how to create a database and putting it in the App_Data folder which is no problem.  What I want to do is Programmatically do it.  in the example code that I see it says:
Dim ConnString as string = ConfigurationManager.ConnectionStrings(connStringName).ConnectionString
or
Dim MyConnString as new sqlconnection = New SqlConnection(ConfigurationManager.ConnectionStrings(connStringName).ConnectionString)
now every time I put the connStringName in the parenthasis it tells me that its an error: Name connstringName is not declared.
I figure the connstringName is the name in the connectionStrings in the web.config.
 So I am doing something wrong here, I would appreciate any help.

View 1 Replies View Related

Can't Figure Out Error Message In SQL

Oct 16, 2007

Can someone please look at my stored procedure? I am trying to create the following stored procedure, but get the following errormessages:
Msg 102, Level 15, State 1, Procedure InsertWork, Line 3Incorrect syntax near '7'.Msg 102, Level 15, State 1, Procedure InsertWork, Line 25Incorrect syntax near '@7am8am'.
USE [Work]GO
SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE PROCEDURE [dbo].[InsertWork](  7am8am       nvarchar(500),  8am9am       nvarchar(500),  9am10am       nvarchar(500),  10am11am      nvarchar(500),                  11am12noon    nvarchar(500),                12Noon1pm     nvarchar(500),  1pm2pm       nvarchar(500),  2pm3pm       nvarchar(500),  3pm4pm        nvarchar(500),  4pm5pm       nvarchar(500),                  5pm6pm       nvarchar(500),  6pm7pm       nvarchar(500),  7pm8pm       nvarchar(500),  8pm9pm        nvarchar(500),  9pm10pm       nvarchar(500),                  10pm11pm      nvarchar(500),  Notes         nvarchar(500),         Date           nvarchar(15))AS BEGIN INSERT INTO WorkDay VALUES                 @7am8am,                 @8am9am,  @9am10am,   @10am11am,   @11am12Noon,  @12Noon1pm,  @1pm2pm,  @2pm3pm,  @3pm4pm,  @4pm5pm,  @5pm6pm,  @6pm7pm,  @7pm8pm,   @8pm9pm,  @9pm10pm,  @10pm11pm,  @Notes,  @DateEND
 

View 3 Replies View Related

Trying To Figure Out A Suitable Sql Structure

Oct 17, 2007

I'm new to sql and have come up with a problem and can't seem to find the answer. Most would probably find it simple but I cant get my head around it! :p
I have the following table structure
User_Table-----------User_ID (Key)FirstNameLastName
Contacts_Table--------------User_ID (F key)Contact_User_ID
User_Table stores all users details and assigns them a User_ID. Users can add other users to their contacts, and this will be stored in Contacts_Table on a one-to-many basis.
*deep breath*... So User_ID in Contacts_Table, will store the User_ID from User_Table, and the Contact_User_ID in Contacts_Table will store the User_ID from User_Table. Does this seem ok? Sorry if I confused everyone!
So my question is, how do I select a user and show all his contacts (names etc)? I thought I could use innerjoin but I dont think it would work here.
Any ideas?
Thanks!

View 3 Replies View Related

I Need A Very Good SQL Genious To Help Me Figure Out How To Do This....

Sep 15, 2005

I have a simple table right now that has some rows listed like this:Table Name = TicketStatusTicketNumber      TicketType        Status         Time1                             Normal               In                 09/15/2005 10:50:213                             Normal               In                 09/11/2005 19:25:101                             Normal               Out              09/15/2005 11:45:103                             Normal               Out              09/11/2005 20:27:092                             Normal               In                 09/14/2005 17:25:101                             Normal               Pay              09/15/2005 11:15:152                             Normal               Out              09/14/2005 21:45:30What I want to do is select only 1 row per ticket number, and this row needs to be the row that has the LATEST time for that particular ticket number.  Then I want to sort the results by ticket number decending.  So for instance, the select I am looking for would bring me back ONLY the following rows in the following order: TicketNumber      TicketType        Status         Time3                             Normal               Out              09/11/2005 20:27:092                             Normal               Out              09/14/2005 21:45:301                             Normal               Out              09/15/2005 11:45:10My issue is I do not know how to go about selecting ONLY 1 row per ticket number, and the row I select has to be the row with the latest date for that particular ticket number.Can any SQL gurus provide me with some code in order to do this?  Thanks so much for the help guys!

View 1 Replies View Related

Invalid Column Name...can't Figure This Out

Feb 25, 2005

The following is an approximation of the code I'm trying:

SELECT 'dummy' as DummyField, FieldA, FieldB, FieldC,
FieldD, FieldE, FieldF, FieldG, FieldH, FieldI,
FieldJ, FieldK, FieldL, AVG(AMT) AS AMT,
AVG(QTY) AS QTY, FieldM

FROM GetMonthlyData('200501') as i

GROUP BY DummyField, FieldA, FieldB, FieldC,
FieldD, FieldE, FieldF, FieldG, FieldH, FieldI,
FieldJ, FieldK, FieldL, FieldM

It checks out OK syntactically but whenever I try to run it I get a message like the following:

Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'DummyField'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'DummyField'.

I've also tried not giving DummyField an alias and using the 'dummy' value in the GROUP BY clause but that won't even get by the CheckMark button:

Server: Msg 164, Level 15, State 1, Line 8
GROUP BY expressions must refer to column names that appear in the select list.

:confused: Advice?

View 8 Replies View Related

How To Join Data - Can't Figure This One Out

May 22, 2008

Hi -

I have three tables that I am trying to join in order to produce some report output. I have tried numerous ways, but they all don't give the results I need to provide.

Below is the table creation, some inserts for data and the output I am looking for.

CREATE TABLE [SalesAcct]
(
[EmpName] [varchar] (100),
[Acct_Num] [varchar](15),
[Acct_Name] [varchar] (50)

)


CREATE TABLE [Perf]
(
[Acct_Num] [varchar](15),
[PerfDate] [smalldatetime],
[Channel] [varchar](25),
[Medium] [varchar](25),
[Clicks] [int],
[Impressions] [int]
)



CREATE TABLE [Revenue]
(
[Acct_Num] [varchar](15),
[RevenueDate] [smalldatetime],
[Channel] [varchar](25),
[Medium] [varchar](25),
[ServiceLevel] [varchar],
[TransactionType] [varchar],
[GrossRev] [decimal](18, 2)
)

INSERT INTO SalesAcct (EmpName, Acct_Num, Acct_Name)
VALUES ('BobSmith', '100XYZ', 'My Account')

INSERT INTO Perf (Acct_NUm, PerfDate, Channel, Medium, Clicks, Impressions)
VALUES ('100XYZ', '05-11-2008', 'US', 'Net', 240, 500)

INSERT INTO Revenue (Acct_Num, RevenueDate, Channel, Medium, GrossRev)
VALUES ('100XYZ', '05-11-2008', 'US', 'Net',500.50)
INSERT INTO Revenue (Acct_Num, RevenueDate, Channel, Medium, GrossRev)
VALUES ('100XYZ', '05-11-2008', 'US', 'Cable',23.75)



What I need to provide to mgmt is two reports...one is rolled up by EmpName and AcctNum and Date, and the other is details like:

Suumary_Acct_By_Day:
EMPName | Acct_Num | Acct_Name | Date Rev $ | Clicks | Impressions
BobSmith| X0000005 | My Account | 2008-05-11 |524.25| 240500


Detail_Acct_By_Day:
EMPName | Acct_Num | Channel | Medium | Date | Rev $ | Clicks | Impressions
BobSmith | X0000005 | US | Net | 2008-05-11 | 500.50 |240 | 500
BobSmith | X0000005 | US | Cable | 2008-05-11 | 23.75 | 0 | 0

My latest query, which doesn't work is:
select S.EmpName, S.acct_num, R.RevenueDate as Date, coalesce(R.Channel, P.channel)as channel,
coalesce(R.medium, P.medium), GrossRev, coalesce(P.clicks, ''), coalesce(P.impressions, '')
FROM SalesAcct AS S
left outer join Revenue as R on S.acct_num = R.acct_num
left outer join Perf as P on S.acct_num = P.acct_num

Yields two rows...but the Medium duplicates and the Clicks and Impressions also get duplicated when they should be zero.

Any help would be appreciated.

Thank you,
Will

View 4 Replies View Related

Complicated Query? Cant Figure Out What I Need...

Jan 3, 2006

I have run into a problem, I have 2 fields in my database, both key fields:

Table 1
=====
Field X <key>
Field Y <key>

In field X, there are say about 3 records for each unique Field Y. I let my users query the data base like follows:

Enter the Codes you want: 1000 and 3000 and 8500

So I want to pick up records where there will be the above values for All Y values. i.e 1000/AAA, 3000/AAA, and 8500 for AAA - if there is even ONE of the X values not matching a record without a matching X value, leave it out.

i.e:
X=1000,Y=AAA
X=3000,Y=AAA
X=8500,Y=AAA
X=1000,Y=BBB
X=3000,Y=BBB
X=8500,Y=BBB
X=1000,Y=CCC
X=3000,Y=CCC
X=9999,Y=CCC

When the query runs, I want to see the following records:

X=1000,Y=AAA
X=3000,Y=AAA
X=8500,Y=AAA
X=1000,Y=BBB
X=3000,Y=BBB
X=8500,Y=BBB

BUT NOT:

X=1000,Y=CCC
X=3000,Y=CCC
X=9999,Y=CCC

because one of the X values was not matched (the last X value =9999 and not one of the requirements of the search)

So I guess I want something like this:

SELECT X,Y from TABLE1 WHERE ALL Y VALUES HAVE ALL OF THESE X VALUES (X=1000,X=3000,X=8500) IF ANY X VALUES ARE MISSING SKIP RECORD

^^ Hope the above makes sense... but I am really stuck. The only other way I think I could do it is, copy all records that match all 3 X values into a temp table, and weed out any that are missing any one of the X values after they are copied but, I am running this on MYSQL 5.0 Clustered, and there is not enough room in memory for it probably... and query time has to remain under a second.

Anyhelp would be appreciated...

View 5 Replies View Related

Calculation For Accumulated Figure

Feb 27, 2008

I've created SQL statement to display data that include Project ID, WBS Element, DebitTotal, CreditTotal, Net (net figure is sum between DebitTotal and CreditTotal = CreditTotal - DebitTotal), FiscalPeriod and FiscalYear. To display data I need to key-in the timekey. If 200702 refer to data for February 2007.

The problem is, how to calculate accumulated figure for Net? I have to display YearToDate figure which is accumulated figure for every month for Net table.
e.g Net for February is 1000, March is 2000. YearToDate should be 3000 (1000+2000).
Another problem is because I'm using the timekey to display the data, table name is the same for every month.
How to accumulate the Net figure? Please help.

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved