HiI have to create an XML file based on a SQL SERVER 2005 table.Everything works fine:SELECT 1 as Tag,
NULL as Parent,
Ex_Id as [Exam!1!Ex_Id],
NULL as [Ex_Title!2!!cdata]
FROM Exams
WHERE ex_State = 'P'
UNION ALL
SELECT 2 as Tag,
1 as Parent,
Ex_Id,
ex_Title
FROM Exams
WHERE ex_State = 'P'
order by [Exam!1!Ex_Id], [Ex_Title!2!!cdata]
FOR XML EXPLICIT, ROOT('Exams')
BUT when i add another column, a text column, I get into trouble: SELECT 1 as Tag,
NULL as Parent,
Ex_Id as [Exam!1!Ex_Id],
NULL as [Ex_Title!2!!cdata],
NULL as [Ex_Situation!3!!cdata]
FROM Exams
WHERE ex_State = 'P'
UNION ALL
SELECT 2 as Tag,
1 as Parent,
Ex_Id,
ex_Title,
ex_Situation
FROM Exams
WHERE ex_State = 'P'
order by [Exam!1!Ex_Id], [Ex_Title!2!!cdata], [Ex_Situation!3!!cdata]
FOR XML EXPLICIT, ROOT('Exams')
The problem is clearly the fact that i have to sort on the text column, plus the fact that this column requires the CDATA tag enclosurePlease help me....i'm desperate :(
for some unknow reasons.. my store proc stop working.. and i got an error.. i have installaed the latest SP4 for SQL server 2000 and still have the problem !any ideas why ?? Message "An explicit value for the identity column in table 'LCMS_Modules' can only be specified when a column list is used and IDENTITY_INSERT is ON."CREATE procedure LCMS_Modules_Add @PageID int, @ModuleDefID int, @Panename nvarchar(32), @Title nvarchar(128), @Admins nvarchar(256) as insert into LCMS_Modulesvalues(@PageID, @ModuleDefID, 99, @Panename, @Title, '0;', @Admins, 0, '', 'Center', '', '', '', 1, 0)GO
I use SQLExpress2005 and I search about this problem , this is a BUG in MsSql 2000 but I use sql Express 2005.although in my table I set IDENTITY_INSERT on (master Key)Please help me
Private Sub AddClientToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles AddClientToolStripMenuItem.Click Dim addclient As New newclient() newclient.ClientID = 0 Dim result As DialogResult result = addclient.ShowDialog If result = DialogResult.OK Then
Dim Client As clients_and_sitesDataSet1.ClientsRow Client = Clients_and_sitesDataSet1.Clients.NewClientsRow Client.ClientID = addclient.ClientID Client.Clientname = addclient.Clientname Client.Street_and_number = addclient.Street_and_number Client.Zipcode = addclient.Zipcode Client.Place = addclient.Place Client.Phone = addclient.Phone Client.Email = addclient.Email Client.Contact_person = addclient.Contact_person Clients_and_sitesDataSet1.Clients.AddClientsRow(Client) ClientsTableAdapter.Update(Client)------------------------------problem MessageBox.Show("New Client is saved") Else MessageBox.Show("User cancelled operation") End If addclient = Nothing End Sub
Code in VB (2005 express edition)
Hi all above is aa snipped from the code I am using. all works fine exxept the ------problem line. when i try to run the program, i get an exeption
Cannot insert explicit value for identity column in table 'Clients' when IDENITY_INSERT is set to OFF
how can i turn this on???? I seem to be stuck, vieuwed the msdn video on the subject and cant find what I am doing wrong. I hope u guys can help me out.
And please give me specific guide lines on how to put in the code and code lines. I tried using SET IDENTITY_INSERT Clients ON But than I get all errors bout things not being declared, set not being supported anymore, things like that.
Thanks for the effort you will put into helping me out and becomming a "self supported programmer"
Hi everyone,I encountered an error "Need to run the object to perform this operationCode execution exception: EXCEPTION_ACCESS_VIOLATION" When I try to import data from Oracle to MS SQL Server with EnterpriseManager (version 8.0) using DTS Import/Export Wizard. There are 508 rowsin Oracle table and I did get first 42 rows imported to SQL Server.Anyone knows what does the above error message mean and what causes therest of the row failed importing?Thanks very much in advance!Rene Z.--Posted via http://dbforums.com
i have a situation, where i need to group my information by a certainid, but that information which should already be grouped, i need toorder based on a number in one of the columnsexample:<ex id=2 listorder=1><description>desc 2</description></ex><ex id=1 listorder=2><description>desc 1</description></ex>in my order clause i first put order by the id, and it will group theinformation accordingly, but will put the lowest number id first, notcaring about the listorderbut if i order by the listorder column first, the information isn'tgroupd properlyi don't know if i made myself clear enough?hope someboyd can help methnx in advance
I don't know MS-SQLserver internal system at all. I 've just used Oracle a couple years ago and so in some cases (e.g using TP-monitor MTS or Tuxedo) you can switch off the implicit transaction by using the option AUTOCOMMIT ON/OFF.
How can switch off the implicit transaction system on MS-SQLServer ?
I created a SELECT statement that uses the FOR XML EXPLICIT clause so that SQL Server data can be exported to another system. Format thios system accepts is XML. What should I define within a DTS package so that this Select statement is executed and an XML file is created?
I have an cursor that loops 720 times. Each FETCH does 6 INSERTS. Each FETCH the 6 INSERTs a total of 200-300 records appended.
Would explicit transactions speed up the cursor?
In Oracle, I would keep track of how many times I've looped, and commit every nth time. I would put a catch remainder COMMIT at the end of the procedure.
I've got the IF @@TRANCOUNT > 19 COMMIT TRAN (about 6k records), but say I had <= 19 remaining at the end of the cursor, how do you get those to commit? I tried to put an IF @@TRANCOUNT > 1 COMMIT TRAN, but that didn't work.
What's a good @@TRANCOUNT to commit at in this case?
In the SP below, I get the following error when I run it: Explicit value must be specified for identity column in table 'Tests' when IDENTITY_INSERT is set to ON.
From what I've read of the INSERT method, it says you can add a variable value. So I don;t understand why it won't take mine.
CREATE PROC qaspAddTestRecord AS
DECLARE @aKey bigint DECLARE @aTestSuiteId bigint
exec qaspGetNewID 'Tests', @aKey OUT
SELECT @aTestSuiteId= NewID FROM ID WHERE TableName='TestSuite'
SET IDENTITY_INSERT Tests ON
INSERT INTO Tests (TestSuiteId, TestIDInternal) VALUES (@aTestSuiteId, @aKey)
Hello all,I'm trying to generate some XML directly from MS SQL with thefollowing codeSELECT1 AS tag, NULL AS parent, NULL AS [GoogleCustomizations!1], NULL AS [Annotations!2], NULL AS [Annotation!3], NULL AS [Annotation!3!about], NULL AS [Annotation!3!score], NULL AS [Label!4], NULL AS [Label!4!name]UNIONSELECT2 AS tag, 1 AS parent, NULL, NULL, NULL, NULL, NULL, NULL, NULLUNIONSELECTTOP 503 AS tag, 2 AS parent, NULL, NULL, NULL, 'www.' + domainName, 1 -- score, NULL, NULLFROM tbl_auDomainNameUNIONSELECT4 AS tag, 3 AS parent, NULL, NULL, NULL, NULL, NULL, NULL, '_cse_ad-o6lgdody'FOR XML EXPLICITThe XML it needs to create is as following<GoogleCustomizations><Annotations><Annotation about="www.clickfind.com.au/*" score="1"><Label name="_cse_ad-o6lgdody" /></Annotation><Annotation about="www.lookle.com/*" score="1"><Label name="_cse_ad-o6lgdody" /></Annotation><Annotation about="www.sensis.com.au/*" score="1"><Label name="_cse_ad-o6lgdody" /></Annotation></Annotations></GoogleCustomizations>It is currently creating<GoogleCustomizations><Annotations><Annotation about="www.10000steps.org.au" score="1" /><Annotation about="www.101fm.asn.au" score="1" /><Annotation about="www.aao.gov.au" score="1"><Label name="_cse_ad-o6lgdody" /></Annotation></Annotations></GoogleCustomizations>I cannot get my head around how I can get the label <Labelname="_cse_ad-o6lgdody" />in each element. Does anyone know?Thanks in advance.
I am developing an integration process between two databases. One ofthem is a SQL Server 2000 and the other is using MSDE 2000. Theintegration process is done in C# (VS2003).The main database is the SQL Server, the MSDE will contain a reallysmall subset of the data found on the main. To help diminish the amountof time taken to develop an integration process between thosedatabases, the same structure are found on both side. The onlydifference, when I insert data in the MSDE from the SQL Server, I setthe IDENTITY_INSERT to ON and use the same IDs found on the SQL Server.I can insert one set of data without problem, but from there, if I tryagain, I will always receive the "Cannot insert explicit value foridentity column in table ... when IDENTITY_INSERT is set to OFF." Isaw on Microsoft website the article ID 878501; I noticed I was usingMSDE sp3, I upgraded to SP4... and I still have the problem.I know, when I call the update function on the sqldataadapter, theadapters contain the IDENTITY_INSERT ON and it's set to OFF after theinsert. The "Cannot insert..." error is the only one I received.Can anyone help me on that issue? Take note that this approach wasused because of customer requirements; the size of the database alsocauses some problem (over 200 tables) and we decided to use the samestructure on both side to minimize the support time.
I'm using SQL Server Express 2005, with mixed authentication mode. I would like to connect, manipulate data and administrate db's on my server without explicit permissions on those db's. I've created a new Login with SQL Server authentication and assigned it the public & sysadmin server roles BUT I can't even connect to db's where I don't have an explicit db role (db_owner, ddl_admin...)! What am I missing? How can I administrate a db without explicit permissions (db role) on that db?
I have a DTS that pulls a file from a networked drive (generally using the u: designation) for part of its process. Recently, the server has been dropping network drive connections, so sometimes the DTS will fail (I'm not certain why it's dropping the connections, nor do I have the access or control to figure out such.)
I've been told that I should change the DTS package to use an explicit path (i.e., \serverfolder1folder2file.mdb instead of u:folder2file.mdb) to work around this problem. However, Enterprise Manager doesn't seem to like using this for the "File name" attribute in the connection properties. (This is an Access connection, if that matters.)
Is there any way to get the explicit path (probably the wrong terminology here) to work with the connection properties?
I'm attempting to use FOR XML EXPLICIT in SQLServer to adhere to the following template: <group> <sourceid> <source></source> <id></id> <grouptype> <scheme></scheme> <typevalue></typevalue> </grouptype> <description> <short></short> <long></long> </descrption> </group>
Here's my code: SELECT 1 as Tag, NULL as Parent, @p_school_desc as [sourceid!1!source!element], @p_term_code as [sourceid!1!id!element], NULL as [grouptype!1!scheme!element], NULL as [grouptype!1!typevalue!element], NULL as [description!1!short!element], NULL as [description!1!long!element] FROM course_main AS sourceid where sourceid.course_id = @p_course UNION SELECT 1 as Tag, NULL as Parent, NULL, NULL, @p_destination as [grouptype!2!scheme!element], 'Term' as [grouptype!2!typevalue!element], NULL, NULL FROM course_main AS grouptype where grouptype.course_id = @p_course UNION SELECT 1 as Tag, NULL as Parent, NULL, NULL, NULL, NULL, @p_term_code as [description!2!short!element], @p_term_description as [description!2!long!element] FROM course_main AS grouptype where grouptype.course_id = @p_course FOR XML EXPLICIT
I'm receiving the foollowing error: Server: Msg 6812, Level 16, State 1, Line 14 XML tag ID 1 that was originally declared as 'sourceid' is being redeclared as 'grouptype'. Is there anyway to have two different child notes off of the same parent node?
I'm attempting to use FOR XML EXPLICIT in SQLServer to adhere to the following template: <group> <sourceid> <source></source> <id></id> <grouptype> <scheme></scheme> <typevalue></typevalue> </grouptype> <description> <short></short> <long></long> </descrption> </group>
Here's my code: SELECT 1 as Tag, NULL as Parent, @p_school_descas [sourceid!1!source!element], @p_term_code as [sourceid!1!id!element], NULLas [grouptype!1!scheme!element], NULLas [grouptype!1!typevalue!element], NULLas [description!1!short!element], NULLas [description!1!long!element] FROM course_main AS sourceid where sourceid.course_id = @p_course UNION SELECT 1 as Tag, NULL as Parent, NULL, NULL, @p_destinationas [grouptype!2!scheme!element], 'Term' as [grouptype!2!typevalue!element], NULL, NULL FROM course_main AS grouptype where grouptype.course_id = @p_course UNION SELECT 1 as Tag, NULL as Parent, NULL, NULL, NULL, NULL, @p_term_codeas [description!2!short!element], @p_term_descriptionas [description!2!long!element] FROM course_main AS grouptype where grouptype.course_id = @p_course FOR XML EXPLICIT
I'm receiving the foollowing error:Server: Msg 6812, Level 16, State 1, Line 14 XML tag ID 1 that was originally declared as 'sourceid' is being redeclared as 'grouptype'.
Is there anyway to have two different child notes off of the same parent node?
Hi, Is it possible to format any kind of XML file with the FOR XML EXPLICIT mode in SQL Server 2005? I am asking because I am trying to format my file in a way that I could get 2 or 3 elements on my parent or child level and put some attributes (icon path for example) in these 2nd element or 3rd element. I am unable to format it that way. Let me show you the way that I would like to have my XML file formated :
I want to have all my parent, children, sub-children to have these attribut but I am having difficulty putting one in the “Cell” tags. And I really think that my problem resides in the specifying of my column and their directives but I do not know how to fix it. See my icon path is commented here. I have to put the [row!2!cell!xmltext], but it is not working.
Here is the way that I am building my Select query.
SELECT 1 as tag, NULL as parent, NULL AS 'rows!1!', ----Root rows tag ID_metric as [row!2!id], Description_english as [row!2!cell!Element], ----Icon as [row!2!cell!xmltext], Sort as [row!2!sort], ID_metric as [row!3!id], Description_english as [row!3!cell!Element], --Icon as [row!3!cell!xmltext], sort as [row!3!sort], ID_metric as [row!4!id], Description_english as [row!4!cell!Element], --Icon as [row!4!cell!xmltext], sort as [row!4!sort], ID_metric as [row!5!id], Description_english as [row!5!cell!Element], --Icon as [row!5!cell!xmltext], sort as [row!5!sort], FROM #buildData WHERE HLevel = 0 UNION ALL SELECT 2 as tag, 1 as parent, NULL AS 'rows!1!', ID_metric as [row!2!id], Description_english as [row!2!cell!Element], --Icon as [row!2!cell!xmltext], sort as [row!2!sort], ID_metric as [row!3!id], Description_english as [row!3!cell!Element], --Icon as [row!3!cell!xmltext], sort as [row!3!sort], ID_metric as [row!4!id], Description_english as [row!4!cell!Element], --Icon as [row!4!cell!xmltext], sort as [row!4!sort], ID_metric as [row!5!id], Description_english as [row!5!cell!Element], --Icon as [row!5!cell!xmltext], sort as [row!5!sort], FROM #buildData WHERE HLevel = 1 UNION ALL SELECT 3 as tag, 2 as parent, NULL AS 'rows!1!', ID_metric as [row!2!id], Description_english as [row!2!cell!Element], --Icon as [row!2!cell!xmltext], sort as [row!2!sort], ID_metric as [row!3!id], Description_english as [row!3!cell!Element], --Icon as [row!3!cell!xmltext], sort as [row!3!sort], ID_metric as [row!4!id], Description_english as [row!4!cell!Element], --Icon as [row!4!cell!xmltext], sort as [row!4!sort], ID_metric as [row!5!id], Description_english as [row!5!cell!Element], --Icon as [row!5!cell!xmltext], sort as [row!5!sort], FROM #buildData WHERE HLevel = 2 UNION ALL SELECT 4 as tag, 3 as parent, NULL AS 'rows!1!', ID_metric as [row!2!id], Description_english as [row!2!cell!Element], --Icon as [row!2!cell!xmltext], sort as [row!2!sort], ID_metric as [row!3!id], Description_english as [row!3!cell!Element], --Icon as [row!3!cell!xmltext], sort as [row!3!sort], ID_metric as [row!4!id], Description_english as [row!4!cell!Element], --Icon as [row!4!cell!xmltext], sort as [row!4!sort], ID_metric as [row!5!id], Description_english as [row!5!cell!Element], --Icon as [row!5!cell!xml], sort as [row!5!sort], FROM #buildData WHERE HLevel = 3 ORDER BY [row!2!sort], [row!3!sort], [row!4!sort], [row!5!sort] for XML explicit
The FOR XML clause is invalid in views, inline functions, derived tables, and subqueries when they contain a set operator. To work around, wrap the SELECT containing a set operator using derived table syntax and apply FOR XML on top of it.
I have tried a couple of other ways but continue to get an error message.
I am trying to insert a new record into a sql table and I get the following error message:
Msg 544, Level 16, State 1, Line 1 Cannot insert explicit value for identity column in table 'tbl_MLS_ReportCriteria' when IDENTITY_INSERT is set to OFF.
Is there any difference between explicit inner join and implicitinner joinExample of an explicit inner join:SELECT *FROM employeeINNER JOIN departmentON employee.DepartmentID = department.DepartmentIDExample of an implicit inner join:SELECT *FROM employee, departmentWHERE employee.DepartmentID = department.DepartmentID
I gave a developer rights to the Public role on a SQL Server 2000 database. The Public role only has explicitly set select rights to the system tables and one user table. There are no other explicit rights set. The developer was able to open a table that had no rights set in enterprise manager and change data. Is this possible?
Hi, I am trying to create a XML out of sql 2005 database using FOR XML. I need to create XML for tables which may contain data having non-printable ascii characters (1-32 ascii character). I found FOR XML AUTO failes to genrate this XML, but i can genrate XML using CDATA section in FOR XML EXPLICIT. As following querie works fine for me.
SELECT 1 AS tag NULL AS parent, template_id AS [Emailqueue!1!user_id], misc1 AS [Emailqueue!1!!cdata] FROM Emailqueue WITH (NOLOCK) WHERE queue_id = -2147483169 FOR XML EXPLICIT
in above query misc1 column may contain some non printable ascii characters.
But i need to store this XML data in some sql XML variable as i need to pass it to store procedure which expects an xml input. While doing following i gets an error saying "illegal xml character"
DECLARE @XMLMessage XML
SET @XMLMessage = (SELECT 1 AS tag NULL AS parent, template_id AS [Emailqueue!1!user_id], misc1 AS [Emailqueue!1!!cdata] FROM Emailqueue WITH (NOLOCK) WHERE queue_id = -2147483169 FOR XML EXPLICIT)
I am doing all this exercise for SQL service broker. For which i even need to process same message using OPENXML on differen database server. Again which will need well formated XML.
Hi, I am mapping the .csv files to a destination table in sql server. The names of each column in the .csv file is the same as the ones in the destination table. In the destination table there is an extra field i.e. FileTypeField
Here is the question; When transfering the data from .csv to the destination table i.e flat file source to oledb destination, I would like to place a text into the field FileTypeField I do not see an option to do this. Can you help please? Thanks
