Cant get linq to recognize that sproc has changed

I have a GridView that is populated by a stored procedure via linq. I changed the stored procedure to return an additional column, but I can't get the additional column to show up in the GridView. The GridView has autogeneratecolumns=true, so if I were using a SqlDataSource the new column would show up automagically. But now it is not showing up and I cannot reference it. I can't figure out how to make linq recognize that my stored procedure has changed.

I have tried deleting the sproc definition from the dbml file, making sure the partial class is removed the .vb file, refreshing the stored procedures list in database explorer, and adding the sproc definition back again (a hassle), but still it insists on defining only the original columns and not the new column.

How do get linq to get back in sync with the database?

Is there any way to get linq back in sync after database changes??

sql

Cant get LIKE to work

I have the below procedure that will not work- I must be losing my mind, this is not that difficult - mental roadblock for me.

Using SQL Server 2000 to create SP being called by ASP.Net with C# code behind

stored procedure only returns if input exactly matches L_Name

PROCEDURE newdawn.LinkNameLIKESearch
@.L_Name nvarchar(100)
AS
SELECT [L_Name], [L_ID], [C_ID], [L_Enabled], [L_Rank], [L_URL]
FROM tblContractorLinkInfo
WHERE L_Name LIKE @.L_Name
RETURN

I tried: WHERE L_Name LIKE ' % L_Name % ' no luck. What am I missing?

Thank you

try it like this:

LIKE '%' + @.L_Name + '%'

|||

Ever have a day when you really fell dumb - I am having way too many of them.

Thank you.

can't find xp_sqlagent_proxy_account procedure

I want to allow access to xp_cmdshell to a regular (not sysadmin) user. I am not finding the xp_sqlagent_proxy_account in my master database. Hence I can’t allow this option. Any idea why and how should i proceed from here?
I'm using April CTP.
Thanks
Christina

To do this in SQL Server 2005 use the sp_xp_cmdshel_proxy_account stored procedure. It's actually an xp and listed under the Extended Stored Procedures in master in Management Studio.

Here's a portion of BOL's topic on xp_cmdshell from a recent build (sorry, I do not have an April CTP to test this on or see if this has changed recently).

xp_cmdshell Proxy Account

When it is called by a user that is not a member of the sysadmin fixed server role, xp_cmdshell connects to Windows by using the account name and password stored in the credential named ##xp_cmdshell_proxy_account##. If this proxy credential does not exist, xp_cmdshell will fail.

The proxy account credential can be created by executing sp_xp_cmdshell_proxy_account. As arguments, this stored procedure takes a Windows user name and password. For example, the following command creates a proxy credential for Windows domain user SHIPPING\KobeR that has the Windows password sdfh%dkc93vcMt0.

EXEC sp_xp_cmdshell_proxy_account 'SHIPPING\KobeR','sdfh%dkc93vcMt0'

Don|||Thanks Don. I was waiting for someone to answer since a long time.
i'll try this method as soon as i can and let you know if it worked with the april ctp.

by the way, if i install the june ctp sql server and keep the visual studio in april ctp will it work? i mean will there be connection problems as with previous releases?

Thanks
Christina|||Hi Don.
I tried the function and it worked.
But it seems that it is not mentioned in the BOL of April CTP.

Thanks Again
Christina|||

Thanx Don and Christina,

Just the answer I needed

|||This was the answer I needed as well. I couldn't find this solution documented anywhere else. Thanks.

can't find xp_sqlagent_proxy_account procedure

I want to allow access to xp_cmdshell to a regular (not sysadmin) user. I am not finding the xp_sqlagent_proxy_account in my master database. Hence I can’t allow this option. Any idea why and how should i proceed from here?
I'm using April CTP.
Thanks
Christina

To do this in SQL Server 2005 use the sp_xp_cmdshel_proxy_account stored procedure. It's actually an xp and listed under the Extended Stored Procedures in master in Management Studio.

Here's a portion of BOL's topic on xp_cmdshell from a recent build (sorry, I do not have an April CTP to test this on or see if this has changed recently).

xp_cmdshell Proxy Account

When it is called by a user that is not a member of the sysadmin fixed server role, xp_cmdshell connects to Windows by using the account name and password stored in the credential named ##xp_cmdshell_proxy_account##. If this proxy credential does not exist, xp_cmdshell will fail.

The proxy account credential can be created by executing sp_xp_cmdshell_proxy_account. As arguments, this stored procedure takes a Windows user name and password. For example, the following command creates a proxy credential for Windows domain user SHIPPING\KobeR that has the Windows password sdfh%dkc93vcMt0.

