Get Analysis Services Version string in c#.net 2.0

Hi

I want to get the version string for AS 2000 and 2005 in c#. Can I use the same object model to connect to both or do I need one for each. I saw a reference to DSO 8.5 which implied you could.

Either way could you let me know what DDL I should reference in Visual Studio.

Thanks

Steve

If you are using Microsoft.AnalysisServices.AdomdClient then you can use AdomdConnection.ServerVersion property to obtain textual representation of the version. If you use System.Runtime.InteropServices.Version object then you can construct one to parse such things like major and minor numbers.

It works for AS2005 and AS2000.|||Works a treat Andrew thanks

Get 10 records fro each group

Hi
I have a table that has fromNode, toNode and idGroup columns
I want to return the TOP 3 toNodes for each IDGroup that the FromNode =
1000
E.G. The query would return
fromNode, toNode, idGroup
1000,2001,1
1000,2002,1
1000,2003,1
1000,2004,2
1000,2005,2
1000,2006,2
1000,2013,3
1000,2014,3
1000,2016,4
But I am having trouble comming up with such a query. Can any one help.
SQL code and inserts below.
CREATE TABLE [dbo].[memberPathsGroups](
[fromNode] [int] NOT NULL,
[toNode] [int] NOT NULL,
[idGroup] [int] NOT NULL,
CONSTRAINT [PK_memberPathsGroups] PRIMARY KEY CLUSTERED
(
[fromNode] ASC,
[toNode] ASC,
[idGroup] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
insert into memberPathsGroups values (1000,2001,1)
insert into memberPathsGroups values (1000,2002,1)
insert into memberPathsGroups values (1000,2003,1)
insert into memberPathsGroups values (1000,2004,2)
insert into memberPathsGroups values (1000,2005,2)
insert into memberPathsGroups values (1000,2006,2)
insert into memberPathsGroups values (1000,2007,2)
insert into memberPathsGroups values (1000,2008,2)
insert into memberPathsGroups values (1000,2009,2)
insert into memberPathsGroups values (1000,2010,2)
insert into memberPathsGroups values (1000,2012,2)
insert into memberPathsGroups values (1000,2013,3)
insert into memberPathsGroups values (1000,2014,3)
insert into memberPathsGroups values (1000,2015,2)
insert into memberPathsGroups values (1000,2016,4)
insert into memberPathsGroups values (1001,2001,1)
insert into memberPathsGroups values (1001,2010,1)
insert into memberPathsGroups values (1001,2012,1)
insert into memberPathsGroups values (1001,2016,2)Here's one way:
SELECT
fromNode,
toNode,
idGroup
FROM dbo.memberPathsGroups mpg
WHERE
mpg.toNode IN
(
SELECT TOP 3
mpg1.toNode
FROM dbo.memberPathsGroups mpg1
WHERE
mpg1.fromNode = mpg.fromNode
AND mpg1.idGroup = mpg.idGroup
ORDER BY mpg1.toNode
)
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Rippo" <info@.rippo.co.uk> wrote in message
news:1131373532.924245.290810@.o13g2000cwo.googlegroups.com...
> Hi
> I have a table that has fromNode, toNode and idGroup columns
> I want to return the TOP 3 toNodes for each IDGroup that the FromNode =
> 1000
> E.G. The query would return
> fromNode, toNode, idGroup
> 1000,2001,1
> 1000,2002,1
> 1000,2003,1
> 1000,2004,2
> 1000,2005,2
> 1000,2006,2
> 1000,2013,3
> 1000,2014,3
> 1000,2016,4
> But I am having trouble comming up with such a query. Can any one help.
> SQL code and inserts below.
>
> CREATE TABLE [dbo].[memberPathsGroups](
> [fromNode] [int] NOT NULL,
> [toNode] [int] NOT NULL,
> [idGroup] [int] NOT NULL,
> CONSTRAINT [PK_memberPathsGroups] PRIMARY KEY CLUSTERED
> (
> [fromNode] ASC,
> [toNode] ASC,
> [idGroup] ASC
> )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
> ) ON [PRIMARY]
> GO
> insert into memberPathsGroups values (1000,2001,1)
> insert into memberPathsGroups values (1000,2002,1)
> insert into memberPathsGroups values (1000,2003,1)
> insert into memberPathsGroups values (1000,2004,2)
> insert into memberPathsGroups values (1000,2005,2)
> insert into memberPathsGroups values (1000,2006,2)
> insert into memberPathsGroups values (1000,2007,2)
> insert into memberPathsGroups values (1000,2008,2)
> insert into memberPathsGroups values (1000,2009,2)
> insert into memberPathsGroups values (1000,2010,2)
> insert into memberPathsGroups values (1000,2012,2)
> insert into memberPathsGroups values (1000,2013,3)
> insert into memberPathsGroups values (1000,2014,3)
> insert into memberPathsGroups values (1000,2015,2)
> insert into memberPathsGroups values (1000,2016,4)
> insert into memberPathsGroups values (1001,2001,1)
> insert into memberPathsGroups values (1001,2010,1)
> insert into memberPathsGroups values (1001,2012,1)
> insert into memberPathsGroups values (1001,2016,2)
>

Get @@rowcount data from MSSQL using SqlDataSource with delete command

Hi

I'm using a simple SqlDataSource to connect to a stored proc to delete a number of rows from different tables.

In my SQL im using:

DECLARE @.table1CountintDELETE FROM Table1WHERE id = @.new_idSET @.table1Count=@.@.rowcountSELECT @.table1Count

I'm then using an input box and linking it to the delete control parameter. Then on a button click event i'm running SqlDataSource1.Delete() which all works fine. But how do i get the @.table1Count back into my aspx page?

Thanks

use OUT parameter

CREATE PROC dbo.SP_DeleteID(@.new_id int, @.effRowCnt intOUT)

As

begin

DELETE FROM Table1WHERE id = @.new_id

SET @.effRowCnt=@.@.rowcount

end

|||

ok, but how do i reference the @.effRowCnt in the code behind page?

thanks

|||

If you are just trying to get a count of the number of rows deleted, an easier way to do it is to use the Deleted methodof the DataSource, something like:

ProtectedSub SQLDataSource1_Deleted(ByVal senderAsObject,ByVal eAs System.Web.UI.WebControls.SqlDataSourceStatusEventArgs)Handles SQLDataSource1.Deleted

NumberofRowsDeleted = e.AffectedRows

EndSub

HTH

Generating unique id strings

Hi
I want to generate a unique record number in the following format
Company initials/year/Counter/RecordType (e.g. SDS/04/00123/WB)

Could someone recommend how I go about generating this number?

Should I create a separate table with columns for each section of the number and concantenate the columns in ID field of the actual table?

... or should I just create a stored procedure to generate the number each time?

... or should I do something completely different?

Regards
John :confused:First, I'd recommend against doing this unless this code is required by the business process, i.e. recognized by the system users. Such codes are of little or no value to the application.

If all four of these values already exist in the table, then you could add a calculated column to your table that concatenates them to form the ID. These types of keys are often called "Superkeys", but with the availability of composite keys they are of little use these days and are often difficult to maintain.|||Thanks blindman, I'll take your advise and k.i.s.|||you could create a formula to concatenate the other fields and use it as the default value of a new field in the same row

I don't think I explained this very well

er

read about using formulas as default values in BOL

GW|||As a default value it would not update automatically if any of it's components change. That's the advantage of a calculated field.|||Apologies

U right of course blindman

I was'nt thinking

GW

generating crystal report using VB

hi
i am doing a project i VB and SQL Server.....
i want to display a report made in Crystal report when i click the print button of my application......
please send me an example code which i can use to do it...
reply soon
shantanuTake a look at this:

http://support.businessobjects.com/communityCS/TechnicalPapers/scr8_ttxado.pdf.asp

Generate SQL for a table

Hi

I have two questions

i) is there any SP which will generates SQL Script for a table including all constarint(PK,FK), index etc. and the table data in the format of "INSERT INTO" . if NO, how can i create to do so, or any other SP which will help me( may be third party one)

ii)Where had "Scptxfr.exe" gone in SQL Server 2005 ?

