Help in database design

I am designing an inventory database in which I need some help, I have the following entities:

Items
Notebook
Wireless Card
ADSL Modem
etc...
Models
Acer centrino 1.6
Acer centrino 1.73
3COM 4x1
25 hours Wi-fi access
50 hours Wi-fi access
etc...

Packages
Package A:
Acer centrino 1.6
3COM 4x1
25 hours Wi-fi access
Package B:
Acer centrino 1.73
3COM 4x1
50 hours Wi-fi access
I made a table for the items having the following fields: Item_ID, Item_Name
and another for the Models having: Model_ID, Model_Name, Item_Type(Foreign Key to Items table)
up to this point is this correct?
About the packages table, I don't know if it is correct to have a field for each model (one for notebook, other for modem, and other for wireless card) like this it would be like having 3 foreign keys to the same table but nothing distinguishes them
I don't know how to relate the packages and the models table.
Any recommendations for a proper design for those entities?

I would use the relational data model, and design it as follows. I really can't show you how the Items table is to be implemented in this schema as I haven't seen how you intend to use that table yet.

Table: Items
Columns: ItemID, ItemDescription

Table: Models
Columns: ModelID, ModelDescription

Table: Packages
Columns: PackageID, PackageDescription

Table: PackageModels
Columns: PackageID, ModelID

|||Also look like a relationship (1-M) between item and model although the naming seems strange.|||

Thanks for your replies,but they were a little bit late, I already finished the project

Here is the design I used, maybe it would be useful for anyone who might face something like that.

Items Table

Item_ID, Item_Name

Models Table

Model_ID, Model_Name, ItemType(FK to Items table,i.e. this model is of which item type)

Packages Table

Package_ID, Package_Name

Package_ItemModel

Package_ID, Item_ID, Model_ID

|||I'm suprised that Model isn't independant of item.|||

Why?

I need to specify whether hp nx6110 is a notebook,router or card and so on for other types.

|||argh, sorry the naming has confused me yet again.

Help in crystalReorts8.5

Hi all,

How to get hours between two datetime ranges.
Actually i have to do (enddatetime-startdatetime) and it converts into hours
plz help meuse datediff function
datediff('h',startdatetime,enddatetime)

hope it helps you|||Thanks for your reply

But if i use datediff function as you suggested,getting hours

i mean i am getting round fig : 1 hour and 2 hours likethat
but i need if it contains 1 hr 30 mins need to get 1.30

but iam getting 1 hr instead of 1.30

do u have any idea abt that|||u can find minutes and divide by 60 u will get in hrs

datediff('n',sdate,edate)/60|||Thanks for ur reply
but small problem make in grand total

i get this values like that
1.12
1.12
1.12
---
3.35 (instead of 3.36)
i get this value
what is the problem in this|||1.12 is not actually 1 hr and 12 mins

since 124/60 is not rounded off thats tats y u r not getting correct sum.

u can use

datediff('h',datetime('1:20pm'),datetime('2:22pm'))&'Hr'& datediff('n',datetime('1:20pm'),datetime('2:22pm'))-datediff('h',datetime('1:20pm'),datetime('2:22pm'))*60 & 'Min'

which returns
1.00hr2.00min

but u cannot perform any sum on this as it is return string|||thanks for reply,

but i want grantotal of total hours
what can i do
is there any alternative
plz reply me|||Thanks for ur reply
but small problem make in grand total

i get this values like that
1.12
1.12
1.12
---
3.35 (instead of 3.36)
i get this value
what is the problem in this

Hi use this

val(cstr(datediff('n',cdatetime('1:28pm'),cdatetime('1.45pm'))/60))

hope it works for u

Help in crystal reports 8.5

Hi all

I am working on Crystal reports 8.5
In one report we need to display weekdays and weekends in every month for the given date range

I mean

Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
Days of Service
Weekdays Number of weekdays in the month
Weekends Number of weekends in the month

And I need to calculate total of some items from Friday after 3 pm

2) In another report I am using stored procedure to get fields
As per my knowledge we cannot use other than one stored procedure in crystal reports 8.5
So I am using sub report to get some more fields other than stored procedure fields

But in main report I need to get sub- total of fields in sub report.