EXEC sp_xp_cmdshell_proxy_account 'SHIPPING\KobeR','sdfh%dkc93vcMt0'

Don|||Thanks Don. I was waiting for someone to answer since a long time.
i'll try this method as soon as i can and let you know if it worked with the april ctp.

by the way, if i install the june ctp sql server and keep the visual studio in april ctp will it work? i mean will there be connection problems as with previous releases?

Thanks
Christina|||Hi Don.
I tried the function and it worked.
But it seems that it is not mentioned in the BOL of April CTP.

Thanks Again
Christina|||

Thanx Don and Christina,

Just the answer I needed

|||This was the answer I needed as well. I couldn't find this solution documented anywhere else. Thanks.

Cant figure out error message in SQL

Can someone please look at my stored procedure? I am trying to create the following stored procedure, but get the following error
messages:

Msg 102, Level 15, State 1, Procedure InsertWork, Line 3
Incorrect syntax near '7'.
Msg 102, Level 15, State 1, Procedure InsertWork, Line 25
Incorrect syntax near'@.7am8am'.

USE [Work]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[InsertWork]
(
7am8am nvarchar(500),
8am9am nvarchar(500),
9am10am nvarchar(500),
10am11am nvarchar(500),
11am12noon nvarchar(500),
12Noon1pm nvarchar(500),
1pm2pm nvarchar(500),
2pm3pm nvarchar(500),
3pm4pm nvarchar(500),
4pm5pm nvarchar(500),
5pm6pm nvarchar(500),
6pm7pm nvarchar(500),
7pm8pm nvarchar(500),
8pm9pm nvarchar(500),
9pm10pm nvarchar(500),
10pm11pm nvarchar(500),
Notes nvarchar(500),
Date nvarchar(15)
)
AS BEGIN
INSERT INTO WorkDay
VALUES
@.7am8am,
@.8am9am,
@.9am10am,
@.10am11am,
@.11am12Noon,
@.12Noon1pm,
@.1pm2pm,
@.2pm3pm,
@.3pm4pm,
@.4pm5pm,
@.5pm6pm,
@.6pm7pm,
@.7pm8pm,
@.8pm9pm,
@.9pm10pm,
@.10pm11pm,
@.Notes,
@.Date
END

You cannot have variables/parameters names that starts with a number. Try prefixing them with an underscore (quick fix).

|||

johram:

You cannot have variables/parameters names that starts with a number.

Actually you can. The parameters have to start with @. for SQL Server to know its a parameter..

CREATE PROCEDURE [dbo].[InsertWork](@.7am8amnvarchar(500),@.8am9amnvarchar(500),@.9am10amnvarchar(500),@.10am11amnvarchar(500), @.11am12noonnvarchar(500),@.12Noon1pmnvarchar(500),@.1pm2pmnvarchar(500),@.2pm3pmnvarchar(500),@.3pm4pmnvarchar(500),@.4pm5pmnvarchar(500), @.5pm6pmnvarchar(500),@.6pm7pmnvarchar(500),@.7pm8pmnvarchar(500),@.8pm9pmnvarchar(500),@.9pm10pmnvarchar(500), @.10pm11pmnvarchar(500),@.Notesnvarchar(500),@.Datenvarchar(15))AS BEGIN INSERT INTO WorkDayVALUES (@.7am8am, @.8am9am,@.9am10am, @.10am11am, @.11am12Noon,@.12Noon1pm,@.1pm2pm,@.2pm3pm,@.3pm4pm,@.4pm5pm,@.5pm6pm,@.6pm7pm,@.7pm8pm, @.8pm9pm,@.9pm10pm,@.10pm11pm,@.Notes,@.Date )END

|||

ndinakar:

Actually you can. The parameters have to start with @. for SQL Server to know its a parameter..

Of course :-)

Can't drop procedure ... Snapshot won't run... Help!

