Access and SQL

I have an access database that is currently at the 2GB limit that I would
like to convert to SQL. I used DTS to load the database into SQL and created
an .adp project in Access to get at it. The problem occurs when I try and
open the .adp project it kills whatever PC I am on because it loads the
entire 2GB database into memory on the client. The database only has one
table inside it .. but that table contains pictures which constitute the 2GB.
Why does it try and load the entire table into memory, when before I
converted it to SQL it loaded just fine? Any suggestions on improving the
performance?
I suspect this is really an Access issue so you would probably get a better
answer if you post in an Access NG. But do you issue a SELECT with no WHERE
clause anywhere? Do you have Primary Key constraints defined on the table?
Andrew J. Kelly SQL MVP
"Scott T" <ScottT@.discussions.microsoft.com> wrote in message
news:28282103-A602-48A8-AACB-8BCADE1336E2@.microsoft.com...
>I have an access database that is currently at the 2GB limit that I would
> like to convert to SQL. I used DTS to load the database into SQL and
> created
> an .adp project in Access to get at it. The problem occurs when I try and
> open the .adp project it kills whatever PC I am on because it loads the
> entire 2GB database into memory on the client. The database only has one
> table inside it .. but that table contains pictures which constitute the
> 2GB.
> Why does it try and load the entire table into memory, when before I
> converted it to SQL it loaded just fine? Any suggestions on improving the
> performance?
|||Whenever you upsize an Access/Jet application to SQL Server, you have
to be aware of the fact that data which used to reside on the client
computer now consumes both server and network resources. When
constructing your Access front-end, you need to follow the golden rule
of "fetch only needed data". Never do a 'select * from' or open the
table directly in the UI. FWIW, you would be better off storing the
pictures on a network drive and only storing the path to the file in
the database. OTOH, if you had done that to begin with, then you
wouldn't have had a problem with your Jet database exceeding 2GB.
If you don't want to store the pictures on the file system, then in
your application, only load a single picture on demand, not all of
them at once.
--Mary
On Tue, 10 Jan 2006 13:10:02 -0800, "Scott T"
<ScottT@.discussions.microsoft.com> wrote:

>I have an access database that is currently at the 2GB limit that I would
>like to convert to SQL. I used DTS to load the database into SQL and created
>an .adp project in Access to get at it. The problem occurs when I try and
>open the .adp project it kills whatever PC I am on because it loads the
>entire 2GB database into memory on the client. The database only has one
>table inside it .. but that table contains pictures which constitute the 2GB.
>Why does it try and load the entire table into memory, when before I
>converted it to SQL it loaded just fine? Any suggestions on improving the
>performance?

Access 2000 to Sql2000 : file size change?

I have an Access 2000 database with 640,000 records.
(growing weekly) File size is hovering around 245MB.
I plan to import/convert this database into a new database
on our SQL2000 server. (As soon as I finished that
chapter of the book!)
Implementation planning question: Will the database size
change significantly either way once it's imported in SQL?
TIA,
TeresaIt will probably me less than with Access.
When you import the access stuff, take a minute to check the column-type
definitions and optimize it for your needs.
You might also want to check indexing (wizard) when running on SQL2000 with
your applications, so you can provide propre and fast usage.
jobi
"teresa" <teresaj@.vvh.org> wrote in message
news:06c301c35550$497ded40$3501280a@.phx.gbl...
> I have an Access 2000 database with 640,000 records.
> (growing weekly) File size is hovering around 245MB.
> I plan to import/convert this database into a new database
> on our SQL2000 server. (As soon as I finished that
> chapter of the book!)
> Implementation planning question: Will the database size
> change significantly either way once it's imported in SQL?
> TIA,
> Teresa
>sql

About Function:GETDATE()

Hello,
I useSELECT CONVERT(VARCHAR(30),GETDATE(),105)
and get the now data
25-04-2006,
but if i want to get the data format is 2006-04-25
How?
Thank you!Hi

