I'm trying to resolve an issue that I've run into in my current system.
I have about 10 clients accessing a SQL Server several times per minute (every 10-20 seconds). To have an individual find the next record, I follow the following process:
1. Select the value of the next record in the database to be checked out.
2. Update the record to show that it is checked out to the user.
3. Select the data in the record to display to the user.
3. Update the record to show any changes and to check the record back in after the user edits it.
My issue is that clients can execute at the same time. Right now, with just SQL statements, two clients can get the same value in step #1. That makes them select the same record for editing. Can I use T-SQL to prevent this from happening? If I use a transaction, will the SQL Server 2005 queue up the transactions, or could I still get the same problem of opening up the same record?
Dear All, We have recently moved our NT boxes with SQL Server to a new computer room 10 minutes walk away. Is it possible to examine the NT error logs from a SQL Server client using perhaps an xp command?
I have 6 servers registred on my local computer. I run DTS to transfer users from server SQL1 to SQL2 from my computer. DTS status OK.I am looking in in security my computer for SQL1 and see YES all users transferd but they lost database roles!.
BUT !!! when I login to SQL1 using teminal server I see all new users with correct database roles.
I try to refresh on my computer but it does not help. Did anyone had a same problem?
Hey all,prolly a simple solution, but why isn't the following string working inmy execute sql step within DTS? It produces results, just not the onesI want... What am I doing wrong?select x from new_files where x like '%[^0-9]%' and x like '%[^a-z]%'It's displaying all the records? It should only be displaying thoserecords that do *not* contain letters or numbers.Thanks in advance!-Roy
I am using a filter in a matrix control. I would like to display a "No data" type message if no data is present. The problem I'm running into is the base query is returning data, but the optional filter is removing all rows from display.
So using CountRows in the base dataset doesn't quite cut it. Is there an alternative?
I connect from SQL Server on Windows 2000 to Progress Database on UNIX. The database name of SQL Server is cstarsql and the name is cstarint on UNIX.
I would like to schedule to copy data from cstarint(Progress) to cstarsql(SQLServer). I did for one time, but I want to control if the data has already copied or not. If not, it will copy.
In DTS Query Builder to copy from Progress to SQL Server, SELECT * from calls WHERE call_date = TODAY The name of table of calls is the same for both database.The above calls is cstarint(Progress Database) This command is enough for one time. But I need to control if the record has already copied.
In SQL Server, I control if the record is available or not with @@FETCH_STATUS . Now I would like to mix two queries, but I couldn't. Can anybody do this?
DECLARE calls_cursor SCROLL CURSOR FOR SELECT * FROM calls WHERE call_date = TODAY
OPEN calls_cursor
-- Perform the first fetch. FETCH NEXT FROM calls_cursor
-- Check @@FETCH_STATUS to see if there are any more rows to fetch. WHILE @@FETCH_STATUS = 0 BEGIN -- This is executed as long as the previous fetch succeeds. FETCH NEXT FROM calls_cursor END
I am trying to use €œSQL Server Business Intelligence Development Studio€? (what is the short name for this) to complete a check and then possibly email me depending on what happens.
The check is around two tables that I have. There is a chance that they will become out of sync with each other and so I want to know when a field (ACCOUNTID) has a value that is in one table and not the other. I can do the check but I would like the package to email me when there is a problem (and if possible email me the details) and do nothing when everything is OK.
Can anybody give me some hints to point me in the correct direction as I just seem to be chasing my tail at the moment,
Hi All,Can anyone help with the following problem? I have a database whichcontains a table with a 'text' field, and the text field contains anxml document - typically 50-100K. Now I'd like to make use of SQLServer 2005s XMLData type. To do this I have created a new field ofthe 'xmldata' datatype, and run an SQL statement to update the contentsfrom one field to another - hoping to end up with a complete table ofxml (based on the old text field).The problem I have is after a minute or so, it must come across anbadly-formed xml fragment because I get the following message:Msg 9436, Level 16, State 1, Line 1XML parsing: line 1, character 67640, end tag does not match start tagCan I turn off the checking during the update, or is it not possible toadd badly formed data to the xmldata field. Any help appreciated asthe table runs into tens of thousands of rows, so I can't really checkthe contents of each!Many thanks,Duncan.
I want to do something with error checking in my company. For this we have a selection of different tables and the data needs to meet various validation rules else it is classed as an error.
To deal with this I'm currently thinking of this approach:
1. Create a view pulling all of the various data together from the multiple tables. 2. Create an empty 'errors' data table. 3. Create an Excel file with a button to call a Check for Errors Script
Then in the the script:
1. Clear the 'errors' data table 2. Call multiple scripts, each of which uses the new view, applies the checks for that specific error and writes any erroring data into the 'errors' data table (along with a text string with the unique error code for filtering / sorting purposes). 3. After calling all the scripts, the table can be refreshed in excel when when used with a pivot table can show the various errors, and let us drill down into all the data so we can fix them.
Also.. Ideally, I'd like some way to write comments in an excel column for each entry and error code and be able to write that back into a comment table.
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.
I want to make data changes in read_only database , that's why i must set database read_write. While database is at read_write mode, i want to be sure that no one makes change in database.
For this aim, i write the code below, but i suspect that after setting the database read_write, till the setting database single_user ,is it possible get DML script from another user. Is the code below enough for this operation. Or is there another way?
Reminding: Read_only database can not be set single_user mode. That's why, first you must set database read_write.
The code;
use master alter database xxx set read_write with rollback immediate alter database xxx set single_user with rollback immediate
use xxx update tablexxx set columnxxx=yyy use master alter database xxx set read_only with rollback immediate alter database xxx set multi_user with rollback immediate
Just started looking at SQL Server Replication today. Just need to be shoved in the right direction...I have a 1000 users, and each need to have only their personal data replicated to them....So, is this possible, do I create a 1000 "articles" or do I create one article and somehow pass in a parameter?
We have an existing SSRS server, and have just created a new child domain. We'll be migrating users from the parent to the child, and want to add the users of that new domain with access to SSRS. In the parent domain they are able to access, but after migration with the child domain account, they cannot.
I have added the group CHILDDomain Users with a system user role on SSRS, and PARENTDomain Users was already there.
Is there any additional step I should/could take to get this active?
I have had this issue just pop up. I have local users who can connect fine, but my users that require connection by VPN cannot connect. I get the server not available or access denied error. I did confirm that the VPN'ers are connected to the network correctly and can see that their shares and mappings are correct. Any ideas? Thanking you all in advance!!
I am trying to revert back to Windows 7 after upgrading to Windows 10, however it will not let me and the following message occurs: "Remove new accounts.Before you can go back to a previous version of Windows, you'll need to remove any user accounts you added after the most recent upgrade. The accounts need to be completely removed, including their profiles.You created one account (NT SERVICEMSSQLSERVER) Go to Settings> Accounts> Other users to remove these accounts and then try again".However I did not create any new users and there are no other users listed in the Accounts section.
I have users logging into a sql database using sql accounts. I've created below a simplified example of three tables in my 100+ table database. I have a sql table that holds extra information about the sql users and two data tables. I would like to restrict which records the user has access to in the manufacturer and automobile tables. I know I can add the UserID column to these two tables, and then add the appropriate userID into the UserID column of the manufacturer and automobile tables. Hence records in the automobile table with userid X will not be seen by userid Y. My question is, how can I possibly avoid modifying 1000+ sql queries to reference the UserID column? It would save me a lot of time if I can filter the results a user gets based on thier sql login/userid.
+--------------------------+ + USERS TABLE + + USERID int + Name varchar + Telephone varchar + +--------------------------+
+--------------------------+ + MANUFACTURER TABLE + + MANID int + Name varchar + +--------------------------+
+--------------------------+ + AUTOMOBILE TABLE + + AUTOID int + Name varchar + +--------------------------+
I have two table. Department is hierarchical table.
Department --- id (int primary key) --- name (varchar) --- parent (int)
Users --- Id --- name --- department_id
This query return all data from departments. But i cannot understand how get data from users
SELECT t1.name AS lvl1, t2.name as lvl2, t3.name as lvl3 FROM Department AS t1 LEFT JOIN Department AS t2 ON t2.parent = t1.id LEFT JOIN Department AS t3 ON t3.parent = t2.id
I'm needing to load data on the client side to load into a DataGrid. I decided to use excel to load the data, but it doesn't need to be. My problem is that it only loads from the server not the client. I browse to find the file and get the path with a control named ctlFindFile. A button labeled ctlLoadData will when pressed display the pathway in label1 and also place the pathway in the function GetDataFromExcel which returns a dataset from the spreadsheet and displays the data in a dataset. Data is returned fine if I’m on the server, but when I'm on a remote machine I receive an error. That is unless I've place a spreadsheet with the same name and pathway on the server as on the client machine, then I’m able to load the file. Now how do I get it to load from on the client machine?
Code below:
Private Sub ctlLoadData_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ctlLoadData.Click Label1.Text = ctlFindFile.Value DataGrid1.DataSource = GetDataFromExcel(ctlFindFile.Value, "SampleNamedRange").Tables(0) DataGrid1.DataBind() End Sub Public Function GetDataFromExcel(ByVal FileName As String, ByVal RangeName As String) As System.Data.DataSet 'Returns a DataSet containing information from a named range in the passed Excel worksheet Try Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;" &_ Data Source=" & FileName & ";Extended Properties=Excel 8.0;" Dim objConn As New System.Data.OleDb.OleDbConnection(strConn) objConn.Open() ' Create objects ready to grab data Dim objCmd As New System.Data.OleDb.OleDbCommand("SELECT * FROM " &_ RangeName, objConn) Dim objDA As New System.Data.OleDb.OleDbDataAdapter objDA.SelectCommand = objCmd ' Fill DataSet Dim objDS As New System.Data.DataSet objDA.Fill(objDS) ' Cleanup and return DataSet objConn.Close() Return objDS Catch ex As Exception ' Possible errors include Excel file already open and locked, et al. Return Nothing End Try End Function Error on Client: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.NullReferenceException: Object reference not set to an instance of an object.Source Error:
Line 31: Private Sub ctlLoadData_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ctlLoadData.Click Line 32: Label1.Text = ctlFindFile.Value Line 33: DataGrid1.DataSource = GetDataFromExcel(ctlFindFile.Value, "SampleNamedRange").Tables(0) Line 34: DataGrid1.DataBind() Line 35: End SubSource File: C:InetpubwwwrootSAI_LoadWebForm1.aspx.vb Line: 33 Stack Trace:
[NullReferenceException: Object reference not set to an instance of an object.] SAI_Load.WebForm1.ctlLoadData_Click(Object sender, EventArgs e) in C:InetpubwwwrootSAI_LoadWebForm1.aspx.vb:33 System.Web.UI.WebControls.Button.OnClick(EventArgs e) +108 System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +57 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +18 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33 System.Web.UI.Page.ProcessRequestMain() +1292
Version Information: Microsoft .NET Framework Version:1.1.4322.2032; ASP.NET Version:1.1.4322.2032
hi alli've got two tables called "webusers" (id, name, fk_country) and "countries" (id, name) at the meantime, i've a search-page where i can fill a form to search users. in the dropdown to select the country i included an option which is called "all countries". now the problem is: how can i make a stored procedure that makes a restriction to the fk_country depending on the submitted fk_country parameter?it should be something like SELECT * FROM webusers(if @fk_country > 0, which is the value for "all countries"){ WHERE fk_country = @fk_country} who has an idea how to solve this problem?
My insert statement for #Data - I only need to process each @EmployeeID one time, which is why I thought a loop would be sufficient, but I let this process run for 2 hrs and it still had not completed, so I feel I must have set-up something incorrectly!
This is my syntax, I am creating a table of active users, then wanting to get data for each of those active users. Â But only get the data for each active user 1 time.
Declare @EmployeeID varchar(50) CREATE TABLE #ActiveUsers ( ID INT IDENTITY NOT NULL ,EmployeeID varchar(50) ,processed int ) Create Table #Data
How do i prevent other users from changing the data of my tables? Means one can change data using only my login rest others cannot even DBA or also from server administrator
We have a large SQL database, and we need to send out updated records to many clients' sites which are not connected.
We currently have a tool which looks at the audit log of changes we made, creates a file based on this, which is then emailed to our clients. They then run a tool we created to remerge the changes.
I suspect SQL server replication might make all this possible. Am I right? Can SQL server produce a file automatically which can be applied to a remote database to update the tables as appropriate? From looking at some replication stuff it looks to me like you have to have the servers on the same network.
Is it at all possible to use this tool without any connection to SQL Server (mine data in Excel Worksheets only)?? Or is it required to connect to SQL Server?? From reading the documentation, one minute I get the feeling this is possible, then the next section I get a feeling it's not. Can anyone help? Thanks.
Product: Microsoft SQL Server 2005 -- Error 29515. SQL Server Setup could not connect to the database service for server configuration. The error was: [Microsoft][SQL Native Client]Encryption not supported on the client. Refer to server error logs and setup logs for more information. For details on how to view setup logs, see "How to View Setup Log Files" in SQL Server Books Online.
On Windows XP systems I get the following issue when trying to browse the MSDB folder in SSIS
Client unable to establish connection Encryption not supported on SQL Server. (Microsoft SQL Native Client)
I have noticed another post where several others have noticed the same issue. It appears to only occur on Windows XP installations. Is there a workaround or fix for this?
I have SQL2000 installed as the default instance, and now I'm trying to install SQL 2005 standard edition as a named instance.
I receive this error : SQL Server could not connect to database service for server configuration.. [SQL Native client] Encryption not supported on the client. However I'm able to install client tools
The setup works fine on other box with the same config : SQL 2000/Windows XP, is there any work around for this issue ?
In my SQL 2000 client network utilty "Force proctocol encryption " is desabled and did not find the setting for SQL 2005 !
Hi,I have a web app, that runs fine, except for one particular section that uses a class called by an event in the code behind. The class resides as a dll in the bin folder. We had no problems during testing, when only one user was running this dll. Problems soon occurred when multiple users tried running it. Here's the error & stack: 06/02/2007 09:25:26 ==> cburns ==> There is already an open DataReader associated with this Command which must be closed first. at System.Data.SqlClient.SqlInternalConnectionTds.ValidateConnectionForExecute(SqlCommand command) at System.Data.SqlClient.SqlConnection.ValidateConnectionForExecute(String method, SqlCommand command) at System.Data.SqlClient.SqlCommand.ValidateCommand(String method, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader() at ESP.Validator.Data.DatabaseEvents.DatabaseEventManager.Read(IEventable eventObject, Int16 eventType, DateTime earliestDate, DateTime latestDate) in C:My PathValidatorValidator.NETDataDatabaseEventsDatabaseEventManager.cs:line 92 at ESP.Validator.Data.Translink.CATCard.GetDespatchDate() in C:My PathProjectsValidatorValidator.NETDataTranslinkCATCard.cs:line 94 at ESP.Validator.Data.Translink.ExistingSchemeEntitlement.ReadCards() in C:My PathProjectsValidatorValidator.NETDataTranslinkExistingSchemeEntitlement.cs:line 215 at ESP.Validator.Data.Translink.ExistingSchemeEntitlement.Read() in C:My PathProjectsValidatorValidator.NETDataTranslinkExistingSchemeEntitlement.cs:line 147 at ESP.Validator.Data.Translink.TranslinkApplicant.ReadEntitlements() in C:My PathProjectsValidatorValidator.NETDataTranslinkTranslinkApplicant.cs:line 369 at ESP.Validator.Data.Translink.TranslinkApplicant.Read() in C:My PathProjectsValidatorValidator.NETDataTranslinkTranslinkApplicant.cs:line 353 at ESP.Validator.Data.Translink.PrePrintedLetter.Read() in C:My PathProjectsValidatorValidator.NETDataTranslinkPrePrintedLetter.cs:line 282 at ESP.Validator.ValidationProcessor.Read(ValidationSubject subject) in C:My PathProjectsValidatorValidator.NETValidationProcessor.cs:line 82 at clear_applications_scan_applications.ProcessValidation() It seems the data reader is getting reused. We have ensured after each read the reader is closed. Though all users are using the same connection string. Could it be a connection pool problem, with the connection being overwritten during execution? Should i edit it according to the users logon?I am really at a loss for ideas, and I don't mind admitting I am a bit out of my depth with this one!! Any ideas/suggestions would be greatly appreciated. Thanks
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 a large table with email column. I need to grant select access to this email column to many users but the actual email should not be visible to those users. I thought of following options:
1.Create an indexed view with that column encrypted and then grant access to users.While searching by email, their search text will also be encrypted and then matched with view column.Problem with this is that I have to create indexed view because most searches will be on Email column and table size is pretty big,20 m records. I don't prefer indexed view in general.
2. Add another column to table with encrypted email and grant access to that column to users. Problem with this is that it will increase table size and i'll need one more index.
