SQL Alert: code

Showing posts with label code. Show all posts

Tuesday, March 27, 2012

Accessing Java within SQL Server

have a Java Class that I want to be able to access from within a SQL Server Stored procedure. I know we can access C# and other .NET Framework code, but how can you access a Java Class?

How would you access a Java class from "normal" .NET code?

Niels

Accessing HttpContext.Current from code section in report

I am trying to access HttpContext.Current in my report code section (report properties) since I have to look at a cookie. However, HttpContext.Current is always null. Has anyone managed to access HttpContex.Current from a report?

Thanks in advance.

Ok, it was security permission problem with the code section. After deploying the report I got the #Error when trying to access HttpContext.Current. I modified the rssrvpolicy.config (location c:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\ReportServer\) to FullTrust for the Code section and then it started working.

<CodeGroup class="UnionCodeGroup" version="1" PermissionSetName="FullTrust"

Name="Report_Expressions_Default_Permissions" Description="This code group grants default permissions for code in report

expressions and Code element. ">

instead of

<CodeGroup class="UnionCodeGroup" version="1" PermissionSetName="Execution"

Name="Report_Expressions_Default_Permissions" Description="This code group grants default permissions for code in report

expressions and Code element. ">

Probably not recommended but for now I am just testing. Will move the code to an assembly later and give that assembly FullTrust instead.

Sunday, March 25, 2012

accessing datasets / Field collections through custom code

Hi,

My report has multiple datasets, and I want to access the fields of a particular dataset from custom code. The problem is that the name of the dataset to use is only calculated during the execution of the custom code.

Allow me to illustrate:

public function Test(data as DataSets, fieldName as string)

dim datasetName as string = CalculateDatasetName()
dim ds as Dataset = data(datasetName)

' now here I want to get the fieldName out of the dataset.
' in the report, I would do something like
' First(Fields!fieldName.Value, datasetName)
' but in custom code, this obviously doesn't work.

end function

I've been looking for a way to accomplish this, but it seems you cannot get data from a dataset through custom code (there is only the commandtext property). Also it is not possible to pass the Fields collection as a parameter, as I do not know the dataset name when in the report designer.

I hope my problem description is clear.
Does anyone know how to solve this issue?

Thanks,
Phil

Hi Phil,

Look at the code below

public DataSet Dataset_name = new DataSet();

public DataTable DataTable_name = new DataTable();

public DataColumn DataColumn_name = new DataColumn();

public DataView View_name = new DataView();

public datatype function(name_of _variable_which catch_the column_value datatype)

{

DataColumn_name = new DataColumn("name_of _variable_which catch_the column_value", System.Type.GetType("System.String"))

}

Hope this helps

Priyank

Accessing dataset with custom code

Hi,

Need a little help on the code here. How can I import values from an RSS dataset? I will store them in a hash table and then call the function to check for dups. An example or pointer or article reference would be appreciated.

Thanks.

Since RSS is xml, you can use the XML data processing extension that comes with SQL Server 2005. This link has documentation on this feature:

http://msdn2.microsoft.com/en-us/library/ms159741.aspx

Basically, you create a data source in report designer with type xml and specify the connection string to be the url of RSS feed. The query language it uses is similar to XPath. The doc for the query language is here:

http://msdn2.microsoft.com/en-US/library/ms365158.aspx

|||Here is an example of using the Xml Data Processing extension using this forum's RSS feed as the source.

Connection String:
http://forums.microsoft.com/MSDN/rss.aspx?ForumID=82&Mode=0&SiteID=1

Query:
<Query>
<ElementPath IgnoreNamespaces="True">rss{}/channel{title,link, description}/item{title, date, link, description, comments}</ElementPath>
</Query>

Ian|||

Thanks for all the help. This stuff is pretty much new to me. I'm mostly familiar with VBA, Classic VB, and T-SQL but we'll see where this leads.

Thursday, March 22, 2012

Accessing ASP datasource results through VB code

I have the following datasource...

<asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="<%$ ***%>"

ProviderName="<%$ ***%>"SelectCommand='SELECT "var1", "var2", "var3" FROM "tbl1"'>

</asp:SqlDataSource>

Would it be possible to use VB code to read the reasults from this datasource? (There are conditions that restrict me from running the query itself through VB code.)

As always, any help is greatly appreciated. Thanks!

you should use the connection and recordset that is intrinsic to vb....I am not sure you can use vb6 or vb5 to look into a dataset.

you could use vb.net but i am stating the obvious here...

actually i found this

http://www.dotnet247.com/247reference/msgs/15/75021.aspx

looks like you can do it but will need some extra code.

Tuesday, March 20, 2012

Access2SQL

Hi
I want to change the code from using Access to SQL server 2000, what do i have to change in the next sentence:
dim Conn as new OleDbConnection("Provider=" & "Microsoft.Jet.OLEDB.4.0;" & _
"Data Source =D:\f\o\forfera\private\R.mdb")

dim objCmd as OleDbCommand = new OleDbCommand ("ValidateUser", Conn)
objCmd.CommandType = CommandType.StoredProcedure

Do i have to change another things in my code, like importing any namespace?
Thank'sSkip the OleDb Namespace and exchange it with the SqlClient Namespace. As for your code:


Dim Conn as New OleDbConnection("Server=<yoursqlserver>;uid=<username>;pwd<secret>;database=<yourdatabasename>")
Dim objCmd as New SqlCommand("ValidateUser", Conn)
objCmd.CommandType = CommandType.StoredProcedure

Remember also to change your eventual OleDbType to SqlDbType when using parameters. In short, whereever you use OleDb, use Sql instead.|||I know Andre has answered this question already,
and I'd like to share a tool with all of you. :)
FYI:http://weblogs.asp.net/coltk/archive/2004/05/31/144810.aspx

Regards, Colt|||Hi
Thank's for the answer, dont i have to write the source of the data base(location)?
Thank's|||The source of your database is your SQL Server. If the SQL Server is installed on your local development machine, your server is 'localhost' or '<machinename>'. If you are connecting to a remote machine, use the IP no, or Url of the Sql Server machine.

Access2K Connecting to SQL Only as LocalAdmin

I have created an Access2K front end application that connects to a
SQLServer2K backend. I use this vba code to create the connection from
the Access app:

Dim strConnect As String
'make sure all previous connections are closed:
CurrentProject.OpenConnection "Provider="

'create new connection string to server:
strConnect = "PROVIDER=SQLOLEDB.1;INTEGRATED SECURITY=SSPI;PERSIST
SECURITY INFO=FALSE;INITIAL CATALOG=NewsBaseDataSQL;DATA
SOURCE=nycvnewsbas01"

CurrentProject.OpenConnection strConnect

Everything functions.

The problem is the users cannot make the connection if they are not
part of the local admins group on the server. As soon as they are
removed from the local admins group their conenctions fail.

How do I remedy this?By default, only 'BUILTIN\Administrators' can access SQL Server and this is
as sysadmin. You can grant a Windows login access to SQL Server with:

EXEC sp_grantlogin 'MyDomain\MyUser'

Then, grant the login access to your database:

USE NewsBaseDataSQL
EXEC sp_grantdbaccess 'MyDomain\MyUser'

Users will need permissions on those database objects used by your
application. A best practice is to create database roles and grant required
permissions to roles. You can then control user permissions via role
membership:

USE NewsBaseDataSQL
EXEC sp_addrole 'MyRole'
GRANT ALL ON MyTable TO MyRole

EXEC sp_addrolemember 'MyRole', 'MyDomain\MyUser'

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Blake" <blakesell@.hotmail.com> wrote in message
news:a8ceff1a.0407170635.48ae2b44@.posting.google.c om...
> I have created an Access2K front end application that connects to a
> SQLServer2K backend. I use this vba code to create the connection from
> the Access app:
> Dim strConnect As String
> 'make sure all previous connections are closed:
> CurrentProject.OpenConnection "Provider="
> 'create new connection string to server:
> strConnect = "PROVIDER=SQLOLEDB.1;INTEGRATED SECURITY=SSPI;PERSIST
> SECURITY INFO=FALSE;INITIAL CATALOG=NewsBaseDataSQL;DATA
> SOURCE=nycvnewsbas01"
> CurrentProject.OpenConnection strConnect
> Everything functions.
> The problem is the users cannot make the connection if they are not
> part of the local admins group on the server. As soon as they are
> removed from the local admins group their conenctions fail.
> How do I remedy this?|||Dan,
Thanks for the reply.
Can I do this automatically witht the existing database role "public"
sine that has already been grated permission to all objects?

Since there are hundreds of users, is there a way I can get around
having to grantlogin for every MyDomain\MyUser?

Thanks

"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message news:<x3bKc.5326$mL5.1112@.newsread1.news.pas.earthlink.n et>...
> By default, only 'BUILTIN\Administrators' can access SQL Server and this is
> as sysadmin. You can grant a Windows login access to SQL Server with:
> EXEC sp_grantlogin 'MyDomain\MyUser'
> Then, grant the login access to your database:
> USE NewsBaseDataSQL
> EXEC sp_grantdbaccess 'MyDomain\MyUser'
> Users will need permissions on those database objects used by your
> application. A best practice is to create database roles and grant required
> permissions to roles. You can then control user permissions via role
> membership:
> USE NewsBaseDataSQL
> EXEC sp_addrole 'MyRole'
> GRANT ALL ON MyTable TO MyRole
> EXEC sp_addrolemember 'MyRole', 'MyDomain\MyUser'
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Blake" <blakesell@.hotmail.com> wrote in message
> news:a8ceff1a.0407170635.48ae2b44@.posting.google.c om...
> > I have created an Access2K front end application that connects to a
> > SQLServer2K backend. I use this vba code to create the connection from
> > the Access app:
> > Dim strConnect As String
> > 'make sure all previous connections are closed:
> > CurrentProject.OpenConnection "Provider="
> > 'create new connection string to server:
> > strConnect = "PROVIDER=SQLOLEDB.1;INTEGRATED SECURITY=SSPI;PERSIST
> > SECURITY INFO=FALSE;INITIAL CATALOG=NewsBaseDataSQL;DATA
> > SOURCE=nycvnewsbas01"
> > CurrentProject.OpenConnection strConnect
> > Everything functions.
> > The problem is the users cannot make the connection if they are not
> > part of the local admins group on the server. As soon as they are
> > removed from the local admins group their conenctions fail.
> > How do I remedy this?|||> Dan,
> Thanks for the reply.
> Can I do this automatically witht the existing database role "public"
> sine that has already been grated permission to all objects?