Could any one please help me out?You would use the Crystal Reports (http://www.saveonsupport.com) shared variable functionality. In your subreport you would have a formula field something like this:

WhilePrintingRecords;
Shared numbervar myTotal;
myTotal := myTotal + {field name}

Add this field to the subreport and suppress it.
Then in the main report you need another formula something like this:

WhilePrintingRecords;
Shared numbervar myTotal;
myTotal

The subreport keeps adjusting the total while printing records and the main report simply displays this value.

Hop this helps!|||Thanks for your reply
it works for me

help in crystal report & dataset

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

Help in Crystal Report

Hi,

I am new in crystal report (using vb.net). Currenlty, my report format
looks like below:

School : ABC
Class : A1
======================================
Details Stud1 IC No1 Address1
Stud2 IC No.2 Address2

Page Footer: Page 1

School : ABC
Class : A2
======================================
Details Stud3 IC No1 Address1
Stud4 IC No.2 Address2

Page Footer: Page 2

School : DBF
Class : A1
======================================
Details Stud1 IC No1 Address1
Stud2 IC No.2 Address2

Page Footer: Page 3

School : DBF
Class : A2
======================================
Details Stud3 IC No1 Address1
Stud4 IC No.2 Address2

Page Footer: Page 4

Both School and Class formula were placed in Page Header. Also, I have
two group headers ie. School then follows by Class (before the details
section). Currently, the report prints at new page for every different
Class.

How do I print the all classes of the same School continuously without
page break; and only move to new page if the current page has been
filled up. I need both the Class and School names to appear in every
new page. Also, For every new school, it should be a fresh new page ie.
should not mix the details with other school. Can anyone help in this
problem? Many thanks!Hi,

I hope you are using two groups. That means the grouping is made on "School" field as well as "Class" field. uncheck the "NEW page AFTER" under the "Class " group footer and check the same in "School" group footer.
For heading should be shown in all pages, check the "Repeat Group header on each page" Group Expert option

I hope , you got what you wanted

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>

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

Below is an RDL that demonstrates this.

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 In Coding

Dim objConn As New SqlConnection
Dim objCmd As New SqlCommand
Dim Value As String = EventCmb.SelectedItem.ToString()
objConn.ConnectionString = "Data Source=.\SQLEXPRESS;AttachDbFilename='C:\Documents and Settings\HP\My Documents\Visual Studio 2005\WebSites\FYP2\App_Data\Event.mdf';Integrated Security=True;Connect Timeout=30;User Instance=True"
Try
objConn.Open()
objCmd.Connection = objConn
objCmd.CommandType = CommandType.Text
objCmd.CommandText = "SELECT EventTel FROM Event WHERE (EventID = @.Value)" ' See how i changed Value to @.Value. This is called a Named Parameter
objCmd.Parameters.AddWithValue("@.Value", Value) ' Add the @.value withthe actual value that should be put. This makes it securer
Dim RetVal As Object = objCmd.ExecuteScalar() ' This returns the First Column of the first row regardless of how much data is returned.
If Not ((RetVal Is Nothing) Or (RetVal Is DBNull.Value)) Then
ContactLbl.Text = RetVal.ToString()
Else
' noting was returned
End If
Catch ex As Exception
Throw ex
Finally
objConn.Close()
End Try

There's an error for in line "Dim RetVal As Object = objCmd.ExecuteScalar() "

Error Message is as follow"Conversion failed when converting the nvarchar value 'LTA' to data type int."

It is due to "ExecuteScalar() "can only store int? I just need to display one data, andValue data comes form a combo box "EventCmb", which i wanted to find the selected String, compared to the "Event" database to get the "EventTel" data How do i solved this problem, any advice? Thanks!

In your code

Dim Value As String = EventCmb.SelectedItem.ToString()

you defined Value as a string, but in our sql

"SELECT EventTel FROM Event WHERE (EventID = @.Value)"

EventID look like a int, this is where the convering error come from

Hope this help

sql

Help in Coalescing distinct values

Hi all,

I want to know how to coalesce distinct values as comma seperated into a variable which is used elsewhere. Here are my ddl and the query I tried.
My Expected result is
[Java],[MySQL],[.Net]

Code Snippet

CREATE TABLE #Tbl_Request
(
ID INT,
SkillCategoryID INT
)
GO

CREATE TABLE #Lkp_SkillCategory
(
ID INT,
Skill varchar(50)
)
GO

INSERT INTO #Tbl_Request VALUES(1,0)
INSERT INTO #Tbl_Request VALUES(2,1)
INSERT INTO #Tbl_Request VALUES(3,2)
INSERT INTO #Tbl_Request VALUES(4,0)
INSERT INTO #Tbl_Request VALUES(5,2)
INSERT INTO #Tbl_Request VALUES(6,2)
INSERT INTO #Tbl_Request VALUES(7,1)
GO

INSERT INTO #Lkp_SkillCategory VALUES(0,'Java')
INSERT INTO #Lkp_SkillCategory VALUES(1,'MySQL')
INSERT INTO #Lkp_SkillCategory VALUES(2,'.Net')
GO

DECLARE @.listSkills nvarchar(max)
SELECT DISTINCT @.listSkills= COALESCE(@.listSkills+',','')+'['+ #Lkp_SkillCategory.Skill+']'
FROM #Tbl_Request INNER JOIN
#Lkp_SkillCategory ON #Tbl_Request.SkillCategoryID = #Lkp_SkillCategory.ID
SET @.listSkills=(SELECT Skill = @.listSkills)
SELECT @.listSkills

DROP TABLE #Lkp_SkillCategory,#Tbl_Request

The following is one of the method

Code Snippet

CREATE TABLE #Tbl_Request

(

ID INT,

SkillCategoryID INT

)

GO

CREATE TABLE #Lkp_SkillCategory

(

ID INT,

Skill varchar(50)

)

GO

INSERT INTO #Tbl_Request VALUES(1,0)

INSERT INTO #Tbl_Request VALUES(2,1)

INSERT INTO #Tbl_Request VALUES(3,2)

INSERT INTO #Tbl_Request VALUES(4,0)

INSERT INTO #Tbl_Request VALUES(5,2)

INSERT INTO #Tbl_Request VALUES(6,2)

INSERT INTO #Tbl_Request VALUES(7,1)

GO

INSERT INTO #Lkp_SkillCategory VALUES(0,'Java')

INSERT INTO #Lkp_SkillCategory VALUES(1,'MySQL')

INSERT INTO #Lkp_SkillCategory VALUES(2,'.Net')

GO

--Add Temp table to hold distinct values

create table #temp ( Skill varchar(50) )

Insert into #temp(Skill)

SELECT distinct #Lkp_SkillCategory.Skill

FROM #Tbl_Request INNER JOIN

#Lkp_SkillCategory ON #Tbl_Request.SkillCategoryID = #Lkp_SkillCategory.ID

--Coalesce from Temp table

DECLARE @.listSkills nvarchar(max)

SELECT @.listSkills= COALESCE(@.listSkills+',','')+'['+ #Temp.Skill+']'

FROM #Temp

SELECT @.listSkills

--Drop the tables

DROP TABLE #Temp,#Lkp_SkillCategory,#Tbl_Request

|||

or you can try this one

Code Snippet

CREATE TABLE #Tbl_Request
(
ID INT,
SkillCategoryID INT
)
GO

CREATE TABLE #Lkp_SkillCategory
(
ID INT,
Skill varchar(50)
)
GO

INSERT INTO #Tbl_Request VALUES(1,0)
INSERT INTO #Tbl_Request VALUES(2,1)
INSERT INTO #Tbl_Request VALUES(3,2)
INSERT INTO #Tbl_Request VALUES(4,0)
INSERT INTO #Tbl_Request VALUES(5,2)
INSERT INTO #Tbl_Request VALUES(6,2)
INSERT INTO #Tbl_Request VALUES(7,1)
GO

INSERT INTO #Lkp_SkillCategory VALUES(0,'Java')
INSERT INTO #Lkp_SkillCategory VALUES(1,'MySQL')
INSERT INTO #Lkp_SkillCategory VALUES(2,'.Net')
GO

DECLARE @.listSkills nvarchar(max)

select @.listSkills = COALESCE(@.listSkills+',','') + '[' + Skill + '] '
FROM
(
SELECT distinct Skill
FROM #Tbl_Request INNER JOIN
#Lkp_SkillCategory ON #Tbl_Request.SkillCategoryID = #Lkp_SkillCategory.ID
) tbl

SET @.listSkills=(SELECT Skill = @.listSkills)
SELECT @.listSkills

DROP TABLE #Lkp_SkillCategory,#Tbl_Request

Thanks,

R@.j

|||

Code Snippet

Declare @.OutputString varchar(max)

select @.OutputString = IsNull(@.OutputString + ', ' + sValue , sValue)

from (

select distinct '[' + Skill + ']' as [sValue]

from [MyTable]

) as [SubQuery]

select @.OutputString

|||Thank you both rusag2 and Raj for the answer. Works perfectly fine.

Help in Chosing the Software for Reporting Services

Hi All,

I have downloaded SQL Server 2005 Express Edition with Advanced Services SP2 from the following link

http://msdn.microsoft.com/vstudio/express/sql/download/

This I did after hearing Advanced Services provide Reporting Services tool with basic functionalities. After installion I am not able to find the SQL Server Business Intelligence Development Studio. Also there is no provision for installing the Reporting Services.

I already have Visual Studio Express installed.

Could someone tell me what else to download? Also it is asking for another software to be downloaded along with the existing one.

Regards

Karthik

In Visual Studio, when you try and create new projects, do you see a folder called "business intelligence"?|||

I am using Visual Web Developer 2005 Express Edition not Visual Studio. I don't have that option.

I was browsing through many other but couldn't understand clearly what is going wrong. Could you tell me the pre-requisites required for the installation?

|||http://www.microsoft.com/technet/prodtechnol/sql/2005/usingssrswithsqlexpress.mspx|||

Thanks for the link...

After lot of searching I figured out the option to install Reporting Services would not appear till IIS is installed. After doing this it is working fine now.

Could you please let me know if installation of SQL Server 2005 Express Edition with Advanced Services SP2 is possible in Vista Home Basic. Nowhere it is clearly explained if it can be done or not.

Help in choosing SQL Server edition

My company has a website that connects to a sql server (on a different box). I am trying to convince them to get sql server 2005. However, I do not know if SQL Server 2005 Workgroup edition is okay for our needs. Can someone please tell me if it is.

Basically, our setup is the following:

The SQL Server will only have one/two clients - the web server

The SQL Server will have a number of different user accounts though

The SQL Server will have its databases replicated to another SQL Server - both will be 2005

The SQL Server will not have more than 4Gb of RAM

The SQL Server will only have one processor - possible Xeon, and/or Dual Core

The database will not have complicated analysis done on them. The most that will be done are complicated selects which indexing should be able to handle.

We use crystal reports.

Thank you for any help.

Jagdip.

One other big consideration to make in your analysis is the volume of traffic you're going to have to the web server. If your web server is incredibly active and is going to be hammering the SQL server with requests, that will definitely affect your decision.

This page illustrates the differences between the editions:

http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx

Off the bat, it sounds like one feature that the Workgroup edition might lack that you need is Mirroring. Other than that, from what you described, I would think that the Workgroup edition would be fine for you. If you can find a way around the requirement for Mirroring (some other replication scheme--several exist), then you might be able to stick with the Workgroup rather than going with Standard.

Ryan

|||

Hi ya,

You have got a difficult choice. Well if you don't want to have that much and can replicate using your own logic then Sql Server express, the only limit for Database size which is a 4 GB. Workgroup would be better but you have got RAM limit of 3 GB although no limit on actual DB itself.

If you want to be cost effective then use SQL Express, if you can okay with small cost then workgroup

You can read the whole comparision about it from thislink

Hope that helps

Cheers

|||

Thank you both for your replies.

SQL Express is not an option as some of the databases are already over 4Gb.

The volume of traffic is not going to be great. If things go the way they are, we are looking at about 10 users per hour !!!

Thank you for the links as well. I had read these and was still unsure. Database Mirroring sounds good - but we are implementing a failover solution on the web server instead. If the primary web server/database fails over, then the secondaries will become active (using either Neverfail or Doubletake software). We are planning on using merge replication, so this should not be a problem.

The one thing I am worried about is the number of clients that can access the sql server simultaneously. Will the sql server restrict the number of users logged at once?

|||

I can't find anything anywhere about a limit on concurrent users for Workgroup Edition. If any of you SQL guys out there know of one, please chime in. It sounds like you're looking at VERY low usage though, so you should be fine. Were it not for your other requirements, SQL Express would be the perfect solution here. It sounds like you have a good plan in place and like you've got a good idea of which product is the right one for you. The nice thing about SQL is that it's easy to upgrade down the road as well, so if your requirements change in the future you should remain flexible.

|||

Hi ya,

If sql express is out of the question still workgroup would be more then enough as there is no hard core limit on concurrent no of users that can attach to a Sql Server. However it does depend on the actual machine performance which you already explained would be having 2-3 GB.

Hope that helps

Cheers

|||

Brilliant.

I just found out that we do not need to spend ￡300 for a server license of visual sourcesafe - so the money will hopefully go towards a new sql server.

|||Awesome. That worked out great. I'm glad you were able to find the right product for your needs.

Help in Case statement

Hi,

I have to use case statement in where condition of a sql query. I get an error

Incorrect syntax near '<'.

Declare @.iPostingID int

set @.iPostingId = 1

Select PostingID from JobsDB_JobPostings where DateDiff(day,PostingDate,getdate())

case @.iPostingId

when 1 then '<1'

when 2 then '<7'

when 3 then '<30'

when 4 then '<60'

when 5 then '<90'

end

Can anyone help me please

Thanks,

Uma Ramiya

Hi,

Maybe you want this:

Code Snippet

SELECT

CASE PostingID

WHEN 1 THEN '<1'

WHEN 2 THEN '<7'

WHEN 3 THEN '<30'

WHEN 4 THEN '<60'

WHEN 5 THEN '<90'

END

FROM JobsDB_JobPostings WHERE DATEDIFF(DAY,PostingDate,GETDATE())

Regards,

Janos

|||The parameter @.iPostingId is used for comparison and not the column field.|||Well, you need the select before the CASE|||Hi,

We use similar construct in our daily life here. Have a look on following query, this will really work.

DECLARE @.iPostingID int

SET @.iPostingId = 1

SELECT
PostingID
FROM
JobsDB_JobPostings
WHERE
DateDiff(day,PostingDate,getdate()) < (CASE
WHEN @.iPostingId = 1 THEN 1
WHEN @.iPostingId = 2 THEN 7
WHEN @.iPostingId = 3 THEN 30
WHEN @.iPostingId = 4 THEN 60
WHEN @.iPostingId = 5 THEN 90
END)|||

Hi I have the following case.i would like a precise and efficient SQL Statement for it.

I have 7 parameters which are used for comparison. I need to run the query with these parameters. which ever is not null should be compared and the null ones should be ignored. AS a result I end with lots of "if' statements as the parameters form a lot of copmbination. Is there any way to cut short the code

ALTER PROCEDURE [dbo].[JobsDb_Resumes_SelectForMatchingSkills]

@.sSkill varchar(50),

@.iCountryID int=NULL,

@.iStateID int=NULL,

@.iJobTypeID int=NULL,

@.iMinSal int=NULL,

@.iMaxSal int NULL,

@.iPeriodPosted int NULL

AS

IF @.iCountryID!=NULL AND @.iStateID=NULL AND @.iJobTypeID = NULL and @.iMinSal = NULL and @.iMaxSal = Null and iPeriodPosted = NULL

SELECT * FROM [dbo].[JobsDb_Resumes]

where targetcountryid=@.iCountryID and

resumetext like ('%' + @.sSkill + '%')

ORDER BY [postdate] DESC

IF @.iCountryID!=NULL AND @.iStateID!=NULL AND @.iJobTypeID = NULL and @.iMinSal = NULL and @.iMaxSal = Null and iPeriodPosted = NULL

SELECT * FROM [dbo].[JobsDb_Resumes]

where targetcountryid=@.iCountryID AND

targetstateid=@.iStateID and

resumetext like ('%' + @.sSkill + '%')

ORDER BY [postdate] DESC

IF @.iCountryID!=NULL AND @.iStateID!=NULL AND @.iJobTypeID != NULL and @.iMinSal = NULL and @.iMaxSal = Null and iPeriodPosted = NULL

SELECT * FROM [dbo].[JobsDb_Resumes]

where targetcountryid=@.iCountryID AND

targetstateid=@.iStateID and

jobtypeid=@.iJobTypeID and

resumetext like ('%' + @.sSkill + '%')

ORDER BY [postdate] DESC

IF @.iCountryID!=NULL AND @.iStateID!=NULL AND @.iJobTypeID != NULL and @.iMinSal != NULL and @.iMaxSal = Null and iPeriodPosted = NULL

SELECT * FROM [dbo].[JobsDb_Resumes]

where targetcountryid=@.iCountryID AND

targetstateid=@.iStateID and

jobtypeid=@.iJobTypeID and

minsalary >= @.iminsal and

resumetext like ('%' + @.sSkill + '%')

ORDER BY [postdate] DESC

IF @.iCountryID!=NULL AND @.iStateID!=NULL AND @.iJobTypeID != NULL and @.iMinSal != NULL and @.iMaxSal != Null and iPeriodPosted = NULL

SELECT * FROM [dbo].[JobsDb_Resumes]

where targetcountryid=@.iCountryID AND

targetstateid=@.iStateID and

jobtypeid=@.iJobTypeID and

minsalary >= @.iminsal and

maxsalary >= @.imaxsal and

resumetext like ('%' + @.sSkill + '%')

ORDER BY [postdate] DESC

IF @.iCountryID!=NULL AND @.iStateID!=NULL AND @.iJobTypeID != NULL and @.iMinSal != NULL and @.iMaxSal != Null and iPeriodPosted = NULL

SELECT * FROM [dbo].[JobsDb_Resumes]

where targetcountryid=@.iCountryID AND

targetstateid=@.iStateID and

jobtypeid=@.iJobTypeID and

minsalary >= @.iminsal and

maxsalary >= @.imaxsal and

resumetext like ('%' + @.sSkill + '%')

ORDER BY [postdate] DESC

IF @.iCountryID!=NULL AND @.iStateID!=NULL AND @.iJobTypeID != NULL and @.iMinSal != NULL and @.iMaxSal != Null and iPeriodPosted != NULL

SELECT * FROM [dbo].[JobsDb_Resumes]

where targetcountryid=@.iCountryID AND

targetstateid=@.iStateID and

jobtypeid=@.iJobTypeID and

minsalary >= @.iminsal and

maxsalary >= @.imaxsal and

resumetext like ('%' + @.sSkill + '%')

ORDER BY [postdate] DESC

Help in CASE function

Thanks Rudy for the answer of my previous thread.
Here's another scenario of my problem that i hardly get the correct query.

I have a tables with fields and sample records in my database;

Table: tblPo
FIELDS RECORD1
po_number: PO10001
entered_by: chris
approved_by: albert

Table: tblUser
FIELDS RECORD1 RECORD2
user_id: chris albert
first_name: Christopher Alberto
mi: S W
last_name: Lambert Del Puerto

So this is how my database is structured. entered_by and approved_by fields in tblPo are both existed in the tblUser table as user_id so they would share the same table tblUser for First, Middle,Last Names since they both also must be a valid user to create and approve a PO.

Now, I need to get a result like this:

PO # Prepared by Approved by
PO10001 Christopher S. Lambert Alberto W. Del Puerto

My problem now is how to make a query to generate this report. I already joined the two tables with tblPo.user_id to tblUser.user_id and tblPo.approved_by to tblUser.user_id. Unfortunately, both Preparedby and Approvedby resulted to NULL values.

Any help? thanks in advance

BernieHere you go :

use pubs
go
create table tblPO (po_number varchar(100),entered_by varchar(100),approved_by varchar(100))
go
create table tblUser (user_id varchar(100),first_name varchar(100),mi varchar(100),last_name varchar(100))
go
insert into tblPO values('PO10001','chris','albert')
insert into tbluser values ('chris','Christopher','S','Lambert')
insert into tbluser values ('albert','Alberto','W','Del Puerto')
go
select
a.po_number,
isnull(b.first_name,'') +' '+ isnull(b.mi,'') +'. '+ isnull(b.last_name,'') Prepared_by,
isnull(c.first_name,'') +' '+ isnull(c.mi,'') +'. ' + isnull(c.last_name,'') Approved_by
from
tblPO a,
tbluser b,
tbluser c
where
a.entered_by = b.user_id
and a.approved_by = c.user_id

go
drop table tbluser
go
drop table tblpo
go|||create table tblpo (po_number char(10), entered_by char(10), approved_by char(10))
create table tblUser (user_id char(10), first_name char(30), mi char(30), last_name char(30))
insert into tblpo values ('PO10001','chris','albert')
insert into tbluser values ('chris','Christopher','S','Lambert')
insert into tbluser values ('albert','Alberto','W','Del Puerto')

select
tblpo.po_number as po#,
rtrim(EnteredBy.first_name)+' '+rtrim(EnteredBy.mi)+'. '+rtrim(EnteredBy.last_name) as PreparedBy,
rtrim(ApprovedBy.first_name)+' '+rtrim(ApprovedBy.mi)+'. '+rtrim(ApprovedBy.last_name) as ApprovedBy
from tblpo
left outer join tbluser as EnteredBy
on EnteredBy.user_id = tblpo.entered_by
left outer join tbluser as ApprovedBy
on ApprovedBy.user_id = tblpo.approved_by

po# PreparedBy ApprovedBy
---- -------- -------
PO10001 Christopher S. Lambert Alberto W. Del Puerto

(1 row(s) affected)|||Originally posted by cvandemaele
create table tblpo (po_number char(10), entered_by char(10), approved_by char(10))
create table tblUser (user_id char(10), first_name char(30), mi char(30), last_name char(30))
insert into tblpo values ('PO10001','chris','albert')
insert into tbluser values ('chris','Christopher','S','Lambert')
insert into tbluser values ('albert','Alberto','W','Del Puerto')

select
tblpo.po_number as po#,
rtrim(EnteredBy.first_name)+' '+rtrim(EnteredBy.mi)+'. '+rtrim(EnteredBy.last_name) as PreparedBy,
rtrim(ApprovedBy.first_name)+' '+rtrim(ApprovedBy.mi)+'. '+rtrim(ApprovedBy.last_name) as ApprovedBy
from tblpo
left outer join tbluser as EnteredBy
on EnteredBy.user_id = tblpo.entered_by
left outer join tbluser as ApprovedBy
on ApprovedBy.user_id = tblpo.approved_by

po# PreparedBy ApprovedBy
---- -------- -------
PO10001 Christopher S. Lambert Alberto W. Del Puerto

(1 row(s) affected)

Thats better .. that would work even if userid does not exist in tblUser ... but remember to take care of null values .. coz null added to anything yields null|||SALAMAT! Thank you guyz...

I can proceed now with my report...

Berniesql

help in bulk load - first time user

Hi all,
I've a xml file like this:
<ROOT>
<customerlist>
<Customers sequence="1">
<details>
<PersonID>1</PersonID>
</details>
<name>
<LastName>Vinod</LastName>
<FirstName>Kumar</FirstName>
</name>
</Customers>
<Customers sequence="2">
<details>
<PersonID>2</PersonID>
</details>
<name>
<LastName>Saravana</LastName>
<FirstName>Kumar</FirstName>
</name>
</Customers>
</customerlist>
</ROOT>
how should I write an xsd file to store the data in the following sql server table (Person) with columns:
personId - primary key, LastName and FirstName.
Thanks.
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.
"SqlJunkies User" <User@.-NOSPAM-SqlJunkies.com> wrote in message
news:exDw0UcdEHA.320@.TK2MSFTNGP10.phx.gbl...
[snip]
> how should I write an xsd file to store the data in the following sql
> server table (Person) with columns:
I would start with the examples given in the SQLXML documentation. Look at
how the XSD is mapped to the XML. That is the best way to learn.
Bryant

HELP in "Where" clause

Hi,
I would like to pass a stream of varchar as my "where" clause as follow.
Select * from test where + @.Filters
I can't get this work until I declare the SQL as varchar and then execute th
e query as follow:
DECLARE @.SQL VARCHAR(2000)
SET @.SQL = 'Select * from test where ' + @.Filters
EXEC (@.SQL)
Is there a way that I can implement the SP in my first example?Hi,
You can do like this;
declare @.filters varchar(30)
set @.filters='1 =1'
exec('Select * from test where '+ @.Filters)
You can change the where clause based on ur requirement.
Thanks
Hari
MCDBA
"Abi" <anonymous@.discussions.microsoft.com> wrote in message
news:4E5BACA6-9C0B-4643-A3DD-C65375830D37@.microsoft.com...
> Hi,
> I would like to pass a stream of varchar as my "where" clause as follow.
> Select * from test where + @.Filters
> I can't get this work until I declare the SQL as varchar and then execute
the query as follow:
> DECLARE @.SQL VARCHAR(2000)
> SET @.SQL = 'Select * from test where ' + @.Filters
> EXEC (@.SQL)
> Is there a way that I can implement the SP in my first example?|||No, you need dynamic SQL for that. But please consider below first:
http://www.sommarskog.se/dynamic_sql.html
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Abi" <anonymous@.discussions.microsoft.com> wrote in message
news:4E5BACA6-9C0B-4643-A3DD-C65375830D37@.microsoft.com...
> Hi,
> I would like to pass a stream of varchar as my "where" clause as follow.
> Select * from test where + @.Filters
> I can't get this work until I declare the SQL as varchar and then execute
the query as follow:
> DECLARE @.SQL VARCHAR(2000)
> SET @.SQL = 'Select * from test where ' + @.Filters
> EXEC (@.SQL)
> Is there a way that I can implement the SP in my first example?

Help in "Limit"

we all know
mysql: select * from table limit ?1, ?2

equals

sqlserver: SELECT TOP ?2 *
FROM table WHERE (IDENTITYCOL NOT IN
(SELECT TOP ?1 IDENTITYCOL
FROM table order by IDENTITYCOL))
order by IDENTITYCOL

but the below SQL in mysql,how to convert?I enmesh.........
select pageid,pagename,pageaddr,pageauditflag,pageartaudi tflag,startplaytime
from pageinfo where entryid= ?1 and startplaytime= ?2
limit ?3, ?4

thanks!do it the same way -- pretend ?1 and ?2 don't exist, put ?3 where ?1 is and ?4 where ?2 is in the TOP query|||do it the same way -- pretend ?1 and ?2 don't exist, put ?3 where ?1 is and ?4 where ?2 is in the TOP query
you mean if the query is:
select * from AselectPageInfo_view where entryid=1 and startplaytime='2006-03-08' limit 0,1
I can convert it to below:
SELECT TOP 1 * FROM (select * from AselectPageInfo_view where
entryid=1 and startplaytime='2006-03-08' ) WHERE (pageid NOT IN (SELECT
TOP 0 pageid FROM (select * from AselectPageInfo_view where entryid='1'
and startplaytime='2006-03-08' ) order by pageid)) order by pageid

right?
but it can't run!
what's wrong?
Please help!thanks!|||oh,May be this is right!
SELECT TOP 1 * FROM AselectPageInfo_view WHERE
(pageid NOT IN (SELECT TOP 0 pageid FROM
AselectPageInfo_view where
entryid='1' and startplaytime='2006-03-08' order by pageid))
and (entryid='1' and startplaytime='2006-03-08') order by pageid

Expert,Please check it,is it right?
Thank you from bottom of my heart!|||SELECT TOP 0 is not going to return anything, is it?

:)|||no，it return result( one line )
expert, the query that i write in #4 is right?|||i mean
select * from AselectPageInfo_view where entryid=1 and
startplaytime='2006-03-08' limit 0,1
whether equals
SELECT TOP 1 * FROM AselectPageInfo_view WHERE
(pageid NOT IN (SELECT TOP 0 pageid FROM
AselectPageInfo_view where
entryid='1' and startplaytime='2006-03-08' order by pageid))
and (entryid='1' and startplaytime='2006-03-08') order by pageid|||please tell me how many rows you get for this --

