Need Experts on Vb.net CLR intergration problem

Hi all I have the following CLR stored procedure :

Partial Public Class StoredProcedures

<Microsoft.SqlServer.Server.SqlProcedure()> _

Public Shared Sub sssGetActiveRepositoryByTitle( _

ByVal title As String)

' Add your code here

Using conn As New SqlConnection("context connection=true")

Dim objCommand As New SqlCommand()

Dim TitleParam As New SqlParameter("@.Title", SqlDbType.VarChar, 100)

TitleParam.Value = title

objCommand.Connection = conn

conn.Open()

'build the delete command

objCommand.CommandText = _

"select * from sstRepository where IsActive = 1 and Title =" & TitleParam.Value.ToString

SqlContext.Pipe.ExecuteAndSend(objCommand)

conn.Close()

End Using

End Sub

Now I have a windows service in my data layer that needs to access this stored procedure and convert it into a dataaset to pass to the client application :

Imports System.Data.SqlClient

Imports NBS.SURVEYSDATABASEservice.DBMS

Public Class clsClient

' it inherits the stored procedures from the DBMS class which is the name

'of the CLR dll

Inherits StoredProcedures

Public Function GetClientByVirtualPath(ByVal pstrVirtualPath As String) As DataSet

Try

Dim i As SqlDataReader

'parameters are stored in an array (zero based) for use in the base class

Dim parmArrSqlParms(0) As SqlClient.SqlParameter

' Dim fff As Int32

i = sdsGetClientByVirtualPath(pstrVirtualPath)

' Return MyBase.RunProcedure("dbo.sdsGetClientByVirtualPath", parmArrSqlParms)

Catch ex As Exception

'log the error

'cLogger.LogMessage("ACME", "SampleApplication", Logger.EntryTypes.RunError, System.Environment.MachineName, "clsDemoClass.SelectAllCompanies", ex.Message)

'raise the error to the caller for handling

Throw ex

End Try

End Function

I've tried a bunch of different things to no avail the error I keep getting trying to access the sqlpipe resulsts is " this expressions does not return any values"

any ideas ? I am basically converting around TSQL 50 stored procs into managed CLR code and the CLR funtions are created but I am really having problems accessing the resuluts on the client end .

Help please !

Before moving 50 TSQL stored proc into managed code, have you considered our TSQL vs CLR guidelines located here?

Need Experts on Vb.net CLR intergration problem

Hi all I have the following CLR stored procedure :

Partial Public Class StoredProcedures

<Microsoft.SqlServer.Server.SqlProcedure()> _

Public Shared Sub sssGetActiveRepositoryByTitle( _

ByVal title As String)

' Add your code here

Using conn As New SqlConnection("context connection=true")

Dim objCommand As New SqlCommand()

Dim TitleParam As New SqlParameter("@.Title", SqlDbType.VarChar, 100)

TitleParam.Value = title

objCommand.Connection = conn

conn.Open()

'build the delete command

objCommand.CommandText = _

"select * from sstRepository where IsActive = 1 and Title =" & TitleParam.Value.ToString

SqlContext.Pipe.ExecuteAndSend(objCommand)

conn.Close()

End Using

End Sub

Now I have a windows service in my data layer that needs to access this stored procedure and convert it into a dataaset to pass to the client application :

Imports System.Data.SqlClient

Imports NBS.SURVEYSDATABASEservice.DBMS

Public Class clsClient

' it inherits the stored procedures from the DBMS class which is the name

'of the CLR dll

Inherits StoredProcedures

Public Function GetClientByVirtualPath(ByVal pstrVirtualPath As String) As DataSet

Try

Dim i As SqlDataReader

'parameters are stored in an array (zero based) for use in the base class

Dim parmArrSqlParms(0) As SqlClient.SqlParameter

' Dim fff As Int32

i = sdsGetClientByVirtualPath(pstrVirtualPath)

