Is it possible to put in "IF ...ELSE" or "Case" in WHERE CLAUSE?

Dear all...

need your help... i am now trying to create a report using SQL reporting services... when declare all the @.parameters needed in where clause, i have come across a problem. where one of the parameters that prompting user to key in...i need to put in some condition.

select..............(blah blah).....
.....(SELECT CASE WHEN
((SELECT COUNT(*)
FROM tbl_OutpatientReg OPT
WHERE OPT.PatientID = tbl_Patient.PatientID)) = 1 THEN 0 ELSE 1 END) AS PTType .....................(blah blah)......

where (CONVERT(Varchar(10), tbl_OutpatientReg.VisitDatetime, 103) BETWEEN @.FromDate AND @.ToDate) OR (@.FromDate = ' ') OR (@.ToDate = ' ')
AND (@.PatientType = CASE WHEN
(SELECT COUNT(*)
FROM tbl_OutpatientReg OPT
WHERE OPT.PatientID = tbl_Patient.PatientID) = 1 THEN 0 ELSE 1 END)

my situition is something like above, i know i have done something wrong in teh WHERE clause for the @.PatientType... can i ask how to restrict the parameters entered by user, let's say if user enter parameter "0", then the visitcount is 1, if enter "1" then the visit count refers to more than 1...

thanks in advanced ...............Your post is kind of confusing regarding requirements, but part of your problem may be due to using brackets where they are not necessary, and not using them where they might be necessary to specify logical operations.

--Your Version:
where (CONVERT(Varchar(10), tbl_OutpatientReg.VisitDatetime, 103) BETWEEN @.FromDate AND @.ToDate)
OR (@.FromDate = ' ')
OR (@.ToDate = ' ')
AND (@.PatientType = CASE
WHEN (SELECT COUNT(*)
FROM tbl_OutpatientReg OPT
WHERE OPT.PatientID = tbl_Patient.PatientID) = 1 THEN 0
ELSE 1
END)

--Unnecessary brackets removed:
where CONVERT(Varchar(10), tbl_OutpatientReg.VisitDatetime, 103) BETWEEN @.FromDate AND @.ToDate
OR @.FromDate = ' '
OR @.ToDate = ' '
AND @.PatientType = CASE
WHEN (SELECT COUNT(*)
FROM tbl_OutpatientReg OPT
WHERE OPT.PatientID = tbl_Patient.PatientID) = 1 THEN 0
ELSE 1
END

--Useful brackets added:
where (CONVERT(Varchar(10), tbl_OutpatientReg.VisitDatetime, 103) BETWEEN @.FromDate AND @.ToDate
OR @.FromDate = ' '
OR @.ToDate = ' ')
AND @.PatientType = CASE
WHEN (SELECT COUNT(*)
FROM tbl_OutpatientReg OPT
WHERE OPT.PatientID = tbl_Patient.PatientID) = 1 THEN 0
ELSE 1
END|||ya...thanks for reminding me...as there are too many parameters to pass, i also confused... ;) anyway, really appreciate ur help

Is it possible to prevent databases from being copied?

Hi,