SELECT TOP 0 pageid FROM
AselectPageInfo_view where
entryid='1' and startplaytime='2006-03-08' order by pageid|||oh,it's zero|||do you know why?|||i change the 0 to 1
that can get one row
the 0 is reason?|||hello,expert,why we need know the result? 1 and 0 we can change to another parameter,
i just want to know the two query whether equals?|||no, they are not the same|||oh,expert,can you tell me which query can equals
select * from AselectPageInfo_view where entryid=1 and
startplaytime='2006-03-08' limit 0,1
because the limit can't be used in mssql|||that query just gets 1 row -- and since there's no ORDER BY, it could be any row

so the equivalent in microsoft sql server isselect top 1 * from AselectPageInfo_view where entryid=1 and
startplaytime='2006-03-08'|||and please stop calling me "expert"

i have a name|||ok,I know,Rudy Limeback :)|||that query just gets 1 row -- and since there's no ORDER BY, it could be any row

so the equivalent in microsoft sql server isselect top 1 * from AselectPageInfo_view where entryid=1 and
startplaytime='2006-03-08'

well,if i change the reference variables, your query is not right
i mean if the query like this:

select * from AselectPageInfo_view where entryid=1 and
startplaytime='2006-03-08' limit 5,10
depend on your opinion,I should write the query like below,right?
select top 10 * from AselectPageInfo_view where entryid=1 and
startplaytime='2006-03-08'|||well,if i change the reference variables, your query is not right i did not say you could change my query and still get similar results