' Return MyBase.RunProcedure("dbo.sdsGetClientByVirtualPath", parmArrSqlParms)

Catch ex As Exception

'log the error

'cLogger.LogMessage("ACME", "SampleApplication", Logger.EntryTypes.RunError, System.Environment.MachineName, "clsDemoClass.SelectAllCompanies", ex.Message)

'raise the error to the caller for handling

Throw ex

End Try

End Function

I've tried a bunch of different things to no avail the error I keep getting trying to access the sqlpipe resulsts is " this expressions does not return any values"

any ideas ? I am basically converting around TSQL 50 stored procs into managed CLR code and the CLR funtions are created but I am really having problems accessing the resuluts on the client end .

Help please !

Before moving 50 TSQL stored proc into managed code, have you considered our TSQL vs CLR guidelines located here?

Need advice on massive database

Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.clustering:14782
Hi,
We're in the process of architecting a very large SQL2K database. We
believe this database will grow 2TB per month. 99% of the inserts will be
done via bulk inserts. Approximately 3-4 per day an application that we
wrote will query and pull approximately 6 million records at a time.
* Will this work with SQL Enterprise or should I use DataCenter?
* Will this operate on Win2k Enterprise or should I use Datacenter?
* What I/O recommendations do you suggest (RAID or fiberchanner / RAID
array, SAN, or NetSAN, memory on controller, etc) to keep up with a bulk
insert of approximately 2,000 records per second?
* Will failover clustering work with a database this size (stupid
question but I have to ask)?
Thanks!
Jack
Comments Inline
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Jack Victor" <Nomail@.JustPostReply.tv> wrote in message
news:pan.2004.05.21.14.06.17.720000@.JustPostReply. tv...
> Hi,
> We're in the process of architecting a very large SQL2K database. We
> believe this database will grow 2TB per month. 99% of the inserts will be
> done via bulk inserts. Approximately 3-4 per day an application that we
> wrote will query and pull approximately 6 million records at a time.
> * Will this work with SQL Enterprise or should I use DataCenter?
DataCenter is an OS-level difference. SQL stops at Enterprise Edition, but
it will run on DataCenter OS. You may need DataCenter, but only for the #of
CPUs it will support.
> * Will this operate on Win2k Enterprise or should I use Datacenter?
>
Again, you may need the memory and/or CPU scalability of Datacenter. A lot
depends on the number of concurrent users and the total load.
> * What I/O recommendations do you suggest (RAID or fiberchanner / RAID
> array, SAN, or NetSAN, memory on controller, etc) to keep up with a bulk
> insert of approximately 2,000 records per second?
SAN. Big SAN with multiple HBAs and paths.
> * Will failover clustering work with a database this size (stupid
> question but I have to ask)?
Sure. It may take some time to go through the recovery process during a
failover, but clustering does not affect scalability.
> Thanks!
> Jack
>

Need a Sql Scriptor

I need to generate the Sql Scripts for a particular Database in a Sql Server 7.0.

Also i need help in the Data Migration of Foxpro2.6a to SqlServer.

If there are any good tools for such conversion,I am very much thankful to all who comes with a good reply.