Regards,

Thanks.

Gurpreet S. Gill

There is no SP. You will have to create one yourself or use SMO for example. I don't know the answer to the 2nd one. I am moving the thread to Tools forum to see if someone there might know.|||

Gurpreet Singh Gill wrote:

Hi

I have two questions

i) is there any SP which will generates SQL Script for a table including all constarint(PK,FK), index etc. and the table data in the format of "INSERT INTO" . if NO, how can i create to do so, or any other SP which will help me( may be third party one)

ii)Where had "Scptxfr.exe" gone in SQL Server 2005 ?

Regards,

Thanks.

Gurpreet S. Gill

Hi,

if you are a .Net developer the insert scripts are already genrated for you by the wizard if you have pk in every tables. You might be interested in using it. even if you are not a VS developer

the answer to the second question is just a click away. here. check this out

look for this topic Where to Find SCPTXFR?

http://www.sqlservercentral.com/columnists/jreade/howtoscheduleasqlserverdatabasecreationscript.asp

thanks,

joey

|||

joey

I dont want to create it by .NET code infact only by using the some SP, or inside SSMS only.

I read the artical but i dont find any thing about where is the SCPTXFR.EXE had gone, only one line say, "this is missing", but they are refering the Beta version of SQL Server.

Regards,

Thanks.

Gurpreet S. Gill

|||

the title is misleading. sorry

anyway you can still use the v2000 version of scptxfr.exe

with 2005

|||

I know about scptxfr.exe, I want only one table Script(not all), with Data too.

|||

hi,

got this one from another thread

you can use the sql server wizard to generate scripts insert, update, delete query

Run sql server enterprise manager > tools > wizards > database > create stored procedures wizards.

choose the table and click on insert.

on the completing tab click on edit then click edit Sql

regards,

joey

|||

Sorry to say man, i want to create the SP which will Generate the SQL Script(including Constaints, indexes, checks..etc) for the table as well as the data of that table. The aim is to create the .sql file, which can be used for migration to other DB with the table(create script) with data.

