I'm using Dynamic Data and have created a custom List.aspx page. I simply want to populate the grid with a stored procedure. I've seen some posts about doing this with a LinqDataSource but not an EntityDataSource. Does someone have an example or a different way of doing this? Thanks.

Using the SQL2008R2 profiler and DTA connected to a SQL2000 server.
I created a tuning tracefile with profiler for a certain db on the sql2000 server because I need to tune 1 table in the database referenced by 3 parameterized stored procedures which built a query depending on the parameters and is executed with an sp_executesql
statement.
When I run the DTA loading the tracefile I see the exec stored procedure events with it's parameters and the message Event does not reference any tables. And that's it.
The DTA generates no Recommendatioin at all.
How can I get the exact sql statements in the tracefile so the DTA can do what he needs to do?
Regards

The reason that I would like to consider this functionailty is because my table architecture is complicated and I do not want to modify my master table to accept all of this data where some of the data should be normalized into sub tables. Has anyone
see evidence of the stored-procedure parm approach? Is this best accomplished through VS 2010 or can I do it through SPD?
Thanks

Hi everyone,
I wish to create a scenario whereby my Stored Procedure will be able to do an Insert values into two colunms but single row of a table.
For instance:
AS
BEGIN
INSERT INTO Table1 (Message1, Number1) VALUES(@Message1, @Number1)
END
When I do the above stored procedure using SQL Express, and Excecute the stored procedure in VS2005 C# using ADO.Net, the table is updated but the values of Message1 and Number1 are inserted into different rows i.e. when value is inserted into Message1,
Number1 is '0' and vice-versa.
what I really want is for both Message1 and Number1 to be inserted with values at the same row. How do I acheive this please?
Many thanks in advance.

Hi everyone,
I wish to create a scenario whereby my Stored Procedure will be able to do an Insert values into two colunms but single row of a table.
For instance:
AS
BEGIN
INSERT INTO Table1 (Message1, Number1) VALUES(@Message1, @Number1)
END
When I do the above stored procedure using SQL Express, and Excecute the stored procedure in VS2005 C# using ADO.Net, the table is updated but the values of Message1 and Number1 are inserted into different rows i.e. when value is inserted into Message1,
Number1 is '0' and vice-versa.
what I really want is for both Message1 and Number1 to be inserted with values at the same row. How do I acheive this please?
Many thanks in advance.

