Please Help Just Upsized Access To Sql And Now Code Doesnt Work

here is the code i was using from a command button which basically used the first and last name fields to make a primary key called chart number. now it doesnt work and i belive it is in the dim rs as dao.recordset statement because this data now resides on an sql server. here is the code snippet.

Public Function chartlookup()
Dim db As DAO.Database <--believe to be the problem
Dim rs As DAO.Recordset <-- belive to be the problem
Dim SQL As String
Dim NewNum As Integer
Dim NeWChartNum As String

SQL = "Select max(Cint(Right([chartnumber],6))) As RecNum From tblpatientinfo WHERE UCase(Left([chartnumber],5)) = '" & UCase(Left([Forms]![fpatient]![lname], 3)) & UCase(Left([Forms]![fpatient]![fname], 2)) & "'"

Set db = CurrentDb() <-- -problem
Set rs = db.OpenRecordset(SQL) <--problem

If IsNull([Forms]![fpatient]![chartnumber]) = False Then GoTo 400

If rs.EOF = False Then
If IsNull(rs!RecNum) = False Then
NewNum = rs!RecNum + 1
Else
NewNum = 1
End If
End If
'If NewNum = 1 Then

[Forms]![fpatient]![chartnumber] = UCase(Left([Forms]![fpatient]![lname], 3)) & UCase(Left([Forms]![fpatient]![fname], 2)) & Format(NewNum, "000000")
'End If

400 End Function

basically if you typw in john smith this code would put smijo00001 into chartnumber field now i get a run-time error 91.What error are you receiving?

Also be advised that UCase is not supported in MS SQL, you should use UPPER, nor is Cint.

You have a few access specific casts there that are going to cause trouble for you.|||Change you dao to ado - using recordset and/or connection objects.|||Originally posted by Teddy
What error are you receiving?

Also be advised that UCase is not supported in MS SQL, you should use UPPER, nor is Cint.

You have a few access specific casts there that are going to cause trouble for you.

im receiving a run-time error 91 in the set rs= db.recordset(sql) statement but i know it coming from previous dim db as dao.database and dim rs as dao.recordset. i dont know how to convert this vb code to work with sql. maybe dim rs as sql.recordset and dim db as sql.database . please forgive my ignorance but i am new to sql and i dont know the syntax for sql. by the way thank you for the advice with ucase and cint not working . i tried a previous suggestion and turned dim rs as ado.recordset but the only option i get is adobe.recordset am i missing a reference .|||You need to use the ms ado 2.x library reference. Using adodb.recordset and adodb.connection - however, in your case you only need adodb.recordset.|||Hey
Who one saying that ODBC Connections not possible by DAO?

[QUOTE][SIZE=1]Originally posted by opcbriley
here is the code i was using from a command button which basically used the first and last name fields to make a primary key called chart number. now it doesnt work and i belive it is in the dim rs as dao.recordset statement because this data now resides on an sql server. here is the code snippet.

Public Function chartlookup()
Dim wrkODBC As Workspace
Dim conPubs As Connection
Dim rs As DAO.Recordset <-- belive to be the problem
Dim SQL As String
Dim NewNum As Integer
Dim NeWChartNum As String

SQL = "Select max(Cint(Right([chartnumber],6))) As RecNum From tblpatientinfo WHERE UCase(Left([chartnumber],5)) = '" & UCase(Left([Forms]![fpatient]![lname], 3)) & UCase(Left([Forms]![fpatient]![fname], 2)) & "'"

Set wrkODBC = CreateWorkspace("NewODBCWorkspace", "admin", "", dbUseODBC)
Set conPubs = wrkODBC.OpenConnection("costing", dbDriverNoPrompt)
.........

You may also open connection by using this code
remaining code will remain same
plz use it n reply me
thx|||You can use odbc - but don't - you are much better off using oledb (or use tds in .net). Better performance, resource usage and flexibility/functionality.|||yeah i know that OLEDB performance much better that dao but person asked abt DAO libraray n no one was there to reply by DAO libraray so I hv ginven reply by DAO library|||i appreciate all your help i was able to switch to ado connection i finally learned the syntax last night after about hour of reading. that code is working perfect now.

now on to find all my other problems that i have to change to be compatible with sql. i sure hope sql is worth all this trouble.

Please Help !

Hello All-

By mistake I overwrote the Start Menu for my MS SQL Server.
I am not able to figure how to get this menu back nither I know the command using which I can start the Console and Enterprise Manager.

I would highly appreciate if anyone can post the start menu commands to start these.

