|
Dear All, I have following statement to create procedure from an assembly
but i am getting error which says can't find type Assembly name
exec sp_configure 'clr enabled',1 --enable sql to execute CLR
reconfigure --install and run it
go
alter database TestTrigger set trustworthy on --enable Database to access external files
go
use TestTrigger
go
drop assembly WriteToFile
go
declare @dllPath varchar(500)
set @dllPath='C:\Documents and Settings\a.hamidy\My Documents\SharpDevelop Projects\WriteToFile\WriteToFile\bin\Debug\WriteToFile.dll'
create assembly WriteToFile from @dllPath with permission_set = external_access --create assembly from following dll file
go
--now create store procedure to access that dll file class + methods
create proc dbo.writeToFile
(
@filePath varchar(500),
@fileContent varchar(max)
)
as external name [WriteToFile].[WriteToFile].[writeInFile]
The last statement (create proc) gives this error
Msg 6505, Level 16, State 1, Procedure writeToFile, Line 2
Could not find Type 'WriteToFile' in assembly 'WriteToFile'.
Abdul Rahaman Hamidy
Database Developer
Kabul, Afghanistan
|
|
|
|
|
I solved it with this
as external name WriteToFile.[WriteToFile.WriteToFile].writeInFile
but now i am getting this error
Msg 6552, Level 16, State 3, Procedure writeToFile, Line 2
CREATE PROCEDURE for "writeToFile" failed because T-SQL and CLR types for parameter "@filePath" do not match.
let me mention the method paramater
public static void writeInFile(String filePath,string contents)
....
Abdul Rahaman Hamidy
Database Developer
Kabul, Afghanistan
|
|
|
|
|
i have solved again
in store procedure creation i used nvarchar instead of varchar
Abdul Rahaman Hamidy
Database Developer
Kabul, Afghanistan
|
|
|
|
|
Hi
iam working on a task management system. The database is sql server 2005. In my database iam keeping the task details such as TaskName,EstimatedStartTime,EstimatedFinishTime etc. i want to calculate the difference of Estimated start and finish time to get the estimated time required to finish the task. The problem is that there may be holidays(saturday,sunday) in between this dates. And there will be only 8 Hrs for a working day. So how will i calculate the estimated time required to finish the tasks. No matter the format of the estimated time required, it may in minutes,hours or in days. Anybody please do a help. Thanks in advance
|
|
|
|
|
try this
select DATEDIFF(hour,'08:00:00','16:00:00')
Instead of startdate and enddate parameters use your column data from your table.
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
Here is what I've used in the past ...
Notice that there is a commented out function call reference that allows for custom logic to provide for holidays to be not considered working days. You would have to code the "IsDateAHoliday" function yourself.
-- You can try this out by invoking the statement: select dbo.GetBusinessDays(cast ('10/01/2007' as datetime),cast('10/09/2007' as datetime))
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date, ,>
-- Description: <Description, ,>
-- =============================================
ALTER function [dbo].[GetBusinessDays]
(
@StartDate datetime,
@EndDate datetime
)
returns int
as
begin
declare @DaysBetween int
declare @BusinessDays int
declare @Cnt int
declare @EvalDate datetime
select @DaysBetween = 0
select @BusinessDays = 0
select @Cnt=0
select @DaysBetween = datediff(Day,@StartDate,@endDate) + 1
while @Cnt < @DaysBetween
begin
select @EvalDate = @StartDate + @Cnt
--if (dbo.IsDateAHoliday(@EvalDate) = 0)
--BEGIN
if ((datepart(dw,@EvalDate) <> 1) and (datepart(dw,@EvalDate) <> 7))
BEGIN
select @BusinessDays = @BusinessDays + 1
END
--END
select @Cnt = @Cnt + 1
end
return @BusinessDays
end
|
|
|
|
|
Hi all,
I have a table Adp_Item_Conversion with following fields
ConversionID identity,
BranchID,
Itemcode,
BatchNo,
LotNo,
Date,
CTSCode
And i need to insert data in this table using a select query on other table as well as some predefined variables because the number of rows to be inserted is not fixed.
insert into Adp_Item_Conversion
(
BranchID,
Itemcode,
BatchNo,
LotNo,
Date,
CTSCode
)
select @branchID,Adp_master_detail.child_itemcode,@Br_No,@lt_No,@tr_date,@tr_code from Adp_master_detail where Adp_master_detail.Parent_Item=@ParentItemID
The problem is I have written an insert trigger on the table. The trigger only writes the last record to the log table. Although i have access to the trigger , i cannot change it now. So i need to rewrite the insert query so as to accommodate the insert as shown above and also the insert should be 1 row at a time. Can i use cursors or is there a other way round????
When you fail to plan, you are planning to fail.
|
|
|
|
|
Hi
I want to a site which shows all Sql Command explanation
Thanks
Satish Pai B
|
|
|
|
|
Try Microsoft.com
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|
|
Try Googling "sql commands". There is lots of good stuff there.
Regards,
Russ
|
|
|
|
|
|
Hi, thanks for looking
I receive a few app errors, the error says:
System.Web.HttpUnhandledException: Exception of type 'System.Web.HttpUnhandledException' was thrown. ---> System.Data.SqlClient.SqlException: Conversion failed when converting the nvarchar value '26' And char(124)+(Select Cast(Count(1) as varchar(8000))+char(124) From [sysobjects] Where 1=1)>0 and ''='' to data type int.
What can this query do?? It will only return the same record. I think they wrote this n the querystring
I guess they were only testing if they could inject code
How could i test how secure is my app??
Thanks in advance
Alexei Rodriguez
|
|
|
|
|
There's an excellent article on SQL Injection attacks (and how to prevent them) here[^].
|
|
|
|
|
here is an sql query in which all fiels are correctly written-
(select admin.*, brok.FirstName+' '+brok.LastName as BrokerName
from adminmsgrecieved as admin,brokerdetails as brok
where admin.AdminId='111' and brok.BrokerId=admin.FromBrokerId ) as custo(AdminId,FromBrokerId,FromCustId,Subject,DateOfMsg,Message,Status,MsgId,BrokerName)
but i got an error "syntax error near as" .This problem is near as custo(..........). Actually I am giving table name generated from query-
(select admin.*, brok.FirstName+' '+brok.LastName as BrokerName
from adminmsgrecieved as admin,brokerdetails as brok
where admin.AdminId='111' and brok.BrokerId=admin.FromBrokerId )
what it may be??
plzz help.....
|
|
|
|
|
Try rewriting the SQL without using 'as'
select admin.*, BrokerName = brok.FirstName + ' ' + Brok.LastName
from adminmsgrecieved admin, brokerdetails brok
where admin.AdminId='111' and brok.BrokerId = admin.FromBrokerId
Also, for future reference, specify what type of SQL database you are using: Oracle, SQLServer, MYSQL, etc. Syntax varies based on the type.
Tim
|
|
|
|
|
thanks...
I am using sql server 2000.It is working well without as but i have to give a name to the table generated fom selet query.This table name will be use further for another join query.so plz guide me how can i give a name to the table.
|
|
|
|
|
The easiest way is to save your first query as a View, then use the View to link into the next query.
eg: The query (fixed up) from your first post is saved as vwBrokerName then do something similar to this:
select tblName.*, vwBrokerName.BrokerName
from tblName
inner joint vwBrokerName on tblName.BrokerID = vwBrokerID
(assuming the ID fields exist in the view and table tblName)
|
|
|
|
|
Hi all,
The application I'm working on is going to be used internationally. So I've loaded up some test data in my SQL Server CE database in fields that are declared as datetime fields. I used American dating when loading them (i.e., MM/DD/YYYY). Now I switched my computer over to UK dating and am searching using UK dating (DD/MM/YYYY). My search date dependent so if search from Jan 1, 2006 through June 2, 2009 I should find an entry that has a date of May 27, 2009. If I'm using American dates and search 1/1/2006 through 6/2/2009 it finds 5/27/2009. But if I'm using UK dates and search for 1/1/2006 through 2/6/2009 it doesn't find 5/27/2009.
I'm not sure if there's something wrong with the way I've declared the fields, with the SQL I use to store the data or with the SQL I use to search for the data. Help?
Denise "Hypermommy" Duggan
|
|
|
|
|
Hi,
assuming your date fields really got declared as DateTime fields, and you use parameterized SQL queries, the only place where it could possibly go wrong is where you parse the user input to turn it into DateTime values; e.g. if you provided a format there, it would use that instead of your regional settings one.
Luc Pattyn [Forum Guidelines] [My Articles]
The quality and detail of your question reflects on the effectiveness of the help you are likely to get.
Show formatted code inside PRE tags, and give clear symptoms when describing a problem.
|
|
|
|
|
Hi
Can anyone tell me how to generate an XML file using a typed dataset?
I have a typed dataset called _WorkStationDataSet. But WriteXml() can not be called direclty from it, I have to create a new instance of it using:
_WorkStationDataSet dsWorkstation = new _WorkStationDataSet(), but then calling WriteXml() on dsWorkstation does not create a hierarchical xml file, instead it just puts two lines of xml at the top of the file showing the name of the dataset.
How do i call WriteXml() on a Strongly Typed Dataset?
Do I need to fill dsWorkstation again once a new instance is created? If yes, how?
Please this is quite urgent
|
|
|
|
|
Make sure the dataset property includeschema = true
Sorry, i meant SchemaSerializationMode = IncludeSchema
Alexei Rodriguez
|
|
|
|
|
Hi
I Just want to know the difference between Network Service Account,Local System Account & Dedicated User Account in sql server 2005
|
|
|
|
|
|
Hi All,
Basically I have a C#.Net Windows Forms application where most of the data is stored in a database (currently SQL Server 2005). Some data that needs storage is file uploads - at the moment I have an FTP setup for transfer of these files to/from a server as and when needed. I also know it is possible to store files in the database (using BLOB's).
I have done the research and found a number of sources that basically agree on one thing - Databases are good for small files ( < 256KB) where as file systems are better for files larger than this. The file uploads in question will most likely be 1MB or larger but can basically be any size.
Anyway, I am posting here because I am looking for some opinions on which option to go for (or maybe another alternative completely). Has anybody had any experience with using either option and can recommend the best route to take?
Thanks for any input
Life goes very fast. Tomorrow, today is already yesterday.
|
|
|
|
|
I'm a big fan of keeping things simple.
Store your files on a fileserver. Store your data in a database server.
Think about how you are going to use this files ... will they need to be updated ? Will there be concurrent access to them ? Do you need version control ? Do you need to track who changed the files ?
If your requirements are simple, then go with the simple solution of storing your files outside the database.
That's my vote.
|
|
|
|
|