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 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 debug SP in VS 2003

I can't debug SP in VS 2003
My config:
* Windows XP Pro
* VS.NET 2003
* SQL Server 2000 SP3a
* Breakpoints are not hit in stored procs either... they become question
marks when I run and the tooltip text says "The breakpoint will not
currently be hit. Unable to bind SQL breakpoint at this time. Object
containing the breakpoint not loaded."
I have followed ALL these instructions :
* I have granted myself EXEC permissions on sp_sdidebug -- GRANT EXEC
on master..sp_sdidebug to [MYDOMAIN\ME]
* I have run -- exec sp_sdidebug 'legacy on'
* Sqlle.dll and sqldbreg2.exe exist on your debugging workstation. The
former library is the SQL language engine for the debugger.
* Sqldbg.dll exists on both the database server and the debugging
work-station. This library is the SQL debugging proxy.
* Mssdi98.dll exists in the \binn subfolder of every SQL Server instance
on the database server.
* If you're running the full version of SQL Server
* My domain account has permission to the master database itself.
* I re- execute sp_sdidebug 'legacy on' every time I reboot SQL Server.
* I turned on SQL debugging for every project in my VS solution -
* Connection pooling is disabled.
Any help?i usually debug sp's using query analyzer. i right click the stored
procedure in the debug manager and chose debug. u can even pass paramaters
to it
"Oleg Bulay" wrote:

> I can't debug SP in VS 2003
> My config:
> * Windows XP Pro
> * VS.NET 2003
> * SQL Server 2000 SP3a
> * Breakpoints are not hit in stored procs either... they become question
> marks when I run and the tooltip text says "The breakpoint will not
> currently be hit. Unable to bind SQL breakpoint at this time. Object
> containing the breakpoint not loaded."
> I have followed ALL these instructions :
> * I have granted myself EXEC permissions on sp_sdidebug -- GRANT EXEC
> on master..sp_sdidebug to [MYDOMAIN\ME]
> * I have run -- exec sp_sdidebug 'legacy on'
> * Sqlle.dll and sqldbreg2.exe exist on your debugging workstation. The
> former library is the SQL language engine for the debugger.
> * Sqldbg.dll exists on both the database server and the debugging
> work-station. This library is the SQL debugging proxy.
> * Mssdi98.dll exists in the \binn subfolder of every SQL Server instance
> on the database server.
> * If you're running the full version of SQL Server
> * My domain account has permission to the master database itself.
> * I re- execute sp_sdidebug 'legacy on' every time I reboot SQL Server.
> * I turned on SQL debugging for every project in my VS solution -
> * Connection pooling is disabled.
> Any help?
>
>|||Check out this article...
http://support.microsoft.com/?kbid=839280
HTH
"Oleg Bulay" wrote:

> I can't debug SP in VS 2003
> My config:
> * Windows XP Pro
> * VS.NET 2003
> * SQL Server 2000 SP3a
> * Breakpoints are not hit in stored procs either... they become question
> marks when I run and the tooltip text says "The breakpoint will not
> currently be hit. Unable to bind SQL breakpoint at this time. Object
> containing the breakpoint not loaded."
> I have followed ALL these instructions :
> * I have granted myself EXEC permissions on sp_sdidebug -- GRANT EXEC
> on master..sp_sdidebug to [MYDOMAIN\ME]
> * I have run -- exec sp_sdidebug 'legacy on'
> * Sqlle.dll and sqldbreg2.exe exist on your debugging workstation. The
> former library is the SQL language engine for the debugger.
> * Sqldbg.dll exists on both the database server and the debugging
> work-station. This library is the SQL debugging proxy.
> * Mssdi98.dll exists in the \binn subfolder of every SQL Server instance
> on the database server.
> * If you're running the full version of SQL Server
> * My domain account has permission to the master database itself.
> * I re- execute sp_sdidebug 'legacy on' every time I reboot SQL Server.
> * I turned on SQL debugging for every project in my VS solution -
> * Connection pooling is disabled.
> Any help?
>
>

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 SP with a CTE