We have a point of sale application (C# .NET 2.0) and a Sql Server 2005 database back end.

Our customers are concerned that employees could create a backup of the SQL Server database (or even of the MDF file) and use it to steel customer data.

Very often, the application is running on a single PC in a shop using Sql Server Express Edition 2005 under Windows XP. The users usually log on as local administrator. It's hard for us to force our customers to change their local security policies.

Ideally, I would like some form of security mechanism that prevents a backup from being restored on to another PC without either a password or some other form of authentication.

Is this possible?

Regards,

Sigol.

I'm assuming you meant 'steal' (to take), rather than 'steel' (to harden).

There are several issues.

1. As you noted, a backup 'could' be restored on another server. Various third party backup programs allow passwords and encryption for backups. Any SQL Admin can create a backup that can be restored elsewhere.
2. A SQL administrator could take the database 'offline' for a few minutes and copy the data file (*.mdf)
3. A local administrator could shut down the SQL Service for a few minutes and copy the data file (*.mdf).
4. Even with an Encrypted database, or tables, or even specific columns, a local SQL Administrator can usually get around the protections.

Don't allow any local administrators to be in the SQL Admins role.

So if you are concerned about protecting a database, the 'best' solutions, in a situation where you can't control the local administrators, is to look into database encryption using encryption keys, or better yet, certificates.

|||

Thank you for your comments, Arnie. This was very helpful to me.

Regards,

Sigol.

|||

Arnie Rowland wrote:

I'm assuming you meant 'steal' (to take), rather than 'steel' (to harden).

Having a grammatically bad day, Arnie!

sql

Is it possible to manually run a subscription?

I'm wondering if there's a way through code to manually trigger a timed
subscription to run? The only thing I can see is to create an additional
schedule on the fly set for a few minutes in advance.
Any help would greatly be appriciated.
JoshYou can open Enterprise Manager and find the job with the same
scherduled time, right click and tell it to run.|||holy crap, no wonder sql server agent has to be running. I'm a bit
ashamed I never investigated this before.
I actually need to fire off the subscription through code. I'd imagine
this could be done then through SQLDMO.
Thanks for your response.
Lon wrote:
> You can open Enterprise Manager and find the job with the same
> scherduled time, right click and tell it to run.
>

Is it possible to make "proxy tables" win SQL-server 2000?

Hello
I am coming from Sybase and are learning SQL-Server now.
In Sybase there is a feature called "proxy table" which makes it possible to create a proxy table that really exists in another database. But it appears a ordinary local table to a user. Does a similar feature exists in SQL-server? Anyone knows?
Thanks
Per
Per,
No, but you can fully qualify the object in the other database to access
it. i.e.
select * from database2.dbo.objectname
You will need the appropriate permissions on the other object.
You may also wish to look at cross-database ownership chaining in the
updated Books online available from www.microsoft.com/sql
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Per wrote:
> Hello
> I am coming from Sybase and are learning SQL-Server now.
> In Sybase there is a feature called "proxy table" which makes it possible to create a proxy table that really exists in another database. But it appears a ordinary local table to a user. Does a similar feature exists in SQL-server? Anyone knows?
> Thanks
> Per
|||Hi
To add to Marks post...you can also create a view in the "current" database.
That would mean that the three part name is only require in the view
definition.
John
"Per" <anonymous@.discussions.microsoft.com> wrote in message
news:CD9FFDA6-BCE1-4D83-8960-1D612462424D@.microsoft.com...
> Hello
> I am coming from Sybase and are learning SQL-Server now.
> In Sybase there is a feature called "proxy table" which makes it possible
to create a proxy table that really exists in another database. But it
appears a ordinary local table to a user. Does a similar feature exists in
SQL-server? Anyone knows?
> Thanks
> Per

Is it possible to make "proxy tables" win SQL-server 2000?

Hello
I am coming from Sybase and are learning SQL-Server now.
In Sybase there is a feature called "proxy table" which makes it possible to
create a proxy table that really exists in another database. But it appears
a ordinary local table to a user. Does a similar feature exists in SQL-serv
er? Anyone knows?
Thanks
PerPer,
No, but you can fully qualify the object in the other database to access
it. i.e.
select * from database2.dbo.objectname
You will need the appropriate permissions on the other object.
You may also wish to look at cross-database ownership chaining in the
updated Books online available from www.microsoft.com/sql
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Per wrote:
> Hello
> I am coming from Sybase and are learning SQL-Server now.
> In Sybase there is a feature called "proxy table" which makes it possible
to create a proxy table that really exists in another database. But it appea
rs a ordinary local table to a user. Does a similar feature exists in SQL-se
rver? Anyone knows?
> Thanks
> Per|||Hi
To add to Marks post...you can also create a view in the "current" database.
That would mean that the three part name is only require in the view
definition.
John
"Per" <anonymous@.discussions.microsoft.com> wrote in message
news:CD9FFDA6-BCE1-4D83-8960-1D612462424D@.microsoft.com...
> Hello
> I am coming from Sybase and are learning SQL-Server now.
> In Sybase there is a feature called "proxy table" which makes it possible
to create a proxy table that really exists in another database. But it
appears a ordinary local table to a user. Does a similar feature exists in
SQL-server? Anyone knows?
> Thanks
> Per

Is it possible to grant a login the permission to create and schedule jobs?

I have a user who does not need to have any other server permissions other
than to create and schedule jobs, but I'm going round in circles trying to
figure out if I can actually do this using BOL as a resource. Is it possible
and if so, how?
TIA
Michael MacGregor
Database ArchitectMichael,
Yes it is possible, as long as the user is the job owner. The BOL says that
sp_add_job is public, which means that anyone who is a user of msdb (which
you may not have granted to everyone) should be able to create a job. So
first, the user needs rights to msdb.
*** SQL 2000 - I remember that we needed to do more, as show below, but no
longer remember all the reasons.
You can create a role, such as AgentJobManager and grant specific rights.
Users will (as non-sysadmins) be limited to managing the jobs that they
personally create.
GRANT EXECUTE ON sp_add_category TO AgentJobManager
GRANT EXECUTE ON sp_add_job TO AgentJobManager
GRANT EXECUTE ON sp_add_jobschedule TO AgentJobManager
GRANT EXECUTE ON sp_add_jobstep TO AgentJobManager
GRANT EXECUTE ON sp_delete_category TO AgentJobManager
GRANT EXECUTE ON sp_delete_job TO AgentJobManager
GRANT EXECUTE ON sp_delete_jobschedule TO AgentJobManager
GRANT EXECUTE ON sp_delete_jobstep TO AgentJobManager
GRANT EXECUTE ON sp_post_msx_operation TO AgentJobManager
GRANT EXECUTE ON sp_update_category TO AgentJobManager
GRANT EXECUTE ON sp_update_job TO AgentJobManager
GRANT EXECUTE ON sp_update_jobschedule TO AgentJobManager
GRANT EXECUTE ON sp_update_jobstep TO AgentJobManager
GRANT SELECT ON syscategories TO AgentJobManager
GRANT SELECT ON sysjobsTO AgentJobManager
GRANT SELECT ON sysjobserversTO AgentJobManager
Then make the necessary users member of the AgentJobManager role.
*** SQL 2005
Make the users members of one of the following roles, according to what
rights you want them to have:
SQLAgentOperatorRole
SQLAgentReaderRole
SQLAgentUserRole
RLF
"Michael MacGregor" <nospam@.nospam.com> wrote in message
news:uxaKhSVyHHA.4004@.TK2MSFTNGP05.phx.gbl...

>I have a user who does not need to have any other server permissions other
>than to create and schedule jobs, but I'm going round in circles trying to
>figure out if I can actually do this using BOL as a resource. Is it
>possible and if so, how?
> TIA
> Michael MacGregor
> Database Architect
>|||Thanks Russell. It would have probably taken me a long time to figure that
out.
MTM

Is it possible to generate alter Table statements using SMO

Hi

I'm trying to modify existing tables in a database.

How can I create alter Table scripts using SMO/DMO

Thank you

Yep, you can use the following to either execute and capture, just execute (which is the default) or just capture the executed commands:

Server s = new Server(".");

s.ConnectionContext.SqlExecutionModes = Microsoft.SqlServer.Management.Common.SqlExecutionModes.CaptureSql

//Microsoft.SqlServer.Management.Common.SqlExecutionModes.CaptureSql

//Microsoft.SqlServer.Management.Common.SqlExecutionModes.ExecuteAndCaptureSql

//Microsoft.SqlServer.Management.Common.SqlExecutionModes.ExecuteSql

//s.ConnectionContext.CapturedSql.Text; //Get the Text

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Thanks again Jens

I'm trying the follwong code

Server server1 = new Server(".");

Database db= server1.Databases["master"];

server1.ConnectionContext.SqlExecutionModes = SqlExecutionModes.CaptureSql;

foreach (Table Tbl in db.Tables)

{

tabl.Alter ();

}

db1.Refresh();

//writing to a file

writeToFile(server1.ConnectionContext.CapturedSql.Text, "alter", "tables");

But it is not generating Alter statments.

But if I use Create(), in place of alter(), it's generating Create statments.

|||Hi,
if you do not change anything, what are you supposed to see in the ALTER script :-) ?

