Heres my problem, the first part selects a row from the database, if
there is no row with the criteria it inserts a row and then returns it,
the problem is the IF statement that inserts the row, never returns the
select after it. if there is a row initially in the database, it
returns the right information, I just can't get it to return the row
after inserting it. Anyone know what the problem could be?
Stored procedure:
ALTER PROCEDURE dbo.CheckCurrentPayPeriod
(@UserID varchar(50))
AS
BEGIN
-- This SP checks to see if the current PayPeriod exists,
-- if not it will create the payperiod for them and return
DECLARE @appStartDate DATETIME
DECLARE @dt DATETIME
DECLARE @rows int
SET @appStartDate = (SELECT PayPeriodStart FROM PayPeriodStart)
SET @dt = GETDATE()
SELECT
UserID
FROM
PayPeriod
WHERE
(PeriodStart <= CONVERT(varchar(10), @dt, 101)) AND (PeriodEnd >=
CONVERT(varchar(10), @dt, 101)) AND (UserID = @UserID)
-- Inserts their new PayPeriod
DECLARE @PayPeriodID int
if (@@ROWCOUNT = 0)
BEGIN
DECLARE @sDate datetime
DECLARE @eDate datetime
SET @sDate = @appStartDate
SET @eDate = DATEADD(day, 13, @sDate)
INSERT INTO
PayPeriod
(UserID, PeriodStart, PeriodEnd)
VALUES
(@UserID, @sDate, @eDate)
where @serilializedReturnCode is locally declared as varchar(250)
This is the error I get:
Syntax error converting the varchar value '<return_code><error_code>0</error_code><row_count>0</row_count></return_code>' to a column of data type int.
I'm still having issues with this despite my attempts to resolve. I even have "with exec as dbo" in my sproc, and and "exec as dbo" in my execution, but still the encrypted data returns nulls when I exec as a user other than DBO. Below is precisely what I have done. All ideas are welcomed.
TIA, ChrisR
--If there is no master key, create one now
IF NOT EXISTS (SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101) CREATE MASTER KEY ENCRYPTION BY PASSWORD = '23987hxJKL95QYV4369#ghf0%94467GRdkjuw54ie5y01478d Dkjdahflkujaslekjg5k3fd117 r$$#1946kcj$n44ncjhdlj' GO
CREATE CERTIFICATE HumanResources037 WITH SUBJECT = 'Employee Social Security Numbers'; GO
CREATE SYMMETRIC KEY SSN_Key_01 WITH ALGORITHM = DES ENCRYPTION BY CERTIFICATE HumanResources037; GO
USE [AdventureWorks]; GO
-- Create a column in which to store the encrypted data ALTER TABLE HumanResources.Employee ADD EncryptedNationalIDNumber varbinary(128); GO
-- Open the symmetric key with which to encrypt the data OPEN SYMMETRIC KEY SSN_Key_01 DECRYPTION BY CERTIFICATE HumanResources037;
-- Encrypt the value in column NationalIDNumber with symmetric -- key SSN_Key_01. Save the result in column EncryptedNationalIDNumber. UPDATE HumanResources.Employee SET EncryptedNationalIDNumber = EncryptByKey(Key_GUID('SSN_Key_01'), NationalIDNumber); GO
-- Verify the encryption. -- First, open the symmetric key with which to decrypt the data OPEN SYMMETRIC KEY SSN_Key_01 DECRYPTION BY CERTIFICATE HumanResources037; GO
-- Now list the original ID, the encrypted ID, and the -- decrypted ciphertext. If the decryption worked, the original -- and the decrypted ID will match.
alter procedure getDecryptedIDNumber with exec as owner as SELECT NationalIDNumber, EncryptedNationalIDNumber AS "Encrypted ID Number", CONVERT(nvarchar, DecryptByKey(EncryptedNationalIDNumber)) AS "Decrypted ID Number" FROM HumanResources.Employee; GO
/*works for me, shows the decrypted data*/
exec getDecryptedIDNumber
USE [master] GO
CREATE LOGIN [test] WITH PASSWORD=N'test', DEFAULT_DATABASE=[AdventureWorks], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF GO
USE [AdventureWorks] GO
CREATE USER [test] FOR LOGIN [test] GO
use [AdventureWorks] GO
GRANT EXECUTE ON [dbo].[getDecryptedIDNumber] TO [test] GO
GRANT IMPERSONATE ON USER:: dbo TO test; GO
/*Now, open up a "file/new/DB Engine Query" and login with the test login*/ exec as user = 'dbo' exec getDecryptedIDNumber
/*This returns NULL values where it should show the decrypted data*/
We have a service written in c# that is processing packages of xml that contain up to 100 elements of goods consignment data.
In amongst that element is an identifier for each consignment. This is nvarchar(22) in our table. I have not observed any IDs that are different in length in the XML element.
The service picks up these packages from MSMQ, extracts the data using XPATH and passes the ID into the SPROC in question. This searches for the ID in one of our tables and returns a bool to the service indicating whether it was found or not. If found then we add a new row to another table. If not found then it ignores and continues processing.
The service seems to be dealing with a top end of around 10 messages a minute... so a max of about 1000 calls to the SPROC per minute. Multi-threading has been used to process these packages but as I am assured, sprocs are threadsafe.It is completing the calls without issue but intermittently it will return FALSE. For these IDs I am observing that they exist on the table mostly (there are the odd exceptions where they are legitimately missing).e.g Yesterday I was watching the logs and on seeing a message saying that an ID had not been found I checked the database and could see that the ID had been entered a day earlier according to an Entered Timestamp.
USE [xxxxxxxxxx] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO
[code]....
So on occasions (about 0.33% of the time) it is failing to get a bit 1 setting in @bFound after the SELECT TOP(1).
change @pIdentifier nvarchar(25) to nvarchar(22) Trim any potential blanks from either side of both parts of the identifier comparison Change the SELECT TOP(1) to an EXISTS
The only other thought is the two way parameter direction in the C# for the result OUTPUT. I have been unable to replicate this using a test app and our test databases. Have observed selects failing to find even though the data is there, like this before?
I need to run a select statement that only returns 50 rows. How do I limit the amount of rows returned? Normally the query will return hundreds of rows but all I need is the first 50 it retrieves. I have looked in the BOL and can only find help with a block cursor not just a query.
Is there a way to create a query that will return only 1 row within a join for example:
select a,b,c,d from tbl1 inner join tbl2 on top 1 tbl2.a = tbl1.a <-- return only the top record here...
I dont have too much of a problem with joins... however I was wondering if there was a mechanism to just specify what would be the top most record from a join clause.
I have a query set up that returns the data that I would like, but Iwould only like the latest data for each vehicle number. The query Ihave set up isSELECT TOP 100 PERCENT dbo.vwEvents.EventName,dbo.luSessionAll.SessionName, dbo.luOuting.OutingNumber,dbo.luVehicle.VehicleName, dbo.luOuting.OutingID,dbo.tblOutings.OutingStartTime,dbo.tblSessions.Ses sionDate,dbo.tblSessions.SessionStartTimeFROM dbo.vwSessions INNER JOIN dbo.vwEvents ONdbo.vwSessions.Event = dbo.vwEvents.EventIDINNER JOINdbo.luSessionAll ON dbo.vwEvents.EventID =dbo.luSessionAll.Event INNER JOINdbo.luOuting ON dbo.luSessionAll.SessionID =dbo.luOuting.SessionID INNER JOINdbo.luVehicle ON dbo.luSessionAll.Vehicle =dbo.luVehicle.VehicleID INNER JOINdbo.tblOutings ON dbo.luOuting.OutingID =dbo.tblOutings.OutingID INNER JOINdbo.tblSessions ON dbo.tblOutings.[Session] =dbo.tblSessions.SessionIDGROUP BY dbo.vwEvents.EventName, dbo.luSessionAll.SessionName,dbo.luOuting.OutingNumber, dbo.luVehicle.VehicleName,dbo.luOuting.OutingID, dbo.tblOutings.OutingStartTime,dbo.tblSessions.SessionStartTime, dbo.tblSessions.SessionDateORDER BY dbo.luVehicle.VehicleName, dbo.tblSessions.SessionDate,dbo.tblSessions.SessionStartTime, dbo.tblOutings.OutingStartTimethis returns all the outings. I would like the outing that has, inorder of importance, the latest session date, latest session time andlatest outing start time. Outing start time can sometimes be <<Null>>but the other two always have values. How would I go about doing this?thanks in advance for any help
I have a stored procedure below that returns a table of coaches. It worked before now it does not. It returns nothing, in vistual studio 2005 and on my asp.net webpage. But when I execute it in query analyzer with just SELECT * FROM Coaches it returns the rows. There is no error just will not return what I need. I recreated the database and stored procedure still doing the same thing. Any ideas? Would this be on my server hosting side? ALTER PROCEDURE [dbo].[GetCo]ASSELECT * FROM Coaches
Hi All: My below sub in application is returning only the Header Row instead of the relevant rows I guess therez problem in my "str" Syntax. However I fail to understand where exactly is it faultering. Private Sub btnSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSearch.Click Try con.Open()Dim empID As String = txtEmpID.Text Dim FName As String = txtFName.Text Dim LName As String = txtLName.TextDim str As String = "Select Emp_ID, Emp_FName,Emp_LName,Emp_Address1,Emp_HNo,Emp_MNo from Emp_Details where Emp_ID='" & empID & " ' Or Emp_LName='" & LName & " ' Or Emp_FName=' " & FName & "'"""Dim da1 As New SqlDataAdapter(str, con) da1.Fill(ds, "Emp_Details") dgEmp.DataSource = ds dgEmp.DataMember = "Emp_Details" dgEmp.DataBind() Finally con.Close() End Try End Sub
Thanks in Advance for your quick help. Regards, Brandy
TotalSelected.Value = SqlDataSource1.SelectCommand = "SELECT COUNT(*) FROM tblNews";
the reason i am tring to do this is so if i can find out the amount of rows before sqldatasource selects for details view then i can make the sqldataesource select depends on total minus 5 so e.g. if total 200 then - 5 so i can select bottom 195 so it misses top 5 for details view any1 any ideas? Thanks Andy,
I was racking my brains trying to figure out why SomeCommand.ExecuteNonQuery() was not returning any rows... SQL Server 2005 likes to put the SET NOCOUNT ON statement in every stored procedure you write. By hiding the count of records touched by your query, you also disable the results to be consumed by your application. So I don't recommend using this statement for your stored procedures and ASP.NET applications, as this functionality is fairly critical for error trapping.
This question has been posted on the site before but I could not find any resolution....I want to return rows 11 - 20 from a query that returns 100 records without using a cursor or temp table.
The closest query I have found is a query that numbers the rows, but I can't seem to use rownumber in a between clause...
Use Pubs SELECT emp_id, lname, fname, job_id, (SELECT COUNT(*) FROM employee e2 WHERE e2.emp_id <= e.emp_id AND e2.job_id = 10) AS rownumber FROM employee e WHERE job_id = 10 ORDER BY emp_id
I have two tables and I want to return data from both. Currently my select statement is returning just 1 child record for each parent record and I want to return all child records that match the parent record.
Say you have a table that contains 1000 rows, you create a simple select statement with a where clause that returns 100 or so of those rows. Easy enough.
Now, let's say that you wish to modify that select so that out of the 100 rows that match the where clause criteria, you only wish to return 10 rows randomly (i.e., you could run this query multiple times and get different results). How exactly would you go about doing this, efficiently?
I've thought about creating a stored procedure that will query the initial 100 rows into a temp table with an additional column (to number the rows from 1 to 100). Then setting up a loop (10 iterations) that will then generate a random number from 1 to 100 and select that row number into another temp table. At the end of the loop I'll have my table of randomly selected records. I am sure there is probably a better way to do this...
I'm trying to write a query that will return rows within a specified range and print to a Crystal Report. When I run the query, it produces 2 row of everything. I would use the SELECT DISTINCT clause, but Crystal Reports will not let me edit the Select statement. But I can edit the FROM, WHERE and ORDER BY clauses. I think the problem is in my INNER JOINS but I'm having a problem figuring it out. Can someone please guide me in the right direction.
SELECT DrawingVouchers."PlayerID", DrawingVoucherNumbers."PromoID", DrawingVoucherNumbers."VoucherNumber", DrawingVoucherNumbers."IssueDate", DrawingVoucherNumbers."UserID", CDS_PLAYER."LastName", CDS_PLAYER."FirstName", CDS_ACCOUNT."Address1A", CDS_ACCOUNT."City1", CDS_ACCOUNT."State1", CDS_ACCOUNT."Zip1" FROM { oj (("WinOasis"."dbo"."DrawingVouchers" DrawingVouchers INNER JOIN "WinOasis"."dbo"."DrawingVoucherNumbers" DrawingVoucherNumbers ON DrawingVouchers."PlayerID" = DrawingVoucherNumbers."PlayerID") INNER JOIN "WinOasis"."dbo"."CDS_PLAYER" CDS_PLAYER ON DrawingVoucherNumbers."PlayerID" = CDS_PLAYER."Player_ID") INNER JOIN "WinOasis"."dbo"."CDS_ACCOUNT" CDS_ACCOUNT ON CDS_PLAYER."Player_ID" = CDS_ACCOUNT."Primary_ID"} WHERE DrawingVoucherNumbers."VoucherNumber" >= 37806 AND DrawingVoucherNumbers."VoucherNumber" <= 37813
SELECT [tblSections].[pageTitle], [tblSections].[sectionURL], [tblSectionContents].[articleID], [tblSectionContents].[fileID], [tblSectionContents].[linkID], [tblCopy].[copyText], [tblFiles].[fileName], [tblFiles].[fileCaption], [tblGroupings].[grouping], [tblLinks].[linkURL] FROM [tblSections] LEFT JOIN [tblSectionContents] ON [tblSectionContents].[sectionID] = [tblSections].[id] LEFT JOIN [tblCopy] ON [tblSectionContents].[articleID] = [tblCopy].[id] LEFT JOIN [tblFiles] ON [tblSectionContents].[fileID] = [tblFiles].[id] LEFT JOIN [tblGroupings] ON [tblFiles].[groupingID] = [tblGroupings].[id] LEFT JOIN [tblLinks] ON [tblSectionContents].[linkID] = [tblLinks].[id] WHERE [tblSections].[id]=2 ORDER BY [tblSectionContents].[articleID], [tblSectionContents].[fileID], [tblSectionContents].[linkID]
If I pass it the ID of a section that has files or copy or [stuff in other tables] attached, then I get a result set that makes sense.
But if I pass it a section ID that doesn't reference any other content tables (ie: the section just has a title and a link URL), I don't get anything back.
Shouldn't it should still get me the fields from the row in tblSections that matches the ID I'm passing it?
I can't even think how I would go about beginning to solve this problem, so any tips/pointers would help immensely.
I'm running a query that builds a time line of a process using messages in a table called Event. The Event table is joined with a table called Charge_Info based on N_Charge, the charge number. It builds the time line by looking in the Message field for various text strings that signify a specific point has been reached in the process and builds a record for each charge number with data from N_Charge and timestamps from the events table.
The problem I have is that there aren't records for every event in the Event table, ie, for every charge that doesn't have a "Heating Complete" event, the returned recordset has no mention of that charge. The system recording these events is pretty much crap, but there's nothing to be done about that right now. What I want to happen is for the query to return every record in N_Charge and a null string or NULL in place of the non-existing events.
The stored procedure is pasted below. I pass the dates I want and select from the Event table into a temporary table and then do the join/text search on that table. It helped performance tremendously compared to the text search searching all 400,000+ records in the Event table and then doing the date select.
Thanks for any help in advance, TimC
SELECT EVENT.Time_Stamp, EVENT.N_CHARGE, EVENT.Message INTO #EVENT_FILTERED FROM EVENT WHERE (EVENT.TIME_STAMP >= @StartDate) AND (EVENT.TIME_STAMP < @EndDate)
SELECT CHARGE_INFO.TIME_STAMP, CHARGE_INFO.N_CHARGE, CHARGE_INFO.BASE, CHARGE_INFO.N_RECIPE, CHARGE_INFO.N_FCE, CHARGE_INFO.N_CH, CHARGE_INFO.HEIGHT, CHARGE_INFO.CREW_EOC, CHARGE_INFO.CREW_SOC, CHARGE_INFO.TIME_START, CHARGE_INFO.TIME_FCE_SET, CHARGE_INFO.TIME_FCE_IGNITED, CHARGE_INFO.TIME_FCE_REMOVED, CHARGE_INFO.TIME_CH_SET, CHARGE_INFO.TIME_CH_REMOVED, CHARGE_INFO.WEIGHT, CHARGE_INFO.TIME_POST_PRG_COMPLETE, EVENT.TIME_STAMP AS IC_Set, EVENT_1.TIME_STAMP AS Cycle_Started, EVENT_2.TIME_STAMP AS Leak_Test_Done, EVENT_3.TIME_STAMP AS End_N2_PrePurge, EVENT_4.TIME_STAMP AS Heating_Complete, EVENT_5.TIME_STAMP AS Split_Temp_Met, EVENT_6.TIME_STAMP AS End_N2_Final_Purge, EVENT_7.TIME_STAMP AS Inner_Cover_Removed, EVENT_8.TIME_STAMP AS Cycle_Complete, EVENT_9.TIME_STAMP AS Post_Purge_Time_Met
FROM dbo.CHARGE_INFO CHARGE_INFO LEFT OUTER JOIN #EVENT_FILTERED EVENT_9 ON CHARGE_INFO.N_CHARGE = EVENT_9.N_CHARGE LEFT OUTER JOIN #EVENT_FILTERED EVENT_7 ON CHARGE_INFO.N_CHARGE = EVENT_7.N_CHARGE LEFT OUTER JOIN #EVENT_FILTERED EVENT_6 ON CHARGE_INFO.N_CHARGE = EVENT_6.N_CHARGE LEFT OUTER JOIN #EVENT_FILTERED EVENT_8 ON CHARGE_INFO.N_CHARGE = EVENT_8.N_CHARGE LEFT OUTER JOIN #EVENT_FILTERED EVENT_5 ON CHARGE_INFO.N_CHARGE = EVENT_5.N_CHARGE LEFT OUTER JOIN #EVENT_FILTERED EVENT_3 ON CHARGE_INFO.N_CHARGE = EVENT_3.N_CHARGE LEFT OUTER JOIN #EVENT_FILTERED EVENT_1 ON CHARGE_INFO.N_CHARGE = EVENT_1.N_CHARGE LEFT OUTER JOIN #EVENT_FILTERED EVENT_4 ON CHARGE_INFO.N_CHARGE = EVENT_4.N_CHARGE LEFT OUTER JOIN #EVENT_FILTERED EVENT_2 ON CHARGE_INFO.N_CHARGE = EVENT_2.N_CHARGE LEFT OUTER JOIN #EVENT_FILTERED EVENT ON CHARGE_INFO.N_CHARGE = EVENT.N_CHARGE
WHERE (EVENT.MESSAGE LIKE '%Inner Cover Set%') AND (EVENT_1.MESSAGE LIKE '%Cycle Started%') AND (EVENT_2.MESSAGE LIKE '%Leak Test Done%') AND (EVENT_3.MESSAGE LIKE '%End of N2 PrePurge%') AND (EVENT_4.MESSAGE LIKE '%Heating Complete%') AND (EVENT_5.MESSAGE LIKE '%Split Temp Met%') AND (EVENT_6.MESSAGE LIKE '%End N2 Final%') AND (EVENT_7.MESSAGE LIKE '%Inner Cover Removed%') AND (EVENT_8.MESSAGE LIKE '%Cycle Complete%') AND (EVENT_9.MESSAGE LIKE '%Post Purge Time Met%') AND (CHARGE_INFO.TIME_STAMP >= @StartDate) AND (CHARGE_INFO.TIME_STAMP < @EndDate)
ORDER BY CHARGE_INFO.BASE, CHARGE_INFO.TIME_STAMP DESC
I've been tearing my hair out over this UDF. The code works within astored procedure and also run ad-hoc against the database, but does notrun properly within my UDF. We've been using the SP, but I do need aUDF instead now.All users, including branch office, sub-companies and companies and soon up the lines are in the same table. I need a function which returnsa row for each level, eventually getting to the master company all theway at the top, but this UDF acts as though it can't enter the loop andonly inserts the @userID and @branchID rows. I have played with theWHILE condition to no avail.Any ideas on what I am missing?(Running against SQL Server 2000)---------------------------------------------------ALTER FUNCTION udfUplineGetCompany (@userID int)RETURNS @upline table (companyID int, companyname varchar(100), infovarchar(100))ASBEGINDECLARE @branchID intDECLARE @companyID intDECLARE @tempID int--Insert the original user dataINSERT INTO @uplineSELECT tblusersid, companyname, 'userID'FROM tblusersWHERE tblusersid = @useridSELECT @branchID = tblUsers.tblUsersIDFROM tblUsersINNER JOIN tblUsersUsersLnkON tblUsers.tblUsersID = tblUsersUsersLnk.tblUsersID_ParentWHERE tblUsersUsersLnk.tblUsersID_Child = @userid--Up one levelINSERT INTO @uplineSELECT tblusersid, companyname, 'branchID'FROM tblusersWHERE tblusersid = @branchidSET @tempID = @branchIDWHILE @@ROWCOUNT <> 0BEGINSELECT @companyID = tblUsers.tblUsersIDFROM tblUsersINNER JOIN tblUsersUsersLnkON tblUsers.tblUsersID = tblUsersUsersLnk.tblUsersID_ParentWHERE tblUsersUsersLnk.tblUsersID_Child = @tempIDAND tblUsersId <> 6--Insert a row for each level upINSERT INTO @uplineSELECT tblusersid, companyname, 'companyID'FROM tblusersWHERE tblusersid = @companyIDSET @tempID = @companyIDENDRETURNEND
This is probably very elementary to someone more experienced, but I'm having a hard time coming up with a way to do the following:
SELECT * FROM Transactions WHERE MyField LIKE (SELECT MyValues FROM SearchValues)
But I can't because the subquery will return multiple rows. That's all I'm really trying to do - search all the rows in Transactions.MyField for any of the search values returned from the subquery.
And I can't restructure the SearchValues table to combine them into a single-row comma-delimited field or anything like that.
If stored procedure A EXECUTEs stored procedure B, and stored procedure B does a select statement which returns a dataset, how does stored procedure A access that data ?
Lately I noticed that I cannot return any rows using enterprise manager. The version I am using is MS-SQL 7.0 with SP 1 on NT. This will return an error:-
An unexpected error happened during this operatation. [Query]-Query Designer encountered a query error. Unspecified error.
I can return rows using another computer with enterprise manager installed.
I am trying to run queries on a table (table has zero rows). Inspite of giving 0 rows returned the query keeps on running and I have to cancel it. I tried inserting a dummy row into the table but even the insert operation is taking too long.Every query which I hit on the table just keeps on running without giving any result.
But this is not the case with other tables in the database.They are all running fine giving proper results. But this one table is behaving funny.
Hi,I have the following stored procedure that does some processing andputs the result in a temporary table. I tried several things thatprocedure to display output that I can access with ADO.Net, but itdoesn't work. It doesn't even display the result in the query analyzerunless I add SELECT @ReturnFullNameAny help?The stored procedure:CREATE PROCEDURE sp_SEARCH_MULTIPLE_NAMES @search4fatherOfvarchar(255), @maximum_fathers int = 100, @ReturnFullName varchar(255)Output....SELECT @ReturnFullName = name FROM #FULLNAME------------------------------------------------To Execute the stored procedure:DECLARE @test varchar(255)EXEC sp_SEARCH_MULTIPLE_NAMES @search4fatherof='مريم',@returnfullname=@testPRINT CONVERT(varchar(255), @test)
Hi,In the process of localizing the 'regions' table, we added three newtables. The localized data will be stored in the TokenKeys andTokenValues tables. It would be easier if we did away with theTokeyKeys/TokenValues tables and just added a localeid in the regionstable, but this is the desired schema by the client. Here's theschema:Table: regionsid nameabbreviation1 United StatesUSTable: localesid locale1 en_US2 fr_CATable: TokenKeysid key1 db.regions.name2 db.regions.abbreviationTable: TokenValuesid keyid valuelocaleid1 1 Etas Unis22 2 EU2The old sql was simply this:select name, abbreviation from regionswhich returns:United States, USBut the new sql needs to link in the localized data from the tokeykeysand tokenvalues tables using the localeid... I’m trying to figure outwhat the sql statement would look like to return this:Etats Unis, EU (This is supposed to be the French version)My confusion is we are trying to return multiple column values fromthe same column (TokenValues.value) and make them act as separatecolumns in the same record, like it was with the original.Thanks
I have a stored procedure which return a single value and one which return multiple rows between two colums. In my code for the procedure which returns a single value i use (executescalar) which works fine. I am not sure what command to use in my code when i am calling the stored procedure that returns multiple rows between colums. Any help would be appreciated. Thanks.
I wish to call a custom code function from a table control that would return rows of data to be displayed in the table. Is this possible?
Specifically, I'd like to pass a large text string to the function, have the function break the string into smaller strings, and then have the smaller strings displayed in the table. The number of lines returned may vary, depending on the original string passed in.
I have an external process that polls message rows from a table. apon taking them, it also needs to mark them as taken. there is a status column in the table. marking messages as taken will change the value of status.
How can i perform both these operations in one command? Select the top x rows where their status is equal 1, then update the status of those same rows to a value of 5 for example.
I could iterate through the result of hte intial select and change the status 1 by 1 using a cursor, but this seems like a slow option.
I have had at least 2 occurences of a DFT OLEDB source returning the correct number of rows but all rows are empty (they contain zeros or ''). This has happened in two different places in different SSIS packages within an ETL task. In one case the source was on a different server running SQLServer and in the other it was a different database on the same server as the SSIS package. This occured running on different servers, one with SQL 2005 SP2 and the other without. Both are 64 bit AMD systems running 64 bit SQL 2005. As there is a derived column transformation that has performed the derivation on the blank columns I assume the problem is with the OLEDB source but it could be with the data stream from the OLEDB source to to whatever follows. Has anyone else had this problem and does anyone know of a fix?