all you asked was the equivalence to LIMIT 1, and i gave you that equivalence

if you want the equivalent to LIMIT 5,10, you need to first ask yourself what is the ORDER BY that determines the sequence of rows?|||i did not say you could change my query and still get similar results

all you asked was the equivalence to LIMIT 1, and i gave you that equivalence

if you want the equivalent to LIMIT 5,10, you need to first ask yourself what is the ORDER BY that determines the sequence of rows?
of course,it ispageid|||please show your entire query, i do not see pageid anywhere in there|||ok,i want to change a SQL which in mysql to mssql

select pageinfo.pageid,pageinfo.pagename,pageinfo.pageadd r,
pageinfo.pageauditflag,pageinfo.pageartauditflag,p ageinfo.startplaytime
from pageinfo where pageinfo.entryid=?1 and pageinfo.startplaytime=?2
limit ?3,?4"

first, i creat a view

CREATE VIEW dbo.AselectPageInfo_view
AS
SELECT pageid, pagename, pageaddr, pageauditflag, pageartauditflag,
startplaytime, entryid
FROM appsdb.pageinfo

second, i need change the query

select * from AselectPageInfo_view where entryid=?1 and
startplaytime=?2 limit ?3,?4

but i don't know whether the query which i changed is right?|||thank you for your kindly help:)|||i am going to say this only one more time

