I've two databases with different structures, i want to add a record with it's data from the first database into the second database in the place i define, this action must happen automatically as soon as the record is created in the first database.
View 1 RepliesHI,
I have to send an automatic e-mail based on database table .Could any body help me
how to write stored procedure and where to execute it.How to send an automatic e-mail through sql server?
Thanks in advance.
Regards,
Raja.
Hi,
I have to send an automatic mail alert from sql server.
I have to send mail based on one table.In that particular table
expiry date as one column and I have to send mail each time it
has to check the expirydate column automatically and send mail.
please help me and could any body can send me piece of code.
Thankyou in advance
regards,
Raja.
I am duplicating a record from my asp.net page in to the database. When i click on save I am getting the following error message
Violation of PRIMARY KEY constraint 'PK_clientinfo'. Cannot insert duplicate key in object 'clientinfo'. The statement has been terminated.
The above message i am getting since i have tried to duplicate the clientname field of my table which is set as the primary key.
What i want is instead of this message in the browser i need to display an alert saying "the clientname entered already exists" by checking the value from the database.
Here is my code. pls modify it to achieve the said problem
if(Page.IsValid==true) { conn.Open(); SqlCommand cmd = new SqlCommand("insert into clientinfo (client_name, address) values ('"+txtclientname.Text+"', '"+txtaddress.Text+"')", conn); cmd.ExecuteNonQuery(); conn.Close(); BindData(); txtclear(); System.Web.HttpContext.Current.Response.Write("<script>alert('New Record Added!');</script>"); Response.Redirect("Clientinfo.aspx"); }
Can you use the below query to get CPU high utilisation alert purposes for both named and default instance? or, do I need to make any changes here (@wmi_namespace=N'.ROOTCIMV2' ) ?
USE [msdb]
GO
EXEC msdb.dbo.sp_add_alert @name=N'CPU_WM_Utilization_Check',
@message_id=0,
@severity=0,
[code]....
How can i make a sum (concatenation) of strings of one column in a table.
for example i have a table like this
field1 field2
1 abc
1 bcd
2 sdf
2 sdd
I want to get these strings added group by field 1
Thanks
For an existing table containing data, how would you add a primary key field, that contains a value for each row?
(Under Access I'd just add a new field and set it to type AutoNumber, but you can't do that in SQL Server)
Hi Everybody,
I hv executed the following query in the Query Qnalyzer
(Ofcourse I am Using SQL 2000 Enterprise Edition) and Surprisingly
I am getting the Output as follows,
query:-
select '5' + 10
Output:- 15
Can anybody please tell me, why it is happening like this? OR Did I miss any Configuration Parameter in the Server Settings?.
Tks in Advance,
Sam
Hi
Is there anyway to perform a addition on two items in a COUNT query?
For example if i use the followig query:
SELECT
Region
,COUNT(*)
FROM Regions
GROUP BY Regions
It would give me the following results:
Region1 22
Region2 33
Region3 21
Region4 4
Region5 9
Region6 61
However I would want it display the the combined totals Region1 and Region2 together so it would give the figure of 55.
Is this possible?
Thanks
i have the idea that 23+null=null
now following is my case with 2 columns as:
salary bonus
1 null
2 5
3 null
i have to find the total salary(i.e. salary+bonus)
this is how i managed to do it:
select empname,(salary + commission) as 'total salary'
from emp
where commission is not null
union
select empname,salary from emp
where commission is null
now my question is,wht if the case now is different as below:
salary bonus hra
1 null 6
2 5 null
3 null null
now how do i calculate total salary(i.e. salary+bonus+hra)
am a beginner
help appreciated
cheers
hi guys,
I have got something like this,
A S D
Bi Ar 23
Bi Ar 12
As Ar 52
As Ar 11
Ap Jo 24
I Have 3 columns. First 2 columns are Text and the third one is Number. I need to see if any duplicate values are there in first 2 columns. If so then i need to add the values of 3 columns and make it one. So the output should look like this,
A S D
Bi Ar 35
As Ar 63
Ap Jo 24
Hi I have a query, what I would like to do is create a column that takes the results in two coulms and add them together:
Col A Col B Col C
Row1 1 1 2
Row2 2 3 5
Here is the query
declare @t table( player_name varchar(100), BuyIn int, TopUp int, ReBuy int, Winnings int, Events int, Test int)
INSERT INTO @t (player_name, TopUp)
SELECT Player_name, SUM([Top-ups]) AS TOPUPS
FROM (SELECT Event_data.Transaction_type, Players.Player_name, Events.Top_up, Event_data.Transaction_value,
Events.Top_up * Event_data.Transaction_value AS [Top-ups]
FROM Event_data INNER JOIN
Events ON Event_data.Event_id = Events.id INNER JOIN
Players ON Event_data.Player_id = Players.Player_id
WHERE (Event_data.Transaction_type = 2)) AS Topups
GROUP BY player_name
INSERT INTO @t (player_name, ReBuy )
SELECT Player_name, SUM([Re-buys]) AS REBUYS
FROM (SELECT Event_data.Transaction_value, Players.Player_name, Events.Rebuys, Event_data.Transaction_value * Events.Rebuys AS [Re-buys]
FROM Event_data INNER JOIN
Events ON Event_data.Event_id = Events.id INNER JOIN
Players ON Event_data.Player_id = Players.Player_id
WHERE (Event_data.Transaction_type = 3)) AS REBUYS
GROUP BY Player_name
Insert into @t (player_name, BuyIn)
SELECT dbo.Players.Player_name, SUM(dbo.Events.Buy_in) AS BuyIn
FROM dbo.Players INNER JOIN
dbo.Event_data ON dbo.Players.Player_id = dbo.Event_data.Player_id INNER JOIN
dbo.Events ON dbo.Event_data.Event_id = dbo.Events.id
GROUP BY dbo.Players.Player_name, dbo.Event_data.Transaction_type
HAVING (dbo.Event_data.Transaction_type = 1)
ORDER BY SUM(dbo.Events.Buy_in) DESC
Insert into @t (player_name, Winnings)
SELECT dbo.Players.Player_name, SUM(dbo.Event_data.Transaction_value) AS Winnings
FROM dbo.Players INNER JOIN
dbo.Event_data ON dbo.Players.Player_id = dbo.Event_data.Player_id
GROUP BY dbo.Players.Player_name, dbo.Event_data.Transaction_type
HAVING (dbo.Event_data.Transaction_type = 1)
insert into @t (player_name, Events)
SELECT dbo.Players.Player_name, COUNT(dbo.Event_data.Place) AS Expr1
FROM dbo.Players INNER JOIN
dbo.Event_data ON dbo.Players.Player_id = dbo.Event_data.Player_id INNER JOIN
dbo.Events ON dbo.Event_data.Event_id = dbo.Events.id
GROUP BY dbo.Players.Player_name
HAVING (NOT (COUNT(dbo.Event_data.Place) IS NULL))
insert into @t (player_name, test)
select player_name, ((TopUp) + (Rebuy)) as Test
from @t
SELECT player_name, min (BuyIn) as BuyIn, min(TopUp) as TopUps, min(ReBuy)as ReBuy, min(Winnings) as Winnings, min(Events) as Events, min(test) as test
FROM @t
GROUP BY player_name
ORDER BY BuyIn DESC
--ORDER BY TOPUPS DESC
END
THis is where I attempt to add the coloms but I get a null result
insert into @t (player_name, test)
select player_name, ((TopUp) + (Rebuy)) as Test
from @t
any help would be great.
I am trying one of the examples from the .NET Framework 2.0 Web-Based Client Development and I'm having a problem authenticating. I'm running everything on my local computer (IIS, SQL 2005 STANDARD ADDITION and Visual Studio). While in Visual Studio (in other words, debug mode) when I run the example, I get the following error:
Cannot open database "pubs" requested by the login. The login failed.Login failed for user 'DPJ-6E95AFFA416ASPNET'.
This could be more of a SQL question that a ASP.NET question, but I'm not sure. I'm running under the local administrator account, but this seems to indicate that ASP is using it's own credentials to access SQL. This is my first time using these tools, and I'm guessing this is a fairly well know issue.
I was looking for a "User Admin" function in SQL and could not find it, so I'm a bit lost here.
Can anyone help please.
Thanks
I have a form set-up and am trying to add multiple items in my checkbox to the database; however, the only thing added to the database is the first item checked. Here is the code and any help will be greatly appreciated. Thank you!:<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:ConConnectionString %>" InsertCommand="INSERT INTO Consultations(FullName, Email, Business, BusinessInfo, Services, Comments) VALUES (@FullName, @Email, @Business, @BusinessInfo, @Services, @Comments)"
SelectCommand="SELECT Consultations.* FROM Consultations">
<InsertParameters>
<asp:ControlParameter Name="FullName" Type="String" ControlId="fullname" PropertyName="Text" />
<asp:ControlParameter Name="Email" Type="String" ControlId="emailTextBox" PropertyName="Text" />
<asp:ControlParameter Name="Business" Type="String" ControlId="bisDropDownList" PropertyName="SelectedValue" />
<asp:ControlParameter Name="BusinessInfo" Type="String" ControlId="businessTextBox" PropertyName="Text" />
<asp:ControlParameter Name="Services" Type="String" ControlID="checkboxlist" PropertyName="SelectedValue" />
<asp:ControlParameter Name="Comments" Type="String" ControlID="commentsTextBox" PropertyName="Text" />
</InsertParameters>
</asp:SqlDataSource>
I need to up my license limit on a SQL box from 1 to 2 user connections allowed. Where do I do this?
View 1 Replies View RelatedHi,
I have several INT columns in a table that I need to update.
For example, in column 'aa' I need to add 2 to all of the values in that column.
I'm using Query Analyzer - what update statement should I write?
@quantity is the number that the user has input in the txtbox. I want to use this value to ADD together with the current quantity value in the shopcartitem table. I do not know how, my current query only replaces the old quantity with the new .. can someone help me? strSql = "UPDATE ShopCartItem sci SET sci.Quantity" & _
"WHERE sci.ProductID=@ProductID AND sci.ShopCartID=@ShopCartID"Dim cmd2 As New SqlCommand(strSql, conn)
cmd2.Parameters.AddWithValue("@Quantity", quantity)cmd2.Parameters.AddWithValue("@ProductID", productId)
cmd2.Parameters.AddWithValue("@ShopCartID", ShopCartID)
Hi,
is there a way to perform an addition to the value of a report parameter?
so like Param Value + 1 = 6
where Param Value would be 5?
Are there any differences between the Developer Addition and Enterprise Addition of 64 Bit SQL Server 05 that would prevent me from testing full capabilities of my production Enterprise Addition in a QA environment utilizing Developer Addition? Any known restrictions on number of DB connections, memory, CPUs etc??
View 4 Replies View RelatedHow can I check for Null for the amounts if no records are returned in either select. Basically it errors out if one or both of the Amounts return no records. I need to do some sort of IF statement to set one of the amounts or both amounts to zero in those cases so it doesn't error out on me
SELECT (Coalesce(pd1_Amount, 0) + Coalesce(PD2_Amount, 0)) as Amount
FROM
(
SELECT pd.Amount as pd1_Amount
FROM Master m (NOLOCK)
LEFT JOIN dbo.pdc pd ON pd.number = m.number
INNER JOIN dbo.Customer c ON c.Customer = m.Customer
WHERE pd.Active = 1
AND pd.Entered BETWEEN DATEADD(DAY, -DATEPART(DAY, @FirstDayMonthDate) + 1, @FirstDayMonthDate) AND DATEADD(DAY, -DATEPART(DAY, @FirstDayMonthDate), DATEADD(MONTH, 1, @FirstDayMonthDate))
AND pd.Entered <> '1900-01-01 00:00:00.000'
AND pd.Deposit BETWEEN DATEADD(DAY, -DATEPART(DAY, @FirstDayMonthDate) + 1, @FirstDayMonthDate) AND DATEADD(DAY, -DATEPART(DAY, @FirstDayMonthDate), DATEADD(MONTH, 1, @FirstDayMonthDate))
--AND pd.Deposit IS NOT NULL
--AND pd.OnHold IS NULL
AND c.customer <> '9999999'
UNION
SELECT pdd.Amount as PD2_Amount
FROM Master m (NOLOCK)
LEFT JOIN dbo.pdcdeleted pdd ON pdd.number = m.number
INNER JOIN dbo.Customer c ON c.Customer = m.Customer
WHERE pdd.Entered BETWEEN DATEADD(DAY, -DATEPART(DAY, @FirstDayMonthDate) + 1, @FirstDayMonthDate) AND DATEADD(DAY, -DATEPART(DAY, @FirstDayMonthDate), DATEADD(MONTH, 1, @FirstDayMonthDate))
AND pdd.Entered <> '1900-01-01 00:00:00.000'
AND pdd.Deposit BETWEEN DATEADD(DAY, -DATEPART(DAY, @FirstDayMonthDate) + 1, @FirstDayMonthDate) AND DATEADD(DAY, -DATEPART(DAY, @FirstDayMonthDate), DATEADD(MONTH, 1, @FirstDayMonthDate))
--AND pdd.Deposit IS NOT NULL
--AND pdd.OnHold IS NULL
AND c.customer <> '9999999'
) as PDC_Main
Hi,
I was wondering if you can help.
In my vb.net form I am running a query to insert data from one database table to another.
However what I need to do is to be able is to add the id of a record I have just created to this insert into sql command.
I have managed to use @@ identity to get the id of my first sql insert statement
but I am wondering how I can use it in the second insert into and select statement. At the moment my sql statement just copies exactly what is in the select statement. I can't figure out how to add the @@identity value to my second sql statement.
My second sql statement is a follows:
sql2 = "INSERT INTO ProjectDeliveData(ProjId,ProjDeliveId,RoleId,MeasurementId,ProjDeliveValue, ProjDeliveComments,ProjDeliveYear,FinDataTypeId, FinFileId, ProjDeliveMonthFrom, ResourceId,ProjDeliveDateAdded)" & " select ProjId,ProjDeliveId,RoleId,MeasurementId,ProjDeliveValue, ProjDeliveComments,ProjDeliveYear,FinDataTypeId, FinFileId, ProjDeliveMonthFrom, ResourceId,ProjDeliveDateAdded from ProjectDeliveData where FinFileId=" & strActualFileId
I hope you can help
Cheers
Mark :)
Dear all participants,
I manage complete online system for two locations, connected via dialup lease line. I have to used merge replication for the same data should be at both locations. It is working fine, I don’t like disturbe it but current requirement is addition of two column in one table which one as also use in merge replication.
How I can add aforesaid columns in table x, column name c1 numeric , c2 character?
What is impact on replication?
Let me know step by step.
Thanks in advance.
Thanks R.Mall
Can any help me addition of article in runnning merge replication in MSSQL SERVER 2000 with SP3.
Any step by step help is realy great for me.
Thanks
R.Mall
SQL Server allows addition of two "datetime" values. For example let d1='April 10,2004' and d2='April 11, 2004'. Now when I execute d1+d2 I get 2108-07-20 00:00:00.000. On what logic is this calculation done.
I am actually looking to derive the same result in Oracle. Hence wanting to know the funda behind this.
many thanks.
My goal is to be able to include test information in the change request that will make the operations manager confident that the change can proceed. Right now all I can think of is to add the index in dev and staging and compare some before and after stats. But I am concerned that an index change might also affect other operations besides SELECTs - such as updates, transactions, or scheduled jobs.
[URL]
We are using SSRS 2012. Before we were with 2005. Both the versions have the problem. When a change made to parameters (like adding additional parameter to existing report or changing default value of parameter) using visual studio on developers machine and re-deploy to server, additional parameter or changes to parameter is not showing up. So, we are first deleting the report from server and then re-deploying. This works fine. But we realized that we are losing history of the report (like data related to this report run from execution log). We need the history of report. It is very important to top management to see the details.When I searched in forums, users say that it still exist in 2012 and 2014 and every one talks about deleting and re-deploying report. I would like to know if there is any other alternate route to redeploy additional parameter and not lose history.
View 6 Replies View RelatedI have an SSIS package (TransAgentMaster) that I recently modified to include a call to a child package via the file system. The child package creates a text file. When I run the package in dev studio then the child package/text file is produced.
I then imported the TransAgentMaster as a stored packagesfilesystem package into SQL SSIS and executed the package. The child package produced the text file.
I then ran the SQL Server Agent to see if the child package would work and it did not generate the text file. Thus after updating a SSIS package importing the package into SSIS the job that calls the package will not call the child package. Please not that the TransAgentMaster package calls 7 children packages €¦ just not my new one.
Any thoughts why the agent will not run the child newly crated childe package?
Folks, i want an alert to be displayed by SQL when the processor shoots above 80% constantly for 30 seconds. Is there any perfomance counter alert sepcifically for this purpose?
Howdy!
Hi there
I've created the new alert by going to use the following stored procedure:
EXEC msdb.dbo.sp_add_alert @name=N'Severity
19 Error',
@message_id=0,
@severity=19,
@enabled=1,
@delay_between_responses=180,
@include_event_description_in=7,
@category_name=N'[Uncategorized]',
@job_id=N'00000000-0000-0000-0000-000000000000'
GO
But the result has a strange characters between 'Severity' and '19 Error' in sysalerts column. So I can't even drop this by using sp_delete_alert.
My question is how to access and modify the sysalerts system table cause I don't think you can access system table anymore?
BTW ... I am using the 90.00.3042 (Sp2).
Thanks
Hi,
I have to generate an automatic mail alert based on the sql server database table.In that
table has expiry dates baed on that dates ,I have to send an automatic mail through sql server.
please give solution for this issue.
Thanks in advance.
Regards,
Raja.
I'm trying to develop a email alert feature on my project. I was trying to approach with SQL trigger wich I think is the best option. Basically, when a new record is inserted into ad table, a email alert goes to peolpe who selected to receive alert wehen certain conditions are met. What would be the best approach? any examples?Thanks
View 2 Replies View RelatedI have set up an alert to detect when Page Deadlocks rise above 0. Overnight I have DTS packages populating SQL Server and various other jobs (Cognos Cube Builds etc.). My alert detected a Deadlock during the night but all of my processes completed fine. My problem/misunderstanding is that my alert is still popping up every 5 mins saying there is a Deadlock yet there is nothing running or no-one accessing SQL server and I cannot see any trace of the Deadlock in the Current Activity. Is this normal or is it a bug?
View 1 Replies View RelatedI have two questions:
1) how can I get an alert (via paging or email) when the SQL server is down ?
2) how can I get an alert ( via paging or email) when the SQL Agent goes
down?
I'm a novice SQL Server DBA.
thanks!