Can I inherited from a template report?

I must make many report in my project.

All the reports must have header of company name and footer of page number.

Can I desgin a template custom report, then all reports in my project are inherited from it?

This is very easy to do if you are developing your reports in Visual Studio. The templates for reports are stored at the following path:

C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies\ProjectItems\ReportProject\

You can create a template report and save it in this location, and then when you want to use it, from within your report project, click on the 'Project' menu option, and select 'Add New Item...'. When the dialog opens, you should see all .rdl files located in the above directory.

Hope that helps - Dan

can I export tables so that existing tables in destination database will be modified?

I'm working on an ASP.Net project where I want to test code on a local
machine using a local database as a back-end, and then export it to
the production machine where it uses the hosting provider's SQL Server
database on the back-end. Is there a way to export tables from one
SQL Server database to another in such a way that if a table already
exists in the destination database, it will be updated to reflect the
changes to the local table, without existing data in the destination
table being lost? e.g. suppose I change some tables in my local
database by adding new fields. Can I "export" these changes to the
destination database so that the new fields will be added to the
destination tables (and filled in with default values), without losing
data in the destination tables?

If I run the DTS Import/Export Wizard that comes with SQL Server and
choose "Copy table(s) and view(s) from the source database" and choose
the tables I want to copy, there is apparently no option *not* to copy
the data, and since I don't want to copy the data, that choice doesn't
work. If instead of "Copy table(s) and view(s) from the source
database", I choose "Copy objects and data between SQL Server
databases", then on the following options I can uncheck the "Copy
Data" box to prevent data being copied. But for the "Create
Destination Objects" choices, I have to uncheck "Drop destination
objects first" since I don't want to lose the existing data. But when
I uncheck that and try to do the copy, I get collisions between the
properties of the local table and the existing destination table,
e.g.:
"Table 'wbuser' already has a primary key defined on it."

Is there no way to do what I want using the DTS Import/Export Wizard?
Can it be done some other way?

-Bennett"Bennett Haselton" <bennett@.peacefire.org> wrote in message
news:e614455c.0411160123.294dfd10@.posting.google.c om...
> I'm working on an ASP.Net project where I want to test code on a local
> machine using a local database as a back-end, and then export it to
> the production machine where it uses the hosting provider's SQL Server
> database on the back-end. Is there a way to export tables from one
> SQL Server database to another in such a way that if a table already
> exists in the destination database, it will be updated to reflect the
> changes to the local table, without existing data in the destination
> table being lost? e.g. suppose I change some tables in my local
> database by adding new fields. Can I "export" these changes to the
> destination database so that the new fields will be added to the
> destination tables (and filled in with default values), without losing
> data in the destination tables?

In a word, no.

I'd suggest take the same version controlled scripts (you are of course
using version control, right? :-) you used to make the changes in Dev and
run those against the Prod server.

> If I run the DTS Import/Export Wizard that comes with SQL Server and
> choose "Copy table(s) and view(s) from the source database" and choose
> the tables I want to copy, there is apparently no option *not* to copy
> the data, and since I don't want to copy the data, that choice doesn't
> work. If instead of "Copy table(s) and view(s) from the source
> database", I choose "Copy objects and data between SQL Server
> databases", then on the following options I can uncheck the "Copy
> Data" box to prevent data being copied. But for the "Create
> Destination Objects" choices, I have to uncheck "Drop destination
> objects first" since I don't want to lose the existing data. But when
> I uncheck that and try to do the copy, I get collisions between the
> properties of the local table and the existing destination table,
> e.g.:
> "Table 'wbuser' already has a primary key defined on it."
> Is there no way to do what I want using the DTS Import/Export Wizard?
> Can it be done some other way?
> -Bennett|||"Greg D. Moore \(Strider\)" <mooregr_deleteth1s@.greenms.com> wrote in message news:<7Mnmd.16$1u.15@.twister.nyroc.rr.com>...
> "Bennett Haselton" <bennett@.peacefire.org> wrote in message
> news:e614455c.0411160123.294dfd10@.posting.google.c om...
> > I'm working on an ASP.Net project where I want to test code on a local
> > machine using a local database as a back-end, and then export it to
> > the production machine where it uses the hosting provider's SQL Server
> > database on the back-end. Is there a way to export tables from one
> > SQL Server database to another in such a way that if a table already
> > exists in the destination database, it will be updated to reflect the
> > changes to the local table, without existing data in the destination
> > table being lost? e.g. suppose I change some tables in my local
> > database by adding new fields. Can I "export" these changes to the
> > destination database so that the new fields will be added to the
> > destination tables (and filled in with default values), without losing
> > data in the destination tables?
> In a word, no.
> I'd suggest take the same version controlled scripts (you are of course
> using version control, right? :-) you used to make the changes in Dev and
> run those against the Prod server.