In this sample I added a column to the table resulting in a script with an ALTER Script and an ADD column command.

Server s = new Server(".");

s.ConnectionContext.SqlExecutionModes = Microsoft.SqlServer.Management.Common.SqlExecutionModes.CaptureSql;

Table t = s.Databases["SMOTest"].Tables["TestTable"];

t.Columns.Add(new Column(t,"SomeSMOTest",DataType.DateTime));

t.Alter();

foreach (string st in s.ConnectionContext.CapturedSql.Text)

{

Console.WriteLine(st);

}

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Thanks Jens

Got it.

while comparing a table in one database to other table (identical) in other database,

if the Source table has some modified(altered) columns and need to be modified in the target table.

How to solve this problem.Any Idea.

I generated the alter scripts manually for each column.

Like

ALTER TABLE [dbo].[wo]

ADD [requested-time] varchar (8 ) NULL

Thank you

|||You will have to do this manually. Load the two schemas and compare the columns (if you just want to check the columns) with each other. Change the columns appropiately with SMO and get the script from the Context. if you want an integrated tool which can do this on its own use Visual Studio for database professionals, this does have a comparer and script generator for keeping the databases in sync.

HTH, Jens K. Suessmeyer.

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

Thanks alot Jens.

I'll try for this

Is it possible to dynamically create columns in a table in SSRS