All users are automatically members of the public role so granting a user
access to this database will provide the needed permissions. However, you
might consider creating your own roles so that you can provide different
levels of permissions (e.g. read-only or read-write) and control this with
role membership. Below is a script than can setup role-based object
security on all database objects that you can run to initially setup
security and after schema changes.

> Since there are hundreds of users, is there a way I can get around
> having to grantlogin for every MyDomain\MyUser?

One method is to create a local Windows group on your SQL box and grant that
group access to SQL Server and your database. You can then add the desired
users to that local group so they are authorized via group membership. This
method allows you to control SQL Server access at the OS level rather than
SQL Server but note that is about the same amount of work as adding
individual users to SQL Server; it mostly depends on your personal
preference.

--Grant permissions to specified role
SET NOCOUNT ON

DECLARE @.GrantStatement nvarchar(500)
DECLARE @.LastError int

DECLARE GrantStatements CURSOR LOCAL FAST_FORWARD FOR
SELECT
N'GRANT ALL ON ' +
QUOTENAME(USER_NAME([ob].[uid])) + '.' + QUOTENAME([ob].[name]) +
' TO MyRole'
FROM
sysobjects ob
WHERE
OBJECTPROPERTY([ob].[id], 'IsMSShipped') = 0 AND
(OBJECTPROPERTY([ob].[id], 'IsProcedure') = 1 OR
OBJECTPROPERTY([ob].[id], 'IsUserTable') = 1 OR
OBJECTPROPERTY([ob].[id], 'IsView') = 1 OR
OBJECTPROPERTY([ob].[id], 'IsInlineFunction') = 1 OR
OBJECTPROPERTY([ob].[id], 'IsScalarFunction') = 1 OR
OBJECTPROPERTY([ob].[id], 'IsTableFunction') = 1)
OPEN GrantStatements
WHILE 1 = 1
BEGIN
FETCH NEXT FROM GrantStatements INTO @.GrantStatement
IF @.@.FETCH_STATUS = -1 BREAK
RAISERROR (@.GrantStatement, 0, 1) WITH NOWAIT
EXECUTE sp_ExecuteSQL @.GrantStatement
END
CLOSE GrantStatements
DEALLOCATE GrantStatements

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Blake" <blakesell@.hotmail.com> wrote in message
news:a8ceff1a.0407171331.35ed65bc@.posting.google.c om...
> Dan,
> Thanks for the reply.
> Can I do this automatically witht the existing database role "public"
> sine that has already been grated permission to all objects?
> Since there are hundreds of users, is there a way I can get around
> having to grantlogin for every MyDomain\MyUser?
> Thanks
>
> "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:<x3bKc.5326$mL5.1112@.newsread1.news.pas.earthlink.n et>...
> > By default, only 'BUILTIN\Administrators' can access SQL Server and this
is
> > as sysadmin. You can grant a Windows login access to SQL Server with:
> > EXEC sp_grantlogin 'MyDomain\MyUser'
> > Then, grant the login access to your database:
> > USE NewsBaseDataSQL
> > EXEC sp_grantdbaccess 'MyDomain\MyUser'
> > Users will need permissions on those database objects used by your
> > application. A best practice is to create database roles and grant
required
> > permissions to roles. You can then control user permissions via role
> > membership:
> > USE NewsBaseDataSQL
> > EXEC sp_addrole 'MyRole'
> > GRANT ALL ON MyTable TO MyRole
> > EXEC sp_addrolemember 'MyRole', 'MyDomain\MyUser'
> > --
> > Hope this helps.
> > Dan Guzman
> > SQL Server MVP
> > "Blake" <blakesell@.hotmail.com> wrote in message
> > news:a8ceff1a.0407170635.48ae2b44@.posting.google.c om...
> > > I have created an Access2K front end application that connects to a
> > > SQLServer2K backend. I use this vba code to create the connection from
> > > the Access app:
> > > > Dim strConnect As String
> > > 'make sure all previous connections are closed:
> > > CurrentProject.OpenConnection "Provider="
> > > > 'create new connection string to server:
> > > strConnect = "PROVIDER=SQLOLEDB.1;INTEGRATED SECURITY=SSPI;PERSIST
> > > SECURITY INFO=FALSE;INITIAL CATALOG=NewsBaseDataSQL;DATA
> > > SOURCE=nycvnewsbas01"
> > > > CurrentProject.OpenConnection strConnect
> > > > Everything functions.
> > > > The problem is the users cannot make the connection if they are not
> > > part of the local admins group on the server. As soon as they are
> > > removed from the local admins group their conenctions fail.
> > > > How do I remedy this?|||Dan,
Thanks for your responses.
In this database, access to forms is controlled by Windows signon and
the public role has persmission to run all stored procedures. For its
purposes, this level of security works fine. So at this point I don't
need to create a new role.
What I gather then is that all I need to do is to grant each Windows
user/domain permission to SQL Server by looping through my user table
and running the following for each user:

EXEC sp_grantlogin 'MyDomain\MyUser'

I this correct?

"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message news:<gmwKc.6281$mL5.5101@.newsread1.news.pas.earthlink.n et>...
> > Dan,
> > Thanks for the reply.
> > Can I do this automatically witht the existing database role "public"
> > sine that has already been grated permission to all objects?
> All users are automatically members of the public role so granting a user
> access to this database will provide the needed permissions. However, you
> might consider creating your own roles so that you can provide different
> levels of permissions (e.g. read-only or read-write) and control this with
> role membership. Below is a script than can setup role-based object
> security on all database objects that you can run to initially setup
> security and after schema changes.
> > Since there are hundreds of users, is there a way I can get around
> > having to grantlogin for every MyDomain\MyUser?
> One method is to create a local Windows group on your SQL box and grant that
> group access to SQL Server and your database. You can then add the desired
> users to that local group so they are authorized via group membership. This
> method allows you to control SQL Server access at the OS level rather than
> SQL Server but note that is about the same amount of work as adding
> individual users to SQL Server; it mostly depends on your personal
> preference.
>
> --Grant permissions to specified role
> SET NOCOUNT ON
> DECLARE @.GrantStatement nvarchar(500)
> DECLARE @.LastError int
> DECLARE GrantStatements CURSOR LOCAL FAST_FORWARD FOR
> SELECT
> N'GRANT ALL ON ' +
> QUOTENAME(USER_NAME([ob].[uid])) + '.' + QUOTENAME([ob].[name]) +
> ' TO MyRole'
> FROM
> sysobjects ob
> WHERE
> OBJECTPROPERTY([ob].[id], 'IsMSShipped') = 0 AND
> (OBJECTPROPERTY([ob].[id], 'IsProcedure') = 1 OR
> OBJECTPROPERTY([ob].[id], 'IsUserTable') = 1 OR
> OBJECTPROPERTY([ob].[id], 'IsView') = 1 OR
> OBJECTPROPERTY([ob].[id], 'IsInlineFunction') = 1 OR
> OBJECTPROPERTY([ob].[id], 'IsScalarFunction') = 1 OR
> OBJECTPROPERTY([ob].[id], 'IsTableFunction') = 1)
> OPEN GrantStatements
> WHILE 1 = 1
> BEGIN
> FETCH NEXT FROM GrantStatements INTO @.GrantStatement
> IF @.@.FETCH_STATUS = -1 BREAK
> RAISERROR (@.GrantStatement, 0, 1) WITH NOWAIT
> EXECUTE sp_ExecuteSQL @.GrantStatement
> END
> CLOSE GrantStatements
> DEALLOCATE GrantStatements
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Blake" <blakesell@.hotmail.com> wrote in message
> news:a8ceff1a.0407171331.35ed65bc@.posting.google.c om...
> > Dan,
> > Thanks for the reply.
> > Can I do this automatically witht the existing database role "public"
> > sine that has already been grated permission to all objects?
> > Since there are hundreds of users, is there a way I can get around
> > having to grantlogin for every MyDomain\MyUser?
> > Thanks
> > "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
> news:<x3bKc.5326$mL5.1112@.newsread1.news.pas.earthlink.n et>...
> > > By default, only 'BUILTIN\Administrators' can access SQL Server and this
> is
> > > as sysadmin. You can grant a Windows login access to SQL Server with:
> > > > EXEC sp_grantlogin 'MyDomain\MyUser'
> > > > Then, grant the login access to your database:
> > > > USE NewsBaseDataSQL
> > > EXEC sp_grantdbaccess 'MyDomain\MyUser'
> > > > Users will need permissions on those database objects used by your
> > > application. A best practice is to create database roles and grant
> required
> > > permissions to roles. You can then control user permissions via role
> > > membership:
> > > > USE NewsBaseDataSQL
> > > EXEC sp_addrole 'MyRole'
> > > GRANT ALL ON MyTable TO MyRole
> > > > EXEC sp_addrolemember 'MyRole', 'MyDomain\MyUser'
> > > > --
> > > Hope this helps.
> > > > Dan Guzman
> > > SQL Server MVP
> > > > "Blake" <blakesell@.hotmail.com> wrote in message
> > > news:a8ceff1a.0407170635.48ae2b44@.posting.google.c om...
> > > > I have created an Access2K front end application that connects to a
> > > > SQLServer2K backend. I use this vba code to create the connection from
> > > > the Access app:
> > > > > > Dim strConnect As String
> > > > 'make sure all previous connections are closed:
> > > > CurrentProject.OpenConnection "Provider="
> > > > > > 'create new connection string to server:
> > > > strConnect = "PROVIDER=SQLOLEDB.1;INTEGRATED SECURITY=SSPI;PERSIST
> > > > SECURITY INFO=FALSE;INITIAL CATALOG=NewsBaseDataSQL;DATA
> > > > SOURCE=nycvnewsbas01"
> > > > > > CurrentProject.OpenConnection strConnect
> > > > > > Everything functions.
> > > > > > The problem is the users cannot make the connection if they are not
> > > > part of the local admins group on the server. As soon as they are
> > > > removed from the local admins group their conenctions fail.
> > > > > > How do I remedy this?|||> What I gather then is that all I need to do is to grant each Windows
> user/domain permission to SQL Server by looping through my user table
> and running the following for each user:
> EXEC sp_grantlogin 'MyDomain\MyUser'