This has me brain locked.
1. I've got a stored procedure.
2. It does a select with a couple joins.
I want to take the results of that stored proc and read them into some a table -- temp table would be ideal.
here's my (really tough) constraints... these really complicate things:
1. the stored proc uses dynamic SQL. As a consequence, I don't know the columns it's going to return (it's kind of the whole point of using it).
2. Since the stored proc uses dynamic SQL, it needs to use sp_executesql internally.
What won't work(i don't think?):
1. I can't create a function that returns a table and does a "return select * from....", since you can't (I don't think?) call a stored a stored proc in the return statement of a function
2. I can't just do an "Insert into #xyz exec .." because I can't pre-define the temp table (I think this is needed?).
3. I don't have a linked server. and i'd rather not. All of this runs local in the same instance of SqlServer, and will have to handle loads of volume (each hit will return a few million rows). So (I think?) OPENQUERY is out.
4. OPENROWSET is a possibility, I suppose, but again, everything is running locally. I think that goes through OLEDB (?) and I'm not sure that's even an option.
THOUGHTS??? HELP?? is it even possible??
Pl

I am experiencing a strange issue related to schema qualification at a customer. No matter what we've tried we cannot recreate the issue internally. I am hoping someone can help.
We create a Stored Procedure on a schema called "Import". In this stored procedure, we have numerous references to tables on the "dbo" schema, but we have not qualified them (I know this is not best practice, but it's legacy code and we're not going to change
it right now). This has been used at other customers without issue. This one new customer is getting an issue where it does not seem to find the dbo table when trying to create the stored procedure. If we take the query out of a stored proceure and just run
it as a T-SQL script, it works.
So, the code below works
select *
from Import.Dirsync a
join Inventory i
on a.Audit_InventoryID = i.InventoryID
But this does not. It complains that the column "InventoryID" on the table "Inventory" does not exist.
create procedure Import.Test as
select *
from Import.Dirsync a
join Inventory i
on a.Audit_InventoryID = i.InventoryID
My understanding is that any object on the dbo schema will be resolved.
Again, this is working on all other servers with our databases.
Any suggestions?
Thanks
Craig Bryden - Please mark correct answers

I'm creating a stored procedure that will take the results of a temp table and check for existence using the IN keyword. The code looks like this:
ALTER PROCEDURE [dbo].[studentBranchLogin]
-- Add the parameters for the stored procedure here
@userName varchar(50),
@userPass varchar(20)
AS
BEGIN
DECLARE @dActive bit;
DECLARE @TT TABLE (dept int, active bit);
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
INSERT INTO @TT(dept, active)
SELECT deptid, active FROM srcuDeptId WHERE active = TRUE
-- Insert statements for procedure here
SELECT srcuid FROM srcuLogins WHERE logon = @userName AND password = @userPass AND srcuid IN @TT
END
I get a Msg 170, Level 15 state 1
Line 24: Incorrect syntax near '@TT'.
So while I'm sure the @TT temp table isn't being read, I find very few resources for answer what the proper syntax should be.
Any assistance is greatly appreciated.

My stored procedure expects a uniqueidentifier as a parameter. However, this unique identifier is stored in a table variable, which looks like this:DECLARE @TableGiftGuid table ( GiftGuid uniqueidentifier )When I try to execute a stored procedure by passing the GiftGuid like so:EXEC my_procedure (SELECT GiftGuid FROM @TableGiftGuid) I get an error. What I ended up doing is declaring another variable, storing the GiftGuid into it and then passing the variable to the stored procedure like this: DECLARE @TableGiftGuid table ( GiftGuid uniqueidentifier )
DECLARE @GiftGuid uniqueidentifieDECLARE @TableGiftGuid table ( GiftGuid uniqueidentifier )
DECLARE @GiftGuid uniqueidentifier
SET @GiftGuid = (SELECT GiftGuid FROM @TableGiftGuid)
EXEC my_procedure @GiftGuidThat works, but is there a more elegant way of doing this?

Hello,
I have a table-valued function that splits string into a table column. I can easily call this function from a stored procedure within a regular SELECT statement:
SELECT * FROM Table1
WHERE Code1 = '1'
AND Code2 IN(SELECT * FROM [dbo].[fnSplitValues](@Code2String))
However when I try to use the same logic for a dynamic SELECT statement:
‘SELECT * FROM Table1
WHERE Code1 = '1'
AND Code2 IN(SELECT * FROM ' + [dbo].[fnSplitValues](@Code2String) + ')’
I get an error ‘Cannot find either column "dbo" or the user-defined function or aggregate "dbo.fnSplitValues", or the name is ambiguous.’
Any idea what is wrong with my dynamic SQL?
Please help,
Lana

hi my requirement is like i want to insert data to table after caliculationliketable name salaryhikecolumns salaryinput,20%hike, 25%hike and 30%hikesalaryinput column details to be input by the user and hike is to be caliculated and inserted with 20%hike, 25%hike and 30%hike are columns of same table.plz help me.thank you very much.

Hi All,
I am just curious to know if there is any easier way to synchronize the TypedDataSets with the chages made to backend Table / Stored Procedure schema.
I use Typed DataSets in my project extensively and I found them very useful and easier to code, but difficult to maintain
The difficuly I have always faced is whenever there is a change in a backend database table structure or stored procedure that is linked to any of the Typed DataSet in our project requires to recreate whole DataSet again by scrapping the old one. The backend changes will not be reflected unless and untill you recreate the whole stuff again.
Is there any easier way to synchronize those typed datasets with the changes made to backend database schema or any other workaround that will not required to recreate the whole DataSet again.Hope above question makes sense. Any ideas.

Hello. First, I don't know if this is the right forum section to post this in, but I couldn't fine a more suitable one. Feel free to move this if it's totally worng.
I have a weird problem when using LINQ to SQL and Store Procedures with a return value...
The problem is that when I drag my SP's onto the "Methods Pane" the property field "Return Type" is set to (None) and greyed out. And here's the most weird thing... I created a test SP yesterday wich returned SCOPE_IDENTITY() and it worked flawlessly. Dragged it onto the "Methods Pane", could play with the return type property (hence it was not greyed out) and everything was working out nice. I used a ISingleResult<T> and could fetch the .ReturnValue...
Today I was going to contiune to create the rest of the SP's i needed with a return type but today when I drag my Store Procedures to my Methods Pane, I couldn't access the Return Type property... I even dragged the SAME procedure I used yesterday (wich then worked great) and now I can't get the dbml-layer to notice that there is a return value...
I've tried to create new solutions, new Data Layers, used different databases/servers, but still the same problem... Don't really know if it's LINQ or something else that's causing this...
This is how part of my SP wich I used yesterday to test looks