creating extended stored procedure using vs.net 2005 for sql2005

Hi,

web searches give no end of how extended stored procedures can only be written in C++ ( or maybe vb also) .

And that extended stored procedures should be abandonded in favour of CLR framework procedures.

And how most articles explain how to convert ESPs to CLR procedures!!!!!

But I need to pass a non-discript block of binary data, extract pieces of data identified by its offset into the block, data type inferred by offset; into data to be written to the SQL database. These offsets are determinede by mapping (C UNION) to C typedef structures.

This cannot be done by managed code, therefore cannot be done by C++ CLR.

It is also ill suited for C# .

Sounds like a job for C++ extended stored procedure.

But how does one create and deploy an ESP with Visual Studio 2005? All wizards seem to insist on CLR.

Help!?

Boyd

This is an interesting problem, why are you sending in a binary block of data like that instead of disassembling it on the client side?

Thanks,

John

|||

Hi,

The binary block could have in the order of 50 different fields concatinated one byte after another. This binary structure maps to a "c typedef struct " definition.

Legacy systems take such a structure, do a "memcpy" out a communication port, a machine at the other end receives the bytes, assigns a pointer of the appropriate "typedef struct" and by inference knows exactly where the data as and the type ( integer, real, character array of fixed length, .... )

Disembling this stuff on the client side requires round-trips to the server for each and every field - very time consuming - especially when I could easily have 400 such blocks (8000 fields) per second from another machine on the network. Then write each field back if it changes (16000 transactions per second over the network). Might be a performance issue. Much more efficient to forward the block to the sql server once and let it do the ripping and shredding.

Creating Dynamic dimension Security with Custom Procedure

Hi All,

We are trying to implement dynamic dimension security using a Custom procedure written in C#.

If we create a single entry in the return set everything works fine (See Below)

Microsoft.AnalysisServices.AdomdServer.Expression Expression = new Microsoft.AnalysisServices.AdomdServer.Expression();

Expression.ExpressionText = "[Sub Branch Id].[All Branch].[4]";

Microsoft.AnalysisServices.AdomdServer.Member Member = Expression.CalculateMdxObject(null).ToMember();

TupleBuilder.Add(Member);

SetBuilder.Add(TupleBuilder.ToTuple());

However when we try and add a second item to the Tuple builder the object returns the error : The 'Sub Branch Id' Hierarchy appears more than once in the tuple.

Code snippet :

{

Microsoft.AnalysisServices.AdomdServer.Expression Expression = new Microsoft.AnalysisServices.AdomdServer.Expression();

Expression.ExpressionText = "[Sub Branch Id].[All Branch].[2]";

Microsoft.AnalysisServices.AdomdServer.Member Member = Expression.CalculateMdxObject(null).ToMember();

TupleBuilder.Add(Member);

Expression=null;

Member=null;

}

{

Microsoft.AnalysisServices.AdomdServer.Expression Expression = new Microsoft.AnalysisServices.AdomdServer.Expression();

Expression.ExpressionText = "[Sub Branch Id].[All Branch].[3]";

Microsoft.AnalysisServices.AdomdServer.Member Member = Expression.CalculateMdxObject(null).ToMember();

TupleBuilder.Add(Member);

Expression=null;

Member=null;

}

SetBuilder.Add(TupleBuilder.ToTuple());

Please can some one help resolve this issue as its becoming critical for the project that we are currently working on, otherwise we will have to revisit the security model.

TIA

Hello,

most likely you don't create a new tuple for each new member. Create a new instance of TupleBuilder to resolve it.

Radim

|||

Thanks for the Help Radmin, It was that.

