newbie question on select

Hi All,
I need to get records vayring from 1 to 100, What is the best way without g
etting one record at a time.
Is the only other way by generating a select command with In Statment each t
ime,
But this will make the string very long.
Any help will appreicated
Thank You.Sound like you need a server side cursor / paging solution for your
problem:
http://www.google.com/search?hl=de&...ql+server&meta=
There are tons of hits on the internet, perhaps you take a deeper look
in the examples to decide for one.
HTH, jens Suessmeyer.|||Please post your DDL plus sample data and expected results. Do you want the
rows where a particular column is in the range 1 - 100? If so, try:
select
*
from
MyTable
where
MyCol between 1 and 100
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
<CobraStrikes@.al.com> wrote in message
news:1140960857.29276.0@.ersa.uk.clara.net...
Hi All,
I need to get records vayring from 1 to 100, What is the best way
without getting one record at a time.
Is the only other way by generating a select command with In Statment each
time,
But this will make the string very long.
Any help will appreicated
Thank You.|||What version are you using ?
SQL Server 2005
CREATE TABLE SpeakerStats
(
speaker VARCHAR(10) NOT NULL PRIMARY KEY,
score INT NOT NULL,
)
SET NOCOUNT ON
INSERT INTO SpeakerStats VALUES('Dan', 1)
INSERT INTO SpeakerStats VALUES('Ron', 2)
INSERT INTO SpeakerStats VALUES('Kathy', 3)
INSERT INTO SpeakerStats VALUES('Suzanne', 4)
INSERT INTO SpeakerStats VALUES('Joe', 5)
INSERT INTO SpeakerStats VALUES('Robert', 6)
INSERT INTO SpeakerStats VALUES('Mike', 7)
WITH myCTE (rownum,speaker,score)
AS
(
SELECT ROW_NUMBER() OVER(ORDER BY score DESC) AS rownum,
speaker, score
FROM SpeakerStats
)
SELECT * FROM myCTE WHERE rownum BETWEEN 5 AND 7
ORDER BY rownum DESC
SQL Server 2000
SELECT * FROM
(
SELECT * ,(SELECT COUNT(*) FROM SpeakerStats S
WHERE S.speaker<=SpeakerStats.speaker)rownum
FROM SpeakerStats
) AS Der WHERE rownum >=5 AND rownum <8
ORDER BY rownum
<CobraStrikes@.al.com> wrote in message
news:1140960857.29276.0@.ersa.uk.clara.net...
> Hi All,
> I need to get records vayring from 1 to 100, What is the best way
> without getting one record at a time.
> Is the only other way by generating a select command with In Statment each
> time,
> But this will make the string very long.
> Any help will appreicated
> Thank You.
>
>|||We need more information on what you're trying to do. An example that we
could work with would be more helpful.
Regards
Colin Dawson
www.cjdawson.com
<CobraStrikes@.al.com> wrote in message
news:1140960857.29276.0@.ersa.uk.clara.net...
> Hi All,
> I need to get records vayring from 1 to 100, What is the best way
> without getting one record at a time.
> Is the only other way by generating a select command with In Statment each
> time,
> But this will make the string very long.
> Any help will appreicated
> Thank You.
>
>|||Sorry, I have posted this in the wrong group, it should have posted it to th
e Access group.
I have table with 500 employee details depending on the user selection it c
an be between
1 and 100 emp records of the 500 records not necessarily consecutive record
s.
I will google with link provided.
Thank you all for the quick replies.

Newbie Question about Events

Hello,
I have a report in an .aspx page. When the user expands the row the records
display correctly except when there are a ton of records (1500 or more).
Does anyone know a way to display the records in another page when the user
expands a row?
Thanks,
JohnOn Jun 4, 11:18 am, John <J...@.discussions.microsoft.com> wrote:
> Hello,
> I have a report in an .aspx page. When the user expands the row the records
> display correctly except when there are a ton of records (1500 or more).
> Does anyone know a way to display the records in another page when the user
> expands a row?
> Thanks,
> John
You can either use a subreport or you can use drill-through or Jump to
URL or Report (via right-clicking the cell/etc to jump through and
select the Navigation tab and link to a new report/etc). Hope this
helps.
Regards,
Enrique Martinez
Sr. Software Consultant