Thanks & Regds,
B.SethuJust right click any of your databases and choose "all tasks" then "generate sql scripts". If you have an odbc driver for the foxpro database then you can use the dts import wizard. If not, just export the foxpro database to a delimited file and use the wizard.|||To migrate your data look into Distributed Transaction Coordinator (DTS),which comes with SQL Server. Also use Google (http://www.google.com) to research on the migration.|||Thanks for the reply.I need to create Insert Statements for all my Master Tables(say 1000).I need a tool which gives me the insert statements,either if i give the Database or Tables of the Database.

Also for foxpro we don't have any ODBC driver,moreover my all foxpro tables have memo fields,which i can't transform via DTS.

Thanks & Regds,
B.Sethu|||To generate the scripts for all of your databases you can use the sql-dmo script method - look at your books online.

What happens when you try to transform the foxpro table with a memo field ?|||I've created a stored procedure that will generate the INSERT statements

Example in Pubs database
exec usp_CreateInsert authors

INSERT authors ( au_id, au_lname, au_fname, phone, address, city, state, zip, contract)
VALUES ('409-56-7008','Bennet','Abraham','415 658-9932','6223 Bateman St.','Berkeley','CA','94705',1)
INSERT authors ( au_id, au_lname, au_fname, phone, address, city, state, zip, contract)
VALUES ('427-17-2319','Dull','Ann','415 836-7128','3410 Blonde St.','Palo Alto','CA','94301',1)

I've attached the script|||For the foxpro conversion - have you tried converting to access first then to sql server ?

Need a Solution

Hi,

How can i define in SqlServer an way to make my identity's columns folow somekind of rule.

I need to define it in sqlserver not in my application code.
So basically what i need is to maintain the inserts i have , assuming that ids are automatic, but in the database being able to modyfing them.
Using trigers or other way, please help me... :)

thanks-.-I assume you don't want to use Sql Server's Identity="Yes" for an int field.

The alternative would be to use a Trigger that fires on Insert that will populate your identity column. The code inside the trigger can follow whatever rule you can code in Transact-Sql.

At least the last time I used Oracle you had to do it that way anyway.

CREATE TRIGGER [MyIdentifyTrigger] ON dbo.YourTable
FOR INSERT
AS
-- Figure out identify value and set column in "inserted" record to that value.

|||Hi,

No, my problem is that i have the Identity set to "Yes" in the fields. I've already seen that with SET IDENTITY_INSERT to ON i can explicit the value to the field with identity.

No, i just need to know the syntax of the trigger to do all of this, when i insert.

I need this because all my inserts in the application assumes auto ids, and now i need to control the value inserted in those field, but i cant go to the code, so must do this in database level.

thanks|||You should be able to accomplish this with an INSTEAD OF trigger. Something like this:

CREATE TRIGGER Insert_Data ON Test
INSTEAD OF INSERT
AS
DECLARE @.ID int, @.Col_SomeText varchar(50)
SELECT @.ID=ID, @.Col_SomeText = SomeText FROM inserted
IF @.ID IS NULL
 BEGIN
 BEGIN TRANSACTION
 SELECT @.ID = IDNumber FROM IDNumberTable HOLDLOCK
 UPDATE IDNumberTable SET IDNumber = IDNumber + 1
 COMMIT
 END
INSERT INTO Test VALUES(@.ID, @.Col_SomeText)

Terri

Need %complete stat from stored procedure to vb

Is there a way to call a SQLServer stored procedure from within VB6
and have the % completion stat be returned to the calling program as
it is updated? Our clients hate seeing static screens and while this
procedure is processing I want to be able to display it's progress.
Thanks!Not from a single statement, there's not any way to get back the completion
status from a query.
Some operations, which are predictable, like BACKUP, has an option to get ST
ATS back, but not for
general DML statements. If your procedure is executing *several* statements,
you can use RAISERROR
using the NO_WAIT option to make SQL server flush the output buffer for each
RAISERROR statement.
For a single query, fake it... :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Mark V" <mgvincent@.excite.com> wrote in message
news:96887154.0411170859.77244be0@.posting.google.com...
> Is there a way to call a SQLServer stored procedure from within VB6
> and have the % completion stat be returned to the calling program as
> it is updated? Our clients hate seeing static screens and while this
> procedure is processing I want to be able to display it's progress.
> Thanks!

Need %complete stat from stored procedure to vb