Uh, no... my "script" is that I move the mouse pointer over the
"wbuser" table in Server Explorer in VS .Net, right-click, pick
"Design Table", and add columns that way :)

So I guess I'll just have to remember to run that "script" against the
Prod database whenever I make any changes.

Still, I would have thought that what I was trying, would be a pretty
common thing for people to want to do... surprised it's not built-in.

-Bennett|||Bennett Haselton (bennett@.peacefire.org) writes:
> Uh, no... my "script" is that I move the mouse pointer over the
> "wbuser" table in Server Explorer in VS .Net, right-click, pick
> "Design Table", and add columns that way :)
> So I guess I'll just have to remember to run that "script" against the
> Prod database whenever I make any changes.
> Still, I would have thought that what I was trying, would be a pretty
> common thing for people to want to do... surprised it's not built-in.

There are third-party tools. Red Gate's SQL Compare is popular.

One reason that this is not built-in is that changes to tables is far
from always something that can be performed automatically. In our shop
we keep all table definitions under source control. To generate an update
script we have a tool that reads SourceSafe between two labels to load
all new and changed objects. For changed tables you get a script that
copies data from the old definition to the new, but this script is really
only a template. In many cases I need to change the script, because a
new column is supposed be initiated with some values derived from
other columns or tables. And there might be even wilder changes.

Point and click may be convenient, but for something which is to be
run several times needs to be automized to be sure that all are updates
are carried out the same way. Thus, you need a script.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

can i create a cube by VB.net

can i create a cube by VB.net?

I wanna a dynamic Cube, so I can not define it in the Analysis Services Project.

So the problems come.

I must deploy and process and compile the cube.

That can be done?

thank you.

Yes. This can be done using AMO (AnalysisServices Management Objects). In your project, add a reference to the AMO DLL and you can see lots of classes exposed.

|||

Can you clarify what you mean by "dynamic cube"? Just making sure that indeed you do need to create the cube programmatically using AMO.

By the way, there's a good sample in C# (which you'll probably be able to work from and translate to VB) at the following path after you install the SQL Server Samples:

C:\Program Files\Microsoft SQL Server\90\samples\Analysis Services\Programmability\AMO\AMOAdventureWorks\CS\AmoAdventureWorks

|||

Thank you for your replies.

AMO can do the work exactly.

END.

Can I copy cube from other project to my current project by using XMLA?

Hi, all here,

I have question about copying a cube from other project to my current project by using XMLA, if they have the same data source and data source view? Is it possible?

Thanks a lot for any guidance.

You might have a problem with matching dimension names and ID's.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

can I connect asp 1 to sqlserver 2005

Hi All,
I hopen this isnt a stupid question but can I connect asp 1 to sqlserver 2005? i want to update the database used on a ASP.net 1 project, I seem to be able to get it to connect but cant get it to see the tables?
Thanks for any help
LeeHi,
Were you able to try the connection to an SQL 2000, or other DBMS DB? I'm not up to speed on SQL Server 2005 yet (and as it's still hasn't been released, you may be having a problem peculiar to the beta version. Try posting your connection string (with any sensative parts x'ed out), along with more info on the particulars of your project. It always helps to provide enough information. BRN..|||