newbie needs dataset-sqltable update code

I have dataadapter and dataset that reads/writes to SQL tables.
I can read. I can create "new" records.
However, I have not been able to master the "updating" of an existing row.
Can someone provide me specific code for doing this please or tell me what I doing wrong in the code below.
The code I using is below. I don't get error, but changes do not get written to SQL dbase.
For starters, I think I "not" supposed to use the 2nd line(...NewRow). I think this is only for new row, not updating of existing row - but I don't know any other way to get schema of row.
thanks to any who can help

Dim drow As DataRow
drow = Me.dsRequests1.Tables("REQUESTS").NewRow
drow.BeginEdit()
drow.Item("Request_Name") = Me.txtRequestName.Text
drow.Item("Request_Comments_Txt") = Me.txtRequestComments.Text
drow.Item("Requestor_Contact_Id") = Me.txtRequestor.Text
drow.Item("Request_BigX_Status_Type_Cd") = Me.ddlBigXStatus
drow.Item("Request_Action_Type_Cd") = Me.ddlRequestActionRequested.SelectedItem.Text
drow.EndEdit()
Me.DaREQUESTS.Update(Me.dsRequests1.Tables("REQUESTS"))
Me.dsRequests1.AcceptChanges()You're right
NewRow is only for new rows,
To update an existing row you should find the row in your dataset/datatable - using your table's primary key and find method or by index - change it's values , and update your dataset/datatable
Something like this (if you're using a typed dataset)

Dim drow as DataRow = dsRequests.Requests.findbyprimarykey(myParimaryKeyValue)
drow.property = newValue
daRequests.update(myTable)

hth

Newbie HELP (ON SQL)

Hi all
I am very new to SQL and trying to select some records from a M/S
SQL database to display 7 days worth of data

My date column is set as a small date UK format

15/10/2002 00:33:13

Would I use the commands something like this

Select *
From my database
Where my table between GETDATE () AND GETDATE ()-7

Can anybody help as i am real lostohhh found it

SELECT Whatever, WhateverElse FROM TableName WHERE DateField >= DATEADD(d, -7, GETDATE())

Originally posted by webstep
Hi all
I am very new to SQL and trying to select some records from a M/S
SQL database to display 7 days worth of data

My date column is set as a small date UK format

15/10/2002 00:33:13

Would I use the commands something like this

Select *
From my database
Where my table between GETDATE () AND GETDATE ()-7

Can anybody help as i am real lost|||If you put this at the top of your script:

declare @.date1 datetime, @.date2 datetime
set @.date2 = convert(varchar(11), getdate(), 111)
set @.date1 = convert(varchar(11), getdate()-7, 111)

...then you can just use @.date1 as your from date and @.date2 as your to date. You won't have to put in any dates.

Steve

Newbie : Need Help in joining Multiple tables

I am using a query to get data about temp job & temp rates for an employee database. Problem is this query pulls up two records with different rates for the same work period. THE JOB_RATE table has two different job rates with different JOBRATE_EFFECTIVE_RATE . What candition should I add in this query so that it pulls up the Jobrate applicable to that particular WORKDATE & not all JOBRATES .

i.e,
say if Jobrate = 10 on 1-Dec-2002 & later revised to Jobrate =20 effective 1-Jan-2003, then for a particular workdate 16-Dec-02 ,
the report should display one record with Temp_rate= 10 instadof two records with diffenrent rates, other data being same

select EMPLOYEE.EMP_ID,
Job.Job_name Temp_job,
Job_Rate.Jobrate_Rate Temp_Rate,
To_Char(Work_Detail.Wrkd_Work_Date,'MM-DD-YYYY') WorkDate ,
To_Char(Work_Detail.wrkd_Start_Time,'HH24:MI') BeginTime ,
To_Char(Work_Detail.wrkd_End_time,'HH24:MI') EndTime
from Employee , Job, Job_Rate ,Work_Detail,Work_Summary
where EMPLOYEE.EMP_ID = WORK_SUMMARY.EMP_ID
AND WORK_SUMMARY.WRKS_ID = WORK_DETAIL.WRKS_ID
AND WORK_DETAIL.JOB_ID = JOB.JOB_ID
AND JOB.JOB_ID = Job_Rate.Job_IdOriginally posted by ritz1975
I am using a query to get data about temp job & temp rates for an employee database. Problem is this query pulls up two records with different rates for the same work period. THE JOB_RATE table has two different job rates with different JOBRATE_EFFECTIVE_RATE . What candition should I add in this query so that it pulls up the Jobrate applicable to that particular WORKDATE & not all JOBRATES .

i.e,
say if Jobrate = 10 on 1-Dec-2002 & later revised to Jobrate =20 effective 1-Jan-2003, then for a particular workdate 16-Dec-02 ,
the report should display one record with Temp_rate= 10 instadof two records with diffenrent rates, other data being same

select EMPLOYEE.EMP_ID,
Job.Job_name Temp_job,
Job_Rate.Jobrate_Rate Temp_Rate,
To_Char(Work_Detail.Wrkd_Work_Date,'MM-DD-YYYY') WorkDate ,
To_Char(Work_Detail.wrkd_Start_Time,'HH24:MI') BeginTime ,
To_Char(Work_Detail.wrkd_End_time,'HH24:MI') EndTime
from Employee , Job, Job_Rate ,Work_Detail,Work_Summary
where EMPLOYEE.EMP_ID = WORK_SUMMARY.EMP_ID
AND WORK_SUMMARY.WRKS_ID = WORK_DETAIL.WRKS_ID
AND WORK_DETAIL.JOB_ID = JOB.JOB_ID
AND JOB.JOB_ID = Job_Rate.Job_Id
You need to say:

AND job_rate.effective_date =
( SELECT MAX(jr.effective_date)
FROM job_rate jr
WHERE jr.effective_date <= Work_Detail.Wrkd_Work_Date
AND jr.job_id = job.job_id)

It is common to have a job_rate.end_date column to overcome this, so that the condition is simply:

AND Work_Detail.Wrkd_Work_Date BETWEEN job_rate.effective_date AND job_rate.end_date

This simplifies the query, but adds complication to the rate maintenance functionality.|||Thanks Andrewst .

The query has worked & I am satisfied after testing it .Thanks a lot for the help.

Newbie : Hard sql query?

Hey,
I have a table that records all my bank transactions. So in it there
is one entry per transaction. This entry has the date the transaction
occured.
All I want to do is perform a query that returns how many transactions
I had for each month.
Ie.
jan : 24 transactions
feb : 19 transactions
etc
is this possible? I know I can do "counts" ... but it seems hard
because I need to split the results return by the month it occurs in.
Any help you can give would be greatly appreciated!!
Thanx
Ryan RittenNot the most efficient way, but...
SELECT YEAR(TransactionDate), MONTH(TransactionDate), COUNT(*)
FROM TransactionTable
-- if you want to limit to specific year:
-- WHERE TransactionDate >= '20050101' AND TransactionDate < '20060101'
GROUP BY YEAR(TransactionDate), MONTH(TransactionDate)
ORDER BY 1,2
"Sparticus" <sparticusREMOVE@.thesparticusarena.com> wrote in message
news:1129914248.191858.104870@.f14g2000cwb.googlegroups.com...
> Hey,
> I have a table that records all my bank transactions. So in it there
> is one entry per transaction. This entry has the date the transaction
> occured.
> All I want to do is perform a query that returns how many transactions
> I had for each month.
> Ie.
> jan : 24 transactions
> feb : 19 transactions
> etc
> is this possible? I know I can do "counts" ... but it seems hard
> because I need to split the results return by the month it occurs in.
> Any help you can give would be greatly appreciated!!
> Thanx
> Ryan Ritten
>|||> I have a table that records all my bank transactions. So in it there
> is one entry per transaction. This entry has the date the transaction
> occured.
> All I want to do is perform a query that returns how many transactions
> I had for each month.
>
select Year(Date) As Year ,month(Date) Month, count(*) as TransCount from
YourTable
group by Year(Date),month(Date)
Francesco Anti|||Sparticus,
This should give you what you want. You'll need to WHERE for a specify year
or year range. If you want month names and/or you want the months with 0 or
as columns instead of rows...let me know.
SELECT DATEPART(MM,TDATE)AS 'MONTH', COUNT(*) 'TRANSACTION COUNT'
FROM #TRANSACTIONS
GROUP BY DATEPART(MM,TDATE)
HTH
Jerry
"Sparticus" <sparticusREMOVE@.thesparticusarena.com> wrote in message
news:1129914248.191858.104870@.f14g2000cwb.googlegroups.com...
> Hey,
> I have a table that records all my bank transactions. So in it there
> is one entry per transaction. This entry has the date the transaction
> occured.
> All I want to do is perform a query that returns how many transactions
> I had for each month.
> Ie.
> jan : 24 transactions
> feb : 19 transactions
> etc
> is this possible? I know I can do "counts" ... but it seems hard
> because I need to split the results return by the month it occurs in.
> Any help you can give would be greatly appreciated!!
> Thanx
> Ryan Ritten
>|||wow...thanx everyone. Learn somethign new everyday :)|||group your data by datepart(month,datefield),datepart(year,
datefield)
http://sqlservercode.blogspot.com/
"Sparticus" wrote:

> Hey,
> I have a table that records all my bank transactions. So in it there
> is one entry per transaction. This entry has the date the transaction
> occured.
> All I want to do is perform a query that returns how many transactions
> I had for each month.
> Ie.
> jan : 24 transactions
> feb : 19 transactions
> etc
> is this possible? I know I can do "counts" ... but it seems hard
> because I need to split the results return by the month it occurs in.
> Any help you can give would be greatly appreciated!!
> Thanx
> Ryan Ritten
>|||CREATE TABLE #MyTransaction
(
id int NOT NULL IDENTITY (1, 1),
[date] datetime NULL,
amount money NULL
) ON [PRIMARY]
insert into #MyTransaction
([date], amount)
values
('1-1-05', 100)
insert into #MyTransaction
([date], amount)
values
('1-10-05', 100)
insert into #MyTransaction
([date], amount)
values
('1-20-05', 100)
insert into #MyTransaction
([date], amount)
values
('2-1-05', 100)
insert into #MyTransaction
([date], amount)
values
('2-5-05', 100)
insert into #MyTransaction
([date], amount)
values
('2-7-05', 100)
insert into #MyTransaction
([date], amount)
values
('3-8-05', 10)
select datepart(yy,[date]) as Year, datename(mm,[date]) as month, count(id)
as Count
from #MyTransaction
group by datepart(yy,[date]), datename(mm,[date])
drop table #MyTransaction
"Sparticus" <sparticusREMOVE@.thesparticusarena.com> wrote in message
news:1129914248.191858.104870@.f14g2000cwb.googlegroups.com...
> Hey,
> I have a table that records all my bank transactions. So in it there
> is one entry per transaction. This entry has the date the transaction
> occured.
> All I want to do is perform a query that returns how many transactions
> I had for each month.
> Ie.
> jan : 24 transactions
> feb : 19 transactions
> etc
> is this possible? I know I can do "counts" ... but it seems hard
> because I need to split the results return by the month it occurs in.
> Any help you can give would be greatly appreciated!!
> Thanx
> Ryan Ritten
>