There is a more efficient way of stripping the time portion off however it doesn't quite match your format. Second function is perhaps more what you are after.
SElECT DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)

SELECT CONVERT(CHAR(10),GETDATE(),120)

HTH|||the result is right! but

SELECT CONVERT(CHAR(10),GETDATE(),120)
is it insecurely?|||Um... I don't know. What do you mean by "insecurely"?

BTW - you can look up the constants in the BOL entry for CAST and CONVERT too.|||ok,thank you!

About extended stored procedures

Hi,
I have such situation:
A function that make some custom translation of numbers (convert numbers to
text). I have this function in DLL. I want to use that function in my
selects like that:
SELECT SOME_NUMBER, xp_transfunc(SOME_NUMBER) AS SOME_NUMBER_AS_TEXT
FROM SOME_TABLE
What are my options? Extended stored procedure? Something else? I know that
Extended stored procedures are function in external DLL that meet some
requirements, but can I use Extended stored procedure in such way?
TIA,
Miro.Hi Miro.
You can't call an xp_ directly in a set based call like that.
To do a set based call against an external library, you'd wrap the library
in a udf(), but calls to the xp_ will be serialized so performance might not
be too thrilling, depending on the number of rows in the select & the amount
of work / efficiency of the xp_.
If the custom logic isn't too complex, you might consider consider
converting to a t-sql udf() as this would give you the best performance, if
that's important to you..
HTH
Regards,
Greg Linwood
SQL Server MVP
"Miro Penchev" <miroslav.penchev@.bsc.bg> wrote in message
news:opr1pl4riy12pphn@.msnews.microsoft.com...
> Hi,
> I have such situation:
> A function that make some custom translation of numbers (convert numbers
to
> text). I have this function in DLL. I want to use that function in my
> selects like that:
> SELECT SOME_NUMBER, xp_transfunc(SOME_NUMBER) AS SOME_NUMBER_AS_TEXT
> FROM SOME_TABLE
> What are my options? Extended stored procedure? Something else? I know
that
> Extended stored procedures are function in external DLL that meet some
> requirements, but can I use Extended stored procedure in such way?
> TIA,
> Miro.|||There are only extended stored procedures and you would need to use EXEC to
call them. This sort of syntax is currently not supported for extended
stored procedures. Even if you call extended stored procedures from
functions, you have many restrictions there.
--
HTH,
SriSamp
Please reply to the whole group only!
http://www32.brinkster.com/srisamp
"Miro Penchev" <miroslav.penchev@.bsc.bg> wrote in message
news:opr1pl4riy12pphn@.msnews.microsoft.com...
> Hi,
> I have such situation:
> A function that make some custom translation of numbers (convert numbers
to
> text). I have this function in DLL. I want to use that function in my
> selects like that:
> SELECT SOME_NUMBER, xp_transfunc(SOME_NUMBER) AS SOME_NUMBER_AS_TEXT
> FROM SOME_TABLE
> What are my options? Extended stored procedure? Something else? I know
that
> Extended stored procedures are function in external DLL that meet some
> requirements, but can I use Extended stored procedure in such way?
> TIA,
> Miro.|||For now you should use a UDF. When YUKON comes out, you can make your UDF in
ainy dot net language... today your UDF must be written in T-SQL...
Another thing you might consider is writing a com object then using
sp_oacreate in a function to invoke it, but performance ( I suspect) would
not be as good as if you had written the UDF directly ONLY using T-SQL
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Miro Penchev" <miroslav.penchev@.bsc.bg> wrote in message
news:opr1pl4riy12pphn@.msnews.microsoft.com...
> Hi,
> I have such situation:
> A function that make some custom translation of numbers (convert numbers
to
> text). I have this function in DLL. I want to use that function in my
> selects like that:
> SELECT SOME_NUMBER, xp_transfunc(SOME_NUMBER) AS SOME_NUMBER_AS_TEXT
> FROM SOME_TABLE
> What are my options? Extended stored procedure? Something else? I know
that
> Extended stored procedures are function in external DLL that meet some
> requirements, but can I use Extended stored procedure in such way?
> TIA,
> Miro.|||On Tue, 13 Jan 2004 21:55:34 +1100
"Greg Linwood" <g_linwoodQhotmail.com> wrote:
> Hi Miro.
> You can't call an xp_ directly in a set based call like that.
> To do a set based call against an external library, you'd wrap the library
> in a udf(), but calls to the xp_ will be serialized so performance might not
> be too thrilling, depending on the number of rows in the select & the amount
> of work / efficiency of the xp_.
> If the custom logic isn't too complex, you might consider consider
> converting to a t-sql udf() as this would give you the best performance, if
> that's important to you..
The performance is not important in my case because that function will be used in SELECTs with small number of rows in result set (in most cases with only one row in result set). And the logic is complex enough to have that logic in two sources - one in SQL and one in my source. So I will made it with xp_.
Thanks again for your help,
Miro.|||On Tue, 13 Jan 2004 06:24:04 -0500
"Wayne Snyder" <wsnyder@.computeredservices.com> wrote:
> For now you should use a UDF. When YUKON comes out, you can make your UDF in
> ainy dot net language...
:) Ooo god - no! The 'dot net' is the BIG mistake in world of programming for last 20 years ;).
> today your UDF must be written in T-SQL...
> Another thing you might consider is writing a com object then using
> sp_oacreate in a function to invoke it, but performance ( I suspect) would
> not be as good as if you had written the UDF directly ONLY using T-SQL
I prefer to do it in xp_ and make wrapper for it in UDF (see my other post in that thread). Logic inside is too complex to have two sources to maintain - one in T_SQL and one in my source (Delphi source actually).
10x,
Miro.