Yes, and also:

USE MyDatabase
EXEC sp_grantdbaccess 'MyDomain\MyUser'

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Blake" <blakesell@.hotmail.com> wrote in message
news:a8ceff1a.0407190926.527fca70@.posting.google.c om...
> Dan,
> Thanks for your responses.
> In this database, access to forms is controlled by Windows signon and
> the public role has persmission to run all stored procedures. For its
> purposes, this level of security works fine. So at this point I don't
> need to create a new role.
> What I gather then is that all I need to do is to grant each Windows
> user/domain permission to SQL Server by looping through my user table
> and running the following for each user:
> EXEC sp_grantlogin 'MyDomain\MyUser'
> I this correct?
>
> "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:<gmwKc.6281$mL5.5101@.newsread1.news.pas.earthlink.n et>...
> > > Dan,
> > > Thanks for the reply.
> > > Can I do this automatically witht the existing database role "public"
> > > sine that has already been grated permission to all objects?
> > All users are automatically members of the public role so granting a
user
> > access to this database will provide the needed permissions. However,
you
> > might consider creating your own roles so that you can provide different
> > levels of permissions (e.g. read-only or read-write) and control this
with
> > role membership. Below is a script than can setup role-based object
> > security on all database objects that you can run to initially setup
> > security and after schema changes.
> > > > Since there are hundreds of users, is there a way I can get around
> > > having to grantlogin for every MyDomain\MyUser?
> > One method is to create a local Windows group on your SQL box and grant
that
> > group access to SQL Server and your database. You can then add the
desired
> > users to that local group so they are authorized via group membership.
This
> > method allows you to control SQL Server access at the OS level rather
than
> > SQL Server but note that is about the same amount of work as adding
> > individual users to SQL Server; it mostly depends on your personal
> > preference.
> > --Grant permissions to specified role
> > SET NOCOUNT ON
> > DECLARE @.GrantStatement nvarchar(500)
> > DECLARE @.LastError int
> > DECLARE GrantStatements CURSOR LOCAL FAST_FORWARD FOR
> > SELECT
> > N'GRANT ALL ON ' +
> > QUOTENAME(USER_NAME([ob].[uid])) + '.' + QUOTENAME([ob].[name]) +
> > ' TO MyRole'
> > FROM
> > sysobjects ob
> > WHERE
> > OBJECTPROPERTY([ob].[id], 'IsMSShipped') = 0 AND
> > (OBJECTPROPERTY([ob].[id], 'IsProcedure') = 1 OR
> > OBJECTPROPERTY([ob].[id], 'IsUserTable') = 1 OR
> > OBJECTPROPERTY([ob].[id], 'IsView') = 1 OR
> > OBJECTPROPERTY([ob].[id], 'IsInlineFunction') = 1 OR
> > OBJECTPROPERTY([ob].[id], 'IsScalarFunction') = 1 OR
> > OBJECTPROPERTY([ob].[id], 'IsTableFunction') = 1)
> > OPEN GrantStatements
> > WHILE 1 = 1
> > BEGIN
> > FETCH NEXT FROM GrantStatements INTO @.GrantStatement
> > IF @.@.FETCH_STATUS = -1 BREAK
> > RAISERROR (@.GrantStatement, 0, 1) WITH NOWAIT
> > EXECUTE sp_ExecuteSQL @.GrantStatement
> > END
> > CLOSE GrantStatements
> > DEALLOCATE GrantStatements
> > --
> > Hope this helps.
> > Dan Guzman
> > SQL Server MVP
> > "Blake" <blakesell@.hotmail.com> wrote in message
> > news:a8ceff1a.0407171331.35ed65bc@.posting.google.c om...
> > > Dan,
> > > Thanks for the reply.
> > > Can I do this automatically witht the existing database role "public"
> > > sine that has already been grated permission to all objects?
> > > > Since there are hundreds of users, is there a way I can get around
> > > having to grantlogin for every MyDomain\MyUser?
> > > > Thanks
> > > > > "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
> > news:<x3bKc.5326$mL5.1112@.newsread1.news.pas.earthlink.n et>...
> > > > By default, only 'BUILTIN\Administrators' can access SQL Server and
this
> > is
> > > > as sysadmin. You can grant a Windows login access to SQL Server
with:
> > > > > > EXEC sp_grantlogin 'MyDomain\MyUser'
> > > > > > Then, grant the login access to your database:
> > > > > > USE NewsBaseDataSQL
> > > > EXEC sp_grantdbaccess 'MyDomain\MyUser'
> > > > > > Users will need permissions on those database objects used by your
> > > > application. A best practice is to create database roles and grant
> > required
> > > > permissions to roles. You can then control user permissions via
role
> > > > membership:
> > > > > > USE NewsBaseDataSQL
> > > > EXEC sp_addrole 'MyRole'
> > > > GRANT ALL ON MyTable TO MyRole
> > > > > > EXEC sp_addrolemember 'MyRole', 'MyDomain\MyUser'
> > > > > > --
> > > > Hope this helps.
> > > > > > Dan Guzman
> > > > SQL Server MVP
> > > > > > "Blake" <blakesell@.hotmail.com> wrote in message
> > > > news:a8ceff1a.0407170635.48ae2b44@.posting.google.c om...
> > > > > I have created an Access2K front end application that connects to
a
> > > > > SQLServer2K backend. I use this vba code to create the connection
from
> > > > > the Access app:
> > > > > > > > Dim strConnect As String
> > > > > 'make sure all previous connections are closed:
> > > > > CurrentProject.OpenConnection "Provider="
> > > > > > > > 'create new connection string to server:
> > > > > strConnect = "PROVIDER=SQLOLEDB.1;INTEGRATED SECURITY=SSPI;PERSIST
> > > > > SECURITY INFO=FALSE;INITIAL CATALOG=NewsBaseDataSQL;DATA
> > > > > SOURCE=nycvnewsbas01"
> > > > > > > > CurrentProject.OpenConnection strConnect
> > > > > > > > Everything functions.
> > > > > > > > The problem is the users cannot make the connection if they are
not
> > > > > part of the local admins group on the server. As soon as they are
> > > > > removed from the local admins group their conenctions fail.
> > > > > > > > How do I remedy this?|||Dan,
Perhaps a stupid question...
Is this shoot and forget (I loop through my user tables once and then
the user is always permissioned) or do I have to run through this
every time the user logs on?
b

