i have 3 question :
1. CAN MY MICROSOFT ACCESS Database imported to sql server 2005 ?
2. can i add field for queries with custom calculation like field 1*fiedld 2. like access can do?
3. is that any wizard to do no 1 and 2?
thanks
i have 3 question :
1. CAN MY MICROSOFT ACCESS Database imported to sql server 2005 ?
2. can i add field for queries with custom calculation like field 1*fiedld 2. like access can do?
3. is that any wizard to do no 1 and 2?
thanks
Hi experts,I am working on SQL server 2005 reporting services and i am getting aproblem in writting a query.Situation is given below.There is one table in database Named ChildNow i have to find the All childrens whoes Age is 13 years Base onSome given parameter.If User select Augus 2007 then It has to calculate the Childs who bornin August 1994 And if he select September Then queryshould show only those child Who born in September 1994 and soon..... And use can select another year month also likeAugust 2009 ...I am writting the following querySelect Child_Name, DOb from Childwhere ((CONVERT(DateTime, A.Date_Of_Birth, 103) >= @ Parameter1And (CONVERT(DateTime, A.Date_Of_Birth, 103) <= @Parameter2)If i know already month and year then i can write easily parameter1and parameter2 But since these are comming from user so i m notfinding how to handle this.Now please suggest me what i have to write in Where statement I thinka lot but not getting any idea about it.Any help wil be appriciated.RegardsDinesh
View 2 Replies View RelatedI'm importing data from a text file into SQL as a varchar, and I'm leaving it a varchar in its final destination table. It is essentially a price, i.e., $25.65. I'm using this price field (varchar) to perform a calculation...
Everything seems to work OK, but I'm not sure about using this varchar field to perform this calculation. Is this doable, or should it absolutely be converted to say, decimal?
Hi,
This seems simple but can't find. Say in my report I have 3 fields(A,B and C).
In field C I want to give a calcultion based on fields A and B. Say field C value = A+B
How do you do this? In expressions I couldn't see existing report fields, it only shows dataset columns.
Sonny
I have created calcalated measures in a SQL Server 2012 SSAS multi dimensional model by creating empty measures in the cube and use scope statements to fill the calculation.
(so I can use measure security on calculations
as explained here  )
SCOPE [Measures].[C];
THIS = IIF([B]=0,0,[Measures].[A]/[Measures].[B]);
Hello Friends
I have 3 tables
1) Product Id, ShortName
2) IncomingStockId, ProductId, Quantity, InDate
3) OutGoingStock Id, OutDate, ProductId, Quantity
I need to get the results like thisProduct name, quantity in stock today
the "quantity in stock today" = sum (quantity recieved) -sum (quantity sent)
Thank you for your timeSara
how do i calculate something like this if I have the table with names and count?
Name Count Percent
Name1 27 4.69%
Name2 2 0.35%
....
Totals 576 100.00%
Hi I am having to convert some oracle reports to Reporting Services. Where I am having difficulty is with the
calculations.
Oracle
TO_DATE(TO_CHAR(Visit Date+Visit Time/24/60/60,'DD-Mon-YYYY HH24:MISS'),'DD-Mon-YYYY HH24:MISS')
this is a sfar as I have got with the sql version
SQLSERVER2005
= DateAdd("s",Fields!VISIT_DATE.Value,Fields!VISIT_TIME.Value246060 )
visit_date is date datatype visit_time is number datatype. have removed : from MI(here)SS as was showing as smiley.
using:
VS 2005 BI Tools
SQLServer 2005
I'm trying to run a dyncamic query that returns all records within a specific distance of a certain point. The longitude and latitude of each record is stored in the database. The query is constructed from two dynamic variables $StartLatitude and $StartLongitude with represent the starting point.
SELECT UserID, ACOS(SIN($StartLatitude * PI() / 180) * SIN(Latitude * PI() / 180) + COS($StartLatitude * PI() / 180) * COS(Latitude * PI() / 180) * COS(($StartLongitude - Longitude) * PI() / 180)) * 180 / PI() * 60 * 1.1515 AS Distance
FROM HPN_Painters
HAVING (Distance <= 150)
It runs fine until I add the 'HAVING (Distance <= 150)' clause, in which I recieve the error: Invalid column name 'Distance' It seems that Distance cannot be referenced in the HAVING clause.
I have a table with the following creation sql script
CREATETABLE [dbo].[TEST_ENROLLMENT](
     [ENROLLMENT_ID] [int]
NOTNULL,
     [COURSE_OFFERING_ID] [int]
NOTNULL,
     [STUDENT_ID] [int]
[Code] ....
Below is the sql insert for the above table’s data
INSERTINTO TEST_ENROLLMENT(ENROLLMENT_ID,COURSE_OFFERING_ID,STUDENT_ID,DEPT_ID,COURSE_CODE,CREDIT,SECTION_ID,
SEMESTER,ACAD_LEVEL,ACAD_YEAR,DATE_ENROLLED,[STATUS],TOTAL_MARK,GRADE_LETTER,QPE,Honor,INSTRUCTOR_ID,USERNAME
)VALUES(877,356,104,15,'IRD
[Code] ...
I was trying to calculate GPA and Commulative GPA (CGPA) for this student and the formula of GPA is SUM(Honor)/SUM(CREDIT) and the formula of the CGPA is the Average of the SUM(Honor)/SUM(CREDIT) grouped by semester and I wrote the below query which is calculating the GPA correctly but wrongly calculating the CGPA.
How to get the correct CGPA shown in the below desired result set table.
select x.STUDENT_ID,AVG(x.gpa)as
GPA,AVG(y.gpa)AS'COMMULATIVE GPA'
from
(
select STUDENT_ID,SUM(Honor)/SUM(CREDIT)
gpa,ACAD_YEAR,SEMESTER
[Code] ....
Current result set (Incorrect CUMULATIVE GPA)
STUDENT_ID GPA CUMULATIVE GPA
104 2.9 2.9
104 2.25 2.25
104 3 3
Â
Desired result set
STUDENT_ID GPA COMMUTATIVE GPA
104 2.9 2.9
104 2.25 2.575
104 3 2.716
Hi,
I have a query which gives me the following results:
lLedgerCode sGLCode bDebit TotalSum sGLDesc
61 6843000701 0 600ALPS Holding
33 8345000701 0 1116ALPS Premium Due
56 1000000701 0 1116Regular Premium Income
63 6836000701 1 516ALPS Group Holding
61 6843000701 1 600ALPS Holding
30 6842000701 1 600ALPS Policy Clearing
33 8345000701 1 1116ALPS Premium Due
The Column bDebit has either value '0' or value '1' in it ('0' being debit - positive amount, and '1' - credit, negative amount).
I would like it to show the net amount for each account. Therefore in plain English I would like to take all GLCodes that are the same (eg 6843000701) and sum all amounts that have debit value of '0' and subtract all amounts that have debit value '1'. Therefore I would only see '6843000701' code once, and the amount would be '0' becase 600 - 600 = 0.
The current query is:
SELECT dbo.tbGLTransactions.lLedgerCode, dbo.tbGLTransactions.sGLCode, dbo.tbGLTransactions.bDebit, SUM(curAmount)As TotalSum, dbo.tbLedgerCode.sGLDesc
FROM dbo.tbGLTransactions
INNER JOIN dbo.tbLedgerCode
on dbo.tbGLTransactions.lLedgerCode = dbo.tbLedgerCode.lLedgerCode
WHERE dbo.tbGLTransactions.lGLExtractRun = '452'
Group By dbo.tbGLTransactions.lLedgerCode, dbo.tbGLTransactions.sGLCode, dbo.tbGLTransactions.bDebit, dbo.tbLedgerCode.sGLDesc
Order By dbo.tbGLTransactions.bDebit, dbo.tbLedgerCode.sGLDesc
Is someone able to help me as to how i need to modify this query to get the desired result?
Thanks!
Hi,
I get a no. of seconds (like '33450', varchar) for each day and I have a day field (like '19.10.2004', varchar).
How can I easily convert it into a datetime-field (like 2004-10-19 09:17:50) ?
Does anybody has an idea ?
:confused:
This is my syntax, and if I print the value that is stored in each variable @goodtries = 120 @badtries = 25 but when I run the syntax below it gives me 0.00
Declare @goodtries as int, @badtries as int
select @goodtries = convert(decimal(18,4),count(userID))
from table1
WHERE logintype IN ('Valid', 'Success')
select @badtries = convert(decimal(18,4),count(userID))
[code].....
I've made another topic before concerning this problem, but since it was really confusing, I will made one clearer (it was about orthodromic formula, in case you read it, but the problem change during the topic, so thats why im creating this new one too).
I have a stored procedure with custom pagin method inside, and I want to sort my records on a fields I create myself (which will receive a different value for each record.) Now, I want to sort on this temporary field. And since this is a custom paging method I can choose between many page. Now, for the first page, it sorts fine. But when I choose a page above the first one, the sorting is not right (the results all are wrong).
So my real question is: is it really possible to sort on a Temporary Field in a custom paging method (because I know I can do it without any problem on a real field from my table, it just doesnt work right when I try on a temporary field). I tried to solve my problem with this little SQL instruction, but it didnt give me any result yet:
SELECT TOP 20 PK, test = field_value FROM Table WHERE PK not in (SELECT TOP 10 ad_id FROM Table ORDER BY ?) ORDER BY ?
well thanks for taking the time to read this, any help woulb be appreciated.
Hi,
My report has multiple datasets, and I want to access the fields of a particular dataset from custom code. The problem is that the name of the dataset to use is only calculated during the execution of the custom code.
Allow me to illustrate:
public function Test(data as DataSets, fieldName as string)dim datasetName as string = CalculateDatasetName()dim ds as Dataset = data(datasetName)' now here I want to get the fieldName out of the dataset.' in the report, I would do something like
' First(Fields!fieldName.Value, datasetName)' but in custom code, this obviously doesn't work.end function
I've been looking for a way to accomplish this, but it seems you cannot get data from a dataset through custom code (there is only the commandtext property). Also it is not possible to pass the Fields collection as a parameter, as I do not know the dataset name when in the report designer.
I hope my problem description is clear.
Does anyone know how to solve this issue?
Thanks,
Phil
SELECT ArticleID, ArticleTitle, ArticleDate, Published, PublishDate, PublishEndDateFROM UserArticlesWHERE (ArticleDate >= PublishDate) AND (ArticleDate <= PublishEndDate)
When I use the above on a GridView I get nothing. Using SQL manager it works great.
I don't know how to pass the value of the ArticleDate field as a default parameter or I think that's what I don't know how to do.
I am trying to create a app that I can set the dates an article will appear on a page and then go away depending on the date.
Thanks for any help!
Hi,
I am creating a custom transformation component, and a custom user interface for that component.
In
my custom UI, I want to show the custom properties, and allow users to
edit these properties similar to how the advanced editor shows the
properties.
I know in my UI I need to create a "Property Grid".
In
the properties of this grid, I can select the object I want to display
data for, however, the only objects that appear are the objects that I
have already created within this UI, and not the actual component
object with the custom properties.
How do I go about getting the properties for my transformation component listed in this property grid?
I am writing in C#.
Hi,
I want to update value of a custom field for a perticular project in Project Server 2007 using PSI.
I have created 5 enterprise custom fields(A,B,C,D,E) through PWA/Server Settings.
I want to search all Projects on Server. If any project is having value for custom field A then I want to update rest of the custom fields(B,C,D,E) for that perticular project.
I dont know how to do it please help.
Thanks in Advance
Madhukar
When I first saw SqlCacheDependancy and Query Notifications I was really stoked. I began using these features in ASP.NET and some of this caching really sped up my site. However, I noticed some of my queries and results were not cached. This led me on a looonnnnggggg search of the internet for anything related to these topics.
Anyway, the problem is that the queries are very limited (http://msdn2.microsoft.com/en-us/library/ms181122.aspx) in what they can contain, no count(*), no top, no order by, etc etc. I understand that the reason for this may be that the Query Notifications is using some of the Indexed Views infrastructure and therefore has the same limitations. That's fine and all but caching the results of these types of queries is still very useful for a front page of a site where you want the top ten users by number of postings etc and if you use the TOP N clause to limit it to the top 10 then you lose the built in caching functionality because this query doesn't work with Query Notifications and the cache expires immediately.
Is there a way to just get notifications when the tables involved in a query changes? I don't need the granular level of the rowsets involved I just want to be notified if my table has changed so I can refresh my cached objects.
I know there is an overload for the constructor of the SqlCacheDependency class which takes the database and table names as parameters. MSDN states that this is for Sql Server 7 and 2000 but would this also work in my scenerio? I know it wouldn't be auto-magical but if I can still get pushed notifications of changes instead of polling for notifications I would believe this to be a more efficient solution.
Query Notifications are fantastic but the query limitations make real world usage a pain, it's like have a nice red sports car but finding out you can only drive it whens it is raining and then you can only make right turns.
Regards
Hi,I
am working on this select query for a report on website users. The
resulting rows will be displayed in a datagrid with custom paging. I
want to fetch 100 rows each time. This is the simplified query,
@currpage is passed as a parameter. ________________________________________________________________________________DECLARE @table TABLE (rowid INT IDENTITY(1,1), userid INT)INSERT INTO @table (userid) SELECT userid FROM UsersSELECT T.rowid, T.userid, ISNULL(O.userid, 0)FROM @table TLEFT OUTER JOIN ( SELECT DISTINCT(userid) FROM orders)AS OON O.userid = T.useridAND T.rowid > ((@currpage-1) * 100) AND T.rowid <= (@currpage * 100)ORDER BY T.rowid________________________________________________________________________________If
I run this query it returns all the rows, not just the 100 rows
corresponding to the @currpage value. What am I doing wrong? (The
second table with left outer join is there as I need one field to
indicate whether the user has placed an order with us or not. If the
value is 0, the user has not placed any orders) Thanks.
Hi,
Is it possible to connect to a database and fetch data from it from within custom code inside a report?
Appreciate any help.
Regards,
Asim.
Hi
in the acquireconnection method Using the below statment I can get a connection Object
oledbConnection = cmado.AcquireConnection(transaction) as OleDbConnection;
from the connection object I can get the connectionstring from the object by calling
oledbConnection.connectionstring() property which will have all the details like DataBase, UserName & other Inofrmation but there is no password Info.
How to get the password Information, I need that information since I will use that info to make OCI calls to fetch the data from the Oracle database in m,y custome source component.
any help is much appriciated
thanks in advance.
Hi experts,
I have a custom code that successfully query my database in SSRS 2005 report.
However, I am hard coding its connection string (in the custom code) and I want it to be able to use the same connection string as the ones that the report use. This information is available in my registry setting, but if it is possible, I want to avoid that, since I aim for a quite simple solution. If I am able to get the connection string details at the report level or from within the custom code, it would be great.
Anybody have a suggestion? Anything would be really appreciated.
Thanks
I am building myself a datadriven menu control and I have got one table where I store all menu items as rows. On each row there is a column for roles, in this column I have added one or several roles as a string. Today I can retrieve all menu items (rows) for a requested role, but how can I retrieve menu items for two or more roles? I have each role inserted as rows in a temp table (@Role), if that makes it easier?DECLARE @Role TABLE ( role varchar(15)) SELECT SiteMap.Id, SiteMap.Title, SiteMap.UrlFROM SiteMapWHERE (CHARINDEX(@Roles, SiteMap.Roles) > 0 ) Regards, Sigurd
View 2 Replies View RelatedI created a function to use in a View, works similar to DATEADD but only with my company's Business days Monday-Thursday.
I am running a query but it never ends to run.
This is the function:
USE [RA_dev]
GO
/****** Object: UserDefinedFunction [Production].[GBDATEADD] Script Date: 4/11/2015 5:58:19 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
[Code] ...
This is how I am trying to use it in the View:
Production.GBDATEADD(Production.Schedule.START_DATE, Production.Operations_Data_Pool.MFG_DAY - 1) AS SCH_DATE
I have some custom code in my report <Code> element that I use to query a database. I have hardcoded the connectionstring in this code but I would like to use the same datasource that the report uses. Is there some report property that allows me to access this datasource?
View 11 Replies View RelatedHow to Protect From SQL Injection in ASP.NET and SQL 2005 for custom query expression?In my project, I allow user to custom query expression through UI, such asstring queryCondition=' sale>20 and sale <100'string queryCondition=' createDate>"10/10/2005"'string queryCondition='Fullname like "%Paul%" '...I construct SQL based the queryCondition string, such as string mysql='select * from mytable where '+queryConditionI know it's very dangerous because of SQL Injection, but it's very convenient for user to custom query expressionCould you tell me how to do? many thanks!
View 13 Replies View RelatedI have the following:
create table testdata
(
StepName varchar(25)
,StepStatus char(25)
,EntryDate datetime)
insert testdata values ('Step1','Starting',getdate())
insert testdata values ('Step2','Starting',getdate())
insert testdata values ('Step3','Starting',getdate())
insert testdata values ('Step4','Starting',getdate())
insert testdata values ('Step1','Finished',getdate())
insert testdata values ('Step2','Finished',getdate())
insert testdata values ('Step3','Finished',getdate())
I need a query that will basically return Step 4 hasn't finished.
I have tried a few ways but can't get it to work...
Hi,
I've created a Custom Data Flow Component and added some Custom Properties.
I want the user to set the contents using an expression. I did some research and come up with the folowing:
Code Snippet
IDTSCustomProperty90 SourceTableProperty = ComponentMetaData.CustomPropertyCollection.New();
SourceTableProperty.ExpressionType = DTSCustomPropertyExpressionType.CPET_NOTIFY;
SourceTableProperty.Name = "SourceTable";
But it doesn't work, if I enter @[System:ackageName] in the field. It comes out "@[System:ackageName]" instead of the actual package name.
I'm also unable to find how I can tell the designer to show the Expression editor. I would like to see the elipses (...) next to my field.
Any help would be greatly appreciated!
Thank you
Hi,
I'm trying to enable Expression for a custom property in my custom data flow component.
Here is the code I wrote to declare the custom property:
public override void ProvideComponentProperties()
{
ComponentMetaData.RuntimeConnectionCollection.RemoveAll();
RemoveAllInputsOutputsAndCustomProperties();
IDTSCustomProperty90 prop = ComponentMetaData.CustomPropertyCollection.New();
prop.Name = "MyProperty";
prop.Description = "My property description";
prop.Value = string.Empty;
prop.ExpressionType = DTSCustomPropertyExpressionType.CPET_NOTIFY;
...
}
In design mode, I can assign an expression to my custom property, but it get evaluated in design mode and not in runtime
Here is my expression (a file name based on a date contained in a user variable):
"DB" + (DT_WSTR, 4)YEAR( @[User::varCurrentDate] ) + RIGHT( "0" + (DT_WSTR, 2)MONTH( @[User::varCurrentDate] ), 2 ) + "\" + (DT_WSTR, 4)YEAR( @[User::varCurrentDate] ) + RIGHT( "0" + (DT_WSTR, 2)MONTH( @[User::varCurrentDate] ), 2 ) + ".VER"
@[User::varCurrentDate] is a DateTime variable and is assign to 0 at design time
So the expression is evaluated as: "DB189912189912.VER".
My package contains 2 data flow.
At runtime,
The first one is responsible to set a valid date in @[User::varCurrentDate] variable. (the date is 2007-01-15)
The second one contains my custom data flow component with my custom property that was set to an expression at design time
When my component get executed, my custom property value is still "DB189912189912.VER" and I expected "DB200701200701.VER"
Any idea ?
is it possible to make a custom query to fetch and update data from sql server 2005 in SharePoint designer
i make a new data source library and use a custom query to get data but don€™t know how to configure update custom command
can any buddy help me out
What I want to accomplish is that at design time the designer can enter a value for some custom property on my custom task and that this value is accessed at executing time.
View 10 Replies View Related