Help if possible

I have very limited skill in Access, but am working on a project for work. I have a database file that includes fields like item number and ticket number. There are currently identical records for both of these fields. I'm trying to create a query that dislplays a specific item only if it has the same ticket number as any other item.

I'm not sure if I explained this the best, but any help would be greatly appreciated.select t1.itemnumber
, t1.ticketnumber
from yourtable as t1
inner
join yourtable as t2
on t1.ticketnumber
= t2.ticketnumber
and t1.itemnumber
<> t2.itemnumber|||I tried that code and it didn't work right, but maybe it's because I forgot to explain something in full detail. I'm looking to display the ticket number in which an itemnum 8999 was sold, but another itemnum was sold on that same ticket.|||select t2.itemnumber
, t1.ticketnumber
from yourtable as t1
inner
join yourtable as t2
on t1.ticketnumber
= t2.ticketnumber
and t1.itemnumber
<> t2.itemnumber
where t1.itemnumber = 8999sql

HELP I can't access cubes from excel

I try to register the dll in a remote machine but I couldn't.
Do I need to install something else in the remote machine?whats the message / error u r gett5ing. what exactly have u done?"|||The error is Load Library "C:\........................." failed
GetLastError returns 0x00000485
In the client I haven't installed anything. I only copied the dll and try to register it. Should I install something else?
Thanks in advance|||Does anyone has connected with Excel from other computer than the server?
From the server I can connect.
What whould I install in the computer to connect to the server with Excel?
Thanks in advance.|||

Before you can connect from Excel to the Olap 2005 server, you need to install Microsoft OLEDB for Olap 9.0.

The file you need to install is: pts9.msi - Located in
"En_SQL_DEV_x86_ctp_JUNE2005\Redist\Analysis Services\pts9.msi".

Best regards,

Jakobsgaard

|||Yes I have tried this with no luck.
I think is a version problem, my server is April CTP.
I'll try to install client tools in remote client.

Help getting error when using operation rename a file in the File system task editor?

Does anyone know how to do this using variables? Everytime I try it, I get the

Error: Failed to lock variable for read access with error 0xc00100001.

I also tried it writing a script and still the same error. If I hard code the values into the variables it works fine but I will be running this everday so that it will pull in the current date along with the filename. So the value of the variables will change everyday. Here is my expression:

@.[User::Variable] +(DT_WSTR,4) YEAR( GETDATE() )+"0"+(DT_WSTR,2) MONTH( GETDATE() ) + (DT_WSTR,2) DAY( GETDATE() )

The result:

C:\Documents and Settings\mroush\Desktop\OSU20060818

the 20060818 part will change everyday ie.(tomorrow will be 20060819, next day 20060820 and so on.)

I am not sure how you are doing this, were is the expression and which variable is selected in the File System Task editor.

How about this-

Create a variable called Filename.

Then go to the Properties grid for the Filename variable and set the EvaluateAsExpression to true. Now set the Expression property to your expression.

Now go back to the File System Task, and select variable Filename for the operation you want.

Is this what you have done or not?

Do you really need to rename the file? Normally I would use an Expression, like you have above, but on the ConnectionString property for my File/FlatFile connection, such that when I use the connection it automatically gets the "current" filename.

|||

Darren, thanks for the reply and

yes I done this in the expression property of the variable and set it to true, that works except it adds the file name twice. The result is below.

C:\Documents and Settings\mroush\Desktop\Cnet Apps File\Apps0821.txt0821.txt

How do I get it to only evaluate once and and it looks like everyday that it runs, it is going to just keep adding to the string? Is there away to make it clear the old filename before renaming the current one everyday or is there another way to do this? I have to rename the file with the current date everyday, instead of using 1 filename and setting the overwrite destination to true. Maybe this can't be done using this task?

Mike

|||

I don't get why there appears to be two filename parts. You have got two variables, one for the base path an then one for the full filename, upon which you set the expression?

Creating a new file everyday should be fine using an expression. If you are exporting thsi file, then why not set the expression on the flat file connection manager's connection string property, and save the extra step of the rename.

|||

Hi Darren, thanks again for responding.

Here are the two files that I am working with:

The file comes in everyday and is zipped as AAA20060822 with no extension. The numbers are the date and it changes everyday with the current date so I need the expression to get that date everyday when a new file appears. Like tomorrows file will be AAA20060823

The filename that I need to rename it as is BBBB0822.txt. It has to be renamed this way with the date format this way. I'm using 2 variables so that the dates are updated everyday automatically so that the task can be scheduled nightly. I know this is confusing but this is how the file comes in everday and this is how it needs to be renamed so the data can be processed correctly through our mainframe.

I've tried setting the isdestination and issourcepath variables to true and using the expressions in the source and destination properties through the expressions option but it errors telling me that it can't lock the variables or the variables have been renamed or do not exist in the container. Error 0xC00100001

So then I tried putting the expressions in the variables expression property and setting the evaluateasexpression property to true but it evaluates the expression twice each time I run the task adding the dates twice to the path causing it not to find the source file. I'm stupmed! I don't know what else to do? I works either way without an expression hard coding the filenames but not using variables that will change everday with the date?

Thanks,

mike

help for starting SQL Express Manager 2005

Can anyone advise how I access SQL Express Manager ?

After starting Express Manager (preview 1) and selecting "Windows Authentication" I am asked for "Server Instance" What is meant by server Instance ?
Or with "SQL Server authentication" option selected I'm asked for name and passsword as well.

Is this something I ovelooked setting up when installing SQL 2005 Express ? I dont remember setting any passwords.

WHich option should I be going for ?

Have read accompanying documentation and looked on web but have not found a guide on this.

RECEIVED THIS REPLY TO MY QUERY ON ANOTHER THREAD

Hi,

SQL Server Express default instance which is the instance that gets created when you do not specify a name is installed as SQLExpress.

In Windows Authentication the current interactive Windows User is used for logging on so you just need to supply the instance name as .\SQLExpress or
MachineName\SQLExpress.

SQL Express also supports SQL Authentication. In case you want to take that route please go through the following post:
http://forums.microsoft.com/msdn/ShowPost.aspx?PostID=62998
(Scrolll down to see the thread where I have provided the steps.)

Regards,
Vikram

.\SQLExpress - worked for me for the Windows Authentication - 0oseven

help fixing a divide by zero error

I am very new to using SQL Server 2005. I have used Microsoft Access in the
past for my queries and reports.
I have the following calculation will give me the divide by zero error
message and not show the affected records.
The calculation is used to calculate how much footage (in feet) different
materials will give me.
The formula that I use is (material thickness * material width * 12 *
density) I have the density set up as a parameter to allow me to enter the
density of the particular material I am looking up.
The problem that I have is that on some material thickness and material
width may have either a 0 or may be blank.
I want the output field for the calculation to show a 0 for the calculated
footage, or something to show me that this record is not calculated but still
shows up on the report.
Thank you in advance for any help.
--
joef1946On Nov 3, 2:42 pm, joef1946 <joef1...@.discussions.microsoft.com>
wrote:
> I am very new to using SQL Server 2005. I have used Microsoft Access in the
> past for my queries and reports.
> I have the following calculation will give me the divide by zero error
> message and not show the affected records.
> The calculation is used to calculate how much footage (in feet) different
> materials will give me.
> The formula that I use is (material thickness * material width * 12 *
> density) I have the density set up as a parameter to allow me to enter the
> density of the particular material I am looking up.
> The problem that I have is that on some material thickness and material
> width may have either a 0 or may be blank.
> I want the output field for the calculation to show a 0 for the calculated
> footage, or something to show me that this record is not calculated but still
> shows up on the report.
> Thank you in advance for any help.
> --
> joef1946
The divide by zero error usually only occurs when dividing by zero.
You should be able to avoid the error if you use a conditional
statement/expression similar to this where you are doing the
calculation (in the report field/cell).
=iif(Fields!MaterialThickness.Value is Nothing or Fields!
MaterialThickness.Value = 0 or Fields!MaterialWidth.Value is Nothing
or Fields!MaterialWidth.Value = 0 or Parameters!Density.Label is
Nothing or Parameters!Density.Label = 0, 0, (Fields!
MaterialThickness.Value * Fields!MaterialWidth.Value * 12 * Parameters!
Density.Label))
This expression basically says if any of the variables or the
parameter are zero or null/empty, set the calculation to zero,
otherwise, set the calculation to the formula with the values
provided. Of course, if you are truly dividing by zero, use a similar
expression that checks for the denominator/bottom of the fraction
variable to see if it is zero or null/empty. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultantsql

Help convert MS Access function to MS SQL User Defined Function

I have this function in access I need to be able to use in ms sql. Having problems trying to get it to work. The function gets rid of the leading zeros if the field being past dosn't have any non number characters.
For example:
TrimZero("000000001023") > "1023"
TrimZero("E1025") > "E1025"
TrimZero("000000021021") > "21021"
TrimZero("R5545") > "R5545"
Here is the function that works in access:
Public Function TrimZero(strField As Variant) As String
Dim strReturn As String
If IsNull(strField) = True Then
strReturn = ""
Else
strReturn = strField
Do While Left(strReturn, 1) = "0"
strReturn = Mid(strReturn, 2)
Loop
End If
TrimZero = strReturn
End Function
Is this not possible? I seem not to be able to figure it out onmy own. If I am missing any information in my question, whichinformation should I include?
|||Okay.. with the help from experts-exchange.com I was able to get the answer.
CREATE FUNCTION [dbo].[TrimZero] (@.MyString varchar(50))
RETURNS varchar(50)
AS
BEGIN
If ISNUMERIC(@.MyString) = 1
Begin
While Left (@.MyString,1) = '0'
Begin
Set @.MyString = Right(@.Mystring,Len(@.Mystring) -1)
End
End
Return @.MyString
END
|||A even better function to do the same thing:
create function dbo.trimzero (@.mystring varchar(50))
returns varchar(50)
AS
begin
declare @.myStrInt int
if isnumeric(@.mystring)=1
set @.myString = cast(@.mystring as int)
return @.mystring
end
GO

Help connecting to SQL Database

Hi. I'm totally new here, and I'm not an expert when it comes to SQL, so bear with me. This is my dilemma:

I use Access 2000 to connect to my Microsoft SQL Server 2000 database. Recently, our provider switched us from a shared server to a dedicated server. The site is up and running perfectly, but I can't connect to my database via Access anymore. Once I received the new location of the database, I ran odbcad32.exe and configured the database's DSN info where appropriate. I run the "Test Data Source..." function and the tests are seemingly successful. When I open my database using Access, I'm able to log in successfully, but when I click on any one of my linked tables, I get the following error:

Could not execute query; could not find linked table.