"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message news:<OcZKc.7563$mL5.2573@.newsread1.news.pas.earthlink.n et>...
> > What I gather then is that all I need to do is to grant each Windows
> > user/domain permission to SQL Server by looping through my user table
> > and running the following for each user:
> > EXEC sp_grantlogin 'MyDomain\MyUser'
> Yes, and also:
> USE MyDatabase
> EXEC sp_grantdbaccess 'MyDomain\MyUser'
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Blake" <blakesell@.hotmail.com> wrote in message
> news:a8ceff1a.0407190926.527fca70@.posting.google.c om...
> > Dan,
> > Thanks for your responses.
> > In this database, access to forms is controlled by Windows signon and
> > the public role has persmission to run all stored procedures. For its
> > purposes, this level of security works fine. So at this point I don't
> > need to create a new role.
> > What I gather then is that all I need to do is to grant each Windows
> > user/domain permission to SQL Server by looping through my user table
> > and running the following for each user:
> > EXEC sp_grantlogin 'MyDomain\MyUser'
> > I this correct?
> > "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
> news:<gmwKc.6281$mL5.5101@.newsread1.news.pas.earthlink.n et>...
> > > > Dan,
> > > > Thanks for the reply.
> > > > Can I do this automatically witht the existing database role "public"
> > > > sine that has already been grated permission to all objects?
> > > > All users are automatically members of the public role so granting a
> user
> > > access to this database will provide the needed permissions. However,
> you
> > > might consider creating your own roles so that you can provide different
> > > levels of permissions (e.g. read-only or read-write) and control this
> with
> > > role membership. Below is a script than can setup role-based object
> > > security on all database objects that you can run to initially setup
> > > security and after schema changes.
> > > > > > > Since there are hundreds of users, is there a way I can get around
> > > > having to grantlogin for every MyDomain\MyUser?
> > > > One method is to create a local Windows group on your SQL box and grant
> that
> > > group access to SQL Server and your database. You can then add the
> desired
> > > users to that local group so they are authorized via group membership.
> This
> > > method allows you to control SQL Server access at the OS level rather
> than
> > > SQL Server but note that is about the same amount of work as adding
> > > individual users to SQL Server; it mostly depends on your personal
> > > preference.
> > > > > --Grant permissions to specified role
> > > SET NOCOUNT ON
> > > > DECLARE @.GrantStatement nvarchar(500)
> > > DECLARE @.LastError int
> > > > DECLARE GrantStatements CURSOR LOCAL FAST_FORWARD FOR
> > > SELECT
> > > N'GRANT ALL ON ' +
> > > QUOTENAME(USER_NAME([ob].[uid])) + '.' + QUOTENAME([ob].[name]) +
> > > ' TO MyRole'
> > > FROM
> > > sysobjects ob
> > > WHERE
> > > OBJECTPROPERTY([ob].[id], 'IsMSShipped') = 0 AND
> > > (OBJECTPROPERTY([ob].[id], 'IsProcedure') = 1 OR
> > > OBJECTPROPERTY([ob].[id], 'IsUserTable') = 1 OR
> > > OBJECTPROPERTY([ob].[id], 'IsView') = 1 OR
> > > OBJECTPROPERTY([ob].[id], 'IsInlineFunction') = 1 OR
> > > OBJECTPROPERTY([ob].[id], 'IsScalarFunction') = 1 OR
> > > OBJECTPROPERTY([ob].[id], 'IsTableFunction') = 1)
> > > OPEN GrantStatements
> > > WHILE 1 = 1
> > > BEGIN
> > > FETCH NEXT FROM GrantStatements INTO @.GrantStatement
> > > IF @.@.FETCH_STATUS = -1 BREAK
> > > RAISERROR (@.GrantStatement, 0, 1) WITH NOWAIT
> > > EXECUTE sp_ExecuteSQL @.GrantStatement
> > > END
> > > CLOSE GrantStatements
> > > DEALLOCATE GrantStatements
> > > > --
> > > Hope this helps.
> > > > Dan Guzman
> > > SQL Server MVP
> > > > "Blake" <blakesell@.hotmail.com> wrote in message
> > > news:a8ceff1a.0407171331.35ed65bc@.posting.google.c om...
> > > > Dan,
> > > > Thanks for the reply.
> > > > Can I do this automatically witht the existing database role "public"
> > > > sine that has already been grated permission to all objects?
> > > > > > Since there are hundreds of users, is there a way I can get around
> > > > having to grantlogin for every MyDomain\MyUser?
> > > > > > Thanks
> > > > > > > > "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
> news:<x3bKc.5326$mL5.1112@.newsread1.news.pas.earthlink.n et>...
> > > > > By default, only 'BUILTIN\Administrators' can access SQL Server and
> this
> is
> > > > > as sysadmin. You can grant a Windows login access to SQL Server
> with:
> > > > > > > > EXEC sp_grantlogin 'MyDomain\MyUser'
> > > > > > > > Then, grant the login access to your database:
> > > > > > > > USE NewsBaseDataSQL
> > > > > EXEC sp_grantdbaccess 'MyDomain\MyUser'
> > > > > > > > Users will need permissions on those database objects used by your
> > > > > application. A best practice is to create database roles and grant
> required
> > > > > permissions to roles. You can then control user permissions via
> role
> > > > > membership:
> > > > > > > > USE NewsBaseDataSQL
> > > > > EXEC sp_addrole 'MyRole'
> > > > > GRANT ALL ON MyTable TO MyRole
> > > > > > > > EXEC sp_addrolemember 'MyRole', 'MyDomain\MyUser'
> > > > > > > > --
> > > > > Hope this helps.
> > > > > > > > Dan Guzman
> > > > > SQL Server MVP
> > > > > > > > "Blake" <blakesell@.hotmail.com> wrote in message
> > > > > news:a8ceff1a.0407170635.48ae2b44@.posting.google.c om...
> > > > > > I have created an Access2K front end application that connects to
> a
> > > > > > SQLServer2K backend. I use this vba code to create the connection
> from
> > > > > > the Access app:
> > > > > > > > > > Dim strConnect As String
> > > > > > 'make sure all previous connections are closed:
> > > > > > CurrentProject.OpenConnection "Provider="
> > > > > > > > > > 'create new connection string to server:
> > > > > > strConnect = "PROVIDER=SQLOLEDB.1;INTEGRATED SECURITY=SSPI;PERSIST
> > > > > > SECURITY INFO=FALSE;INITIAL CATALOG=NewsBaseDataSQL;DATA
> > > > > > SOURCE=nycvnewsbas01"
> > > > > > > > > > CurrentProject.OpenConnection strConnect
> > > > > > > > > > Everything functions.
> > > > > > > > > > The problem is the users cannot make the connection if they are
> not
> > > > > > part of the local admins group on the server. As soon as they are
> > > > > > removed from the local admins group their conenctions fail.
> > > > > > > > > > How do I remedy this?|||Dan,
WIll this cut it?

Create Procedure "sp_GrantUSerAccess"
@.DomainUser nvarchar(200) /*where @.DomainUser = 'Domain/User' */
AS
set nocount on
EXEC sp_grantlogin @.DomainUser