Hi,

I have a sproc that returns somevalues and everything is working fine... and in my reports i am assigning the header data (in a detail column) based on the some feilds in the sproc... and there around 20 feilds that i want to show... but at a given time i am pretty sure that there wont be more than 10 fields that will have data.

So is it possible that show only the columns that have data in it and sometimes if there is less that 5 - 6 fields.. i want to realign the widths of the column in those tables without shrinking the size of the Table...

any help is appreciated..

Regards

Karen

Hi,

So is it possible that show only the columns that have data in it and sometimes if there is less that 5 - 6 fields.. i want to realign the widths of the column in those tables without shrinking the size of the Table...

If there's no record in one of your row, you can use =IsNothing(Fields!productname.Value) filter your record. But if you want to hide the column that has no data, I suggest you to handle these works in your data accessing modular. For example, if you check one of your column is empty, just remove the column in your record set, so the column would not show in the report.

Thanks.

|||

Jin,

Thanks for your response.. what do u mean by remove the column from the recordset... cause i am populating the Reports using a stored procedure and sometimes... there may be some data or not..

Can u pls give me code snipet or an example

Regards

Karen

|||

Hi,

if you check one of your column is empty, just remove the column in your record set, so the column would not show in the report.

Here's the sample code, suppose you have two fields, Sp and Hd. If there's no data in Hd field of your return set, then only Sp field would been selected.

DECLARE @.NULLCOUNTINTSELECT @.NULLCOUNT =COUNT(*)FROM MatrixCapitalWHERE MatrixCapital.Spisnot nullif @.NULLCOUNT=0BEGIN SELECT MatrixCapital.HdFROM MatrixCapitalEND ELSEBEGIN SELECT MatrixCapital.Hd,MatrixCapital.SpFROM MatrixCapitalEND

Thanks.|||

Jin,

Thanks a lot for your answer so this mean that if i have more 5 - 6 columns NULL... i have check for each column in the NULL count and then prob union for each feild so that my end resultset will be columns that has data in them?

I have another question too.. if i have a table variable like

Declare @.tbl table

(

tblid int indentity(1,1),

Col 1,

Col 2,

..,

Col n

)

Is it possible to have a variable column size depending on the number of entries in my select column like for example.. my if i have 10 columns in my select statement and in that 5 are null... so can just insert 5 rows to the table and then remove the remaining columns out.

Regards

Karen

|||

Hi,

i have check for each column in the NULL count and then prob union for each feild so that my end resultset will be columns that has data in them?

Yes, that's right.

Is it possible to have a variable column size depending on the number of entries in my select column like for example..

Based on my knowledge, another way i can see is to use CASE WHEN clause in your SQL, but it still requires you to give differrent sql statments accoring to the "isNUll" result of a column.

Thanks.

sql

Is it possible to create thread & start from CLR Stored Proc

My simple CLR Stored procedure is as below:

[Microsoft.SqlServer.Server.SqlProcedure]
public static int MyParallelStoredProc(string name1, string name2)
{
Thread t = null;
Worker wth = null;
int parallel = 2;
Object[] obj = new object [parallel];
SqlPipe p;
p = SqlContext.Pipe;

for (int i = 0; i < parallel; i++)
{
if (i == 0)
wth = new Worker(name1);
else
wth = new Worker(name2);
t = new Thread(new System.Threading.ThreadStart(wth.WorkerProc));
t.Name = "Thread -" + i.ToString() + ":";
t.Start();
p.Send(t.Name + ":Started");
obj[ i] = t;
}
for (int i = 0; i < parallel; i++)
{
t = (System.Threading.Thread)obj[ i];
t.Join();
p.Send(t.Name + ":Finished");
}
return 0;
}

The worker class implementing Thread Proc:

public class Worker
{
private string Name;

public Worker(string name)
{
SqlPipe p;
p = SqlContext.Pipe;
Name = name;
p.Send("In Constructor:" + Name);
}

public void WorkerProc()
{
SqlPipe p;
p = SqlContext.Pipe;
for (int i = 0; i < 10; i++)
p.Send(i.ToString()+":"+Name);
}
}

The assembly is registered with UNSAFE permission set.

CREATE ASSEMBLY
ThreadTest
FROM
'C:\\ThreadTest\bin\Debug\ThreadTest.dll'
WITH
permission_set = unsafe;
GO

CREATE PROC ParallelStoredProc
@.Name1 NVARCHAR(1024),
@.Name2 NVARCHAR(1024)
AS
EXTERNAL NAME ThreadTest.[MyTest.ThreadTest].MyParallelStoredProc

When I invoke the the stored procedure from T-SQL script as below,

EXEC ParallelStoredProc @.Name1, @.Name2

the thread class constructor gets called; but the 'WorkerProc' does not execute ?

Whether an UNSAFE assembly is allowed to spawn threads

inside SQL Server ?

Your code works correctly to start and run threads under unsafe. The reason you think it doesn't work is because the SqlContext connection is not available on new threads, so you can't use it to Pipe.Send information back.

If you try/catch for exceptions in your WorkerProc, you should see an error like the following:

"The requested operation requires a Sql Server execution thread. The current thread was started by user code or other non-Sql Server engine code."

Steven

|||

Thanks steve. Your input was very useful.

If I use SqlConnection in WorkerProc, the thread gets aborted

and goes into "Stopped" state.

It means the main CLR Stored proc can only execute the T-SQL commands ?

WorkerProc's are restricted to computations.

Is it possible to create the columns of a #Temp table base on a query result?

I’ve been trying without success something like this…

CREATE TABLE #table

(

(SELECT MAX(Column) FROM Table) varchar(50)

)

I'm working with SQL 2005; thank you for any help in advance.

Not quite like that.

You can SELECT ... INTO.

In the process, you need to provide a column Name for any computed or derived columns, and you can change the datatype.

Something like this:

Code Snippet

USE Northwind
GO

SELECT cast( max( EmployeeID ) AS decimal(6,2)) AS MaxEmp
INTO #MyTable
FROM Employees

|||

There are 2 options,

Create Table #Table (

ColumnValue Varchar(50)

);

Insert Into #Table

SELECT MAX(Column) FROM TableName;

--OR

SELECT Cast(MAX(Column) as Varchar(50)) ColumnValue Into #Table FROM TableName;

|||Thank you for the help provided so far. Another question on the same subject. How can the Column name be assigned dynamically after a query result? Thanks.
|||

You should use the aliase name. if you failed to give the aliase name for the expression sql server thow an error says that "No column was specified for column n on tablename".

Code Snippet

Select Max(Column) as MaxColumn Into NewTable From OldTable

Select Max(Column) MaxColumn into Newtable From OldTable

|||

As indicated above, No.

My apologies, I guess this statement was not clear enough.

In the process, you need to provide a column Name for any computed or derived columns...

sql

Is it possible to create some kind of "libraries"?

Hi,
I want to reuse parts of my last report in a new one -
is it possible to create some kind of "libraries" (or templates) in
Reporting Services?
Thanks.Now you can create your custom Report Templates, and keep it in managed code
and reuse it!, in this example will show you, how to sub-class
the Report Class and create your report templates for further reuse.
http://www.rdlcomponents.com/examples/inherited/inherited.aspx
Thanks
Jerry
"Hawkeye" wrote:
> Hi,
> I want to reuse parts of my last report in a new one -
> is it possible to create some kind of "libraries" (or templates) in
> Reporting Services?
> Thanks.
>

