INSERT INTO UserInquiries (UserName, InquiryDate)
SELECT 'George', '10/1/2003'
UNION SELECT 'George', '10/2/2003'
UNION SELECT 'George', '10/3/2003'
UNION SELECT 'George', '10/4/2003'
UNION SELECT 'George', '10/5/2003'
UNION SELECT 'George', '10/6/2003'
UNION SELECT 'George', '10/7/2003'
UNION SELECT 'George', '10/8/2003'
UNION SELECT 'George', '10/9/2003'
UNION SELECT 'George', '10/10/2003'
UNION SELECT 'George', '10/11/2003'
UNION SELECT 'George', '10/12/2003'
UNION SELECT 'Harold', '11/1/2003'
UNION SELECT 'Harold', '11/2/2003'
UNION SELECT 'Harold', '11/3/2003'
UNION SELECT 'Harold', '11/4/2003'
UNION SELECT 'Harold', '11/5/2003'
UNION SELECT 'Harold', '11/6/2003'
UNION SELECT 'Harold', '11/7/2003'
UNION SELECT 'Harold', '11/8/2003'
UNION SELECT 'Harold', '11/9/2003'
UNION SELECT 'Harold', '11/10/2003'
UNION SELECT 'Harold', '11/11/2003'
UNION SELECT 'Harold', '11/12/2003'
I'm brain dead on other things right now. Any help would be gratefully accepted.
I would like to know if there is a way to find out who changed a users roles/access WITHOUT using the audit function. For example, if a user account was created and given SA access then changed to read only, how can I find out who made that change? I tried searching for an answer, but kept getting no results. I'm thinking this may tie into the sys.sysusers view?
This is my first time to deploy an asp.net2 web site. Everything is working fine on my local computer but when i published the web site on a remote computer i get the error "Failed to generate a user instance of SQL Server due to failure in retrieving the user's local application data path. Please make sure the user has a local user profile on the computer. The connection will be closed" (only in pages that try to access the database) Help pleaseee
I am new to ASP.NET so pordon me if my questions seems to be stupid.
I was given an assign to develop a ASP that would return results from a SQL server.
The problem is that part of my query to the SQL server comes from the webform
select a.invnum, a.invdate, a.duedate, a.invamt,a.payamt from vpshead a inner join vendors b on (a.vendnum = b.vendnum) where (a.vendnum = " & user.text & " ) and (b.vendpass = " & Pass.text & " ) and (a.payflag <> 'V') and ( (a.invamt <> 0) or (a.payamt <> 0) )and (a.chkno = '') order by a.invdate"
As your can tell I am using the user.text and Pass.text in my query which will come from the web form.
I'm trying to produce a user activity table out of a table that contains rows on when users log in or out. However, I can't seem to get the query to 'pick' the right date time in the join.
Here is what I came up with, but it doesn't seem to be working right :eek:
select distinct (userin.changeid), userin.userid, userin.createdate as LoggedInDate, userout.createdate as LoggedOutDate, DateDiff(mm,userin.createdate,userout.createdate) as Duration from userChange userin inner join ( select * from userChange where loggedin = 0 ) userout on userin.userid = userout.userid where and userin.loggedin = 1 and userin.createdate < userout.createdate order by userin.createdate desc
Dear Masters; I have a Messages table; I use this table to post System Messages to my users. But what I don't know is how can I add data for multiple users. I mean I want to add same message for multiple users (ex: Please update your infos). In the belove table I have some messages for users 100 and 200; How can I add same (please update your infos) messages to both users? Ex: Messages Table ID UserID Msg 1 100 "Hello" 2 100 "Hi" 3 200 "Hello"
just getting started with my first db driven web project... I am using a MySql database with membership and roles and got that working fine. I have tables containing details of courses that users are enrolled on etc. and want to display a list of courses for the user that is signed in, so he can continue lessons on the one of his choice. How do I pass the users name to the database query for a DataList control. So far I have tried lots of variations of the following: <asp:SqlDataSource ID="dsCourses" runat="server" ConnectionString="<%$ ConnectionStrings:xxx %>" ProviderName="<%$ ConnectionStrings:xxx.ProviderName %>" SelectCommand="SELECT c.CourseName FROM courses c, enrolments e WHERE c.CourseID=e.CourseID AND e.Username='<% =User.Identity.Name %>'"></asp:SqlDataSource> <asp:DataList ID="DataList1" runat="server" DataSourceID="dsCourses"> <ItemTemplate> <asp:HyperLink ID="HyperLink1" runat="server" NavigateUrl="Lesson.aspx">'<%# Eval("CourseName") %>'</asp:HyperLink><br /> </ItemTemplate></asp:DataList> </p> However, the <% =User.Identity.Name %> doesn't work here (but it does elsewhere on the page) - not sure why?? The code works fine if I hard code a user name into the select statement. Suggestions pleeeeeese!!
I need to be able to have users run a query under the 'sa' account that will create another account that will just be used for reading and writing to a specific database. Is there a way to do this with just a sql script or can you only set up logins and accounts from the enterprise manager?
I have a need to identify the name of a user and would like to access the NT userlist. Or failing a realtime access, can I achieve a periodic update of a SQL table with userid, username from the NT userlist?
I am trying to retrieve the user names of all users within a database. The problem is, that I have only created a login within SQL 7.0 for the NT group that the users belong to.
As such, when I try to query the syslogins table I only get the NT group.
Is there any way of retrieving the users that belong to this NT group.
Any help or suggestions on this would be greatly appreciated.
Hi folks, i am lookin for an option in the QA to take user input while executing a query. just like we do in SQLPLUS: SELECT * FROM dept WHERE deptno=$abc here it would ask me for the deptno. I guess it's a very handy feature and we could test our variables and calculations withing the procedures etc.
There is a large data table called emp_history on an SQL Server, which contains the employment history of each employee. The significant columns are as follows:
entry_id employee_id start_date position
where entry_id is the primary key and auto-increments.What I need, is to create a query to extract the employee_id and position, based on the most recent record (most recent start_date) for each employee.
I need to trigger an email to user when a work order due date is approaching 1 day prior to the due date. Also I need to trigger when a user adds, delete, or modify work order to check on work order due.
I would like to setup a query where the user is firstly prompted to input the information e.g. if they are trying to locate a particular person from a telephone database they would be prompted to enter the surname for that person.
Hi,I tried to create a simple view as followsCREATE VIEW V_ALL_USERTABLE_COLUMNSAS(SELECTOBJ.NAME as TableName,COL.NAME as ColName,TYP.NAME AS TYPEFROMSYSOBJECTS OBJ,SYSCOLUMNS COL,SYSTYPES TYPWHEREOBJ.TYPE = 'U'AND OBJ.ID = COL.IDAND COL.TYPE = TYP.TYPE)Combined with consistent naming conventions I will use this view toeasily find foreign keys; a laSELECT *FROM V_ALL_USERTABLE_COLUMNSWHERE ColName LIKE ('%user_id')There is something wrong with my view definition that I don't getthough; it doesn't return all the columns. I have a table with thefollowing definitionCREATE TABLE [dbo].[c_messages]([cid] [int] IDENTITY (1, 1) NOT NULL ,[touser_id] [int] NULL ,[tosession_id] [char] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[fromuser_id] [int] NOT NULL ,[message] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOTNULL ,[message_read] [bit] NOT NULL ,[logout] [bit] NULL) ON [PRIMARY]GOThe problem is that the select I used to define the view doesn'treturn the touser_id column. I have sort of a sneaking suspicion thatthe problem has to do with joining syscolumns.type to systypes.type,but I don't know what to do instead (I'd really like to include thetype; it's useful if I ever changed the type of a primary key and wantto check that I also changed all the foreign keys).Any help would be appreciated!
what's the best way to set user vars in ssis from a query. I have some components that set a from and to date in a table. I'd like to select those values right into some user vars in the pkg.
Is there a way through T-SQL to run a store procedure and inside the store procedure, change the user, execute a select, and get back the original user that run the store procedure?
I am sure this has been asked a thousand times before in a thousand different ways, but here goes...
I am in need of the ability to prompt users for a From Date and To Date to insert into a WHERE clause in SQL queries. What is the easiest least painful way of accomplishing this? A great many of our customers utilized SQL Server Express or MSDE, so they do not have the full gambit of SQL tools handy. Believe me when I say that I have searched high and low across the internet for this solution. I was hoping I might be able to accomplish this through VB Script or J Script. Or, anything reasonable.
I have a problem selecting fields from a table where fields are equal to user input from a listbox. example listbox of zip codes: 33023[red]22300[/red]39844[red]29339[/red]23883[red]38228[/red] user wants to retreive highlight zip codes from database.connection working perfect.Thank you for your help.
I have a table which has six fields ID, dateDue, dateReceived, dueday. month, ContactFYEmy query looks likeselect ID, DateDue, dateReceived, dueday, month, ContactFYEfrom Reportwhere (dbo.Report.DateDue BETWEEN dbo.udfDisplayTime(dueday, month, ContactFYE) AND DateDue )user defined functionCREATE FUNCTION dbo.udfDisplayTime ( @dueday int, @month int, @ContactFYE smalldatetime) RETURNS smalldatetime AS BEGIN DECLARE @ReturnString AS smalldatetime Declare @dueday1 as int if (@month =1)beginset @dueday1 = @dueday -7endelse if (@month =2)begin set @dueday1 = @dueday -14end else if (@month =3)begin set @dueday1 = @dueday -60endselect @ReturnString = DATEADD ( dd, @dueday1, @ContactFYE) Return @ReturnStringENDI got a incorrect result when using this query and user defined function.the result that I got was out of between displaytime and datdue. any idea for this , Thanks.
I currently have a query in an access database that filters the records in a Sql Linked table for their user ID. So basically i use Environ("UserName") to filter for a field that contains their user name. I want to secure this and put it on the SQL Server so there is no way anyone can see any records other then the ones that are filtered for them. Does anyone know how to do this? I think it will be some SQL query.
I was able to do this on a Windows XP machine but I am not able to get this to work on a Windows 2003 Server machine. I am connecting to the Windows 2003 Server and MS SQL Express via remote desktop on the lan.
This is what worked for me before: EXEC sp_grantlogin '4800X2ASPNET' USE MyDB EXEC sp_grantdbaccess '4800X2ASPNET' EXEC sp_addrolemember 'db_owner', '4800X2ASPNET'
But for some reason now I am getting this error: Msg 15401, Level 11, State 1, Procedure sp_grantlogin, Line 49 Windows NT user or group '4800X2ASPNET' not found. Check the name again. Msg 15401, Level 16, State 1, Line 1 Windows NT user or group '4800X2ASPNET' not found. Check the name again. Msg 15410, Level 11, State 1, Procedure sp_addrolemember, Line 75 User or role '4800X2ASPNET' does not exist in this database.
I thought what I needed to add was the name of this pc that will connect remotely, but that doesn't seem to be working. What weird is the old copy of this query I used had the local pc as the computer name. If I exchange the computer name from the remote pc name to the local 2003 Server machine name, the query works fine. Although that still doesn't let me add connections via Web Developer Express without error. It doesn't make sense to me anyhows, how could I have used the local machine's name and not the remote machine's name to get this to work previously. So confused.
Hi there!Is there any way to find the user who did some transaction of updatein query analyser in certain time ?The update was done manually in query analyser and is not throughsystem.Is there any way to check it?Any command or software to check this data as when certain table wasupdated?Thanks for the help!
I'm experiencing quite an interesting issue with our database. We're currently running SQL Server 2005 Standard (x64) (v9.00.3054.00) and I have a block of code that just insists on not working within a UDF. Everything I've read seems to point that I'm doing this corerctly but apparently I'm missing something.
Executing the query below:
DECLARE @sp DATETIME DECLARE @ep DATETIME DECLARE @trackType INT
SET @sp = '4/21/2008' SET @ep = '4/28/2008' SET @trackType = 1
SELECT c.unqempid, isnull(count(c.contactid),0) AS contacts, isnull(bookings.count,0) AS bookings, isnull(showed.count,0) AS showed, isnull(ow.totalPurchased,0) AS purchases, isnull(ow.totalSold,0) AS volume FROM contacts c WITH (NOLOCK) LEFT JOIN ( SELECT b.unqempid, ISNULL(count(bookingid),0) AS count FROM bookings b WITH (NOLOCK) INNER JOIN contacts c WITH (NOLOCK) ON c.contactid = b.relcontactid WHERE c.contactdt BETWEEN @sp AND @ep GROUP BY b.unqempid ) AS bookings ON bookings.unqempid = c.unqempid LEFT JOIN ( SELECT b.unqempid, ISNULL(count(bookingid),0) AS count FROM bookings b WITH (NOLOCK) INNER JOIN contacts c WITH (NOLOCK) ON c.contactid = b.relcontactid WHERE c.contactdt BETWEEN @sp AND @ep AND didshow > 0 GROUP BY b.unqempid ) AS showed ON showed.unqempid = c.unqempid LEFT JOIN ( SELECT c.unqempid, count(leadid) TotalPurchased, sum(saleprice) AS TotalSold FROM ordermgmt.dbo.orders o WITH (NOLOCK) INNER JOIN ordermgmt.dbo.appointments a WITH (NOLOCK) ON a.weborderid = o.orderid INNER JOIN contacts c WITH (NOLOCK) ON c.personid = a.leadid INNER JOIN bookings b WITH (NOLOCK) on b.relcontactid = c.contactid WHERE c.contactdt BETWEEN @sp AND @ep GROUP BY c.unqempid ) as ow ON ow.unqempid = c.unqempid WHERE c.contactdt BETWEEN @sp AND @ep GROUP BY c.unqempid, bookings.count, showed.count, ow.totalPurchased, ow.totalSold
Yields no issues however if I put this into a UDF, it runs and never finishes executing. I'm not quite sure what my issue was so I'm hoping someone could point out where my goof is.
Here is the function:
CREATE FUNCTION [dbo].[fnEmployeeSchedulingResults] ( @sp DATETIME, @ep DATETIME ) RETURNS TABLE AS RETURN (
SELECT c.unqempid, isnull(count(c.contactid),0) AS contacts, isnull(bookings.count,0) AS bookings, isnull(showed.count,0) AS showed, isnull(ow.totalPurchased,0) AS purchases, isnull(ow.totalSold,0) AS volume FROM contacts c WITH (NOLOCK) LEFT JOIN ( SELECT b.unqempid, ISNULL(count(bookingid),0) AS count FROM bookings b WITH (NOLOCK) INNER JOIN contacts c WITH (NOLOCK) ON c.contactid = b.relcontactid WHERE c.contactdt BETWEEN @sp AND @ep GROUP BY b.unqempid ) AS bookings ON bookings.unqempid = c.unqempid LEFT JOIN ( SELECT b.unqempid, ISNULL(count(bookingid),0) AS count FROM bookings b WITH (NOLOCK) INNER JOIN contacts c WITH (NOLOCK) ON c.contactid = b.relcontactid WHERE c.contactdt BETWEEN @sp AND @ep AND didshow > 0 GROUP BY b.unqempid ) AS showed ON showed.unqempid = c.unqempid LEFT JOIN ( SELECT c.unqempid, count(leadid) TotalPurchased, sum(saleprice) AS TotalSold FROM ordermgmt.dbo.orders o WITH (NOLOCK) INNER JOIN ordermgmt.dbo.appointments a WITH (NOLOCK) ON a.weborderid = o.orderid INNER JOIN contacts c WITH (NOLOCK) ON c.personid = a.leadid INNER JOIN bookings b WITH (NOLOCK) on b.relcontactid = c.contactid WHERE c.contactdt BETWEEN @sp AND @ep GROUP BY c.unqempid ) as ow ON ow.unqempid = c.unqempid WHERE c.contactdt BETWEEN @sp AND @ep GROUP BY c.unqempid, bookings.count, showed.count, ow.totalPurchased, ow.totalSold )
What is better for performance, using a user defined function or a sub query to perform an aggregate calculation in a select statement. for eg would it be best to call a user defined function which performs the below calculation of the sub query shown. I'm speaking purely from a performance point of view.
SELECT o.ordersid,
o.orders,
(SELECT SUM(i.total) FROM items AS i WHERE i.ordersid = o.ordersid) AS [total]