where is your ORDER BY clause?

without an ORDER BY clause, nothing you do will turn out correctly|||Rydy, I applaud your patience!|||Rydy, I applaud your patience!
My bad! .. Rudy|||thanks for the kind words, GDMI

i do like to give people lots of opportunity to catch up, eh

;)|||oh，I know
thank you！|||<<< quoted text and followup removed >>>

and rudy, you are my favorite expert.|||thanks, sean, this thread has been quite a challenge for me, and i'm not completely happy with my performance (you can see in a couple of places where my exasperation shows through)

it's nice to get positive feedback :)|||Rudy - you are my favourite Database Guru of the Year (2005) too :)|||Rudy - you are my favourite Database Guru of the Year (2005) too :)

he's been my favorite since a long time... :D

Help improving this script

Hello and thank you for taking the time to read this.
Can anyone provide any recomendations for improving this script. It is
fairly straight forward, and it works. I am looking for tips the experts
would use.
Also there are two things I would like to do that this script currently does
not
1. Print the print statement after each section is finished
2. The last Drop Table action. I do not want to drop the table unless I know
the previous Insert Into was successfull.
thank you very much
USE ADV_Goldmine
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[CONTACTDel]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[CONTACTDel]
GO
PRINT 'Create Table CONTACTDel to replace CONTACT1DEL'
CREATE TABLE [dbo].[CONTACTDel] (
[ACCOUNTNO] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[RECTYPE] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CONTSUPREF] [varchar] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[COMPANY] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CONTACT] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LASTNAME] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DEPARTMENT] [varchar] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TITLE] [varchar] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SECR] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PHONE1] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PHONE2] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PHONE3] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FAX] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[EXT1] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[EXT2] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[EXT3] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[EXT4] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ADDRESS1] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ADDRESS2] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ADDRESS3] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CITY] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[STATE] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ZIP] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[COUNTRY] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DEAR] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SOURCE] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[KEY1] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[KEY2] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[KEY3] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[KEY4] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[KEY5] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[STATUS] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MERGECODES] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CREATEBY] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CREATEON] [datetime] NULL ,
[CREATEAT] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[OWNER] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LASTUSER] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LASTDATE] [datetime] NULL ,
[LASTTIME] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RECID] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
CREATE INDEX [CONTACCT] ON [dbo].[CONTACTDel]([ACCOUNTNO], [RECID]) WITH
FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [CREC] ON [dbo].[CONTACTDel]([RECID]) WITH FILLFACTOR = 90
ON [PRIMARY]
GO
PRINT 'Drop if EXISTS and create Trigger Supp_contact_delete on CONTSUPP'
IF EXISTS (SELECT name from sysobjects
where name ='Supp_contact_delete' AND type = 'TR')
DROP TRIGGER Supp_contact_delete
GO
CREATE TRIGGER Supp_contact_delete ON Contsupp
FOR DELETE
AS
SET NOCOUNT ON
INSERT INTO ContactDEL
(accountno, rectype, contact, title, contsupref, dear, phone1, ext1, fax,
address1, address2, address3, city, state, zip, country, mergecodes,
lastdate, lasttime, recid, company,key5)
SELECT deleted.accountno, deleted.rectype, deleted.contact, deleted.title,
deleted.contsupref, deleted.dear, deleted.phone , deleted.ext,
deleted.fax, deleted.address1,
deleted.address2, deleted.address3, deleted.city, deleted.state,
deleted.zip, deleted.country, deleted.mergecodes, GETDATE(),
deleted.lasttime, deleted.recid, contact1.company, contact1.key5
FROM deleted
INNER join contact1(NOLOCK)
ON contact1.accountno = deleted.accountno
WHERE deleted.rectype='C' AND deleted.accountno > ''
INSERT INTO ContactDEL
(accountno, rectype, contact, title, contsupref, dear, phone1, ext1, fax,
address1, address2, address3, city, state, zip, country, mergecodes,
lastdate, lasttime, recid, company,key5)
SELECT deleted.accountno, deleted.rectype, deleted.contact, deleted.title,
deleted.contsupref, deleted.dear, deleted.phone , deleted.ext,
deleted.fax, deleted.address1,
deleted.address2, deleted.address3, deleted.city, deleted.state,
deleted.zip, deleted.country, deleted.mergecodes, GETDATE(),
deleted.lasttime, deleted.recid, Contactdel.company, Contactdel.key5
FROM deleted
INNER join contactdel(NOLOCK) ON contactdel.accountno = deleted.accountno
left join contact1(NOLOCK) /*only the ones not in contact1*/
ON contact1.accountno = deleted.accountno
WHERE contactdel.rectype IS NULL and deleted.rectype='C'
AND contact1.accountno IS NULL /*not in Contact1*/
GO
PRINT 'Drop old Trigger on CONTACT1 DeleteTrigger'
IF EXISTS (SELECT name from sysobjects
where name ='DeleteTrigger' AND type = 'TR')
DROP TRIGGER DeleteTrigger
GO
PRINT 'Create Trigger C1Delete on CONTACT1'
IF EXISTS (SELECT name from sysobjects
where name ='C1Delete' AND type = 'TR')
DROP TRIGGER C1Delete
GO
CREATE TRIGGER C1Delete ON Contact1
FOR DELETE
AS
insert into ContactDEL (ACCOUNTNO, COMPANY, CONTACT, LASTNAME, DEPARTMENT,
TITLE, SECR, PHONE1, PHONE2, PHONE3, FAX, EXT1, EXT2, EXT3, EXT4, ADDRESS1,
ADDRESS2, ADDRESS3, CITY, STATE, ZIP, COUNTRY, DEAR, SOURCE, KEY1, KEY2,
KEY3, KEY4, KEY5, STATUS, MERGECODES, CREATEBY,
CREATEON, CREATEAT, OWNER, LASTUSER, LASTDATE, LASTTIME, RECID)
select ACCOUNTNO, COMPANY, CONTACT, LASTNAME, DEPARTMENT, TITLE, SECR,
PHONE1, PHONE2, PHONE3, FAX, EXT1, EXT2, EXT3, EXT4, ADDRESS1,
ADDRESS2, ADDRESS3, CITY, STATE, ZIP, COUNTRY, DEAR, SOURCE, KEY1, KEY2,
KEY3, KEY4, KEY5, STATUS, MERGECODES, CREATEBY,
CREATEON, CREATEAT, OWNER, LASTUSER, GETDATE(), LASTTIME, RECID
from deleted
GO
PRINT 'Move data from CONTACT1DEL to CONTACTDEL'
INSERT INTO ContactDel
(ACCOUNTNO, COMPANY, CONTACT, LASTNAME, DEPARTMENT, TITLE, SECR, PHONE1,
PHONE2, PHONE3, FAX, EXT1, EXT2, EXT3, EXT4, ADDRESS1,
ADDRESS2, ADDRESS3, CITY, STATE, ZIP, COUNTRY, DEAR, SOURCE, KEY1, KEY2,
KEY3, KEY4, KEY5, STATUS, MERGECODES, CREATEBY,
CREATEON, CREATEAT, OWNER, LASTUSER, LASTDATE, LASTTIME, RECID)
select [CONTACT1Del].[ACCOUNTNO],[CONTACT1Del].[COMPANY],
[CONTACT1Del].[CONTACT], [CONTACT1Del].[LASTNAME],
[CONTACT1Del].[DEPARTMENT], [CONTACT1Del].[TITLE], [CONTACT1Del].[SECR],
[CONTACT1Del].[PHONE1], [CONTACT1Del].[PHONE2], [CONTACT1Del].[PHONE3],
[CONTACT1Del].[FAX],
[CONTACT1Del].[EXT1], [CONTACT1Del].[EXT2], [CONTACT1Del].[EXT3],
[CONTACT1Del].[EXT4],
[CONTACT1Del].[ADDRESS1], [CONTACT1Del].[ADDRESS2], [CONTACT1Del].[ADDRESS3]
,
[CONTACT1Del].[CITY], [CONTACT1Del].[STATE], [CONTACT1Del].[ZIP],
[CONTACT1Del].[COUNTRY],
[CONTACT1Del].[DEAR], [CONTACT1Del].[SOURCE], [CONTACT1Del].[KEY1],
[CONTACT1Del].[KEY2],
[CONTACT1Del].[KEY3], [CONTACT1Del].[KEY4], [CONTACT1Del].[KEY5],
[CONTACT1Del].[STATUS],
[CONTACT1Del].[MERGECODES], [CONTACT1Del].[CREATEBY],
[CONTACT1Del].[CREATEON],
[CONTACT1Del].[CREATEAT], [CONTACT1Del].[OWNER], [CONTACT1Del].[LASTUSER],
[CONTACT1Del].[LASTDATE], [CONTACT1Del].[LASTTIME], [CONTACT1Del].[RECID]
from [CONTACT1Del]
where [CONTACT1Del].[ACCOUNTNO]>''
GO
PRINT 'Drop Contact1del table'
DROP TABLE Contact1delTry:

> 1. Print the print statement after each section is finished
use raiserror instead print.
Example:
raiserror('put the msg here.', 10, 1) with nowait
go

> 2. The last Drop Table action. I do not want to drop the table unless I kn
ow
> the previous Insert Into was successfull.
declare @.error int, @.rowcnt int
INSERT INTO ContactDel
...
select @.error = @.@.error, @.rowcnt = @.@.rowcount
if @.rowcnt > 1 and @.error = 0
begin
PRINT 'Drop Contact1del table'
DROP TABLE Contact1del
end
go
AMB
"jenks" wrote:

> Hello and thank you for taking the time to read this.
> Can anyone provide any recomendations for improving this script. It is
> fairly straight forward, and it works. I am looking for tips the experts
> would use.
> Also there are two things I would like to do that this script currently do
es
> not
> 1. Print the print statement after each section is finished
> 2. The last Drop Table action. I do not want to drop the table unless I kn
ow
> the previous Insert Into was successfull.
> thank you very much
> USE ADV_Goldmine
> GO
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[CONTACTDel]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table [dbo].[CONTACTDel]
> GO
> PRINT 'Create Table CONTACTDel to replace CONTACT1DEL'
> CREATE TABLE [dbo].[CONTACTDel] (
> [ACCOUNTNO] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [RECTYPE] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [CONTSUPREF] [varchar] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [COMPANY] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [CONTACT] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [LASTNAME] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [DEPARTMENT] [varchar] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [TITLE] [varchar] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [SECR] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [PHONE1] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [PHONE2] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [PHONE3] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [FAX] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [EXT1] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [EXT2] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [EXT3] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [EXT4] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [ADDRESS1] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [ADDRESS2] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [ADDRESS3] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [CITY] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [STATE] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [ZIP] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [COUNTRY] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [DEAR] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [SOURCE] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [KEY1] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [KEY2] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [KEY3] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [KEY4] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [KEY5] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [STATUS] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [MERGECODES] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [CREATEBY] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [CREATEON] [datetime] NULL ,
> [CREATEAT] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [OWNER] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [LASTUSER] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [LASTDATE] [datetime] NULL ,
> [LASTTIME] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [RECID] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE INDEX [CONTACCT] ON [dbo].[CONTACTDel]([ACCOUNTNO], [RECID]) WITH
> FILLFACTOR = 90 ON [PRIMARY]
> GO
> CREATE INDEX [CREC] ON [dbo].[CONTACTDel]([RECID]) WITH FILLFACTOR = 90
> ON [PRIMARY]
> GO
>
> PRINT 'Drop if EXISTS and create Trigger Supp_contact_delete on CONTSUPP'
> IF EXISTS (SELECT name from sysobjects
> where name ='Supp_contact_delete' AND type = 'TR')
> DROP TRIGGER Supp_contact_delete
> GO
> CREATE TRIGGER Supp_contact_delete ON Contsupp
> FOR DELETE
> AS
> SET NOCOUNT ON
> INSERT INTO ContactDEL
> (accountno, rectype, contact, title, contsupref, dear, phone1, ext1, fax,
> address1, address2, address3, city, state, zip, country, mergecodes,
> lastdate, lasttime, recid, company,key5)
> SELECT deleted.accountno, deleted.rectype, deleted.contact, deleted.title,
> deleted.contsupref, deleted.dear, deleted.phone , deleted.ext,
> deleted.fax, deleted.address1,
> deleted.address2, deleted.address3, deleted.city, deleted.state,
> deleted.zip, deleted.country, deleted.mergecodes, GETDATE(),
> deleted.lasttime, deleted.recid, contact1.company, contact1.key5
> FROM deleted
> INNER join contact1(NOLOCK)
> ON contact1.accountno = deleted.accountno
> WHERE deleted.rectype='C' AND deleted.accountno > ''
> INSERT INTO ContactDEL
> (accountno, rectype, contact, title, contsupref, dear, phone1, ext1, fax,
> address1, address2, address3, city, state, zip, country, mergecodes,
> lastdate, lasttime, recid, company,key5)
> SELECT deleted.accountno, deleted.rectype, deleted.contact, deleted.title,
> deleted.contsupref, deleted.dear, deleted.phone , deleted.ext,
> deleted.fax, deleted.address1,
> deleted.address2, deleted.address3, deleted.city, deleted.state,
> deleted.zip, deleted.country, deleted.mergecodes, GETDATE(),
> deleted.lasttime, deleted.recid, Contactdel.company, Contactdel.key5
> FROM deleted
> INNER join contactdel(NOLOCK) ON contactdel.accountno = deleted.accountno
> left join contact1(NOLOCK) /*only the ones not in contact1*/
> ON contact1.accountno = deleted.accountno
> WHERE contactdel.rectype IS NULL and deleted.rectype='C'
> AND contact1.accountno IS NULL /*not in Contact1*/
>
> GO
> PRINT 'Drop old Trigger on CONTACT1 DeleteTrigger'
> IF EXISTS (SELECT name from sysobjects
> where name ='DeleteTrigger' AND type = 'TR')
> DROP TRIGGER DeleteTrigger
> GO
> PRINT 'Create Trigger C1Delete on CONTACT1'
> IF EXISTS (SELECT name from sysobjects
> where name ='C1Delete' AND type = 'TR')
> DROP TRIGGER C1Delete
> GO
> CREATE TRIGGER C1Delete ON Contact1
> FOR DELETE
> AS
>
> insert into ContactDEL (ACCOUNTNO, COMPANY, CONTACT, LASTNAME, DEPARTMENT,
> TITLE, SECR, PHONE1, PHONE2, PHONE3, FAX, EXT1, EXT2, EXT3, EXT4, ADDRESS1
,
> ADDRESS2, ADDRESS3, CITY, STATE, ZIP, COUNTRY, DEAR, SOURCE, KEY1, KEY2,
> KEY3, KEY4, KEY5, STATUS, MERGECODES, CREATEBY,
> CREATEON, CREATEAT, OWNER, LASTUSER, LASTDATE, LASTTIME, RECID)
> select ACCOUNTNO, COMPANY, CONTACT, LASTNAME, DEPARTMENT, TITLE, SECR,
> PHONE1, PHONE2, PHONE3, FAX, EXT1, EXT2, EXT3, EXT4, ADDRESS1,
> ADDRESS2, ADDRESS3, CITY, STATE, ZIP, COUNTRY, DEAR, SOURCE, KEY1, KEY2,
> KEY3, KEY4, KEY5, STATUS, MERGECODES, CREATEBY,
> CREATEON, CREATEAT, OWNER, LASTUSER, GETDATE(), LASTTIME, RECID
> from deleted
> GO
> PRINT 'Move data from CONTACT1DEL to CONTACTDEL'
> INSERT INTO ContactDel
> (ACCOUNTNO, COMPANY, CONTACT, LASTNAME, DEPARTMENT, TITLE, SECR, PHONE1,
> PHONE2, PHONE3, FAX, EXT1, EXT2, EXT3, EXT4, ADDRESS1,
> ADDRESS2, ADDRESS3, CITY, STATE, ZIP, COUNTRY, DEAR, SOURCE, KEY1, KEY2,
> KEY3, KEY4, KEY5, STATUS, MERGECODES, CREATEBY,
> CREATEON, CREATEAT, OWNER, LASTUSER, LASTDATE, LASTTIME, RECID)
> select [CONTACT1Del].[ACCOUNTNO],[CONTACT1Del].[COMPANY],
> [CONTACT1Del].[CONTACT], [CONTACT1Del].[LASTNAME],
> [CONTACT1Del].[DEPARTMENT], [CONTACT1Del].[TITLE], [CONTACT1Del].[SECR],
> [CONTACT1Del].[PHONE1], [CONTACT1Del].[PHONE2], [CONTACT1Del].[PHONE3],
> [CONTACT1Del].[FAX],
> [CONTACT1Del].[EXT1], [CONTACT1Del].[EXT2], [CONTACT1Del].[EXT3],
> [CONTACT1Del].[EXT4],
> [CONTACT1Del].[ADDRESS1], [CONTACT1Del].[ADDRESS2], [CONTACT1Del].[ADDRES
S3],
> [CONTACT1Del].[CITY], [CONTACT1Del].[STATE], [CONTACT1Del].[ZIP],
> [CONTACT1Del].[COUNTRY],
> [CONTACT1Del].[DEAR], [CONTACT1Del].[SOURCE], [CONTACT1Del].[KEY1],
> [CONTACT1Del].[KEY2],
> [CONTACT1Del].[KEY3], [CONTACT1Del].[KEY4], [CONTACT1Del].[KEY5],
> [CONTACT1Del].[STATUS],
> [CONTACT1Del].[MERGECODES], [CONTACT1Del].[CREATEBY],
> [CONTACT1Del].[CREATEON],
> [CONTACT1Del].[CREATEAT], [CONTACT1Del].[OWNER], [CONTACT1Del].[LASTUSER],
> [CONTACT1Del].[LASTDATE], [CONTACT1Del].[LASTTIME], [CONTACT1Del].[RECID]
> from [CONTACT1Del]
> where [CONTACT1Del].[ACCOUNTNO]>''
> GO
> PRINT 'Drop Contact1del table'
> DROP TABLE Contact1del
>
>

Help importing legacy FoxPro db into SQL 2000

Greetings,
Any help/tips appreciated. I'm charged with importing a legacy FoxPro
database appliction into SQL 2000 and building a new .NET front-end for it.
I've imported .mdb, Excel, text files, other MS SQL data, etc in the past,
but the FoxPro stuff is rather different. I see the .dbf files (which I'm
guessing will import as tables), but when I try to import, the DSN import bo
x
comes up. I try 'Visual FoxPro drivers' but they're not installed. A little
research shows MS stopped bundling FoxPro drivers in MDAC 2.6.
Does anyone have experience with this type of import?
Thanks in advance!
jgHi Johnny,
Download and install the FoxPro and Visual FoxPro OLE DB data provider from
msdn.microsoft.com/vfoxpro/downloads/updates.
When you set up your import you'll want to determine whether you have a
database container (a DBC file is present in the directory where the DBFs
are) or free tables. With a DBC you connect to the DBC and with free tables
you just connect to the directory they are in.
After that it's just like importing any other OLE DB compliant data.
Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
cindy_winegarden@.msn.com www.cindywinegarden.com
"johnnyG" <johnnyG@.discussions.microsoft.com> wrote in message
news:CBE76BA7-C433-4EDD-8C73-123690CAA92E@.microsoft.com...
> Greetings,
> Any help/tips appreciated. I'm charged with importing a legacy FoxPro
> database appliction into SQL 2000 and building a new .NET front-end for
> it.
> I've imported .mdb, Excel, text files, other MS SQL data, etc in the past,
> but the FoxPro stuff is rather different. I see the .dbf files (which I'm
> guessing will import as tables), but when I try to import, the DSN import
> box
> comes up. I try 'Visual FoxPro drivers' but they're not installed. A
> little
> research shows MS stopped bundling FoxPro drivers in MDAC 2.6.
> Does anyone have experience with this type of import?
> Thanks in advance!
> jg|||If the original appliation is pre-VFP, then it's probably not normalized, so
you'll want to re-think the database model as well. I've seen legacy -> SQL
Server ports where this was not done.
"Cindy Winegarden" <cindy_winegarden@.msn.com> wrote in message
news:eFWvPxZLGHA.2904@.TK2MSFTNGP10.phx.gbl...
> Hi Johnny,
> Download and install the FoxPro and Visual FoxPro OLE DB data provider
> from msdn.microsoft.com/vfoxpro/downloads/updates.
> When you set up your import you'll want to determine whether you have a
> database container (a DBC file is present in the directory where the DBFs
> are) or free tables. With a DBC you connect to the DBC and with free
> tables you just connect to the directory they are in.
> After that it's just like importing any other OLE DB compliant data.
> --
> Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
> cindy_winegarden@.msn.com www.cindywinegarden.com
>
> "johnnyG" <johnnyG@.discussions.microsoft.com> wrote in message
> news:CBE76BA7-C433-4EDD-8C73-123690CAA92E@.microsoft.com...
>|||Thanks guys...I downloaded the data provider and found the DBC and managed t
o
successfully import the tables. I'm reviewing the warnings now (some date
data type precision issues), but overall I'm underway. Thanks Cindy.
And JT thanks for the sound advice also...
johnnG
"johnnyG" wrote:

> Greetings,
> Any help/tips appreciated. I'm charged with importing a legacy FoxPro
> database appliction into SQL 2000 and building a new .NET front-end for it
.
> I've imported .mdb, Excel, text files, other MS SQL data, etc in the past,
> but the FoxPro stuff is rather different. I see the .dbf files (which I'm
> guessing will import as tables), but when I try to import, the DSN import
box
> comes up. I try 'Visual FoxPro drivers' but they're not installed. A litt
le
> research shows MS stopped bundling FoxPro drivers in MDAC 2.6.
> Does anyone have experience with this type of import?
> Thanks in advance!
> jg|||Hi Johnny,
In case you haven't figured it out yet, FoxPro Date data types (a date only,
no time) can be "empty" as well as null or having a valid date. SQL Server
doesn't know how to handle these and substitutes 12/31/1899 or 1/1/1900. The
same goes with DateTime data types.
Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
cindy_winegarden@.msn.com www.cindywinegarden.com
"johnnyG" <johnnyG@.discussions.microsoft.com> wrote in message
news:FFFBC100-6D09-41CC-BB40-0452495014D2@.microsoft.com...
> Thanks guys...I downloaded the data provider and found the DBC and managed
> to
> successfully import the tables. I'm reviewing the warnings now (some date
> data type precision issues), but overall I'm underway. Thanks Cindy.
> And JT thanks for the sound advice also...sql