Is there a way to call a SQLServer stored procedure from within VB6
and have the % completion stat be returned to the calling program as
it is updated? Our clients hate seeing static screens and while this
procedure is processing I want to be able to display it's progress.
Thanks!Not from a single statement, there's not any way to get back the completion status from a query.
Some operations, which are predictable, like BACKUP, has an option to get STATS back, but not for
general DML statements. If your procedure is executing *several* statements, you can use RAISERROR
using the NO_WAIT option to make SQL server flush the output buffer for each RAISERROR statement.
For a single query, fake it... :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Mark V" <mgvincent@.excite.com> wrote in message
news:96887154.0411170859.77244be0@.posting.google.com...
> Is there a way to call a SQLServer stored procedure from within VB6
> and have the % completion stat be returned to the calling program as
> it is updated? Our clients hate seeing static screens and while this
> procedure is processing I want to be able to display it's progress.
> Thanks!

Need %complete stat from stored procedure to vb

Is there a way to call a SQLServer stored procedure from within VB6
and have the % completion stat be returned to the calling program as
it is updated? Our clients hate seeing static screens and while this
procedure is processing I want to be able to display it's progress.
Thanks!
Not from a single statement, there's not any way to get back the completion status from a query.
Some operations, which are predictable, like BACKUP, has an option to get STATS back, but not for
general DML statements. If your procedure is executing *several* statements, you can use RAISERROR
using the NO_WAIT option to make SQL server flush the output buffer for each RAISERROR statement.
For a single query, fake it... :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Mark V" <mgvincent@.excite.com> wrote in message
news:96887154.0411170859.77244be0@.posting.google.c om...
> Is there a way to call a SQLServer stored procedure from within VB6
> and have the % completion stat be returned to the calling program as
> it is updated? Our clients hate seeing static screens and while this
> procedure is processing I want to be able to display it's progress.
> Thanks!

Necessary Help-->Database Mirroring