Thanks
Sanjeev.For Enterprise Manager on my computer:

Target: D:\WINNT\system32\mmc.exe /s "D:\Program Files\Microsoft SQL Server\80\Tools\BINN\SQL Server Enterprise Manager.MSC"

Starts in: "D:\Program Files\Microsoft SQL Server\80\Tools\BINN"sql

Please help - unusual warning when creating a column

Hi everyone,
This is weird. I create a new column programatically using the "ALTER
TABLE" command (you know the one) inside SQL Query Analyzer. The
column is indeed created but the following warning is thrown:
WARNING: The table 'OrderRecords' has been created but its maximum row
size (15,420) exceeds the maximum number of bytes per row (8060).
INSERT or UPDATE of a row in this table will fail if the resulting row
length exceeds 8060 bytes
I then checked a likely column. I ran a wee query to find the maximum
row and it returned a row of maximum length of 2,647 characters.
Now I'm totally confused. Does anyone know why such a warning is being
thrown? Any suggestions/comments/ideas/queries to run - much
appreciated.
Puzzled,
Al.
The whole size of the rows exceeds 8060 bytes, thats the most possible
bytes SQl Server can handle per row, so this is just a warning (not an
error), that it might could be if you exceed the magic border, data
will be truncated.
If you are aware of that and you=B4ll be happy with it or you have
another way to handle this, this is fine, but you should keep that in
mind. For the best you should redesign your table by putting some
values in another table or thinking of changing the actual types of the
columns, if that is possible.
HTH, Jens Suessmeyer.
|||This means that the sum of all you columns can in theory be 15,420
bytes
If someone inserts data and it exceeds 8060 bytes, then the statement
will fail
look at all the field sizes in this table and make sure they don't
exceed 8060 bytes
http://sqlservercode.blogspot.com/
|||> This is weird. I create a new column programatically using the "ALTER
> TABLE" command (you know the one) inside SQL Query Analyzer. The
> column is indeed created but the following warning is thrown:
> WARNING: The table 'OrderRecords' has been created but its maximum row
> size (15,420) exceeds the maximum number of bytes per row (8060).
> INSERT or UPDATE of a row in this table will fail if the resulting row
> length exceeds 8060 bytes
It is a WARNING about the maximum possible rowsize based on the columns
defined for the table. Just guessing, but it is likely that you have a
couple (or a bunch) of large varchar columns in the table that could, if
completely filled with data, exceed the maximum possible rowsize supported
by sql server.
|||Thanks everyone - that's fine. I can live with that. Thanks for all
your feedback.
Al.
The happy one.

Please help - unusual warning when creating a column

Hi everyone,
This is weird. I create a new column programatically using the "ALTER
TABLE" command (you know the one) inside SQL Query Analyzer. The
column is indeed created but the following warning is thrown:
WARNING: The table 'OrderRecords' has been created but its maximum row
size (15,420) exceeds the maximum number of bytes per row (8060).
INSERT or UPDATE of a row in this table will fail if the resulting row
length exceeds 8060 bytes
I then checked a likely column. I ran a wee query to find the maximum
row and it returned a row of maximum length of 2,647 characters.
Now I'm totally confused. Does anyone know why such a warning is being
thrown? Any suggestions/comments/ideas/queries to run - much
appreciated.
Puzzled,
Al.The whole size of the rows exceeds 8060 bytes, thats the most possible
bytes SQl Server can handle per row, so this is just a warning (not an
error), that it might could be if you exceed the magic border, data
will be truncated.
If you are aware of that and you=B4ll be happy with it or you have
another way to handle this, this is fine, but you should keep that in
mind. For the best you should redesign your table by putting some
values in another table or thinking of changing the actual types of the
columns, if that is possible.
HTH, Jens Suessmeyer.|||This means that the sum of all you columns can in theory be 15,420
bytes
If someone inserts data and it exceeds 8060 bytes, then the statement
will fail
look at all the field sizes in this table and make sure they don't
exceed 8060 bytes
http://sqlservercode.blogspot.com/|||> This is weird. I create a new column programatically using the "ALTER
> TABLE" command (you know the one) inside SQL Query Analyzer. The
> column is indeed created but the following warning is thrown:
> WARNING: The table 'OrderRecords' has been created but its maximum row
> size (15,420) exceeds the maximum number of bytes per row (8060).
> INSERT or UPDATE of a row in this table will fail if the resulting row
> length exceeds 8060 bytes
It is a WARNING about the maximum possible rowsize based on the columns
defined for the table. Just guessing, but it is likely that you have a
couple (or a bunch) of large varchar columns in the table that could, if
completely filled with data, exceed the maximum possible rowsize supported
by sql server.|||Thanks everyone - that's fine. I can live with that. Thanks for all
your feedback.
Al.
The happy one.