Hi,
I have a server set up for Merge replication. It is the publisher and the
distributer. Has been working fine.
It has two publications.
I wanted the subscribers to get a new copy of the data next time they
replicate, so I went to the first publication, which is stored procs and
view, went to Status tab of the publication properties, and clicked the run
agent now. When it was done, I right clicked on the publication and selected
reinitialize subscriptions.
Then I went to the publication for tables. Right clicked and selected
Re-initialize subscritions. Then I went to the status tab and selected run
agent now. When it came back with a timestamp, red x's appeared on the
replication monitor on the snap shot agents.
The error message it give is :
Cannot drop the procedure 'dbo.sp_sel_B4AC8FE9123F47EDB952DCE5249B4F84_pal'
because it is being used for replication.
As a last resort, I tried to delete the publication, I was going to re do
it. When I try to delete it
it gives me the same error message as above, but with a different sp_sel_
name.
What should I do, or what did I do. I really need it back to working today.
.. .
Any help,
Thanks,
Steve
Well, here is what I did.
I kept trying to delete the publication that I could not get the snapshot
agent to run on, and kept getting the error above. So I delete the stored
proc publication, it went OK. Then I deleted the table publication, and this
time it let me. Then I went to each of the clients, deleted both
subscriptions, and re-created them. Ran fine then, and am able to do manual
pull replications from the subscriber boxes now.
If any one can help me understand what happened, please do. How did I get
in that mess, and what would have been a good way to get out. I was lucky
that I could get to all subscriber boxes and re-create the subscriptions.
Thanks,
Steve
"SteveInBeloit" wrote:

> Hi,
> I have a server set up for Merge replication. It is the publisher and the
> distributer. Has been working fine.
> It has two publications.
> I wanted the subscribers to get a new copy of the data next time they
> replicate, so I went to the first publication, which is stored procs and
> view, went to Status tab of the publication properties, and clicked the run
> agent now. When it was done, I right clicked on the publication and selected
> reinitialize subscriptions.
> Then I went to the publication for tables. Right clicked and selected
> Re-initialize subscritions. Then I went to the status tab and selected run
> agent now. When it came back with a timestamp, red x's appeared on the
> replication monitor on the snap shot agents.
> The error message it give is :
> Cannot drop the procedure 'dbo.sp_sel_B4AC8FE9123F47EDB952DCE5249B4F84_pal'
> because it is being used for replication.
> As a last resort, I tried to delete the publication, I was going to re do
> it. When I try to delete it
> it gives me the same error message as above, but with a different sp_sel_
> name.
> What should I do, or what did I do. I really need it back to working today.
> . .
> Any help,
> Thanks,
> Steve
>
sql

Can't create stored procedure with access 2003 when connected to SQL 2005

It tells me I may have to update a driver.

Where can I find this update and what is updated?

Thanks for the help.

More than likely you have a disconnect with the MDAC. You can download the latest version of that here:

http://msdn.microsoft.com/data/mdac/default.aspx

If that doesn't work out, you might need the SQL Server Native Client. You can find that here:

http://msdn.microsoft.com/data/sqlnative/default.aspx

Buck

Can't Create Asymmetric Key

I'm trying to get my first clr stored procedure going.
I believe I was able to create the clr stored procedure in visual studio,
set 'clr enabled' on the server, create an assembly, create an sp based on
the external name, and finally EXEC it from T-SQL to call it. When I call it
,
I get the System.Security.HostProtectionException below.
I read the documentation, and decided I needed to create an asymmetric key
to give myself permission to execute the clr stored procedure. I tried the
following in SS Management Studio:
CREATE ASYMMETRIC KEY SQLCLRTestKey FROM EXECUTABLE FILE =
'D:\ClrStoredProcedures\ClrStoredProcedu
res.dll'
This generates the following error:
Msg 15208, Level 16, State 1, Line 1
The certificate, asymmetric key, or private key file does not exist or has
invalid format.
Any suggestions on how to create the asymmetric key? The error seems to be
saying that I can't create the key because I don't already have one.
Thanks
--
Randy
Msg 6522, Level 16, State 1, Procedure sp_DotNetFunSProcPrint, Line 0
A .NET Framework error occurred during execution of user defined routine or
aggregate 'sp_DotNetFunSProcPrint':
System.Security.HostProtectionException: Attempted to perform an operation
that was forbidden by the CLR host.
The protected resources (only available with full trust) were: All
The demanded resources were: UI
System.Security.HostProtectionException:
at
System.Security.CodeAccessSecurityEngine.ThrowSecurityException(Assembly asm
,
PermissionSet granted, PermissionSet refused, RuntimeMethodHandle rmh,
SecurityAction action, Object demand, IPermission permThatFailed)
at System.Security.CodeAccessSecurityEngine.ThrowSecurityException(Object
assemblyOrString, PermissionSet granted, PermissionSet refused,
RuntimeMethodHandle rmh, SecurityAction action, Object demand, IPermission
permThatFailed)
at System.Security.CodeAccessSecurityEngine.CheckSetHelper(PermissionSet
grants, PermissionSet refused, PermissionSet demands, RuntimeMethodHandle
rmh, Object assemblyOrString, SecurityAction action, Boolean throwException)
at
System.Security.CodeAccessSecurityEngine.CheckSetHelper(CompressedStack cs,
PermissionSet grants, PermissionSet refused, PermissionSet demands,
RuntimeMethodHandle rmh, Assembly asm, SecurityAction action)
at ClrStoredProcedures.TestClrStoredProcedure.PrintMessage(String Message)
.Hi Randy,
Welcome to use MSDN Managed Newsgroup Support.
From your description, my understanding is: you want to create a CLR stored
procedure and get the System.Security.HostProtectionException error.
If I misunderstood your concern, please feel free to point it out.
Based on my experience, the System.Security.HostProtectionException error
most related to your CLR code.
Please first try to follow the step in the SQL 2005 Books Online to create
a CLR stored procedure and deploy it to the database to check if this issue
is related to your code.
http://msdn2.microsoft.com/en-us/library/ms255336.aspx
If you can successfully deploy the stored procedure, would you please post
your CLR stored procedure here so that I could provide further assistance?
If you have installed Visual Studio 2005, you can create a SQL Server
Project and create a CLR stored procedure and then Deploy it to your
database. You may try this method to check if this issue appeared.
Sincerely,
Wei Lu
Microsoft Online Community Support
========================================
==========
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hello randy1200,