help importing large flat file into relational tables

I am trying to find the best (fastest) way to import large text files into
sql server 2005 relational tables. The database has simple recovery model.
The total size of data to be load is about 1gb (each file)
source:
Multiple source files with different layout.
Destination:
3 sql server tables; a parent table with a ID (IDENTITY) primary key and a 3
column unique index (alternate key) and a detail table that has a DETAIL_ID
(IDENTITY) primary key and a foreign key ID to the parent table.
So, for each source file, I need to convert some columns to decimal and
separate the data into parent and detail tables.
Here is one way to do this:
For each source file
* use a thread for each file with Microsoft Visual Studio .NET 2003
* run insert query with SP's por each record, and join parent table with
details.
* process next record
My app works fine in XP and SQL express, When I run the queries the CPU
utilization is consitantly around 90%. This is my development environment.
My production enviroment is with SQL 2005 and Win 2003 Enterprise Edition Sp1.
When I run the queries the CPU utilization is consitantly around 3%.
I have 2 Questions:
1- Is there a better way to do this?
2- Why XP has utilization around 90%, and Win2003 3%?
Speed is the primary concern.
Thank you for any suggestions.
Macisu wrote:
> I am trying to find the best (fastest) way to import large text files into
> sql server 2005 relational tables. The database has simple recovery model.
> The total size of data to be load is about 1gb (each file)
> source:
> Multiple source files with different layout.
>
> Destination:
> 3 sql server tables; a parent table with a ID (IDENTITY) primary key and a 3
> column unique index (alternate key) and a detail table that has a DETAIL_ID
> (IDENTITY) primary key and a foreign key ID to the parent table.
>
> So, for each source file, I need to convert some columns to decimal and
> separate the data into parent and detail tables.
> Here is one way to do this:
> For each source file
> * use a thread for each file with Microsoft Visual Studio .NET 2003
> * run insert query with SP's por each record, and join parent table with
> details.
> * process next record
> My app works fine in XP and SQL express, When I run the queries the CPU
> utilization is consitantly around 90%. This is my development environment.
> My production enviroment is with SQL 2005 and Win 2003 Enterprise Edition Sp1.
> When I run the queries the CPU utilization is consitantly around 3%.
> I have 2 Questions:
> 1- Is there a better way to do this?
> 2- Why XP has utilization around 90%, and Win2003 3%?
>
> Speed is the primary concern.
> Thank you for any suggestions.
SQL Server Integration Services is the most obvious solution to try.
Read about Integration Services in Books Online. Depending on the
format of your files BCP may also be an option.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx

help importing large flat file into relational tables

I am trying to find the best (fastest) way to import large text files into sql server 2000 relational tables. The database has simple recovery model.

The total size of data to be load is about 1gb (each file)

source:

Multiple source files with different layout.

Destination:

3 sql server tables; a parent table with a ID (IDENTITY) primary key and a 3 column unique index (alternate key) and a detail table that has a DETAIL_ID (IDENTITY) primary key and a foreign key ID to the parent table.

So, for each source file, I need to convert some columns to decimal and separate the data into parent and detail tables.

Here is one way to do this:

For each source file

* use a thread for each file with Microsoft Visual Studio .NET 2003

* run insert query with SP's por each record, and join parent table with details.

* process next record

My app works fine in XP and SQL express, When I run the queries the CPU utilization is consitantly around 90%. This is my development environment.

My production enviroment is with SQL 2005 and Win 2003 Enterprise Edition Sp1.

When I run the queries the CPU utilization is consitantly around 3%.

I have 2 Questions:

1- Is there a better way to do this?

2- Why XP has utilization around 90%, and Win2003 3%?

Speed is the primary concern.

Thank you for any suggestions.

Using SPs calls is not the fastest way. The quickest way is to generate files including the identity values and then bcp/.bulk insert the data into SQL. You could use SSIS to do this as well.

The reason you should generate you ID values outside of the database is so that when spliting your input file into your tables you can just insert the data straight into the tables. You can let SQL generate the PK identity values of the detail tables.

help importing large flat file into relational tables

I am trying to find the best (fastest) way to import large text files into
sql server 2005 relational tables. The database has simple recovery model.
The total size of data to be load is about 1gb (each file)
source:
Multiple source files with different layout.
Destination:
3 sql server tables; a parent table with a ID (IDENTITY) primary key and a 3
column unique index (alternate key) and a detail table that has a DETAIL_ID
(IDENTITY) primary key and a foreign key ID to the parent table.
So, for each source file, I need to convert some columns to decimal and
separate the data into parent and detail tables.
Here is one way to do this:
For each source file
* use a thread for each file with Microsoft Visual Studio .NET 2003
* run insert query with SP's por each record, and join parent table with
details.
* process next record
My app works fine in XP and SQL express, When I run the queries the CPU
utilization is consitantly around 90%. This is my development environment.
My production enviroment is with SQL 2005 and Win 2003 Enterprise Edition Sp
1.
When I run the queries the CPU utilization is consitantly around 3%.
I have 2 Questions:
1- Is there a better way to do this?
2- Why XP has utilization around 90%, and Win2003 3%?
Speed is the primary concern.
Thank you for any suggestions.Macisu wrote:
> I am trying to find the best (fastest) way to import large text files into
> sql server 2005 relational tables. The database has simple recovery model.
> The total size of data to be load is about 1gb (each file)
> source:
> Multiple source files with different layout.
>
> Destination:
> 3 sql server tables; a parent table with a ID (IDENTITY) primary key and a
3
> column unique index (alternate key) and a detail table that has a DETAIL_I
D
> (IDENTITY) primary key and a foreign key ID to the parent table.
>
> So, for each source file, I need to convert some columns to decimal and
> separate the data into parent and detail tables.
> Here is one way to do this:
> For each source file
> * use a thread for each file with Microsoft Visual Studio .NET 2003
> * run insert query with SP's por each record, and join parent table with
> details.
> * process next record
> My app works fine in XP and SQL express, When I run the queries the CPU
> utilization is consitantly around 90%. This is my development environment.
> My production enviroment is with SQL 2005 and Win 2003 Enterprise Edition
Sp1.
> When I run the queries the CPU utilization is consitantly around 3%.
> I have 2 Questions:
> 1- Is there a better way to do this?
> 2- Why XP has utilization around 90%, and Win2003 3%?
>
> Speed is the primary concern.
> Thank you for any suggestions.
SQL Server Integration Services is the most obvious solution to try.
Read about Integration Services in Books Online. Depending on the
format of your files BCP may also be an option.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--