|||I was struggling with the same case a few months back in SQL Server 2005 as the wizard does not include drop statements before creating the object. Unlike SQL Server 2000, the 2005 version uses the IF NOT EXISTS logic instead of the IF EXISTS logic. This is where chanced upon Scriptio, a tool created by SQLTeam.com. From the looks of your requirement, you definitely have to write a script (or an application) to generate an INSERT Statement if you want to migrate the data as well. Why don't you just generate a script to build the database and use SSIS to migrate the data afterwards?|||

Basically, the things are bit different for me, we had a setup at client end, which uses the SQL Server 2005 as a backend(before that the application was using the SQL Base by Centura/Gurpta Technologies).Client dont have SSMS(only Database Services), infact they are using the SQL Talk(again the tool from the same). they want the same funcrionalties as provided by SQL Base, this procedure, which i need is one of them

Could any body help me.

Regards,

Thanks.

Gurpreet S. Gill

Generate Script

Hi
I am looking for a script that will generate all the foreign keys, defaults
and check constraints only from all the tables in my database.
Can anyone point me in the right direction?Right click on the tables in EM, All Tasks--> Generate Script
Go to the options tab and select what you want
http://sqlservercode.blogspot.com/
"Jaco" wrote:

> Hi
> I am looking for a script that will generate all the foreign keys, default
s
> and check constraints only from all the tables in my database.
> Can anyone point me in the right direction?|||sorry, should have said I want to do this without EM
"SQL" wrote:
> Right click on the tables in EM, All Tasks--> Generate Script
> Go to the options tab and select what you want
> http://sqlservercode.blogspot.com/
> "Jaco" wrote:
>|||Jaco,
SQL-DMO? Check out the Script method in the SQL BOL.
HTH
Jerry
"Jaco" <Jaco@.discussions.microsoft.com> wrote in message
news:3E4BAF12-3CFC-49E3-95ED-43B2E193FDDF@.microsoft.com...
> sorry, should have said I want to do this without EM
> "SQL" wrote:
>

generate a text file from SQL Server

Hi

I need to generate a text file from SQL Server. The task automatically runs daily.

The format of the text file use "/" to separate the field and the row. At the end of the file, it adds number of char and uses "\". The number of char includes the data parts and excludes a few word of "the number of char".

For example,
The underline fonts mean the first row record. The Bold fonts mean the second row record. The Italic fonts mean the third row record. The red fonts mean the number of char and the end of file.

1/060222/008888/234/1/7441/2/BB/10000//////290025/////1/060222/008888/234/1/7441/3/XX/100-//////290025/////1/060222/008881/234/1/7442/2/BB/10000//////290025/////161\

I am no idea about this problem. Please give me some suggestions. Thanks a lot

regards
Alex

For your information

I use SQL Server 2000 in Windows 2000 Server environments

Hi Alex,

You need to generate a Text File from SQL Server with field delimeter as "/" and the line delimeter as "/////"?

Also please let us know that whether you want to generate a Text file with static filename or it may vary?

Anyway I will also try to generate the file based on my understandings from your post.

Regards,

Prakash Srinivasan

|||

Hi Prakash Srinivasan

The field delimeter is "/" and there is no line delimeter.

The task generates many files and place at different places where are the fixed folders. The filenames are static and need to override the old one.

Thank you for your help.

Alex

|||

Hi Alex,

If you don't have any line delimeter (Row delimeter), then how would you differentiate the records. So there should be a row delimeter in the file to separate the records.

I assume there is and proceed further.

Regards,

Prakash Srinivasan

|||

Hi Alex,

First create a Package and put a Data Flow. In the Data Flow, put one OleDb Source and Flat File Destination. Also create Connection for both OleDb Source and Flat File Destination.

And in the Flat File Connection Manager, specify the filename in which you want to dump the data, and leave the rest of the setting in General tab as it is.

In Advanced tab, create columns based on your requirement (Suppose if you need only 3 columns, create 3 columns and set the datatype based on the datatype specified in your SQL Server database).

In Columns tab, give the Row Delimeter as "////" and Column delimeter as "/".

Now connect your OleDb Source to the OleDb Connection manager and mention the tables which you want to access.

Now map the columns appropriately in the Flat File Destination and execute the package.

The results are coming exactly in my machine. And for writing the number of characters at the end of the file, put one Script Task and write a code to open the text file and read till the end of the file and write at the end of the file and add "\".

I hope there is a way to get this done in SSIS. If I get that, will let you know.

Regards,

Prakash Srinivasan.

|||

One record followed by one record. The number of field are fixed in each row, so there are not the row delimeter.

for example,
record1field1/record1field2/record2field1/record2field2/55\

--Alex

|||Being new to this, how would you do it with a dynamically generated name? I need to generate a text file with the date as the filename and a key field from from a table daily. Thanks in advance!|||

Miles Calunod wrote:

Being new to this, how would you do it with a dynamically generated name? I need to generate a text file with the date as the filename and a key field from from a table daily. Thanks in advance!

Miles,

Put an expression on the connection string property of the connection manager that you are using to point at the destination file. This expression will get evaluated at runtime and therefore can set the connection string (i.e. the file location) to be whatever you like.

-Jamie