Hi Everyone,
I want to start database mirroring in SQL server 2005 -Microsoft SQL
server management studio Express-,I use windows XP P2.
I try to use database mirroring but it doesn't work.What should I
do?Which Operating System, and SQL server should I use?
Thanks,
Nassa
data base mirroring is not supported on SQL Server Express.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Nassa" <nassim.czdashti@.gmail.com> wrote in message
news:1169268992.469697.24710@.s34g2000cwa.googlegro ups.com...
> Hi Everyone,
> I want to start database mirroring in SQL server 2005 -Microsoft SQL
> server management studio Express-,I use windows XP P2.
> I try to use database mirroring but it doesn't work.What should I
> do?Which Operating System, and SQL server should I use?
> Thanks,
> Nassa
>
|||Hilary Cotter
thanks,
I use SQL server 2005-Standard.
Nassa
Hilary Cotter wrote:[vbcol=seagreen]
> data base mirroring is not supported on SQL Server Express.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Nassa" <nassim.czdashti@.gmail.com> wrote in message
> news:1169268992.469697.24710@.s34g2000cwa.googlegro ups.com...
|||Hilary Cotter
thanks,
I use SQL server 2005-Standard.
Nassa
Hilary Cotter wrote:[vbcol=seagreen]
> data base mirroring is not supported on SQL Server Express.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Nassa" <nassim.czdashti@.gmail.com> wrote in message
> news:1169268992.469697.24710@.s34g2000cwa.googlegro ups.com...
|||Hilary Cotter
thanks,
I use SQL server 2005-Standard.
Nassa
Hilary Cotter wrote:[vbcol=seagreen]
> data base mirroring is not supported on SQL Server Express.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Nassa" <nassim.czdashti@.gmail.com> wrote in message
> news:1169268992.469697.24710@.s34g2000cwa.googlegro ups.com...
|||Ok can you try this then:
--On your principal
CREATE ENDPOINT [Mirroring]
AS TCP (LISTENER_PORT = 5022)
FOR DATABASE_MIRRORING (ROLE = PARTNER);
--on your mirror
CREATE ENDPOINT [Mirroring]
AS TCP (LISTENER_PORT = 5022)
FOR DATABASE_MIRRORING (ROLE = PARTNER, ENCRYPTION);
--on your witness
CREATE ENDPOINT [Mirroring]
AS TCP (LISTENER_PORT = 5022)
FOR DATABASE_MIRRORING (ROLE = WITNESS);
--Start endpoints
ALTER ENDPOINT [Mirroring] STATE = STARTED;
--now backup your database on the principal copy it to your mirror and
restore it there with norecovery
--Connect to your mirror
-- Specify the partner from the mirror server - note this can be an ip
address rather than the fqdn
ALTER DATABASE [AdventureWorks] SET PARTNER
='TCP://Mirror.corp.mycompany.com:5022';
--Connect to your principle
-- Specify the witness from the principal server
ALTER DATABASE [AdventureWorks] SET WITNESS =
'TCP://Wittness.corp.mycompany.com:5022';
That's it
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Nassa" <nassim.czdashti@.gmail.com> wrote in message
news:1169355344.015758.44280@.m58g2000cwm.googlegro ups.com...
> Hilary Cotter
> thanks,
> I use SQL server 2005-Standard.
> Nassa
> Hilary Cotter wrote:
>
|||Thanks Hilary Cotter,
if I dont have witness how I should specify the principle?
Thanks,
Nassa
On Jan 23, 4:23 pm, "Hilary Cotter" <hilary.cot...@.gmail.com> wrote:
> Ok can you try this then:
> --On your principal
> CREATE ENDPOINT [Mirroring]
> AS TCP (LISTENER_PORT = 5022)
> FOR DATABASE_MIRRORING (ROLE = PARTNER);
> --on your mirror
> CREATE ENDPOINT [Mirroring]
> AS TCP (LISTENER_PORT = 5022)
> FOR DATABASE_MIRRORING (ROLE = PARTNER, ENCRYPTION);
> --on your witness
> CREATE ENDPOINT [Mirroring]
> AS TCP (LISTENER_PORT = 5022)
> FOR DATABASE_MIRRORING (ROLE = WITNESS);
> --Start endpoints
> ALTER ENDPOINT [Mirroring] STATE = STARTED;
> --now backup your database on the principal copy it to your mirror and
> restore it there with norecovery
> --Connect to your mirror
> -- Specify the partner from the mirror server - note this can be an ip
> address rather than the fqdn
> ALTER DATABASE [AdventureWorks] SET PARTNER
> ='TCP://Mirror.corp.mycompany.com:5022';
> --Connect to your principle
> -- Specify the witness from the principal server
> ALTER DATABASE [AdventureWorks] SET WITNESS =
> 'TCP://Wittness.corp.mycompany.com:5022';
> That's it
> --
> Hilary Cotter
> Looking for a SQL Server replication book?http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTShttp://www.indexserverfaq.com
> "Nassa" <nassim.czdas...@.gmail.com> wrote in message
> news:1169355344.015758.44280@.m58g2000cwm.googlegro ups.com...
>
>
>
>
>
Thanks Hilary

>
> - Show quoted text -
|||Hi Hilary Cotter,
It gives me some error messages.
1- after I backup the database in the primary server,then I copy it to
the mirror server and when I try to restore it in the mirror server,it
gives me an error.
2-after I write the code
ALTER DATABASE [AdventureWorks] SET PARTNER
='TCP://Mirror.corp.mycompany.com:5022';
again gives me an error which said : database mirroring transport is
disable in the endpoint configuration.
please inform me as soon as possible.
Thanks,
Nassa
On Feb 14, 9:05 am, "Nassa" <nassim.czdas...@.gmail.com> wrote:[vbcol=seagreen]
> Thanks Hilary Cotter,
> if I dont have witness how I should specify the principle?
> Thanks,
> Nassa
> On Jan 23, 4:23 pm, "Hilary Cotter" <hilary.cot...@.gmail.com> wrote:
>
>
|> > ALTER DATABASE [AdventureWorks] SET PARTNER
>
>
>
>
>
>
>
> Thanks Hilary
>
>
>
> - Show quoted text -- Hide quoted text -
> - Show quoted text -