Queue Insert Statement What Is The Best SQL Server 2005 Feature To Use?
Apr 4, 2007
Hello everybody, I'm not completly aware of the SQL server 2005 possibilities so I'd need an hints from somebody with a wide knowledge to understand the direction to take!
This is what I have to do.
I insert into a table XML message. the messages are pushed automatically by an application I have no ""control" on and I get several messages "at the same time".
Everytime the message is inserted into the database I need to trasform the XML data into the correspondent relational value and I know already that in some cases it could take a while (1 second can be considered a while..)
My worry is that in the moment I process one message I loose the other one inserted after ,,,
There is some tool that helps me to handte the process as I would..
I was looking into SQL service broker?
It can be the right choice?
Thank you for any help!!
Marina B.
View 1 Replies
ADVERTISEMENT
Oct 10, 2007
Hello,
This seems like such a simple problem but I am new developer even through I have been on the administration end of things for some time. I will go into more detail about my tables and there relationships below. Anyway, I am trying to create a many-to-many relationship within ms sql server 2005. I have created both of my primary tables and also a junction table per the directions on microsoft's website all per ms's instructions as stated here...
http://msdn2.microsoft.com/en-us/library/ms178043.aspx
At then end of these instruction it states as a NOTE: The creation of a junction table in a database diagram does not insert data from the related tables into the junction table. For information about inserting data into a table, see How to: Create Insert Results Queries (Visual Database Tools).
http://msdn2.microsoft.com/en-us/library/ms189098.aspx
and these directions do not go into detail on how to do an insert on a junction table. And I cant find out how to do this anywhere on the internet... I did create a T-SQL INSERT statement in a trigger as listed below but I end up getting an error AS LISTED BELOW....
Here is how I set everything up...
PetitionSet table consists of:
PetitionSetID int auto-increment primary key
PetitionSetName varchar(50) no nulls
PetitionSetScope varchar(50) no nulls
the Petition table consists of:
PetitionID int auto-increment primary key
PetitionSetID int no nulls
PetitionName varchar(50) no nulls
the SetToPetitionJunction table consists of:
PetitionSetID int
PetitionID int
And, there is a composite key made up of both the PetitionSetID and PetitionID fields.
I have created the foreign key relationships with DEFAULT VALUES from the SetToPetitionJunction table to each column's respective corresponding column in each of the tables: PetitionSet and Petition.
The trigger is on the Petition table and it has the following code:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
-- =============================================
-- Author: Name
-- Create date:
-- Description:
-- =============================================
ALTER TRIGGER .[SetToPetitionJunctionTrigger]
ON .[dbo].[Petition]
AFTER INSERT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
INSERT INTO SetToPetitionJunction
(PetitionID, PetitionSetID)
SELECT Petition.PetitionID, PetitionSet.PetitionSetID
FROM Petition INNER JOIN
PetitionSet ON Petition.PetitionSetID = PetitionSet.PetitionSetID
END
I have created an asp.net 2.0 front end to insert values into the PetitionSet table and the Petition Table. And in the detailsview for the Petition table I manually insert the PetitionSetID field to the number that corresponds to an auto-generated number on the primary key of the PetitionSet table. So I am maintaining referential integrity...
The first time it works and inserts one record in the Junction table containing the PetitionSetID from the PetitionSet table and the PetitionID from the petition table.
Then when I try to add in another petition for the same petition set number just like I did the first time and then I get this error...
Violation of PRIMARY KEY constraint 'PK_SetToPetitionJunction'. Cannot insert duplicate key in object 'dbo.SetToPetitionJunction'.
The statement has been terminated.
David
All Rights Reserved in All Media
View 3 Replies
View Related
Aug 15, 2006
I use the Microsoft SQL Server 2005 JDBC Driver (1.0.809.102 and 1.1.1320.0) to connect to a SQL Server 2005 database. I'm currently implementing a generic data access layer that executes an arbitrary SQL statement:
public void prepareQuery(String sql) throws SQLException, ClassNotFoundException {
PreparedStatement stm = getConnection().prepareStatement(sql);
ParameterMetaData pmd = stm.getParameterMetaData();
int numPar = pmd.getParameterCount();
System.out.println("Number of parameters: " + numPar);
// ... acquire and process 'numPar' parameters ...
}
Exemplarily, I created a table named 'TEST_TABLE' with three Integer columns ('C1', 'C2' and 'C3') and a Varchar column ('C4'). Calling
prepareQuery("INSERT INTO [TEST_TABLE] ( [C1], [C2], [C3], [C4] ) VALUES ( 1, 2, ?, ? )")
gives the following result:
Number of parameters: 4
This is definitely wrong because that statement has only two parameters, one of type Integer and one of type Varchar. How can I get the correct number and types of the parameters?
View 3 Replies
View Related
Apr 26, 2006
Hi,
I have installed sql server standard edition 2005.I want to enable service broker feature or services after intalling the sql server.Please let me known any tool or utiltiy to enable the feature
Alter datatabase set enable_broker is not working.
Its giving syntax error
Thanks
View 1 Replies
View Related
Feb 22, 2007
I tried to insert large pool of data from table A to table B. Table B is then exported to Excel for viewing purpose. However, i found that some of the rows are not inserted in order, they seemed to be inserted in between other rows that are inserted before them. May I know what is the problem? There is no key assigned to table B. Do we need to disable key in order to have items inserted in sequence order?
in other word, instead of "insert", can we "append" records?
View 9 Replies
View Related
Nov 16, 2006
Some of the individual installs on the "Feature Pack for Microsoft SQL Server 2005 Service Pack 2 - Community Technology Preview (CTP) November 2006" page available here:
http://www.microsoft.com/downloads/details.aspx?familyid=7a9ad90f-7f95-4369-a206-e84053d63fd3&displaylang=en
say "requires Microsoft Core XML Services (MSXML) 6.0, also available on this page". However, there is not an XML Services install on the page.
Is it included in one of the installs or not needed if you're already running SP1?
Thanks,
Terry
View 1 Replies
View Related
May 1, 2008
Hi all,
i m using VS 2005 and I have to display records with feature of INSERT / DELETE ITEMS But when i connect to Sql Server Database and select * from columns but here when clicking the "Advance" button , i do not get "Advance Sql generation Option " highlighted. Instead , it is turned off. i.e
The Following options are not highlighting
------ Generate Insert, Update, Delete statements
------ use optimistic concurrency
Plz guide me anyone..... is anything wrong with our VS 2005 software installed?
Bilal
View 4 Replies
View Related
Oct 24, 2006
Hi,
I downloaeded "Microsoft SQL Server 2005 Datamining Viewer Controls" feature pack, and tried to run the dataminingviewerclient.exe.
When i view my local time series data mining model, It works well for single series model, but when i load a Microsoft time series model containing multiple data series, an unhandled exception occurs.
I post the error message below:
See the end of this message for details on invoking
just-in-time (JIT) debugging instead of this dialog box.
************** Exception Text **************
System.NullReferenceException: Object reference not set to an instance of an object.
at Microsoft.AnalysisServices.Viewers.TimeSeriesViewer.PreprocessChartArrays(ArrayList rgPredicted, ArrayList rgActual, ArrayList rgDeviations)
at Microsoft.AnalysisServices.Viewers.TimeSeriesViewer.LoadChartSpace(MiningModel model)
at Microsoft.AnalysisServices.Viewers.TSCheckedListViewCombo.HandleChangesCommitted(Object sender, EventArgs ea)
at Microsoft.DataWarehouse.Controls.BaseControlComboBox.OnCommittedChanges(EventArgs e)
at Microsoft.DataWarehouse.Controls.BaseControlComboBox.SetSelection(String str, Object obj)
at Microsoft.AnalysisServices.Viewers.TimeSeriesViewer.InitializeCurrentlySelectedPage(Object context)
at Microsoft.AnalysisServices.Viewers.TreeViewer.viewerTabControl_SelectedIndexChanged(Object sender, EventArgs e)
at System.Windows.Forms.TabControl.OnSelectedIndexChanged(EventArgs e)
at System.Windows.Forms.TabControl.WmSelChange()
at System.Windows.Forms.TabControl.WndProc(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
************** Loaded Assemblies **************
mscorlib
Assembly Version: 2.0.0.0
Win32 Version: 2.0.50727.42 (RTM.050727-4200)
CodeBase: file:///E:/WINDOWS/Microsoft.NET/Framework/v2.0.50727/mscorlib.dll
----------------------------------------
DataMiningViewerClient
Assembly Version: 1.0.2023.16746
Win32 Version: 1.0.2023.16746
CodeBase: file:///E:/Documents%20and%20Settings/admin/Desktop/Microsoft%20Download/DataMiningViewerClient/DataMiningViewerClient.exe
----------------------------------------
System.Windows.Forms
Assembly Version: 2.0.0.0
Win32 Version: 2.0.50727.42 (RTM.050727-4200)
CodeBase: file:///E:/WINDOWS/assembly/GAC_MSIL/System.Windows.Forms/2.0.0.0__b77a5c561934e089/System.Windows.Forms.dll
----------------------------------------
System
Assembly Version: 2.0.0.0
Win32 Version: 2.0.50727.42 (RTM.050727-4200)
CodeBase: file:///E:/WINDOWS/assembly/GAC_MSIL/System/2.0.0.0__b77a5c561934e089/System.dll
----------------------------------------
System.Drawing
Assembly Version: 2.0.0.0
Win32 Version: 2.0.50727.42 (RTM.050727-4200)
CodeBase: file:///E:/WINDOWS/assembly/GAC_MSIL/System.Drawing/2.0.0.0__b03f5f7f11d50a3a/System.Drawing.dll
----------------------------------------
Microsoft.AnalysisServices.AdomdClient
Assembly Version: 9.0.242.0
Win32 Version: 9.00.2047.00
CodeBase: file:///E:/WINDOWS/assembly/GAC_MSIL/Microsoft.AnalysisServices.AdomdClient/9.0.242.0__89845dcd8080cc91/Microsoft.AnalysisServices.AdomdClient.dll
----------------------------------------
System.Data
Assembly Version: 2.0.0.0
Win32 Version: 2.0.50727.42 (RTM.050727-4200)
CodeBase: file:///E:/WINDOWS/assembly/GAC_32/System.Data/2.0.0.0__b77a5c561934e089/System.Data.dll
----------------------------------------
System.Xml
Assembly Version: 2.0.0.0
Win32 Version: 2.0.50727.42 (RTM.050727-4200)
CodeBase: file:///E:/WINDOWS/assembly/GAC_MSIL/System.Xml/2.0.0.0__b77a5c561934e089/System.Xml.dll
----------------------------------------
System.Configuration
Assembly Version: 2.0.0.0
Win32 Version: 2.0.50727.42 (RTM.050727-4200)
CodeBase: file:///E:/WINDOWS/assembly/GAC_MSIL/System.Configuration/2.0.0.0__b03f5f7f11d50a3a/System.Configuration.dll
----------------------------------------
Microsoft.AnalysisServices.Viewers
Assembly Version: 9.0.242.0
Win32 Version: 9.00.1186.00
CodeBase: file:///E:/Documents%20and%20Settings/admin/Desktop/Microsoft%20Download/DataMiningViewerClient/Microsoft.AnalysisServices.Viewers.DLL
----------------------------------------
Microsoft.DataWarehouse
Assembly Version: 9.0.242.0
Win32 Version: 9.00.1186.00
CodeBase: file:///E:/Documents%20and%20Settings/admin/Desktop/Microsoft%20Download/DataMiningViewerClient/Microsoft.DataWarehouse.DLL
----------------------------------------
Microsoft.DataWarehouse.Interfaces
Assembly Version: 9.0.242.0
Win32 Version: 9.00.2047.00
CodeBase: file:///E:/WINDOWS/assembly/GAC_MSIL/Microsoft.DataWarehouse.Interfaces/9.0.242.0__89845dcd8080cc91/Microsoft.DataWarehouse.Interfaces.dll
----------------------------------------
Microsoft.AnalysisServices.Controls
Assembly Version: 9.0.242.0
Win32 Version: 9.00.1186.00
CodeBase: file:///E:/Documents%20and%20Settings/admin/Desktop/Microsoft%20Download/DataMiningViewerClient/Microsoft.AnalysisServices.Controls.DLL
----------------------------------------
Microsoft.Office.Interop.Owc11
Assembly Version: 11.0.0.0
Win32 Version: 11.0.5530
CodeBase: file:///E:/WINDOWS/assembly/GAC/Microsoft.Office.Interop.Owc11/11.0.0.0__71e9bce111e9429c/Microsoft.Office.Interop.Owc11.dll
----------------------------------------
Microsoft.AnalysisServices.Graphing
Assembly Version: 9.0.242.0
Win32 Version: 9.00.1186.00
CodeBase: file:///E:/Documents%20and%20Settings/admin/Desktop/Microsoft%20Download/DataMiningViewerClient/Microsoft.AnalysisServices.Graphing.DLL
----------------------------------------
MSDATASRC
Assembly Version: 7.0.3300.0
Win32 Version: 7.00.9466
CodeBase: file:///E:/WINDOWS/assembly/GAC/MSDATASRC/7.0.3300.0__b03f5f7f11d50a3a/MSDATASRC.dll
----------------------------------------
mscomctl
Assembly Version: 10.0.4504.0
Win32 Version: 10.0.4504
CodeBase: file:///E:/WINDOWS/assembly/GAC/mscomctl/10.0.4504.0__31bf3856ad364e35/mscomctl.dll
----------------------------------------
Accessibility
Assembly Version: 2.0.0.0
Win32 Version: 2.0.50727.42 (RTM.050727-4200)
CodeBase: file:///E:/WINDOWS/assembly/GAC_MSIL/Accessibility/2.0.0.0__b03f5f7f11d50a3a/Accessibility.dll
----------------------------------------
Microsoft.AnalysisServices.OleDbDM
Assembly Version: 9.0.242.0
Win32 Version: 9.00.1186.00
CodeBase: file:///E:/Documents%20and%20Settings/admin/Desktop/Microsoft%20Download/DataMiningViewerClient/Microsoft.AnalysisServices.OleDbDM.DLL
----------------------------------------
Microsoft.SqlServer.CustomControls
Assembly Version: 9.0.242.0
Win32 Version: 9.00.2047.00
CodeBase: file:///E:/WINDOWS/assembly/GAC_MSIL/Microsoft.SqlServer.CustomControls/9.0.242.0__89845dcd8080cc91/Microsoft.SqlServer.CustomControls.dll
----------------------------------------
Microsoft.NetEnterpriseServers.ExceptionMessageBox
Assembly Version: 9.0.242.0
Win32 Version: 9.00.1399.00
CodeBase: file:///E:/WINDOWS/assembly/GAC_MSIL/Microsoft.NetEnterpriseServers.ExceptionMessageBox/9.0.242.0__89845dcd8080cc91/Microsoft.NetEnterpriseServers.ExceptionMessageBox.dll
----------------------------------------
System.Transactions
Assembly Version: 2.0.0.0
Win32 Version: 2.0.50727.42 (RTM.050727-4200)
CodeBase: file:///E:/WINDOWS/assembly/GAC_32/System.Transactions/2.0.0.0__b77a5c561934e089/System.Transactions.dll
----------------------------------------
************** JIT Debugging **************
To enable just-in-time (JIT) debugging, the .config file for this
application or computer (machine.config) must have the
jitDebugging value set in the system.windows.forms section.
The application must also be compiled with debugging
enabled.
For example:
<configuration>
<system.windows.forms jitDebugging="true" />
</configuration>
When JIT debugging is enabled, any unhandled exception
will be sent to the JIT debugger registered on the computer
rather than be handled by this dialog box.
***** end of error message***
Can someone help to figure out what went wrong? thx.
View 7 Replies
View Related
Nov 7, 2007
I've tried to installed sql server 2005 developer edition on windows vista home premium but failed many times, almost giving up.
I've installed and turned on all the features of IIS 7.0 but it still failed and I tried to test IIS 7 by running the local host and received this error " HTTP Error 500.0 - Internal Server Error."
I even ran the windows update SP1 for Vista and MDAC 2.8 SP1
I really appreciate any comments and help.
-----
Here are the messages in the windows system check for the sql server 2005 installation:
IIS Feature Requirement (Warning)
Messages
€¢ IIS Feature Requirement
Microsoft Internet Information Services (IIS) is either not installed or is disabled. IIS is required by some SQL Server features. Without IIS, some SQL Server features will not be available for installation. To install all SQL Server features, install IIS from Add or Remove Programs in Control Panel or enable the IIS service through the Control Panel if it is already installed, and then run SQL Server Setup again. For a list of features that depend on IIS, see Features Supported by Editions of SQL Server in Books Online.
View 24 Replies
View Related
Nov 8, 2007
I've tried to installed sql server 2005 developer edition on windows vista home premium but failed many times, almost giving up.
I've installed and turned on all the features of IIS 7.0 but it still failed and I tried to test IIS 7 by running the local host and received this error " HTTP Error 500.0 - Internal Server Error."
I even ran the windows update SP1 for Vista and MDAC 2.8 SP1
I really appreciate any comments and help.
-----
Here are the messages in the windows system check for the sql server 2005 installation:
IIS Feature Requirement (Warning)
Messages
€¢ IIS Feature Requirement
Microsoft Internet Information Services (IIS) is either not installed or is disabled. IIS is required by some SQL Server features. Without IIS, some SQL Server features will not be available for installation. To install all SQL Server features, install IIS from Add or Remove Programs in Control Panel or enable the IIS service through the Control Panel if it is already installed, and then run SQL Server Setup again. For a list of features that depend on IIS, see Features Supported by Editions of SQL Server in Books Online.
View 5 Replies
View Related
Sep 17, 2006
I am testing SQL Server 2005.
I need a replication where "DELETE delivery format" field in Table Articles Properties is set to "Do not replicate DELETE statement". Unfortunately nothing I set in this dialog is saved and used. Doesn matter what I change the replication behaves the same and the next time I open "Table Articles Properties", every value is still default.
Am I doing something wrong or is it an issue (bug) in 2005 replication?
I googled about the issue and also searched in the SQL Server Replication threads on this site, both with no success. The thread named "Selective replication of DELETE transactions" doesn't provide enough information about SWL Server 2005 replication configuration.
Use Case:
I have a small "operational" database with live data. I need to keep a complete history for all records that ever appeared within the "operational" DB. It came to my mind that I can "easily" achieve this if I set up a replication that does not replicate a delete statement ever. I strongly prefer not to mess with the subscription stored procedures but to configer my publication properly instead.
View 1 Replies
View Related
Mar 19, 2008
I'm writing some generalized stored procedures for use form managed code. The following stored proc works great:
CREATE PROCEDURE fnd_Send (
@Message VARCHAR(5000),
@Contract SYSNAME,
@MessageType SYSNAME,
@FromService SYSNAME,
@ToService VARCHAR(100),
@Encrypted Bit
)
AS
BEGIN
DECLARE @Handle UNIQUEIDENTIFIER;
BEGIN DIALOG CONVERSATION @Handle
FROM SERVICE @FromService
TO SERVICE @ToService
ON CONTRACT @Contract
WITH ENCRYPTION = OFF;
SEND ON CONVERSATION @Handle
MESSAGE TYPE @MessageType(@Message);
Notice the use of SYSNAME parameters €“ translating the parameter into an object name - allows it to work in the BEGIN DIALOG statement when an system object is needed. Works create and cues up generic messages.
The following will not compile:
CREATE PROCEDURE fnd_Receive (
@Queue SYSNAME,
@Message VARCHAR(5000) out,
@MessageType SYSNAME out
)
AS
BEGIN
DECLARE @Handle UNIQUEIDENTIFIER;
RECEIVE TOP (1)
@Handle = conversation_handle,
@MessageType = message_type_name,
@Message = message_body
FROM @Queue;
END
GO
It won€™t let me use the SYSNAME field @Queue in the FROM clause. I€™m getting an unfriendly:
Msg 102, Level 15, State 1, Procedure fnd_Receive, Line 15
Incorrect syntax near '@Queue'.
It looks like maybe I can€™t bind a variable in the FROM clause €“ perhaps like you cannot in a SELECT statement. I can fall back to dynamic SQL in the stored proc or in the managed code; I was hoping not to.
Can anyone shed any light on this? Also, are there any other techniques I'm missing to avoid falling back to a dynamic SQL statement.
Thank you in advance -
Jeff Odell
Catapult Systems
View 3 Replies
View Related
Jan 26, 2007
Hi,
One of our client application launches a execuatable on server by calling master..xp_cmdshell. We are migrating to SQL 2005 and I would like to use the Context Switching feature
We have sql login €œclient€? which is used by clinet application and has very least privelge and have only required access to requried database and tables.
xp_cmdshell is disabled by default on our server and I would like to enable xp_cmdshell, launch the executable on the server and disable xp_cmdshell.I have created another SQL Login €œBob€? and is member of sysadmin. The below SP is created by logging in as €œBob€?. My objective is to grant execute access on this SP and grant impersonate access to sql login €œclient€? . So that client application connecting to SQL server can launch the executable by calling the SP
create proc Bob.[Bob_enable_xp_cmdshell]
with execute as 'Bob'
as
EXEC sp_configure N'show advanced options', N'1' RECONFIGURE WITH OVERRIDE
EXEC sp_configure N'xp_cmdshell', N'1'
RECONFIGURE WITH OVERRIDE
EXEC sp_configure N'show advanced options', N'0' RECONFIGURE WITH OVERRIDE
-- launch the application using xp_cmdshell and disable the xp_command shell
When I try to execute (exec [Bob].[jtr_enable_xp_cmdshell]
) the above SP by logging in as €˜Bob€™, I€™m getting the below error
Msg 15247, Level 16, State 1, Procedure sp_configure, Line 94
User does not have permission to perform this action.
Msg 5812, Level 14, State 1, Procedure jtr_enable_xp_cmdshell, Line 4
You do not have permission to run the RECONFIGURE statement.
Msg 15123, Level 16, State 1, Procedure sp_configure, Line 51
The configuration option 'xp_cmdshell' does not exist, or it may be an advanced option.
Msg 5812, Level 14, State 1, Procedure jtr_enable_xp_cmdshell, Line 6
You do not have permission to run the RECONFIGURE statement.
Msg 15247, Level 16, State 1, Procedure sp_configure, Line 94
User does not have permission to perform this action.
Msg 5812, Level 14, State 1, Procedure jtr_enable_xp_cmdshell, Line 7
You do not have permission to run the RECONFIGURE statement.
Please note the SQL Login €œBob€? is a member of sysadmin fixed role. Thank in advance for you help.
With regards
GK
View 10 Replies
View Related
Jan 11, 2006
Hello,
This is info that I am still not certain about and I just need to make sure, my gut feeling is correct:
A.
When a procedure is triggered upon reception of a message in a queue, what happens when the procedure fails and rolls back?
1. Message is left on the Queue.
2. is the worker procedure triggered again for the same message by the queue?
3. I am hoping the Queue keeps on triggering workers until it is empty.
My scenario is that my queue reader procedure only reads one message at a time, thus I do not loop to receive many messages.
B.
For my scenario messages are independent and ordering does not matter.
Thus I want to ensure my Queue reader procedures execute simultaneously. Is reading the Top message in one reader somehow blocking the queue for any other reader procedures? I.e. if I have BEGIN TRANSACTION when reading messages of the Queue, is that effectively going prevent many reader procedures working simultaneously. Again, I want to ensure that Service broker is effectively spawning procedures that work simultaneously.
Thank you very much for the time,
Lubomir
View 5 Replies
View Related
Jun 20, 2006
Good day,
I have seen in Enterprise manager there is a toll that can script the all tables in a database, but nothing that can generate the insert statements for all the rows in each table in a secified database.
Does any one know of a application, plug in, script that can generate the insert statments for all the tables in a database?
Please someone help, this is driving me insane.
Thanks
View 5 Replies
View Related
Sep 28, 2015
Have any seen Insert statement deadlocking itself ? Most of the articles published by Microsoft says to change the transaction isolation level from Read Committed to Read Committed Snapshot.Below is the XML file on the deadlock
<deadlock>
<victim-list>
<victimProcess id="processe259948" />
</victim-list>
<process-list>
[code]...
View 0 Replies
View Related
Dec 29, 2006
Hi -
I am using linked server to insert data to a table. When I do select, it does show me results but when I do insert, it does not work. My source/destination has exact same data types defined. Any idea?
insert into dbo.tb_PERSONNEL
select * from openquery(CADC1, 'SELECT PERSONNEL_1_NF.ID, PERSONNEL_1_NF.NAME, PERSONNEL_1_NF.PNUM, PERSONNEL_1_NF.PN
FROM PERSONNEL_1_NF')
I get:
Msg 8152, Level 16, State 14, Line 2
String or binary data would be truncated.
The statement has been terminated.
View 1 Replies
View Related
Apr 6, 2015
What statement do I use, as part of an insert trigger, to insert xml data from the xml database to a flat file database, to check if a record with a specific ID exists to delete first then insert the changed record, instead of adding the changes or an updated from the original xml database.
What I’m trying to do is take the xml formatted data out of one sql server database and insert the data only in that xml into a another sql database. So I can play with the data.
Problem is if the data in the xml is updated or changed for a specific record on the original xml database then the trigger inserts another copy into the created database (which I don’t want).
This is on SQL Server 2008R2.
View 2 Replies
View Related
Apr 20, 2015
I am having 2 tables one is staging temp and another is main import table.
In my staging table there are 3 column Col001,Id,Loaddate
in Col001 column data are present with '¯' delemeter.
I am having function which is used to load data from staging to import table using one function.
this function create a insert statement.
My Existing function
-- Description: To Split a Delimited field by a Delimiter
ALTER FUNCTION [dbo].[ufn_SplitFieldByDelimiter]
(
@fieldname varchar(max)
,@delimiter varchar(max)
,@delimiter_count int
[Code] ....
I am unable to get correct statement with above function.
View 1 Replies
View Related
Sep 18, 2015
I have two tables for insertion in one transaction scope. Table one have 10 rows. After first table insert statement (not yet committed) if I run select on first table from other session, it holds table until my insert is committed or rolled back and from (SSMS), it display 10 rows and then wait for transaction scope till finished. My question is do I need to use no lock hint in this situation. Or there is something wrong with isolation level. One saying that in this situation table should not hols select while insert is in transaction scope.
View 5 Replies
View Related
Oct 7, 2015
In a t-sql 2012 merge statement that is listed below, the insert statement on the merge statement listed below is not working. The update statement works though.
Merge test.dbo.LockCombination AS LKC1
USING
(select LKC.lockID,LKC.seq,A.lockCombo1,A.schoolnumber
from
[Inputtb] A
JOIN test.dbo.School SCH ON A.schoolnumber = SCH.type
[Code] ....
Thus would you tell me what I need to do to make the insert statement work on the merge statement listed above?
View 6 Replies
View Related
Aug 8, 2007
Try this in sql server 2005:
select COALESCE(a1, char(254)) as c1 from
(select 'Z' as a1 union select 'Ya' as a1 union select 'Y' as a1 union select 'W' as a1) as b1
group by a1
with rollup order by c1
select COALESCE(a1, char(255)) as c1 from
(select 'Z' as a1 union select 'Ya' as a1 union select 'Y' as a1 union select 'W' as a1) as b1
group by a1
with rollup order by c1
The only difference is that the first one uses 254 and the second one uses 255. The first sorts like this:
WYYaZþ
The second one sorts like this:
WYÿYaZ
Is this expected behavior?
View 3 Replies
View Related
Nov 5, 2015
I've have a need with SQL Server 2005 (so I've no MERGE statement), I have to merge 2 tables, the target table has 10 fields, the first 4 are the clustered index and primary key, the source table has the same fields and index.Since I can't use the MERGE statement (I'm in SQL 2005) I have to make a double step operation, and INSERT and an UPDATE, I can't figure how to design the WHERE condition for the insert statement.
View 2 Replies
View Related
Jun 25, 2015
In C# .NET I have the possible to create some validations of my data, with regulary expressions. Do SQL have the same feature? I will like to do an data validation of all my insert statement inside the sql-server. Is that possible?
View 5 Replies
View Related
Aug 14, 2004
Theres a feature in oracle that allows you to modify tables, colums, values and the data from its enterprise console the same way that you can in sql server. In oracle however theres a button called 'show sql' that allows you to see and copy/paste the resulting sql for the changes made via the console.
I would imagine that sql server has a similar option. The reason i ask is that i would like to more fully learn how to do this through the query analyser and get more familiar with sql involved and I would be able to do this if I could see the resulting sql from enterprise manager.
Hope this makes sense.
I did find something in sql server called 'generate sql' but this doesnt update during changes you make automatically.
Thanks
View 3 Replies
View Related
Apr 21, 2008
An insert statement was not inserting all the data into a table. Found it very strange as the other fields in the row were inserted. I ran SQL profiler and found that sql statement had all the fields in the insert statement but some of the fields were not inserted. Below is the sql statement which is created dyanmically by a asp.net C# class. The columns which are not inserted are 'totaltax' and 'totalamount' ...while the 'shipto_name' etc...were inserted.there were not errors thrown. The sql from the code cannot be shown here as it is dynamically built referencing C# class files.It works fine on another test database which uses the same dlls. The only difference i found was the difference in date formats..@totalamount=1625.62,@totaltax=125.62are not inserted into the database.Below is the statement copied from SQL profiler.exec sp_executesql N'INSERT INTO salesorder(billto_city, billto_country, billto_line1, billto_line2, billto_name,billto_postalcode, billto_stateorprovince, billto_telephone, contactid, CreatedOn, customerid, customeridtype,DeletionStateCode, discountamount, discountpercentage, ModifiedOn, name, ordernumber,pricelevelid, salesorderId, shipto_city, shipto_country,shipto_line1, shipto_line2, shipto_name, shipto_postalcode, shipto_stateorprovince,shipto_telephone, StateCode, submitdate, totalamount,totallineitemamount, totaltax ) VALUES(@billto_city, @billto_country, @billto_line1, @billto_line2,@billto_name, @billto_postalcode, @billto_stateorprovince, @billto_telephone, @contactid, @CreatedOn, @customerid,@customeridtype, @DeletionStateCode, @discountamount,@discountpercentage, @ModifiedOn, @name, @ordernumber, @pricelevelid, @salesorderId,@shipto_city, @shipto_country, @shipto_line1, @shipto_line2,@shipto_name, @shipto_postalcode, @shipto_stateorprovince, @shipto_telephone,@StateCode, @submitdate, @totalamount, @totallineitemamount, @totaltax)',N'@billto_city nvarchar(8),@billto_country nvarchar(13),@billto_line1 nvarchar(3),@billto_line2 nvarchar(4),@billto_name nvarchar(15),@billto_postalcode nvarchar(5),@billto_stateorprovince nvarchar(8),@billto_telephone nvarchar(3),@contactid uniqueidentifier,@CreatedOn datetime,@customerid uniqueidentifier,@customeridtype int,@DeletionStateCode int,@discountamount decimal(1,0),@discountpercentage decimal(1,0),@ModifiedOn datetime,@name nvarchar(33),@ordernumber nvarchar(18),@pricelevelid uniqueidentifier,@salesorderId uniqueidentifier,@shipto_city nvarchar(8),@shipto_country nvarchar(13),@shipto_line1 nvarchar(3),@shipto_line2 nvarchar(4),@shipto_name nvarchar(15),@shipto_postalcode nvarchar(5),@shipto_stateorprovince nvarchar(8),@shipto_telephone nvarchar(3),@StateCode int,@submitdate datetime,@totalamount decimal(6,2),@totallineitemamount decimal(6,2),@totaltax decimal(5,2)',@billto_city=N'New York',@billto_country=N'United States',@billto_line1=N'454',@billto_line2=N'Road',@billto_name=N'Hillary Clinton',@billto_postalcode=N'10001',@billto_stateorprovince=N'New York',@billto_telephone=N'124',@contactid='8DAFE298-3A25-42EE-B208-0B79DE653B61',@CreatedOn=''2008-04-18 13:37:12:013'',@customerid='8DAFE298-3A25-42EE-B208-0B79DE653B61',@customeridtype=2,@DeletionStateCode=0,@discountamount=0,@discountpercentage=0,@ModifiedOn=''2008-04-18 13:37:12:013'',@name=N'E-Commerce Order (Before billing)',@ordernumber=N'BRKV-CC-OKRW5764YS',@pricelevelid='B74DB28B-AA8F-DC11-B289-000423B63B71',@salesorderId='9CD0E11A-5A6D-4584-BC3E-4292EBA6ED24',@shipto_city=N'New York',@shipto_country=N'United States',@shipto_line1=N'454',@shipto_line2=N'Road',@shipto_name=N'Hillary Clinton',@shipto_postalcode=N'10001',@shipto_stateorprovince=N'New York',@shipto_telephone=N'124',@StateCode=0,@submitdate=''2008-04-18 14:37:10:140'',@totalamount=1625.62,@totallineitemamount=1500.00,@totaltax=125.62
thanks
View 7 Replies
View Related
Apr 9, 2007
Hello
I have a problem with setting relations properly when inserting data using adonet. Already have searched for a solutions, still not finding a mistake...
Here's the sql management studio diagram :
and here goes the code1 DataSet ds = new DataSet();
2
3 SqlDataAdapter myCommand1 = new SqlDataAdapter("select * from SurveyTemplate", myConnection);
4 SqlCommandBuilder cb = new SqlCommandBuilder(myCommand1);
5 myCommand1.FillSchema(ds, SchemaType.Source);
6 DataTable pTable = ds.Tables["Table"];
7 pTable.TableName = "SurveyTemplate";
8 myCommand1.InsertCommand = cb.GetInsertCommand();
9 myCommand1.InsertCommand.Connection = myConnection;
10
11 SqlDataAdapter myCommand2 = new SqlDataAdapter("select * from Question", myConnection);
12 cb = new SqlCommandBuilder(myCommand2);
13 myCommand2.FillSchema(ds, SchemaType.Source);
14 pTable = ds.Tables["Table"];
15 pTable.TableName = "Question";
16 myCommand2.InsertCommand = cb.GetInsertCommand();
17 myCommand2.InsertCommand.Connection = myConnection;
18
19 SqlDataAdapter myCommand3 = new SqlDataAdapter("select * from Possible_Answer", myConnection);
20 cb = new SqlCommandBuilder(myCommand3);
21 myCommand3.FillSchema(ds, SchemaType.Source);
22 pTable = ds.Tables["Table"];
23 pTable.TableName = "Possible_Answer";
24 myCommand3.InsertCommand = cb.GetInsertCommand();
25 myCommand3.InsertCommand.Connection = myConnection;
26
27 ds.Relations.Add(new DataRelation("FK_Question_SurveyTemplate", ds.Tables["SurveyTemplate"].Columns["id"], ds.Tables["Question"].Columns["surveyTemplateID"]));
28 ds.Relations.Add(new DataRelation("FK_Possible_Answer_Question", ds.Tables["Question"].Columns["id"], ds.Tables["Possible_Answer"].Columns["questionID"]));
29
30 DataRow dr = ds.Tables["SurveyTemplate"].NewRow();
31 dr["name"] = o[0];
32 dr["description"] = o[1];
33 dr["active"] = 1;
34 ds.Tables["SurveyTemplate"].Rows.Add(dr);
35
36 DataRow dr1 = ds.Tables["Question"].NewRow();
37 dr1["questionIndex"] = 1;
38 dr1["questionContent"] = "q1";
39 dr1.SetParentRow(dr);
40 ds.Tables["Question"].Rows.Add(dr1);
41
42 DataRow dr2 = ds.Tables["Possible_Answer"].NewRow();
43 dr2["answerIndex"] = 1;
44 dr2["answerContent"] = "a11";
45 dr2.SetParentRow(dr1);
46 ds.Tables["Possible_Answer"].Rows.Add(dr2);
47
48 dr1 = ds.Tables["Question"].NewRow();
49 dr1["questionIndex"] = 2;
50 dr1["questionContent"] = "q2";
51 dr1.SetParentRow(dr);
52 ds.Tables["Question"].Rows.Add(dr1);
53
54 dr2 = ds.Tables["Possible_Answer"].NewRow();
55 dr2["answerIndex"] = 1;
56 dr2["answerContent"] = "a21";
57 dr2.SetParentRow(dr1);
58 ds.Tables["Possible_Answer"].Rows.Add(dr2);
59
60 dr2 = ds.Tables["Possible_Answer"].NewRow();
61 dr2["answerIndex"] = 2;
62 dr2["answerContent"] = "a22";
63 dr2.SetParentRow(dr1);
64 ds.Tables["Possible_Answer"].Rows.Add(dr2);
65
66 myCommand1.Update(ds,"SurveyTemplate");
67 myCommand2.Update(ds, "Question");
68 myCommand3.Update(ds, "Possible_Answer");
69 ds.AcceptChanges();
70
and that causes (at line 67):"The INSERT statement conflicted with the FOREIGN KEY constraint
"FK_Question_SurveyTemplate". The conflict occurred in database
"ankietyzacja", table "dbo.SurveyTemplate", column
'id'.
The statement has been terminated.
at System.Data.Common.DbDataAdapter.UpdatedRowStatusErrors(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
at System.Data.Common.DbDataAdapter.UpdatedRowStatus(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.UpdateFromDataTable(DataTable dataTable, DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String srcTable)
at AnkietyzacjaWebService.Service1.createSurveyTemplate(Object[] o) in J:\PL\PAI\AnkietyzacjaWebService\AnkietyzacjaWebServicece\Service1.asmx.cs:line 397"
Could You please tell me what am I missing here ?
Thanks a lot.
View 5 Replies
View Related
Jan 14, 2008
This problem is being seen on SQL 2005 SP2 + cumulative update 4
I am currently successfully using the output clause of an insert statement to return the identity values for inserted rows into a table variable
I now need to add an "instead of insert" trigger to the table that is the subject of the insert.
As soon as I add the "instead of insert" trigger, the output clause on the insert statement does not return any data - although the insert completes successfully. As a result I am not able to obtain the identities of the inserted rows
Note that @@identity would return the correct value in the test repro below - but this is not a viable option as the table in question will be merge replicated and @@identity will return the identity value of a replication metadata table rather than the identity of the row inserted into my_table
Note also that in the test repro, the "instead of insert" trigger actually does nothing apart from the default insert, but the real world trigger has additional code.
To run the repro below - select each of the sections below in turn and execute them
1) Create the table
2) Create the trigger
3) Do the insert - note that table variable contains a row with column value zero - it should contain the @@identity value
4) Drop the trigger
5) Re-run the insert from 3) - note that table variable is now correctly populated with the @@identity value in the row
I need the behaviour to be correct when the trigger is present
Any thoughts would be much appreciated
aero1
/************************************************
1) - Create the table
************************************************/
CREATE TABLE [dbo].[my_table](
[my_table_id] [bigint] IDENTITY(1,1) NOT NULL,
[forename] [varchar](100) NULL,
[surname] [varchar](50) NULL,
CONSTRAINT [pk_my_table] PRIMARY KEY NONCLUSTERED
(
[my_table_id] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, FILLFACTOR = 70) ON [PRIMARY]
)
GO
/************************************************
2) - Create the trigger
************************************************/
CREATE TRIGGER [dbo].[trig_my_table__instead_insert] ON [dbo].[my_table]
INSTEAD OF INSERT
AS
BEGIN
INSERT INTO my_table
(
forename,
surname)
SELECT
forename,
surname
FROM inserted
END
/************************************************
3) - Do the insert
************************************************/
DECLARE @my_insert TABLE( my_table_id bigint )
declare @forename VARCHAR(100)
declare @surname VARCHAR(50)
set @forename = N'john'
set @surname = N'smith'
INSERT INTO my_table (
forename
, surname
)
OUTPUT inserted.my_table_id INTO @my_insert
VALUES( @forename
, @surname
)
select @@identity -- expect this value in @my_insert table
select * from @my_insert -- OK value without trigger - zero with trigger
/************************************************
4) - Drop the trigger
************************************************/
drop trigger [dbo].[trig_my_table__instead_insert]
go
/************************************************
5) - Re-run insert from 3)
************************************************/
-- @my_insert now contains row expected with identity of inserted row
-- i.e. OK
View 5 Replies
View Related
Feb 12, 2014
I have created a trigger that is set off every time a new item has been added to TableA.The trigger then inserts 4 rows into TableB that contains two columns (item, task type).
Each row will have the same item, but with a different task type.ie.
TableA.item, 'Planning'
TableA.item, 'Design'
TableA.item, 'Program'
TableA.item, 'Production'
How can I do this with tSQL using a single select statement?
View 6 Replies
View Related
Jul 31, 2015
I get the below error on the event log of my application server which uses SQL database.
Details: RuleId:a811dcbc-4c5b-d9de-592b-f01e17fc0e9a. HealthServiceId:a5f70248-b545-4d35-7c84-e7aa87610ee4. The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Alert_BaseManagedEntity". The conflict occurred in database "OperationsManager",
table "dbo.BaseManagedEntity", column 'BaseManagedEntityId'.
The statement has been terminated.RuleId:a811dcbc-4c5b-d9de-592b-f01e17fc0e9a. HealthServiceId:a5f70248-b545-4d35-7c84-e7aa87610ee4. The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Alert_BaseManagedEntity". The conflict occurred in database "OperationsManager", table "dbo.BaseManagedEntity", column 'BaseManagedEntityId'.The statement has been terminated..
Details: RuleId:a811dcbc-4c5b-d9de-592b-f01e17fc0e9a. HealthServiceId:a5f70248-b545-4d35-7c84-e7aa87610ee4. The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Alert_BaseManagedEntity". The conflict occurred in database "OperationsManager", table "dbo. BaseManaged Entity", column 'BaseManagedEntityId'.The statement has been terminated..
View 5 Replies
View Related
Mar 9, 2004
I am using the full text indexing feature of SQL Server .
I have indexed two text fields in the Content table and two other text fields in the ContentDetail table
The select queries are shown below for the two seperate calls.
I want to be able to show the results based on the rank ( The column that has the best match for the searched key ).
Since I am getting two different RANK arrays from the two calls, can i resort them in the code to get the new rank order to show the results in the screen
SELECT KEY_TBL.RANK,FT_TBL.Description, FT_TBL.Title
FROM Content AS FT_TBL
INNER JOIN
CONTAINSTABLE( Content, * ,
'ISABOUT (Cancer weight(1),Survivor weight(1), Shared weight(1), Story weight(1))' ) AS KEY_TBL
ON FT_TBL.ContentID = KEY_TBL.[KEY]
ORDER BY KEY_TBL.RANK DESC
SELECT KEY_TBL.RANK,FT_TBL.Description, FT_TBL.SubTitle
FROM ContentDetail AS FT_TBL
INNER JOIN
CONTAINSTABLE( ContentDetail, * ,
'ISABOUT (Cancer weight(1),Survivor weight(1), Shared weight(1), Story weight(1))' ) AS KEY_TBL
ON FT_TBL.ContentDetailID = KEY_TBL.[KEY]
ORDER BY KEY_TBL.RANK DESC
View 2 Replies
View Related
May 26, 2006
Hi All,
I've looked through the forum hoping I'm not the only one with this issue but alas, I have found nothing so I'm hoping someone out there will give me some assistance.
My problem is the case statement in my Insert Statement. My overall goal is to insert records from one table to another. But I need to be able to assign a specific value to the incoming data and thought the case statement would be the best way of doing it. I must be doing something wrong but I can't seem to see it.
Here is my code:
Insert into myTblA
(TblA_ID,
mycasefield =
case
when mycasefield = 1 then 99861
when mycasefield = 2 then 99862
when mycasefield = 3 then 99863
when mycasefield = 4 then 99864
when mycasefield = 5 then 99865
when mycasefield = 6 then 99866
when mycasefield = 7 then 99867
when mycasefield = 8 then 99868
when mycasefield = 9 then 99855
when mycasefield = 10 then 99839
end,
alt_min,
alt_max,
longitude,
latitude
(
Select MTB.LocationID
MTB.model_ID
MTB.elevation, --alt min
null, --alt max
MTB.longitude, --longitude
MTB.latitude --latitude
from MyTblB MTB
);
The error I'm getting is:
Incorrect syntax near '='.
I have tried various versions of the case statement based on examples I have found but nothing works.
I would greatly appreciate any assistance with this one. I've been smacking my head against the wall for awhile trying to find a solution.
View 10 Replies
View Related
Oct 20, 2014
In the below code i want to use select statement for getting customer
address1,customeraddress2,customerphone,customercity,customerstate,customercountry,customerfirstname,customerlastname
from customer table.Rest of the things will be as it is in the following code.How do i do this?
INSERT INTO EMImportListing ("
sql += " CustId,Title,Description,JobCity,JobState,JobPostalCode,JobCountry,URL,Requirements, "
sql += " IsDraft,IsFeatured,IsApproved,"
sql += " Email,OrgName,customerAddress1,customerAddress2,customerCity,customerState,customerPostalCode,
[code]....
View 1 Replies
View Related
