Hey,
I changed the database name in the initial cataloge in the web.config conncetion string so that it now connects to other databas that contains same tables as the old one,but now i am getting that error at the update stmt !
thank u in advance
Hiba
Dynamic SQL generation for the UpdateCommand is not supported against a SelectCommand that does not return any key column information.
I am getting the above error if i try to create a update command through a Command builder in .net cf on a SQLCE 3.0 database
The Select query I used for creating the update command is 'SELECT * FROM Users'
I populated a datatable with the above query through a DataAdapter, to check for the primary key and the primary key was available in the table. But still the CB could not able to create an Update Command for that query,
The Primary Key column that I use is of type GUID
Then when try to do the above job using a Typed DataSet with TableAdapters , then it throws the following error
'Update requires a valid UpdateCommand when passed DataRow Collection with modified rows'
can any one please tell me a way to fix this ,
Thanks and Regards
Varadan
Hi, I'm having a problem with my code,I am doing the following - retrieving a field from a table, which is linked to another table, i'm only updating one of the fields in one of the two tables...
Code: ( text )
Hi all,I have a gridview that bound to a SqlDataSource called SqlDataSourceGridView. I have enabled Edit in my GridView, but I do all the updating in code behind with stored procedure (using onRowUpdating). Now each time when I click "Update", the update went through and all the data got updated, but I received this error: Updating is not supported by data source 'SqlDataSourceGridView' unless UpdateCommand is specified.
Description: An
unhandled exception occurred during the execution of the current web
request. Please review the stack trace for more information about the
error and where it originated in the code.
Exception Details: System.NotSupportedException:
Updating is not supported by data source 'SqlDataSourceGridView' unless
UpdateCommand is specified. What do I need to do to fix this problem?Thanks a lot.
Good morning.I'm writing an application that allows users to generate Personal Leave requests and route them to their managers.I'm using the integrated Windows Authentication, so I'm able to get the user's username from HttpContext.Current.User.Identity.Name.How do I dynamically generate the SelectCommand? Right now, I do:Dim strQuery As String = "SELECT * FROM [tblPLRequest] WHERE employee = '" & strUsername & "'"to get the query I want, but when I set my SelectCommand to that, it thinks it's a stored procedure (and can't find it).Am I going about this in the completely wrong way?Thanks.
View 3 Replies View RelatedHi Everybody,I have a probs with dynamic generation.I am writing the probs======================================create proc testasdeclare @query varchar(500)set @query = 'select * from table'----------------------------------------------declare mycur Cursor for Select * from table |open mycur |----------------------------------------------but instate of above block how can I dynamically generate this query?---------------------------------------declare mycur Cursor for exec (@query) |---------------------------------------Or tell me the way.RegardsArijit Chatterjee
View 2 Replies View RelatedHi.I have a database I need to supply something (I'm assuming a t-sql script..maybe something else is better) to update customer tables with.The operations include mostly changing varchar lengths, though a couple ofcolumns were renamed.I'd like to maybe figure out how to get Enterprise Manager or Query Analyzerto generate the scripts.I can't just send alter table scripts because I'm involving all sorts ofconstraints that have to be disabled/or dropped, the alter made, then havethem enabled/ or re-created.Basically I'm hoping to get the tools to do the rather large amount of workfor me. I'm targetting sql server 2000.Can someone make a knowledgeable suggestion?RegardsJeff Kish
View 4 Replies View Relatedfix the below SP which is having cursor output with an alternate logic which can make the SP work.(may be using temp table or any other option)
CREATE PROCEDURE Get_IDS
/*
* SSMA warning messages:
* O2SS0356: Conversion from NUMBER datatype can cause data loss.
*/
@p_uid float(53),
@return_value_argument varchar(8000) OUTPUT
[code]....
Using the Process Info Page of Activity Monitor in SQL Server Management Studio, I can discover which Process ID has been Blocked another Process ID.
I need a scipt i can run to Find out what Query or procedure is actually causing the Blocking.
For example I run a SQL Statement to Insert records in to a Table, but it is blocked by another process runing a SQL Statement. How can i discover the specific SQL Statement that is causing the blocking.
What SQL Script or Method can i use to accomplish this Task. Thanks
I am using SQL Server 2005
Hi
I'm developing a Destination Component. I need to create the input columns in my ReinitializeMetadata() method. This is a small code sample of the method:
Code Snippet
IDTSInput90 input = ComponentMetaData.InputCollection[0];
input.InputColumnCollection.RemoveAll();
IDTSVirtualInput90 vInput = input.GetVirtualInput();
foreach( IDTSVirtualInputColumn90 vColumn in vInput.VirtualInputColumnCollection ) {
SetUsageType( input.ID, vInput, vColumn.LineageID, DTSUsageType.UT_READONLY );
}
This piece of code works fine, but now i want to use a UI Type DataFlowDestinationUI with a Destination mapping page added by base.AddDestinationMappingsPageToForm( form, "" );.
When I open the mapping page - ReinitializeMetadata() is called BEFORE opening this page - this error is shown:
Code Snippet
===================================
Exception from HRESULT: 0xC0047041 (Microsoft Visual Studio)
------------------------------
Program Location:
at Microsoft.SqlServer.Dts.Pipeline.Wrapper.IDTSVirtualInputColumn90.get_UsageType()
at Microsoft.DataTransformationServices.DataFlowUI.DataFlowDestinationUI.mappingPage_MapColumns(Object sender, MapColumnsEventArgs args)
I think it's because the mapping page calls GetVirtualInput() itself. Do you know a solution for this problem?
Thank you very much.
Manuel Bauer
I have a business requirement to build a tabular data model, where I need to mask information of other Agents from a given Agent but I still need to show the overall sales of the given product.Â
For eg: IF an Agent is in APAC region he should see APAC region sales and also should be able see the sales of the same product in other region without knowing region specific break down.
For Agent  "Tom" in APAC region, the numbers will look like this
APAC_Sales = 100,000
Other_Sales = 500,000
And if "John" is in NA region, then the number will look like this for him
NA_Sales    = 200,000
Other_Sales = 400,000
I wanted to create "Roles" based on the Region, so all the agents belong to "APAC" region will have same view as Tom and "NA" region agents will have John's view.
What query should I run?
1. To determine whether a column is a rowguid or not using C# .NET 1.1
2. To add/modify column information and be able to set/change:
- Primary key
- Column Name
- Data Type
- Length
- Allow Null
- Default value
- Precision
- Scale
- Identity
- Identity Seed
- Identity Increment
- Row guid
Thanks a lot!
Hi Craig/Kamal,
I got your email address from your web cast. I really enjoyed the web cast and found it to be
very informative.
Our company is planning to use SSIS (VS 2005 / SQL Server 2005). I have a quick question
regarding the product. I have looked for the information on the web, but was not able to find
relevant information.
We are getting Source data from two of our client in the form of Excel Sheet. These Excel sheets
Are generated using reporting services. On examining the excel sheet, I found out that the name
Of the columns contain data itself, so the names are not static such as Jan 2007 Sales, Feb 2007 Sales etc etc.
And even the number of columns are not static. It depends upon the range of date selected by the user.
I wanted to know, if there is a way to import Excel sheet using Integration Services by defining the position
Of column, instead of column name and I am not sure if there is a way for me to import excel with dynamic
Number of columns.
Your help in this respect is highly appreciated!
Thanks,
Hi Anthony, I am glad the Web cast was helpful.
Kamal and I have both moved on to other teams in MSFT and I am a little rusty in that area, though in general dynamic numbers of columns in any format is always tricky. I am just assuming its not feasible for you to try and get the source for SSIS a little closer to home, e.g. rather than using Excel output from Reporting Services, use the same/some form of the query/data source that RS is using.
I suggest you post a question on the SSIS forum on MSDN and you should get some good answers.
http://forums.microsoft.com/msdn/showforum.aspx?forumid=80&siteid=1
http://forums.microsoft.com/msdn/showforum.aspx?forumid=80&siteid=1
Thanks
Craig Guyer
SQL Server Reporting Services
I need to get a return value from a sproc. I know how to do it this way:
CREATE PROCEDURE A_SPROC (
@RETVAL REAL OUTPUT
)
AS
SET @RETVAL=(SELECT A_VALUE FROM A_TABLE)
RETURN @RETVAL
GO
What if I am using dynamic SQL? How do I set @RETVAL?
Hii I am Varun i have a problem with the dynamic stored procedure
This is my stored procedureALTER PROCEDURE dbo.sp_TimeTableAdjustment1
(@TeacherID_OnLeave numeric(9),
@DateFrom datetime ,@DateTo datetime , @UserID numeric(9)
)
AS
declare @flag as numeric(9)
declare @year as varchar(4)
set @year=(select batch from batchmaster where iscurrent=1 and isdeleted=0)
if( @year=null or len(@year)=0)
set @year = year(getdate())exec ('if not exists(select * from timetableadjustments_'+@year+' where datefrom='''+@datefrom+''' and dateto='''+@dateto+''' and teacherid_onleave='+@teacherid_onleave+')
begin
insert into TimeTableAdjustments_'+@year+' (TeacherID_OnLeave,DateFrom,DateTo,UserID) values ('+@TeacherID_OnLeave+','''+@DateFrom+''','''+@DateTo+''','+@UserID+');
end')
else
set @flag=(select timetableadjustmentid from timetableadjustments_2007 where datefrom=@datefrom and dateto=@dateto and teacherid_onleave=@teacherid_onleave)
return @flag
--exec('select timetableadjustmentid from timetableadjustments_'+@year+' where datefrom='''+@DateFrom+''' and dateto='''+@DateTo+''' and teacherid_onleave='+@TeacherID_OnLeave+'
--')
--return @flag
--exec('@flag=select timetableadjustmentid from timetableadjustments_'+@year+' where datefrom='''+@DateFrom+''' and dateto='''+@DateTo+''' and teacherid_onleave='+@TeacherID_OnLeave+'')
Greetings,
I've search around quite extensively on the net and found a few examples that touch on this subject, but the only definitive one that seemed to solve this problem used a temp table in the UDF, which, to my knowledge, is impossible...
The problem is thus:
I want to create either a stored procedure or a user defined function to return a list of values I can intersperse to use in a WHERE AccountID IN (<values>). This way, if someone were to create a new stored procedure and they wanted to either only select accounts with those IDs or perform a NOT IN and use it to filter.
The Solution I'm attempting:
My idea is best represented in psuedo-code:
- Create a Function that stores all account Ids we relate to a particular account type, in this case, let's say accountsids "100, 101, 102, 407" are all accounts we want to consider "cash".
- The function would look something like:
CREATE FUNCTION CashAccountIDs()
RETURNS TABLE
AS
BEGIN
DECLARE TABLE @t1 (account INT)
INSERT INTO @t1 VALUES (100)
INSERT INTO @t1 VALUES (101)
INSERT INTO @t1 VALUES (102)
INSERT INTO @t1 VALUES (407)
RETURN @t1
END
Then I could call this function by doing something such as:
SELECT *
FROM Accounts
WHERE AccountId IN (dbo.CashAccountIds())
I would presumably do this for other collections of accounts as well, so that I would end up with say 5 functions I could call to filter various types of accounts.
Not too certain if I am approaching this the correct way or not, I've been receiving a myriad of errors trying different methods. If I use the function above it tells me "Must declare @t1", so I modified it so @t1 is declared in the RETURNS statement, and the syntax checks then work, but when I attempt to save the function it tells me "Cannot perform alter on fn_cashaccountids because it is an incompatible object type"
(The code I use to generate this error is:
CREATE FUNCTION fn_cashaccountids ()
RETURNS @t1 TABLE (i INT)
AS
BEGIN
INSERT INTO @t1 VALUES (100)
RETURN
END
Hopefully I've provided enough but not too much info to sift through, it seems to me this would be something encountered a bit before.
Any help is very much appreciated.
- Jeff
Is it possible to write dynamic sql on scalar function and assign the value to return value? I like some thing like below but it is not working...
Thanks
______________________________________________________________________
set @sql = 'select @CallLegKey= min(calllegkey) as CallLegKey
from rt_'+@platform+'_CallLegs
where datediff(day,convert(datetime, CallEndTime) ,'''+cast(@today as varchar(20))+''') = '+cast(@cutoff as varchar(5))
exec @sql
return @CallLegKey
Is it possible in SQL 2000 to return a table variable from dynamic sql? We need to have some code that looks kind of like this:
declare @qry varchar(8000)
declare @sourcetable varchar(100) -- name of source table
declare @mytable table (ID_Num int identity(1,1), Child_Key varchar(100))
set @sourcetable = (select tablename from tblConfig where app = 1)
set @qry = 'insert into @mytable
select * from @sourcetable'
execute (@qry)
if (select count(*) from @mytable) > 0
begin
'insert into myFinalTable
select * from @mytable where blah blah blah
I can get the first part to work by declaring the table variable (mytable) in the dynamic sql, but I can't figure out how to return the table object.
I tried to create a dynamic table, fill in it and return it as recordset. The codes as this:
Declare @tbl Table(id int, name varchar(100), age int)Â
Insert Into @tbl(id, name, age)
Values(1, 'James, Lee', 28),
  (2, 'Mike, Richard', 32),
  (3, 'Leon Wong', 29)
Select * From @tbl Order By age
It works well in "SQL Query Ananizer". But return no records in ASP page.
Hi,
I have a client with the following table structure:
ItemNumber, Name, Description
containing the following Data (example):
ItemNumber
Name
Value
6473764
SDRAM
4GB
6473764
Xeon
2300 Mhz
6473764
Video
256 MB
6473764
Bus
1300 Mhz
6473759
SDRAM
2GB
6473759
Xeon
2000 Mhz
6473759
Video
128 MB
6473759
Bus
1066 Mhz
I am trying to use PIVOT to convert this into Columns:
Item Number
SDRAM
Xeon
Video
Bus
6473764
4GB
2300 Mhz
256 MB
1300 Mhz
6473759
2 GB
2000 Mhz
128 MB
1066 Mhz
The problem is that I do not know the names of the columns before I run the query. I retrieve them based on a a list of Item Numbers:
SELECT DISTINCT Name FROM TableName WHERE ItemNumber IN (...)
What I was trying to do is the following, but I am not exactly sure what is wrong:
WITH ProductLines (ItemNumber, ItemName, ItemValue)
AS
(
SELECT ItemNumber, Name, Value FROM ProductItems WHERE ItemNumber IN (...)
)
SELECT * FROM ProductLines -- Don't know the column names
PIVOT
(
... -- Not sure what should come here
FOR ItemNumber IN (...)
)
Thanks a lot,
Aric Levin
Okay, I'll admit it: I am not a Guru. Far from it. So here is one of the zillion tasks I cannot do - unfortunately it is one that I need to have done... :
I need a recordset containing the Name, Datatype, Size, Precision, and Name_of_parent_table of all columns in all non-system tables in a given database.
Most likely this can be accomplished with one or two SPs, but which ones? And how?
Is there any Kind Soul out there, perhaps?
Peter
Hi,
Is it possible to retrieve column information alone in the sp_columns command? I saw that it has an additional option "@column_name". But when I include it in my sql, it doesn't work the way I had expected it to..
I had tried sp_columns @table_name='mytable', @column_name='COLUMN_NAME'
I expect just the column names alone in the resultset. What am I doing wrong here?
Thanks and Regards,
Celia.
This is the case.... I would like to learn the statement that make therelation between these tables.Why? Cos these are separated in two different databases and if a usermake an update in a table from database X these changes must to beapplied in the other table in the another database:The tables are :Principal Database Name : Server Information 2004Table Name : ClientsFields : ID_Client, ClientSecondary Database Name : Index2003Table Name : ContratosFields : ID_Con, ID_Client, ClientI need to write a Trigger for Update the table Contratos everytime auser change the values in Clients.I´m using the follow Trigger :CREATE TRIGGER UPDate_Clients ON dbo.ClientsFOR UPDATEASupdate Contratosset Client = inserted.Clientfrom Clientsinner join inserted on Clients.Client = inserted.ClientWhen I update the register the follow message in the application raise :"Key column information is insufficient or incorrect. Too many rows wereaffected by update."If somebody can help me THANKS A LOT OF....Leonardo Almeida*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 5 Replies View RelatedThis is the case.... I would like to learn the statement that make therelation between these tables.Why? Cos these are separated in two different databases and if a usermake an update in a table from database X these changes must to beapplied in the other table in the another database:The tables are :Principal Database Name : Server Information 2004Table Name : ClientsFields : ID_Client, ClientSecondary Database Name : Index2003Table Name : ContratosFields : ID_Con, ID_Client, ClientI need to write a Trigger for Update the table Contratos everytime auser change the values in Clients.I´m using the follow Trigger :CREATE TRIGGER UPDate_Clients ON dbo.ClientsFOR UPDATEASupdate Contratosset Client = inserted.Clientfrom Clientsinner join inserted on Clients.Client = inserted.ClientWhen I update the register the follow message in the application raise :"Key column information is insufficient or incorrect. Too many rows wereaffected by update."If somebody can help me THANKS A LOT OF....Leonardo Almeida*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 1 Replies View RelatedI have created a function that will check whether the data is null or not. If its null then it will display that as No data else it will display the original value. Below is the function
GO
Object: UserDefinedFunction [dbo].[fnchkNull] Script Date: 3/4/2015 12:01:58 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
[code]...
The code is working good. However i want the return type to be dynamic. If the data type supplied is integer then i want to return a integer value like 0 if its null. if the data value is varchar then i want to return 'No Data'.
I want to write a function, which accept 3 parameters, 1 TableName 2 ColumnName 3 DateValue, and returns number of records in that table for that particular date(in parameter date), I have written below function but it is not returning the desired result.
CREATE FUNCTION dbo.[f_Rec_cnt]
(@InTableName NVARCHAR(100),
@InDtColName NVARCHAR(50),
@InDate NVARCHAR(50)
)
RETURNS INT
[Code] .....
I don't know why this is so difficult. What I want to do is take a table name as a parameter to build a query and get an integer value from the result of the query. But from all of the research I have been doing, Dynamic SQL is bad in SQL server because of SQL Injections. But my users are not going to be supplying the table names.
Things I have learned:
 - SQL Functions cannot use Exec to execute query strings.
 - SQL Functions can return a concatenated string that could be used by a stored procedure to Exec the query string.
So how can I write a stored procedure that will
Â
1. take a parameter
2. Pass the parameter to a function that will return a string
3. Execute that string as SQL
4. Get a return value from that SQL statement
5. Then finally, from a View, how can I pass a parameter to the stored procedure and get the returned value from the stored procedure to be used as a field in the View?
Numbers 3, 4, and 5 are where I am really stuck. I guess I don't know the proper syntax and limitations of SQL Server.
Hi, all..
I want to know the query to retrieve Column information that we can see from table Design view of Enterprise manager, such as Column name, Pk or not, FK table, Data Type, Null or not, Description(Specially Descrition).
Is there any sp for this or any query for this?
Thank you all...
Hello
I'm trying to get data from as400 server to sql server, trough a job.
The job executes a stored procedure that retrieves data from a view in an other database on the same server. That view gets the data from as400.
Now, when i run the mentioned stored procedure in the query analyzer, everything works fine, but when the stored procedure is executed from the scheduled job, it fails each time giving the error
"Could not get the column information from the OLE DB provider 'IBMDA400'. [SQLSTATE 42000] (Error 7350). The step failed."
on random points during that process (by that i mean the lenght of time it runs)
does anyone know what the diffrence is between executing an sp in query analyzer or using a job, keeping in mind retrieving data from as400?
thanks
Hi,
I need to provide a report that lists the databases in each instance, then the tablenames and the number of rows in each table.
Is there any easy way to do this in SQL 2005?
Cheers
Gregg
Dear Friends,I have table contain 2000 out of those some areduplicate when i select duplicate records by using Enterprise Managerand make modification to one of those duplicate records the followingmessage flashes/display.key columen information is insufficient or incorrect.Too many rows wereaffected by updatepls suggest what is this and how to solve this problemThanks in advanceDinesh Patwal
View 1 Replies View RelatedI'm working on a social network where I store my friend GUIDs in a table with the following structure:user1_guid user2_guidI am trying to write a query to return a single list of all a users' friends in a single column. Depending on who initiates the friendship, a users' guid value can be in either of the two columns. Here is the crazy sql I have come up with to give what I want, but I'm sure there's a better way... Any ideas?SELECT DISTINCT UserIdFROM espace_ProfilePropertyWHERE (UserId IN
(SELECT CAST(REPLACE(CAST(user1_guid AS VarChar(36)) + CAST(user2_guid AS VarChar(36)), @userGuid, '') AS uniqueidentifier) AS UserId FROM espace_UserConnection WHERE (user1_guid = @userGuid) OR
(user2_guid = @userGuid))) AND (UserId IN
(SELECT UserId FROM espace_ProfileProperty))
Please provide me a column-level merge replication in SQL Server 2005. In the SQL Sever books online, I could able to get very few informaiton about this topic. Please provide me a best practices for using this kind of conflict resolution in Merge-replication.
View 5 Replies View Related