USE myDatabaseName
EXEC sp_grantdbaccess @.DomainUser

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Blake" <blakesell@.hotmail.com> wrote in message
news:a8ceff1a.0407201037.386d74db@.posting.google.c om...
> Dan,
> WIll this cut it?
> Create Procedure "sp_GrantUSerAccess"
> @.DomainUser nvarchar(200) /*where @.DomainUser = 'Domain/User' */
> AS
> set nocount on
> EXEC sp_grantlogin @.DomainUser
> USE myDatabaseName
> EXEC sp_grantdbaccess @.DomainUser
>
> "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:<OcZKc.7563$mL5.2573@.newsread1.news.pas.earthlink.n et>...
> > > What I gather then is that all I need to do is to grant each Windows
> > > user/domain permission to SQL Server by looping through my user table
> > > and running the following for each user:
> > > > EXEC sp_grantlogin 'MyDomain\MyUser'
> > Yes, and also:
> > USE MyDatabase
> > EXEC sp_grantdbaccess 'MyDomain\MyUser'
> > --
> > Hope this helps.
> > Dan Guzman
> > SQL Server MVP
> > "Blake" <blakesell@.hotmail.com> wrote in message
> > news:a8ceff1a.0407190926.527fca70@.posting.google.c om...
> > > Dan,
> > > Thanks for your responses.
> > > In this database, access to forms is controlled by Windows signon and
> > > the public role has persmission to run all stored procedures. For its
> > > purposes, this level of security works fine. So at this point I don't
> > > need to create a new role.
> > > What I gather then is that all I need to do is to grant each Windows
> > > user/domain permission to SQL Server by looping through my user table
> > > and running the following for each user:
> > > > EXEC sp_grantlogin 'MyDomain\MyUser'
> > > > I this correct?
> > > > > > "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
> > news:<gmwKc.6281$mL5.5101@.newsread1.news.pas.earthlink.n et>...
> > > > > Dan,
> > > > > Thanks for the reply.
> > > > > Can I do this automatically witht the existing database role
"public"
> > > > > sine that has already been grated permission to all objects?
> > > > > > All users are automatically members of the public role so granting a
> > user
> > > > access to this database will provide the needed permissions.
However,
> > you
> > > > might consider creating your own roles so that you can provide
different
> > > > levels of permissions (e.g. read-only or read-write) and control
this
> > with
> > > > role membership. Below is a script than can setup role-based object
> > > > security on all database objects that you can run to initially setup
> > > > security and after schema changes.
> > > > > > > > > > Since there are hundreds of users, is there a way I can get around
> > > > > having to grantlogin for every MyDomain\MyUser?
> > > > > > One method is to create a local Windows group on your SQL box and
grant
> > that
> > > > group access to SQL Server and your database. You can then add the
> > desired
> > > > users to that local group so they are authorized via group
membership.
> > This
> > > > method allows you to control SQL Server access at the OS level
rather
> > than
> > > > SQL Server but note that is about the same amount of work as adding
> > > > individual users to SQL Server; it mostly depends on your personal
> > > > preference.
> > > > > > > > --Grant permissions to specified role
> > > > SET NOCOUNT ON
> > > > > > DECLARE @.GrantStatement nvarchar(500)
> > > > DECLARE @.LastError int
> > > > > > DECLARE GrantStatements CURSOR LOCAL FAST_FORWARD FOR
> > > > SELECT
> > > > N'GRANT ALL ON ' +
> > > > QUOTENAME(USER_NAME([ob].[uid])) + '.' + QUOTENAME([ob].[name])
+
> > > > ' TO MyRole'
> > > > FROM
> > > > sysobjects ob
> > > > WHERE
> > > > OBJECTPROPERTY([ob].[id], 'IsMSShipped') = 0 AND
> > > > (OBJECTPROPERTY([ob].[id], 'IsProcedure') = 1 OR
> > > > OBJECTPROPERTY([ob].[id], 'IsUserTable') = 1 OR
> > > > OBJECTPROPERTY([ob].[id], 'IsView') = 1 OR
> > > > OBJECTPROPERTY([ob].[id], 'IsInlineFunction') = 1 OR
> > > > OBJECTPROPERTY([ob].[id], 'IsScalarFunction') = 1 OR
> > > > OBJECTPROPERTY([ob].[id], 'IsTableFunction') = 1)
> > > > OPEN GrantStatements
> > > > WHILE 1 = 1
> > > > BEGIN
> > > > FETCH NEXT FROM GrantStatements INTO @.GrantStatement
> > > > IF @.@.FETCH_STATUS = -1 BREAK
> > > > RAISERROR (@.GrantStatement, 0, 1) WITH NOWAIT
> > > > EXECUTE sp_ExecuteSQL @.GrantStatement
> > > > END
> > > > CLOSE GrantStatements
> > > > DEALLOCATE GrantStatements
> > > > > > --
> > > > Hope this helps.
> > > > > > Dan Guzman
> > > > SQL Server MVP
> > > > > > "Blake" <blakesell@.hotmail.com> wrote in message
> > > > news:a8ceff1a.0407171331.35ed65bc@.posting.google.c om...
> > > > > Dan,
> > > > > Thanks for the reply.
> > > > > Can I do this automatically witht the existing database role
"public"
> > > > > sine that has already been grated permission to all objects?
> > > > > > > > Since there are hundreds of users, is there a way I can get around
> > > > > having to grantlogin for every MyDomain\MyUser?
> > > > > > > > Thanks
> > > > > > > > > > > "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
> > news:<x3bKc.5326$mL5.1112@.newsread1.news.pas.earthlink.n et>...
> > > > > > By default, only 'BUILTIN\Administrators' can access SQL Server
and
> > this
> > is
> > > > > > as sysadmin. You can grant a Windows login access to SQL Server
> > with:
> > > > > > > > > > EXEC sp_grantlogin 'MyDomain\MyUser'
> > > > > > > > > > Then, grant the login access to your database:
> > > > > > > > > > USE NewsBaseDataSQL
> > > > > > EXEC sp_grantdbaccess 'MyDomain\MyUser'
> > > > > > > > > > Users will need permissions on those database objects used by
your
> > > > > > application. A best practice is to create database roles and
grant
> > required
> > > > > > permissions to roles. You can then control user permissions via
> > role
> > > > > > membership:
> > > > > > > > > > USE NewsBaseDataSQL
> > > > > > EXEC sp_addrole 'MyRole'
> > > > > > GRANT ALL ON MyTable TO MyRole
> > > > > > > > > > EXEC sp_addrolemember 'MyRole', 'MyDomain\MyUser'
> > > > > > > > > > --
> > > > > > Hope this helps.
> > > > > > > > > > Dan Guzman
> > > > > > SQL Server MVP
> > > > > > > > > > "Blake" <blakesell@.hotmail.com> wrote in message
> > > > > > news:a8ceff1a.0407170635.48ae2b44@.posting.google.c om...
> > > > > > > I have created an Access2K front end application that connects
to
> > a
> > > > > > > SQLServer2K backend. I use this vba code to create the
connection
> > from
> > > > > > > the Access app:
> > > > > > > > > > > > Dim strConnect As String
> > > > > > > 'make sure all previous connections are closed:
> > > > > > > CurrentProject.OpenConnection "Provider="
> > > > > > > > > > > > 'create new connection string to server:
> > > > > > > strConnect = "PROVIDER=SQLOLEDB.1;INTEGRATED
SECURITY=SSPI;PERSIST
> > > > > > > SECURITY INFO=FALSE;INITIAL CATALOG=NewsBaseDataSQL;DATA
> > > > > > > SOURCE=nycvnewsbas01"
> > > > > > > > > > > > CurrentProject.OpenConnection strConnect
> > > > > > > > > > > > Everything functions.
> > > > > > > > > > > > The problem is the users cannot make the connection if they
are
> > not
> > > > > > > part of the local admins group on the server. As soon as they
are
> > > > > > > removed from the local admins group their conenctions fail.
> > > > > > > > > > > > How do I remedy this?|||Permissions are remembered. You only need to grant permissions again if you
drop and recreate the object.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Blake" <blakesell@.hotmail.com> wrote in message
news:a8ceff1a.0407201030.4beca352@.posting.google.c om...
> Dan,
> Perhaps a stupid question...
> Is this shoot and forget (I loop through my user tables once and then
> the user is always permissioned) or do I have to run through this
> every time the user logs on?
> b
>
> "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:<OcZKc.7563$mL5.2573@.newsread1.news.pas.earthlink.n et>...
> > > What I gather then is that all I need to do is to grant each Windows
> > > user/domain permission to SQL Server by looping through my user table
> > > and running the following for each user:
> > > > EXEC sp_grantlogin 'MyDomain\MyUser'
> > Yes, and also:
> > USE MyDatabase
> > EXEC sp_grantdbaccess 'MyDomain\MyUser'
> > --
> > Hope this helps.
> > Dan Guzman
> > SQL Server MVP
> > "Blake" <blakesell@.hotmail.com> wrote in message
> > news:a8ceff1a.0407190926.527fca70@.posting.google.c om...
> > > Dan,
> > > Thanks for your responses.
> > > In this database, access to forms is controlled by Windows signon and
> > > the public role has persmission to run all stored procedures. For its
> > > purposes, this level of security works fine. So at this point I don't
> > > need to create a new role.
> > > What I gather then is that all I need to do is to grant each Windows
> > > user/domain permission to SQL Server by looping through my user table
> > > and running the following for each user:
> > > > EXEC sp_grantlogin 'MyDomain\MyUser'
> > > > I this correct?
> > > > > > "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
> > news:<gmwKc.6281$mL5.5101@.newsread1.news.pas.earthlink.n et>...
> > > > > Dan,
> > > > > Thanks for the reply.
> > > > > Can I do this automatically witht the existing database role
"public"
> > > > > sine that has already been grated permission to all objects?
> > > > > > All users are automatically members of the public role so granting a
> > user
> > > > access to this database will provide the needed permissions.
However,
> > you
> > > > might consider creating your own roles so that you can provide
different
> > > > levels of permissions (e.g. read-only or read-write) and control
this
> > with
> > > > role membership. Below is a script than can setup role-based object
> > > > security on all database objects that you can run to initially setup
> > > > security and after schema changes.
> > > > > > > > > > Since there are hundreds of users, is there a way I can get around
> > > > > having to grantlogin for every MyDomain\MyUser?
> > > > > > One method is to create a local Windows group on your SQL box and
grant
> > that
> > > > group access to SQL Server and your database. You can then add the
> > desired
> > > > users to that local group so they are authorized via group
membership.
> > This
> > > > method allows you to control SQL Server access at the OS level
rather
> > than
> > > > SQL Server but note that is about the same amount of work as adding
> > > > individual users to SQL Server; it mostly depends on your personal
> > > > preference.
> > > > > > > > --Grant permissions to specified role
> > > > SET NOCOUNT ON
> > > > > > DECLARE @.GrantStatement nvarchar(500)
> > > > DECLARE @.LastError int
> > > > > > DECLARE GrantStatements CURSOR LOCAL FAST_FORWARD FOR
> > > > SELECT
> > > > N'GRANT ALL ON ' +
> > > > QUOTENAME(USER_NAME([ob].[uid])) + '.' + QUOTENAME([ob].[name])
+
> > > > ' TO MyRole'
> > > > FROM
> > > > sysobjects ob
> > > > WHERE
> > > > OBJECTPROPERTY([ob].[id], 'IsMSShipped') = 0 AND
> > > > (OBJECTPROPERTY([ob].[id], 'IsProcedure') = 1 OR
> > > > OBJECTPROPERTY([ob].[id], 'IsUserTable') = 1 OR
> > > > OBJECTPROPERTY([ob].[id], 'IsView') = 1 OR
> > > > OBJECTPROPERTY([ob].[id], 'IsInlineFunction') = 1 OR
> > > > OBJECTPROPERTY([ob].[id], 'IsScalarFunction') = 1 OR
> > > > OBJECTPROPERTY([ob].[id], 'IsTableFunction') = 1)
> > > > OPEN GrantStatements
> > > > WHILE 1 = 1
> > > > BEGIN
> > > > FETCH NEXT FROM GrantStatements INTO @.GrantStatement
> > > > IF @.@.FETCH_STATUS = -1 BREAK
> > > > RAISERROR (@.GrantStatement, 0, 1) WITH NOWAIT
> > > > EXECUTE sp_ExecuteSQL @.GrantStatement
> > > > END
> > > > CLOSE GrantStatements
> > > > DEALLOCATE GrantStatements
> > > > > > --
> > > > Hope this helps.
> > > > > > Dan Guzman
> > > > SQL Server MVP
> > > > > > "Blake" <blakesell@.hotmail.com> wrote in message
> > > > news:a8ceff1a.0407171331.35ed65bc@.posting.google.c om...
> > > > > Dan,
> > > > > Thanks for the reply.
> > > > > Can I do this automatically witht the existing database role
"public"
> > > > > sine that has already been grated permission to all objects?
> > > > > > > > Since there are hundreds of users, is there a way I can get around
> > > > > having to grantlogin for every MyDomain\MyUser?
> > > > > > > > Thanks
> > > > > > > > > > > "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
> > news:<x3bKc.5326$mL5.1112@.newsread1.news.pas.earthlink.n et>...
> > > > > > By default, only 'BUILTIN\Administrators' can access SQL Server
and
> > this
> > is
> > > > > > as sysadmin. You can grant a Windows login access to SQL Server
> > with:
> > > > > > > > > > EXEC sp_grantlogin 'MyDomain\MyUser'
> > > > > > > > > > Then, grant the login access to your database:
> > > > > > > > > > USE NewsBaseDataSQL
> > > > > > EXEC sp_grantdbaccess 'MyDomain\MyUser'
> > > > > > > > > > Users will need permissions on those database objects used by
your
> > > > > > application. A best practice is to create database roles and
grant
> > required
> > > > > > permissions to roles. You can then control user permissions via
> > role
> > > > > > membership:
> > > > > > > > > > USE NewsBaseDataSQL
> > > > > > EXEC sp_addrole 'MyRole'
> > > > > > GRANT ALL ON MyTable TO MyRole
> > > > > > > > > > EXEC sp_addrolemember 'MyRole', 'MyDomain\MyUser'
> > > > > > > > > > --
> > > > > > Hope this helps.
> > > > > > > > > > Dan Guzman
> > > > > > SQL Server MVP
> > > > > > > > > > "Blake" <blakesell@.hotmail.com> wrote in message
> > > > > > news:a8ceff1a.0407170635.48ae2b44@.posting.google.c om...
> > > > > > > I have created an Access2K front end application that connects
to
> > a
> > > > > > > SQLServer2K backend. I use this vba code to create the
connection
> > from
> > > > > > > the Access app:
> > > > > > > > > > > > Dim strConnect As String
> > > > > > > 'make sure all previous connections are closed:
> > > > > > > CurrentProject.OpenConnection "Provider="
> > > > > > > > > > > > 'create new connection string to server:
> > > > > > > strConnect = "PROVIDER=SQLOLEDB.1;INTEGRATED
SECURITY=SSPI;PERSIST
> > > > > > > SECURITY INFO=FALSE;INITIAL CATALOG=NewsBaseDataSQL;DATA
> > > > > > > SOURCE=nycvnewsbas01"
> > > > > > > > > > > > CurrentProject.OpenConnection strConnect
> > > > > > > > > > > > Everything functions.
> > > > > > > > > > > > The problem is the users cannot make the connection if they
are
> > not
> > > > > > > part of the local admins group on the server. As soon as they
are
> > > > > > > removed from the local admins group their conenctions fail.
> > > > > > > > > > > > How do I remedy this?|||
i saw a ton of replies, and wonder what im missing?
why dont you create a user in SQL and have your connection string
connect as that user? then put the credentials in the connection
string. that way any user can use the application without having to
have access.

