I just installed SQL Server 2005 express. I have full rights, and enabled XP_CMDSHELL...
Here's my Trigger:
USE [JobCost]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[start_jobcost]
ON [dbo].[TJC]
AFTER INSERT
AS
EXEC master..xp_cmdshell 'C:jobCost.exe'
This Trigger saves successfully but the trigger won't run when I append a record to the table (TJC) using access query. Is there something else I'm missing...do I need to enable the trigger function?
I'm in a slightly sticky situation. I recently got a job I interviewed for as a business analyst/programmer. I know quite a bit of VB 6.0 and SQL language, also enough access to build a decent database as a backend for my apps. I've recently received a 4 yr CIS degree, so I know a little about everything. I told them very clearly three times in the interview that I'd never built a SQL Server DB, but they must not have cared about that. They need someone who can program, do some DB work, and also do some business analysis. I was the best fit they could find.
After working with the group a few days, I figured out they need a data warehouse and marts to do some historical analysis, and eventually data mining and trend analysis, etc. I think they said they'd like to have that by the end of next year.
What I need now is one or two really great books to get started with, and a small synopsis on what I'm going to need as far as dev software and server specifics to get this thing off the ground. The first thing they would like to see is simply enough tables to dump data into from 4 or 5 different DBs, from which they can pull data for analysis whenever they need to do so. The total rows for this DB will be around 170mil, but with just a few measures in each row. Microsoft spreadsheet says I should expect about 5gig per year of data.
So I'm freaking out because I know ziltch about SQL Server, what tools and server set up I'm going to need, and the more I read, the less it sounds like "Oh it's not that big of a deal. It's easy", as a few friends told me.
I have an existing ASP.NET application (in C#) that currently uses an Access database. Due to a corporate mandate I need to switch over to using SQL Server 2005.
It appears that my development machine has SQL Server 2005 installed on it but not necessarily configured. I tried running the Upsizing Wizard under Access 2003 to do a simple port of my Access database to SQL Server but it fails saying it cannot connect to the database.
No doubt something is not yet setup or configured. I haven't a clue where to begin! For now I just want to be able to port the data and test my application on my development machine. Eventually the real database will exist on a corporate server. So I'm just interested in setting up the development environment.
I have the membership stuff up and running. I've added a field to the membership table called custnmbr. Once a user logs in, I want store his custnbmr in the session and use that to lookup data in another db. ie: Joe logs in and his custnumbr is 001, he goes to the login success page and sees his list of service calls which is: select top 10 * from svc00200 where custnmbr = 001 (the membership.custnmbr for the logged in user) I know how to do this in old ASP using session variables....but I have no idea where to even start with .Net. Many thanks
Does anyone know how I can determine the number of page writes that have been performed during a set period of time? I need to figure out the data churn in that time period.
Very new to SQL and trying to get this query to run. I need to sum the total trips and total values as separate columns by day to insert them into another table.....
My code is as follows;
Insert Into [dbo].[CombinedTripTotalsDaily] ( Year, Month, Week, DayNo, Day, Trip_Date,
I haven't a clue how to accomplish this.All the data is in one table. The data is stored by registration dateand includes county and number of students brokne out by grade.Any help appreciated!Rob
I have a table that writes daily sales each night but it adds the day's sales to the cumulative total for the month. I need to pull the difference of todays cumulative total less yesterdays. So when my total for today is 30,000 and yesterday's is 28,800, my sales for today would be 1,200. I want to write this to a new field but I just can't seen to get the net sales for the day. Here is some sample data. For daily sales for 6-24 I want to see 2,000, for 6-25 3,000, 6-26 3,500, and 6-27 3,500. I'm thinking a case when but can't seem to get it right.
I have created a Report using Visual studio-the report displays a subreport within it.
On the Subjective Report I have 12 values for each month of the year.
For the first month the value is =sum(Fields! Month_1.Value), and I have named this text box €™SubRepM1€™ The name of the subreport is €˜subreport1'.
On my Main Report, again I have 12 values for each month of the year. For the first month the value is =sum(Fields! Month_1.Value)*-1, and I have named this text box 'MainRepM1' The name of the main report is 'GMSHA Budget Adjustment Differentials'
The report displays both of the subreport and main report values but I now need to total these values together for each month in order to produce a grand total.
I have tried using the following to add the totals for Month 1 together, =subreport1.Report.SubRepM1 + MainRepM1 but this does not work and I get the following error message €˜The value expression for the text box 'textbox18'contains an error [BC30451] Name subreport1 is not declared'.
I feel that it should be a simple matter of adding the two sets of values together but I€™m having major problems trying to get these totals to work.
Hi there I sorry if I have placed this query in the wrong place. I'm getting to grips with ASP.net 2, slowly but surely! When i try to access my site which uses a Sql Server 2005 express DB i am receiving the following error:
Server Error in '/jarebu/site1' Application.
Database 'd:hostingmemberasangaApp_DataASPNETDB.mdf' already exists.Could not attach file 'd:hostingmemberjarebusite1App_DataASPNETDB.MDF' as database 'ASPNETDB'. 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.Data.SqlClient.SqlException: Database 'd:hostingmemberasangaApp_DataASPNETDB.mdf' already exists.Could not attach file 'd:hostingmemberjarebusite1App_DataASPNETDB.MDF' as database 'ASPNETDB'.Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below. Stack Trace:
Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.210
This is the connection string that I am using: <connectionStrings> <add name="ConnectionString" connectionString="Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|ASPNETDB.MDF;Integrated Security=True;Initial Catalog=ASPNETDB;User Instance=True" providerName="System.Data.SqlClient"/> </connectionStrings>
The database is definitly in the folder that the error message relates to. What I'm finding confusing is that the connection string seems to be finding "aranga"s database. Is it something daft?
Anyone has a "one sql statement" to get the total spaceused and totalspace allocated of an instance ? ie same as sum of relevance fieldsfrom sp_spaceused for each database in an instance, that works accrossversion of mssql from 6 onward.ThanksKD
I have a table named Prescription that consists of attributes like PatientId, MedicineCode, MedicineName, Prices of different drugs, quantity of different drugs(e.g 1,2,3,10), date .
I would like to get a summary of the total number and amount of different drugs in a specific period, the total amount of each type of drug.
I am trying to create a report on some data. I have about 8 tables and 30+ queries attached to those 15 reports. In one of those reports I want to get the percentage based on the data in the tables and queries. Say I have the minimum hours for an employee as 176 hours and the employee works for 227 hours in a month. I want to see the result in percentage.
My report looks something like this :
ID Name Oct Nov Dec Jan Feb March Total 001 alex 87.6% 104.1% 65.1% 50.2% 85.6% 002 Linda 87.4% 109.1% 68.1% 35.2% 90.8% 003 Jon 87.6% 104.1% 004 alex 87.6% 104.1% 65.1% 50.2% 85.6% 005 Linda 87.4% 109.1% 68.1% 35.2% 90.8%
For the 002 ID, though he has worked for Nov and Dec the total % is blank.
The formula that I used for all of these entries is :
=IIf(IsError([total]),"",[total])
and for the month it is : =IIf(IsError([Oct]),"",[oct]), nov and so on.
It works fine for all, but where ever there is blank in one field it doesn;t calculates for the others too..
I have a report which totals sales by customer. Then table footer has a grand total of all customer sales. I would like to get a percent of each customer's sales against the total sales. How do I get the sum from the table footer to use in an individual customer row?
I have built a query in Access that calculates the total to be charged to clients based on 3 cost columns.
I have tried to run the query in MSSQL but it will not run, so could someone help me out with the syntax in my query.
I have done about half of it myself but I am stuck on the calculation and I havent a clue of the syntax to be used.
This is my Access Query; SELECT DISTINCT Holiday_Bookings.ClientID, Holiday_Bookings.Booking_Cost, Room_Facilities.FacilityCost, Rooms.CostPerNight, Rooms!CostPerNight*Nights_Stayed+Holiday_Bookings! Booking_Cost+Room_Facilities!FacilityCost AS TotalCost, [TotalCost]*17.5/100+[TotalCost] AS [Total+VAT] FROM Room_Facilities INNER JOIN (Hotels INNER JOIN (Holiday_Bookings RIGHT JOIN Rooms ON Holiday_Bookings.ClientID = Rooms.ClientID) ON Hotels.HotelID = Rooms.HotelID) ON Room_Facilities.FacilityID = Rooms.FacilityID;
and this is what I have managed to salvage in MSSQL format: SELECT Holiday_Bookings.ClientID, Holiday_Bookings.Booking_Cost, Rooms.CostPerNight, Room_Facilities.FacilityCost FROM Rooms INNER JOIN Holiday_Bookings ON (Rooms.Clients_ID = Holiday_Bookings.ClientID) INNER JOIN Room_Facilities ON (Rooms.FacilityID = Room_Facilities.FacilityID)
USE CHEC SELECT DISTINCT DAT01.[_@051] AS Branch, DAT01.[_@550] AS LoanType, convert(varchar(10), DAT01.[_@040], 110) AS Date, DAT01.[_@LOAN#] AS LoanNum FROM DAT01 INNER JOIN [DATE_CONVERSION_TABLE_NEW] ON DAT01.[_@040] = [_@040] INNER JOIN [SMT_BRANCHES] ON DAT01.[_@051] = SMT_BRANCHES.[BranchNbr] WHERE DAT01.[_@040] Between '06/01/2006' And '06/30/2006' AND DAT01.[_@051] = '540' And DAT01.[_@LOAN#] Like '2%' And DAT01.[_@550] = '3' GROUP BY DAT01.[_@051], DAT01.[_@550], DAT01.[_@TP], DAT01.[_@040], DAT01.[_@LOAN#] ORDER BY DAT01.[_@051]
Where obviously DAT01.[_@LOAN#] is the LoanNum column.
I have the following query which gives me a list of names, logins, and group name along with a count of how many training modules each individual has sat. This returns 70 rows with each individuals details and totals.
However I want to be able to report instead just the total modules sat for all 70 rows instead of displaying each row individually. This is the query I am using:
SELECTpps_principals.name AS pname, EXT_USER_GROUPS.LOGIN, EXT_USER_GROUPS.NAME, COUNT(PPS_SCOS.SCO_ID) AS coursecount FROMPPS_PRINCIPALS LEFT JOINPPS_TRANSCRIPTS ON PPS_TRANSCRIPTS.PRINCIPAL_ID = PPS_PRINCIPALS.PRINCIPAL_ID AND PPS_TRANSCRIPTS.STATUS like '[PCF]' AND PPS_TRANSCRIPTS.TICKET not like 'l-%' AND pps_transcripts.date_created between '2006-10-01' and '2007-09-30' LEFT JOINPPS_SCOS ON PPS_SCOS.SCO_ID = PPS_TRANSCRIPTS.SCO_ID AND pps_scos.name like 'MT%' LEFT JOIN EXT_USER_GROUPS ON EXT_USER_GROUPS.LOGIN = PPS_PRINCIPALS.LOGIN WHERE pps_principals.login like '%score%' and ext_user_groups.name like 'ALL SCORE PTY' AND dbo.PPS_PRINCIPALS.DISABLED IS NULL GROUP BY pps_principals.name, EXT_USER_GROUPS.LOGIN, EXT_USER_GROUPS.NAME HAVING COUNT(PPS_SCOS.SCO_ID) > 0 ORDER BY pps_principals.name, EXT_USER_GROUPS.LOGIN, EXT_USER_GROUPS.NAME
I am trying to use SUM to get the overall total but without success so far.
I need to sum the totals for each of the ratings, then group them by owneridname. the problem i am having is that the "Count(*) as total" returns a total that includes ratings that are not listed below as a criteria.
Eg. the total for all ratings for a user john would be 789. that figure would inlcude amounts for a rating eg. dead. that is not in the list below. please help.
SELECT owneridname, SUM(CASE WHEN new_ratingname = 'Hot' THEN 1 ELSE 0 END) AS Hot, SUM(CASE WHEN new_ratingname = 'warm' THEN 1 ELSE 0 END) AS Warm, SUM(CASE WHEN new_ratingname = 'cold' THEN 1 ELSE 0 END) AS Cold, SUM(CASE WHEN new_ratingname = 'cool' THEN 1 ELSE 0 END) AS Cool, SUM(CASE WHEN new_ratingname = 'new' THEN 1 ELSE 0 END) AS New, SUM(CASE WHEN new_ratingname = 'Reservation Holder' THEN 1 ELSE 0 END) AS [Reservation Holder], SUM(CASE WHEN new_ratingname = 'site visit' THEN 1 ELSE 0 END) AS [Site Visit], SUM(CASE WHEN new_ratingname IS NULL THEN 1 ELSE 0 END) AS [Not Rated], SUM(CASE WHEN new_ratingname = 'Continous Updates' THEN 1 ELSE 0 END) AS [Cont Updates], SUM(CASE WHEN new_ratingname = 'worked tasks' THEN 1 ELSE 0 END) AS [Wkd Tasks], COUNT(*) AS Total FROM FilteredContact AS filteredcontact WHERE (statuscodename = 'active') GROUP BY owneridname
HI everyone, I been looking for the answer for sometime now, thought i would post and see if anyone could help me it. This is the output of my query.
F_DivDiv_4Div_5Div_35 424600 504000 3510114
After the row 35 i want to add a row for total. I want to add the values of column Div_4 so it would be 246+0+1. The problem i'm having is that the columns are populated from using this logic.
Select rr.F_BVT_DIV_NO, (Case When rr3.F_DIVISION_NO = 4 then Max(rr3.Total_Pol_Count) else 0 end ) + (MAX(isnull(rr2.Div4,0))) Div_4
from RNEWL_RTENTN_DETL RR --join for division 4 transfers
left join(
select rr2.F_BVT_DIV_NO, count(rr2.X_POLICY_NO) As Div4 from RNEWL_RTENTN_DETL RR2 where RR2.F_BVT_DIV_NO<>RR2.F_DIVISION_NO and month(RR2.X_POLICY_XPRTN_DT)=2 and year(RR2.X_POLICY_XPRTN_DT)=2007 and rr2.F_DIVISION_NO = 4 Group By rr2.F_BVT_DIV_NO ) rr2 on rr2.F_BVT_DIV_NO = rr.F_BVT_DIV_NO
from TRNEWL_RTENTN_DETL rr3 where month(rr3.X_POLICY_XPRTN_DT)=2 and year(rr3.X_POLICY_XPRTN_DT)=2007 Group by rr3.F_DIVISION_NO) rr3 on rr3.F_DIVISION_NO = rr.F_BVT_DIV_NO group by rr.F_BVT_DIV_NO, rr3.F_DIVISION_NO order by rr.F_BVT_DIV_NO
That code is just a little piece of it, but the logic is the same throughout. Thanks for the input!
Hopefully this is the right place to post this, I am trying to learn ADO.Net programming using VWD Express with SQL Express... I tried to do the walk-through tutorial on ADO.Net through the asp.net tutorial. The walk-through asked you to download and install a script (check) then it said to open a command window with sql server running (check) and then to copy all of this special script code into to bind the database to the web page (not check). Everything pretty much went down hill after that part. I couldn't get the Update/Cancel/Delete buttons. After that dissapointing attempt at learning I decided to just try and make my own database. All was going pretty well until I try to add the UPDATE/DELETE fields after adding the WHERE clause. No go. So, I don't know if I'm just totally flubbing this up or if this is a common problem. Do I need the two or three pages of custom script like the Microsoft example to make a database work?
1 Hi, I'm a complete newbie at this, and need some help I want to import a text file into a DB. I know how to set it up in the DTS, however when I check the DB each line from the text file is in a seperate row. What have I missed? I want the file just in one row, like its a memo.
2 What do I use to connect to a SQL DB on the web so i can see the DB and all the tables
3 Also if I want to add data to a DB is that done on the server, or do I change my copy of the DB and re-upload
I've imported an access .mbd table into SQL 7.0, and need to know the best way to overwrite my SQL 7.0 table with the new Updated information that is contained in the access table. I've read a little bit about update tables, but is there an easy way to overwrite an entire table without having to specify each row and column?
Ok, not sure if this is where i should post this, but here goes.
I am trying to setup a database to handle RMA stuff at work. I need to be able to access the DB from numerous systems at once. Now i know NOTHING about SQL or any other DB for the most part. Messed around with Access some, but nothing major.
Is there anyone that could help me, explain how, or at least point me in the right direction?
I have MS SQL server 2000. Any help, ideas, or suggestions would be great!!
Hey! I',m totally new to Ms SQL Server. I'm currently trying to import a database from LINUX using SQL+ (Oracle) to MS SQL Server. Any suggestions on how this could be achieved would be most helpful
I need to know how to write a sql statement for VB to Microsoft Access for the following criteria.
TblEmployees only Field to be concerned with is EmployeeID -Its PK TblEmpAttendane has these fields: EntryID - Pk EmployeeID - Fk Date- A text form date validated through vb to avoid hassle Value-A single digit value that can be alpha OR numeric
To get the Date range for the current month I use variables to store the First and last day of the month(in a CUSTOM calendar control).
I have been trying to use this Sql statement(that wont work): SqlString = "SELECT * FROM TblEmpAttendance WHERE Date BETWEEN '" & FirstDay & "' AND '" & LastDay & "'"
I need to: 1.)using the current EmployeeID (from TblEmployees) 2.) find the same EmployeeID in TblEmpAttendance 3.)Get the date range for the current month and year(valid days currnt mo.) -these date are supplied by the variables "FirstDay" AND "LastDay" as seen in above SqlString
4.) Find the Values associated with the dates and EmployeeID
I have a table with 10 records: 10, 9, 8, 7 ... and so on. I want to SELECT the top 3 records starting with the second record on the list (which in this case is 9.) So the result should be 9, 8, and 7. What would be the correct SQL statement for this?
I tried "SELECT TOP 3 * FROM tableXXX WHERE (FieldID<>(SELECT TOP 1 * FROM tableXXX ORDER BY FieldID DESC) ORDER BY FieldID DESC" but sure as heck it didn't work. Can anyone help?
just for understanding a simple iif statement: I like to set a numeric value to 0, if it is smaller than 0. This expression shall be valid over all dimensions for a specific measure
like Iif([Measures].[Provision Ankauf]<0,0,[Measures].[Provision Ankauf] )
it's not working, Pivottable returns ##VALUE everywhere
