|
You would normally use the command:
CREATE ASSEMBLY MyAssembly FROM 'c:\dev\MyAssembly.dll'
WITH PERMISSION_SET = SAFE
Obviously, this would need to be run using sqlcmd or SQL Server Management Studio.
the last thing I want to see is some pasty-faced geek with skin so pale that it's almost translucent trying to bump parts with a partner - John Simmons / outlaw programmer
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
Hi,
thank you that was helpful. But maybe I'm to stupid but it doesn't work correct for me. If I execute this e.g. in sqlcmd then I see the deployed assembly, but I don't see the Stored Procedures in my database (as I said I just see the deployed assembly).
I tried this with a new clean database, but on my sql server I already have another database with the same assembly (and stored procedures). Could this be the reason that I don't see the Stored Procs in my first mentioned database ? (I think I heard time ago something like, that all Stored Procs are saved globally in the sql server)
|
|
|
|
|
That's because the command I showed before is used to make the assembly available to SQL Server. What you need to do is to create the stored procedure that references this. Here is an example of a stored function:
CREATE FUNCTION MyFunction(@Name NVARCHAR(30))
RETURNS INT
AS EXTERNAL NAME [MyAssembly].[MyDll.ClassName].[MyMethod]
I hope that this helps.
the last thing I want to see is some pasty-faced geek with skin so pale that it's almost translucent trying to bump parts with a partner - John Simmons / outlaw programmer
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
Mh yes it helps again  But I think that I am somehow to stupid... I get always an error saying, that the return types of the t-sql- and clr-types aren't equal. I tried it e.g. this way:
create function MyStoredProc(@name nvarchar(30)) RETURNS uniqueidentifier as external name [MyAssembly].[MyClass].[MyStoredProc]
In my c#-code I see that the implemented storedprocedure MyStoredProc has an output parameter with type of SqlGuid (which is a uniqueidentifier (I think)).
I have also another stored procedure that hasn't any output parameter, but which return value should I specify there ? NULL doesn't work
|
|
|
|
|
FreeCastle wrote: Mh yes it helps again But I think that I am somehow to stupid...
Don't think like that. These are all legitimate questions and they don't reflect on you at all.
FreeCastle wrote: In my c#-code I see that the implemented storedprocedure MyStoredProc has an output parameter with type of SqlGuid (which is a uniqueidentifier (I think)).
I have also another stored procedure that hasn't any output parameter, but which return value should I specify there ? NULL doesn't work
It's hard to say what's going on without seeing your code. If you want to email me it then feel free and I will take a look at it.
the last thing I want to see is some pasty-faced geek with skin so pale that it's almost translucent trying to bump parts with a partner - John Simmons / outlaw programmer
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
Hi,
did you receive my email ?
|
|
|
|
|
 Hi Guy's