Newbie - What is the CLR Keyword/Method for @@IDENTITY - thanks!

What is the CLR Keyword/Method for @.@.IDENTITY? I'm doing some master detail inserts and when doing the insert for the child records, i need the new key that was generated for the parent records. Thanks!
Think I may have found the answer at the following site,

http://davidhayden.com/blog/dave/archive/2006/02/16/2803.aspx

It basically says that on your insert statement to append the following statement onto the end of the insert statement:

"SET @.MyParameter = SCOPE_IDENTITY()"

And then on your command object to pass in a parameter with the name of @.MyParameter (with Direction set to output) and then access the parameter after executing the command. Haven't tried it yet but hopefully it works. Is this correct as far you guys know? Is there a better way, lemme know, thanks!

newbie - Most Recent Records from multiple tables

Hi,

I'm trying to create a view or TSQL statement to return in one recordset...

a) the most recent record of a PK in table1 [foodRecipes]

b) the most recent record (if exists) of FK from table 1 with the PK from table2

Goal: Each recipe can have many versions, and each version can have many historical attempts at making cookies...

example:

table 1: foodRecipes (PK = foodGroup + recipeName + recipeDateModified)

foodGroup [nvarchar (50)]

recipeName [nvarchar (50]

recipeDateModified [datetime]

cupsOfSugar [float]

sampleData:

cookies, peanutButter, 3/3/2007, 1.5

cookies, peanutButter, 3/4/2007, 2.0

cookies, sugar, 3/3/2007, 5.0

table 2: foodRecipeHistory (PK = foodGroup + recipeName + recipeDateModified + historyDateModified)

foodGroup [nvarchar (50)] ...FK from table1

recipeName [nvarchar (50] ...FK from table1

recipeDateModified [datetime] ...FK from table1

historyDateModified [datetime]

cupsOfSugarHistory [float]

sampleData:

cookies, peanutButter, 3/3/2007, 3/3/2007 10:15:00 AM, 1.5

cookies, peanutButter, 3/4/2007, 3/4/2007 10:20:00 AM, 2.0

cookies, peanutButter, 3/4/2007, 3/4/2007 10:21:00 AM, 2.2

What I want: the view or TSQL should provide the most recent unique recipes data + the most recent history (if exists, otherwise NULL)

SELECT * FROM myRecipies

sample Resultset:

foodGroup, recipeName, recipeDateModified, cupsOfSugar, historyDateModified, cupsOfSugarHistory

cookies, peanutButter, 3/4/2007, 2.0, 2.2

cookies, sugar, 3/3/2007, 5.0, <NULL>

What I've got now:

1. TSQL that gives me back the most recent recipes (No History yet)

SELECT foodGroup, recipeName, recipeDateModified, cupsOfSugar, CONVERT(nvarchar(30), recipeDateModified, 9) AS strModifiedDate
FROM dbo.foodRecipes oher
WHERE (CONVERT(nvarchar(30), recipeDateModified, 9) IN
(SELECT MAX(CONVERT(nvarchar(30), recipeDateModified, 9))
FROM dbo.foodRecipes
WHERE foodGroup= oher.foodGroupAND recipeName = oher.recipeName))

...and this works great, I get back each unique recipe from table #1, the most recent...

anyone good at this?

thanks in advance,

bsierad

You must have a sweet tooth if your only ingredient is CupsOfSugar.... ;)

Anyway, try the query below to see if this is what you're after.

Chris

SELECT foodGroup,

recipeName,

recipeDateModified,

cupsOfSugar,

CONVERT(nvarchar(30), recipeDateModified, 9) AS strModifiedDate,

(SELECT TOP 1 frh.cupsOfSugarHistory

FROM dbo.foodRecipeHistory frh

WHERE frh.foodGroup = oher.foodGroup

AND frh.recipeName = oher.recipeName

AND frh.recipeDateModified = oher.recipeDateModified

ORDER BY frh.historyDateModified DESC) AS cupsOfSugarHistory

FROM dbo.foodRecipes oher

WHERE (CONVERT(nvarchar(30), recipeDateModified, 9) IN

(SELECT MAX(CONVERT(nvarchar(30), recipeDateModified, 9))

FROM dbo.foodRecipes

WHERE foodGroup= oher.foodGroupAND recipeName = oher.recipeName))

|||

Thanks!

Works great...and I can soak this in and apply it in other areas...

My real fields don't taste this good...machineGasFlow sounds pretty boring...

Can't thank you enough,

bsierad

newbie - Display rows with identical values

Hello,
how can i retrieve duplicate records?
I have this table with about 6500 records, and i know that there are a few
where a combination (Column1 - Column2) is identical
how can i retrieve these?SELECT Column1, Column2
FROM YourTable
GROUP BY Column1 - Column2
HAVING COUNT(*) > 1
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"benoit" <benoit@.discussions.microsoft.com> wrote in message
news:2851EFFB-2722-47E6-A654-D953AA4FB86A@.microsoft.com...
> Hello,
> how can i retrieve duplicate records?
> I have this table with about 6500 records, and i know that there are a few
> where a combination (Column1 - Column2) is identical
> how can i retrieve these?
>
>|||hi benoit,
Just a question, your table own a indentity field?
If so, use this:
delete from table1
where <identityfield> not in
(select max(<identityfiedl> ) from table1
group by [<field1>,<field2>]
Otherwise, let me know or post DDL
regards,
"benoit" wrote:

> Hello,
> how can i retrieve duplicate records?
> I have this table with about 6500 records, and i know that there are a few
> where a combination (Column1 - Column2) is identical
> how can i retrieve these?
>
>|||i was thinking the same way as roji
use northwind
select lastname,firstname into x from employees
union all
select top 5 lastname,firstname from employees
go
select lastname, firstname from x
group by lastname,firstname
having count(*)>1
Jose de Jesus Jr. Mcp,Mcdba
Data Architect
Sykes Asia (Manila philippines)
MCP #2324787
"benoit" wrote:

> Hello,
> how can i retrieve duplicate records?
> I have this table with about 6500 records, and i know that there are a few
> where a combination (Column1 - Column2) is identical
> how can i retrieve these?
>
>|||works great !
thx
"Roji. P. Thomas" wrote:

> SELECT Column1, Column2
> FROM YourTable
> GROUP BY Column1 - Column2
> HAVING COUNT(*) > 1
> --
> Roji. P. Thomas
> Net Asset Management
> http://toponewithties.blogspot.com
>
> "benoit" <benoit@.discussions.microsoft.com> wrote in message
> news:2851EFFB-2722-47E6-A654-D953AA4FB86A@.microsoft.com...
>
>|||Hi
This article had written by Itzik Ben-Gan
CREATE TABLE #Demo (
idNo int identity(1,1),
colA int,
colB int
)
INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (2,4)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (4,2)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (5,1)
INSERT INTO #Demo(colA,colB) VALUES (8,1)
PRINT 'Table'
SELECT * FROM #Demo
PRINT 'Duplicates in Table'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo <> B.idNo
AND A.colA = B.colA
AND A.colB = B.colB)
PRINT 'Duplicates to Delete'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo < B.idNo -- < this time, not <>
AND A.colA = B.colA
AND A.colB = B.colB)
DELETE FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo < B.idNo -- < this time, not <>
AND A.colA = B.colA
AND A.colB = B.colB)
PRINT 'Cleaned-up Table'
SELECT * FROM #Demo
DROP TABLE #Demo
"benoit" <benoit@.discussions.microsoft.com> wrote in message
news:2851EFFB-2722-47E6-A654-D953AA4FB86A@.microsoft.com...
> Hello,
> how can i retrieve duplicate records?
> I have this table with about 6500 records, and i know that there are a few
> where a combination (Column1 - Column2) is identical
> how can i retrieve these?
>
>