[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name
'NAMEOFDATABASE.NAMEOFTABLE'. (#208)

The support team at my provider are not exactly geniuses, so who knows if and when they'll be getting back to me. In the meantime, does anyone have a clue what might be causing this? Since the site is working perfectly, I assume the connection strings are okay and the database is perfectly intact (I even checked to see that the most recent entries were live), so what gives? If there's any more info I can provide, let me know. Thanks in advance.This is a pain. You have to view the linked tables in design view. Right-click on the title bar and select properties. You can then change the connection string to what it needs to be. The connection string gets hard-coded. Isn't that nice. :)

It's usually easier to just drop the linked tables and recreate all of them.|||Thank you. I was able to open each table in design view, but when I right clicked on the title bar and selected propeterties, I wasn't able to change the connection string under Description. This is so weird to me, since I'm using the same version of Access I used to access the SQL database when it was on a different server. I'm also able to make the connection and open the database using SQL Query Tool, but that obviously doesn't help me when it comes to inputing data. How do I exactly drop the tables and recreate them? :(|||delete the linked tables in access (only the linked tables!!). this does NOT delete the real tables on your server, only the links to them.

...then relink to them in their new location.

izy

LATER: i mean delete the links to the linked tables... ie in A's database window / tables, use the DEL button on all the linked tables|||Thanks izyrider. Repulsion you are just deleting the links like he said.|||Thanks derrickleggett and izyrider for bearing with me. That was a big help and with a little experimenting I was able to delete the linked talbes in Access, relinked to them in their new location, and lo and behold: I don't get the error message anymore. I can see all my tables, but now I can't input, change or delete data. The User and Group Permissions function under Tools-->Secruity seems to be locked. Is there something I can do on my end to actually use my database now, or do I have to contact my provider to deal with permission settings?|||If it's a third-party database contact your provider.sql

Help connecting ole/db linked server to msaccess database in a different machine than sql server

Hi,

I have a msaccess linked server that I use to execute sql 2000 stored procedures from a front end in adp (access data project) format without any problem, if it is used on the same machine where the sql server resides (with any user logged on). In any other machine on the local network where I also need to use it, I get an ole/db error message saying that the microsoft jet database engine can not open the file because it's allready opened exclusivly or because it do not has permissions. I created the linked server with both UNC and normal path with the same result.

Thank's for all the help/clues you can give me.

Hi!

This is usually a permissions issue. The account that the SQL Server runs under needs to have NT permissions to the directory where the MDB is stored.

|||

Thank's for your help Cindy, but the case is that I can't figure out what to do to solve the problem.

I am sorry but I don't know exactly what do you mean by "the account that SQL Server runs".

The users that are running the sp that reports the error all have rights in the local network directory where the mdb is stored.

Could you please be kind enough to continue helping me on this?

|||Running SQL Server Service needs a service account to run with. The account can be determined using e.g. the Service Control manager. The access for file access is impersonated using the service account which is running SQL Server. If this has no access to the UNC share (e.g. System Account) you won′t be able to access the file / establish a link to the "server" / Access database. In addition to the explanation you should not use a mapped drive letter always use UNC paths working with SQL Server. it cannot be guranteed that the account which is running the process has also the mapped drived letter assigned to his profile.

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

The service account SQL Server is using is the network login account of the users.
All of them have access to the network drive that is referenced in the linked server by the UNC path.

This is the code I used to set the linked server:

USE master
GO
EXEC sp_addlinkedserver
@.server = 'CRED85', -- Name of the linked Server
@.provider = 'Microsoft.Jet.OLEDB.4.0', -- Access Provider
@.srvproduct = 'dbaccess', -- may be anything
@.datasrc = '\\Gcxncliflsv301h\Aplic\AplicDep\DAI\APLIC\Produtos\Cred85.mdb' -- UNC path + Access db name
GO

and after that:

exec sp_addlinkedsrvlogin 'dbaccess', false, 'GRUPOCGD\c008645', 'Admin', NULL

So, my question remains:
- Why does it work only when the front end is executed in the machine where resides the server (with any user)
and NOT in any other machine? What's wrong?

Help connecting ole/db linked server to msaccess database in a different machine than sql server

Hi,

I have a msaccess linked server that I use to execute sql 2000 stored procedures from a front end in adp (access data project) format without any problem, if it is used on the same machine where the sql server resides (with any user logged on). In any other machine on the local network where I also need to use it, I get an ole/db error message saying that the microsoft jet database engine can not open the file because it's allready opened exclusivly or because it do not has permissions. I created the linked server with both UNC and normal path with the same result.

Thank's for all the help/clues you can give me.

Hi!

This is usually a permissions issue. The account that the SQL Server runs under needs to have NT permissions to the directory where the MDB is stored.

|||

Thank's for your help Cindy, but the case is that I can't figure out what to do to solve the problem.

I am sorry but I don't know exactly what do you mean by "the account that SQL Server runs".

The users that are running the sp that reports the error all have rights in the local network directory where the mdb is stored.

Could you please be kind enough to continue helping me on this?

|||Running SQL Server Service needs a service account to run with. The account can be determined using e.g. the Service Control manager. The access for file access is impersonated using the service account which is running SQL Server. If this has no access to the UNC share (e.g. System Account) you won′t be able to access the file / establish a link to the "server" / Access database. In addition to the explanation you should not use a mapped drive letter always use UNC paths working with SQL Server. it cannot be guranteed that the account which is running the process has also the mapped drived letter assigned to his profile.

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

The service account SQL Server is using is the network login account of the users.
All of them have access to the network drive that is referenced in the linked server by the UNC path.

This is the code I used to set the linked server:

USE master
GO
EXEC sp_addlinkedserver
@.server = 'CRED85', -- Name of the linked Server
@.provider = 'Microsoft.Jet.OLEDB.4.0', -- Access Provider
@.srvproduct = 'dbaccess', -- may be anything
@.datasrc = '\\Gcxncliflsv301h\Aplic\AplicDep\DAI\APLIC\Produtos\Cred85.mdb' -- UNC path + Access db name
GO

and after that:

exec sp_addlinkedsrvlogin 'dbaccess', false, 'GRUPOCGD\c008645', 'Admin', NULL

So, my question remains:
- Why does it work only when the front end is executed in the machine where resides the server (with any user)
and NOT in any other machine? What's wrong?

Help again

I have a database with SQL server 2000 as the engine and Access 2000 on the client end (GUI user interface) I was wanting to make a switchboard like the original one in accesss only I dont have that option to use it when the tables are sql tables instead of access tables. The only I could come up with is a form with buttons on it that lead to different forms but it doesnt have the same abilities as the original switchboard in access. When I click on a particular form button the form comes up but the original form is not hidden, its just not the same..and I dont know vb so what can I do?I'm not sure I completely understand your question, but you should be able to export all your Access table to SQL, drop your existing Access tables, link the SQL tables (via ODBC), rename the tables to the appropriate names and everything should work like a charm. Unless I'm missing something, of course. :confused: :o|||You're from Lakeside? Well, Yee Haw!!!.

Just kidding! :)

HELP accessing SSAS cube from distance

hello to everybody. well i just put the ssas database to a distant server. well what can i do now in order to be able to access the cube from distance? what component do i need to install to the remote pc and is there any way to access the management studio of the distant server from a remote pc(that is to say by clicking to a link can i have access to the server's managment studio?)?

I recommend using Terminal Server. It is a part of Windows 2003 server and I think that up to two users are allowed to connect through the Windows XP remote PC. With more users you will have to pay a license. But check the license requirements.

Regards

Thomas Ivarsson

|||well i want to give the possibility for more than 5 personns to access the cube. can i do that by installing sslserver express edition?do i have to install managment studio to all posts? thank u for your answer|||

Install Management Studio on the remote server and run Terminal Server from your own workstation. With this setup you will only need a remote desktop connection on your workstation. Windows XP have this as default.

I am talking about Terminal Server licenses. I think you have to pay for more than two simultaneous users.

Regards

Thomas Ivarsson

HELP accessing SSAS cube from distance

hello to everybody. well i just put the ssas database to a distant server. well what can i do now in order to be able to access the cube from distance? what component do i need to install to the remote pc and is there any way to access the management studio of the distant server from a remote pc(that is to say by clicking to a link can i have access to the server's managment studio?)?

I recommend using Terminal Server. It is a part of Windows 2003 server and I think that up to two users are allowed to connect through the Windows XP remote PC. With more users you will have to pay a license. But check the license requirements.

Regards

Thomas Ivarsson

|||well i want to give the possibility for more than 5 personns to access the cube. can i do that by installing sslserver express edition?do i have to install managment studio to all posts? thank u for your answer|||

Install Management Studio on the remote server and run Terminal Server from your own workstation. With this setup you will only need a remote desktop connection on your workstation. Windows XP have this as default.

I am talking about Terminal Server licenses. I think you have to pay for more than two simultaneous users.

Regards

Thomas Ivarsson

HELP Access to sql server 2005 express connection FAILS UPDATE

Hi

I got an access 2002 application front end with a sql server 2005 express back end. Some of my clients are having some difficulties. After using the application for a while, some of the users are finding that the system just hangs up. It usually happens after the front end application has been running for about an hour (sometimes sooner and sometimes later). There are perhaps 1 to 5 concurrent users and I have checked to see if there are any firewalls stalling it (I think I check all of them)- Is there any way that SQL Server 2005 express could be caused to just stall- This even occurs with the odd laptop. All the appropriate protocols are enabled as well. These databses are not very large.

ANY HELP WOULD BE GREATLY APPRETIATED!!!

Thanks

Frank Srebot

Moved thread to the SQL Server Express forum.|||

hi Frank,

what do you mean by "just hangs up"? does it completely stalls requiring a reboot, or it's "sleeeping" for just a while and then restarts working "as expected" or the like?

to start, few things to consider..

SQLExpress sets the "autoclose" property of it's created databases to true, and this causes the dbs to be shut down when not in use, meaning that tyey will be closed if no active connection references them.. this involves a little overhead at next re-use as the dbs must be re-open, but I do not think this is your problem... anyway, the eventual related "problem" can be workaround modifying the relative database property via sp_dboption database's system stored procedure call...

"autoshrink" database property is even set to true, and this causes, at engine scheduled time frames, the eventual shrinking of the involved databases, so that when lots of insert/delete operations are performed (actually lots of deletes), the engine wakes up a thread to shrink (when necessary) the databases, requiring some time to execute..

if the autogrowth property of the database's datafiles and logfiles is set to true and the engine states new file space is required, the engine enlarges the files (when needed) and this will obviously involve some time as well...

other non SQL Server related issues includes OS's scheduled tasks requiring lots of CPU and/or I/O..

but it's hard to solve this way

regards

|||

Thanks for the great and quick response.

To clarify, sql server just hangs up meaning that the application displays an hourglass and eventually the sql connection is lost and an error message is given. I was doing some research and I was wondering if the problem could be in the connection pooling configuration- currently the setting are that pool connections are enabled by default in the ODBC config settings- the databases which I am dealing are quite small- would any one have any ideas perhaps along these lines?

Thanks

Frank srebot

|||

Hi

This is an update to my connection Problem with Access 2002 to SQL SERVER EXPRESS 2005.

We are having random disconnects on the client side with Access putting up a "Connection Failure" dialog box even when the user is actively entering records into the system. Have any of you ever encountered this situation? We have disabled all TCP offloading engine technology on the machine thinking this was causing a problem with SQL Server as well as changing network cards to a whole different brand. We've pretty much ruled out the physical network at this point because we have changed cables and moved to another port on a different switch to no avail.

Is there some timeout setting or connection pooling setting that I am unaware of at the SQL Server level that has a problem interacting with Windows Server 2003 or Windows XP? I have checked and double checked all of the server settings between the old machine and the new and they are identical.

I did read that the connection pooling may be stressed and the pool of connections are 'Leaking'. This might be due to a bad cable or connection, but thats all I have found out.

Any info would be greatly appreciated.

Thanks

Frank

help a newb with excel and sql server

hello everyone.
I recently changed the database of a project from Ms Access to SQL
server and had to make a lot of adjustments but there's one here
where
I can't figure it out how to do this in SQL server.
I was using the following to create an excel file based on the select
from Ms Access.
"SELECT * INTO [Excel 8.0;Database=" & server.mapPath("../documents/
example.xls].[sheet1]")&" FROM (SELECT * FROM TABLE1)"
This was working fine until the bd was changed to Ms Server 2005
where
it occurs an error. I will post the error even if its kind useless.
Error:
Microsoft OLE DB Provider for SQL Server error '80040e14'
Incorrect syntax near ')'.
As I said, the exact same code works fine in access. Any sugestion
how
to get this solved'
I know some turn arounds like create a content/type x-excel page and
this allow the user to save the file, but I really need is to create
the file .XLS on the disk to allow the user to download whenever they
want.
Any help is highly appreciated.
Thanks in advance
JoaoHi
You can create a linked server to EXCEL file.BOL has pretty good explanation
about the subject among with examples
<joaotsetsemoita@.gmail.com> wrote in message
news:1191855011.546016.198450@.r29g2000hsg.googlegroups.com...
> hello everyone.
> I recently changed the database of a project from Ms Access to SQL
> server and had to make a lot of adjustments but there's one here
> where
> I can't figure it out how to do this in SQL server.
>
> I was using the following to create an excel file based on the select
> from Ms Access.
>
> "SELECT * INTO [Excel 8.0;Database=" & server.mapPath("../documents/
> example.xls].[sheet1]")&" FROM (SELECT * FROM TABLE1)"
>
> This was working fine until the bd was changed to Ms Server 2005
> where
> it occurs an error. I will post the error even if its kind useless.
>
> Error:
> Microsoft OLE DB Provider for SQL Server error '80040e14'
> Incorrect syntax near ')'.
>
> As I said, the exact same code works fine in access. Any sugestion
> how
> to get this solved'
>
> I know some turn arounds like create a content/type x-excel page and
> this allow the user to save the file, but I really need is to create
> the file .XLS on the disk to allow the user to download whenever they
> want.
>
> Any help is highly appreciated.
>
> Thanks in advance
>
> Joao
>

Help - syncronizing forms in Access 2000

regular access db - usually use sub-forms or tabs - this time I want seperate forms - have linked fields - but can't final code line in "open form" button command. Any suggestions welcome. Denny :(but can't final code line in "open form" button command

Can you explain this a little better. You might want to bullet point exactly what you want. If you want to just populate two forms on the open, you need to just scroll down on properties of main form until you get to the open propety. You'll want to set the properties of the parent form first, then the second. I have no idea if this is what you are trying to do.

HELP - need a function like MID in access

I am looking for a function that behaves like the MID function in access. I need to return all values in a column that has a specific charater in a specific location within the string. Example: I need to return all rows that have the number 2 in the fourth position of column TEXT.

Quote:

Originally Posted by tingirl76

I am looking for a function that behaves like the MID function in access. I need to return all values in a column that has a specific charater in a specific location within the string. Example: I need to return all rows that have the number 2 in the fourth position of column TEXT.

Nevermind I got it...SUBSTRING(expression, startposition, length)....thank you anyway!!!

HELP - my virtual directory is not working!

When I follow carefully the instruction inside the book,
I cannot access my virtual directory through url query
(404 page not found error). I am sure that I have allowed
the virtual directory to be queried through url. No
matter where I create the actual path to my virtual
directory, I still got error.
Before setting up the virtual directory, I can access the
files in C:\Inetpub\wwwroot\mydb through url, but after
setting up the virtual directory, what I can get is only
404 error. Could anyone help'What I typed is:
http://myservername/mydb?
sql=select+*+from+tablename+for+xml+uto+root=ROOT
I've got a file named index.htm in
C:\Inetpub\wwwroot\mydb, before settting it as virtual
directory, it works, but after, no luck
>--Original Message--
>When I follow carefully the instruction inside the book,
>I cannot access my virtual directory through url query
>(404 page not found error). I am sure that I have
allowed
>the virtual directory to be queried through url. No
>matter where I create the actual path to my virtual
>directory, I still got error.
>Before setting up the virtual directory, I can access
the
>files in C:\Inetpub\wwwroot\mydb through url, but after
>setting up the virtual directory, what I can get is only
>404 error. Could anyone help'
>.
>

HELP - Combining Rows in a View

Hi All,

I can do this in Access, with VB, but I'm pretty new to SQL Server.

Say you have the following table, call it TblStudents:

Grade Name
8 John
8 Mike
8 Ed
9 Tom
9 Greg
10 Jack
10 Tony

And you wanted a view that would give you:

Grade Name
8 John, Mike, Ed
9 Tom, Greg
10 Jack, Tony

How would you do this in SQL Server?

Thanks.

HenryRemember, you asked! I'd use:CREATE TABLE tHenry (
grade INT
, name VARCHAR(20)
)

INSERT INTO tHenry (grade, name)
SELECT 8, 'John'
UNION ALL SELECT 8, 'Mike'
UNION ALL SELECT 8, 'Ed'
UNION ALL SELECT 9, 'Tom'
UNION ALL SELECT 9, 'Greg'
UNION ALL SELECT 10, 'Jack'
UNION ALL SELECT 10, 'Tony'
GO

CREATE FUNCTION dbo.fHenry(@.piGrade INT) RETURNS VARCHAR(200) AS
BEGIN
DECLARE @.cList VARCHAR(8000)

SELECT @.cList = Coalesce(@.cList + ', ' + name, name)
FROM tHenry
WHERE grade = @.piGrade

RETURN @.cList
END
GO

CREATE VIEW vHenry AS SELECT DISTINCT TOP 100 PERCENT
grade, dbo.fHenry(grade) AS students
FROM tHenry
ORDER BY grade
GO

SELECT * FROM vHenry
GO

DROP VIEW vHenry
DROP FUNCTION dbo.fHenry
DROP TABLE tHenry-PatP

Help - All Users can Access All Databases

Dan wrote:
> Hello. I am using SQL Server 2005 Std. 32bit on Windows 2003 R2. I am
> having an issue where, when I create a new user, even before I assign them
> any rights they can connect to any database in my SQL instance and read/wr
ite
> everywhere. I can't imagine this is by design.
> Anyone know whats going on here?
http://msdn.microsoft.com/msdnmag/i...ServerSecurity/Hello. I am using SQL Server 2005 Std. 32bit on Windows 2003 R2. I am
having an issue where, when I create a new user, even before I assign them
any rights they can connect to any database in my SQL instance and read/writ
e
everywhere. I can't imagine this is by design.
Anyone know whats going on here?|||Dan wrote:
> Hello. I am using SQL Server 2005 Std. 32bit on Windows 2003 R2. I am
> having an issue where, when I create a new user, even before I assign them
> any rights they can connect to any database in my SQL instance and read/wr
ite
> everywhere. I can't imagine this is by design.
> Anyone know whats going on here?
http://msdn.microsoft.com/msdnmag/i...ServerSecurity/|||Are any of these users in Windows groups (such as the administrators group)?
"Dan" <Dan@.discussions.microsoft.com> wrote in message
news:D49A8E3D-1788-48E6-B4D6-F42F6A9B16C3@.microsoft.com...
> Hello. I am using SQL Server 2005 Std. 32bit on Windows 2003 R2. I am
> having an issue where, when I create a new user, even before I assign them
> any rights they can connect to any database in my SQL instance and
> read/write
> everywhere. I can't imagine this is by design.
> Anyone know whats going on here?|||We ran into a similar issue. The issue for us is that we granted
permissions to the public role, and since the guest account is enabled
by default, everyone has access to that database via the guest role.
Since the public role includes guest, granting public role gives them
access. I dont know if this is the scenario you have encountered, but
it can be handled by not using the public role and just granting
permissions to roles or groups that you are in control of.|||Are any of these users in Windows groups (such as the administrators group)?
"Dan" <Dan@.discussions.microsoft.com> wrote in message
news:D49A8E3D-1788-48E6-B4D6-F42F6A9B16C3@.microsoft.com...
> Hello. I am using SQL Server 2005 Std. 32bit on Windows 2003 R2. I am
> having an issue where, when I create a new user, even before I assign them
> any rights they can connect to any database in my SQL instance and
> read/write
> everywhere. I can't imagine this is by design.
> Anyone know whats going on here?|||We ran into a similar issue. The issue for us is that we granted
permissions to the public role, and since the guest account is enabled
by default, everyone has access to that database via the guest role.
Since the public role includes guest, granting public role gives them
access. I dont know if this is the scenario you have encountered, but
it can be handled by not using the public role and just granting
permissions to roles or groups that you are in control of.

Help - All Users can Access All Databases

Hello. I am using SQL Server 2005 Std. 32bit on Windows 2003 R2. I am
having an issue where, when I create a new user, even before I assign them
any rights they can connect to any database in my SQL instance and read/write
everywhere. I can't imagine this is by design.
Anyone know whats going on here?Dan wrote:
> Hello. I am using SQL Server 2005 Std. 32bit on Windows 2003 R2. I am
> having an issue where, when I create a new user, even before I assign them
> any rights they can connect to any database in my SQL instance and read/write
> everywhere. I can't imagine this is by design.
> Anyone know whats going on here?
http://msdn.microsoft.com/msdnmag/issues/05/06/SQLServerSecurity/|||Are any of these users in Windows groups (such as the administrators group)?
"Dan" <Dan@.discussions.microsoft.com> wrote in message
news:D49A8E3D-1788-48E6-B4D6-F42F6A9B16C3@.microsoft.com...
> Hello. I am using SQL Server 2005 Std. 32bit on Windows 2003 R2. I am
> having an issue where, when I create a new user, even before I assign them
> any rights they can connect to any database in my SQL instance and
> read/write
> everywhere. I can't imagine this is by design.
> Anyone know whats going on here?|||We ran into a similar issue. The issue for us is that we granted
permissions to the public role, and since the guest account is enabled
by default, everyone has access to that database via the guest role.
Since the public role includes guest, granting public role gives them
access. I dont know if this is the scenario you have encountered, but
it can be handled by not using the public role and just granting
permissions to roles or groups that you are in control of.