Hi Brian,

Thanks for helping!.

the connection string I am using in the web config is
<add key="ConnectionString" value="Server=xxxxx\SQLEXPRESS; Network=DBMSSOCN; Database=xxxxxx; Uid=xxxxx; Pwd=xxxxxx;"/>
SqlConnection sqlConn = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
SqlDataAdapter xxxxx= new SqlDataAdapter(strSql, sqlConn);

The error I get is
Invalid object name 'tbBob'
I forgot to mention I am using sql server 2005 express
Lee

|||There are two possible reasons for that, one you are in SQL Server Express without the Express Manager installed and two you are in the developer edition without management studio installed. Both are separate install the management studio is under management tools. Try the link below to download what you need. Hope this helps.
http://forums.asp.net/1069636/ShowPost.aspx|||Hi Caddre,
I am confused sorry...I need express manager to run a web page?
The error I get is from a web page .. the database isnt even on the same machine?
Lee|||No you don't need Express manager to run web pages but you need Express manager to manage SQL Server Express and there is no need for the database to be on the same machine. I am sorry did not intend to confuse you. Try the link below for Microsoft provided SQL permissions tutorial. Hope this helps.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnpag2/html/paght000010.asp

Can anyone explain what the pre-execute phase is doing for OLE DB data sources using sql com

I have a project which spans multiple servers and aggregates literally billions of rows of data into a much smaller and manageable result set which I store on another server. There are two stored procedures which take up 99.9% of the processing time. Each one of these SPs are estimated to run over 3 hours a piece (gives you an indication of how much data there really is). With in this SSIS package, I have two control flows, one for each SP. When I run the SSIS package from VS2005, I can see that there is a pre-execute phase which takes about an hour abnd a half to complete, and then move on to executing. My question is, what the heck is this, and what is it doing? I know it validates the sql in the procedure, but does it actually run the SP during that pre-execute phase? If it does, is there any way to get around that?

Any help would be appreciated.

Have tried to open a SQL profiler to see what is going on on the DB server during that period?

Rafael Salas

|||Yes, I have, and I did not see anything that stood to me as a procedure call to that SP. I just want to know why it takes so long to pre-execute.

Can AMO be used on VB.Net

Hi,

I want to program the Analysis Roles in AMO. Can I use VB.Net? What is the reference that I have to add to my project? Can you provide a simple sample connection.

Thanks a lot!

cherriesh

Hello,

you can find a kind of tutorial about AMO here

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

HTH

J?rg

Can a subscriber (anonymous...) *change* an article?