About extended stored procedures

Hi,
I have such situation:
A function that make some custom translation of numbers (convert numbers to
text). I have this function in DLL. I want to use that function in my
selects like that:
SELECT SOME_NUMBER, xp_transfunc(SOME_NUMBER) AS SOME_NUMBER_AS_TEXT
FROM SOME_TABLE
What are my options? Extended stored procedure? Something else? I know that
Extended stored procedures are function in external DLL that meet some
requirements, but can I use Extended stored procedure in such way?
TIA,
Miro.Hi Miro.
You can't call an xp_ directly in a set based call like that.
To do a set based call against an external library, you'd wrap the library
in a udf(), but calls to the xp_ will be serialized so performance might not
be too thrilling, depending on the number of rows in the select & the amount
of work / efficiency of the xp_.
If the custom logic isn't too complex, you might consider consider
converting to a t-sql udf() as this would give you the best performance, if
that's important to you..
HTH
Regards,
Greg Linwood
SQL Server MVP
"Miro Penchev" <miroslav.penchev@.bsc.bg> wrote in message
news:opr1pl4riy12pphn@.msnews.microsoft.com...

quote:

---

> Hi,
> I have such situation:
> A function that make some custom translation of numbers (convert numbers

---

to

quote:

---

> text). I have this function in DLL. I want to use that function in my
> selects like that:
> SELECT SOME_NUMBER, xp_transfunc(SOME_NUMBER) AS SOME_NUMBER_AS_TEXT
> FROM SOME_TABLE
> What are my options? Extended stored procedure? Something else? I know

---

that

quote:

---

> Extended stored procedures are function in external DLL that meet some
> requirements, but can I use Extended stored procedure in such way?
> TIA,
> Miro.

---

|||There are only extended stored procedures and you would need to use EXEC to
call them. This sort of syntax is currently not supported for extended
stored procedures. Even if you call extended stored procedures from
functions, you have many restrictions there.
--
HTH,
SriSamp
Please reply to the whole group only!
http://www32.brinkster.com/srisamp
"Miro Penchev" <miroslav.penchev@.bsc.bg> wrote in message
news:opr1pl4riy12pphn@.msnews.microsoft.com...