Newbie - Copy table from SQL Server 2005 to Access

I need to copy a farily large table (+200,000 records) to Access. I
need to do this from a VB.NET 2005 program. I can get this to work
using .NET code. But it's too slow.
I've heard of DTS, but know nothing about it. Is this what I should
be using? Does any one have some good code that I can use?Hi Paul
"Paul" wrote:

> I need to copy a farily large table (+200,000 records) to Access. I
> need to do this from a VB.NET 2005 program. I can get this to work
> using .NET code. But it's too slow.
> I've heard of DTS, but know nothing about it. Is this what I should
> be using? Does any one have some good code that I can use?
>
In SQL 2005 SQL Server Integration Services supersedes DTS, you can find out
more about this by looking in Books online. Sites such as
http://www.sqlis.com/ are also useful. You could use the Export Wizard to
create the initial SSIS package for you invoke this by right clicking the
database node in SSMS choose Tasks, then Export Data and step through the
wizard specifying your Access database as the destination.
Another possibile solution may be to use a linked server.
John|||Paul,
Why not link the table directly to the Access database and eliminate the
copy step?
[url]http://www.sqlservercentral.com/columnists/awarren/linkingaccesstosqlserver.asp[/u
rl]
-- Bill
"Paul" <pwh777@.hotmail.com> wrote in message
news:1174497377.108640.157780@.l77g2000hsb.googlegroups.com...
>I need to copy a farily large table (+200,000 records) to Access. I
> need to do this from a VB.NET 2005 program. I can get this to work
> using .NET code. But it's too slow.
> I've heard of DTS, but know nothing about it. Is this what I should
> be using? Does any one have some good code that I can use?
>|||> Why not link the table directly to the Access database and eliminate the
> copy step?http://www.sqlservercentral.com/col...gaccesstosql...