> I believe I was able to create the clr stored procedure in visual
> studio, set 'clr enabled' on the server, create an assembly, create an
> sp based on the external name, and finally EXEC it from T-SQL to call
> it. When I call it, I get the System.Security.HostProtectionException
> below.
Here's the URL for an article I wrote to help with this kind of thing. Its
the same idea the BOL covers, but in more tutorial fashion.
http://www.sqljunkies.com/WebLog/kt...op.com/ktegels/|||You first make a assembly using CREATE ASSEMBLY statement.
And your guery must modify FROM clause.
ASSEMBLY Assembly_Name
"randy1200"?? ??? ??:

> I'm trying to get my first clr stored procedure going.
> I believe I was able to create the clr stored procedure in visual studio,
> set 'clr enabled' on the server, create an assembly, create an sp based on
> the external name, and finally EXEC it from T-SQL to call it. When I call
it,
> I get the System.Security.HostProtectionException below.
> I read the documentation, and decided I needed to create an asymmetric key
> to give myself permission to execute the clr stored procedure. I tried the
> following in SS Management Studio:
> CREATE ASYMMETRIC KEY SQLCLRTestKey FROM EXECUTABLE FILE =
> 'D:\ClrStoredProcedures\ClrStoredProcedu
res.dll'
> This generates the following error:
> Msg 15208, Level 16, State 1, Line 1
> The certificate, asymmetric key, or private key file does not exist or has
> invalid format.
> Any suggestions on how to create the asymmetric key? The error seems to be
> saying that I can't create the key because I don't already have one.
> Thanks
> --
> Randy
>
> Msg 6522, Level 16, State 1, Procedure sp_DotNetFunSProcPrint, Line 0
> A .NET Framework error occurred during execution of user defined routine o
r
> aggregate 'sp_DotNetFunSProcPrint':
> System.Security.HostProtectionException: Attempted to perform an operation
> that was forbidden by the CLR host.
> The protected resources (only available with full trust) were: All
> The demanded resources were: UI
> System.Security.HostProtectionException:
> at
> System.Security.CodeAccessSecurityEngine.ThrowSecurityException(Assembly a
sm,
> PermissionSet granted, PermissionSet refused, RuntimeMethodHandle rmh,
> SecurityAction action, Object demand, IPermission permThatFailed)
> at System.Security.CodeAccessSecurityEngine.ThrowSecurityException(Obje
ct
> assemblyOrString, PermissionSet granted, PermissionSet refused,
> RuntimeMethodHandle rmh, SecurityAction action, Object demand, IPermission
> permThatFailed)
> at System.Security.CodeAccessSecurityEngine.CheckSetHelper(PermissionSe
t
> grants, PermissionSet refused, PermissionSet demands, RuntimeMethodHandle
> rmh, Object assemblyOrString, SecurityAction action, Boolean throwExceptio
n)
> at
> System.Security.CodeAccessSecurityEngine.CheckSetHelper(CompressedStack cs
,
> PermissionSet grants, PermissionSet refused, PermissionSet demands,
> RuntimeMethodHandle rmh, Assembly asm, SecurityAction action)
> at ClrStoredProcedures.TestClrStoredProcedure.PrintMessage(String Messa
ge)
> .
>