Please help - unusual warning when creating a column

Hi everyone,
This is weird. I create a new column programatically using the "ALTER
TABLE" command (you know the one) inside SQL Query Analyzer. The
column is indeed created but the following warning is thrown:
WARNING: The table 'OrderRecords' has been created but its maximum row
size (15,420) exceeds the maximum number of bytes per row (8060).
INSERT or UPDATE of a row in this table will fail if the resulting row
length exceeds 8060 bytes
I then checked a likely column. I ran a wee query to find the maximum
row and it returned a row of maximum length of 2,647 characters.
Now I'm totally confused. Does anyone know why such a warning is being
thrown? Any suggestions/comments/ideas/queries to run - much
appreciated.
Puzzled,
Al.The whole size of the rows exceeds 8060 bytes, thats the most possible
bytes SQl Server can handle per row, so this is just a warning (not an
error), that it might could be if you exceed the magic border, data
will be truncated.
If you are aware of that and you=B4ll be happy with it or you have
another way to handle this, this is fine, but you should keep that in
mind. For the best you should redesign your table by putting some
values in another table or thinking of changing the actual types of the
columns, if that is possible.
HTH, Jens Suessmeyer.|||This means that the sum of all you columns can in theory be 15,420
bytes
If someone inserts data and it exceeds 8060 bytes, then the statement
will fail
look at all the field sizes in this table and make sure they don't
exceed 8060 bytes
http://sqlservercode.blogspot.com/|||> This is weird. I create a new column programatically using the "ALTER
> TABLE" command (you know the one) inside SQL Query Analyzer. The
> column is indeed created but the following warning is thrown:
> WARNING: The table 'OrderRecords' has been created but its maximum row
> size (15,420) exceeds the maximum number of bytes per row (8060).
> INSERT or UPDATE of a row in this table will fail if the resulting row
> length exceeds 8060 bytes
It is a WARNING about the maximum possible rowsize based on the columns
defined for the table. Just guessing, but it is likely that you have a
couple (or a bunch) of large varchar columns in the table that could, if
completely filled with data, exceed the maximum possible rowsize supported
by sql server.|||Thanks everyone - that's fine. I can live with that. Thanks for all
your feedback.
Al.
The happy one.

PL/SQL command to print a saved file in unix?

I've created and written a file to a unix directory using a stored procedure. Is there a way through that same stored procedure to send that file to a printer.

Right now I'm using lp -d 1_it_zeb overpack_lbl.txt to print the file from a command prompt. How would I send this same command to unix using my procedure?

Thanks!
CraigOne method is to create a pipe on unix that performs a specific task, say printing in your case. The following example shows a pipe that is created such that it compresses that file being written to (in this case, PL/SQL).

#--Setup the pipeline to be used in the export process
mknod $dmp_file_name p
compress < $dmp_file_name > $dmp_file_name.Z &

You should be able to formulate a pipe to send the contents to a printer.

Or, you could set up a cron job to look at files being generated and once completed, send it off to the printer.

PL/sql

I have problem in creating index on particular column with pL/SQL procedure . I dont know what command to and how to use?
how to run that PL/sql procedure. And howto check that wether index is created or not?
Please guide meHere's the example:

/* create a procedure */
CREATE OR REPLACE PROCEDURE brisime
AS
ci VARCHAR2 (255);
BEGIN
ci := 'create index i1 on tob_pool_ocit (pool_id, omm_id)';

EXECUTE IMMEDIATE ci;
END;

/* execute the procedure */
BEGIN
brisime ();
END;

/* checking whether it is created */
SELECT *
FROM user_indexes
WHERE index_name = 'I1';

PKZip issue

I have command line PKZip (pkzipc.exe) installed on my SQL Server 2000.
I want to use it to zip up some text files that I receive and process
on a daily basis to save space. I want to schedule it to run as a job.
The problem is that when I run the job as a standard user, it just does
not work. No error messages or anything. If I make the job owned by
SA, it is fine.

My only thought is that when I am signed in as a SA, my account on the
box is in the Admin group. When I sign in as a standard user, I am not
sure what account it uses to hit the file system. I would assume it
would use the xp_cmdshelluser account I have set up but I am not sure.

