Can anyone suggest an alternative to the following problem?
We have had 2 users change their NTLogin in AD. For example, from jsmith to jbloggs. NB this is a change, NOT a completely new login. When the users connect to sql server and call the function SYSTEM_USER, the login returned is the old login:
TheDomainjsmith
This is causing an audit trail problem. Exactly the same issue exists with sys.sysprocesses.
The only way I have found of correcting the problem is by restarting the SQL server service which is obviously not ideal for such a trivial issue.
You can recreate this problem via local users on a sql box as well:
1. Create a local user on the SQL box called FirstUserName and grant access to SQL
2. do a runas on management studio and runas the FirstUserName user
3. connect to SQL and run SELECT SYSTEM_USER, you will get [nameofsqlbox]FirstUserName which is correct
4. kill management studio
5. rename the user SecondUserName
6. do a runas on management studio and runas the SecondUserName user (note, same user, now has different login name)
7. connect to SQL and run SELECT SYSTEM_USER, you will get [nameofsqlbox]FirstUserName which is now incorrect
8. restart sql server service
9. connect to SQL and run SELECT SYSTEM_USER, you will get [nameofsqlbox]SecondUserName which is now correct
Any solutions other than just sucking up a restart everytime a user changes login name would be much appreciated!
I have sql server 2000 loaded on my nt machine. Also have another sql2000 server on the network. I have connected to the these servers thru sql enterprise managers. We have a vb application which uses sql database. I have created a DSN to the server on the network with the options to use sql login and supplied the username and passwords.
when I try to start the vb application it asks for the which dsn to use. then it gives the sql error message saying login failed for the nt user ( i.e my nt login name) then displays another login box with use trusted connection option checked. I wasn't ables to figure out why the DSN is using the nt login instead of sql login. Any help is greatly apppreciated. Thank you.
is there a way to move login names or Win 2000 active directory to SQL? need to get NT login name, emp id to database so i can use for some other application and the way to automate update like in DTS
Hi, I have a student results table with the layout shown below (four records with the fields separated by dashes). Sorry its so messy. Anyway, you can see that there are duplicates. I want to write an SQL statement that will pick out only the 'supplemental' records if duplicates occur. Any ideas on how to do this?
The problem is to find a subset of rows such that each value in each of two columns (animals and food brands in this example) appears in at least one row. The purpose is to produce a set of samples from a large table. The table has a animal_name column and an food_brand column; I want a set of samples that contains at least one of each animal_name and at least one of each food_brand, but no more than necessary.
CREATE TABLE Feeding_Options (license_nbr INTEGER NOT NULL PRIMARY KEY, animal_name VARCHAR (10) NOT NULL, food_brand VARCHAR(15) NOT NULL); INSERT INTO Feeding_Options VALUES (6401715, 'rat', 'IAMS'),
[code]....
To frame the problem better, her are the values in each column:
In this data, (6401715, 'rat', 'IAMS') and (1058337, 'rat', 'IAMS') are interchangeable, as are some of the other rows. There can be more than one minimal solution whcihmight be the whole set.
The key to this problem is the "valueID". The first digit of the value is significant - the "1" and the "7" in the above mean the values are parts of different groups of search criteria.
What I need to do is to select from this list into a temp table all those bookings (identified by AdBookingID) which have valueIds of both types - i.e. an row in the table where the valueId starts with a 1 and a row in the table where the valueId starts with a 7 - in the above data sample the only AdBooking which qualifies is 98220.
I worked out a method of doing this going by the number of entries each item had in the table:
select count(distinct valueId) as valueId, adWeeks, clientId, adID, AdBookingID into #worktable from #tmp group by adWeeks, clientId, adID, AdBookingID having count(distinct valueid) > 1
Which is largely accurate. But - and here's the killer - although each adBooking can only have one valueId starting with 1, it can have multiple entries starting with 7. These bookings are rare, but they do exist and are causing anomalies in the data returned which - if you recall - must only contain booking records for which there are valueId entries with both a 1 and a 7.
Can anyone suggest a way I can get just the data I need? Cheers, Matt
I'm using SQL Server 2005. The problem I have is as follows. I have several production lines and as with everything parts in the line tend to break. I have data from all the breaks that occurred in the last 2 years. What I want to do is predict the next break and the production line it's going to happen on. I would also like to go to a future date and check what possible breaks might occur on that date. I've run quite a few models but none of them helps me with future events. I think I might be using the wrong algorithm or I€™m just not doing it right. If somebody can please suggest an algorithm and maybe help me with a web site that has a tutorial similar to my problem
Hi,I have a gridview on a page, which should select data from a database. I want it to select the data row if it finds its UserName in the UserName column, but there will be many usernames in the username column, seperated by commas.Here is the select statement that I have at the moment, which doesnt return any data atall:SELECT [message], [senddate], [subject], [messageid], [sendername], [recievername] FROM [Messages] WHERE ([recievername] LIKE '% ' + @recievername + ' %')@recievername = Profile("UserName") which is the vwd wizards way of saying the current user. A Username column has data like this:bezzer , bezlan , beezer , beezler with a space between commas and a space at the start and the end.Thanks if someone can help!Jon
I have this SP that works, except I need to add another field value for the WHERE clause. As you can see I have "WM" but I need to add "PR", and those two are definitely in the table field. I've tried a variety of syntax arrangements using the AND operator, the OR operator, the & operator, just a comma between the two, nothing between the two. Can someone please show me what I'm doing wrong. It fileters for "WM" fine, but I also need it to filter in the WHERE clause for "PR". Here is the SP:
CREATE procedure spDemoSchedule (@beginDate varchar(10), @endDate varchar(10), @storeNum int) AS
SELECT Progstats.[Program#], Progstats.KCKOFF, Progstats.ProgramName, Progstats.Parent, Store.[Str#], Store.Status, Progstats.Dept, Store.[Program#], Product.[Item#], Product.[Item] FROM Progstats INNER JOIN Product ON Progstats.[Program#] = Product.[Program#] INNER JOIN Store ON Progstats.[Program#] = Store.[Program#] WHERE Progstats.KCKOFF BETWEEN @beginDate AND @endDate AND Store.[Str#]=@storeNum AND Progstats.CLASS="WM" GO
Since the Customers Table contains the complete list of customers I seek to have a Join made between it and the Paymentstable to check the amount made by the customers.
However, the trouble arises when I seek to check the average amounts made by each customer during last two months in which such a customer had paid.
In further clarification, lets consider current month of May 2015 going on;
Customer_A had paid $10,000 in AprilCustomer_A had paid $5,000 in AprilCustomer_B had paid $10,000 in AprilCustomer_A had paid $18,000 in MarchCustomer_A had paid $2,000 in MarchCustomer_A had paid $10,000 in FebruaryCustomer_B had paid $8,000 in FebruaryCustomer_B had paid $4,000 in FebruaryCustomer_B had paid $3,000 in February...
Hi am creating a new database not sure which way is the best way to go. Here are the questions
I have a history table which has its an identity field and an identification field which shows what type of history field it is. As in payment, printout, change and so on.... For this field should i use Full text which has the payment, printout, change and so on and make it easy on the eye and for queries and coded to use the same text all the time have varchar(2) type of field which holds an understandable field PR, PO, CH and a reference table for it as in PR= PAYMENT, PO = Printout, CH as in change and so on or have a integer field as in 1, 2, 3 and ref. table 1=payment, 2 = printout, 3=change and so on I have a address table which holds multiple types of addresses which are really limited count maybe upto 4 different types have a integer field which links to a reference table as above have bit fields which can be used to check if it gets in one of the group types have a full text option like above Im open for any type of suggestions and i would love to hear the reasons why its picked TY
Hi there, I believe this is simple requirement but to me it seems to be complicated. My data source is excel. I would like to configure the path of my data source in SSIS Package and file name is not a constant value. SSIS package should pick the file named as Today's (current) date from the configured path. As well, package should catch the exception for example file not found and log into custom log. It would be gr8 if some body helps me out.
I've created a solution with 5 packages in. I've scripted a config file for each package where I would be able to change the source file connection and the database servers (source and destination). On the first package, I am able to change the config attributes and the affects are picked up, but the rest of the packages do no pick up the changes.
For example, if the source file is "C:Filessource.txt" and this is changed to "C:Filessource123.txt" (Just to make sure it fails) it wont pick up the new changes and still uses "C:Filessource.txt".
Also, I tried changing the name of config file itself and the package still ran as if nothing had changed. It's as if it's not recognising the config file.
I cannot seem to get the report to pick up the custom style sheet I placed into Reporting servicesReportServerStyles subdirectory. Nor will it pick up any changes to the HTMLViewer.css either. The report obviously isn't looking there perhaps? I've used the http: etc. ReportServer/Pages/ReportViewer.aspx?%reportname%&rs:Format=HTML4.0&rs:Command=Render&rc:StyleSheet=MyStyleSheetname
I've even modified the HTMLViewer.css file as well. Nothing. Nada. Is there somewhere else this is cached? Or some way I can find where the report looks for this?
My package is having .csv file as a source and I kept OLEDB destination to load it.
Stored the .csv file in a shared folder and the exact path is given in Enumerator configuration of the foreach loop container. When I execute my package, it is giving the warning as below:
It is saying that file is not there in the specified path and directory is empty. I am running the SSIS package from TFS. I am sure that I have read and write access for the shared folder for my userID. Is there any access there to pick up this file from path.
I have a text field which has entries of variable length of the form:
"house:app.apx&resultid=1234,clientip" or "tost:app.apx&resultid=123,clientip" or "airplane:app.apx&resultid=123489,clientip"
I'm trying to pick out the numbers between resultid='...',clientip no matter what the rest of the string looks like. So in this example it would be the numbers:
1234 123 12389
the part of the string of the form resultid='...',clientip always stays the same except the length of the number can vary.
I'm trying to finish a DTS Package by controlling the error flow with @@ERROR. Yet, there's one error which I can't seem to capture 'Syntax error converting the varchar value to a column of data type int'
If you try the code below, I can't seem to pull an @@ERROR ID from it.
IF OBJECT_ID('tempdb..##LogTemp') IS NOT NULL BEGIN DROP TABLE ##LogTemp END
CREATE TABLE ##LogTemp ( Error_ID INT ) INSERT INTO ##LogTemp ( Error_ID )
VALUES ( 'Test' )
PRINT @@ERROR
Query Analyzer prints this out:
Server: Msg 241, Level 16, State 1, Line 83 Syntax error converting datetime from character string.
in this procedure the Gender column is a bit datatype. as you can see i'am adding a string.
when i exec proc my error message "print 'error'" is not showing. please help.
you can email at cbmorton!at!gmail.com
CREATE PROCEDURE [dbo].[sp_newuserregistration] AS BEGIN TRAN INSERT INTO UserLoginInfo (UserName,[Password]) values ('?','?') SELECT @@IDENTITY AS 'Identity' DECLARE @UserID AS INT SET @UserID = @@Identity INSERT INTO UserAccountInfo (UserID, Email) VALUES (@UserID, '?') INSERT INTO UserProfileInfo (UserID, FirstName, LastName, Gender, DateOfBirth) VALUES (@UserID, '?', '?', 'x', null) IF @@ERROR <> 0 BEGIN PRINT 'error'
My server is a dual AMD x64 2.19 GHz with 8 GB RAM running under Windows Server 2003 Enterprise Edition with service pack 1 installed. We have SQL 2000 32-bit Enterprise installed in the default instance. AWE is enabled using Dynamically configured SQL Server memory with 6215 MB minimum memory and 6656 maximum memory settings.
I have now installed, side-by-side, SQL Server 2005 Enterprise Edition in a separate named instance. Everything is running fine but I believe SQL Server2005 could run faster and need to ensure I am giving it plenty of resources. I realize AWE is not needed with SQL Server 2005 and I have seen suggestions to grant the SQL Server account the 'lock pages in memory' rights. This box only runs the SQL 2000 and SQL 2005 server databases and I would like to ensure, if possible, that each is splitting the available memory equally, at least until we can retire SQL Server 2000 next year. Any suggestions?
We have an old machine which holds SQL server 2000 database. We need to migrate a whole database to a new machine which has SQL server 2005.
When we tried to move whole database using Import and Export Wizard, only tables can be selected to import/export. However we want to import/export the whole database, including tables, stored procedure, view, etc. Which tool should we use?
We have an old machine which holds SQL server 2000 database. We need to migrate a whole database to a new machine which has SQL server 2005.
When we tried to move whole database using Import and Export Wizard, only tables can be selected to import/export. However we want to import/export the whole database, including tables, stored procedure, view, etc. Which tool should we use?
When I proposed start to use SQL Server 2005 for new VS 2005 web sites, one of my co-workers responded that we will update the old SQL Server 2000 databases to SQL Server 2005 when we are ready to use 2005 SQL Server.
Questions: 1. Any expected problems to upgrade old 2000 databases to new 2005 SQL Server? 2. I have installed both 2005/Management Studio Express and 2000/Enterprise Manager in my PC. Any expected problems when running both 2000 and 2005 SQL Server at the same database server? 3. What is the best configuration for running SQL Server 2005 when we have old 2000 databases? Upgade or not upgrade?
I am getteing need help Query analyzer error Unable to connect server local Msg17, level 16,state 1 ODBC SQL server driver [DBNETLIB]SQL server does not exist
Hi, I am having a problem connecting my .net applications from the application server to the database server. When I run the application from my windows xp (sp2) box it works fine. When I try to connect via SQL Management Studio to the database server from the application server I get the same error. Here is the error: System.Data.SqlClient.SqlException: An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) Here is the Environment: App Server: Windows Server 2003 Standard Edition Inside Company's Firewall/ Network Database Server: Windows Server 2000 Advanced Edition SQL Server 2000 SP4 Remote Connections to the Server is checked Enable Protocols: Named Pipes & TCP/IP TCP/IP Port: 1402 (I don't know why it isn't the default of 1433) The db server is sitting out side the Company's firewall (don't ask me why). I can access it fine from inside the firewall on my XP box but not from windows server 2003. There is a web server outside the our network that also connects to the db server with no problem and that is running Windows Server 2003 Web Edition. I can ping the db server from the app server using the IP address. I tried using the IP address and the port 1402 in my connection string but that didn't work from any machine (XP and Server). I imagine the issue is somehow related to the company's firewall but why would it only block Windows Server 2003 and not XP? What do I tell the network admin to change? Any help would be appreciated. Thanks, Oran
if you can restore a database to Server B using Server A as the service. Meaning we would issue the command on Server A but somehow point to Server B as where we want the restore to happen.
The backup file would be in a location independent of both servers.
Dear all,On Win2000 server with SP3, I am trying to access a SQL Server 7.0database, "TestDB", from VB6 via a SQL Server ODBC system DSN using ADO2.7. In SQL Server Enterprise Manager, there is a login named "Tester".In its property window, NO "Server Roles" was assigned but its"Database Access" was set to "TestDB". This login was also made as theuser of "TestDB" with "public", "db_datareader" and "db_datawriter"selected as its "Database role membership". All the tables I am tryingto access in "TestDB" were created under "Tester".My code is like:Set conn = New ADODB.Connectionconn.Open "DSN=TestDSN;UID=Tester;PWD=test"Set cmd = New ADODB.Commandcmd.ActiveConnection = conncmd.CommandText = SQLset rs = cmd.Execute()If I set the SQL to something like "SELECT * FROM tbl_test", I alwaysget an error of "-2147217865" saying "[Microsoft][ODBC SQL ServerDriver][SQL Server] Invalid object name tbl_test". If I set the SQL to"SELECT * FROM Tester.tbl_test", everything runs properly. Could anyoneplease kindly advise why the first SQL is not working? Or in otherwords, why must I prefix the table name with its owner while the DBconnection is already made under that owner name? Thanks in advance.Tracy
When I try to migrate a database on a SQL Server 2000 server to a SQL Server 2005 server with the Copy Database Wizard of the SQL Server Management Studio, I'm confronted with the following problem;
Performing operation...
- Add log for package (Success) - Add task for transferring database objects (Success) - Create package (Success) - Start SQL Server Agent Job (Success) - Execute SQL Server Agent Job (Error) Messages * The job failed. Check the event log on the destination server for details. (Copy Database Wizard)
When I take a look at 'Event viewer' on the SQL 2005 server, the following error is displayed;
InnerException-->An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
I already enabled the MSSQLSERVER network configuration protocols (TCP/IP and Named Pipes ).
My site works fine in VWD2008 express, but I get this error when I try to use it on my live website. An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. According to this article: http://support.microsoft.com/kb/914277 I am supposed to:
1. Click Start, point to Programs, point to Microsoft SQL Server 2005, point to Configuration Tools, and then click SQL Server Surface Area Configuration. Ok, there is no such program in this folder. The only thing in there is "SQL Server Error and Usage Reporting"... The other thing I am greatly concerned with is this: All is want is for my webpages to be able to access my database for user authentication. I DO NOT want to grant the internet rights to remote connect to my database.