I want to Create a trigger on 'Table1' by which at the time of insertion the same data will be insert in to another table 'Table2' of same database please help me.
Sasmi
|
|
|
|
|
Try this:
CREATE TRIGGER [InsertIntoTable2] ON [Table1]
FOR INSERT
AS
INSERT INTO Table2
SELECT (Field1, Field2, (and so on)) FROM INSERTED
the last thing I want to see is some pasty-faced geek with skin so pale that it's almost translucent trying to bump parts with a partner - John Simmons / outlaw programmer
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
Hi,
Problem: Get all records from MyTable where mydate value is equal to ‘12/31/2006’. Note: Disregard TimeStamp
I know the solution in T-SQL but in PL-SQL i'm just a newbie.
Can you please help me to convert the below T-SQL comannd to PL-SQL.
---TSQL Solution 1: Use 24hrs timespan
SELECT * FROM MyTable
WHERE MyDate BETWEEN '12/31/2006 12:00:00.000 AM' AND '12/31/2006 12:59:59.999 PM'
---TSQL Solution 2: REMOVE TimeStamp
SELECT * FROM MyTable
WHERE RTRIM(CONVERT(VARCHAR(11),MyDate,101)) = ‘12/31/2006’
Thanks,
|
|
|
|
|
Doesn't PL/SQL still have a Truncate or DateTruncate function?
(Haven't used Oracle for many years.)
|
|
|
|
|
I want develop a database managment module in a DLL that is dynamically loaded at run time , i thought i have problems using ADO beacuse it's contained in another DLL .. which should i use ADO, ODBC or OLE DB ..??
|
|
|
|
|
I'm having a stupid problem:
when I try to use INSERT sentance
INSERT INTO .... VALUE(N 'walla',x);
the first parameter (walla) is ging well
the second parameter (x) is making my prog. stack!
I checked the atrribute of the collumns
and it's like the variable x (int).
can anyone help me?
|
|
|
|
|
It would be better if you showed us the code you are actually using because your description appears to be missing some vital bit of information.
My guess, based on the limited information you've provided, is that x is not a valid column name available to the INSERT statement. However, I get the impression that you think x is a variable name. It isn't.
If it is what I think it is then show some code and I can show you how to fix it.
|
|
|
|
|
I wrote:
int x = 10;
int y = 20;
str= "INSERT INTO table1 (C1,C2) VALUES (x,y)";
I called the sqlCommand object with this str santence.
and then the ExecuteNonQuery(); method.
the collumns C1 and C2 declared as int;
|
|
|
|
|
groundzero111 wrote: int x = 10;
int y = 20;
str= "INSERT INTO table1 (C1,C2) VALUES (x,y)";
There's your problem then. As x and y are inside the quotes, they are treated as literals. You would need to do this as:
str= string.Format("INSERT INTO table1 (C1,C2) VALUES ({0},{1})",x,y);
Before you go any further with this though, I would like to point out that it is a very bad idea to build your SQL like this and execute it. While you are only using local variables here, you will eventually want to move onto inserting data that has been input by a user and this is open to SQL Injection Attacks. See this article for more information:
SQL Injection Attacks[^]
the last thing I want to see is some pasty-faced geek with skin so pale that it's almost translucent trying to bump parts with a partner - John Simmons / outlaw programmer
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
Hello,
I want to write a query something like the following
UPDATE INCOMETABLE SET NAME ='PETER',AGE=30 WHERE RECORDNO = 10;
In the above what should be the keyword of RECORDNO
or there is another way of writing it
THANKS
Prithaa
|
|
|
|
|
Hi,
There is not such a keyword in Sql Server.
(There is one in Oracle, but it's a bad practice to use it in such a case)
What you should do is to use a primary key in the INCOMETABLE table.
UPDATE INCOMETABLE SET NAME ='PETER',AGE=30 WHERE EMPLOYEEID = 10
Uri
|
|
|
|
|
Hello,
Thanks,I got it.
Prithaa
|
|
|
|
|
i have two tables in my database parentTbl with primary key id and childTbl with foreign key pId. i created a relationships between them is it possible to ensure that the record in the parentTbl can not be deleted if it is been used in a record in childTbl.
i want to this from a database level i know if i was using store procedure i could always put a check but i was wondering if open the database and the table directly and try to delete record if i can set up the constraints to even prevent this from occurring once there is a corresponding record in the childTbl
|
|
|
|
|
This is what a foreign key is for. Ensure that it doesn't perform a cascade delete then the database will take care of this for you automatically.
the last thing I want to see is some pasty-faced geek with skin so pale that it's almost translucent trying to bump parts with a partner - John Simmons / outlaw programmer
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
What is the best practice to get multiple output values of stored procedures from ASP.net...
I am doing as below. IS it best practice or you have some good suggestion?
1) At the end of my SP, I am doing SELECT for all output parameters.
2) In ASp.net, i am calling SP thru ExecuteReader from DAL layer.
3) Storing datareader in DATATABLE in Business Layer
4) Passing DataTable to UI layer.
Please suggest....
Amit
|
|
|
|
|
Amit Kumar G wrote: 1) At the end of my SP, I am doing SELECT for all output parameters.
Do you mean you have multiple parameters where the direction is set to output, or do you mean you just call SELECT several times.
Amit Kumar G wrote: 2) In ASp.net, i am calling SP thru ExecuteReader from DAL layer.
3) Storing datareader in DATATABLE in Business Layer
This is what a data adapter already does. You should try and avoid duplicating functionality - unless of course you have some subtle, but important, differences between what the data adapter produces and what you are producing.
Amit Kumar G wrote: 4) Passing DataTable to UI layer.
Personally, I wouldn't pass a data table to the UI unless it was an application to manipulate databases. I'd have the data transformed into business objects and pass the business objects to the UI.
|
|
|
|
|
Colin Angus Mackay Wrote:
Do you mean you have multiple parameters where the direction is set to output, or do you mean you just call SELECT several times
Reply : Multiple paramters set as Output
Amit
|
|
|
|
|
Hi friends
I know that System.Transaction works with SQL Server 2005, but .... Does it work with SQL Server 2000, or Oracle ?
Thanks in advance
PD: happy 2k7 
|
|
|
|
|
how consistancy is maintained when a number opf different user tries to update arecord at the same time
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
