How to create report using WebService as DataSource.

Hi,

We created a simple web serivce having a Web Method which accepts a string parameter and returns "Hello World" concatenated with parameter value. We tested this web service by browsing it through IE and it was working fine by accepting a parameter and displaying the "Hello World " concated with the value given through the parameter when clicked on "Invoke" button.

We now created a Reporting Services 2005 project using BI having a report for which the data source will be the web service which is mentioned above. We defined a parameter for the report which will pass the value to the webservice and the report will display the returned value from the web service.

When we have done the report and executed by giving a value to the parameter and clicking on view report button it was displaying the "Hello World" but the parameter which we passed to the web service is not getting concatenated. When we tried to trace the application we could see that the value passed to the web service web method was comming as null. So kindly help me where I went wrong in developing such a kind of report which is not passing a parmeter to webservice.

Kindly provide us the solution in order to make this issue to be resolved.

Thanks in Advance.I can concur with VDeevi, I was able to recreate the exact same issue. In my case I'm passing parameters to a web service that is returning a dataset. When I set a default value in the webmethod it returns a dataset successfully. My problem is also that the parameters are not getting passed to the web service.

What I think is strange is that I've done the BOL sample with the SSRS web service Listchildren method and it works successfully. Does that mean that the problem is not in SSRS but in my implementation of the web service?

This works:

<WebMethod()> _
Public Function GetTestDataset() As System.Xml.XmlElement
Dim TestXDataset As New TestXDataset()
TestXDataset.Fill(1)
Dim xdd As System.Xml.XmlDataDocument = New System.Xml.XmlDataDocument(TestXDataset)
Dim docElem As System.Xml.XmlElement = xdd.DocumentElement
Return docElem
End Function

This doesn't work:

<WebMethod()> _
Public Function GetTestDataset(ByVal ID As Integer) As System.Xml.XmlElement
Dim TestXDataset As New TestXDataset()
TestXDataset.Fill(ID)
Dim xdd As System.Xml.XmlDataDocument = New System.Xml.XmlDataDocument(TestXDataset)
Dim docElem As System.Xml.XmlElement = xdd.DocumentElement
Return docElem
End Function

|||

It's probably not your implementation, but in the parsing specifics of the SoapAction and Namespace.

For example, if your SoapAction is http://tempuri.org/HelloMyNameIs, we split it into http://tempuri.org and HelloMyNameIs throwing away the separating / character.

But if you look at the way webservice is defined, the namespace is actually http://tempuri.org/ - with a trailing /.

