I'm the new beginer in C#,I have some problem in doing crystal report:
In my application I have 2 form named Form1 and Form2.
I create one dataset named ds in Form1 and in Form2 I want to creat a crystal report that displays the data in the dataset ds in Form1.
I relly get stuck.Can anyone help me?
Thanx b4 hand!See if you find answer here
www.asp.net
http://support.businessobjects.com/sql
Showing posts with label create. Show all posts
Showing posts with label create. Show all posts
Friday, March 30, 2012
Help in creating xsd file
Hello everybody,
I have following XML and i do want to create xsd file to BulkLoad data
into SQL SERVER table, so pls help me how can i create xsd file out of
following XML file...
<PTLV_PROJ>
<PROJECT>
<PID>10649280</PID>
<PDESC>FS2 - TAX MEETINGS GENERAL</PDESC>
<ACTIVITIES>
<ACT>
<AID>0000</AID>
<ADESC>General</ADESC>
</ACT>
<ACT>
<AID>10000</AID>
<ADESC>General test</ADESC>
</ACT>
</ACTIVITIES>
</PROJECT>
<PROJECT>
<PID>10649489</PID>
<PDESC>AREA KNOWLEDGE NETWORK</PDESC>
<ACTIVITIES>
<ACT>
<AID>20000</AID>
<ADESC>General</ADESC>
</ACT>
<ACT>
<AID>3001</AID>
<ADESC>AWS CHAMPION</ADESC>
</ACT>
</ACTIVITIES>
</PROJECT>
</PTLV_PROJ>Hi
You post may be better answered in microsoft.public.sqlserver.xml
Check out the following on how to use updategrams
http://msdn.microsoft.com/library/d...
egram_375f.asp
http://msdn.microsoft.com/library/d...asp?frame=true
http://msdn.microsoft.com/library/d...ations_0gqb.asp
John
"mvp" wrote:
> Hello everybody,
> I have following XML and i do want to create xsd file to BulkLoad data
> into SQL SERVER table, so pls help me how can i create xsd file out of
> following XML file...
>
> <PTLV_PROJ>
> <PROJECT>
> <PID>10649280</PID>
> <PDESC>FS2 - TAX MEETINGS GENERAL</PDESC>
> <ACTIVITIES>
> <ACT>
> <AID>0000</AID>
> <ADESC>General</ADESC>
> </ACT>
> <ACT>
> <AID>10000</AID>
> <ADESC>General test</ADESC>
> </ACT>
> </ACTIVITIES>
> </PROJECT>
> <PROJECT>
> <PID>10649489</PID>
> <PDESC>AREA KNOWLEDGE NETWORK</PDESC>
> <ACTIVITIES>
> <ACT>
> <AID>20000</AID>
> <ADESC>General</ADESC>
> </ACT>
> <ACT>
> <AID>3001</AID>
> <ADESC>AWS CHAMPION</ADESC>
> </ACT>
> </ACTIVITIES>
> </PROJECT>
> </PTLV_PROJ>
I have following XML and i do want to create xsd file to BulkLoad data
into SQL SERVER table, so pls help me how can i create xsd file out of
following XML file...
<PTLV_PROJ>
<PROJECT>
<PID>10649280</PID>
<PDESC>FS2 - TAX MEETINGS GENERAL</PDESC>
<ACTIVITIES>
<ACT>
<AID>0000</AID>
<ADESC>General</ADESC>
</ACT>
<ACT>
<AID>10000</AID>
<ADESC>General test</ADESC>
</ACT>
</ACTIVITIES>
</PROJECT>
<PROJECT>
<PID>10649489</PID>
<PDESC>AREA KNOWLEDGE NETWORK</PDESC>
<ACTIVITIES>
<ACT>
<AID>20000</AID>
<ADESC>General</ADESC>
</ACT>
<ACT>
<AID>3001</AID>
<ADESC>AWS CHAMPION</ADESC>
</ACT>
</ACTIVITIES>
</PROJECT>
</PTLV_PROJ>Hi
You post may be better answered in microsoft.public.sqlserver.xml
Check out the following on how to use updategrams
http://msdn.microsoft.com/library/d...
egram_375f.asp
http://msdn.microsoft.com/library/d...asp?frame=true
http://msdn.microsoft.com/library/d...ations_0gqb.asp
John
"mvp" wrote:
> Hello everybody,
> I have following XML and i do want to create xsd file to BulkLoad data
> into SQL SERVER table, so pls help me how can i create xsd file out of
> following XML file...
>
> <PTLV_PROJ>
> <PROJECT>
> <PID>10649280</PID>
> <PDESC>FS2 - TAX MEETINGS GENERAL</PDESC>
> <ACTIVITIES>
> <ACT>
> <AID>0000</AID>
> <ADESC>General</ADESC>
> </ACT>
> <ACT>
> <AID>10000</AID>
> <ADESC>General test</ADESC>
> </ACT>
> </ACTIVITIES>
> </PROJECT>
> <PROJECT>
> <PID>10649489</PID>
> <PDESC>AREA KNOWLEDGE NETWORK</PDESC>
> <ACTIVITIES>
> <ACT>
> <AID>20000</AID>
> <ADESC>General</ADESC>
> </ACT>
> <ACT>
> <AID>3001</AID>
> <ADESC>AWS CHAMPION</ADESC>
> </ACT>
> </ACTIVITIES>
> </PROJECT>
> </PTLV_PROJ>
Help in creating Dynamic Rows!
Hi
I am trying to create a dynamic row. Say for example, I want to list all the "Employees" belonging to a particular department say "Physics". So, I would want every employee name to be in a separate row. How do I go about doing this? Any help/suggestions is appreciated.
Thanks
Using a matrix, this is possible if you add a group based on Department, a group based on Emeployee name, and data values in the data section of the matrix. Here is an example of the output:
Phisics
Joe Smith
123
Mike Jones
222
Kelly Roberts
111
Chemistry
Sarah Paulson
145
Kinestetics
Robert Paulson
321
Rob Sam
235
You can also do this with a table. Add a group based on Department and add the the employee fields to the details row. The output would look something like this,
Physics
Joe Smith
123
Mike Jones
222
Kelly Roberts
111
Chemistry
Sarah Paulson
145
Kinestetics
Robert Paulson
321
Jimmy James
235
Ian
<?xml version="1.0" encoding="utf-8"?>
<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<DataSources>
<DataSource Name="DataSource1">
<ConnectionProperties>
<IntegratedSecurity>true</IntegratedSecurity>
<ConnectString>Data Source=localhost; Initial Catalog="AdventureWorks"</ConnectString>
<DataProvider>SQL</DataProvider>
</ConnectionProperties>
<rd:DataSourceID>dafaddb3-d659-48f2-a1bc-6f040759edf9</rd:DataSourceID>
</DataSource>
</DataSources>
<BottomMargin>1in</BottomMargin>
<RightMargin>1in</RightMargin>
<rd:DrawGrid>true</rd:DrawGrid>
<InteractiveWidth>8.5in</InteractiveWidth>
<rd:SnapToGrid>true</rd:SnapToGrid>
<Body>
<ReportItems>
<Table Name="table1">
<Footer>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox11">
<rd:DefaultName>textbox11</rd:DefaultName>
<ZIndex>5</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox12">
<rd:DefaultName>textbox12</rd:DefaultName>
<ZIndex>4</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.25in</Height>
</TableRow>
</TableRows>
</Footer>
<DataSetName>DataSet2</DataSetName>
<Top>2.25in</Top>
<TableGroups>
<TableGroup>
<Footer>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox15">
<rd:DefaultName>textbox15</rd:DefaultName>
<ZIndex>3</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox16">
<rd:DefaultName>textbox16</rd:DefaultName>
<ZIndex>2</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.25in</Height>
</TableRow>
</TableRows>
</Footer>
<Header>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="Department_1">
<rd:DefaultName>Department_1</rd:DefaultName>
<ZIndex>7</ZIndex>
<Style>
<BorderStyle>
<Bottom>Solid</Bottom>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<BorderColor>
<Bottom>Red</Bottom>
</BorderColor>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!Department.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox4">
<rd:DefaultName>textbox4</rd:DefaultName>
<ZIndex>6</ZIndex>
<Style>
<BorderStyle>
<Bottom>Solid</Bottom>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<BorderColor>
<Bottom>Red</Bottom>
</BorderColor>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.25in</Height>
</TableRow>
</TableRows>
</Header>
<Grouping Name="table1_Group1">
<GroupExpressions>
<GroupExpression>=Fields!Department.Value</GroupExpression>
</GroupExpressions>
</Grouping>
</TableGroup>
</TableGroups>
<ZIndex>1</ZIndex>
<Width>5.16667in</Width>
<Details>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="EmployeeName_1">
<rd:DefaultName>EmployeeName_1</rd:DefaultName>
<ZIndex>1</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!EmployeeName.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="DataValue_1">
<rd:DefaultName>DataValue_1</rd:DefaultName>
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!DataValue.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.25in</Height>
</TableRow>
</TableRows>
</Details>
<Header>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox5">
<rd:DefaultName>textbox5</rd:DefaultName>
<ZIndex>9</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox6">
<rd:DefaultName>textbox6</rd:DefaultName>
<ZIndex>8</ZIndex>
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.25in</Height>
</TableRow>
</TableRows>
</Header>
<TableColumns>
<TableColumn>
<Width>2.58333in</Width>
</TableColumn>
<TableColumn>
<Width>2.58333in</Width>
</TableColumn>
</TableColumns>
<Height>1.25in</Height>
</Table>
<Matrix Name="matrix1">
<MatrixColumns>
<MatrixColumn>
<Width>1in</Width>
</MatrixColumn>
</MatrixColumns>
<RowGroupings>
<RowGrouping>
<Width>1.375in</Width>
<DynamicRows>
<ReportItems>
<Textbox Name="Department">
<rd:DefaultName>Department</rd:DefaultName>
<ZIndex>2</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<BorderColor>
<Default>Red</Default>
</BorderColor>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!Department.Value</Value>
</Textbox>
</ReportItems>
<Grouping Name="matrix1_Department">
<GroupExpressions>
<GroupExpression>=Fields!Department.Value</GroupExpression>
</GroupExpressions>
</Grouping>
</DynamicRows>
</RowGrouping>
<RowGrouping>
<Width>1.375in</Width>
<DynamicRows>
<ReportItems>
<Textbox Name="EmployeeName">
<rd:DefaultName>EmployeeName</rd:DefaultName>
<ZIndex>1</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!EmployeeName.Value</Value>
</Textbox>
</ReportItems>
<Grouping Name="matrix1_EmployeeName">
<GroupExpressions>
<GroupExpression>=Fields!EmployeeName.Value</GroupExpression>
</GroupExpressions>
</Grouping>
</DynamicRows>
</RowGrouping>
</RowGroupings>
<ColumnGroupings>
<ColumnGrouping>
<DynamicColumns>
<ReportItems>
<Textbox Name="textbox2">
<rd:DefaultName>textbox2</rd:DefaultName>
<ZIndex>3</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
<Grouping Name="matrix1_ColumnGroup1">
<GroupExpressions>
<GroupExpression />
</GroupExpressions>
</Grouping>
</DynamicColumns>
<Height>0.25in</Height>
</ColumnGrouping>
</ColumnGroupings>
<DataSetName>DataSet2</DataSetName>
<Top>1in</Top>
<Width>3.75in</Width>
<Corner>
<ReportItems>
<Textbox Name="textbox1">
<rd:DefaultName>textbox1</rd:DefaultName>
<ZIndex>4</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</Corner>
<Height>0.5in</Height>
<MatrixRows>
<MatrixRow>
<Height>0.25in</Height>
<MatrixCells>
<MatrixCell>
<ReportItems>
<Textbox Name="DataValue">
<rd:DefaultName>DataValue</rd:DefaultName>
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Sum(Fields!DataValue.Value)</Value>
</Textbox>
</ReportItems>
</MatrixCell>
</MatrixCells>
</MatrixRow>
</MatrixRows>
</Matrix>
</ReportItems>
<Height>6.125in</Height>
</Body>
<rd:ReportID>819bacc0-4ffc-4d72-90d6-8d01b9a574e8</rd:ReportID>
<LeftMargin>1in</LeftMargin>
<DataSets>
<DataSet Name="DataSet2">
<Query>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
<CommandText>Select 'Physics' As Department, 'Joe Smith' As EmployeeName, 123 As DataValue
Union All
Select 'Physics', 'Mike Jones', 222
Union All
Select 'Physics', 'Kelly Roberts', 111
Union All
Select 'Chemistry', 'Sarah Paulson', 145
Union All
Select 'Kinestetics', 'Robert Paulson', 321
Union All
Select 'Kinestetics', 'Jimmy James', 235</CommandText>
<DataSourceName>DataSource1</DataSourceName>
</Query>
<Fields>
<Field Name="Department">
<rd:TypeName>System.String</rd:TypeName>
<DataField>Department</DataField>
</Field>
<Field Name="EmployeeName">
<rd:TypeName>System.String</rd:TypeName>
<DataField>EmployeeName</DataField>
</Field>
<Field Name="DataValue">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>DataValue</DataField>
</Field>
</Fields>
</DataSet>
</DataSets>
<CodeModules>
<CodeModule>System.Xml, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089</CodeModule>
</CodeModules>
<Code />
<Width>7.75in</Width>
<InteractiveHeight>11in</InteractiveHeight>
<Language>en-US</Language>
<TopMargin>1in</TopMargin>
</Report>|||
Ian
I'm getting the following error message: when I add a group by row on my matrix1.
[rsAggregateInGroupExpression] A group expression for the matrix 'matrix1' includes an aggregate function. Aggregate functions cannot be used in group expressions.
Build complete -- 1 errors, 0 warnings
The query is pretty tricky. In my report, I have a report parameter that takes in an employee id and does a select on the Department ID. After getting the Department ID, I select all the other employees corresponding to it. All the data is in one table. So, the SQL query would be like this
SELECT emp_id FROM emp_dept WHERE dept_id IN
(SELECT dept_id FROM emp_dept WHERE emp_id=@.emp_id_param)
In this report I'm trying to display all the emp_id's.
Your suggestions are greatly appreciated.
Thanks
preps.
|||
Ian
In addition, to the information in my previous mail. I'm building this report in Visual Studio2005 and not in Report Builder.
preps.
|||The error you are getting is caused by using an aggregate on a field in the group expression. Aggregates are not allowed in this context, and in your case does not seem to be needed.If all you are displaying is the emp_id field values, then you don't really need to use a matrix. A table should give you what you need. If there is more information to display, including another dimension of data, and aggregations that need to be performed based on the dimensionality of the data, then a matrix would be the report item to use.
That being said, if you continue to use the matrix, make sure that the query includes the dept_id field in the field list, along with the emp_id. This value is needed for the first group.
The first group should have the group expression "=Fields!dept_id.Value" to first group the data by department. The second group should have the expression "=Fields!emp_id.Value" to group the data by employee. Based on the example query, this should give you the matrix described in the previous post.|||
Hi Ian
Thanks for you reply. As per your suggestion I used a table data region (in Report Designer). But for some reason only one emp_id is being display. Instead of three emp_ids which is the actual resultset.
I don't know why the other two emp_id's are not being displayed. Do you have any idea?
|||Which row in the table are you placing the emp_id field in? The one with the three horzontal lines in the box to the left of the row? Are you adding any groups to the table? If so, what fields are you grouping on?
Ian|||
Which row in the table are you placing the emp_id field in? The one with the three horzontal lines in the box to the left of the row? - Yes
Are you adding any groups to the table? - No
preps
|||On the data screen, you see three rows of data, but only one is being displayed in the table? Can you post the rdl, so that I can take a closer look? Make sure to remove any sensitive data from it before posting.
Ian|||
Ian
I figured out the problem. The table that I was trying to create was inside a List report item and hence it was not displaying the emp_ids. I put the table outside the List. It gave my the result I was looking for. If you know why or what caused this behaviour that would give me a better understanding. Otherwise, don't bother about it.
Thanks for all the help and quick response that drove me towards the right solution.
Cheers
preps
Help in creating dynamic rows!
Hi
I am trying to create a dynamic row. Say for example, I want to list all the "Employees" belonging to a particular department say "Physics". So, I would want every employee name to be in a separate row. How do I go about doing this? Any help/suggestions is appreciated.
Thanks
Take a look at the Matrix report item.
With a matrix you can define a group of say 'department' and then show in the details the 'Employees'
Take a look at http://msdn2.microsoft.com/en-us/library/ms157334.aspx
Help I have gone brain dead
I have create a table called production. Where production information is entered on each shift for each day (3 shifts). When I first created the table, there was no need to track equipment without production. Now it is!! What I need to do is be able to query the table by line, shift and date for a date range and determine which lines, shifts and date are missing production data. The machines are numbered from 2 to 12 and the column name is LineNum. I have columns named Production, Shift, and EntryDate. I know that I am probably going to feel stupid when I see the fix, but for the life of me, I just don't see how to do it without recordset and then processing it in vbscript.
Thanks in advance,
LeeLee, I'm not sure if I eally understand the problem, but it sounds like the following
SELECT LineNum, Shift, EntryDate
FROM production
WHERE Production IS NULL
AND EntryDate BETWEEN @.begindate AND @.enddate
@.begindate and @.enddate represent the date range you're searching for. This seems awfully simple, though, perhaps I'm misunderstanding the problem?|||If you can, post here the tables structure in order for us to have a good understanding of your situation.
ionut|||I keep playing until I found something that give me an "okay" way of getting the information. I created a table called NumLine with all possible lines in it then joined the two tables. This method returns me data (count) as long as the line has ran at least one shift. The problem is if the line does not run at least one shift, I will not know it. My manager said that he would deal with this short term.
SELECT DISTINCT Prod.ProDate, Prod.Line, Max(Prod.Shift) AS MaxOfShift, Count(Prod.Shift) AS CountOfShift, Prod.ProDate, Prod.Dept
FROM Prod RIGHT JOIN tblNumLine ON Prod.Line = tblNumLine.NumLine
GROUP BY Prod.ProDate, Prod.Line, Prod.ProDate, Prod.Dept, tblNumLine.NumLine
HAVING (((Prod.ProDate) Between 'MMColParamBeg' And 'MMColParamEnd') AND ((Count(Prod.Shift))<3) ))
tblNumLine
NumLine
tblProd
LineNum
Shift
Production
EntryDate
Thanks,
Lee|||Yes! When I thought over what I had accomplished by creating a new table and the structure of the query, end result NOTHING! I could have accomplished the same with a simple Select and Count(Shift) Where Count(Shift) < 3 Group by Shift. What I need is to be able to return missing production even if it is all 3 shifts.
Thanks to all for your patience with an knucklehead,
Lee
Thanks in advance,
LeeLee, I'm not sure if I eally understand the problem, but it sounds like the following
SELECT LineNum, Shift, EntryDate
FROM production
WHERE Production IS NULL
AND EntryDate BETWEEN @.begindate AND @.enddate
@.begindate and @.enddate represent the date range you're searching for. This seems awfully simple, though, perhaps I'm misunderstanding the problem?|||If you can, post here the tables structure in order for us to have a good understanding of your situation.
ionut|||I keep playing until I found something that give me an "okay" way of getting the information. I created a table called NumLine with all possible lines in it then joined the two tables. This method returns me data (count) as long as the line has ran at least one shift. The problem is if the line does not run at least one shift, I will not know it. My manager said that he would deal with this short term.
SELECT DISTINCT Prod.ProDate, Prod.Line, Max(Prod.Shift) AS MaxOfShift, Count(Prod.Shift) AS CountOfShift, Prod.ProDate, Prod.Dept
FROM Prod RIGHT JOIN tblNumLine ON Prod.Line = tblNumLine.NumLine
GROUP BY Prod.ProDate, Prod.Line, Prod.ProDate, Prod.Dept, tblNumLine.NumLine
HAVING (((Prod.ProDate) Between 'MMColParamBeg' And 'MMColParamEnd') AND ((Count(Prod.Shift))<3) ))
tblNumLine
NumLine
tblProd
LineNum
Shift
Production
EntryDate
Thanks,
Lee|||Yes! When I thought over what I had accomplished by creating a new table and the structure of the query, end result NOTHING! I could have accomplished the same with a simple Select and Count(Shift) Where Count(Shift) < 3 Group by Shift. What I need is to be able to return missing production even if it is all 3 shifts.
Thanks to all for your patience with an knucklehead,
Lee
help how to create a row progressive..
Hi to everybody, I'd like to know how to extract a progressive number in a
select.
Example..
select.
Example..
Select * from employers
Name Surname Position Age Hire Date Progressive
Mario Rossi Consultant 25 26/07/2003 1 (this
field doeas not exists in table)
fredrick Bauman Secretary 30 15/06/1999 2
Liana Orfei Director 45 15/01/1970 3
...................................... .....................
...........n
Thanks to every bodyHere's an example from the Pubs database:
SELECT
(SELECT COUNT(*)
FROM Authors
WHERE au_id <= A.au_id)
,*
FROM Authors AS A
--
David Portas
SQL Server MVP
--|||If you can afford to use an intermediate temp table:
select IDENTITY(int,1,1) as ProgressiveNumber into #Employers
select * from #Employers
MSSQL requires a table (even a temp table) on which to hang the identity
state info.
" Pecos Bill" <pecos.bill@.yahoo.it> wrote in message
news:6KlCc.15581$c_1.447390@.twister1.libero.it...
> Hi to everybody, I'd like to know how to extract a progressive number in a
> select.
> Example..
> Select * from employers
> Name Surname Position Age Hire Date Progressive
> Mario Rossi Consultant 25 26/07/2003 1 (this
> field doeas not exists in table)
> fredrick Bauman Secretary 30 15/06/1999 2
> Liana Orfei Director 45 15/01/1970 3|||> SELECT
> (SELECT COUNT(*)
> FROM Authors
> WHERE au_id <= A.au_id)
> ,*
> FROM Authors AS A
thanks...I'll try soon.|||"Mischa Sandberg" <mischa_sandberg@.telus.net> ha scritto nel messaggio
news:72pCc.6022$E84.827@.edtnps89...
> If you can afford to use an intermediate temp table:
> select IDENTITY(int,1,1) as ProgressiveNumber into #Employers
> select * from #Employers
> MSSQL requires a table (even a temp table) on which to hang the identity
> state info.
thanks...I'll try soon.
alessandro|||>> I'd like to know how to extract a progressive number in a select.
<<
Let's get back to the basics of an RDBMS. Rows are not records; fields
are not columns; tables are not files; there is no sequential access
or ordering in an RDBMS, so "first", "next" and "last" are totally
meaningless. In SQL and tiered architectures, things that deal with a
PHYSICAL ordering are done in the front end, never in the database.
Wednesday, March 28, 2012
Help for printing reports programatically
Hi.....
I have developed a application which create the report and save it in
user selected location.
All this i done in one class. this application shows all the reports on
report server and render the report for user selected parameters. and
save it with folder browser option on a location. file name is report
name.
Now i have to write another class which will take this saved file name
as input and print that.
i found only printing method for a particular report but i dont want to
again render that report in print class. i have sen the available
articles on net which takes the report name and render it and print
that.
how i can access that saved file in another class and if i have to
again render that report then how i can pass those parameters value
again in render method in print class.
But for my application i have already render that report, so any one
please tell me how i can do that.
Thanks & Regards
DineshDinesh,
This is just a suggestion - what if you saved the report as a pdf file ?
Displaying a pdf file from a location on the hard disk doesn't need the
report engine anymore.
"Dinesh" <dinesht15@.gmail.com> wrote in message
news:1164717797.911571.169550@.j72g2000cwa.googlegroups.com...
> Hi.....
> I have developed a application which create the report and save it in
> user selected location.
> All this i done in one class. this application shows all the reports on
> report server and render the report for user selected parameters. and
> save it with folder browser option on a location. file name is report
> name.
>
> Now i have to write another class which will take this saved file name
> as input and print that.
> i found only printing method for a particular report but i dont want to
> again render that report in print class. i have sen the available
> articles on net which takes the report name and render it and print
> that.
>
> how i can access that saved file in another class and if i have to
> again render that report then how i can pass those parameters value
> again in render method in print class.
>
> But for my application i have already render that report, so any one
> please tell me how i can do that.
>
> Thanks & Regards
> Dinesh
>|||hi Andrei.......
Thanks for your reply.....
Ya you are right......It will not require report engine
again........
i want to know only that how i can print that saved file which is saved
in hard disk.
My problem is that i wrote print function in a seprate class. at that
place i am not able to access that file name with full path.......how
i can do this.......
if you know this please tell me..........
Thanks & Regards
Dinesh
Andrei wrote:
> Dinesh,
> This is just a suggestion - what if you saved the report as a pdf file ?
> Displaying a pdf file from a location on the hard disk doesn't need the
> report engine anymore.
> "Dinesh" <dinesht15@.gmail.com> wrote in message
> news:1164717797.911571.169550@.j72g2000cwa.googlegroups.com...
> > Hi.....
> >
> > I have developed a application which create the report and save it in
> > user selected location.
> >
> > All this i done in one class. this application shows all the reports on
> > report server and render the report for user selected parameters. and
> > save it with folder browser option on a location. file name is report
> > name.
> >
> >
> >
> > Now i have to write another class which will take this saved file name
> > as input and print that.
> >
> > i found only printing method for a particular report but i dont want to
> > again render that report in print class. i have sen the available
> > articles on net which takes the report name and render it and print
> > that.
> >
> >
> >
> > how i can access that saved file in another class and if i have to
> > again render that report then how i can pass those parameters value
> > again in render method in print class.
> >
> >
> >
> > But for my application i have already render that report, so any one
> > please tell me how i can do that.
> >
> >
> >
> > Thanks & Regards
> >
> > Dinesh
> >
I have developed a application which create the report and save it in
user selected location.
All this i done in one class. this application shows all the reports on
report server and render the report for user selected parameters. and
save it with folder browser option on a location. file name is report
name.
Now i have to write another class which will take this saved file name
as input and print that.
i found only printing method for a particular report but i dont want to
again render that report in print class. i have sen the available
articles on net which takes the report name and render it and print
that.
how i can access that saved file in another class and if i have to
again render that report then how i can pass those parameters value
again in render method in print class.
But for my application i have already render that report, so any one
please tell me how i can do that.
Thanks & Regards
DineshDinesh,
This is just a suggestion - what if you saved the report as a pdf file ?
Displaying a pdf file from a location on the hard disk doesn't need the
report engine anymore.
"Dinesh" <dinesht15@.gmail.com> wrote in message
news:1164717797.911571.169550@.j72g2000cwa.googlegroups.com...
> Hi.....
> I have developed a application which create the report and save it in
> user selected location.
> All this i done in one class. this application shows all the reports on
> report server and render the report for user selected parameters. and
> save it with folder browser option on a location. file name is report
> name.
>
> Now i have to write another class which will take this saved file name
> as input and print that.
> i found only printing method for a particular report but i dont want to
> again render that report in print class. i have sen the available
> articles on net which takes the report name and render it and print
> that.
>
> how i can access that saved file in another class and if i have to
> again render that report then how i can pass those parameters value
> again in render method in print class.
>
> But for my application i have already render that report, so any one
> please tell me how i can do that.
>
> Thanks & Regards
> Dinesh
>|||hi Andrei.......
Thanks for your reply.....
Ya you are right......It will not require report engine
again........
i want to know only that how i can print that saved file which is saved
in hard disk.
My problem is that i wrote print function in a seprate class. at that
place i am not able to access that file name with full path.......how
i can do this.......
if you know this please tell me..........
Thanks & Regards
Dinesh
Andrei wrote:
> Dinesh,
> This is just a suggestion - what if you saved the report as a pdf file ?
> Displaying a pdf file from a location on the hard disk doesn't need the
> report engine anymore.
> "Dinesh" <dinesht15@.gmail.com> wrote in message
> news:1164717797.911571.169550@.j72g2000cwa.googlegroups.com...
> > Hi.....
> >
> > I have developed a application which create the report and save it in
> > user selected location.
> >
> > All this i done in one class. this application shows all the reports on
> > report server and render the report for user selected parameters. and
> > save it with folder browser option on a location. file name is report
> > name.
> >
> >
> >
> > Now i have to write another class which will take this saved file name
> > as input and print that.
> >
> > i found only printing method for a particular report but i dont want to
> > again render that report in print class. i have sen the available
> > articles on net which takes the report name and render it and print
> > that.
> >
> >
> >
> > how i can access that saved file in another class and if i have to
> > again render that report then how i can pass those parameters value
> > again in render method in print class.
> >
> >
> >
> > But for my application i have already render that report, so any one
> > please tell me how i can do that.
> >
> >
> >
> > Thanks & Regards
> >
> > Dinesh
> >
Help for Newbie: How to create an autonumber
Hi guys/gals. An intro, I've just pick up some database basic skills and currently doing a simple project.
I really need advise on how do I create a field that can generate autonumbering for eg. scenario
There are 3 types of service_id like CTXXXX, GPXXXX, and STXXXX (where XXXX are running numbers). What I need is to have the XXXX auto generate and running. Also if I were to delete an old record for example CT0033, the latest number eg. CT1111 will not be changed to cover up for the missing CT0033.
I apologize if the description is a bit improper, but I'm new to this. Really appreciate anyone that can help. Thanks!! :oI'd suggest the IDENTITY property when you create the table. You'll have to "decorate" the resulting number with the letters you need, but that's trivial.
-PatP|||my money's on access
and what do you mean by basic skills?
can you spell dba?
Is this homework?|||Sarcasm, Brett? How unlike you!
NOT!
:)
If all three of these services are stored in the same table, then you will run into difficulties with IDENTITY unless you don't mind each service being numbered consecutively. IDENTITY will autonumber for the entire table, but not for individual services within the table.
Your best bet is to store the last ID number used for each service in a separate table, and then use a trigger on your data table that looks up the last code used, increments it by 1, and creates the new services codes.
But this is not simple SQL.
An alternative would be to denormalize your data and store the services in separate tables each with their own IDENTITY property, but this is frowned upon for many good reasons.
Actually, your best course of action would be to drop the idea of creating these codes in the first place, since such pseudo-surrogate keys violate several principles of database application design. That is why they are difficult to code.
I really need advise on how do I create a field that can generate autonumbering for eg. scenario
There are 3 types of service_id like CTXXXX, GPXXXX, and STXXXX (where XXXX are running numbers). What I need is to have the XXXX auto generate and running. Also if I were to delete an old record for example CT0033, the latest number eg. CT1111 will not be changed to cover up for the missing CT0033.
I apologize if the description is a bit improper, but I'm new to this. Really appreciate anyone that can help. Thanks!! :oI'd suggest the IDENTITY property when you create the table. You'll have to "decorate" the resulting number with the letters you need, but that's trivial.
-PatP|||my money's on access
and what do you mean by basic skills?
can you spell dba?
Is this homework?|||Sarcasm, Brett? How unlike you!
NOT!
:)
If all three of these services are stored in the same table, then you will run into difficulties with IDENTITY unless you don't mind each service being numbered consecutively. IDENTITY will autonumber for the entire table, but not for individual services within the table.
Your best bet is to store the last ID number used for each service in a separate table, and then use a trigger on your data table that looks up the last code used, increments it by 1, and creates the new services codes.
But this is not simple SQL.
An alternative would be to denormalize your data and store the services in separate tables each with their own IDENTITY property, but this is frowned upon for many good reasons.
Actually, your best course of action would be to drop the idea of creating these codes in the first place, since such pseudo-surrogate keys violate several principles of database application design. That is why they are difficult to code.
Help for INTRO function
Hello.
I need to create a new table from a query result, then I've written this SQL
command:
"SELECT Name, Number FROM Friends INTRO Result"
BUT
SQL give me an error!!
Please Help Me
Bye
CrisI assume you mean INTO not INTRO
SELECT Name, Number INTO result FROM Friends
http://sqlservercode.blogspot.com/|||Cristian wrote:
> Hello.
> I need to create a new table from a query result, then I've written this S
QL
> command:
> "SELECT Name, Number FROM Friends INTRO Result"
> BUT
> SQL give me an error!!
> Please Help Me
> Bye
> Cris
It's INTO not INTRO.
However, the resulting table won't have any keys or constraints.
Although SELECT INTO is handy as a quick and dirty way to create
tables, it may be better to do something like:
CREATE TABLE Result (name VARCHAR(50) NOT NULL, number INTEGER NOT
NULL, PRIMARY KEY ...)
INSERT INTO Result (name, number)
SELECT name, number
FROM Friends ;
David Portas
SQL Server MVP
--|||Also,
If you need an autonumber in the destination table and need the data ordered
you must create the table first.
Good code:-
Create table myTable ( id int identity(1,1) not null, name varchar(50) not
null )
INSERT INTO myTable ( name ) SELECT name FROM friends ORDER BY name
if you use "select identity(int,1,1) AS id , name into MyTable from ..."
the order is not guaranteed and so it is advised against.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1134382059.521678.242420@.g44g2000cwa.googlegroups.com...
> Cristian wrote:
SQL
> It's INTO not INTRO.
> However, the resulting table won't have any keys or constraints.
> Although SELECT INTO is handy as a quick and dirty way to create
> tables, it may be better to do something like:
> CREATE TABLE Result (name VARCHAR(50) NOT NULL, number INTEGER NOT
> NULL, PRIMARY KEY ...)
> INSERT INTO Result (name, number)
> SELECT name, number
> FROM Friends ;
> --
> David Portas
> SQL Server MVP
> --
>
I need to create a new table from a query result, then I've written this SQL
command:
"SELECT Name, Number FROM Friends INTRO Result"
BUT
SQL give me an error!!
Please Help Me
Bye
CrisI assume you mean INTO not INTRO
SELECT Name, Number INTO result FROM Friends
http://sqlservercode.blogspot.com/|||Cristian wrote:
> Hello.
> I need to create a new table from a query result, then I've written this S
QL
> command:
> "SELECT Name, Number FROM Friends INTRO Result"
> BUT
> SQL give me an error!!
> Please Help Me
> Bye
> Cris
It's INTO not INTRO.
However, the resulting table won't have any keys or constraints.
Although SELECT INTO is handy as a quick and dirty way to create
tables, it may be better to do something like:
CREATE TABLE Result (name VARCHAR(50) NOT NULL, number INTEGER NOT
NULL, PRIMARY KEY ...)
INSERT INTO Result (name, number)
SELECT name, number
FROM Friends ;
David Portas
SQL Server MVP
--|||Also,
If you need an autonumber in the destination table and need the data ordered
you must create the table first.
Good code:-
Create table myTable ( id int identity(1,1) not null, name varchar(50) not
null )
INSERT INTO myTable ( name ) SELECT name FROM friends ORDER BY name
if you use "select identity(int,1,1) AS id , name into MyTable from ..."
the order is not guaranteed and so it is advised against.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1134382059.521678.242420@.g44g2000cwa.googlegroups.com...
> Cristian wrote:
SQL
> It's INTO not INTRO.
> However, the resulting table won't have any keys or constraints.
> Although SELECT INTO is handy as a quick and dirty way to create
> tables, it may be better to do something like:
> CREATE TABLE Result (name VARCHAR(50) NOT NULL, number INTEGER NOT
> NULL, PRIMARY KEY ...)
> INSERT INTO Result (name, number)
> SELECT name, number
> FROM Friends ;
> --
> David Portas
> SQL Server MVP
> --
>
Monday, March 26, 2012
Help for database schema
Hello All,
My project uses MS SQL server database and is not too big database (have aound 200 table).
Now I have to create Database schema for my database as my project needs to be integrated with some other product.
I don't know much about database schema and how to start with it.
Can someone please give me some inputs on following:
1) What exactly database schema should include?
2) How should I start creating the schema for my database?
3) Are there any tools for doing this?
Thanks in advance1) CREATE TABLE statements, plus INSERT statements
2) Enterprise Manager will script the CREATE TABLE statements (but not the INSERT statements, i'm afraid)
3) tons of 'em -- do a search for SQL Server admin tools|||if your database is already built and you are looking to reverse engineer it, check out the link in my sig.|||My project uses MS SQL server database and is not too big database (have aound 200 table).I've never seen a database with 200 tables that needed 200 tables. It is indicative of poor design.|||I've never seen a database with 200 tables that needed 200 tables. It is indicative of poor design.That's a bit absolute isn't it?
EDIT - misinterpreted indicative on my first pass.|||Well, he states my project, and a one mans project with 200 tables? Well that clearly indicates poor design.|||Over egging it a bit aren't you? Do you even know what he is modelling?
BTW - I doubt (but do not know) that this has been his project from inception. I suspect it is purchased\ inherited. But as I say I don't know so I won't go so far as to say it is "clearly indicated".|||here's a project i worked on (as the sole data modeller) for an insurance company
i'm happy to report that it came in at just under 200 tables
thank $deity, eh, because i know now that as soon as it goes over the 200 table limit, it's b0rked|||Yeah, I know about data modelling. And I do know that a project with some 200 tables is quite complex, and I do think that more than one person should be involved in the data modelling. In smaller projects, one man can do the whole job, but in projects of the size we are talking about here, I would definitely not be comfortable as the only modeller. With so high complexity i feel pretty safe when I state that the project should involve at least two persons working as a team. One single man increases the chance of some issues being overlooked.
Edit: As I understand the word indicates, it does not mean it has to be that way, just that it's very likely. If you would use a different word for this, please let me know, as English is not my native language :)|||That's a bit absolute isn't it?
I have absolutely never seen a 200 table database that was a good design. I'm merely stating a fact.|||Thanks for all your comments.
Ya i do agree that its a poor design.
Anyways I have created the schema for the DB.
Thanks.|||Ya i do agree that its a poor design.
but how and why do u all of a sudden agree that the design was poor?|||Well, he has the advantage of being able to look at it.sql
My project uses MS SQL server database and is not too big database (have aound 200 table).
Now I have to create Database schema for my database as my project needs to be integrated with some other product.
I don't know much about database schema and how to start with it.
Can someone please give me some inputs on following:
1) What exactly database schema should include?
2) How should I start creating the schema for my database?
3) Are there any tools for doing this?
Thanks in advance1) CREATE TABLE statements, plus INSERT statements
2) Enterprise Manager will script the CREATE TABLE statements (but not the INSERT statements, i'm afraid)
3) tons of 'em -- do a search for SQL Server admin tools|||if your database is already built and you are looking to reverse engineer it, check out the link in my sig.|||My project uses MS SQL server database and is not too big database (have aound 200 table).I've never seen a database with 200 tables that needed 200 tables. It is indicative of poor design.|||I've never seen a database with 200 tables that needed 200 tables. It is indicative of poor design.That's a bit absolute isn't it?
EDIT - misinterpreted indicative on my first pass.|||Well, he states my project, and a one mans project with 200 tables? Well that clearly indicates poor design.|||Over egging it a bit aren't you? Do you even know what he is modelling?
BTW - I doubt (but do not know) that this has been his project from inception. I suspect it is purchased\ inherited. But as I say I don't know so I won't go so far as to say it is "clearly indicated".|||here's a project i worked on (as the sole data modeller) for an insurance company
i'm happy to report that it came in at just under 200 tables
thank $deity, eh, because i know now that as soon as it goes over the 200 table limit, it's b0rked|||Yeah, I know about data modelling. And I do know that a project with some 200 tables is quite complex, and I do think that more than one person should be involved in the data modelling. In smaller projects, one man can do the whole job, but in projects of the size we are talking about here, I would definitely not be comfortable as the only modeller. With so high complexity i feel pretty safe when I state that the project should involve at least two persons working as a team. One single man increases the chance of some issues being overlooked.
Edit: As I understand the word indicates, it does not mean it has to be that way, just that it's very likely. If you would use a different word for this, please let me know, as English is not my native language :)|||That's a bit absolute isn't it?
I have absolutely never seen a 200 table database that was a good design. I'm merely stating a fact.|||Thanks for all your comments.
Ya i do agree that its a poor design.
Anyways I have created the schema for the DB.
Thanks.|||Ya i do agree that its a poor design.
but how and why do u all of a sudden agree that the design was poor?|||Well, he has the advantage of being able to look at it.sql
Friday, March 23, 2012
help creating linked server to Unify ELS on SCO unix
hello,
I'm trying to create a linked server from an SQL2000 to a Unify ELS
(very old, odbc is version 1) database on SCO unix.
The odbc driver is old but it works fine when used by applications for
creating reports.
What I want to do is make a linked server from the SCO box to the win2k
box. So, I'm using enterprise manager to create the linked server.
The DSN of the unify database is CORE_OFFICE1
I'm getting 'Error 7399: OLE DB provider 'MSDASQL' reported an error.
Datasource name not found and no default driver specified.
When I create the linked server I chose the OLE DB for ODBC drivers.
I put 'CORE_OFFICE1' for the Data Source name. I also setup the
security with the name of a unix account for login/password.
What else should I have?
I'm new at this linked server stuff!
Product Name:
Data Source:
Provider String:
Location:
Catalog:
Thanks,
Oskar
Did you install the ODBC driver on the SQL Server box - not
just your PC - and create a system DSN on the SQL Server
box?
-Sue
On Sun, 15 Aug 2004 22:03:00 -0500, pheonix1t
<nothing@.nothing.gone> wrote:
>hello,
>I'm trying to create a linked server from an SQL2000 to a Unify ELS
>(very old, odbc is version 1) database on SCO unix.
>The odbc driver is old but it works fine when used by applications for
>creating reports.
>What I want to do is make a linked server from the SCO box to the win2k
>box. So, I'm using enterprise manager to create the linked server.
>The DSN of the unify database is CORE_OFFICE1
>I'm getting 'Error 7399: OLE DB provider 'MSDASQL' reported an error.
>Datasource name not found and no default driver specified.
>When I create the linked server I chose the OLE DB for ODBC drivers.
>I put 'CORE_OFFICE1' for the Data Source name. I also setup the
>security with the name of a unix account for login/password.
>What else should I have?
>I'm new at this linked server stuff!
>Product Name:
>Data Source:
>Provider String:
>Location:
>Catalog:
>Thanks,
>Oskar
|||Sue Hoegemeier wrote:
> Did you install the ODBC driver on the SQL Server box - not
> just your PC - and create a system DSN on the SQL Server
> box?
Yes, the odbc driver is installed on the sql server box. However, I had
it as a User DSN instead of a System DSN. I changed that and now I get
this error:
Error 7399, authentication failed. Data source rejected connection attempt.
I put the 'sa' user as the local account on the linked server security
tab and I put the unix user/password as the remote account.
I think this is getting closer, but it's still not there!
Thanks,
Oskar
> -Sue
> On Sun, 15 Aug 2004 22:03:00 -0500, pheonix1t
> <nothing@.nothing.gone> wrote:
>
>
|||Yeah...it's closer. Your just stuck on the authentication from SQL to
Unify. You may want to try first by just setting the security context
so that anyone accessing the linked server uses the same login - that
will at least eliminate who you are logging in as for now.
As you are doing this in enterprise manger, on the security tab,
remove the stuff you put in. Then select the last option "Be made
using this security context" and then enter the login and password
needed to access Unify.
-Sue
On Mon, 16 Aug 2004 09:00:08 -0500, pheonix1t <nothing@.nothing.gone>
wrote:
[vbcol=seagreen]
>Sue Hoegemeier wrote:
>Yes, the odbc driver is installed on the sql server box. However, I had
>it as a User DSN instead of a System DSN. I changed that and now I get
>this error:
>Error 7399, authentication failed. Data source rejected connection attempt.
>I put the 'sa' user as the local account on the linked server security
>tab and I put the unix user/password as the remote account.
>I think this is getting closer, but it's still not there!
>Thanks,
>Oskar
|||update
I found out that the unify driver only works in the USER DSN, not the
SYSTEM DSN. So, after a bit of research I've found that it's possible
to do a kluge by using an Access MDB file to connect to the Unify
database via linked table manager. Then, make the linked server using
the Access driver on the SYSTEM DSN.
How does this sound to you? Kinda wild, huh?
Now, I'm in the process of trying to make it work.
Ah, job security!!
Oskar
ps. Any and all advice welcome
I'm trying to create a linked server from an SQL2000 to a Unify ELS
(very old, odbc is version 1) database on SCO unix.
The odbc driver is old but it works fine when used by applications for
creating reports.
What I want to do is make a linked server from the SCO box to the win2k
box. So, I'm using enterprise manager to create the linked server.
The DSN of the unify database is CORE_OFFICE1
I'm getting 'Error 7399: OLE DB provider 'MSDASQL' reported an error.
Datasource name not found and no default driver specified.
When I create the linked server I chose the OLE DB for ODBC drivers.
I put 'CORE_OFFICE1' for the Data Source name. I also setup the
security with the name of a unix account for login/password.
What else should I have?
I'm new at this linked server stuff!
Product Name:
Data Source:
Provider String:
Location:
Catalog:
Thanks,
Oskar
Did you install the ODBC driver on the SQL Server box - not
just your PC - and create a system DSN on the SQL Server
box?
-Sue
On Sun, 15 Aug 2004 22:03:00 -0500, pheonix1t
<nothing@.nothing.gone> wrote:
>hello,
>I'm trying to create a linked server from an SQL2000 to a Unify ELS
>(very old, odbc is version 1) database on SCO unix.
>The odbc driver is old but it works fine when used by applications for
>creating reports.
>What I want to do is make a linked server from the SCO box to the win2k
>box. So, I'm using enterprise manager to create the linked server.
>The DSN of the unify database is CORE_OFFICE1
>I'm getting 'Error 7399: OLE DB provider 'MSDASQL' reported an error.
>Datasource name not found and no default driver specified.
>When I create the linked server I chose the OLE DB for ODBC drivers.
>I put 'CORE_OFFICE1' for the Data Source name. I also setup the
>security with the name of a unix account for login/password.
>What else should I have?
>I'm new at this linked server stuff!
>Product Name:
>Data Source:
>Provider String:
>Location:
>Catalog:
>Thanks,
>Oskar
|||Sue Hoegemeier wrote:
> Did you install the ODBC driver on the SQL Server box - not
> just your PC - and create a system DSN on the SQL Server
> box?
Yes, the odbc driver is installed on the sql server box. However, I had
it as a User DSN instead of a System DSN. I changed that and now I get
this error:
Error 7399, authentication failed. Data source rejected connection attempt.
I put the 'sa' user as the local account on the linked server security
tab and I put the unix user/password as the remote account.
I think this is getting closer, but it's still not there!
Thanks,
Oskar
> -Sue
> On Sun, 15 Aug 2004 22:03:00 -0500, pheonix1t
> <nothing@.nothing.gone> wrote:
>
>
|||Yeah...it's closer. Your just stuck on the authentication from SQL to
Unify. You may want to try first by just setting the security context
so that anyone accessing the linked server uses the same login - that
will at least eliminate who you are logging in as for now.
As you are doing this in enterprise manger, on the security tab,
remove the stuff you put in. Then select the last option "Be made
using this security context" and then enter the login and password
needed to access Unify.
-Sue
On Mon, 16 Aug 2004 09:00:08 -0500, pheonix1t <nothing@.nothing.gone>
wrote:
[vbcol=seagreen]
>Sue Hoegemeier wrote:
>Yes, the odbc driver is installed on the sql server box. However, I had
>it as a User DSN instead of a System DSN. I changed that and now I get
>this error:
>Error 7399, authentication failed. Data source rejected connection attempt.
>I put the 'sa' user as the local account on the linked server security
>tab and I put the unix user/password as the remote account.
>I think this is getting closer, but it's still not there!
>Thanks,
>Oskar
|||update
I found out that the unify driver only works in the USER DSN, not the
SYSTEM DSN. So, after a bit of research I've found that it's possible
to do a kluge by using an Access MDB file to connect to the Unify
database via linked table manager. Then, make the linked server using
the Access driver on the SYSTEM DSN.
How does this sound to you? Kinda wild, huh?
Now, I'm in the process of trying to make it work.
Ah, job security!!
Oskar
ps. Any and all advice welcome
help creating linked server to Unify ELS on SCO unix
hello,
I'm trying to create a linked server from an SQL2000 to a Unify ELS
(very old, odbc is version 1) database on SCO unix.
The odbc driver is old but it works fine when used by applications for
creating reports.
What I want to do is make a linked server from the SCO box to the win2k
box. So, I'm using enterprise manager to create the linked server.
The DSN of the unify database is CORE_OFFICE1
I'm getting 'Error 7399: OLE DB provider 'MSDASQL' reported an error.
Datasource name not found and no default driver specified.
When I create the linked server I chose the OLE DB for ODBC drivers.
I put 'CORE_OFFICE1' for the Data Source name. I also setup the
security with the name of a unix account for login/password.
What else should I have?
I'm new at this linked server stuff!
Product Name:
Data Source:
Provider String:
Location:
Catalog:
Thanks,
OskarDid you install the ODBC driver on the SQL Server box - not
just your PC - and create a system DSN on the SQL Server
box?
-Sue
On Sun, 15 Aug 2004 22:03:00 -0500, pheonix1t
<nothing@.nothing.gone> wrote:
>hello,
>I'm trying to create a linked server from an SQL2000 to a Unify ELS
>(very old, odbc is version 1) database on SCO unix.
>The odbc driver is old but it works fine when used by applications for
>creating reports.
>What I want to do is make a linked server from the SCO box to the win2k
>box. So, I'm using enterprise manager to create the linked server.
>The DSN of the unify database is CORE_OFFICE1
>I'm getting 'Error 7399: OLE DB provider 'MSDASQL' reported an error.
>Datasource name not found and no default driver specified.
>When I create the linked server I chose the OLE DB for ODBC drivers.
>I put 'CORE_OFFICE1' for the Data Source name. I also setup the
>security with the name of a unix account for login/password.
>What else should I have?
>I'm new at this linked server stuff!
>Product Name:
>Data Source:
>Provider String:
>Location:
>Catalog:
>Thanks,
>Oskar|||Sue Hoegemeier wrote:
> Did you install the ODBC driver on the SQL Server box - not
> just your PC - and create a system DSN on the SQL Server
> box?
Yes, the odbc driver is installed on the sql server box. However, I had
it as a User DSN instead of a System DSN. I changed that and now I get
this error:
Error 7399, authentication failed. Data source rejected connection attempt.
I put the 'sa' user as the local account on the linked server security
tab and I put the unix user/password as the remote account.
I think this is getting closer, but it's still not there!
Thanks,
Oskar
> -Sue
> On Sun, 15 Aug 2004 22:03:00 -0500, pheonix1t
> <nothing@.nothing.gone> wrote:
>
>|||Yeah...it's closer. Your just stuck on the authentication from SQL to
Unify. You may want to try first by just setting the security context
so that anyone accessing the linked server uses the same login - that
will at least eliminate who you are logging in as for now.
As you are doing this in enterprise manger, on the security tab,
remove the stuff you put in. Then select the last option "Be made
using this security context" and then enter the login and password
needed to access Unify.
-Sue
On Mon, 16 Aug 2004 09:00:08 -0500, pheonix1t <nothing@.nothing.gone>
wrote:
[vbcol=seagreen]
>Sue Hoegemeier wrote:
>Yes, the odbc driver is installed on the sql server box. However, I had
>it as a User DSN instead of a System DSN. I changed that and now I get
>this error:
>Error 7399, authentication failed. Data source rejected connection attempt
.
>I put the 'sa' user as the local account on the linked server security
>tab and I put the unix user/password as the remote account.
>I think this is getting closer, but it's still not there!
>Thanks,
>Oskar
>|||update
I found out that the unify driver only works in the USER DSN, not the
SYSTEM DSN. So, after a bit of research I've found that it's possible
to do a kluge by using an Access MDB file to connect to the Unify
database via linked table manager. Then, make the linked server using
the Access driver on the SYSTEM DSN.
How does this sound to you? Kinda wild, huh?
Now, I'm in the process of trying to make it work.
Ah, job security!!
Oskar
ps. Any and all advice welcome
I'm trying to create a linked server from an SQL2000 to a Unify ELS
(very old, odbc is version 1) database on SCO unix.
The odbc driver is old but it works fine when used by applications for
creating reports.
What I want to do is make a linked server from the SCO box to the win2k
box. So, I'm using enterprise manager to create the linked server.
The DSN of the unify database is CORE_OFFICE1
I'm getting 'Error 7399: OLE DB provider 'MSDASQL' reported an error.
Datasource name not found and no default driver specified.
When I create the linked server I chose the OLE DB for ODBC drivers.
I put 'CORE_OFFICE1' for the Data Source name. I also setup the
security with the name of a unix account for login/password.
What else should I have?
I'm new at this linked server stuff!
Product Name:
Data Source:
Provider String:
Location:
Catalog:
Thanks,
OskarDid you install the ODBC driver on the SQL Server box - not
just your PC - and create a system DSN on the SQL Server
box?
-Sue
On Sun, 15 Aug 2004 22:03:00 -0500, pheonix1t
<nothing@.nothing.gone> wrote:
>hello,
>I'm trying to create a linked server from an SQL2000 to a Unify ELS
>(very old, odbc is version 1) database on SCO unix.
>The odbc driver is old but it works fine when used by applications for
>creating reports.
>What I want to do is make a linked server from the SCO box to the win2k
>box. So, I'm using enterprise manager to create the linked server.
>The DSN of the unify database is CORE_OFFICE1
>I'm getting 'Error 7399: OLE DB provider 'MSDASQL' reported an error.
>Datasource name not found and no default driver specified.
>When I create the linked server I chose the OLE DB for ODBC drivers.
>I put 'CORE_OFFICE1' for the Data Source name. I also setup the
>security with the name of a unix account for login/password.
>What else should I have?
>I'm new at this linked server stuff!
>Product Name:
>Data Source:
>Provider String:
>Location:
>Catalog:
>Thanks,
>Oskar|||Sue Hoegemeier wrote:
> Did you install the ODBC driver on the SQL Server box - not
> just your PC - and create a system DSN on the SQL Server
> box?
Yes, the odbc driver is installed on the sql server box. However, I had
it as a User DSN instead of a System DSN. I changed that and now I get
this error:
Error 7399, authentication failed. Data source rejected connection attempt.
I put the 'sa' user as the local account on the linked server security
tab and I put the unix user/password as the remote account.
I think this is getting closer, but it's still not there!
Thanks,
Oskar
> -Sue
> On Sun, 15 Aug 2004 22:03:00 -0500, pheonix1t
> <nothing@.nothing.gone> wrote:
>
>|||Yeah...it's closer. Your just stuck on the authentication from SQL to
Unify. You may want to try first by just setting the security context
so that anyone accessing the linked server uses the same login - that
will at least eliminate who you are logging in as for now.
As you are doing this in enterprise manger, on the security tab,
remove the stuff you put in. Then select the last option "Be made
using this security context" and then enter the login and password
needed to access Unify.
-Sue
On Mon, 16 Aug 2004 09:00:08 -0500, pheonix1t <nothing@.nothing.gone>
wrote:
[vbcol=seagreen]
>Sue Hoegemeier wrote:
>Yes, the odbc driver is installed on the sql server box. However, I had
>it as a User DSN instead of a System DSN. I changed that and now I get
>this error:
>Error 7399, authentication failed. Data source rejected connection attempt
.
>I put the 'sa' user as the local account on the linked server security
>tab and I put the unix user/password as the remote account.
>I think this is getting closer, but it's still not there!
>Thanks,
>Oskar
>|||update
I found out that the unify driver only works in the USER DSN, not the
SYSTEM DSN. So, after a bit of research I've found that it's possible
to do a kluge by using an Access MDB file to connect to the Unify
database via linked table manager. Then, make the linked server using
the Access driver on the SYSTEM DSN.
How does this sound to you? Kinda wild, huh?
Now, I'm in the process of trying to make it work.
Ah, job security!!
Oskar
ps. Any and all advice welcome
Help creating Embedded code Function
How can I create a VB.NET function to run my query below that my SSRS report can run to retreive TotalPostingDays so I can show that in a textbox in my report? I need help creating the function in the Report properties of my SSRS report and not sure how to call this stored procedure to return TotalPostingDays.
I need to do this calculation by using a UDF, not stored procedure maybe. The problem is, I do still need to do a lookup to my Holiday table as part of my UDF though; the rest can proabably be done in Straight VB for the weekend and other calculations:
LTER PROCEDURE SSRS_Return_TotalPostingDays
AS
DECLARE @.TotalDaysInMonth int,
@.today datetime,
@.TotalWeekendDays int,
@.TotalHolidaysThisMonth int,
@.TotalPostingDays int
SET @.today = GETDATE()
-- TOTAL DAYS THIS MONTH
SET @.TotalDaysInMonth = CASE WHEN DatePart(mm,GetDate()) IN (1,3,5,7,8,10,12) THEN
31
ELSE
DateDiff(day,GetDate(),DateAdd(mm, 1, GetDate()))
END
-- TOTAL HOLIDAYS THIS MONTH
SELECT @.TotalHolidaysThisMonth = (SELECT COUNT(*) FROM ReportingServer.dbo.Holidays
WHERE HolidayDate BETWEEN (DATEADD(DAY, -DATEPART(DAY, @.today) + 1, @.today))
AND (DATEADD(DAY, -DATEPART(DAY, @.today), DATEADD(MONTH, 1, @.today))))
-- TOTAL # WEEKEND DAYS THIS MONTH
DECLARE @.date DATETIME
SET @.date = '20060101'
SELECT @.TotalWeekendDays = 8 +
CASE WHEN ISDATE(CONVERT(CHAR(6), @.date, 112) + '29') = 1 THEN
CASE WHEN DATENAME(WEEKDAY, CONVERT(CHAR(6), @.date, 112) + '01') IN ('Saturday', 'Sunday')
THEN 1 ELSE 0 END ELSE 0 END +
CASE WHEN ISDATE(CONVERT(CHAR(6), @.date, 112) + '30') = 1 THEN
CASE WHEN DATENAME(WEEKDAY, CONVERT(CHAR(6), @.date, 112) + '02') IN ('Saturday', 'Sunday')
THEN 1 ELSE 0 END ELSE 0 END +
CASE WHEN ISDATE(CONVERT(CHAR(6), @.date, 112) + '31') = 1 THEN
CASE WHEN DATENAME(WEEKDAY, CONVERT(CHAR(6), @.date, 112) + '03') IN ('Saturday', 'Sunday')
THEN 1 ELSE 0 END ELSE 0 END
SET @.TotalPostingDays = @.TotalDaysInMonth - (@.TotalHolidaysThisMonth + @.TotalWeekendDays)
RETURN @.TotalPostingDays
for the holiday lookup, I can probably do something like this then use the variable below to proceed or something:
Dim intTotalPostingDays As Integer
Dim objConn As New SqlConnection("Data Source=server;Initial Catalog=database; integrated security=SSPI;persist security info=False; Trusted_Connection=Yes")
Dim objComm As New SqlCommand("SSRS_Return_TotalHolidaysThi
objComm.CommandType = CommandType.StoredProcedure
Dim returnValueParam As New SqlClient.SqlParameter("@.RETURN_VALUE", SqlDbType.Int)
objComm.Parameters.Add(returnValueParam)
objComm.Connection.Open()
Dim objReader As SqlClient.SqlDataReader = objComm.ExecuteReader()
intTotalHolidays = returnValueParam.Value()
should I use executescalar instead of datareader? I am not sure where to go here for the entire function that I need so I can get this into my SSRS report.
I would do it a little more simply and call the stored procedure using the RS data source and query functionality. You can call the stored procedure and create a one row data set to use in your report.
Forgot to add you can have multiple datasets in your report, so you are not limited to this query.
|||actually, that's not a bad idea...thanks, will try it.|||
For some reason, completely forgot about datasets in my report! I had initially created one to run a Stored Proc as the DataSet...then I just added another to run this stored proc to return the field then added that field to a textbox and that was it!
thanks for refreshing my memory about datasource, which lead me to create a new dataset instead!
Help creating Embedded code Function
How can I create a VB.NET function to run my query below that my SSRS report can run to retreive TotalPostingDays so I can show that in a textbox in my report? I need help creating the function in the Report properties of my SSRS report and not sure how to call this stored procedure to return TotalPostingDays.
I need to do this calculation by using a UDF, not stored procedure maybe. The problem is, I do still need to do a lookup to my Holiday table as part of my UDF though; the rest can proabably be done in Straight VB for the weekend and other calculations:
LTER PROCEDURE SSRS_Return_TotalPostingDays
AS
DECLARE @.TotalDaysInMonth int,
@.today datetime,
@.TotalWeekendDays int,
@.TotalHolidaysThisMonth int,
@.TotalPostingDays int
SET @.today = GETDATE()
-- TOTAL DAYS THIS MONTH
SET @.TotalDaysInMonth = CASE WHEN DatePart(mm,GetDate()) IN (1,3,5,7,8,10,12) THEN
31
ELSE
DateDiff(day,GetDate(),DateAdd(mm, 1, GetDate()))
END
-- TOTAL HOLIDAYS THIS MONTH
SELECT @.TotalHolidaysThisMonth = (SELECT COUNT(*) FROM ReportingServer.dbo.Holidays
WHERE HolidayDate BETWEEN (DATEADD(DAY, -DATEPART(DAY, @.today) + 1, @.today))
AND (DATEADD(DAY, -DATEPART(DAY, @.today), DATEADD(MONTH, 1, @.today))))
-- TOTAL # WEEKEND DAYS THIS MONTH
DECLARE @.date DATETIME
SET @.date = '20060101'
SELECT @.TotalWeekendDays = 8 +
CASE WHEN ISDATE(CONVERT(CHAR(6), @.date, 112) + '29') = 1 THEN
CASE WHEN DATENAME(WEEKDAY, CONVERT(CHAR(6), @.date, 112) + '01') IN ('Saturday', 'Sunday')
THEN 1 ELSE 0 END ELSE 0 END +
CASE WHEN ISDATE(CONVERT(CHAR(6), @.date, 112) + '30') = 1 THEN
CASE WHEN DATENAME(WEEKDAY, CONVERT(CHAR(6), @.date, 112) + '02') IN ('Saturday', 'Sunday')
THEN 1 ELSE 0 END ELSE 0 END +
CASE WHEN ISDATE(CONVERT(CHAR(6), @.date, 112) + '31') = 1 THEN
CASE WHEN DATENAME(WEEKDAY, CONVERT(CHAR(6), @.date, 112) + '03') IN ('Saturday', 'Sunday')
THEN 1 ELSE 0 END ELSE 0 END
SET @.TotalPostingDays = @.TotalDaysInMonth - (@.TotalHolidaysThisMonth + @.TotalWeekendDays)
RETURN @.TotalPostingDays
for the holiday lookup, I can probably do something like this then use the variable below to proceed or something:
Dim intTotalPostingDays As Integer
Dim objConn As New SqlConnection("Data Source=server;Initial Catalog=database; integrated security=SSPI;persist security info=False; Trusted_Connection=Yes")
Dim objComm As New SqlCommand("SSRS_Return_TotalHolidaysThi
objComm.CommandType = CommandType.StoredProcedure
Dim returnValueParam As New SqlClient.SqlParameter("@.RETURN_VALUE", SqlDbType.Int)
objComm.Parameters.Add(returnValueParam)
objComm.Connection.Open()
Dim objReader As SqlClient.SqlDataReader = objComm.ExecuteReader()
intTotalHolidays = returnValueParam.Value()
should I use executescalar instead of datareader? I am not sure where to go here for the entire function that I need so I can get this into my SSRS report.
I would do it a little more simply and call the stored procedure using the RS data source and query functionality. You can call the stored procedure and create a one row data set to use in your report.
Forgot to add you can have multiple datasets in your report, so you are not limited to this query.
|||actually, that's not a bad idea...thanks, will try it.|||
For some reason, completely forgot about datasets in my report! I had initially created one to run a Stored Proc as the DataSet...then I just added another to run this stored proc to return the field then added that field to a textbox and that was it!
thanks for refreshing my memory about datasource, which lead me to create a new dataset instead!
sqlHelp Creating Dynamic Reports
I can create static report with Report Server, but wanted if anyone can help me create a report that is dynamic and regenerates with new data as it comes in.
Thanks so much!
Jvinsky,
Maybe I missing something in what your asking, but adding a dataset to your report, add those fields on the report will get you a dynamic report as "your data changes". If you add a dataset which returns customers, you will see all of your customer, if customers are added to the table and if you refresh your report then you will see those "new" customers.
I hope this is what you meant by "dynamic".
Ham
Help creating a Transact SQL Insert Statement
Hi there can anyone help me to create a SQL Insert Statement. I dont know SQL at all.
To explain, I have two web pages. search.asp and results.asp.
search.asp has the following.
Form Name: searchForm
Set to: Post
Action: results.asp
Text Field: Keyword
Drop Down: CategoryTable
Drop Down: Location
UserName Session Variable: MM_UserName
results.asp has the following SQL which pulls all the results.
SELECT SupplierName, Location, ShortDescription, TimberSpecies, CategoryTitle, Country, CustomerType
FROM Query1
WHERE TimberSpecies LIKE '%MMColParam%' AND CategoryTitle LIKE '%MMColParam2%' AND Location LIKE '%MMColParam3%' AND CustomerType = 'Trade/Retail'
ORDER BY CategoryTitle, TimberSpecies ASC
The database & form I want to insert into.
tblSearcheResults
idSearch (AutoNumber)
location (Text) "Want to insert the 'Location' result here"
category (Text) "Want to insert the 'CategoryTable' result here"
user (Text) "Want to insert the UserName Session Variable result here"
result (Text) "Want to insert the 'Keyword' result here"
Please ask if u need more info.
Mally
Hi,
INSERT INTO tblSearchResults
(
location,
category,
user,
result
)
VALUES
(
Location, --should be due to the POST action in Request.Form("Location")
category, --should be due to the POST action in Request.Form("category")
user, --should be due to the POST action in Session("MM_UserName")
keyword, --should be due to the POST action in Request.Form("keyword")
)
Variable have to be read first and the query string to be constructed before sending it to SQL Server, keep in mind that you have to quote the string while composing the SQL String.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
Help creating a DELETE trigger.
I need to create a trigger that will run whenever a record is deleted. I
want the trigger to delete records from table2 where the value in the ID
field matches the value in the ID field of the record being deleted from
table1.
I'm new to triggers and am not sure how to set this up.
Thanks,
JohnTry,
create trigger tr_table1_del on table1
for delete
as
set nocount on
if exists(select * from deleted as d inner join table2 as t on d.[id] = t[id])
begin
delete table2
where exists(select * from deleted ad d where d.[id] = table2.[id])
if @.@.error != 0
begin
rollback transaction
raiserror('Error deleting rows in table2.', 16, 1)
return
end
end
go
AMB
"John Piotrowski" wrote:
> I need to create a trigger that will run whenever a record is deleted. I
> want the trigger to delete records from table2 where the value in the ID
> field matches the value in the ID field of the record being deleted from
> table1.
> I'm new to triggers and am not sure how to set this up.
> Thanks,
>
> John
want the trigger to delete records from table2 where the value in the ID
field matches the value in the ID field of the record being deleted from
table1.
I'm new to triggers and am not sure how to set this up.
Thanks,
JohnTry,
create trigger tr_table1_del on table1
for delete
as
set nocount on
if exists(select * from deleted as d inner join table2 as t on d.[id] = t[id])
begin
delete table2
where exists(select * from deleted ad d where d.[id] = table2.[id])
if @.@.error != 0
begin
rollback transaction
raiserror('Error deleting rows in table2.', 16, 1)
return
end
end
go
AMB
"John Piotrowski" wrote:
> I need to create a trigger that will run whenever a record is deleted. I
> want the trigger to delete records from table2 where the value in the ID
> field matches the value in the ID field of the record being deleted from
> table1.
> I'm new to triggers and am not sure how to set this up.
> Thanks,
>
> John
help create query against two tables
stepdefinition has
steptype
flowid
stepid
task has
taskid
flowid
stepid
flowid and stepid for both tables match; meaning that if i found a record in task with a certain taskid, i could query stepdefinition with the same flowid and stepid to find the steptype.
well, i wanna do it the other way around. I query stepdefinition to find a list of flowids and stepids for a specific steptype.
select flowid, stepid from stepdefinition where steptype = -3
Now, I want to find all taskids in task for each flowid/stepid combination
here's a visual
http://www.filecabin.com/up1/1144249279-task.gifselect taskid from task
where stepID+flowID in
(select stepID+flowID from stepdefinition where steptype = -3)
order by taskid
works|||select t.taskid, t.stepID, t.flowID
from task as t
join stepdefinition as s
on ( t.stepID = s.stepID
and t.flowID = s.flowID )
where s.steptype = -3
steptype
flowid
stepid
task has
taskid
flowid
stepid
flowid and stepid for both tables match; meaning that if i found a record in task with a certain taskid, i could query stepdefinition with the same flowid and stepid to find the steptype.
well, i wanna do it the other way around. I query stepdefinition to find a list of flowids and stepids for a specific steptype.
select flowid, stepid from stepdefinition where steptype = -3
Now, I want to find all taskids in task for each flowid/stepid combination
here's a visual
http://www.filecabin.com/up1/1144249279-task.gifselect taskid from task
where stepID+flowID in
(select stepID+flowID from stepdefinition where steptype = -3)
order by taskid
works|||select t.taskid, t.stepID, t.flowID
from task as t
join stepdefinition as s
on ( t.stepID = s.stepID
and t.flowID = s.flowID )
where s.steptype = -3
Wednesday, March 21, 2012
Help again
Ok I was able to create a view through the GamingCommissiondb for all the other tables in the LicensingActions.dbo except for this table can someone help me our here?My first guess would be a typo.
After that, a permissions problem.
Then things start to get interesting!
-PatP
After that, a permissions problem.
Then things start to get interesting!
-PatP
Monday, March 19, 2012
Help abt reporting services
Hi,
I have VS 2005 and SQL Server 2005 installed.How do i create reports
using Reporting Services '
I can't find any tab like " Reporting Services" under File | new |
project .
What should I do 'R wrote:
> Hi,
>
> I have VS 2005 and SQL Server 2005 installed.How do i create reports
> using Reporting Services '
> I can't find any tab like " Reporting Services" under File | new |
> project .
> What should I do '
In VS2005, Create an empty solution, then in this solution add a new
project.
Under project types you should see "Business Intelligence Projects"
under project types.
Select this, then you can select "Report Server Project"
You can then create your reports and data sources as required, and
deploy to your report server.|||Have you installed the client tools? If you installed SQL 2005 on your
computer that does not install the client tools. You have to run setup
separately for that.
If you installed the client tools prior to VS 2005 then you need to install
them again.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"R" <rashmi.bhandarkar@.gmail.com> wrote in message
news:1166503957.547110.245550@.73g2000cwn.googlegroups.com...
> Hi,
>
> I have VS 2005 and SQL Server 2005 installed.How do i create reports
> using Reporting Services '
> I can't find any tab like " Reporting Services" under File | new |
> project .
> What should I do '
>
I have VS 2005 and SQL Server 2005 installed.How do i create reports
using Reporting Services '
I can't find any tab like " Reporting Services" under File | new |
project .
What should I do 'R wrote:
> Hi,
>
> I have VS 2005 and SQL Server 2005 installed.How do i create reports
> using Reporting Services '
> I can't find any tab like " Reporting Services" under File | new |
> project .
> What should I do '
In VS2005, Create an empty solution, then in this solution add a new
project.
Under project types you should see "Business Intelligence Projects"
under project types.
Select this, then you can select "Report Server Project"
You can then create your reports and data sources as required, and
deploy to your report server.|||Have you installed the client tools? If you installed SQL 2005 on your
computer that does not install the client tools. You have to run setup
separately for that.
If you installed the client tools prior to VS 2005 then you need to install
them again.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"R" <rashmi.bhandarkar@.gmail.com> wrote in message
news:1166503957.547110.245550@.73g2000cwn.googlegroups.com...
> Hi,
>
> I have VS 2005 and SQL Server 2005 installed.How do i create reports
> using Reporting Services '
> I can't find any tab like " Reporting Services" under File | new |
> project .
> What should I do '
>
Subscribe to:
Posts (Atom)