Thanks for the replies. AlterEgo, the application that I'm creating
is a VB.NET 2005 executable that will run at night via a scheduled
task. So running a query from Access is not possible. I thought
about running a query to do that but do not know how this is done from
within VB.NET. Plus, I didn't really want to do that.|||I successfully created an Access object within my VB.NET app and was
able to run the necessary queries to copy the data from the SQL Server
table to the Access table. It is fast and efficient. The code was
simple:
Dim accessMdb As New Access.Application
accessMdb.OpenCurrentDatabase("P:\WebSite\Databases
\WebDataDatabase.mdb", True)
accessMdb.DoCmd.OpenQuery("Delete_Web_Data_Table")
accessMdb.DoCmd.OpenQuery("Append_SQL1_WebData_to_Web_Data")
accessMdb.CloseCurrentDatabase()
Only other thing I needed was to add a reference to the Microsoft
Access 10.0 Object Library.

Newbie - Copy table from SQL Server 2005 to Access

I need to copy a farily large table (+200,000 records) to Access. I
need to do this from a VB.NET 2005 program. I can get this to work
using .NET code. But it's too slow.
I've heard of DTS, but know nothing about it. Is this what I should
be using? Does any one have some good code that I can use?Hi Paul
"Paul" wrote:
> I need to copy a farily large table (+200,000 records) to Access. I
> need to do this from a VB.NET 2005 program. I can get this to work
> using .NET code. But it's too slow.
> I've heard of DTS, but know nothing about it. Is this what I should
> be using? Does any one have some good code that I can use?
>
In SQL 2005 SQL Server Integration Services supersedes DTS, you can find out
more about this by looking in Books online. Sites such as
http://www.sqlis.com/ are also useful. You could use the Export Wizard to
create the initial SSIS package for you invoke this by right clicking the
database node in SSMS choose Tasks, then Export Data and step through the
wizard specifying your Access database as the destination.
Another possibile solution may be to use a linked server.
John|||Paul,
Why not link the table directly to the Access database and eliminate the
copy step?
http://www.sqlservercentral.com/columnists/awarren/linkingaccesstosqlserver.asp
-- Bill
"Paul" <pwh777@.hotmail.com> wrote in message
news:1174497377.108640.157780@.l77g2000hsb.googlegroups.com...
>I need to copy a farily large table (+200,000 records) to Access. I
> need to do this from a VB.NET 2005 program. I can get this to work
> using .NET code. But it's too slow.
> I've heard of DTS, but know nothing about it. Is this what I should
> be using? Does any one have some good code that I can use?
>|||> Why not link the table directly to the Access database and eliminate the
> copy step?http://www.sqlservercentral.com/columnists/awarren/linkingaccesstosql...
Thanks for the replies. AlterEgo, the application that I'm creating
is a VB.NET 2005 executable that will run at night via a scheduled
task. So running a query from Access is not possible. I thought
about running a query to do that but do not know how this is done from
within VB.NET. Plus, I didn't really want to do that.|||I successfully created an Access object within my VB.NET app and was
able to run the necessary queries to copy the data from the SQL Server
table to the Access table. It is fast and efficient. The code was
simple:
Dim accessMdb As New Access.Application
accessMdb.OpenCurrentDatabase("P:\WebSite\Databases
\WebDataDatabase.mdb", True)
accessMdb.DoCmd.OpenQuery("Delete_Web_Data_Table")
accessMdb.DoCmd.OpenQuery("Append_SQL1_WebData_to_Web_Data")
accessMdb.CloseCurrentDatabase()
Only other thing I needed was to add a reference to the Microsoft
Access 10.0 Object Library.

Newbie - Copy table from SQL Server 2005 to Access

I need to copy a farily large table (+200,000 records) to Access. I
need to do this from a VB.NET 2005 program. I can get this to work
using .NET code. But it's too slow.
I've heard of DTS, but know nothing about it. Is this what I should
be using? Does any one have some good code that I can use?
Hi Paul
"Paul" wrote:

> I need to copy a farily large table (+200,000 records) to Access. I
> need to do this from a VB.NET 2005 program. I can get this to work
> using .NET code. But it's too slow.
> I've heard of DTS, but know nothing about it. Is this what I should
> be using? Does any one have some good code that I can use?
>
In SQL 2005 SQL Server Integration Services supersedes DTS, you can find out
more about this by looking in Books online. Sites such as
http://www.sqlis.com/ are also useful. You could use the Export Wizard to
create the initial SSIS package for you invoke this by right clicking the
database node in SSMS choose Tasks, then Export Data and step through the
wizard specifying your Access database as the destination.
Another possibile solution may be to use a linked server.
John
|||Paul,
Why not link the table directly to the Access database and eliminate the
copy step?
http://www.sqlservercentral.com/columnists/awarren/linkingaccesstosqlserver.asp
-- Bill
"Paul" <pwh777@.hotmail.com> wrote in message
news:1174497377.108640.157780@.l77g2000hsb.googlegr oups.com...
>I need to copy a farily large table (+200,000 records) to Access. I
> need to do this from a VB.NET 2005 program. I can get this to work
> using .NET code. But it's too slow.
> I've heard of DTS, but know nothing about it. Is this what I should
> be using? Does any one have some good code that I can use?
>
|||> Why not link the table directly to the Access database and eliminate the
> copy step?http://www.sqlservercentral.com/columnists/awarren/linkingaccesstosql...
Thanks for the replies. AlterEgo, the application that I'm creating
is a VB.NET 2005 executable that will run at night via a scheduled
task. So running a query from Access is not possible. I thought
about running a query to do that but do not know how this is done from
within VB.NET. Plus, I didn't really want to do that.
|||I successfully created an Access object within my VB.NET app and was
able to run the necessary queries to copy the data from the SQL Server
table to the Access table. It is fast and efficient. The code was
simple:
Dim accessMdb As New Access.Application
accessMdb.OpenCurrentDatabase("P:\WebSite\Database s
\WebDataDatabase.mdb", True)
accessMdb.DoCmd.OpenQuery("Delete_Web_Data_Table")
accessMdb.DoCmd.OpenQuery("Append_SQL1_WebData_to_ Web_Data")
accessMdb.CloseCurrentDatabase()
Only other thing I needed was to add a reference to the Microsoft
Access 10.0 Object Library.