This may be a loaded question, but I was wondering if anyone could outline the main options we have available to us for handling data access with ASP.net 2.0 / SQL Server 2005.
I know at one extreme you can simply use the Data Controls like GridView, FormView, and DetailsView, specifying the necessary SQL on a page-by-page basis. At the other extreme, my understanding is that you can basically set up a SQLConnection object, explicitly specify your SQL (or call a stored procedure) and then execute these items from within the code.
Can anyone either outline the main options or point me to a resource that better explains this? Thanks!
I am using SP as my data set to generate report and there are some multi-value parameters which past as an array back to sql server. Since sql server can not handle the array pass in , how to handle it?
I am having a requirement where I need to load the correct data into the target table and needs to save the bad data for analysis, how can I do that in SSIS.
My source data is from a audit_trail(log) table. Because of the characteristic of audit table, more than one record of the data belongs to a record in another table (TableA). I am wondering what the most efficient way is to handle (Insert or update TableA based on data in audit_trail). Thanks.
I am trying to insert a large amount of data from a web form I created into a "text" field in my DB. Problem is its oly entering the 1st 16 characters. I can see why but I dont understand what I need to do to avoid this shortcomming. Ive searched the forums here and in google groups to no avail :( <code> sqlCommand.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Content", System.Data.SqlDbType.Text, 16, "Content")).Value = ftbContent.Text </code> How do I need to restructure the above line? Any help would be appreciated. TIA,Stue
I'm working on the viability of using SSIS to process data files that are (currently) sent by email or FTP and processed by hand. They are .csv or Excel and have some columns that are required, some that are included but the data is discarded, usually have some anomaly (like columns out of place, or columns missing) from source to source, and if they have headers the actual headers are inconsistent from source to source (and sometimes from submission to sumission ). Some sources send only one file, others might send a dozen. Due to the nature of the sources - many are not very technically inclined and sometimes there are other factors involved in how the data is exported - there's not a lot I can do to leverage them into all using the exact same format. Creating an SSIS package for each source (over 100 in all) is not a viable solution.
The compromise that I've come up with is I would like to come up with a universal SSIS package that can:
Take a .csv file with headers (I think we can force the sources to include the right headers if nothing else) as input , with or without quote delimiters. Not be sensitive to the order of the columns. Not be sensitive to the number of columns. Loop through multiple files from one source. Place the ouput in a staging table for further processing. Reading a .csv file, looping and the staging table is not a problem. What I'm struggling with is the middle portion. I've tried to make the column headers dynamic, but then I lose the ability to have quote delimiters. My next instinct was to the source file(s) and create one interim file with a different delimiter (such as pipe or ## or something) but that doesn't seem to work quite the way I want it - since the source has comma delimiters, the output has both the pipe and the comma. Another way I could do it is replace the commas within quote delimiters with a blank space in the data flow, which would eliminate the quote delimiters in the output. That I haven't tried yet.
Any suggestions? If I could bring down the hammer and tell every source "You must send it this way" I would, but my hands are tied.
basically, is it inefficient to open and close a data connection everytime data needs to be retrieved or is there a way for a user to use the same conenction over multiple pages orfor multiple users to share one global data connection I just wanted to know this before I built a site where data could be constantly being pulled or put into the database.It would be easier to just keep opening and closing the connection since it just means pasting in the small chunk of code I use to do that where I need it. I hear speak of connections sometimes not closing etc and wonder if that is an issue then if you are opening and closing too many of them. I am using SqlConnection SqlCommand and SqlDatareader , my code works, just wondering if my approach lacked scalability before I find out too late and have to rewrite everything . Jim
i have created asp.net page, one feild of this page text area. when i insert some text through this page in "text feild" of SQL server on few words of this feild cut and inserted to text feild of SQL server but all text that i have written in text area feild.
can u please help me how to handle so that i can all text in text feild of SQL server data type text/next.
I have a checkbox on my webform that is bound to a bit field in my SQL table. I'm fine as long as I've got the bit field set to 0 or 1, but if the field is NULL, the checkbox throws an exception during the databind.
Is there any way to handle this without removing the data binding and manually setting the value (ie: some way to intercept it before the exception gets thrown and then setting the field value in the dataset)?
I need help about this , What i've done so far is a data processing extension that gets its data from a WS , I use it as a datasource for a report , That report will be displayed withing an ASPX page containing a reportviewer control that displays that report , The problem is catching errors that occurs withing the data processing extension .. ,
When i debugged the extension i saw that after throwing an exception the code ends up inside the "cancel()" event inside the IDBcommand implementation...
In all examples for a data processing extension i could find online they always have a comment saying the cancel is not implemented and throw a NewUnsupportedExtension()..
What happens is i get some uninformative , non-userfriendly error from the reporting services , something like "an error occurred during the report processing(rssomehing)"
How should i go about handling those exception that happened inside the data processing extension?
I've tried catching the error inside the event of the reportviewercontrol - reporterror but it seems that event don't fire when those errors are generated..
Any help on how to approach this would be highly welcome
I have a very simple SSIS task whcih opens a flat file data source, checks the first two characters of each row and if it is equal to "06", inserts that row into an OLDB destination table. However it is inserting EVERY row, even the ones not equal to "06" in first two columns. So, how do I get it to NOT insert the rows I don't want? Any help is very much appreciated and the following is my code:
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
Here is my idea but I am looking for the best practice.
Each record can have 3 possibilites.
I would read and write the data 3 times to different tables and add an identity key on all 3 files then I would reassembe the data back together on the identity key and map the data to a fourth and final table.
I'm trying to re-write my database to de-couple the interface (MS Access) from the SQL Backend. As a result, I'm going to write a number of Stored Procedures to replace the MS Access code. My first attempt worked on a small sample, however, trying to move this on to a real table hasn't worked (I've amended the SP and code to try and get it to work on 2 fields, rather than the full 20 plus).It works in SQL Management console (supply a Client ID, it returns all the client details), but does not return anything (recordset closed) when trying to access via VBA code.The Stored procedure is:-
USE [VMSProd] GO /****** Object: StoredProcedure [Clients].[vms_Get_Specified_Client] Script Date: 22/09/2015 16:29:59 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON
I'm using service broker and keep getting errors in the log even though everythig is working as expected
SQL Server 2005 Two databases Two end points - 1 in each database Two stored procedures: SP1 is activated when a message enters the sending queue. it insert a new row in a table SP2 is activated when a response is sent from the receiving queue. it cleans up the sending queue.
I have a table with an update trigger In that trigger, if the updted row meets a certain condition a dialogue is created and a message is sent to the sending queue. I know that SP1 and SP2 are behaving properly because i get the expected result. Sp1 is inserteding the expected data in the table SP2 is cleaning up the sending queue.
In the Sql Server log however i'm getting errors on both of the stored procs. error #1 The activated proc <SP 1 Name> running on queue Applications.dbo.ffreceiverQueue output the following: 'The conversation handle is missing. Specify a conversation handle.'
error #2 The activated proc <SP 2 Name> running on queue ADAPT_APP.dbo.ffsenderQueue output the following: 'The conversation handle is missing. Specify a conversation handle.'
I would appreceiate anybody's help into why i'm getting this. have i set up the stored procs in correctly?
i can provide code of the stored procs if that helps.
I have a client who has SSMS installed on her laptop. She is able to connect to the SQL server via SSMS in the office and query data on the server.
She needs to be out of site often and doesn't have internet access. She asks if the data tables can be "backed up" or saved on her laptop, so she can look at them without worrying connecting to the server. I am not sure if this can be achieved, as SSMS is built for accessing a server, not a desktop. Myself never have this need. If I really need it, I would go to Microsoft Access and create an ODBC connection to the datatables. But this client thinks that Microsoft Access is beneath her.
We have implemented our service broker architecture using conversation handle reuse per MS/Remus's recommendations. We have all of the sudden started receiving the conversation handle not found errors in the sql log every hour or so (which makes perfect sense considering the dialog timer is set for 1 hour). My question is...is this expected behavior when you have employed conversation recycling? Should you expect to see these messages pop up every hour, but the logic in the queuing proc says to retry after deleting from your conversation handle table so the messages is enqueued as expected?
Second question...i think i know why we were not receiving these errors before and wanted to confirm this theory as well. In the queuing proc I was not initializing the variable @Counter to 0 so when it came down to the retry logic it could not add 1 to null so was never entering that part of the code...I am guessing with this set up it would actually output the error to the application calling the queueing proc and NOT into the SQL error logs...is this a correct assumption?
I have attached an example of one of the queuing procs below:
Code Block DECLARE @conversationHandle UNIQUEIDENTIFIER, @err int, @counter int, @DialogTimeOut int, @Message nvarchar(max), @SendType int, @ConversationID uniqueidentifier select @Counter = 0 -- THIS PART VERY IMPORTANT LOL :) select @DialogTimeOut = Value from dbo.tConfiguration with (nolock) where keyvalue = 'ConversationEndpoints' and subvalue = 'DeleteAfterSec' WHILE (1=1) BEGIN -- Lookup the current SPIDs handle SELECT @conversationHandle = [handle] FROM tConversationSPID with (nolock) WHERE spid = @@SPID and messagetype = 'TestQueueMsg'; IF @conversationHandle IS NULL BEGIN BEGIN DIALOG CONVERSATION @conversationHandle FROM SERVICE [InitiatorQueue_SER] TO SERVICE 'ReceiveTestQueue_SER' ON CONTRACT [TestQueueMsg_CON] WITH ENCRYPTION = OFF; BEGIN CONVERSATION TIMER ( @conversationHandle ) TIMEOUT = @DialogTimeOut -- insert the conversation in the association table INSERT INTO tConversationSPID ([spid], MessageType,[handle]) VALUES (@@SPID, 'TestQueueMsg', @conversationHandle);
SEND ON CONVERSATION @conversationHandle MESSAGE TYPE [TestQueueMsg] (@Message)
END ELSE IF @conversationHandle IS NOT NULL BEGIN SEND ON CONVERSATION @conversationHandle MESSAGE TYPE [TestQueueMsg] (@Message) END SELECT @err = @@ERROR; -- if succeeded, exit the loop now IF (@err = 0) BREAK; SELECT @counter = @counter + 1; IF @counter > 10 BEGIN -- Refer to http://msdn2.microsoft.com/en-us/library/ms164086.aspx for severity levels EXEC spLogMessageQueue 20002, 8, 'Failed to SEND on a conversation for more than 10 times. Error %i.' BREAK; END -- We tried on the said conversation, but failed -- remove the record from the association table, then -- let the loop try again DELETE FROM tConversationSPID WHERE [spid] = @@SPID; SELECT @conversationHandle = NULL; END;
Dear All! My package has a Data Flow Task. In Data Flow Task, I use a Script Component and a OLE BD Destination to transform data from txt file to database. Within Data Flow Task, I want to call File System Task to move file to a folder or any Task of "Control Flow" Tab. So, Does SSIS support this task? Please show me if any Thanks
I have recently upgraded to SQL2014 on Win2012. The Access front end program works fine.
But, previously created Excel reports with built in MS Queries now fail with the above error for users with MS 2013. The queries still work for users still using MS 2007.
I also cannot create any new queries and get the same error message. If I log on as myself on the domain to another PC with 2007 installed it works fine, so I don't think it is anything to do with AD groups or permissions.
We need to insert data/rows from a SQL Server 2014 database into MS Access database. The problem is, there are so many columns (100+) in the table and there are so many insert transactions of this kind (from different tables) that it is not very easy to write the code in VB.NET that lists all column names.
Both the Access and SQL Server tables have the same number of columns and the equivalent data types, so inserting is not really the problem. It's just that is there a way to do an insert statement in T-SQL that does not name all the columns?
I've been developing desktop client-server and web apps and have used Access and SQL Server Standard most of the time. I'm looking into using SQL CE, and had a few questions that I can't seem to get a clear picture on:
- The documentation for CE says that it supports 256 simultaneous connections and offers the Isolation levels, Transactions, Locking, etc with a 4GB DB. But most people say that CE is strictly a single-user DB and should not be used as a DB Server. Could CE be extended for use as a multi-user DB Server by creating a custom server such as a .NET Remoting Server hosted through a Windows Service (or any other custom host) on a machine whereby the CE DB would run in-process with this server on the machine which would then be accessed by multiple users from multiple machines?? Clients PCs -> Server PC hosting Remoting Service -> ADO.NET -> SQL CE
- and further more can we use Enterprise Services (Serviced Components) to connect to SQL CE and further extend this model to offer a pure high-quality DB Server? Clients PCs -> Server PC hosting Remoting Service -> Enterprise Services -> ADO.NET -> SQL CE
Seems quite doable to me, but I may be wrong..please let me know either ways
When i am trying to start our hospital software based on SQL server 2000, it shows Following Error.Search Condition is not valid, (DBNETLIB) Connection Open (connect()). SQL server does not exist or excess denied. Due to Fetch data.I run our software in Windows 8.1, while it smothly runs in previous version of Windows XP and 7.
When I execute the below stored procedure I get the error that "Arithmetic overflow error converting expression to data type int".
USE [FileSharing] GO /****** Object: StoredProcedure [dbo].[xlaAFSsp_reports] Script Date: 24.07.2015 17:04:10 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO
[Code] .....
Msg 8115, Level 16, State 2, Procedure xlaAFSsp_reports, Line 25 Arithmetic overflow error converting expression to data type int. The statement has been terminated. (1 row(s) affected)
is there a step by step paper to get there? here is what i need to consider. I Iwill have many customers that will need their own set of records and access pages "branded for their company" each customer will have many clients. I am hosting this application on a windows 2003 server with SQL 2005 server enterprise.
I am using windows authentication, I have created a username in windows, then i added the windows user in SQL management studio in security, granted "DB Read" and "DB write" and again under the database security tab. still from the web authentication fails. i must be nissing a step or two?
I expect to set up a username for each database as i setup new customers.
HiI'm having problems following the tutorial on creating a data access layer - http://www.asp.net/learn/dataaccess/tutorial01cs.aspx?tabid=63 - when I try to compile in Visual Studio 2005 I get namespace could not be found. I followed exactly the tutorial - I created a dataset and added this code in my aspx page. <asp:GridView ID="GridView1" runat="server" CssClass="DataWebControlStyle"> <HeaderStyle CssClass="HeaderStyle" /> <AlternatingRowStyle CssClass="AlternatingRowStyle" />In my C# file I added these lines... using NorthwindTableAdapters; <<<<<this is the problem - where does this come from? protected void Page_Load(object sender, EventArgs e) { ProductsTableAdapter productsAdapter = new ProductsTableAdapter(); GridView1.DataSource = productsAdapter.GetProducts(); GridView1.DataBind(); }Thanks in advance
I'm currently working on a BI architecture for a customer, and consider to propose the Power BI data catalog as a data distribution layer. The customer will use Power BI, but also has other BI tools.
Are data sets in the data catalog available to other clients than Power Query alone? E.g. are there OData feed endpoints available? If not, what would be the best way to give other tools access to the data?
I have two database(MYDB1 , MYDB2) on two different server's(SERVER1 , SERVER2) . I want to create an store procedure in MYDB1 on SERVER1 and get some data from a table of MYDB2 on SERVER2. How can i do this?
I'm using a legacy application built using VB5 and SQL Server 7. After recompiling it, and putting the database in SQL Server 2012. I want to access the current login user using the SQL function SUSER_SNAME().
This is the code.
Set rdoRes = goDatabase.Connection.OpenResultset("select suser_sname()")
But I'm unable to get the current user login in the application. If I write any other SQL statement instead of this, then it runs. But only this statement is not running. Is there any security reasons for this?
I have a table that is returning rows from a table query. It seems I have done it before but I cannot seem to get the right procedure to obtain the values. I will paste in the code below in which you will see my bad attempts at accomplishing what I need.
Dim uid As String Dim pw As String Dim em As String, fn, ln, mi As String Dim par As String Dim Field, n, j As Integer Dim JJ As Integer
how I can load the CSV file data into the sql server table. I know there are ways like bulk insert and other to load the csv file data into the table. But in my case the table doesn't exist and has to be created at the run time. With simple insert in temp table we do like select * into #temp from tablename and that creates the temp table. So. I need something like that which create the temp table and load the data into it. because the CSV file would have different number of columns and names so I can not create the table structure in advance. I have to create the table at run time.
I have an MVC asp.net application that stores many records in a table on sql server, in its own system. used the system for 2 months, worked fine accessing, changing data.
Now that other users are logging in? there is cross coupling going on. one user gets the data from another users sql search.
In the mvc app it had used the get async method to read the ID record from the db, i set that to synchronous. no effect; the user makes their own login id but that does nt matter either.
