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 need to manage the number of rows affected by a query. If I run this script on my database: SELECT COUNT(*) FROM TABLE_NAME WHERE TABLE_NAME.FLAG = 'I' AND NOT EXISTS (SELECT* FROMERR_TABLE_NAME WHERE ERR_TABLE_NAME.FIELD_1 = TABLE_NAME.FIELD_1 AND ERR_TABLE_NAME.FIELD_2 = TABLE_NAME.FIELD_2)
the query analyzer gives me back the number of lines affected:
----------- 4,00
(1 row(s) affected)
I need to manage this amount (4,00), so I applied this modification:
DECLARE @NUM_ROWS INT
SET @NUM_ROWS = (SELECT COUNT(*) FROM TABLE_NAME WHERE TABLE_NAME.FLAG = 'I' AND NOT EXISTS (SELECT* FROMERR_TABLE_NAME WHERE ERR_TABLE_NAME.FIELD_1 = TABLE_NAME.FIELD_1 AND ERR_TABLE_NAME.FIELD_2 = TABLE_NAME.FIELD_2) )
but it doesn't work!! the SRV returns me this: Server: Msg 107, Level 16, State 3, Line 3 The column prefix 'TABLE_NAME' does not match with a table name or alias name used in the query.
Do you know another way to store the number of rows affected?
I'm working on a reporting tool that could bring back hundreds of thousands of results back at once. I need some way to run the actual query only once a day, and then the reporting tool would just pull back this cached results. To be short, I need to figure out how to do this using a minimum amount of resources. Would a DataView work with something like this? How would I have it update only once a day? I appreciate any advice!
Hello, We have some queries that are long and intensive. We have thought about running the queries and storing the data in a text file for lookup from our website.
Example: Our online store only displays items that are in stock so when a user selects a category a query runs and grabs only items that are in stock and then displays them. There could be thousands of items the query needs to sort through before displaying the items that are in stock. What if we ran this query once every hours an stored the results in a txt file? The asp page would then go to the text file to grab the results instead of having to run the query every time a user selects a category. Will this speed up the site by not having to query every time? Would this be a correct way to eliminate queries that run thousands of times a day?
declare @str varchar(2000) set @str = 'SELECT * INTO #TmpTable FROM FormHistory' exec (@str) SELECT * FROM #TmpTable
gives the following error:
Invalid object name '#TmpTable'.
This is a very cutdown version of what I am trying to achieve so it might not seem obvious why I am writing it into a string and using exec but in the real code I do need to do this. I have cut it right back to try to get to the bottom of why this doesn't work. I suspect the # in the string is causing the problems.
Hello there,I just want to ask if storing data in dbase is much better than storing it in the file system? Because for one, i am currenlty developing my thesis which uploads a blob.doc file to a web server (currently i'm using the localhost of ASP.NET) then retrieves it from the local hostAlso i want to know if im right at this, the localhost of ASP.NET is the same as the one of a natural web server on the net? Because i'm just thinking of uploading and downloading the files from a web server. Although our thesis defense didn't require us to really upload it on the net, we were advised to use a localhost on our PC's. I'll be just using my local server Is it ok to just use a web server for storing files than a database?
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.
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 :(
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?
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 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
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?