Is it possible to create reports in SSRS 2005 by mining data in SQL Server 2000

All,

I have found a lot of comments on the net but nowhere was I able to have a direct answer to this question:
My client has a complex database deployed on SQL Server 2000. He wishes to create reports based on this data using SSRS. Another division of the same company just implemented SQL Server 2005 and SSRS 2005.
I wish to know if SSRS 2005 is able to create reports based on data from SQL Server 2000?

Thank you.
S
Sure. You'll have no problems reporting against 2005 and 2000 together...

is it possible to create one RDL file that consists of main report and subreport?

is it possible to create one RDL file that consists of main report and
subreport?Not in the current version. Embedding subreports into the main RDL file is
on our future features wishlist.
--
This post is provided 'AS IS' with no warranties, and confers no rights. All
rights reserved. Some assembly required. Batteries not included. Your
mileage may vary. Objects in mirror may be closer than they appear. No user
serviceable parts inside. Opening cover voids warranty. Keep out of reach of
children under 3.
"erdi" <erdincugurlu@.hotmail.com> wrote in message
news:%23Yxn3WjdEHA.2408@.tk2msftngp13.phx.gbl...
> is it possible to create one RDL file that consists of main report and
> subreport?
>

Is it possible to create libraries to reuse parts of my report?

Is it possible to create libraries to reuse parts of my report? For Example
my first page looks always the same - image, title (of courese not always the
same) and three subtitles
Thank youInherited Report
Now you can create your custom Report Templates, and keep it in managed code
and reuse it!, in this example will show you, how to sub-class
the Report Class and create your report templates for further reuse.
http://www.rdlcomponents.com/examples/inherited/inherited.aspx
"Hawkeye" wrote:
> Is it possible to create libraries to reuse parts of my report? For Example
> my first page looks always the same - image, title (of courese not always the
> same) and three subtitles
> Thank you
>|||... I don't want to buy further tools!!!!
"Jerry" wrote:
> Inherited Report
> Now you can create your custom Report Templates, and keep it in managed code
> and reuse it!, in this example will show you, how to sub-class
> the Report Class and create your report templates for further reuse.
> http://www.rdlcomponents.com/examples/inherited/inherited.aspx
>
> "Hawkeye" wrote:
> > Is it possible to create libraries to reuse parts of my report? For Example
> > my first page looks always the same - image, title (of courese not always the
> > same) and three subtitles
> >
> > Thank you
> >|||You can do by yourself w/o buy anything, but if you have enough time:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSAMPLES/htm/rss_tutorials_v1_02ua.asp
"Hawkeye" wrote:
> ... I don't want to buy further tools!!!!
> "Jerry" wrote:
> > Inherited Report
> >
> > Now you can create your custom Report Templates, and keep it in managed code
> > and reuse it!, in this example will show you, how to sub-class
> > the Report Class and create your report templates for further reuse.
> >
> > http://www.rdlcomponents.com/examples/inherited/inherited.aspx
> >
> >
> > "Hawkeye" wrote:
> >
> > > Is it possible to create libraries to reuse parts of my report? For Example
> > > my first page looks always the same - image, title (of courese not always the
> > > same) and three subtitles
> > >
> > > Thank you
> > >

Is it possible to create dynamic reports using reporting services

I have a requirement to create dynamic reports for my client.

once i create these reports then the user will choose columns of there choice.

so the columns may belong to multiple tables.

Now the report should get generated with the layout etc. is it possible.

since our project is totally on the webserver(webbased.)

please if you can provide me with any links with dynamic report creation wizards.

and also we only use Stored procedures via database.

which is best is writing the entire queries right behind the layouit or calling the entire logic via Stored procedure. i am a bit confused. this is my first project working on reports itself.

Thank you all for the helpful information.

Sounds like you need to generate the report definitions programatically. The RDL Object Model code sample in this download should help.sql

Is it possible to create cube file without Microsoft Analysis Services

Can I create a cube file, .cub ,without the Microsoft Analysis Services
Pls Guide me as I am new in this field.
Thanks
LoydI think that other products (Cognos, MicroStrategy, possibly others) may let you do this, but I am not certain.

