Showing posts with label client. Show all posts
Showing posts with label client. Show all posts

Monday, March 26, 2012

Making managed code calls inside SQL Server in context of the client

Dear all,

I am very new to the subject of writing CLR code inside SQL Server, so I apologise if my questions seem naive.

I have a requirement to populate an asp.net 2.0 GridView control with data columns, some of which are directly from a SQL Server 2005 database, but some of which are calculated by calling CLR methods passing the values from the database columns to those methods.

However, the methods I need to call only make sense in the process context of the client web site which is calling the stored procedure which I want to write to return the data columns.

In effect, I want to be able to make a remote procedure call from within the SQL Server CLR code to the methods available in the client process.

Is this possible? If so, could someone please refer me to an example of how to do it.

If it can be done, it opens up lots of very cool possibilities!

Thanks.

It's a much cleaner design if you can explicitly pass the client context information to the server-side CLR code rather than RPC back to the client. While you can do anything you want if you register the assembly as unsafe, I don't recommend that approach. It's better for both security and performance reasons to keep server-side processing on the server itself as much as possible.

|||

Dear BonnieFe,

Thanks for your suggestion. It would be a much cleaner design, if it was possible. Unfortunately, the data I need back changes for every row in the returned data, since it is obtained by passing a value from a returned data column to a method which has to be called in the context of the calling process.

I have now solved the problem by calling a web service in the calling web site from the CLR code inside SQL Server 2005. This seems to work fine, although it is bound to be slower than it would be if it used RPC back through the SQL connection.

Friday, March 23, 2012

making a dimension smart

Hi,

We have a cube with a client dimension which has lots of rows and about 60 attributes.

The table looks like this

ID, first name, infix, lastname, street, housenumber, postal code, city, gender.

The users want to see all the attributes..

Analysis server is making calculations on every attribute.

The users are only intrested in ID, city and gender but they don't want to loose the other attributes.

How can I tell analysis server that it should not make calculations on the first name, infix and lastname, street, housenumber and post code?

I tried setting the attribute IsAggregable to false but that didn't work.

Any suggestions ?

With regards,

Constantijn Enders

If you don't want the data to aggregate, set the AggregationUsage property to None in the Attributes tab, in the Dimension window (possibly the bottom left window) under the Cube structure tab in VS.

C
|||

what type of calculations you do?

usually you do calculations in numeric values...

you can set aggregation to none...

give more details...

Regards

Friday, March 9, 2012

major difference between sp and trigger

hi
stored procedure and trigger which runs on serverside and which runs on client side.


Dear Kanthk,

Have a look at this :

http://www.sqlservercentral.com/columnists/gvijayakumar/storedprocedurevstriggers.asp

HTH,

Suprotim Agarwal

Maintenence plan backup schedule doesnt work

I am running the latest MSDE with the SQL Server 2000 client tools onWindows 2K Server on my production server. I have a simliar setup on mydevelopment machine except it is running Windows 2K (non-server). Ihave the same issue on both machines (I am wondering if it is relatedto the structure of the database). When I open Enterprise Manager,connect to the server, navigate to the Management folder, and add a newmanagement plan, I experience the following error.
I create a management plan that includes nothing but a completedatabase backup (i.e. no reorganization of index pages, logging, etc.).I set the backup schedule to occur every Sun at 12:00AM and hit OK.Sometimes I get the following error :"Error 8114: Error converting datatype int to tinyint" sometimes not. Either way, though, the same thinghappens, the schedule for the backup is not saved. If I reopen themanagement plan and go to the "Complete Backup" tab, there is noschedule in the schedule box.
I have found this link:
http://www.technologyone.org/new-4581847-3733.html
Which appears to be the same issue that I am having, however, there isno resolution there. I was wondering if anyone had any ideas.
Jason
just a quick dirty question: can you perform a normal backup? Is there a job for the backup? If so will this force run?|||Yes, I can perform manual backups without a problem.And, in fact, I can successfully create a maintenence plan withschedules for things like "Index pages" etc. And these schedules worksuccessfully. However, I am only interested in making a maintenenceplan for complete backups on a schedule and that does not work. Oneworkaround that would probably work is to just use the SQL ServerBackup dialog and set a schedule in there. However, this does not allowme the flexibility I require to manage the backups, such as saving thefiles with their dates appended and triaging the backups into seperatefolders for each database. It also does not allow any extensibility fordown the line when I would like to automate the cleaning andoptimization tasks as well as logging.Finally, I think I should alsomention, that I do have SQL Server Agent running, because this isrequired for the schedules to work correctly.
Jason
|||Have you thought of writing back-up scripts for these processes. Thesecould then be controlled by DTS or if you really have to xp_cmdshell?
|||I have thought about it, but have decided that my efforts are betterspent getting the maintenence plan working for several reasons. Firstoff, all I need is a basic backup and I do not require the flexibilitythat custom scripts would provide (nor do I need the overhead ofmaintaining said scripts). Lastly, the maintenance plan SHOULD work, Ijust have to figure out why it is not working rather than determining aworkaround.
Jason
|||

ok, just is case you do want a script here's one you could use.
--Declare variables

declare @.BACKUP varchar (100)
declare @.day varchar(50) -- date dd/mm/yyyy
declare @.hour varchar (4) -- hour hh
declare @.copy varchar (200) -- copy statement to where ever you want

--set day This is used for the day FULL backup
set @.day = convert(varchar(50), getdate(), 103)
set @.day =replace (@.day, '/', '_')

--set hour This is used for the LOG transaction backups Not needed for full backup
--set @.hour = datepart(hour, getdate())
--set the name of the backup
set @.BACKUP = location of backup i.e. 'E:\data\MSSQL\BACKUP\Northwind\Northwind_'+@.day+'.bak'
--backup database SQL
backup database Northwind todisk=@.BACKUP with stats
--copy the file to where
set @.copy = 'copy'+@.BACKUP+ location'\\servername\drive$\folder\