So in this case, when method name/namespace can not be correctly derived from SoapAction and when SoapAction can not be correctly derived from method name (in the above case we would get http://tempuri.org//HelloMyNameIs) you should use both <Method …/> and <SoapAction …/>, e.g.

<Query>
<Method Namespace="http://tempuri.org/" Name="HelloMyNameIs"/>
<SoapAction>http://tempuri.org/HelloMyNameIs</SoapAction>
</Query>

We are working on updates to the documentation.

|||Thanks Brain, it really helped us to solve this issue.

Thanks once again,|||Worked for me as well. Thanks a lot!

How to Create reference for Composite key

Hi All,

Can anyone tell me how to create a reference for composite key.

For ex, I have created tblEmp table successfully.

create tblEmp

(

empId varchar(100),

RegId varchar(100),

empname varchar(100),

constraint pk_add

primary key(empId, RegId)

)

And now, I am going to create another table which references the composite key.

create table tblAccount

(

acctId varchar(100) primary key,

empId varchar(100) references tblEmp(empId),

RegId varchar(100) references tblEmp(RegId)

)

But it gives error like

Server: Msg 1776, Level 16, State 1, Line 1
There are no primary or candidate keys in the referenced table 'tblEmp' that match the referencing column list in the foreign key 'FK__tbl'.
Server: Msg 1750, Level 16, State 1, Line 1
Could not create constraint. See previous errors.

Could anyone please let me know how to create reference for composite key.

Thanks in advance,

Arun.

The below code will do:

create table tblEmp( empIdvarchar(100), RegIdvarchar(100), empnamevarchar(100),constraint pk_addprimary key(empId, RegId) )create table tblAccount( acctIdvarchar(100)primary key, empIdvarchar(100) , RegIdvarchar(100),constraint fk_tblAccountforeign key ( empId, RegId)references tblEmp( empId, RegId) )

In case you've already created both tables, you can use below query to set the foreign key in the account table.

alter table tblAccountadd constraint fk_tblAccountforeign key ( empId, RegId)references tblEmp( empId, RegId)

Hope this will help.

|||

Hi,

We can also do it by

create tblEmp

(

empId varchar(100),

RegId varchar(100),

empname varchar(100),

primary key(empId, RegId)

)

And now, I am going to create another table which references the composite key.

create table tblAccount

(

acctId varchar(100) primary key,

empId varchar(100) ,

RegId varchar(100) ,

foreign key(empId,RegId) references tblEmp(empId,RegId)

)

It works perfect

|||

arunkumar.niit:

primary key(empId, RegId)

arunkumar.niit:

foreign key(empId,RegId) references tblEmp(empId,RegId)

Hey, your queries are exactly the same as what I've posted except one thing that I've given my own names to the constraints and you've left it over the SQL Server.

|||

Hi,

Thanks for your reply.

Thanks & Regards,

Arun.

How to create Merge Replication with latest update always from subscribers

Hi ,

I am trying to create Replication Topology (Merge Replication) like below.

Subscriber1 --> Publisher <-- Subscriber2.

I have created both subscribers with Subscription Type as Server with Priority as 75. I am updating the Column A of Row_10 in Subscriber1 on time say 11 am. After i am running the Starting synchronizing agent from Subscriber1. The value propagated to Publisher now publisher contains the latest value in Column A. Uptonow the Subscriber2 is not synchronized with Publisher.

Now in Subscriber2 also Column A of Row_10 is updated say at 11.10 am. Actually now Publisher contains the value from Subscriber1 for that Column and in Subscriber2 we have the same column updated.

Now i am running the Synchronization in Subscriber2, i am getting the result which is not expected. Here Publisher's value is propagated to Subscriber2. But as per real scenario Subscriber2 has the Latest value which is updated on 11.10 AM.

I don't know what am i missing here. Actually merge replication should see the time stamp and it has to decide winner. But here it always considers publisher as a winner and puts the data to Subscriber.

Can anyone help ?

Thanks in advance

Merge replication tracks changes via a rowguid, not a timestamp. it has no idea what time a change was made. If you want to decide who wins in a conflict scenario based on a column value in your table, then you should create a custom conflict resolver for your article. see sp_addmergearticle in books online for more information.|||Greg,

Thanks for the reply. After going through MDSN I found one solution. We can use Microsoft Provided Custom Resolver for this. DateTime Later Wins resolver. I think using this my issue will be resolved. But i didn't try this resolver yet.

Thanks,

Thams

How to create Merge Replication with latest update always from subscribers

Hi ,

I am trying to create Replication Topology (Merge Replication) like below.

Subscriber1 --> Publisher <-- Subscriber2.

I have created both subscribers with Subscription Type as Server with Priority as 75. I am updating the Column A of Row_10 in Subscriber1 on time say 11 am. After i am running the Starting synchronizing agent from Subscriber1. The value propagated to Publisher now publisher contains the latest value in Column A. Uptonow the Subscriber2 is not synchronized with Publisher.

Now in Subscriber2 also Column A of Row_10 is updated say at 11.10 am. Actually now Publisher contains the value from Subscriber1 for that Column and in Subscriber2 we have the same column updated.

Now i am running the Synchronization in Subscriber2, i am getting the result which is not expected. Here Publisher's value is propagated to Subscriber2. But as per real scenario Subscriber2 has the Latest value which is updated on 11.10 AM.

I don't know what am i missing here. Actually merge replication should see the time stamp and it has to decide winner. But here it always considers publisher as a winner and puts the data to Subscriber.

Can anyone help ?

Thanks in advance

Merge replication tracks changes via a rowguid, not a timestamp. it has no idea what time a change was made. If you want to decide who wins in a conflict scenario based on a column value in your table, then you should create a custom conflict resolver for your article. see sp_addmergearticle in books online for more information.|||Greg,

Thanks for the reply. After going through MDSN I found one solution. We can use Microsoft Provided Custom Resolver for this. DateTime Later Wins resolver. I think using this my issue will be resolved. But i didn't try this resolver yet.

Thanks,

Thams

How to create dynamic reports based on custom business objects?

How can we create dynamic reports(or reports created by the end user) by
selected fields from the list of custom business objects.
We would like to offer full data consolidation. Right Crystal offers this
type of functionality but I would like to steer away from that.
Any ideas how to proceed or whether is it even feasible. We do not want to
use reflection on the objects, I mean we can use it but that would not be the
best approach.
ThanksHi Amarnath,
you said SSRS has a new feature using which report builder could be given to
end user to create reports.
Is this Report builder browser based(so that the end user does not need to
install anything) and could design reports and modify the existing reports,
if yes, how to make use of this?
Thanks
Ponnu
"Amarnath" <Amarnath@.discussions.microsoft.com> wrote in message
news:9A2A1500-E374-45F1-8288-56CB8EC97883@.microsoft.com...
> What do you mean by custom BO, is it in the form of dll or assembly sort
> of
> thing then you can always refer the assembly in SS 2005. If it is in the
> form
> of Stored proc then you can always refer that in the report query..
> Infact in RS you need to create a report model and give it to the end
> users,
> SSRS new feature has report builder which is given to the end user to
> create
> their own reports.
>
> Amarnath
>
> "Atul Bahl" wrote:
>> How can we create dynamic reports(or reports created by the end user) by
>> selected fields from the list of custom business objects.
>> We would like to offer full data consolidation. Right Crystal offers this
>> type of functionality but I would like to steer away from that.
>> Any ideas how to proceed or whether is it even feasible. We do not want
>> to
>> use reflection on the objects, I mean we can use it but that would not be
>> the
>> best approach.
>> Thanks

How to create database from script file?

Hi
I use SQLServer 2000 with SP3
I have script file that has been created using "generate SQL script"
command from SQL server Enterprise manager.
But I can't find how to create database using this script file. Is there
any way to create database from script file?
I appreciate your help!Copy the script to the clipboard during the "generate sql script" command.
Start the query analyzer and past the script
Run the script.
(You could also save the script and open it in the query analyzer)
Nico De Greef
Belgium
Freelance Software Architect
MCP, MCSD, .NET certified
"JK" <invalid@.address> wrote in message
news:e0JzX2Q$DHA.808@.TK2MSFTNGP12.phx.gbl...
> Hi
> I use SQLServer 2000 with SP3
> I have script file that has been created using "generate SQL script"
> command from SQL server Enterprise manager.
> But I can't find how to create database using this script file. Is there
> any way to create database from script file?
>
> I appreciate your help!
>|||In the Options tab of generate script, you have the option to "Script
Database". This will include the CREATE DATABASE statement.
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
"JK" <invalid@.address> wrote in message
news:e0JzX2Q$DHA.808@.TK2MSFTNGP12.phx.gbl...
> Hi
> I use SQLServer 2000 with SP3
> I have script file that has been created using "generate SQL script"
> command from SQL server Enterprise manager.
> But I can't find how to create database using this script file. Is there
> any way to create database from script file?
>
> I appreciate your help!
>sql

How to create database from script file?

Hi
I use SQLServer 2000 with SP3
I have script file that has been created using "generate SQL script"
command from SQL server Enterprise manager.
But I can't find how to create database using this script file. Is there
any way to create database from script file?
I appreciate your help!check out www.dbghost.com
>--Original Message--
>Hi
>I use SQLServer 2000 with SP3
>I have script file that has been created using "generate
SQL script"
>command from SQL server Enterprise manager.
>But I can't find how to create database using this
script file. Is there
>any way to create database from script file?
>
>I appreciate your help!
>
>.
>|||Copy the script to the clipboard during the "generate sql script" command.
Start the query analyzer and past the script
Run the script.
(You could also save the script and open it in the query analyzer)
--
Nico De Greef
Belgium
Freelance Software Architect
MCP, MCSD, .NET certified
"JK" <invalid@.address> wrote in message
news:e0JzX2Q$DHA.808@.TK2MSFTNGP12.phx.gbl...
> Hi
> I use SQLServer 2000 with SP3
> I have script file that has been created using "generate SQL script"
> command from SQL server Enterprise manager.
> But I can't find how to create database using this script file. Is there
> any way to create database from script file?
>
> I appreciate your help!
>|||In the Options tab of generate script, you have the option to "Script
Database". This will include the CREATE DATABASE statement.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"JK" <invalid@.address> wrote in message
news:e0JzX2Q$DHA.808@.TK2MSFTNGP12.phx.gbl...
> Hi
> I use SQLServer 2000 with SP3
> I have script file that has been created using "generate SQL script"
> command from SQL server Enterprise manager.
> But I can't find how to create database using this script file. Is there
> any way to create database from script file?
>
> I appreciate your help!
>

How to create Calculations that do not change when dimensions are added.

I would like to calculate the total business done for a week irrespective of the dimension used and I have created a member ([Measures].ForAWeekOfYear) for the same. The Percent1 member calculates the percentage of business done by a company.

with member [Measures].ForAWeekOfYear as
sum( [Dimdate].[Week Of Year] , [Measures].[TotalBusiness] )
member Measures.Percent1 as [Measures].[TotalBusiness]/[Measures].ForAWeekOfYear
select { [Measures].ForAWeekOfYear , ( Measures.Percent1 ) } on 0,
{ [Dim Customer].[Company].members * [Dimdate].[Week Of Year].Members} on 1
from [Account]

The problem I have if I use the above MDX expression is that the calculation gets split based on the Customer dimension and I assume that this is how the query should work. I assume I have to use the SCOPE statement, but not sure how to use it.

Please note that I am trying to execute this in Management console and not within a cube.

If you don't want your calculation to depend on Customer dimension, you should include Root(Customer). If you want to abstract from all of the dimensions in the cube - use Root().

HTH,

Mosha (http://www.mosha.com/msolap)

How to create an installer for deploying Sql Server Express

Hi,

I have created an installer for deploying Sql Server Express. My installer will start the process of installing Sql Server Express. However the installation of SqlServer Express will fail because it says another installer is currently running (which is my installer). Is there anyway to work around this problem?

The reason why I would like to create an installer for the installation of Sql Server Express is because I would like to configure the SqlServerExpress without the user fiddling with the ini file.

Thanks

Theses sources should help:

SQL Server 2005 UnAttended Installations
http://msdn2.microsoft.com/en-us/library/ms144259.aspx
http://msdn2.microsoft.com/en-us/library/bb264562.aspx
http://www.devx.com/dbzone/Article/31648

Deploy Database with MSI
http://msdn.microsoft.com/msdnmag/issues/04/09/customdatabaseinstaller/

Deploy Database with Application

http://www.codeproject.com/useritems/Deploy_your_database.asp

How to create a update button to update two SqlDataSource controls?

I want to update two tables in one page. So I created two FormView bound on two SqlDataSource controls, and I create a Update button on the bottom of page. And I writen some codes as below:

btnUpate_Click(object sender, EventArgs e)
{

sqlDataSource1.Update();

sqlDateSource2.Update();
}

But, the records haven't updated.

In SqlDataSource2_Updating() function, I found all the parameters is null.

So, how to modify my code to do it.

Zhang

You need to actually set the update parameters. Take a look at this link.

http://msdn2.microsoft.com/en-us/library/fkzs2t3h(VS.80).aspx

Sub EmployeeDetailsSqlDataSource_OnInserted(senderAsObject, eAs SqlDataSourceStatusEventArgs)
Dim commandAs System.Data.Common.DbCommand = e.Command

EmployeeDetailsSqlDataSource.SelectParameters("EmpID").DefaultValue = _
command.Parameters("@.EmpID").Value.ToString()

EmployeesGridView.DataBind()
EmployeeFormView.DataBind()
EndSub

|||

I set the parameters' DafaultValue in the Clicked Event of the Update button. And it work fine.

But it is too hard to set so much parameters by codes. Are there any ways to set them automatically as the update link button had done in the FormView?

How to create a trigger such that it can delete the rows whenever any other application such as

I had created a trigger which sees that whether a database is updated if it is its copy the values of the updated row into another control table now I want to read the content of control_table into BIzTalk and after reading I want to delete it.Can any one suggest the suitable ay to do this?

Hi,

actually Select has no trigger action, only DML operations are capable. You could use a stored procedure to read the rows rather than using a select statement (don′t know if BizTalk is able to do this through stored procedures).

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||Thanks a lot Jens.

How to create a trigger such that it can delete the rows whenever any other application such as

I had created a trigger which sees that whether a database is updated if it is its copy the values of the updated row into another control table now I want to read the content of control_table into BIzTalk and after reading I want to delete it.Can any one suggest the suitable ay to do this?

Hi,

actually Select has no trigger action, only DML operations are capable. You could use a stored procedure to read the rows rather than using a select statement (don′t know if BizTalk is able to do this through stored procedures).

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

How to Create a Minimal user in DB with no access to view Master DB

Hi,

We are using SQL Server 2005 Management Studio.

I created a Minimal User in an application DB. The user will access tables through stored procedures.

I do not want this user to view any other objects including objects in the Master DB.

I can prevent the minimal user from viewing objects from our application DB.

How do you prevent the minimal user from viewing objects in the Master DB?

Thanks.

Tim.

If u want a particular user should not access Master DB, do not add the user to master DB at all. We generally never add users to master db. Thump rule is that u should control permissions using Role. Create a new role add this user to that role and give permission to Role.

Madhu

|||

The user I setup is not in the Master DB. It seems there are default settings in Public on the Master DB that allow access to view tables within Master. Guest is a member of Public, therefore, users in other DB's can view these tables in Master. I want to know how to prevent this from happening. Or, does it really matter?

Tim.

|||

Why do you care if the user sees objects in master? Those objects are system (you should not store your user data in master, or if you decide to store it, just deny permissions on it to guest) and they are present in any installation, so there's no secret about their existence. The actual contents of the system catalogs are protected via catalog security - so even if your user sees a catalog, he'll only see the entries corresponding to entities he has permissions on. I don't think you should care about this, but if you are having a certain scenario in mind where you think that the information that is visible makes a system vulnerable, let us know about it.

Thanks

Laurentiu

|||

Hi Laurentiu,

Thanks for your answer. My concern was with the access that public has in the master DB. Is there a list or document that shows the initial settings of a master DB? I would like to compare the public permissions in my Master DB with the ones that come with SQL Server 2005 standard?

Is there a rule of Thumb that applies here?

Tim.

|||

You could make a fresh installation on another machine and record the list of permissions, then compare it against your current installation. I don't think there is a published list - it would be hard to maintain from release to release, as system objects are added or dropped in service packs as well.

For the public server role, things are pretty simple, as he only gets VIEW ANY DATABASE and some CONNECT permissions; for the public database role, things are more complicated - on my system I have 1664 various grants of SELECT or EXECUTE permissions. Things would get even more complicated if you install applications that create new objects in master and thus add new grants. However, you could write dedicated scripts to check the list of grants against a copy and report any changes, then you could run these scripts periodically.

Thanks

Laurentiu

How to Create a Minimal user in DB with no access to view Master DB

Hi,

We are using SQL Server 2005 Management Studio.

I created a Minimal User in an application DB. The user will access tables through stored procedures.

I do not want this user to view any other objects including objects in the Master DB.

I can prevent the minimal user from viewing objects from our application DB.

How do you prevent the minimal user from viewing objects in the Master DB?

Thanks.

Tim.

If u want a particular user should not access Master DB, do not add the user to master DB at all. We generally never add users to master db. Thump rule is that u should control permissions using Role. Create a new role add this user to that role and give permission to Role.

Madhu

|||

The user I setup is not in the Master DB. It seems there are default settings in Public on the Master DB that allow access to view tables within Master. Guest is a member of Public, therefore, users in other DB's can view these tables in Master. I want to know how to prevent this from happening. Or, does it really matter?

Tim.

|||

Why do you care if the user sees objects in master? Those objects are system (you should not store your user data in master, or if you decide to store it, just deny permissions on it to guest) and they are present in any installation, so there's no secret about their existence. The actual contents of the system catalogs are protected via catalog security - so even if your user sees a catalog, he'll only see the entries corresponding to entities he has permissions on. I don't think you should care about this, but if you are having a certain scenario in mind where you think that the information that is visible makes a system vulnerable, let us know about it.

Thanks

Laurentiu

|||

Hi Laurentiu,

Thanks for your answer. My concern was with the access that public has in the master DB. Is there a list or document that shows the initial settings of a master DB? I would like to compare the public permissions in my Master DB with the ones that come with SQL Server 2005 standard?

Is there a rule of Thumb that applies here?

Tim.

|||

You could make a fresh installation on another machine and record the list of permissions, then compare it against your current installation. I don't think there is a published list - it would be hard to maintain from release to release, as system objects are added or dropped in service packs as well.

For the public server role, things are pretty simple, as he only gets VIEW ANY DATABASE and some CONNECT permissions; for the public database role, things are more complicated - on my system I have 1664 various grants of SELECT or EXECUTE permissions. Things would get even more complicated if you install applications that create new objects in master and thus add new grants. However, you could write dedicated scripts to check the list of grants against a copy and report any changes, then you could run these scripts periodically.

Thanks

Laurentiu

How to create a login/user & grant rights?

I'm trying to create new login/user account in C#. I'm pretty close to getting it working -- login/user accounts get created, but I'm getting hung up on granting the permissions for the User on the database.

The error that is thrown is:
Microsoft.SqlServer.Management.Smo.FailedOperationException: Grant failed for User 'RTOUser'. > Microsoft.SqlServer.Management.Common.ExecutionFailureException: An exception occurred while executing a Transact-SQL statement or batch. > System.Data.SqlClient.SqlException: Incorrect syntax near 'CONNECT...'.

With the SQL server profiler, I can see the grant command that was used:
GRANT CONNECT, DELETE, EXECUTE, INSERT, SELECT ON USER::[RTOUser] TO [test14]

RTOUser is just a generic testing account I'm using that basically has admin rights on the database. It also has with grant rights for the aforementioned rights being granted to the test user, test14.

How does one post formatted code on here? a [code] bracket doesn't work nor do I see anything in the formatting bar above for it. Below is the code I'm using:

public override Int32 CreateEmployee( EmployeeData emp )
{
Int32 lastid = 0;
SqlParameter[] insert_parms = {
new SqlParameter("@.EmpUserName", SqlDbType.VarChar, STD_VARCHAR),
new SqlParameter("@.EmpFullName", SqlDbType.VarChar, STD_VARCHAR),
new SqlParameter("@.EmpDescription", SqlDbType.VarChar, STD_VARCHAR)
};

insert_parms[0].Value = emp.m_UserName;
insert_parms[1].Value = emp.m_FullName;
insert_parms[2].Value = emp.m_Description;

try
{
Server svr = SqlHelper.GetServerConnection( this.ConnectionString );
Login lg = new Login( svr, emp.m_UserName );

if ( !svr.Logins.Contains( emp.m_UserName ) )
{
lg.LoginType = LoginType.SqlLogin;
lg.PasswordPolicyEnforced = false; //really should be true
lg.DefaultDatabase = DBNAME;
lg.Create( emp.m_Password );

Database db = svr.Databases[DBNAME];
User u = new User( db, this.UserName );
u.Login = emp.m_UserName;
ObjectPermissionSet perms = new ObjectPermissionSet();

//todo: revise permissions
perms.Connect = true;
perms.Select = true;
perms.Insert = true;
perms.Delete = true;
perms.Execute = true;
u.Grant( perms, emp.m_UserName );
u.Create();

try
{
int.TryParse( SqlHelper.ExecuteScalar( this.DBSqlConnection, CommandType.Text, SQL_INSERT_EMPLOYEE, insert_parms ).ToString(), out lastid );
}
catch(SqlException)
{
throw;
}
}
}
catch (SmoException ex)
{
Console.WriteLine( ex );
}

return lastid;
}Additionally,

the User.Login Intellisense in VS2K5 w/SP1 says "Gets the login that is

associated with the database user". This is wrong since it can also

SET it.|||Bah, figured it out. After thinking what the heck SMO was trying to do with that grant command, I realized how it was doing it wrong. The database needs to grant the rights to the user, not a user to another user.

Functional code snippet:

Database db = svr.Databases[DBNAME];
User u = new User( db, emp.m_UserName );
u.Login = emp.m_UserName;
u.Create();

//todo: revise permissions once user groups functional
DatabasePermissionSet perms = new DatabasePermissionSet();
perms.Select = true;
perms.Insert = true;
perms.Delete = true;
perms.Update = true;
perms.Execute = true;

db.Grant( perms, emp.m_UserName );

How to create a job to execute an Access program?

I am trying to schedule an Access Job using SQL Server Agent. I have
created a .BAT file with the full path to a Access .mdb file. The Access
program will automatically start up a form and copy data from an Orcale
database into the SQL Server. When I execute the .BAT file by hand (by
double-clicking it) everything works fine. I can't get things to work
when I create a job and specify the full path to the .BAT file in the
command window. The following is what I enter in the command window: "G:
\SQLJobs\GetData.mdb". I get no errors in the event log. Can someone
give me an idea on what I need to do. I assume I am missing something.
Thanks.
JohnHi
Scheduling an interactive program is not a good idea. You may want to change
this to use a linked server and have it totally within SQL Server itself or
you may want to look at DTS as an another alternative.
John
"John" <nomail@.none.com> wrote in message
news:MPG.1ccc2ae08c2915d9989682@.news.comcast.giganews.com...
>I am trying to schedule an Access Job using SQL Server Agent. I have
> created a .BAT file with the full path to a Access .mdb file. The Access
> program will automatically start up a form and copy data from an Orcale
> database into the SQL Server. When I execute the .BAT file by hand (by
> double-clicking it) everything works fine. I can't get things to work
> when I create a job and specify the full path to the .BAT file in the
> command window. The following is what I enter in the command window: "G:
> \SQLJobs\GetData.mdb". I get no errors in the event log. Can someone
> give me an idea on what I need to do. I assume I am missing something.
> Thanks.
> John|||I think you should point the path to "access.exe", something like this:
"C:\program files\microsoft office\office\access.exe g:\...\GetData.mdb"
But I really do not know if it good idea to run such external process by
using SQL Server Agaent. SQL Server agent (and SQL Server) has no control to
the Access app except for starting it. That is why you do not have error
logged. To schedule this Access app, I'd simply use Windows' "Scheduled
Tasks" applet in Control Panel.
Specific to your task, using DTS might be more suitable.
"John" <nomail@.none.com> wrote in message
news:MPG.1ccc2ae08c2915d9989682@.news.comcast.giganews.com...
> I am trying to schedule an Access Job using SQL Server Agent. I have
> created a .BAT file with the full path to a Access .mdb file. The Access
> program will automatically start up a form and copy data from an Orcale
> database into the SQL Server. When I execute the .BAT file by hand (by
> double-clicking it) everything works fine. I can't get things to work
> when I create a job and specify the full path to the .BAT file in the
> command window. The following is what I enter in the command window: "G:
> \SQLJobs\GetData.mdb". I get no errors in the event log. Can someone
> give me an idea on what I need to do. I assume I am missing something.
> Thanks.
> John

How to create a flexible title for a Matrix Report?

I created a matrix report using the wizard, and above it there is a general
title.
The width of the report is not a constant and it depends in the number of
columns in the matrix.
How can I change automatically the width of the title to match the actual
width of the matrix report?Unfortunately Geri,
We don't have run-time access to things like width. So you are not currently
going to be able to do what you wish... The best you can do is pick some
*average* size and go with it...
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"Geri Reshef" wrote:
> I created a matrix report using the wizard, and above it there is a general
> title.
> The width of the report is not a constant and it depends in the number of
> columns in the matrix.
> How can I change automatically the width of the title to match the actual
> width of the matrix report?
>
>

How to create a dimension for multi-value fields

Hi,

I'm new to SSAS and really confused... I'm creating a cube for "Accounts", each account can belong to N categories, I created a "bridge" table on my model where I say:

Account 1 - Category 2
Account 1 - Category 3
Account 2 - Category 2
....

I dont know how to use SSAS to use the bridge table accordingly so whenever I'm browsing the cube using DimCategory and another dimension, lets say DimTerritory I get a proper count. Can you tell me how should I proceed?

Thanks.

Hi,

take a look into Many-to-many dimensional modeling paper from Marko Russo.

It's really good stuff about problems like your one.

How to creat a database via SQL Querry Analyzer to a Web Server

I am new to ASP.NET and met a problem about access a database on a Web Server (www.myserver.com). My meaning here that I created a .sql from my localhost to test, but when to upload to my web server on the internet, an error occured: "System.Data.SqlClient.SqlException: SQL Server does not exist or access denied".
Anyone here can help me to solve my problem. Waiting for your reply soon. Thanksyour SQL Connection on your local host and your Server aren't the same. your should check your connectionstring, eypecially the servername.|||First of all thank to MISIU...
Your idea i had to try first! Just here, I change my line of code from <Add key> to a connection string to myserver for instance hereby. But nothing looked good and still got the above problem. Could you pls show me the detail of creat a database and publish it to my Web Server. And i am waiting for anyone give me the BEST. God bless all of you!

How to copy the DTS Package from one server to another?

Hello,
I have created some DTS packages in my sql server now i want to copy them to some other SQLServer. Is it possible to do that? If so pls help me to do that.
Advance thanks...
MuraliWhen saving, choose "Save As" and then change "Location" to "Structured Storage File" instead of "SQL Server".

That will give you a file.dts that you can transfer and load onto another SQL Server.

(To open it on a target server, place yourself on "Data Transformation Services" in EM, and right-click the mouse on right blank field. You'll see "Open Package" in pop-up menu|||Hi KUKUK,
Thanks for your reply. but I know this method. Is there any other way to do this?. Instead of creating seperate dts files i want to eport all of them at one shot. same like import/export database.

If any one knows pls help me.

--Murali|||Well, maybe try to copy msdb ?
Or just transfer msdb's tables that are related to DTS ?|||The same explained in detail here (http://www.sqlservercentral.com/faq/viewfaqanswer.asp?categoryid=2&faqid=204) .