Hello,
In a project I work on, there's a server and many clients connected to
it. They go offline sometimes and before they do that they request
several items from the server which are then "downloaded" to the client
computer - they basically consist of several rows in related tables
(plus several full lookup tables). Then after they get back online, the
data is being synchronized. The problem is that the current code is
static and since we're implementing some changes we thought of changing
this too.
So my question is - can a subscriber dynamically set the query it needs?
Thanks in advance,
S. Neumann
While there are dynamic filters in merge replication, I wouldn't necessarily
suggest going down that route before finding out a bit more - it may be that
static filters based on some sort of flag would be more appropriate. Please
can you post up an example table schema and tell us in what way the data
required changes from one synchronization to another.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com/default.asp
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Hi,
Thanks for your quick reply!
The very basics of the db are:
Files table: ID, Name, Type
Documents table: ID, FileID, Name, DocPath
Type lookup table: ID, Name
(so there are many documents in each file). There are more tables in
such relations to the files table but two tables are enough for the
example.
Before the user goes offline, he asks to "download" several files he's
been working on to his laptop and what the program does is copy the
lookup tables to a local database (currently jet), copy the data about
the files the user requested, and the actual documents (referred to
with DocPath) as well.
When the user gets back online, changes he's made (eg. update Name
field in documents or files tables, delete a row, or any change to the
actual documents) are updated on the server.
Today we do this by using a saved log of all the changes made locally
(table name, row ID and what the change was - insert/update/delete).
The main problem we have with the way we do it now, is that since the
relations between several tables are going to change to many-to-many,
it'll be hard to keep track of which rows to actually copy to the
client and updating back to the server may also get complicated. For
instance, if the new schema will be like this:
Files table: ID, Name, Type
Documents table: ID, Name, DocPath
File-Document junction table: FileID, DocID
Without cascading delete, it'll be a pain to update the junction table.
If I locally delete a document that is connected to two files of which
I have only one downloaded, the other row in the file-document junction
table (for the file I didn't download) won't be deleted.
I wouldn't want the clients to have the whole database because it's
huge and they really only need the data for the files they work on when
offline.
So can replication help me with this?
Thanks in advance,
S. Neumann
Paul Ibison wrote:

> While there are dynamic filters in merge replication, I wouldn't
> necessarily suggest going down that route before finding out a bit
> more - it may be that static filters based on some sort of flag would
> be more appropriate. Please can you post up an example table schema
> and tell us in what way the data required changes from one
> synchronization to another. Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com/default.asp
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
|||Hi, The very basics of the db are:
Files table: ID, Name, Type
Documents table: ID, FileID, Name, DocPath
Type lookup table: ID, Name
(so there are many documents in each file). There are more tables in
such relations to the files table but two tables are enough for the
example.
Before the user goes offline, he asks to "download" several files he's
been working on to his laptop and what the program does is copy the
lookup tables to a local database (currently jet), copy the data about
the files the user requested, and the actual documents (referred to
with DocPath) as well.
When the user gets back online, changes he's made (eg. update Name
field in documents or files tables, delete a row, or any change to the
actual documents) are updated on the server.
Today we do this by using a saved log of all the changes made locally
(table name, row ID and what the change was - insert/update/delete).
The main problem we have with the way we do it now, is that since the
relations between several tables are going to change to many-to-many,
it'll be hard to keep track of which rows to actually copy to the
client and updating back to the server may also get complicated. For
instance, if the new schema will be like this:
Files table: ID, Name, Type
Documents table: ID, Name, DocPath
File-Document junction table: FileID, DocID
Without cascading delete, it'll be a pain to update the junction table.
If I locally delete a document that is connected to two files of which
I have only one downloaded, the other row in the file-document junction
table (for the file I didn't download) won't be deleted.
I wouldn't want the clients to have the whole database because it's
huge and they really only need the data for the files they work on when
offline.
So can replication help me with this?
Thanks in advance,
S. Neumann
Paul Ibison wrote:

> While there are dynamic filters in merge replication, I wouldn't
> necessarily suggest going down that route before finding out a bit
> more - it may be that static filters based on some sort of flag would
> be more appropriate. Please can you post up an example table schema
> and tell us in what way the data required changes from one
> synchronization to another. Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com/default.asp
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
|||Changing the initializing data retrospectively isn't possible directly
unless you have a separate publication for each subscriber, change the
filter clause and then reinitialize. However, using static filters, it is
possible to reallocate records at the highest level, and the effect will
propagate downwards by virtue of the join filters. This will allow you to
simulate changing the filtering dynamically. Practically speaking, there
would be have to be another table which relates files to subscriber., and
table joins would relate the other tables, while the static filter would
apply to the "File_Subscriber" table only. Checking out another file by a
subscriber would be an insert into the "File_Subscriber" table. Removing a
record from this table will propagate a series of deletes to the subscriber
for the Files and Documents tables of related records. I wouldn't recommend
using cascade deletes in the manner you suggested, as the result could be
unexpected for the reason you stated. Instead, I'd use a bit flag to
designate each record as 'current' and change that accordingly - either in
the stored procedure that initiates the change or in a trigger.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com/default.asp
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Calling webservice from CLR Stored Procedure

I would like to be able to call an webservice from a SP, I'm able to add a
webreference to the webservice in the database project, but how to call it i
n
code, and how to set up the code access illudes me?
any suggestions?
.LPHi LP,
What version of SQL Server are you using?
In SQL Server 2005 you can write a CLR to consume the webservice, check out
my article and samples here: http://sqlserverfaq.com?eid=58 there are
samples and even a blogcast of me showing exactly how to do it.
Tony.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
".LP" <.LP@.discussions.microsoft.com> wrote in message
news:3DF46544-C919-4C90-B101-2CC906A248E8@.microsoft.com...
>I would like to be able to call an webservice from a SP, I'm able to add a
> webreference to the webservice in the database project, but how to call it
> in
> code, and how to set up the code access illudes me?
> any suggestions?
> .LP|||I am using Sql Server 2005, but want to call an extarnal webservice (asmx)
from the clr-code Sp.
The purpose beeing; to send an notification from the database, from an
trigger with the ID of the Inserted/updated record
and the code I'm looking for is to call an webservice, not just consume xml.
.LP
"Tony Rogerson" wrote:

> Hi LP,
> What version of SQL Server are you using?
> In SQL Server 2005 you can write a CLR to consume the webservice, check ou
t
> my article and samples here: http://sqlserverfaq.com?eid=58 there are
> samples and even a blogcast of me showing exactly how to do it.
> Tony.
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlserverfaq.com - free video tutorials
>
> ".LP" <.LP@.discussions.microsoft.com> wrote in message
> news:3DF46544-C919-4C90-B101-2CC906A248E8@.microsoft.com...
>
>|||The code samples does call a webservice - the msn search webservice for one.
Basically, you need to use CLR to encapsulate the web service call.
Tony
--
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
".LP" <LP@.discussions.microsoft.com> wrote in message
news:8FAC07F5-81CE-468F-B1BC-DE91C9F8BDF8@.microsoft.com...
>I am using Sql Server 2005, but want to call an extarnal webservice (asmx)
> from the clr-code Sp.
> The purpose beeing; to send an notification from the database, from an
> trigger with the ID of the Inserted/updated record
> and the code I'm looking for is to call an webservice, not just consume
> xml.
> .LP
> "Tony Rogerson" wrote:
>

calling web service in stored procedure (SQL 2005)

I need to create a stored procedure (SQL 2005). I did it in C# (Visual Studio). The .dll file (inside) uses web services of Project Server 2007 (I just need to log on, create a project and log out). I prepared 2 files (exactly the same code): dll an exe. the .exe works fine. the problem is with .dll file (stored procedure in SQL)...

by the way: I created serialization file with sgen tool.

the problems start when I am trying to register these 2 .dlls in SQL server. I can do that in unsafe mode only. when I am trying to register them in externall access (normal dll) and safe mode (serialization .dll) I am getting errors:

CREATE ASSEMBLY failed because method "add_QueueCheckInProjectCompleted" on type "ConsoleApplication11.ProjectWebSvc.Project" in external_access assembly "ConsoleApplication11" has a synchronized attribute. Explicit synchronization is not allowed in external_access assemblies

thus I did it in unsafe mode (both of them). than I created the procedure.

Now, I tried to call that procedure. I am getting error:

A .NET Framework error occurred during execution of user defined routine or aggregate 'Main':

System.Net.WebException: The request failed with the error message:

--

<html><head><title>Object moved</title></head><body>

<h2>Object moved to <a href="http://my_comp:22278/projectserver/_layouts/1033/error.aspx?ErrorText=Object%20reference%20not%20set%20to%20an%20instance%20of%20an%20object%2E">here</a>.</h2>

</body></html>

--.

System.Net.WebException:

at System.Web.Services.Protocols.SoapHttpClientProtocol.ReadResponse(SoapClientMessage message, WebResponse response, Stream responseStream, Boolean asyncCall)

at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String methodName, Object[] parameters)