Has anyone seen this before? It has me stumped.

Any ideas??

Thanks!

Jim Youmans
St Louis, MissouriIn SQL 2000, the SQL Agent Proxy account is used as the OS security context
for non-sysadmin users. This is configurable from Enterprise Manager under
SQL Agent-->Properties-->Job System. When the 'Only users with Sysadmin
privileges..' is unchecked, you can specify the Windows account to be used
as the security context for non-sysadmin users. It's best to specify a
minimally privileged account.

I see you are located in St. Louis, You might consider joining our local
SQL Server User Group (http://www.stlssug.org/index.html) if you haven't
already done so. We also have a BI special interest group.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Jim" <jdyoumans@.gmail.comwrote in message
news:1158185320.259108.169510@.p79g2000cwp.googlegr oups.com...

Quote:

Originally Posted by

>I have command line PKZip (pkzipc.exe) installed on my SQL Server 2000.
I want to use it to zip up some text files that I receive and process
on a daily basis to save space. I want to schedule it to run as a job.
The problem is that when I run the job as a standard user, it just does
not work. No error messages or anything. If I make the job owned by
SA, it is fine.
>
My only thought is that when I am signed in as a SA, my account on the
box is in the Admin group. When I sign in as a standard user, I am not
sure what account it uses to hit the file system. I would assume it
would use the xp_cmdshelluser account I have set up but I am not sure.
>
Has anyone seen this before? It has me stumped.
>
Any ideas??
>
Thanks!
>
Jim Youmans
St Louis, Missouri
>

|||Thank you for your suggestions. I am a member of the user group and my
coworker who solved this was at the meeting on Wed.

Here is how we ended up solving the issue. Hope it helps someone else.

"I was finally able to get PKZIPC to work as a non-SQL administrator on
FENSQLMLMD04, but it's a bit of a hack. Thanks for all your
suggestions and help. Kudos to Jerry for the final idea...

Tim, since you gave terminal services rights to US\US_SQLCmdshell,
pkzipc worked whenever I remoted in to the server as US_SQLCmdshell.
But as soon as I logged off, it stopped working. Same problem between
pkzipc version 8 and version 6 (except that 6 hangs), same problem on
the other sql server.

I think the problem is that pkzipc is looking in the HKCU registry key,
just to see if it exists. If it does, pkzipc reads the user's
environment information from another registry key,
HKLM...\ProfileList\... and then looks like it sets up the command
prompt environment variables. But if HKCU doesn't exist, it drops out
or hangs.

Maybe that's a standard Win32API thing whenever a command shell program
is launched? I don't think so, because I can run other commands like
"xcopy" fine. Anyway, when I'm not logged in as US_SQLCmdshell, the
HKCU key doesn't exist so pkzipc drops out at that point before it ever
loads the user's environment information.

So Jerry suggested that we use a "dummy" or "benign" Windows service
that is running as US\US_SQLCmdshell in order to keep it logged in.
Brilliant -- now it works! "

Jim Youmans
St Louis Missouri

Dan Guzman wrote:

Quote:

Originally Posted by

In SQL 2000, the SQL Agent Proxy account is used as the OS security context
for non-sysadmin users. This is configurable from Enterprise Manager under
SQL Agent-->Properties-->Job System. When the 'Only users with Sysadmin
privileges..' is unchecked, you can specify the Windows account to be used
as the security context for non-sysadmin users. It's best to specify a
minimally privileged account.
>
I see you are located in St. Louis, You might consider joining our local
SQL Server User Group (http://www.stlssug.org/index.html) if you haven't
already done so. We also have a BI special interest group.
>
--
Hope this helps.
>
Dan Guzman
SQL Server MVP
>
"Jim" <jdyoumans@.gmail.comwrote in message
news:1158185320.259108.169510@.p79g2000cwp.googlegr oups.com...

Quote:

Originally Posted by

I have command line PKZip (pkzipc.exe) installed on my SQL Server 2000.
I want to use it to zip up some text files that I receive and process
on a daily basis to save space. I want to schedule it to run as a job.
The problem is that when I run the job as a standard user, it just does
not work. No error messages or anything. If I make the job owned by
SA, it is fine.

My only thought is that when I am signed in as a SA, my account on the
box is in the Admin group. When I sign in as a standard user, I am not
sure what account it uses to hit the file system. I would assume it
would use the xp_cmdshelluser account I have set up but I am not sure.

Has anyone seen this before? It has me stumped.

Any ideas??

Thanks!

Jim Youmans
St Louis, Missouri