I am using sql server 2005 on my local PC. I have the same copy of the database on a shared hosting environment.I need to transfer daily transaction on some tables in my local database to the remote database. My remote database ison the shared hosting environment so i don't have much control on it.
I want to capture all the DML statement on tables and then at the end of day will execute those DML statement on theremote database.Kindly suggest how can i trace DML statement on database tables.
I just want to put together a SQL statement and load all my results into a table. I don't care about sorting or specifying which columns should be shown.
My SQL Statement changes what columns are returns, and I just want the results to show everything. I don't need bells or whistles at the moment - just all the results loaded into a table, every time.
I have two sections of users data on my site, customers and employees, each one has a UserID that is FK to the UserID from aspnet_Users table.
I have a table that stores User messages
Looks Like This:
[URL]
It Has a FromUserID and ToUserID
Because Employees are Identified through EmployeeID and Customers are identified through CustomerID, I needed to use UserID's so Employees can leave messages for employees and Customers and Customers can leave messages to employees and other Customers 1-2 and 2-1
My Quandry is this, How Do I retrieve the information if the UserID might be a Customer or Might be an Employee, when the UserID could be from one of two tables
ie
How Would I Write?
SELECT wms.FromUserID, ed.FirstName, ed.LastName FROM ed INNER JOIN wms ON wms.FromUserID = ed.UserID OR SELECT wms.FromUserID, cd.FirstName, cd.LastName FROM cd INNER JOIN wms ON wms.FromUserID = cd.UserID
I need to execute a stored procedure and show the value it returns as a usertype column in a selection list. Eventually this select statment will be converted into a stored procedure, so basically the stored procedure will be called in another stored procedure. I can't quite get the syntax correct. I am showing a simplified version of a very long query so it's easy to understand. It gives me the 'Incorrect syntax near the keyword 'CREATE' error. The capitalized the code produces the error:
While executing select statement for a table then iam getting arithmetic overflow error.I found that retrieviing the data for a particular column showed that error ,sum() for that column is showing error.Is there any way i can rectify this problem.
i am writing storedprocedure for creating salary table ,but getting error while executing sp.
USE [Crystal.HRM] GO /****** Object: StoredProcedure [dbo].[Hrm_Salary] Script Date: 01/19/2011 13:11:02 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO
CREATE PROCEDURE [dbo].[Hrm_Salary]( @Pay_Basic char(1), @Pay_Hra char(1) ) AS BEGIN declare @fields nvarchar(max) set @fields='Hrm_Emp_Id [int] not null' if(@Pay_Basic='Y') Begin set @fields=@fields+', Pay_Basic [int] NOT NULL CONSTRAINT [DF_Hrm_Emp_Salary_Pay_Basic] DEFAULT ((0)),' end if(@Pay_Hra='Y') Begin set @fields=@fields+', Pay_Hra [smallint] NOT NULL CONSTRAINT [DF_Hrm_Emp_Salary_Pay_Hra] DEFAULT ((0)),' End set @fields=substring(@fields,0,len(@fields)-2) declare @sql nvarchar(max) set @sql='CREATE TABLE [dbo].[Salary]('+ @fields+' CONSTRAINT [PK_Payroll] PRIMARY KEY CLUSTERED ( [Pay_Emp_Id] ASC )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] GO USE [Crystal.HRM] GO ALTER TABLE [dbo].[Hrm_Emp_Salary] WITH CHECK ADD CONSTRAINT [FK_Hrm_Emp_Salary_Hrm_Emp_Tbl] FOREIGN KEY([Pay_Emp_Id]) REFERENCES [dbo].[Hrm_Emp_Tbl] ([Emp_Id])' exec sp_ExecuteSql @sql END
have been working this issue for the last two days and I cannot for life of me track down the problem. I am using an Update parameter statement to update records in a sql db. I have done all the debugging in VWD but I get no specific error line details. I think it is a mismatch type with the primary key field. Below is the stacktrace:
[SqlException (0x80131904): Error converting data type varchar to numeric.] PowerUsers_OfficerUpdate.btnSave2_Click(Object sender, EventArgs e) in C:UsersxxxxxxDocumentsVisual Studio 2010WebSitesWebSite3xxxxxxxx.aspx.vb:106 System.Web.UI.WebControls.Button.OnClick(EventArgs e) +118 System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +112 System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +10 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +13 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +36 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5563
I have developed a Portal (Request submission) which sends an Email and also it updates all values to the Database. What ever values gets updated to the database, will be sent as an email message to an address. It works fine. I need a Unique number to be generated for a Request submission which has to be sent in the Email message and also it has to be updated in the database. I use SQL Server 2005 and Framework 2, C# laungauge.
I am doing a data warehouse project.I have two tables tblA (id, type) and tblB(city, no_crimes, type).I want to create (insert) a number of rows based on the value of no_crimes.For Example, in tblB(Leeds, 2000, murder). SO, I need to insert 2000 rows into tblA by a single INSERT statement (not 2000 statements).
have a very important issue,i have three Stored Procedures Sp1,Sp2 and Sp3 .the first one (Sp1) will execute the second one (Sp2) and save returned data into @tempTB1 and the Second one will execute the third one (Sp3) and save data into @tempTB2.if I execute the Sp2 it will works and it will returned me all my data from the Sp3 ,but the problem is in the Sp1, when i execute it it will display this Error:INSERT EXEC statement cannot be nested I tried to change the place of execute Sp2 and it display me another error:Cannot use the ROLLBACK statement within an INSERT-EXEC statement.
I'm getting the error "Cannot Find Table 0" when executing an INSERT INTO sql statement. The fishy part is that the code half works -- the new information is submitted to the database. Here the code in question:
I need to display the table name in the select statement. how?
exact question:
we have common columns in two tables. we are displaying the records by using
select column_name from table_name_1 union select column_name from table_name_2
But the requirement is, we need to display the source table_name along with the data. consider a,c are present in table_1 and b,d are present in table_2. we need the output in the following way
eg: column_name table_name a table_1 b table_2 c table_1 d table_2
anyone know that can we used select statement to called queries table in database or not?usually people used select statement to called common table in database.
I happen to have used datasets and hence data tables in most of data access approaches.
This time arround i want to exctract the integer value and use it in the if statement for testing.
I however face a problem in the fact that though i can select the particular cell in a table i still get a table adapter which is not what i need and is not covertible to int value.
See the code below:
[code]....
How can I extra this particular xFiled value and return and integer value rather than a data table?
The following stored procedure updates the value in LeaveTransaction table.
[code].,...
My Question here:
I have written the above trigger,which would update the value for the corresponding records in another table ie) LeaveCumulative table. The trigger is not updating the value in the designated rows in the leavecumulative table. Whereas when I excute the same update command separately in the Query Editor window the trigger is working fine.
my main intention is that, the exsisting table of sql server database(.dbo) with 1000's of records, that should be import to a new access database table(.mdb).for example if we want to had a new table in db2, of exsisting table in db1 with some conditions by using "select * into New_Tabel from (select * from Exsisting_Table where <Condition>) as objectName". Here the new table is created and records inserted in one execution in db2. The same work should be done for access(sqlserver db --> Access db).Here the new access database table with given name (as string_sysdate) must be created dynamically by clicking a button (multiples time creating new table).Is there any query or c# code for sql server database to access database.
At University it was suggested to us that the best way to learn about designing and implementing databases is to practice the art. So I chose the following statement to implement and refine my skills:
Harpers Hauliers deliver shipments on contract for customers. Each contract is identified by a unique contract number. The contract may require a single or many shipments. Each shipment must be allocated resources of a vehicle (regisatration number, type, volume, weight), which is suitable for the shipment and a driver (name, address, telephone number) who is licensed to drive that type of vehicle.
As soon as a customer (companyname, address, telephone number) makes an enquiry, a pending contract is set up. The contract is then negotiated on price between the customer and the haulier. Not all pending contracts are successfully negotiated. Either the customer or the haulier may back out at this stage. If the haulier has previously carried for this customer and financial settlements were not made to his satisfaction, he may reject the contract. He may also reject the contract on the basis that he thinks the workload being undertaken is too big a risk for his company with this customer that is the contract is worth more than he is willing to risk for this customer.
An agreed contract requires the haulier to deliver the listed shipments between a start and an end date, at an agreed price. The haulier always takes out an insurance policy on the contract, in case he cannot allocate enough resources to the contract to get it delivered on time.For each shipment in the delivery there is an origin and destination. The shipment has a description, a weight and a volume. The haulier has a fleet of vehicles of different types and a staff of drivers with different license types, to match the vehicle types. During the contract, the haulier tries to allocate vehicle, driver and time-slot resources to each ship-ment. The vehicle must have a suitable volume and weight capacity and the driver must have a suitable license to drive that vehicle.
The time must be when the driver and vehicle are free and before the contract end date. As each shipment is delivered, it is marked off as delivered by the driver, giving the date and time of delivery. If not all shipments are delivered by the contract end date, the con-tract becomes overdue, incurring a penalty cost on the haulier. When all shipments are delivered, the haulier invoices the customer for the required amount. Each invoice is has a date and is identified by a unique invoice number . Payment from the customer may be in full or may be partial. The contract is kept active until all payments have been made against it.
And here is the ERD that I have come up with. I have created the ERD in Visio 2010.
[IMG]http://i56.tinypic.com/6s43t4.jpg[/IMG]
Can someone who's an expert in Database design kindly check my ERD and see if it perfectly matches the Problem Statement.
I've build a query, but the where statement is doing strange things. First my query:
select * from ( SELECT 1 as Tag, null as Parent, row_number() OVER (ORDER BY tblOccasion.ID DESC) AS [tblOccasion!1!rownum], FROM tblOccasionwhere tblOccasion.UserID = @UserName and ((tblOccasion.MerkDesc like '%' + @search + '%')or(tblOccasion.UitvoeringDesc like '%' + @search + '%')or(tblOccasion.UitvoeringDesc like '%' + @search + '%')) ) AS A where a.[tblOccasion!1!rownum] BETWEEN (@startRowIndex) AND (@startRowIndex+@pageSize) order by a.[tblOccasion!1!MerkDesc!Element],a.[tblOccasion!1!TypeDesc!Element],a.[tblOccasion!1!UitvoeringDesc!Element] FOR XML EXPLICIT;
But the result should be ordered by Merkdesc, then TypeDesc, and then as last the uitvoeringDesc. So for testing i only used the MerkDesc. The strange thing is that i get the result:
BMW BMW BMW Opel Volvo
But this was page 1, on page 2 (paging) you got BMW again... so he takes the first 5 rows ore something and then the order. But i like that he first orders it by MerkDesc and then picks the first 5. How can i change that, i can't add a Order by in the inline sql statement i get this error: Msg 1033, Level 15, State 1, Procedure MijnAdvertenties, Line 47 De ORDER BY-component is ongeldig in weergaven, in line functies, afgeleide tabellen en algemene tabelexpressies, tenzij ook TOP of FOR XML is opgegeven.
