|
Seems you are stuck then.
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|
|
thanks anyway, just checking to see if I've missed anything
dev
|
|
|
|
|
Add the stored proc in code on app start then drop it on app close. Of course if the app crashes, you're stuffed.
Out of interest, why wouldn't you want the client to have the proc?
Henry Minute
Do not read medical books! You could die of a misprint. - Mark Twain
Girl: (staring) "Why do you need an icy cucumber?"
“I want to report a fraud. The government is lying to us all.”
|
|
|
|
|
no - thing is, because I want this to be highly optimized and best place to put the logic is actually in database. Dilemma is now i have to give away the source code.
Perhaps what I can do is to encapsulate only half in stored proc, the other half in obfuscated dll =)
dev
|
|
|
|
|
devvvy wrote: Don't want to give stored proc to client? What are your options if there is one?
you can use encryption. Eg.
create proc MyProc with Encryption
(
@parameters....
)
as
select query.....
BTW why dont you use .
google [^]
hope it helps...
When you fail to plan, you are planning to fail.
|
|
|
|
|
AFAIK you could play around with the permissions, ie execute only to all but the developer. This will not secure your code but will reduce your exposure. The actual text of the code is stored in a sys table and is accessible to anyone with the right permissions (sa) and the knowledge to get at it.
I'd go with the license option - most clients are not SW people and only want to use the toll not reinvent it.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
When using
SELECT ROW_NUMBER() OVER (ORDER BY id) AS [RowNumber]<br />
FROM table where RowNumber > 1
It returns error 'Invalid column name 'RowNumber'. What is the problem here? I'm using SQL Server Express 2005. Thanks.
|
|
|
|
|
You added 'RowNumber' as a column-name alias. Those can be used in the ORDER BY clause, but can't be referenced in the WHERE part. Since you're using SQL 2005, you can bypass this by using a temporary query, selecting into a new CTE first;
SELECT * FROM
(
SELECT ROW_NUMBER() OVER (ORDER BY ID) AS [RowNumber]
FROM [YourTableNameGoesHere]
) AS CTE_TMP
WHERE [RowNumber] < 10
Enjoy 
I are troll
|
|
|
|
|
I have a procedure running with transaction used in it.
I have used multiple insert in the procedures and finally return the scope_identity() of one of the insert by first setting a variable with scope_identity() and then returning it with select @variable at the end of procedure after commit and in case of RollBack i m returning it as select -9
The issue what am i facing is that sometimes it is skipping the return value in case of Commit Transaction as it is not returning anything to the code.
I am using it like this:
Full Procedure Code with Insert Commands here
set @variable=scope_identity()
IF @@ERROR <> 0
BEGIN
-- Rollback the transaction
ROLLBACK
Select -9
END
COMMIT
select @variable
The procedure is not going in the @@ERROR section and also all the insert values are there in tables after commit but why sometimes it is not returning the value required. I am using ExecuteScaler in the code.
Regards,
Kaushal Arora
Regards,
Kaushal Arora
|
|
|
|
|
You are not setting your variable:
set @variable=scope_identity() <br />
IF @@ERROR <> 0 <br />
BEGIN -- Rollback the transaction <br />
ROLLBACK <br />
Select <big>@variable =</big> -9 <br />
END<br />
COMMIT<br />
select @variable
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|
|
I am saying it is committing the records not giving any error and rollback. It is not returning the value in case of committing the records.
|
|
|
|
|
Without the rest of your code its kinda hard to say then.
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|
|
Hi All,
I have an existing database with a table schema that I need to change. However it seems that there is a conflict in the conversion. I'm trying to change a ntext column to a nvarchar column. I'm using C# Express 2008.
I've tried doing it on an empty table with the connection closed but I still see this error. What's the best way to tackle this schema change? I don't like the idea of having to recreate the table from scratch. As I understand it there is no way to script an SqlCe database ...
Any pointers would be great.
Thanks,
|
|
|
|
|
wow, you still can't do this in sql2008?!
One approach is to create a whole new column with the required type, then copy the content from the old column into it & delete the old column. Of course, the column names cannot be the same, so you'll need to use sp_rename at some point.
Unfortunately, this can get very tricky when there are other constraints in the database that rely on your column (default value is probably most relevant here). Also, be aware of the 4000 character limit of nvarchar.
There's lots of tools out there that will assist with this kind of change. We use xSqlObject here, which is excellent (www.xsqlsoftware.com/Product/Sql_Schema_Compare.aspx[^])
Best of luck!
"An eye for an eye only ends up making the whole world blind"
|
|
|
|
|
Thanks for this. I came to the same conclusion. Also I'm using SQLCE ... is the nvarchar limit 4000 characters for this version? I think it's actually 255 ...
|
|
|
|
|
A quick google suggests the limit is still 4000... don't know though, i've never used CE
|
|
|
|
|
A Real Work at Home Opportunity as an Independent Advertising Executive. We offer home workers the opportunity to earn extra money from the comfort of their own home. Visit Us at http://www.clicknearn.net/idevaffiliate/idevaffiliate.php?id=2449_9
|
|
|
|
|
|
Hi, a lot of PTC sites on net , you can post your ads in the right place.
Thanks
|
|
|
|
|
Hello
I have two Database.
DB1 And DB2 there are tbl1 And tbl2 two tables in corresponding database.
if any value change in tbl1 table the corresponding value will also update in tbl2 tables.
Can any one help me in this regard.
Thanks in advance
Sujit
|
|
|
|
|
|
hi all,
i am trying to install SQL express 2005 from following code
str = Application.StartupPath & "\SQLEXPR32\setup.exe /qb ADDLOCAL=ALL INSTANCENAME=" & InstanceName & " SECURITYMODE=SQL SAPWD=mypassword DISABLENETWORKPROTOCOLS=0 "<br />
Shell(str, AppWinStyle.Hide, True)
it works well for first time. When i install from above code and uninstall from Control panel and then once again i execute the above code then it does not install the SQL server Express
I think Uninstall from control panel does not completely remove the SQL server Express.Its require some more steps
Whether anybody can suggest me that how can i completely uninstall the SQL server Express from my machine ?
|
|
|
|
|
How i can show relationship between tables as a diagram in MYSQL
|
|
|
|
|
Hello All,
I've been googling and based on many articles I belive what I'm asking of sql2000 triggers is not possible for the particular type of error i'm getting.
I am trying to integrate with an existing application on sql 2000 without affecting it's behaviour. When it inserts/updates/deletes in a few tables I need to perform an I/U/D in a sql 2005 DB through a DB link. This is all set up fine, as well as the trigger to do the insert. I need to be able to put error handling in the trigger so that it does not affect I/U/D of the existing product.
Right now, MSDTC is having a fit because I don't have it configured properly on purpose. I did configure it correctly and then reverted so it would raise errors so I could test error handling.
Nothing I've tried so far will let me handle this error in an insert trigger on sql 2000:
Server: Msg 7391, Level 16, State 1, Line 2
The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction. OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator.
Execution stops immediately so I can't even get to an "IF @@ERROR" statement and the transaction is rolled back. I know how to fix this error but I need to be able to handle this type of error so I can use xp_logevent or something similar to monitor. Is it possible - Any ideas?
Does anyone know of a way in sql 2000 to handle this type of error in a trigger on sql 2000?
|
|
|
|
|
Hi Guys,
I am having problem when running the Following SQL script.It takes Pretty long time to return result.
I am having about 500,000 records in my database.
select count(distinct(CR_Cli)),SO_name from Callrecords inner join dbo.StudioOperators
On CR_StudioOperatorID=SO_ID
where datename(month,cr_callstart)='April'
and Cr_cli not in (select Cr_cli from Callrecords where cr_callstart<'2009-03-31')
group by SO_name desc
Is there any way i can write this script?
Thanks you very much.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
