I was thinking of using a CTE to return history, have you ever done this?
I know this can be done in other ways but I wanted to wrap this CTE in a view so people could join to it.
Here is an example table:
KEY ASOFDATE
A 1/1/2007
A 3/1/2007
B 1/1/2007
B 2/5/2007
B 3/6/2007
I want the results to look like:
KEY START END
A 1/1/2007 3/1/2007
A 3/1/2007 NULL
B 1/1/2007 2/5/2007
B 2/5/2007 3/6/2007
B 3/6/2007 NULL
This would be even better:
ID KEY ASOFDATE
1 A 1/1/2007
2 A 3/1/2007
3 B 1/1/2007
4 B 2/5/2007
5 B 3/6/2007
I want the results to look like:
ID KEY START END
1 A 1/1/2007 3/1/2007
2 A 3/1/2007 NULL
3 B 1/1/2007 2/5/2007
4 B 2/5/2007 3/6/2007
5 B 3/6/2007 NULL
Books online mention the existence of sample code for several custom tasks, including the one mentioned in the title. But, when I try to find this code in the location mentioned it is nowhere to be found.
I have run a search on the rest of my drive and come up empty.
Does anyone have any sample code for using ntext data type, when inserting or reading big block of texts? How can you read a big huge block of text back into c#?
This is a sample code from an MSDN help site. I copied it and pasted into an open new query. I tried to execute it and got two errors:
USE AdventureWorks; GO DECLARE @tablename sysname SET @tablename = N'Person.AddressType' table_loop: IF (@@FETCH_STATUS <> -2) BEGIN SELECT @tablename = RTRIM(UPPER(@tablename)) EXEC ('SELECT ''' + @tablename + ''' = COUNT(*) FROM ' + @tablename ) PRINT ' ' END FETCH NEXT FROM tnames_cursor INTO @tablename IF (@@FETCH_STATUS <> -1) GOTO table_loop GO
The errors are:
Msg 208, Level 16, State 1, Line 1 Invalid object name 'PERSON.ADDRESSTYPE'.
Msg 16916, Level 16, State 1, Line 9 A cursor with the name 'tnames_cursor' does not exist
The database is connected. Table Person.AddressType is a part of it.
hi, i learn by practice and i would like an asp code using c# that connects to sql database can retrives information from a table. can you please copy paste me some code or give me exact link a?
If somebody have worked on providing the code for the selection of delivery method and delivery schedule page in aspx . Then please share it with me. I am working on creating a Subscription page using aspx rather then reporting services page.
Please let me know if there are any controls provided by reporting services or some 3rd party for this.
I have a package that I have been attempting to return a error code after the stored procedure executes, otherwise the package works great.
I call the stored procedure from a Execute SQL Task (execute Marketing_extract_history_load_test ?, ? OUTPUT) The sql task rowset is set to NONE. It is a OLEB connection.
I have two parameters mapped:
tablename input varchar 0 (this variable is set earlier in a foreach loop) ADO. returnvalue output long 1
I set the breakpoint and see the values change, but I have a OnFailure conditon set if it returns a failure. The failure is ignored and the package completes. No quite what I wanted.
The first part of the sp is below and I set the value @i and return.
Why is it not capturing and setting the error and execute my OnFailure code? I have tried setting one of my parameter mappings to returnvalue with no success.
Does anyone know where to find a simple sample showing you how to use asp.net 2.0 with the login control to direct you to another aspx page using SQL 2000 as the database on a hosted server? I have been beating my head against the wall trying to figure this out and would be very appreciative if anyone knows a place out there that shows this? I can find many sites that show you how to do this with SQL 2005, but not SQL 2000. Thanks - Chris
I am trying to write a ssis surrogate key data transform, my problem is I can't find an example how to add a column to the incoming columns and add some data to it. If anyone has a sample, can you please post it. I found a script option that works but I would like an actual transform.
I am looking for some examples of code for an appliction which will let run RS reports from a custom application. The user will choose the reports from a menu structure
We have an exiting application which we use to run crystal reports which we are going to be migrating to RS. We use custom web controls to capture the required parameter values for the reports, and hope to reuse the same controls (with minimal rework) to capture the parameters values for the RS reports.
What I am after is some code which will
a) let me run the report and display it. I don't want to display the report parameters in the URL etc as we generate some report parameter values based upon the user's ID and do NOT want them displayed back to the users
b) export the report to PDF, CSV etc depending upon the option chosen by the user.
The application will be developed in VS 2005 - VB. Does any one know of some links to some sample code
Hi, there; I try to import data from ODBC using C# programmatically. Is there any sample code we can have a look. Like how to read schema from ODBC source table and then create source column...
I have been developing VBA apps in Access and Excel for sometime and am fairly proficient in VBA. Now we are moving all of our data to SQL Server 2005. I am in need of learning how to write code for the Script Component of a data flow task. And so I have a couple of questions.
First, are there any books you recommend for learning ???? (I'm not even sure what I need to learn: .NET? ADO.NET?)
And as a follow-up, any good websites that provide good reference documentation?
And my second question is more specific to my current problem. If I had a bit of code to get me started, I'm sure I could scream all the way to the bottom of the hill.
Data source is coming from a sort task where the data is sorted by STATUS and then MOD_DATE and the AUDIT_ID. I need to read each row and compare it to the next row. If STATUS is the same, discard the second row. When STATUS is different, send the first row to the output (to be used by the next task in the data flow). Using the "different" row from step 3, go to step 2.
I know how to write if statements, case statements, for/next statements. I'm just not understanding how to read the rows in and then send them back out. I've been searching for some sample code but everything I find tends to be solving much bigger issues.
Any help you can provide would be much appreciated.
I installed a new sql 2005 server and set 4 publications with subscription.
The replication is working but looks slow....It means when I change a value into a table the transaction take more than 30 secondes before it came into the synchronisation status monitor.
I saw that for each subscription my logreader agent could not start ...I have the following message.
Message Executed as user: 280db02280db02sql. The replication agent encountered an error and is set to restart within the job step retry interval. See the previous job step history message or Replication Monitor for more information.
Message -XCMDLINE 0 -XCancelEventHandle 00000000000007B4 -XParentProcessHandle 00000000000003C8 2007-04-20 09:54:32.109 Parameter values obtained from agent profile: -pollinginterval 5000 -historyverboselevel 1 -logintimeout 15 -querytimeout 1800 -readbatchsize 500 -readbatchsize 500000 2007-04-20 09:54:32.109 Status: 4096, code: 20024, text: 'Initializing'. 2007-04-20 09:54:32.109 The agent is running. Use Replication Monitor to view the details of this agent session. 2007-04-20 09:54:32.125 Status: 16384, code: 20007, text: 'No replicated transactions are available.'. 2007-04-20 09:55:37.125 Status: 16384, code: 20007, text: 'No replicated transactions are available.'. 2007-04-20 09:55:37.125 Status: 0, code: 22037, text: 'The last step did not log any message!'. 2007-04-20 09:55:37.125 The last step did not log any message! 2007-04-20 09:55:37.125 Status: 0, code: 1007, text: 'Procedure or function sp_MSadd_logreader_history has too many arguments specified.'.
did some one have an idea of what is the reason ?
FYI.
Server is a HP 2CPU 4Gigs of ram under win2003 sp2 and sql2005 standard SP2 64 bits
In the "Example: Detecting a Poison Message" section, it reads: This Transact-SQL example shows a simple, stateless service that includes logic for handling poison messages. Before the stored procedure receives a message, the procedure saves the transaction. When the procedure cannot process a message, the procedure rolls the transaction back to the save point. The partial rollback returns the message to the queue while continuing to hold a lock on the conversation group for the message.
Is there any sample code to demo the SSB send messages with same sql instance?
my case is very simple:
I want write a stored procedure to send a xml to another database. The stored procedure is called by tables triggers when some data is changed under the specific conditions.
Given the following data how do I make a SQL query that returns only 1 row per product?
The returned rows need consist of only currently active products (that is WHERE (DateEffective <= { fn NOW() }). The twist: Sometimes a product will have duplicate DateEffective records. In that case, only return the record created latest because that's the most current data that exists for a product. RowTimeStap is when the record was created.
Example Data: HistoryID ProductID Name Color DateEffective RowTimeStamp (auto-number PK) 1 1 Wheel Red 2/1/2008 2/1/2008 2 1 Wheel Blue 3/5/2008 3/1/2008 3 1 Wheel Orange 3/5/2008 3/2/2008 4 1 Wheel Black 1/1/2010 3/3/2008 5 2 Knob Blue 3/2/2008 3/2/2008 6 2 Knob Green 3/3/2008 3/3/2008
Query should return: 3 1 Wheel Orange 3/5/2008 3/2/2008 5 2 Knob Green 3/3/2008 3/3/2008
The query I've created fails on the twist part. I have to allow duplicate DateEffective to keep a history of changes. Can anyone help?
How to upload excel data to sql server 2000 thorugh .net application. I want like one upload button should be there,we have to browese corresponding excel file and then we need to upload to database. before uploading it has to ask appending or replace everything in table. both options appending and replacing shoulb be there. How to upload ? any sample code is there plz give me.
After much work and thanks to all of you who helped on this here is a code sample that can be adapted. From the dataflow task add an OLEDB source component, a row count component and finally a Script Destination Component.
On the Script Destination Component rename the Input node of the imports and outputs tree view to "ParsedInput"
The readonly User: variables that start with gs can be read in the PreExecute method
The readwrite User: variable giSuccessCount can only be used in the post execute task because it is populated by the Row Count Component which is the previous object in the Dataflow
The xml code is adapted from an idea in Donald Farmers book
enjoy
Dave
Now if someone can make a Script Source Component that can read a file with a header , data body and trailer that would b egreat! ' Microsoft SQL Server Integration Services user script component ' This is your new script component in Microsoft Visual Basic .NET ' ScriptMain is the entrypoint class for script components
Public Class ScriptMain Inherits UserComponent Dim sw As StreamWriter 'In addition to using the Imports System.Xml statement a reference must be added to the 'System.Xml assembly (Select Project-Add Reference from IDE) Dim xWriter As XmlTextWriter Dim OutputFileType As String '.csv or .xml
Public Overrides Sub PreExecute()
'Read Only variables Dim gsPickUp As String = Me.Variables.gsPickUp 'D:ftprootOutAvid' Dim gsPickUpFilename As String = Me.Variables.gsPickUpFilename '1_AVID_' Dim gsPickUpFileExtn As String = Me.Variables.gsPickUpFileExtn '.csv' Dim gsMemoText As String = Me.Variables.gsMemoText 'Memo Text : credit adjustment' Dim gsStatementText As String = Me.Variables.gsStatementText 'Statment Text : credit adjustment' Dim gsRunMode As String = Me.Variables.gsRunMode 'UPDATE' Dim fileName As String = gsPickUp & "" & gsPickUpFilename fileName = fileName & (Format(Now(), "yyMMdd").ToString) 'MsgBox(fileName)
OutputFileType = gsPickUpFileExtn If OutputFileType = ".csv" Then fileName = fileName & gsPickUpFileExtn sw = New StreamWriter(fileName) 'connection to dest file
'Header records sw.Write(gsRunMode) sw.Write(Environment.NewLine) ' end of line sw.Write(gsMemoText) sw.Write(Environment.NewLine) sw.Write(gsStatementText) sw.Write(Environment.NewLine) sw.Write(Environment.NewLine) 'Spacer End If
If OutputFileType = ".xml" Then fileName = fileName & gsPickUpFileExtn 'xWriter = New XmlTextWriter(Me.Connections.XMLConnection.ConnectionString, Nothing) 'xWriter.WriteStartDocument() 'xWriter.WriteComment("Customer file parsed using script") 'xWriter.WriteStartElement("x", "customer", "http://some.org/name") 'xWriter.WriteAttributeString("FileName", Me.Connections.XMLConnection.ConnectionString) xWriter = New XmlTextWriter(fileName, Nothing) xWriter.WriteStartDocument() xWriter.WriteComment("Customer file parsed using script") xWriter.WriteStartElement("x", "customer", "http://some.org/name") xWriter.WriteAttributeString("FileName", fileName) End If
End Sub
Public Overrides Sub ParsedInput_ProcessInputRow(ByVal Row As ParsedInputBuffer)
If OutputFileType = ".csv" Then Dim delim As String = ","
If OutputFileType = ".csv" Then 'Create the trailer sw.Write(Environment.NewLine) ' blank line sw.Write("RECORD_COUNT: " & Me.Variables.giSuccessCount.ToString) 'ReadWrite Varible sw.Write(Environment.NewLine) sw.Flush() 'send the stream to file 'Close file sw.Close() End If
If OutputFileType = ".xml" Then xWriter.WriteStartElement("RecordCount") xWriter.WriteString(Me.Variables.giSuccessCount.ToString) xWriter.WriteEndElement() xWriter.WriteEndElement()
Does anybody have a working Java code sample that connects to an SQLServer 2005 database on a remote host, via the default named pipe, from a client using the SQLServer 2005 JDBC driver? Could you post it, or a pointer to it?
I've gotten java.sql DriverManager.getConnection() to work fine with TCP/IP connections before. But I'm a newbie with named pipes, and unclear on how the connection string/properties are different. I've tried to piece it together from multiple docs and threads, but haven't found sample code that quite fits my situation. I think a simple working example would best clarify the syntax.
The server is not using SQL Express. Most SQLServer configuration options are defaults; the named pipes protocol is enabled.
Hi I have a maintenance proceudre that trims data tables on the live site, but prior to trimming i need to execute procs via linked servers at four seprate locations (subscribers) to ensure all of their data is up to date before the trim happens (the deletes are blocked at the subs). my problem is that if i go exec @error_id = procname and an error such as permission being denied or proc missing, the return code is still 0 - therefor it looks like th proc ran successfully, meanwhile it didnt. does anyone know how to check for these sorts of erors, so i dont leave this to chance?
xp_startmail is documented as returning two code: 0 success, 1 failure.It would be nice to check the return code.When mail has already been started it returns a 1. This case appearsindistinguishable from a failure to start. (MSFT should have providedseparate return codes for failure-already started, and failure-cannotstart, but they did not.)Does anyone know how to distinguish these cases. Text is printedindicating that it has already been started but I don't know how toprogrammatically grab this case.It seems the conventional workaround is not to check the return code atall (or to start mail once at server startup and never start it again).Neither is ideal.Suggestions?Thanks,Mark Andersen
Can anyone help me with below error code i am in getting while runninga C program using sqls.Password: passwordSQL Return Code: -25001BATCH_LOG_PATH:Thank you,D
Hi. We're experiencing the exact same problem described in this post: http://tinyurl.com/ynqv7o We've tried the 11 steps described there and it didn't fix the problem -- just as it didn't fix it for that person.
I've created a stored procedure which inserts values into a table and upon successful execution the RC column gets returned along with the Identity value (I'm using SELECT SCOPE_IDENTITY()), but I don't want to RC column, I only want to get the Id number of the current row. I'm doing this using the Query Analyzer.
Is there a way to suppress the RC column? I have run the same query on different servers and the RC col doesn't show up. I only want the ID value to put into an ASP.NET page.
While testing different query methods from ASP.NET (Output parameter, Return_Value, etc) did I set a flag within SQL Server?
i have sql statement in ExecuteSQLTask, connecting to AdventureWorks
BEGIN TRY insert into person.contacts(contactid) values (1); END TRY BEGIN CATCH SELECT ERROR_NUMBER() AS ErrorNumber; END CATCH;
i purposely put a table that does not exist in the database to catch the error. however, the error number that is 208 won't appear, instead the whole error message will be displayed.
if i change the query in try block to insert into person.contact(contactid) values (1);
it'll return the error number 544, which is correct.
Im trying to use VB.net 2005 to write a sample app to access a DB. Are there any samples for this and any samples of how I go about making the DB in the first place?
Hi all, if have problem to display error message in vb.net that comes from a stored procedure. IF ...... then msgbox "ERROR at Update" returnvalue = SUCCES ELSE Msgbox "Successfull Updated" returnvalue = ERROREND IFHow can I return values from Stored Procedure to VB.NET and then give to User a Message that the Update was successful or not.Thanks to all
Does anyone know how to call a SQL store procedure that return a value to the page? I've a simple data entry aspx page with several textboxes and a save button. When user fill out the form and click save/submit, it calls a store procedure to insert a row into a SQL table and automatically generate an ID that need to return the the page to display for the user. Are there a similar article somewhere?
I used a function to create dataset as below: Public Function GetSQLDataSet(ByVal SQL As String) As DataSet ...... MyConnection = New SqlConnection(MyConnectionString) MyCommand = New SqlCommand(SQL, MyConnection) MyDataSet = New DataSet MySQLDataAdapter = New SqlDataAdapter(MyCommand) MySQLDataAdapter.Fill(MyDataSet) ...... End function It works fine. How to code a function to return a dataset in which there are two tables and relationship?
My stored procedure works and codes is working except I need to capture the return value from the stored procedure and use that value in my code behind page to indicate that a duplicate record entry was attempted. In my code behind file (VB) how would I capture the value "@myERROR" then display in the label I have that a duplicate entry was attempted. Stored ProcedureCREATE PROCEDURE dbo.usp_InsertNew @IDNumber nvarchar(25), @ID nvarchar(50), @LName varchar(50), @FName varchar(50) AS DECLARE @myERROR int -- local @@ERROR , @myRowCount int --local @@rowcountBEGIN -- See if a contact with the same name and zip code exists IF EXISTS (Select * FROM Info WHERE ID = @ID) BEGIN RETURN 1END ELSEBEGIN TRAN INSERT INTO Info(IDNumber, ID, LName, FName) VALUES (@IDNumber, @ID, @LName, @FName) SELECT @myERROR = @@ERROR, @myRowCount = @@ROWCOUNT If @myERROR !=0 GOTO HANDLE_ERROR COMMIT TRAN RETURN 0 HANDLE_ERROR: ROLLBACK TRAN RETURN @myERROR ENDGO asp.net page<asp:SqlDataSource ID="ContactDetailDS" runat="server" ConnectionString="<%$ ConnectionStrings:EssPerLisCS %>" SelectCommand="SELECT * FROM TABLE_One" UpdateCommand="UPDATE TABLE_One WHERE ID = @ID" InsertCommand="usp_InsertNew" InsertCommandType="StoredProcedure"> <SelectParameters> <asp:ControlParameter ControlID="GridView1" Name="ID" PropertyName="SelectedValue" /> </SelectParameters> </asp:SqlDataSource>