The source example we're using was from Teo Lacevs book, applied MS Analysis Services 2005. On Teo's Forum we have found the correct syntax (though not in C#), and it does work.

This is the link to the solution, http://prologika.com/CS/forums/thread/1861.aspx

Creating Duplicate Table

Please Help me... How to create duplicate table dynamically(In Code). I have written query like this... select * into table2 from table1. But this creates only structure and data. I need to create along with constraints also(Primary key, default,index). Please help out me....

It's amazing what a little searching can find.

Check out this thread from just yesterday. It tells you all you need to know.

|||

I have created one, but not tested 100%.. try this code..

Code Snippet

Create Proc sp_copytable
(
@.TableName Nvarchar(2000),
@.TargetTableName Nvarchar(2000),
@.Copydata as bit =0
)
as
Set NOCOUNT ON;
Create Table #Text
(
Source NVarchar(4000)
)

Insert Into #Text(Source) Values ('Create Table ' + @.TargetTableName + '(');

Insert Into #Text(Source)
Select
'[' + C.Name + '] ' + case when iscomputed=0 then Ty.name + Case When C.xType in (175,239,231,167) Then
'(' + Cast((C.Length/case when left(Ty.name,1) ='N' Then 2 else 1 end) as Varchar) + ') '
When C.Type=108 Then '(' + Cast(C.prec as varchar) + ',' + Cast(C.scale as varchar) + ') '
Else '' End +
Case When C.IsNullable =1 And C.Colstat & 1 <> 1 Then ' NULL ' Else ' NOT NULL ' End
+ Case When C.Colstat & 1 = 1 Then ' Identity(' + Cast(ident_seed(T.name) as varchar) + ',' + Cast(ident_incr(T.name) as Varchar) + ') ' Else '' End
+ Isnull(' Constraint [' + ChkCon.Name + '_' + @.TargetTableName + '] Check ' + comments.Text ,'')
+ Isnull(' Default ' + defcomments.text ,'') + ',' else ' as ' + computed.text + ',' end
From
Sysobjects T
Join Syscolumns C on T.id = C.Id
Join systypes Ty On C.xtype = Ty.xType And Ty.Name <> 'SysName'
Left Outer Join sysobjects ChkCon On ChkCon.parent_obj = T.Id
And ChkCon.xtype= 'C' And ChkCon.Info = C.Colorder
Left Outer Join syscomments comments ON Comments.id = ChkCon.id And Comments.colid =1
Left Outer Join sysobjects def On def.parent_obj = T.Id
And def.xtype= 'D' And def.Info = C.Colorder
Left Outer Join syscomments defcomments ON defcomments.id = def.id
Left outer join syscomments computed ON computed.id = T.id and computed.number = c.colid

Where
T.Type='U'
And T.Name=@.TableName
Order By
T.Name,
C.Colorder

Insert Into #Text(Source)
Select
'Constraint [' + ind.name + '_' + @.TargetTableName + '] ' + case when xtype='PK' Then ' Primary Key ' Else ' Unique ' End + Case when ind.status & 16=16 Then ' clustered ' Else ' nonclustered' End + '(' + dbo.GetAllIndexedColumns(@.TableName, case when xtype = 'PK' Then 1 Else 2 end) + '),'
From
sysindexes ind Join sysobjects tbl On tbl.parent_obj = object_id(@.TableName)
and ind.name = object_name(tbl.id)
and xtype in ('PK', 'UQ')

Insert Into #Text(Source)
select
'Constraint [' + tbl.name + '_' + @.TargetTableName + '] FOREIGN KEY ([' + col_name(fk.fkeyid, fk.fkey) + ']) REFERENCES [' +
object_name(fk.rkeyid) + ']([' + col_name(fk.rkeyid, fk.rkey) + ']),'
from
sysforeignkeys fk Join sysobjects tbl On tbl.parent_obj = object_id(@.TableName)
and fk.constid = tbl.id
and xtype in ('F')

Declare @.Source as Nvarchar(4000);
Select @.Source = Source From #Text;
Update #Text
Set
Source = Substring(Source,1,Len(Source)-1)
Where
Source = @.Source;

Insert Into #Text(Source) values (')');
Declare @.SQL as varchar(8000);
Set @.SQL = ''
Select @.SQL = @.SQL + '
' + Source From #Text;

--Print @.SQL

Exec(@.SQL);
Print 'Table Structered Copied'

If @.Copydata = 1
Begin
Declare @.cols as varchar(8000);
Set @.cols = ''
select @.cols = @.cols + ',[' + name + ']' from syscolumns where id=object_id(@.TargetTableName) and iscomputed =0;
Set @.cols = substring(@.cols,2,8000)
If Exists (Select * from Syscolumns Where id=object_id(@.TargetTableName) and Colstat & 1 = 1)
Begin
Exec('Set IDENTITY_INSERT [' + @.TargetTableName + '] ON;Insert Into ' + @.TargetTableName + '(' + @.cols + ') Select ' + @.cols + ' from [' + @.TableName + '];Set IDENTITY_INSERT [' + @.TargetTableName + '] OFF;')
End
Else
Begin
Exec('Insert Into ' + @.TargetTableName + '(' + @.cols + ') Select ' + @.cols + ' from [' + @.TableName + '];')
End
Print 'Table Data Copied'
End

Go

Create Function dbo.GetAllIndexedColumns(@.ObjectName nvarchar(1000), @.indexid int)
Returns NVarchar(4000)
as
Begin
Declare @.IndexedCols as NVarchar(4000);
Declare @.I as Int;
Select @.I = 1;

Select @.IndexedCols = index_col(@.ObjectName, @.indexid, @.I) + ' ' + Case When indexkey_property(object_id(@.ObjectName),@.indexid, @.i, 'isdescending') = 1 Then ' Desc ' Else '' End
Select @.I = @.I +1;
While index_col(@.ObjectName, @.indexid, @.I) is not null
Begin
Select @.IndexedCols = @.IndexedCols + ',' + index_col(@.ObjectName, @.indexid, @.I) + ' ' + Case When indexkey_property(object_id(@.ObjectName),@.indexid, @.i, 'isdescending') = 1 Then ' Desc ' Else '' End
Select @.I = @.I +1;
End

return @.IndexedCols;
End

Go

exec sp_copytable 'Orders', 'Order1', 1 -- with data; for only structure use 0

|||

Hi Manivannan, Thanks for replying me... I execute Query(Stored Procedure, Function) what u have send to me.. It Successfully created. When i tried to create duplicate table it is not creating NULL values and error is showing for text datatype...(Cannot specify a column width on datatype text).. Please help out me Sir...

|||

I fixed the bugs.. check now

|||

Thanxs for immediate reply Manivannan.. Sir i am getting new error(String or binary data would be truncated) when i create duplicate table. For new table varchar size is taking 1.

|||

Hi Manivannan.Sorry for late .I tried to execute Query but i am getting new error(String or binary data would be truncated) when i create duplicate table. For new table varchar size is taking 1. Please Help me...

|||Which db you are using SQL Server 2000/2005?

|||Hi Manivannan..we are using Sql server 2000.|||

I fixed the bug again.. .

Can you check now?

|||

Hi Manivannan... Now i am able to Create. Thank u Very Much...

Creating Duplicate Table

Please Help me... How to create duplicate table dynamically(In Code). I have written query like this... select * into table2 from table1. But this creates only structure and data. I need to create along with constraints also(Primary key, default,index). Please help out me....

It's amazing what a little searching can find.

Check out this thread from just yesterday. It tells you all you need to know.

|||

I have created one, but not tested 100%.. try this code..

Code Snippet

Create Proc sp_copytable
(
@.TableName Nvarchar(2000),
@.TargetTableName Nvarchar(2000),
@.Copydata as bit =0
)
as
Set NOCOUNT ON;
Create Table #Text
(
Source NVarchar(4000)
)

Insert Into #Text(Source) Values ('Create Table ' + @.TargetTableName + '(');

Insert Into #Text(Source)
Select
'[' + C.Name + '] ' + case when iscomputed=0 then Ty.name + Case When C.xType in (175,239,231,167) Then
'(' + Cast((C.Length/case when left(Ty.name,1) ='N' Then 2 else 1 end) as Varchar) + ') '
When C.Type=108 Then '(' + Cast(C.prec as varchar) + ',' + Cast(C.scale as varchar) + ') '
Else '' End +
Case When C.IsNullable =1 And C.Colstat & 1 <> 1 Then ' NULL ' Else ' NOT NULL ' End
+ Case When C.Colstat & 1 = 1 Then ' Identity(' + Cast(ident_seed(T.name) as varchar) + ',' + Cast(ident_incr(T.name) as Varchar) + ') ' Else '' End
+ Isnull(' Constraint [' + ChkCon.Name + '_' + @.TargetTableName + '] Check ' + comments.Text ,'')
+ Isnull(' Default ' + defcomments.text ,'') + ',' else ' as ' + computed.text + ',' end
From
Sysobjects T
Join Syscolumns C on T.id = C.Id
Join systypes Ty On C.xtype = Ty.xType And Ty.Name <> 'SysName'
Left Outer Join sysobjects ChkCon On ChkCon.parent_obj = T.Id
And ChkCon.xtype= 'C' And ChkCon.Info = C.Colorder
Left Outer Join syscomments comments ON Comments.id = ChkCon.id And Comments.colid =1
Left Outer Join sysobjects def On def.parent_obj = T.Id
And def.xtype= 'D' And def.Info = C.Colorder
Left Outer Join syscomments defcomments ON defcomments.id = def.id
Left outer join syscomments computed ON computed.id = T.id and computed.number = c.colid

Where
T.Type='U'
And T.Name=@.TableName
Order By
T.Name,
C.Colorder

Insert Into #Text(Source)
Select
'Constraint [' + ind.name + '_' + @.TargetTableName + '] ' + case when xtype='PK' Then ' Primary Key ' Else ' Unique ' End + Case when ind.status & 16=16 Then ' clustered ' Else ' nonclustered' End + '(' + dbo.GetAllIndexedColumns(@.TableName, case when xtype = 'PK' Then 1 Else 2 end) + '),'
From
sysindexes ind Join sysobjects tbl On tbl.parent_obj = object_id(@.TableName)
and ind.name = object_name(tbl.id)
and xtype in ('PK', 'UQ')

Insert Into #Text(Source)
select
'Constraint [' + tbl.name + '_' + @.TargetTableName + '] FOREIGN KEY ([' + col_name(fk.fkeyid, fk.fkey) + ']) REFERENCES [' +
object_name(fk.rkeyid) + ']([' + col_name(fk.rkeyid, fk.rkey) + ']),'
from
sysforeignkeys fk Join sysobjects tbl On tbl.parent_obj = object_id(@.TableName)
and fk.constid = tbl.id
and xtype in ('F')

Declare @.Source as Nvarchar(4000);
Select @.Source = Source From #Text;
Update #Text
Set
Source = Substring(Source,1,Len(Source)-1)
Where
Source = @.Source;

Insert Into #Text(Source) values (')');
Declare @.SQL as varchar(8000);
Set @.SQL = ''
Select @.SQL = @.SQL + '
' + Source From #Text;

--Print @.SQL

Exec(@.SQL);
Print 'Table Structered Copied'

If @.Copydata = 1
Begin
Declare @.cols as varchar(8000);
Set @.cols = ''
select @.cols = @.cols + ',[' + name + ']' from syscolumns where id=object_id(@.TargetTableName) and iscomputed =0;
Set @.cols = substring(@.cols,2,8000)
If Exists (Select * from Syscolumns Where id=object_id(@.TargetTableName) and Colstat & 1 = 1)
Begin
Exec('Set IDENTITY_INSERT [' + @.TargetTableName + '] ON;Insert Into ' + @.TargetTableName + '(' + @.cols + ') Select ' + @.cols + ' from [' + @.TableName + '];Set IDENTITY_INSERT [' + @.TargetTableName + '] OFF;')
End
Else
Begin
Exec('Insert Into ' + @.TargetTableName + '(' + @.cols + ') Select ' + @.cols + ' from [' + @.TableName + '];')
End
Print 'Table Data Copied'
End

Go

Create Function dbo.GetAllIndexedColumns(@.ObjectName nvarchar(1000), @.indexid int)
Returns NVarchar(4000)
as
Begin
Declare @.IndexedCols as NVarchar(4000);
Declare @.I as Int;
Select @.I = 1;

Select @.IndexedCols = index_col(@.ObjectName, @.indexid, @.I) + ' ' + Case When indexkey_property(object_id(@.ObjectName),@.indexid, @.i, 'isdescending') = 1 Then ' Desc ' Else '' End
Select @.I = @.I +1;
While index_col(@.ObjectName, @.indexid, @.I) is not null
Begin
Select @.IndexedCols = @.IndexedCols + ',' + index_col(@.ObjectName, @.indexid, @.I) + ' ' + Case When indexkey_property(object_id(@.ObjectName),@.indexid, @.i, 'isdescending') = 1 Then ' Desc ' Else '' End
Select @.I = @.I +1;
End

return @.IndexedCols;
End

Go

exec sp_copytable 'Orders', 'Order1', 1 -- with data; for only structure use 0

|||

Hi Manivannan, Thanks for replying me... I execute Query(Stored Procedure, Function) what u have send to me.. It Successfully created. When i tried to create duplicate table it is not creating NULL values and error is showing for text datatype...(Cannot specify a column width on datatype text).. Please help out me Sir...

|||

I fixed the bugs.. check now

|||

Thanxs for immediate reply Manivannan.. Sir i am getting new error(String or binary data would be truncated) when i create duplicate table. For new table varchar size is taking 1.

|||

Hi Manivannan.Sorry for late .I tried to execute Query but i am getting new error(String or binary data would be truncated) when i create duplicate table. For new table varchar size is taking 1. Please Help me...

|||Which db you are using SQL Server 2000/2005?

|||Hi Manivannan..we are using Sql server 2000.|||

I fixed the bug again.. .

Can you check now?

|||

Hi Manivannan... Now i am able to Create. Thank u Very Much...