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>
Showing posts with label creating. Show all posts
Showing posts with label creating. Show all posts
Friday, March 30, 2012
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
Monday, March 26, 2012
help for creating stored procedure
ALTER PROCEDURE companyinsert
@.companyid INT IDENTITY(1,1) NOT NULL,
@.companyname varchar(20),
@.address1 varchar(30)
AS
INSERT INTO companymaster
( companyname, address1)
VALUES (@.companyname,@.address1)
i don't want the companyname having the same names are recorded again with the different company id..
Can anyone help me and modify my code according it's giving error...in the @.companyid.
It is being done in sql server 2005 with asp.net C# 2005
You cannot declare a parameter with IDENTITY property in a stored proc. I think what you are looking for is more along these lines. You might want to read up documentation on SCOPE_IDENTITY(). Briefly, it returns the Identity value that was created due to your INSERT.
ALTER PROCEDURE companyinsert@.companyidINT OUTPUT,@.companynamevarchar(20),@.address1varchar(30)ASBEGINSET NOCOUNT ONIFNOT EXISTS(SELECT *FROM companymasterWHERE CompanyName = @.companynameAND Address1 = address1)BEGININSERT INTO companymaster ( companyname, address1)VALUES (@.companyname,@.address1)SELECT @.companyid = SCOPE_IDENTITY()ENDSET NOCOUNT OFFEND
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 complicated database...
Hi,
for my website im going to be selling rims, and i need to have available a search by vehicle
SKU: 8438743
CAT: RIM
BRAND: ADVAN
MODEL: KREUZER_SERIES_V
DIAMETER: 17
WIDTH: 8
OFFSET: 45
LUGS: 5
LUG SPACING: 114.3
FINISH: GMETAL
that would be the determing factors of the results, heres what would define for example a
2002 Accord V6
LUGS = 5
LUG SPACING = 114.3
55 > OFFSET > 40
DIAMETER < 20
WIDTH < 9
so for the accord
the required attributes are a 5X114.3 lug pattern
the offset needs to be 40-55
max diameter is 19"
max width = 8"
My question is this, how many databases do i need to make? and what catagories do I use in each database? Also is it possible to do less than, greater than, etc, in databases? I think thats everything, thanks!I think you need two tables.
table Rim
RimID
Sku
Cat
Brand
Model
Diameter
width
Offset
lugs
lugspacing
finish
table CarRimRequirements
CarID
CarModel
Lugs
Lugspacing
MaxDiameter
MaxWidth
OffsetMin
OffsetMax
You can't really put a range in one column, use a minumum and maximum column like I did for the offset.
Hope that helps!
KJ|||This is just another suggestion.
tblRim
RimID
RimDescription
SKU
Brand
Model
Finish
tblAttribute
AttributeID
AttributeName
tblRimAttributes
RimAttributeID
RimID
AttributeID
Value
tblCar
CarID
CarModel
tblCarAttributes
CarAttributeID
CarID
AttributeID
Requirement – Allow this field to have null.
This is how it relates to your example.
tblRim
RimID RimDescription SKU Brand Model Finish
1Rim 8438743 ADVAN KREUZER GMETAL
tblAttribute
AttributeID AttributeName
1Diameter
2Width
3Offset
4MaxOffset
5MinOffset
6Lugs
7LugSpacing
tblRimAttributes
RimAttributeID RimID AttributeID Value
1 1 1 17
2 1 2 8
3 1 3 45
4 1 6 5
5 1 7 114.3
tblCar
CarID CarModel
1 2002 Accord V6
tblCarAttributes
CarAttributeID CarID AttributeID Requirment
1 1 1 19
2 1 2 8
3 1 4 55
4 1 5 40
5 1 6 5
6 1 7 114.3
If you are using Stored Procedures first you could get all the requirements for the selected car.
Then you could pass these values as parameters to another Stored Procedure, which would return all the Rims which meet these requirements.
Help Creating a Trigger
These triggers are killing me, too new, I hope someone can help.
I need a trigger that will fire if data in a table is changed and if so
update another table;
Like: if fieldtable changes update trackingtable where fieldtable.ID =
trackingtable.IDLook into the trigger keyword/function UPDATE()
You might also consider a cascading update in a pk/fk...
<pasterto@.hotmail.com> wrote in message
news:1123103443.926156.216160@.g47g2000cwa.googlegroups.com...
> These triggers are killing me, too new, I hope someone can help.
> I need a trigger that will fire if data in a table is changed and if so
> update another table;
> Like: if fieldtable changes update trackingtable where fieldtable.ID =
> trackingtable.ID
>|||Something like this:
create table table1
(
table1Id int primary key,
value varchar(10)
)
insert into table1 values (1,'value')
insert into table1 values (2,'value')
insert into table1 values (3,'value')
insert into table1 values (4,'value')
go
create table table2
(
table2Id int primary key,
value varchar(10),
table1Id int foreign key references table1 (table1Id)
)
insert into table2 values (10,'value',1)
insert into table2 values (20,'value',1)
insert into table2 values (30,'value',2)
insert into table2 values (40,'value',3)
go
create trigger trTable1_update
on table1
after update
as
update table2
set value = inserted.value
from table2
join inserted
on table2.table1Id = inserted.table1Id
go
update table1
set value = 'newval'
where table1Id = 1
select * from table2
update table1
set value = 'allnew'
select * from table2
This is the lean version, and will need error handling and such, so if you
need info about that, let us know!
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
<pasterto@.hotmail.com> wrote in message
news:1123103443.926156.216160@.g47g2000cwa.googlegroups.com...
> These triggers are killing me, too new, I hope someone can help.
> I need a trigger that will fire if data in a table is changed and if so
> update another table;
> Like: if fieldtable changes update trackingtable where fieldtable.ID =
> trackingtable.ID
>|||THANK YOU... !!!
underscored and understated.|||See here you're running an update on everything not just changed rows. That
will get expensive quickly. I personally would use a cascade update for
this task, but if that doesn't work, I would only update values in table2
that have a corrosponding value in the inserted table that has
UPDATE(value)...
"Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
news:uCYXpEHmFHA.3832@.TK2MSFTNGP10.phx.gbl...
> Something like this:
> create table table1
> (
> table1Id int primary key,
> value varchar(10)
> )
> insert into table1 values (1,'value')
> insert into table1 values (2,'value')
> insert into table1 values (3,'value')
> insert into table1 values (4,'value')
> go
> create table table2
> (
> table2Id int primary key,
> value varchar(10),
> table1Id int foreign key references table1 (table1Id)
> )
> insert into table2 values (10,'value',1)
> insert into table2 values (20,'value',1)
> insert into table2 values (30,'value',2)
> insert into table2 values (40,'value',3)
> go
> create trigger trTable1_update
> on table1
> after update
> as
> update table2
> set value = inserted.value
> from table2
> join inserted
> on table2.table1Id = inserted.table1Id
> go
> update table1
> set value = 'newval'
> where table1Id = 1
>
> select * from table2
> update table1
> set value = 'allnew'
> select * from table2
>
> This is the lean version, and will need error handling and such, so if you
> need info about that, let us know!
> --
> ----
--
> Louis Davidson - http://spaces.msn.com/members/drsql/
> SQL Server MVP
>
> <pasterto@.hotmail.com> wrote in message
> news:1123103443.926156.216160@.g47g2000cwa.googlegroups.com...
>|||What?
This only modifies the table2 rows that match the join. Otherwise rows that
didn't match would end up NULL, right?
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Ben" <ben@.online.nospam> wrote in message
news:57a4f$42f13aec$d8445835$17008@.FUSE.NET...
> See here you're running an update on everything not just changed rows.
> That will get expensive quickly. I personally would use a cascade update
> for this task, but if that doesn't work, I would only update values in
> table2 that have a corrosponding value in the inserted table that has
> UPDATE(value)...
>
> "Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
> news:uCYXpEHmFHA.3832@.TK2MSFTNGP10.phx.gbl...
>|||IF UPDATE() doesn't tell you what rows changed. It tells you what
*columns* were referenced in the SET part of the UPDATE. The IF
UPDATE() is pretty useless IMO. If you really want to know what values
changed then join to the INSERTED table as Louis did and compare the
values in the join.
David Portas
SQL Server MVP
--|||I guess it boils down to what constitutes a data change. I'm stuck with a
one field change to one field. So to me, if you have a row with fields 1, 2
and 3, and you want to cascade updates to field 2 to a different table, it
doesn't make sense to update the second table if only field 1 has been
altered in the original table. This is just an assumption I'm making, which
is probably wrong.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1123107560.009756.66460@.o13g2000cwo.googlegroups.com...
> IF UPDATE() doesn't tell you what rows changed. It tells you what
> *columns* were referenced in the SET part of the UPDATE. The IF
> UPDATE() is pretty useless IMO. If you really want to know what values
> changed then join to the INSERTED table as Louis did and compare the
> values in the join.
> --
> David Portas
> SQL Server MVP
> --
>|||Also as a follow-up, the inserted table in a trigger will contain all
columns for all of the updated rows not just the columns that were updated.
UPDATE() will tell you which of those colums have been updated for which
records.
For example, the update method i'm talking about would look something
like...
UPDATE table2
SET value = inserted.value
FROM table2
INNER JOIN inserted ON table2.table1Id = inserted.table1Id
WHERE UPDATE(value)
And if there are multiple columns being updated I would do the following...
UPDATE table2
SET valueN = inserted.valueN
FROM table2
INNER JOIN inserted ON table2.table1Id = inserted.table1Id
WHERE UPDATE(valueN)
I don't think you need to/can qualify value inside UPDATE it assumes
inserted...
"Ben" <ben@.online.nospam> wrote in message
news:cb8ef$42f14f04$d8445835$26404@.FUSE.NET...
>I guess it boils down to what constitutes a data change. I'm stuck with a
>one field change to one field. So to me, if you have a row with fields 1,
>2 and 3, and you want to cascade updates to field 2 to a different table,
>it doesn't make sense to update the second table if only field 1 has been
>altered in the original table. This is just an assumption I'm making,
>which is probably wrong.
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:1123107560.009756.66460@.o13g2000cwo.googlegroups.com...
>|||Oh, I see. You meant rows that changed, not just updated ones. I always
just do it as a single update statement. An improvement might be to do
something like:
and table2.value <> inserted.value
As for conditional execution, any opinion on which is better?
create trigger trTable1_update
on table1
after update
as
if update(value)
begin
update table2
set value = inserted.value
from table2
join inserted
on table2.table1Id = inserted.table1Id
end
update table2
set value = inserted.value
from table2
join inserted
on table2.table1Id = inserted.table1Id
where update(value)
go
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
news:%23IhRpdHmFHA.320@.TK2MSFTNGP09.phx.gbl...
> What?
>
> This only modifies the table2 rows that match the join. Otherwise rows
> that didn't match would end up NULL, right?
> --
> ----
--
> Louis Davidson - http://spaces.msn.com/members/drsql/
> SQL Server MVP
>
> "Ben" <ben@.online.nospam> wrote in message
> news:57a4f$42f13aec$d8445835$17008@.FUSE.NET...
>
I need a trigger that will fire if data in a table is changed and if so
update another table;
Like: if fieldtable changes update trackingtable where fieldtable.ID =
trackingtable.IDLook into the trigger keyword/function UPDATE()
You might also consider a cascading update in a pk/fk...
<pasterto@.hotmail.com> wrote in message
news:1123103443.926156.216160@.g47g2000cwa.googlegroups.com...
> These triggers are killing me, too new, I hope someone can help.
> I need a trigger that will fire if data in a table is changed and if so
> update another table;
> Like: if fieldtable changes update trackingtable where fieldtable.ID =
> trackingtable.ID
>|||Something like this:
create table table1
(
table1Id int primary key,
value varchar(10)
)
insert into table1 values (1,'value')
insert into table1 values (2,'value')
insert into table1 values (3,'value')
insert into table1 values (4,'value')
go
create table table2
(
table2Id int primary key,
value varchar(10),
table1Id int foreign key references table1 (table1Id)
)
insert into table2 values (10,'value',1)
insert into table2 values (20,'value',1)
insert into table2 values (30,'value',2)
insert into table2 values (40,'value',3)
go
create trigger trTable1_update
on table1
after update
as
update table2
set value = inserted.value
from table2
join inserted
on table2.table1Id = inserted.table1Id
go
update table1
set value = 'newval'
where table1Id = 1
select * from table2
update table1
set value = 'allnew'
select * from table2
This is the lean version, and will need error handling and such, so if you
need info about that, let us know!
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
<pasterto@.hotmail.com> wrote in message
news:1123103443.926156.216160@.g47g2000cwa.googlegroups.com...
> These triggers are killing me, too new, I hope someone can help.
> I need a trigger that will fire if data in a table is changed and if so
> update another table;
> Like: if fieldtable changes update trackingtable where fieldtable.ID =
> trackingtable.ID
>|||THANK YOU... !!!
underscored and understated.|||See here you're running an update on everything not just changed rows. That
will get expensive quickly. I personally would use a cascade update for
this task, but if that doesn't work, I would only update values in table2
that have a corrosponding value in the inserted table that has
UPDATE(value)...
"Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
news:uCYXpEHmFHA.3832@.TK2MSFTNGP10.phx.gbl...
> Something like this:
> create table table1
> (
> table1Id int primary key,
> value varchar(10)
> )
> insert into table1 values (1,'value')
> insert into table1 values (2,'value')
> insert into table1 values (3,'value')
> insert into table1 values (4,'value')
> go
> create table table2
> (
> table2Id int primary key,
> value varchar(10),
> table1Id int foreign key references table1 (table1Id)
> )
> insert into table2 values (10,'value',1)
> insert into table2 values (20,'value',1)
> insert into table2 values (30,'value',2)
> insert into table2 values (40,'value',3)
> go
> create trigger trTable1_update
> on table1
> after update
> as
> update table2
> set value = inserted.value
> from table2
> join inserted
> on table2.table1Id = inserted.table1Id
> go
> update table1
> set value = 'newval'
> where table1Id = 1
>
> select * from table2
> update table1
> set value = 'allnew'
> select * from table2
>
> This is the lean version, and will need error handling and such, so if you
> need info about that, let us know!
> --
> ----
--
> Louis Davidson - http://spaces.msn.com/members/drsql/
> SQL Server MVP
>
> <pasterto@.hotmail.com> wrote in message
> news:1123103443.926156.216160@.g47g2000cwa.googlegroups.com...
>|||What?
This only modifies the table2 rows that match the join. Otherwise rows that
didn't match would end up NULL, right?
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Ben" <ben@.online.nospam> wrote in message
news:57a4f$42f13aec$d8445835$17008@.FUSE.NET...
> See here you're running an update on everything not just changed rows.
> That will get expensive quickly. I personally would use a cascade update
> for this task, but if that doesn't work, I would only update values in
> table2 that have a corrosponding value in the inserted table that has
> UPDATE(value)...
>
> "Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
> news:uCYXpEHmFHA.3832@.TK2MSFTNGP10.phx.gbl...
>|||IF UPDATE() doesn't tell you what rows changed. It tells you what
*columns* were referenced in the SET part of the UPDATE. The IF
UPDATE() is pretty useless IMO. If you really want to know what values
changed then join to the INSERTED table as Louis did and compare the
values in the join.
David Portas
SQL Server MVP
--|||I guess it boils down to what constitutes a data change. I'm stuck with a
one field change to one field. So to me, if you have a row with fields 1, 2
and 3, and you want to cascade updates to field 2 to a different table, it
doesn't make sense to update the second table if only field 1 has been
altered in the original table. This is just an assumption I'm making, which
is probably wrong.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1123107560.009756.66460@.o13g2000cwo.googlegroups.com...
> IF UPDATE() doesn't tell you what rows changed. It tells you what
> *columns* were referenced in the SET part of the UPDATE. The IF
> UPDATE() is pretty useless IMO. If you really want to know what values
> changed then join to the INSERTED table as Louis did and compare the
> values in the join.
> --
> David Portas
> SQL Server MVP
> --
>|||Also as a follow-up, the inserted table in a trigger will contain all
columns for all of the updated rows not just the columns that were updated.
UPDATE() will tell you which of those colums have been updated for which
records.
For example, the update method i'm talking about would look something
like...
UPDATE table2
SET value = inserted.value
FROM table2
INNER JOIN inserted ON table2.table1Id = inserted.table1Id
WHERE UPDATE(value)
And if there are multiple columns being updated I would do the following...
UPDATE table2
SET valueN = inserted.valueN
FROM table2
INNER JOIN inserted ON table2.table1Id = inserted.table1Id
WHERE UPDATE(valueN)
I don't think you need to/can qualify value inside UPDATE it assumes
inserted...
"Ben" <ben@.online.nospam> wrote in message
news:cb8ef$42f14f04$d8445835$26404@.FUSE.NET...
>I guess it boils down to what constitutes a data change. I'm stuck with a
>one field change to one field. So to me, if you have a row with fields 1,
>2 and 3, and you want to cascade updates to field 2 to a different table,
>it doesn't make sense to update the second table if only field 1 has been
>altered in the original table. This is just an assumption I'm making,
>which is probably wrong.
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:1123107560.009756.66460@.o13g2000cwo.googlegroups.com...
>|||Oh, I see. You meant rows that changed, not just updated ones. I always
just do it as a single update statement. An improvement might be to do
something like:
and table2.value <> inserted.value
As for conditional execution, any opinion on which is better?
create trigger trTable1_update
on table1
after update
as
if update(value)
begin
update table2
set value = inserted.value
from table2
join inserted
on table2.table1Id = inserted.table1Id
end
update table2
set value = inserted.value
from table2
join inserted
on table2.table1Id = inserted.table1Id
where update(value)
go
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
news:%23IhRpdHmFHA.320@.TK2MSFTNGP09.phx.gbl...
> What?
>
> This only modifies the table2 rows that match the join. Otherwise rows
> that didn't match would end up NULL, right?
> --
> ----
--
> Louis Davidson - http://spaces.msn.com/members/drsql/
> SQL Server MVP
>
> "Ben" <ben@.online.nospam> wrote in message
> news:57a4f$42f13aec$d8445835$17008@.FUSE.NET...
>
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 Stored Procedure
I have some script for creating the table i want but i need some help creating a Stored Procedure. That will Insert the UserId from the aspnet_Users Table (UserId) into the UserId Column in my table. So when a user creates an account it will put that users id into my table. The data will be retrieved by a FormView and the user can Update their info threw the FormView control..
I just need to associate the UserId from the aspnet_User Table with my table, so the data is stored per UserId in a new row for each new user..
create table dbo.custom_Profile (UserIduniqueidentifiernot nullPrimary Key,IamWeArenvarchar(50)null,InterestedInnvarchar(256)null,IntroTitlenvarchar(100)null,TellOthersnvarchar(MAX)null,MaritalStatusnvarchar(20)null,BodyTypenvarchar(50)null,Racenvarchar(20)null,Smokingnvarchar(20)null,Drinkingnvarchar(20)null,Drugsnvarchar(20)null,Educationnvarchar(256)null)goHere is what i have so far for the stored procedure
create procedure getcustomProfile @.UserIduniqueidentifierasSELECT *FROM dbo.aspnet_UsersWHERE UserId = @.UserIdgoifnot exists (select *from dbo.aspnet_Userswhere UserId = @.UserId)BEGINDeclare @.UserIduniqueidentifier update (dbo.content_Profile)set UserId = @.UserIdwhere UserId = @.UserIdEndELSEBEGIN INSERT INTO dbo.custom_Profile (UserId) VALUES (@.UserId)END|||
Hi,
I'm sure you can achieve what you want from a stored procedure, but there are easier ways. The article at:
http://davidhayden.com/blog/dave/archive/2006/01/27/2775.aspx
This shows how to create a table using SMO.
Hope this helps.
Paul
|||Thats cool something to look into later, but for now i'm just working on studying the creation of Stored Procedures. Won't learn how to create them if a program does most of the work for me, why i prefer to do it this way for now
|||Hi,
No problem with that. Mind you, think i mis-read it anyway - thought you needed to create a table from a stored procedure. You don't actually say what is wrong with what you've done so far. I might be wrong, but I don't think you need the line 'Declare @.UserIduniqueidentifier'as you have already passed this in as a parameter value.
Let me know what appears to be going wrong and I'll try and help further.
Paul
|||I was just trying out the table and sp in my site, and when i create a new user account a new row isn't created in the column for the table..Trying to get the stored procedure to take the UserId from the aspnet_Users table example (59afcb85-c20c-4937-8ab9-a44a57e22ce0). Than Insert that UserId into my table custom_Profile (Column UserId) and do this for each new user account..
|||forgot to put in the T-SQL ...lol here you gocreate table dbo.custom_Profile (UserIduniqueidentifiernot nullPrimary Key,IamWeArenvarchar(50)null,InterestedInnvarchar(256)null,IntroTitlenvarchar(100)null,TellOthersnvarchar(MAX)null,MaritalStatusnvarchar(20)null,SexualOrientationnvarchar(20)null,Heightnchar(10)null,BodyTypenvarchar(50)null,Racenvarchar(20)null,Smokingnvarchar(20)null,Drinkingnvarchar(20)null,Drugsnvarchar(20)null,Educationnvarchar(256)null,Circumcisednvarchar(20)null)gocreate procedure getcustomProfile @.UserIduniqueidentifier asSELECT *FROM dbo.aspnet_UsersWHERE UserId = @.UserIdgoBEGININSERT dbo.custom_Profile (UserId)VALUES (@.UserId)SELECT *FROM dbo.aspnet_UsersWHERE UserId = @.UserIdEND|||
Oh and when i remove the Declare @.UserId uniqueidentifier i get a error::
Msg 137, Level 15, State 2, Line 5
Must declare the scalar variable "@.UserId".
|||Hi,
Apologies, I misunderstood. The problem is passing of the uniqueidentifier - this will cause the SP to fail. You should change this to an nvarchar field. I messed around a little and the following SP will loop through all of the users:
CREATE procedure [dbo].[getcustomProfile] @.UserIdnvarchar(150)ASDECLARE UserInsertCURSORKEYSETFOR SELECT UserIDFROM dbo.aspnet_UsersDECLARE @.Usernvarchar(150)OPEN UserInsertFETCH NEXT FROM UserInsertINTO @.UserWHILE (@.@.fetch_status = 0)BEGININSERT dbo.custom_Profile (UserId)VALUES (@.User)FETCH NEXT FROM UserInsertINTO @.UserENDCLOSE UserInsertDEALLOCATE UserInsert
This does work, and you can use it as your starting point for the actual SP you need to call each time from your form.
Hope it helps,
Paul
|||So i need to call the SP from the code behind correct? for it to insert the UserId into the table upon creation of a new user account|||Your logic in your original stored proc is incorrect. You have
IF NOT EXISTS(...)
UPDATE
ELSE
INSERT
If you think about it, how can you update something that doesnt exist? It should be the other way.
IF NOT EXISTS(...)
INSERT
ELSE
UPDATE
Fix your proc according to the logic above and post back if you have any issues.
|||Hi,
Yes, you should call the SP from BLL once you've gathered together the results of your form. I'm assuming from the way you set out the SP that you're intending to do the User insert first and then then pass in the new UserID (with other parameters) - you should be setting up an output parameter from your Add_User SP. You actually don't need your If Exists part of the SP because you can check that the UserID has been created before allowing the call to this SP - the fact that it is a uniqueidentifier means it won't exist already. Personally, I always keep Insert, Update and Delete SPs seperate - but that is just personal preference.
Let e know if you need anything else on this.
Cheers,
Paul
|||The UserId is the only data set getting populated automatically upon Registration of the site, the other columns aren't populated until they have registered. Than they get Re Directed to another page where they fill in the information via FormView in the User Control Panel. Than that information is displayed by another SqlDataSource and FormView on their profile page but without the Delete,Insert,Update etc. Basically using a Content Management system for the user profile to display textual information, on their page.
I'm going to use the UserId so i can pass it into the URL for unique pages like most communities (Myspace etc etc)..
|||Why is that the aspnet_Membership,Users,UsersInRoles etc don't use CodeBehind to pass the UserId into each table? cause thats what i want to do..Pass the UserId from the aspnet_Users Table into my table|||Hi,
You can do all of that by customising the sqlMembershipProvider. It's not as difficult as you might think - thankfully! There's a pretty good tutorial to get you started at:
http://aspnet.4guysfromrolla.com/articles/120705-1.aspx
I hope this helps.
Paul
sqlHelp creating a SELECT statement for "today"
Hello,
I am attempting to build a MS SQL query that will return data from
"today"; today being current day 8:00AM-10:00PM today. My goal is to
return the data from a table that is written to throughout the day, the
query will provide the current grade of service in our call center.
I am having difficulty defining my where clause:
- I can accomplish my goal my statically defining my 'date between' as
the actual date and time (not ideal)
- I can accomplish the second part of my date using CURRENT_TIMESTAMP;
but I am unable to define the starting point
Here is where I am thus far:
/* We are going to count the total calls into each queue from start of
business today (8:00AM) to now */
select COUNT(Result) as "Total Sales Calls Offered" from
dbo.QueueEncounter
where Direction='0'
and
QueueID='1631'
and
/* This is where I get lost */
Time between DATEPART(day, GETDATE()) and DATEPART(day, GETDATE())
Clearly the last line returns zero as there are no calls between the
same date range. How can I add to that line, or write this to work?
Any thoughts?
Thanks for the help.
-ChrisOn 25 Jan 2006 10:03:53 -0800, Chris wrote:
>Hello,
>I am attempting to build a MS SQL query that will return data from
>"today"; today being current day 8:00AM-10:00PM today. My goal is to
>return the data from a table that is written to throughout the day, the
>query will provide the current grade of service in our call center.
>I am having difficulty defining my where clause:
>- I can accomplish my goal my statically defining my 'date between' as
>the actual date and time (not ideal)
>- I can accomplish the second part of my date using CURRENT_TIMESTAMP;
>but I am unable to define the starting point
>Here is where I am thus far:
>/* We are going to count the total calls into each queue from start of
>business today (8:00AM) to now */
>select COUNT(Result) as "Total Sales Calls Offered" from
>dbo.QueueEncounter
>where Direction='0'
>and
>QueueID='1631'
>and
>/* This is where I get lost */
>Time between DATEPART(day, GETDATE()) and DATEPART(day, GETDATE())
>Clearly the last line returns zero as there are no calls between the
>same date range. How can I add to that line, or write this to work?
>Any thoughts?
>Thanks for the help.
>-Chris
Hi Chris,
You say you want rows for today, 8:00AM-10:00PM. Does this imply that
the table also contains rows outside the 8:00AM-10:00PM time frame that
you don't want to include?
AND Time BETWEEN DATEADD(day, DATEDIFF(day, 0, CURRENT_TIMESTAMP),
'8:00AM')
AND DATEADD(day, DATEDIFF(day, 0, CURRENT_TIMESTAMP),
'10:00PM')
Note that this will include a row with time exactly equal to 10 PM, but
exclude a row with time 3 milliseconds after 10PM.
If you want all rows for the whole day (0:00 - 24:00), use
AND Time >= DATEADD(day, DATEDIFF(day, 0, CURRENT_TIMESTAMP), 0)
AND Time < DATEADD(day, DATEDIFF(day, 0, CURRENT_TIMESTAMP), 1)
--
Hugo Kornelis, SQL Server MVP|||Awesome Hugo, thanks so much for the help - My query is now nearly
complete; with one last problem....
declare @.today datetime,
@.tomorrow datetime,
@.offered smallint,
@.answeredin120 smallint,
@.GOS smallint
set @.today = convert(char(8), GETDATE ( ), 112)
set @.tomorrow = @.today + 1
-- Find total calls offered
set @.offered = (select COUNT(Result) from dbo.QueueEncounter
where Direction='0' and QueueID='1438' and Time >= @.today and Time <
@.tomorrow)
-- Find total calls answered in 120 seconds
set @.answeredin120 =(select COUNT(Result) from dbo.QueueEncounter
where Direction='0' and QueueID='1438' and Time >= @.today and Time <
@.tomorrow and WaitTime <= 120)
-- Divide the total calls offered by the total calls answered in X
multiplied by 100 to find current GOS ??
set @.GOS = (@.offered)/(@.answeredin120)*100
select @.GOS
The problem is my GOS is being returned as 100 when it is really apprx
77%.
Where did I go wrong?
-Thanks!|||On 25 Jan 2006 14:12:48 -0800, Chris wrote:
(snip)
>-- Divide the total calls offered by the total calls answered in X
>multiplied by 100 to find current GOS ??
>set @.GOS = (@.offered)/(@.answeredin120)*100
>select @.GOS
>The problem is my GOS is being returned as 100 when it is really apprx
>77%.
>Where did I go wrong?
Hi Chris,
Integer division: divide two integers, the result is integer too.
SELECT 1/3
SELECT 1.0/3
SELECT 1/3.0
SELECT 1.0/3.0
The above show that forcing at least one operand to non-integer suffices
to get a result with fraction. In your case, one possible way would be
SET @.GOS = CAST(@.offered AS decimal(10,2)) / @.answeredin120 * 100
Or even
SET @.GOS = 100.0 * @.offerec / @.answeredin120
--
Hugo Kornelis, SQL Server MVP|||Hugo, Again many thanks... I will try this at the office tomorrow.
Cheers.
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
Monday, March 19, 2012
HELP ....difficulty in insert of 2 tables data with id
Hello frdz,
I have two tables in sqlserver 2005.
I have created the stored procedure for insert,update data.I m creating my application in asp.net with C#
Table-1 CUSTOMERFields:
customerid int identity,
cardid int,
customername varchar(20) not null,
address varchar(20) not null,
city varchar(20) not null,
emailid varchar(20)Table-2 CARD
Fields:
cardid int identity,
cardtype varchar(20) not null,
carddetails varchar(20) not nullINSERT INTO CUSTOMER (customername ,address,city,emailid)
VALUES (@.customername,@.address,@.city,@.emailid)
SELECT @.customerid = SCOPE_IDENTITY()/* HELP HERE NOT ABLE TO GET DATA OF CARD */
SELECT @.cardid = cardid from CARD where customerid =@.cardid
Pls tell me how to insert the data ...
There is only one cardid for only one customerid both should be unique no duplication...
One customer can have only one and one card...
If I understand your design correctly, you'll have to insert the CARD record first and get the new identity fieldcardid into a variable. Then you insert into CUSTOMER, using the variable to populate CUSTOMERS.cardid.
This is because cardid is a foreign key in the CUSTOMER table - so it has to be generated first. Just switch your order of inserts and you should be fine. And I advise that you put both insert statements within a transaction (BEGIN TRAN and COMMIT) - if one insert fails, then the other one shouldn't be left in the table.
If you need more on stored procedures, here's a link to a free chapter ondeveloping stored procedures for sql server.
|||Your database to me seems odd, I would think that each customer could have many cards, while each card could only have 1 customer, but you seem to have it reversed. In any case, this is how you would insert:
DECLARE @.cardid int
INSERT INTO Card(cardtype,carddetails) VALUES (@.cardtype,@.carddetails)
SET @.cardid=SCOPE_IDENTITY()
INSERT INTO Customer(cardid,customername,address,city,emailid) VALUES (@.cardid,@.customername,@.address,@.city,@.emailid)
and optionally:
SELECT @.cardid,SCOPE_IDENTITY() to return both the generated cardid and customerid
|||Thanxs everyone for the replies...
Motley...thanxs ... ya i want that 1 customer can have only 1 card.
and optionally:
SELECT @.cardid,SCOPE_IDENTITY() to return both the generated cardid and customerid
I have created stored procedur for card and customer.
I don't understand what is the above line and how it worksSELECT @.cardid,SCOPE_IDENTITY() ??
This should be written in which stored procedure ? I mean card or customer ?
Should a single stored procedure be created for this ?
Thanxs......waiting for reply
|||That would return a resultset with 1 row and 2 columns. The first column would be the @.cardid variable that we set earlier in the code, and the second column would be SCOPE_IDENTITY(), which is a function that returns the value of the identity column for the row that was just inserted.
When I said optionally, I meant you could put at the end of the other code like this:
DECLARE @.cardid int
INSERT INTO Card(cardtype,carddetails) VALUES (@.cardtype,@.carddetails)
SET @.cardid=SCOPE_IDENTITY()
INSERT INTO Customer(cardid,customername,address,city,emailid) VALUES (@.cardid,@.customername,@.address,@.city,@.emailid)
SELECT @.cardid,SCOPE_IDENTITY()
||| thanxs very much....
Monday, February 27, 2012
Help - Error: Unable to open the physical file
Hi, I've just finished creating my first ASP.NET website. I created it on my laptop and it works perfectly, but I'm having some problems deploying it to my web server (PC).
The site works ok, but when I try to log in to my client area, I get this error:
"Unable to open the physical file "c:\inetpub\wwwroot\ONeillStrata\App_Data\aspnetdb.mdf". Operating system error 5: "5(Access is denied.)".
An attempt to attach an auto-named database for file c:\inetpub\wwwroot\ONeillStrata\App_Data\aspnetdb.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share."
I've searched far and wide for a solution and have read many articles, but none seem to be able to fix my problem. I've tried using that SEUtil tool, but that didn't work, I've made sure the App_Data directory on the web server isn't read only (the read-only checkbox isn't ticked, but it DOES have that little green square which I can't get rid of), I've assigned the ASPNET user as a db owner and that didn't work and I've manually attached the database to the web server's instance of SQL Express 2005.
When I launch the solution in VS2005 on the web server, I can browse through the database and see the tables and data etc, but when I try to run the application, I get that message above...
Surely it shouldn't be this hard to get it going?
Any help would be massively appreciated.
Hi,
It seems that the physical file location of the aspnetdb.mdf is wrong.
Please open your IIS, right click on your default web site,and click on the properties, change to the ASP.NET tab, edit configurations.Then you can see the LocalSqlServer string listed there, change the string into the following:data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|aspnetdb.mdf;User Instance=true.
Thanks.
Help - Creating reports in pdf format
Hi all,
Greetings
I m using Sql Server 2000 for creating Reports. I like to display the ad-hoc generated report in pdf format. Do i have any pdf writter to display the report or i can able to do it in programmatically. If we can then plz give me the source code to write a pdf report.
Advance Thanks & Regards
Guna.
PDF creating is an outofthebox feature of Reporting Service. It is available as a rendering format in the report manager (Web interface) of each report, unless inactivated.HTH, Jens K. Suessmeyer.
http//www.sqlserver2005.de
Subscribe to:
Posts (Atom)