at ConsoleApplication11.ProjectWebSvc.Project.QueueCreateProject(Guid jobUid, ProjectDataSet dataset, Boolean validateOnly)

at ConsoleApplication11.Program.Main()

That means "Object reference not set to an instance of an object". the problem occurs when calling QueueCreateProject method

I dont understand that error. I wrote the same code for .dll and .exe. the .exe file is working ok (no errors) but .dll (as a stored procedure) shows this error

my code in C#:

using System;

using System.Collections.Generic;

using System.Text;

using System.Data.SqlClient;

using System.Net;

using PSLibrary = Microsoft.Office.Project.Server.Library;

namespace ConsoleApplication11

{

public class Program

{

public static void Main()

{

const string LOGINWINDOWS = "_vti_bin/PSI/LoginWindows.asmx";

string baseUrl = "http://localhost:22278/projectserver/";

CookieContainer cookies = new CookieContainer();

LoginWindowsWebSvc.LoginWindows loginWindows = new LoginWindowsWebSvc.LoginWindows();

loginWindows.Url = baseUrl + LOGINWINDOWS;

loginWindows.Credentials = CredentialCache.DefaultCredentials;

loginWindows.Login();

ProjectWebSvc.Project project = new ProjectWebSvc.Project();

project.Credentials = loginWindows.Credentials;

project.Url = baseUrl + "_vti_bin/psi/project.asmx";

ProjectWebSvc.ProjectDataSet dsProject = new ProjectWebSvc.ProjectDataSet();

ProjectWebSvc.ProjectDataSet.ProjectRow projectRow = dsProject.Project.NewProjectRow();

Guid projectGuid = Guid.NewGuid();

projectRow.PROJ_UID = projectGuid;

projectRow.PROJ_TYPE = 0;

projectRow.PROJ_NAME = "Jakis";

projectRow.PROJ_SESSION_UID = Guid.NewGuid();

dsProject.Project.AddProjectRow(projectRow);

Guid jobGuid = Guid.NewGuid();

project.QueueCreateProject(jobGuid, dsProject, false);

System.Threading.Thread.Sleep(5000);

loginWindows.Logoff();

project.Credentials = null;

}

}

}