email: dguzman@.mccarter.com if you dont understand.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Sunday, March 11, 2012

Access Update Query Problem

Are you comparing apples to apples? In your VBA code, are you
executing the same join syntax that you are trying to excute through
the Access GUI. I have a suspicion that you are not, and the reason
the query is bombing out on you is that the join is forcing your
workstation to bring over all of the SQL Server data and working on the
update data client side. However, without code to examine, no freaking
clue :)
StuOn 7/28/2005 5:13 PM, Stu wrote:
> Are you comparing apples to apples? In your VBA code, are you
> executing the same join syntax that you are trying to excute through
> the Access GUI. I have a suspicion that you are not, and the reason
> the query is bombing out on you is that the join is forcing your
> workstation to bring over all of the SQL Server data and working on the
> update data client side. However, without code to examine, no freaking
> clue :)
> Stu
>
Access Query def:
UPDATE dbo_Order_Line_Invoice INNER JOIN tblOrderTypeValues ON
(dbo_Order_Line_Invoice.Cono = tblOrderTypeValues.cono) AND
(dbo_Order_Line_Invoice.LineNum = tblOrderTypeValues.lineno) AND
(dbo_Order_Line_Invoice.OrderNumber = tblOrderTypeValues.OrderNum) SET
dbo_Order_Line_Invoice.SXUser10 = tblOrderTypeValues.ordertype;
dbo_Order_Line_Invoice is the SQL server table
VBA code that works:
Sub UpdateOrdertype()
Dim rsOrderType As Recordset
Dim strSQL As String
Dim intCounter As Long
Set rsOrderType = CurrentDb.OpenRecordset("tblOrderTypeValues")
Do While Not rsOrderType.EOF
intCounter = intCounter + 1
Debug.Print intCounter: DoEvents
strSQL = "UPDATE dbo_Order_Line_Invoice SET
dbo_Order_Line_Invoice.SxUser10 = '" & rsOrderType.OrderType &
"' WHERE (dbo_Order_Line_Invoice.Cono = 1) AND
(dbo_Order_Line_Invoice.OrderNumber = '" & rsOrderType.OrderNum
& "' ) AND (dbo_Order_Line_Invoice.LineNum =
" & rsOrderType.Lineno & ");"
CurrentDb.Execute (strSQL)
rsOrderType.MoveNext
Loop
rsOrderType.Close
MsgBox "Done updating ordertype."
End Sub

Thursday, March 8, 2012

Access to SQL server

Hi,

I have to write an VB.NET application which imports data from an Access database to an Ms SQL server. Is there any code for this kind of task.

Thanks.

Take a look at this post

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=320478&SiteID=1

Connection string to retrieve from Access DB is as follows

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Test.mdb;"

Hope this helps

Access To SQL

Hi
I want to convert my code from access to sql, i need to know how to write the next words in SQL:
OleDbParameter
OleDbType - I got an error when i wrote SqlType.BSTR.
Thank's.You probably want to use SqlDbType.NVarChar

Access to report objects fom custom code

I can write custom code in my report through the "Report -> Properties"
dialog.
Can this code access any of the report object such as data or Custom Report
Items ?
What context is this code running under ? I tried to figure it out using
reflector and got to a dead end on a internal class called
ReportExprHostImpl.CustomCodeProxy that doesn't seem to have a reference to
anything in the report.
--
Francisco Padron
www.chartfx.comhmm...In the past what I've done is setup c# classes and just
referenced them from the report...
try this: http://msdn2.microsoft.com/en-us/library/ms155798.aspx
Hope this helps!
--
Ben Sullins
http://bensullins.com|||Unfortunatley it is not possible to access any report objects. You'll need
to pass anything you need as parameters into your functions.
"news.microsoft.com" wrote:
> I can write custom code in my report through the "Report -> Properties"
> dialog.
> Can this code access any of the report object such as data or Custom Report
> Items ?
> What context is this code running under ? I tried to figure it out using
> reflector and got to a dead end on a internal class called
> ReportExprHostImpl.CustomCodeProxy that doesn't seem to have a reference to
> anything in the report.
> --
> Francisco Padron
> www.chartfx.com
>
>

Tuesday, March 6, 2012

Access to datasources in custom code

Hi there!
I'm wondering if there is any way to access the datasources inside
your custom code of a VS 2005.NET report. My datasources are made up
of custom business objects, thus you can imagine wanting to manipulate
them from the report. In this particular case I want to build up a TOC
during the build of the report, which right now I build up in a
database table.
DaveYou would have to write a data processing extension (non-trivial). Read up
on this in Books OnLine.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"dnote" <mrdnote@.yahoo.com> wrote in message
news:1170692326.111173.53510@.s48g2000cws.googlegroups.com...
> Hi there!
> I'm wondering if there is any way to access the datasources inside
> your custom code of a VS 2005.NET report. My datasources are made up
> of custom business objects, thus you can imagine wanting to manipulate
> them from the report. In this particular case I want to build up a TOC
> during the build of the report, which right now I build up in a
> database table.
> Dave
>

Access to dataset fields in Code window

Hi,
Is it, or will it be possible to use dataset fields in expressions in the
code window. We are converting formulas which are too complex for an IIF
expression in the report, and would prefer not to create too many sprocs in
the database.
Cheers,
MattYou cannot access fields directly in the code window. You need to pass-in
the field values as arguments to your custom code function. The following
article shows how to do this:
http://odetocode.com/Articles/130.aspx
A more detailed discussion is provided in the following MSDN article:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/ERSCstCode.asp?frame=true
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Matt" <Matt@.discussions.microsoft.com> wrote in message
news:3EFF6484-97F2-4744-BB6F-6DC35F084D7B@.microsoft.com...
> Hi,
> Is it, or will it be possible to use dataset fields in expressions in the
> code window. We are converting formulas which are too complex for an IIF
> expression in the report, and would prefer not to create too many sprocs
> in
> the database.
> Cheers,
> Matt

Access takes 24-35 seconds while SQL Server takes only 2-3 seconds