I do know that MS Excel will let you OPEN a .CUB file, but you can't create a .CUB file.

Regards,

hmscott

Can I create a cube file, .cub ,without the Microsoft Analysis Services

Pls Guide me as I am new in this field.

Thanks
Loyd|||Hi Loyd you can create a .cub file using MSQuery in presnt in MS office.

What do you intend to use it for??

Try and let me know.

Regards,
Hemanrh|||You can create .cub file with MS excel bat you need anyway Analysis Services to do it.
I don't know any other programs

Is it possible to create clustered index for multi tables within a SQL Server 2005 database with

Hi, all,

I am having up to serveral hundred tables within a SQL Server 2005 database, up to 200 of them are without any clustered index. Is it possible to create clustered indexes for all of them together in a same query? :)

Thanks a lot for any guidance and advices for that.

With best regards,

Yours sincerely,

Yes, it is possible with WHILE loop and dynamic SQL (with eithersp_executesql or EXEC statement) that calls CREATE INDEX. However, the tricky part is you have to specify the column(s) for each table to be included in the index in the CREATE INDEX statement. So if you have a way to programmatically get the table names and the index column(s) for each table, it is easy to do the rest.|||

Hi, Hugh Qu,

Thank you very much for your kind guidance and advices. Got the ideas, very appreciated.

With best regards,

Yours sincerely,

Is it possible to create clustered index for multi tables within a SQL Server 2005 database with

Hi, all,

I am having up to serveral hundred tables within a SQL Server 2005 database, up to 200 of them are without any clustered index. Is it possible to create clustered indexes for all of them together in a same query? :)

Thanks a lot for any guidance and advices for that.

With best regards,

Yours sincerely,

Yes, it is possible with WHILE loop and dynamic SQL (with eithersp_executesql or EXEC statement) that calls CREATE INDEX. However, the tricky part is you have to specify the column(s) for each table to be included in the index in the CREATE INDEX statement. So if you have a way to programmatically get the table names and the index column(s) for each table, it is easy to do the rest.|||

Hi, Hugh Qu,

Thank you very much for your kind guidance and advices. Got the ideas, very appreciated.

With best regards,

Yours sincerely,

Is it possible to create clustered index for multi tables within a SQL Server 2005 database

Hi, all,

I am having up to serveral hundred tables within a SQL Server 2005 database, up to 200 of them are without any clustered index. Is it possible to create clustered indexes for all of them together in a same query? :)

Thanks a lot for any guidance and advices for that.

With best regards,

Yours sincerely,

Yes, it is possible with WHILE loop and dynamic SQL (with either sp_executesql or EXEC statement) that calls CREATE INDEX. However, the tricky part is you have to specify the column(s) for each table to be included in the index in the CREATE INDEX statement. So if you have a way to programmatically get the table names and the index column(s) for each table, it is easy to do the rest.|||

Hi, Hugh Qu,

Thank you very much for your kind guidance and advices. Got the ideas, very appreciated.

With best regards,

Yours sincerely,

Is it possible to create CLR function call legacy C++ models?

I tried to convert an legacy C++ program to managed C++ and build a CLR
function. However, too much compiler error. Is it possible to build a CLR
function and call/link.. the legacy C++ code?examnotes <nick@.discussions.microsoft.com> wrote in
news:DA2E1EC7-EFDB-4111-B3A9-0314A8FAA394@.microsoft.com:

> I tried to convert an legacy C++ program to managed C++ and build a
> CLR function. However, too much compiler error. Is it possible to
> build a CLR function and call/link.. the legacy C++ code?
>
As (I believe) I replied earlier to you, you need to either access the C++
object through P/Invoke or COM Interop. In your case it sounds like
P/Invoke is the most likely way of getting to the code, i.e. your C++
object is not a COM component.
Niels
****************************************
**********
* Niels Berglund
* http://staff.develop.com/nielsb
* nielsb at develop dot com
* "A First Look at SQL Server 2005 for Developers"
* http://www.awprofessional.com/title/0321180593
****************************************
**********|||Thanks. Sorry for repost.
One thing I want to know is,
It sounds CLR function required /clr:safe, can safe code still do platform
invoke?
"Niels Berglund" wrote:

> examnotes <nick@.discussions.microsoft.com> wrote in
> news:DA2E1EC7-EFDB-4111-B3A9-0314A8FAA394@.microsoft.com:
>
> As (I believe) I replied earlier to you, you need to either access the C++
> object through P/Invoke or COM Interop. In your case it sounds like
> P/Invoke is the most likely way of getting to the code, i.e. your C++
> object is not a COM component.
> Niels
>
> --
> ****************************************
**********
> * Niels Berglund
> * http://staff.develop.com/nielsb
> * nielsb at develop dot com
> * "A First Look at SQL Server 2005 for Developers"
> * http://www.awprofessional.com/title/0321180593
> ****************************************
**********
>|||examnotes <nick@.discussions.microsoft.com> wrote in
news:7205E76E-7B6D-4572-B5EF-B01A7E312456@.microsoft.com:

> It sounds CLR function required /clr:safe, can safe code still do
> platform invoke?
>
In order to do P/Invoke you need to create your assembly in SQL as unsafe.
Niels
****************************************
**********
* Niels Berglund
* http://staff.develop.com/nielsb
* nielsb at develop dot com
* "A First Look at SQL Server 2005 for Developers"
* http://www.awprofessional.com/title/0321180593
****************************************
**********|||"Niels Berglund" <nielsb@.nospam.develop.com> wrote in message
news:Xns97AADD8FEE8DEnielsbdevelopcom@.20
7.46.248.16...
> examnotes <nick@.discussions.microsoft.com> wrote in
> news:7205E76E-7B6D-4572-B5EF-B01A7E312456@.microsoft.com:
>
> In order to do P/Invoke you need to create your assembly in SQL as unsafe.
>
Running unsafe code in the database is, well, unsafe. Your legacy C++ code
can crash SQL Server. So you should check with whoever owns the production
SQL Server you will target and make sure they are OK with you running this
code inside the database.
David|||Organization: DevelopMentor
Message-ID: <Xns97AB12F509BB6nielsbdevelopcom@.207.46.248.16>
User-Agent: Xnews/2006.03.14
Newsgroups: microsoft.public.sqlserver.programming
Date: Wed, 19 Apr 2006 17:51:47 -0700
NNTP-Posting-Host: 69.177.80.118.adsl.snet.net 69.177.80.118
Path: TK2MSFTNGP01.phx.gbl!TK2MSFTNGP04.phx.gbl
Lines: 1
Xref: TK2MSFTNGP01.phx.gbl microsoft.public.sqlserver.programming:597705
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
news:ufkHThAZGHA.4836@.TK2MSFTNGP05.phx.gbl:

> Running unsafe code in the database is, well, unsafe. Your legacy C++
> code can crash SQL Server. So you should check with whoever owns the
> production SQL Server you will target and make sure they are OK with
> you running this code inside the database.
>
Well, to put things into perspective; running unsafe CLR code inside the
database is inherently safer than running extended procs. But yes, you are
right in that the code should be checked so it won't cause damage.
Niels
****************************************
**********
* Niels Berglund
* http://staff.develop.com/nielsb
* nielsb at develop dot com
* "A First Look at SQL Server 2005 for Developers"
* http://www.awprofessional.com/title/0321180593
****************************************
**********|||"Niels Berglund" <nielsb@.nospam.develop.com> wrote in message
news:Xns97AB12F509BB6nielsbdevelopcom@.20
7.46.248.16...
> "David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
> news:ufkHThAZGHA.4836@.TK2MSFTNGP05.phx.gbl:
>
> Well, to put things into perspective; running unsafe CLR code inside the
> database is inherently safer than running extended procs. But yes, you are
> right in that the code should be checked so it won't cause damage.
>
Unsafe managed code is one thing. P/Invoke to legacy C++ code is another.
David|||well, clr function call legacy dll has too much trouble,
- need to set security to let the user has permission
- or create public key and use it to sign the assembly
- parameter passing...
make the solution less favorite.
i will go to bcp to text file, processing and bulk insert back
"David Browne" wrote:

> "Niels Berglund" <nielsb@.nospam.develop.com> wrote in message
> news:Xns97AB12F509BB6nielsbdevelopcom@.20
7.46.248.16...
> Unsafe managed code is one thing. P/Invoke to legacy C++ code is another.
> David
>
>sql