any help will be appreciated. my email is: stro.na@.interia.pl

Dear fcjh,

I too am trying to call a web service from a stored procedure in SQL Server 2005. I have followed the advice in the following very useful article:

http://www.awprofessional.com/articles/article.asp?p=473457&seqNum=10&rl=1

I have built a solution in Visual Studio 2005 to do this. In order to follow the article as closely as possible, I have three projects in the solution:

1) The web site including the web service.

2) A SQL Server Project for the Stored Procedure, which calls a User Defined Function, defined in the same project. This successfully deploys both to SQL Server 2005 and allows me to step into them to debug. The code of the User Defined Function (from the above article) is:

[Microsoft.SqlServer.Server.SqlFunction]
[return: SqlFacet(Precision = 9, Scale = 2)]
public static decimal GetStockWS(string symbol)
{
Decimal price;
using (WindowsIdentity id = SqlContext.WindowsIdentity)
{
WindowsImpersonationContext c = id.Impersonate();
StockService s = new StockService();
// use the current credentials
s.Credentials =
System.Net.CredentialCache.DefaultNetworkCredentials;
price = s.GetStockPrice(symbol);
c.Undo();
}
return price;
}

3) A SQL Server Project for the Web Service Proxy. This also appears to deploy to SQL Server successfully.

However, at the point where the User Defined Function invokes the constructor of the Web Service Proxy (statement StockService s = new StockService(); above), I get an exception as follows:

.NET Framework execution was aborted. The UDP/UDF/UDT did not revert thread token.

I can't find anything useful about the cause of this. Has anyone else succeeded in calling a web service from SQL Server 2005?

Any help would be much appreciated.

|||Hi there,

You might find Vineet's post here useful.

Cheers,|||

Dear Isaac,

Vineet's post is excellent. Everything now works perfectly and turns out to be very simple, thanks to the power of the Visual Studio 2005 SQL Server Project type and the use of a Web Reference.

Thanks very much for pointing me to that article.