quote:

---

> Hi,
> I have such situation:
> A function that make some custom translation of numbers (convert numbers

---

to

quote:

---

> text). I have this function in DLL. I want to use that function in my
> selects like that:
> SELECT SOME_NUMBER, xp_transfunc(SOME_NUMBER) AS SOME_NUMBER_AS_TEXT
> FROM SOME_TABLE
> What are my options? Extended stored procedure? Something else? I know

---

that

quote:

---

> Extended stored procedures are function in external DLL that meet some
> requirements, but can I use Extended stored procedure in such way?
> TIA,
> Miro.

---

|||For now you should use a UDF. When YUKON comes out, you can make your UDF in
ainy dot net language... today your UDF must be written in T-SQL...
Another thing you might consider is writing a com object then using
sp_oacreate in a function to invoke it, but performance ( I suspect) would
not be as good as if you had written the UDF directly ONLY using T-SQL
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Miro Penchev" <miroslav.penchev@.bsc.bg> wrote in message
news:opr1pl4riy12pphn@.msnews.microsoft.com...

quote:

---

> Hi,
> I have such situation:
> A function that make some custom translation of numbers (convert numbers

---

to

quote:

---

> text). I have this function in DLL. I want to use that function in my
> selects like that:
> SELECT SOME_NUMBER, xp_transfunc(SOME_NUMBER) AS SOME_NUMBER_AS_TEXT
> FROM SOME_TABLE
> What are my options? Extended stored procedure? Something else? I know

---

that

quote:

---

> Extended stored procedures are function in external DLL that meet some
> requirements, but can I use Extended stored procedure in such way?
> TIA,
> Miro.

---

|||On Tue, 13 Jan 2004 21:55:34 +1100
"Greg Linwood" <g_linwoodQhotmail.com> wrote:

quote:

---

> Hi Miro.
> You can't call an xp_ directly in a set based call like that.
> To do a set based call against an external library, you'd wrap the library
> in a udf(), but calls to the xp_ will be serialized so performance might n
ot
> be too thrilling, depending on the number of rows in the select & the amou
nt
> of work / efficiency of the xp_.
> If the custom logic isn't too complex, you might consider consider
> converting to a t-sql udf() as this would give you the best performance, i
f
> that's important to you..

---

The performance is not important in my case because that function will be us
ed in SELECTs with small number of rows in result set (in most cases with on
ly one row in result set). And the logic is complex enough to have that logi
c in two sources - one in S
QL and one in my source. So I will made it with xp_.
Thanks again for your help,
Miro.|||On Tue, 13 Jan 2004 06:24:04 -0500
"Wayne Snyder" <wsnyder@.computeredservices.com> wrote:

quote:

---

> For now you should use a UDF. When YUKON comes out, you can make your UDF
in
> ainy dot net language...

---

Ooo god - no! The 'dot net' is the BIG mistake in world of programming fo
r last 20 years ;).

quote:

---

> today your UDF must be written in T-SQL...
> Another thing you might consider is writing a com object then using
> sp_oacreate in a function to invoke it, but performance ( I suspect) would
> not be as good as if you had written the UDF directly ONLY using T-SQL

---

I prefer to do it in xp_ and make wrapper for it in UDF (see my other post i
n that thread). Logic inside is too complex to have two sources to maintain
- one in T_SQL and one in my source (Delphi source actually).
10x,
Miro.

about datetime

I have to convert date such as '2006-09-21 13:27:25.077' to '20060921'

any ideas?

thanks in advance

I attempted to convert(char(8),getdate()),but result is 'Sep 21...'

I have no idea about that

|||

Problem solved!

datatime have many styles

just like convert(char(8),getdate(),112) can perform what i want