hi
i can't create a stored proc
with a CTE ( for recursion )
if i execute the query it works fine
but when i try to put it in a stored proc, it fails
with an error like
"Incorrect syntax near 'with'. If this is intended to be a common table
expression, you need to explicitly terminate the previous statement with a
semi-colon"
this is the query
--
with recursivo( _regla, _verbo, _pregla, _pverbo )
as (
select f.rg_codigo, f.vb_codigo, f.vf_regla, f.vf_verbo
from VerboFlujo f
where f.vf_regla= 199 and f.vf_verbo= 0
union all
select f.rg_codigo, f.vb_codigo, f.vf_regla, f.vf_verbo
from VerboFlujo f
inner join recursivo x on
x._regla= f.vf_regla and
x._verbo= f.vf_verbo
)
select *
from recursivo
atte,
Hernnsolved !
i have a SET NOCOUNT ON before the CTE
it should be SET NOCOUNT ON ;
( with a semicolon)
atte,
Hernn
"bajopalabra" <bajopalabra@.hotmail.com> escribi en el mensaje
news:Ot6FoWsnGHA.3512@.TK2MSFTNGP02.phx.gbl...
| hi
| i can't create a stored proc
| with a CTE ( for recursion )
|
| if i execute the query it works fine
| but when i try to put it in a stored proc, it fails
| with an error like
| "Incorrect syntax near 'with'. If this is intended to be a common table
| expression, you need to explicitly terminate the previous statement with a
| semi-colon"
|
| this is the query
| --
|
| with recursivo( _regla, _verbo, _pregla, _pverbo )
| as (
| select f.rg_codigo, f.vb_codigo, f.vf_regla, f.vf_verbo
| from VerboFlujo f
| where f.vf_regla= 199 and f.vf_verbo= 0
| union all
| select f.rg_codigo, f.vb_codigo, f.vf_regla, f.vf_verbo
| from VerboFlujo f
| inner join recursivo x on
| x._regla= f.vf_regla and
| x._verbo= f.vf_verbo
| )
| select *
| from recursivo
|
| --
| atte,
| Hernn
|
|

can't create SP with a CTE

hi
i can't create a stored proc
with a CTE ( for recursion )
if i execute the query it works fine
but when i try to put it in a stored proc, it fails
with an error like
"Incorrect syntax near 'with'. If this is intended to be a common table
expression, you need to explicitly terminate the previous statement with a
semi-colon"
this is the query
--
with recursivo( _regla, _verbo, _pregla, _pverbo )
as (
select f.rg_codigo, f.vb_codigo, f.vf_regla, f.vf_verbo
from VerboFlujo f
where f.vf_regla= 199 and f.vf_verbo= 0
union all
select f.rg_codigo, f.vb_codigo, f.vf_regla, f.vf_verbo
from VerboFlujo f
inner join recursivo x on
x._regla= f.vf_regla and
x._verbo= f.vf_verbo
)
select *
from recursivo
--
atte,
Hernánsolved !
i have a SET NOCOUNT ON before the CTE
it should be SET NOCOUNT ON ;
( with a semicolon)
--
atte,
Hernán
"bajopalabra" <bajopalabra@.hotmail.com> escribió en el mensaje
news:Ot6FoWsnGHA.3512@.TK2MSFTNGP02.phx.gbl...
| hi
| i can't create a stored proc
| with a CTE ( for recursion )
|
| if i execute the query it works fine
| but when i try to put it in a stored proc, it fails
| with an error like
| "Incorrect syntax near 'with'. If this is intended to be a common table
| expression, you need to explicitly terminate the previous statement with a
| semi-colon"
|
| this is the query
| --
|
| with recursivo( _regla, _verbo, _pregla, _pverbo )
| as (
| select f.rg_codigo, f.vb_codigo, f.vf_regla, f.vf_verbo
| from VerboFlujo f
| where f.vf_regla= 199 and f.vf_verbo= 0
| union all
| select f.rg_codigo, f.vb_codigo, f.vf_regla, f.vf_verbo
| from VerboFlujo f
| inner join recursivo x on
| x._regla= f.vf_regla and
| x._verbo= f.vf_verbo
| )
| select *
| from recursivo
|
| --
| atte,
| Hernán
|
|

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)
> .
>