I test my C# applicaiton both with Access and SQL Server database. I note
that the following code snippets takes 24-35 seconds with Access database
while takes only 2-3 seconds with SQL Server to fetch same number of records
(80,000 records).
//with Access Database
this.da = new OdbcDataAdapter("select Barcode, Description from
Items_Detail", DB.cn);
//with SQL Server
this.da = new SqlDataAdapter("select Barcode, Description from
Items_Detail", DB.cn);
//common code
this.da.Fill(this.ds, "Get_Quantity_Result");
grd_All_Recs.SetDataBinding(this.ds, "Get_Quantity_Result");
for my customer simplicity i want to give him solution with Access database
but with this un-acceptable delay i can't do this.
Please help in identifying that why it is taking much time to fetch records
from Access database.
Arif.Why Access? Why not SQL 2005 Express or SQL 2000 MSDE?
SQL Express and MSDE are free with most benefits of regular SQL Server
editions.
http://msdn.microsoft.com/library/d...r />
rview.asp
http://msdn.microsoft.com/library/d...br />
67ax.asp
ML

Friday, February 24, 2012

access report builder from custom code

I am writing a .NET app, I'd like the user to have access to the report
builder to make ad-hoc reports, how would I go about doing this from inside
my application? thanks!You can use "reportbuilder.exe" file but I have not tried running this exe,
where the RS environment is not there. Moreover this is a part of SSRS and
ofcourse Sql server so probabily licensing issues will be there because this
is a seperate tool, but will have to check whether it works out of the SS &
SSRS environment first.
Amarnath
"Smokey Grindle" wrote:
> I am writing a .NET app, I'd like the user to have access to the report
> builder to make ad-hoc reports, how would I go about doing this from inside
> my application? thanks!
>
>

Access read only variables in Script Component in a Dataflow

I have a set of comma separated variables in a Script Component list. I want to access them in Script code and use them to build string in the code.Use "Me.Variables.variableName"|||I was I am trying to do the same thing you mentioned but I am the following error

[Script Component [1463]] Error: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. > Microsoft.SqlServer.Dts.Pipeline.ReadOnlyVariablesNotAvailableException: The collection of variables locked for read access is not available at this point. at Microsoft.SqlServer.Dts.Pipeline.ScriptComponent.get_ReadOnlyVariables() at ScriptComponent_720e2ab81e00498aa9bf2e9d8af40422.Variables.get_LogPath() in dts://Scripts/ScriptComponent_720e2ab81e00498aa9bf2e9d8af40422/ComponentWrapper:line 72 at ScriptComponent_720e2ab81e00498aa9bf2e9d8af40422.ScriptMain..ctor() in dts://Scripts/ScriptComponent_720e2ab81e00498aa9bf2e9d8af40422/ScriptMain:line 78 End of inner exception stack trace at System.RuntimeTypeHandle.CreateInstance(RuntimeType type, Boolean publicOnly, Boolean noCheck, Boolean& canBeCached, RuntimeMethodHandle& ctor, Boolean& bNeedSecurityCheck) at System.RuntimeType.CreateInstanceSlow(Boolean publicOnly, Boolean fillCache) at System.RuntimeType.CreateInstanceImpl(Boolean publicOnly, Boolean skipVisibilityChecks, Boolean fillCache) at System.Activator.CreateInstance(Type type, Boolean nonPublic) at System.RuntimeType.CreateInstanceImpl(BindingFlags bindingAttr, Binder binder, Object[] args, CultureInfo culture, Object[] activationAttributes) at System.Activator.CreateInstance(Type type, BindingFlags bindingAttr, Binder binder, Object[] args, CultureInfo culture, Object[] activationAttributes) at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.CreateUserComponent()|||Script Component or Script Task?|||Script Component
|||

Rohit Ghule wrote:
Script Component

Does your script component have all of the Imports?

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Public Class ScriptMain
Inherits UserComponent

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

Dim MaximumKey As Int32 = Me.Variables.MaxKey ' Grab value of MaxKey which was passed in via ReadOnlyVariables

Row.MaxKey = MaximumKey 'Assign the output field of "MaxKey" to the value of the passed in variable
End Sub

End Class|||Searching this forum turned up more information: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=201158&SiteID=1

I think the key is to look at your variables in the Public Overrides Sub section.|||

Most methods in a script task are overrides of base class methods, so you need a bit more info. The PreExecute, which is public, and overridden, so starts with "Public Overrides PreExecute(.." supports the variable manager stuff, but may not always be what you want. If you really need to access variable at a row level then you can, see this thread-

Re: R/W access problem with var in script Component - MSDN Forums
(http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=956181&SiteID=1)

Access Query: Need to return a month name instead of number in the query result

Here's part of my code for my Access query:

SELECT [Enter Month 1 - 12] AS ["Month"], Sum(IIf([ProductName] Like 'FHA30*',1,0)) AS ["FHA 30"]
FROM Calculations2
WHERE (((Month([LockDate]))=[enter month 1 - 12]));

It works fine, but I'm wanting it to return the name of the month in the first column instead of the number that the user inputs. I have another table that simply has a column with the month number and a column with the month name so I thought I might be able to have a second select statment with somewhere with in the SELECT [Enter Month 1 - 12] AS ["Month"], part, but I don't know how to do that. I'm real slow when it comes to coding so any help is appreciated.I did something similar but it was to return the day name, this might be of some help:

SET SERVEROUTPUT ON;

DECLARE

v_date VARCHAR2 (15);

BEGIN

SELECT TO_CHAR(sysdate, 'day')
INTO v_date
FROM dual;

DBMS_OUTPUT.PUT_LINE('The Day Today Is: '|| v_date);

END;
.|||If I understand your code, that will return the system date. In my query, the user inputs what month they want the query to run on by inputting the month number, like 1 for January. Right now, my first SELECT statement returns whatever number they input in the first column just so when it returns the query you can use that if you put the qurey results in excel or something. I just want it to convert that number into the name January. I hope I'm explaining it correctly.

Sunday, February 19, 2012

Access migration to SQL Server

Hello,
We maintain a VB6 front-end application using an Access 2000 database. All
code and forms are in VB6. The program also uses several queries/reports
defined in Access. For corporate reasons we must move to SQL Server right
away. Based on our configuration, we're hoping someone can give us a feel
for what's involved here. Since we're only using Access for the data we're
not sure what an "upsizing" would entail. Can someone point us in the right
direction, tell us the main gotchas, etc. Thanks a lot.
Teri WelchHi
If you are going to remove access completely your access queries will need
to be re-written as stored procedures or views and the reports rewritten in
(say) VB. Alternatively you can use an Access Data Project where most of the
code can remain the same.
The access upgrade wizard will migrate most things, although it can run into
problems if you are using something that is more complex.
I suggest that you create a test system and copy your database and
application onto it. Then you try the upgrade, and you will get a feel for
what is involved and how much can be easily migrated.
John
"Teri Welch" <trwlch@.yahoo.com> wrote in message
news:xLuwd.5516$jn.1131@.lakeread06...
> Hello,
> We maintain a VB6 front-end application using an Access 2000 database. All
> code and forms are in VB6. The program also uses several queries/reports
> defined in Access. For corporate reasons we must move to SQL Server right
> away. Based on our configuration, we're hoping someone can give us a feel
> for what's involved here. Since we're only using Access for the data we're
> not sure what an "upsizing" would entail. Can someone point us in the
> right
> direction, tell us the main gotchas, etc. Thanks a lot.
> Teri Welch
>|||There are a number of tools you can use to upsize the database, but in
my experience, I would tend to do this manually to ensure everything in
the structure is correct.
Your main issue will be with the queries as the syntax can be slightly
different to SQL. It's only subtle, but you would need to address this.
Personally, I would manually create the tables (inc indexes etc...)
then move the data using the import wizard and do any manipulation you
need to get this correctly into the tables. Then, I'd address each
query step by step in VB.
There shouldn't be too many issues (as a general statement). It may be
worthwhile looking at using views and stored procedures as these may
give you additional benefits.
What you will find is that once done, it's probably quicker and much
less likely to have issues with record locking as SQL handles this much
better.
With your reports, I would imagine you will leave these in Access, set
up an ODBC link to your SQL tables in access and call the reports
through VB as you will currently do. All you need to do is swap the
links over and it should be straight forward. You may do this and then
think about leaving the links in for the queries, but I would advise
not to on experience and for simplicity.
I'm sure if there are any specific problems, a number of people on this
NG will help, if not, try...
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&group=comp.databases.ms-access
Ryan
Teri Welch wrote:
> Hello,
> We maintain a VB6 front-end application using an Access 2000
database. All
> code and forms are in VB6. The program also uses several
queries/reports
> defined in Access. For corporate reasons we must move to SQL Server
right
> away. Based on our configuration, we're hoping someone can give us a
feel
> for what's involved here. Since we're only using Access for the data
we're
> not sure what an "upsizing" would entail. Can someone point us in the
right
> direction, tell us the main gotchas, etc. Thanks a lot.
> Teri Welch|||See if this is useful............
"Moving Your Access 2002 Database to SQL Server"
http://msdn.microsoft.com/sql/default.aspx?pull=/library/en-us/dnsql2k/html/sqlbackend.asp
Thanks
GYK|||On Fri, 17 Dec 2004 08:34:55 -0000, "John Bell" <jbellnewsposts@.hotmail.com>
wrote:
>Hi
>If you are going to remove access completely your access queries will need
>to be re-written as stored procedures or views and the reports rewritten in
>(say) VB. Alternatively you can use an Access Data Project where most of the
>code can remain the same.
I have to say it - bah! There - I said it.
Every single solitary time I've seen someone post on Usenet asking about
upsizing an Access database to SQL Server, the first response says something
about having to convert all your queries to views and stored procedures.
Frankly, that's not at all necessary, and a serious drain on time and money.
Access/DAO mostly does a fine job of translating queries of linked tables into
reasonable server-side SQL for execution, and it creates prepared statements
for parameter queries, so SQL Server won't have to keep recompiling each query
each time it sees it. Furthermore, Access is quite happy doing things like
using a named query parameter to look up a value from a form control and
allowing editing of values from multiple tables in a query with a join.
If you convert all these things to views and stored procedures, you have to
figure out how to do things like bind forms to parameterized stored
procedures, and implement extra code and forms for editing that were never
needed before, etc. It's a huge, complex, error prone exercise that forces
you to add complexity and remove convinience in the UI, all for basically no
return on investment.
Now, you're going to say that sometimes Access does not do a good job of
producing the server-side SQL. Yup, that's true, so in -those- cases, using a
view and/or a stored procedure can be very beneficial. You're also going to
say that if you have multiple systems accessing the same back-end, you want to
make the database interactions consistent and centralize business logic. I
say, yes, but again, do that incrementally as needed for specific cases. If
you really need to centralize a -lot- of bunsiness logic, T-SQL is not a great
language for it anyway, and you probably should think about a 3-tier system
and dump the Access UI altogether.
>The access upgrade wizard will migrate most things, although it can run into
>problems if you are using something that is more complex.
Here's a case where I do recommend doing it the "hard way. I've never seen
the upsizing wizard do a particularly good job. For each table, you should be
deciding what should be your clustered index, whether you need a TIMESTAMP
field, checking for NULL in Boolean fields, etc.
>I suggest that you create a test system and copy your database and
>application onto it. Then you try the upgrade, and you will get a feel for
>what is involved and how much can be easily migrated.
An excellent suggestion - I agree.|||Hi Steve
I don't disagree with anything you said, but you seemed to have missed the
caveat in the first sentence, i.e. remove access all together and you don't
really have any choice.
John
"Steve Jorgensen" <nospam@.nospam.nospam> wrote in message
news:kd26s0pl1p1s0dr38ah8gftbqu762cvles@.4ax.com...
> On Fri, 17 Dec 2004 08:34:55 -0000, "John Bell"
> <jbellnewsposts@.hotmail.com>
> wrote:
>>Hi
>>If you are going to remove access completely your access queries will need
>>to be re-written as stored procedures or views and the reports rewritten
>>in
>>(say) VB. Alternatively you can use an Access Data Project where most of
>>the
>>code can remain the same.
> I have to say it - bah! There - I said it.
> Every single solitary time I've seen someone post on Usenet asking about
> upsizing an Access database to SQL Server, the first response says
> something
> about having to convert all your queries to views and stored procedures.
> Frankly, that's not at all necessary, and a serious drain on time and
> money.
> Access/DAO mostly does a fine job of translating queries of linked tables
> into
> reasonable server-side SQL for execution, and it creates prepared
> statements
> for parameter queries, so SQL Server won't have to keep recompiling each
> query
> each time it sees it. Furthermore, Access is quite happy doing things
> like
> using a named query parameter to look up a value from a form control and
> allowing editing of values from multiple tables in a query with a join.
> If you convert all these things to views and stored procedures, you have
> to
> figure out how to do things like bind forms to parameterized stored
> procedures, and implement extra code and forms for editing that were never
> needed before, etc. It's a huge, complex, error prone exercise that
> forces
> you to add complexity and remove convinience in the UI, all for basically
> no
> return on investment.
> Now, you're going to say that sometimes Access does not do a good job of
> producing the server-side SQL. Yup, that's true, so in -those- cases,
> using a
> view and/or a stored procedure can be very beneficial. You're also going
> to
> say that if you have multiple systems accessing the same back-end, you
> want to
> make the database interactions consistent and centralize business logic.
> I
> say, yes, but again, do that incrementally as needed for specific cases.
> If
> you really need to centralize a -lot- of bunsiness logic, T-SQL is not a
> great
> language for it anyway, and you probably should think about a 3-tier
> system
> and dump the Access UI altogether.
>>The access upgrade wizard will migrate most things, although it can run
>>into
>>problems if you are using something that is more complex.
> Here's a case where I do recommend doing it the "hard way. I've never
> seen
> the upsizing wizard do a particularly good job. For each table, you
> should be
> deciding what should be your clustered index, whether you need a TIMESTAMP
> field, checking for NULL in Boolean fields, etc.
>>I suggest that you create a test system and copy your database and
>>application onto it. Then you try the upgrade, and you will get a feel for
>>what is involved and how much can be easily migrated.
> An excellent suggestion - I agree.
>|||Yup - I'd say my knee was a bit quick on the jerk there <g>.
On Fri, 17 Dec 2004 17:05:26 -0000, "John Bell" <jbellnewsposts@.hotmail.com>
wrote:
>Hi Steve
>I don't disagree with anything you said, but you seemed to have missed the
>caveat in the first sentence, i.e. remove access all together and you don't
>really have any choice.
>John
>"Steve Jorgensen" <nospam@.nospam.nospam> wrote in message
>news:kd26s0pl1p1s0dr38ah8gftbqu762cvles@.4ax.com...
>> On Fri, 17 Dec 2004 08:34:55 -0000, "John Bell"
>> <jbellnewsposts@.hotmail.com>
>> wrote:
>>Hi
>>If you are going to remove access completely your access queries will need
>>to be re-written as stored procedures or views and the reports rewritten
>>in
>>(say) VB. Alternatively you can use an Access Data Project where most of
>>the
>>code can remain the same.
>> I have to say it - bah! There - I said it.
>> Every single solitary time I've seen someone post on Usenet asking about
>> upsizing an Access database to SQL Server, the first response says
>> something
>> about having to convert all your queries to views and stored procedures.
>> Frankly, that's not at all necessary, and a serious drain on time and
>> money.
>> Access/DAO mostly does a fine job of translating queries of linked tables
>> into
>> reasonable server-side SQL for execution, and it creates prepared
>> statements
>> for parameter queries, so SQL Server won't have to keep recompiling each
>> query
>> each time it sees it. Furthermore, Access is quite happy doing things
>> like
>> using a named query parameter to look up a value from a form control and
>> allowing editing of values from multiple tables in a query with a join.
>> If you convert all these things to views and stored procedures, you have
>> to
>> figure out how to do things like bind forms to parameterized stored
>> procedures, and implement extra code and forms for editing that were never
>> needed before, etc. It's a huge, complex, error prone exercise that
>> forces
>> you to add complexity and remove convinience in the UI, all for basically
>> no
>> return on investment.
>> Now, you're going to say that sometimes Access does not do a good job of
>> producing the server-side SQL. Yup, that's true, so in -those- cases,
>> using a
>> view and/or a stored procedure can be very beneficial. You're also going
>> to
>> say that if you have multiple systems accessing the same back-end, you
>> want to
>> make the database interactions consistent and centralize business logic.
>> I
>> say, yes, but again, do that incrementally as needed for specific cases.
>> If
>> you really need to centralize a -lot- of bunsiness logic, T-SQL is not a
>> great
>> language for it anyway, and you probably should think about a 3-tier
>> system
>> and dump the Access UI altogether.
>>The access upgrade wizard will migrate most things, although it can run
>>into
>>problems if you are using something that is more complex.
>> Here's a case where I do recommend doing it the "hard way. I've never
>> seen
>> the upsizing wizard do a particularly good job. For each table, you
>> should be
>> deciding what should be your clustered index, whether you need a TIMESTAMP
>> field, checking for NULL in Boolean fields, etc.
>>I suggest that you create a test system and copy your database and
>>application onto it. Then you try the upgrade, and you will get a feel for
>>what is involved and how much can be easily migrated.
>> An excellent suggestion - I agree.
>|||Thanks for the replies from everyone.
1. How can we convert MS-Access import specs to SQL Server. We import many
data files from outside sources -- in both ASCII and Excel formats -- and
make heavy use of import specs within VB6 programs by invoking the
TransferText and TransferSpreadsheet functions.
2. Regarding reports, must we leave them in Access? We are inclined to
remove Access completely from the picture. Maybe that is too drastic and you
would advocate otherwise. But we question the performance of using Access
reports and the necessity to have Access open everytime a report is invoked
from within VB6. Plus, we have to keep Access installed. How can we create
the reports natively within SQL Server.
Thanks,
Teri Welch
"Ryan" <ryanofford@.hotmail.com> wrote in message
news:1103273150.975672.240020@.z14g2000cwz.googlegroups.com...
> There are a number of tools you can use to upsize the database, but in
> my experience, I would tend to do this manually to ensure everything in
> the structure is correct.
> Your main issue will be with the queries as the syntax can be slightly
> different to SQL. It's only subtle, but you would need to address this.
> Personally, I would manually create the tables (inc indexes etc...)
> then move the data using the import wizard and do any manipulation you
> need to get this correctly into the tables. Then, I'd address each
> query step by step in VB.
> There shouldn't be too many issues (as a general statement). It may be
> worthwhile looking at using views and stored procedures as these may
> give you additional benefits.
> What you will find is that once done, it's probably quicker and much
> less likely to have issues with record locking as SQL handles this much
> better.
> With your reports, I would imagine you will leave these in Access, set
> up an ODBC link to your SQL tables in access and call the reports
> through VB as you will currently do. All you need to do is swap the
> links over and it should be straight forward. You may do this and then
> think about leaving the links in for the queries, but I would advise
> not to on experience and for simplicity.
> I'm sure if there are any specific problems, a number of people on this
> NG will help, if not, try...
>
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&group=comp.databases.ms-access
> Ryan
>
> Teri Welch wrote:
> > Hello,
> >
> > We maintain a VB6 front-end application using an Access 2000
> database. All
> > code and forms are in VB6. The program also uses several
> queries/reports
> > defined in Access. For corporate reasons we must move to SQL Server
> right
> > away. Based on our configuration, we're hoping someone can give us a
> feel
> > for what's involved here. Since we're only using Access for the data
> we're
> > not sure what an "upsizing" would entail. Can someone point us in the
> right
> > direction, tell us the main gotchas, etc. Thanks a lot.
> >
> > Teri Welch
>|||Hi Teri
"Teri Welch" <trwlch@.yahoo.com> wrote in message
news:s1hyd.8482$jn.4225@.lakeread06...
> Thanks for the replies from everyone.
> 1. How can we convert MS-Access import specs to SQL Server. We import many
> data files from outside sources -- in both ASCII and Excel formats -- and
> make heavy use of import specs within VB6 programs by invoking the
> TransferText and TransferSpreadsheet functions.
You can use DTS to import from many different locations.
> 2. Regarding reports, must we leave them in Access? We are inclined to
> remove Access completely from the picture. Maybe that is too drastic and
> you
> would advocate otherwise. But we question the performance of using Access
> reports and the necessity to have Access open everytime a report is
> invoked
> from within VB6. Plus, we have to keep Access installed. How can we create
> the reports natively within SQL Server.
Reporting services is provided with SQL server, but this is probably not the
type of reporting you require. There may be some third party VB/ActiveX
component that will do a similar job as your current requirements.
> Thanks,
> Teri Welch
>
John
> "Ryan" <ryanofford@.hotmail.com> wrote in message
> news:1103273150.975672.240020@.z14g2000cwz.googlegroups.com...
>> There are a number of tools you can use to upsize the database, but in
>> my experience, I would tend to do this manually to ensure everything in
>> the structure is correct.
>> Your main issue will be with the queries as the syntax can be slightly
>> different to SQL. It's only subtle, but you would need to address this.
>> Personally, I would manually create the tables (inc indexes etc...)
>> then move the data using the import wizard and do any manipulation you
>> need to get this correctly into the tables. Then, I'd address each
>> query step by step in VB.
>> There shouldn't be too many issues (as a general statement). It may be
>> worthwhile looking at using views and stored procedures as these may
>> give you additional benefits.
>> What you will find is that once done, it's probably quicker and much
>> less likely to have issues with record locking as SQL handles this much
>> better.
>> With your reports, I would imagine you will leave these in Access, set
>> up an ODBC link to your SQL tables in access and call the reports
>> through VB as you will currently do. All you need to do is swap the
>> links over and it should be straight forward. You may do this and then
>> think about leaving the links in for the queries, but I would advise
>> not to on experience and for simplicity.
>> I'm sure if there are any specific problems, a number of people on this
>> NG will help, if not, try...
>>
> http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&group=comp.databases.ms-access
>> Ryan
>>
>> Teri Welch wrote:
>> > Hello,
>> >
>> > We maintain a VB6 front-end application using an Access 2000
>> database. All
>> > code and forms are in VB6. The program also uses several
>> queries/reports
>> > defined in Access. For corporate reasons we must move to SQL Server
>> right
>> > away. Based on our configuration, we're hoping someone can give us a
>> feel
>> > for what's involved here. Since we're only using Access for the data
>> we're
>> > not sure what an "upsizing" would entail. Can someone point us in the
>> right
>> > direction, tell us the main gotchas, etc. Thanks a lot.
>> >
>> > Teri Welch
>

Tuesday, March 27, 2012

Accessing Java within SQL Server

Accessing HttpContext.Current from code section in report

Sunday, March 25, 2012

accessing datasets / Field collections through custom code

Accessing dataset with custom code

Thursday, March 22, 2012

Accessing ASP datasource results through VB code

Tuesday, March 20, 2012

Access2SQL

Access2K Connecting to SQL Only as LocalAdmin

Sunday, March 11, 2012

Access Update Query Problem

Thursday, March 8, 2012

Access to SQL server

Access To SQL

Access to report objects fom custom code

Tuesday, March 6, 2012

Access to datasources in custom code

Access to dataset fields in Code window

Access takes 24-35 seconds while SQL Server takes only 2-3 seconds

Friday, February 24, 2012

access report builder from custom code

Access read only variables in Script Component in a Dataflow

Access Query: Need to return a month name instead of number in the query result

Sunday, February 19, 2012

Access migration to SQL Server

SQL Alert

Blog Archive

About Me