|
i wnat to inset the following data into a table. The data is an xml file as folows
-------------
<root>
<employee>
<id>20</id>
<fname>tom</fname>
<lname>smith</lname>
</employee>
</root>
---------------------
i have the follwoing stored procedure that takes the xml text as a parameter, insert the data and return the table data back to me - but im getting this error = "Incorrect syntax near the keyword 'FROM'" and "Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon."
Help Please
--------------------------------
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
create PROC [dbo].[sp_add_employee1]
@empdata text
AS
DECLARE @hDoc int
exec sp_xml_preparedocument @hDoc OUTPUT,@empdata
INSERT into Employee (id, fname, lname)
SELECT FROM OPENXML (@hDoc, '/root/Employee')
WITH Employee (id varchar(50),fname varchar(50), lname varchar(50))
EXEC sp_xml_removedocument @hDoc
SELECT *
from Employee
FOR XML AUTO
-------------------------------------------
|
|
|
|
|
ONeil Tomlinson wrote: SELECT FROM OPENXML (@hDoc, '/root/Employee')
SELECT * FROM OPENXML (@hDoc, '/root/Employee')
|
|
|
|
|
Hi All
We have just upgaded our old sql server from 2000 to a 2005 cluster server
I am now having problems running some bcp commands
The command i am running is
exec master..xp_cmdshell 'bcp "select fileoutput from [new_db]..[Invoice_Table] order by roworder" queryout c:\invoice\template\customer.html -c -o c:\invoice\template\bcpOutput.txt'
the error i am getting is
User name not provided, either use -U to provide the user name or use -T for Trusted Connection
i have tried to add in -U and -P but then get the below
SQLState = 08001, NativeError = 2
Error = [Microsoft][SQL Native Client]Named Pipes Provider: Could not open a connection to SQL Server [2].
SQLState = HYT00, NativeError = 0
Error = [Microsoft][SQL Native Client]Login timeout expired
SQLState = 08001, NativeError = 2
Error = [Microsoft][SQL Native Client]An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.
i have also tried adding in
exec master..xp_cmdshell 'bcp "select fileoutput from [new_db]..[Invoice_Table] order by roworder" queryout c:\invoice\template\customer.html -c -o -STCSQL1\sqlcluster c:\invoice\template\bcpOutput.txt'
but i get the same error saying it could not open a connection to SQL server
Can anyone help at all ?
|
|
|
|
|
Didn't figure out if you have used all -P, -U and -S at the same time.
If not, provide username, password and instancename for command arguments.
Also check that the server is listening named pipes since it seems to be configured as the protocol for the client component.
Hope this helps,
MIka
|
|
|
|
|
Hi,
I'm trying to create a trigger for a table which has a column of type TEXT. While executing Trigger Statement, It shows a message stating that " can't use text field in inserted/deleted table".
Can anyone give me the solution for this problem?
Thanks in advance
sri
|
|
|
|
|
Hi,
If possible use VARCHAR(MAX) instead of TEXT data type. TEXT should not be used anymore.
If not possible, use INSTEAD OF trigger. Normal AFTER trigger does not support TEXT datatype.
Hope this helps,
Mika
|
|
|
|
|
SELECT * FROM CITY_POPULATION;
CITY_ID CITY_POPULATION
------- ---------------
1 100
2 200
3 300
SELECT * FROM CITY_INFO;
CITY_ID CITY_NAME
------- ---------
1 FIRST CITY
2 SECOND CITY
3 THIRD CITY
How can I have some sort of union query that would have the city id, name and population in one output where the id columns from the two tables match, I mean, the city names from the second table should be picked up to match their id number from the other table showing how much population is in that country id with it's name.. something like:
ID, Country Name, Population
Many thanks guys!
modified on Wednesday, September 3, 2008 3:30 PM
|
|
|
|
|
You would use a join for this.
Select *
From City_Info i
Inner Join City_Population p on
p.City_Id = i.City_Id
The output should be similar to:
City_Id City_Name City_Id City_Population
------ ------ ------- ------------
1 First City 1 100
2 Second City 2 200
3 Third City 3 300
You probably wouldn't want City_Id two times, so you would need to specify the columns that you want in the select list. Be sure to qualify the column names so you don't get an ambiguous column error (ex: i.City_id, i.City_Name, p.City_Population).
|
|
|
|
|
Thank you
Just what I was looking for!
All generalizations are wrong, including this one!
(\ /)
(O.o)
(><)
|
|
|
|
|
Sorry Scott, but I still have a question though
Using your inner join statement, why do I get the same value from any column of the second table, like population here from city population would be 100 for the whole column
Please help me Mr. "Scott Tiger  "
All generalizations are wrong, including this one!
(\ /)
(O.o)
(><)
|
|
|
|
|
Do you really want to union or perhaps join?
Do you want output like :
City_Id City_Population CityName
----------------------------------------
1 100 First City
2 200 Second City...
or something else?
|
|
|
|
|
Mika Wendelius wrote: union or perhaps join?
 Thanks for the hint mate!
All generalizations are wrong, including this one!
(\ /)
(O.o)
(><)
|
|
|
|
|
No problem
Seems that Scott has faster (and more describing) fingers than I 
|
|
|
|
|
I have a problem with connecting to a sql server with ODBC source. I can create this source without any problems in administration tools -> ODBC sources.
But when I try to connect to the SQL server I get an error saying that the ODBC source name doesnt exist.
I have tried to connect directly to server via Excel import data and it works well, but with ODBC source I get this error.
Someone with any idea of the problem
|
|
|
|
|
Few possibilities:
- Verify that the DSN name is written correctly when using DSN.
- When creating the source, use test functionality to verify, that configuration is valid.
- Try to use System DSN if you have used User DSN.
|
|
|
|
|
-I have verified it, everything is correct
-Its tested and it is successfully connected
-I have tried that
Its just weird, i can create this odbc connection without any problems. but connecting with it just dont work
i have gotten it to work in other places, but its this place i cant get it to work
|
|
|
|
|
That's odd...
Check that you don't have another DSN (system or user) with the same name.
If not helpful:
- delete the DSN
- check from the registry that it's gone (HKEY_CURRENT_USER\Software\ODBC\ODBC.INI or HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI)
- create it again (use a different name just in case)
- again verify from registry that it's configured as it should be.
|
|
|
|
|
|
Could you clarify these:
- what is the type of cmd
- which class is giving the exception
- what method are you using when exception is given
|
|
|
|
|
You scared him out to delete his post! 
All generalizations are wrong, including this one!
(\ /)
(O.o)
(><)
|
|
|
|
|
Hopefully not. At least I had good intentions
|
|
|
|
|
I figured out my own problem just after posting the message. It was a subtle syntactical error.
Nonetheless, I have spent many days trying to debug datatype errors, in which using cmd.Parameters.AddWithValue(...) couldn't seem to get the type right. Finally I began adding the parameters first, with the type specified using cmd.Parameters.Add(..., type) and then adding the value to the parameter.
So far this has eliminated the frustrations of my commands not working. However, the jury is still out for me as to whether using parameters is THAT much more convenient or less work that concatenating values into a string. I have a pretty good system worked out where I separate each concatenated value on a separate line my code (for readability) and use a quote(arg) function and formatDate(arg) function for convenience.
Public Function quote(arg as String) as String
Return chr(34) + arg + chr(34)
End Function
You get the idea.
I find it simpler to debug a query in which I can see all the values by simply printing the string to the debug window, rather than iterating through the parameters collection manually in the immediate window.
|
|
|
|
|
Good to hear you got it solved.
I see your point, but I believe that parameters benefit you in many ways so I encourage you to use them.
One idea that may come handy: Derive your own class from OleDbCommand and use that for database operations. If you need to see the statement and the values for parameters, create a helper method in derived class (of course you can separate this to a helper class). In that method list all the info you need to the output window. Something like:
public void WhatsInside() {
Debug.WriteLine(this.CommandText);
foreach (OleDbParameter param in this.Parameters) {
Debug.WriteLine(param.DbType + param.value + ...);
}
other possible info...
}
Added:
This method can be called from immediate window while debugging so calls to it doesn't have to exist in code. Also this should be included only in debug builds.
Mika
modified on Wednesday, September 10, 2008 4:41 PM
|
|
|
|
|
I need to get a value from previous row in result with any extracted row... ?
here's with an example
Table structure
empid EmpName Otherdetails
1 Robert abcd
2 Peter abcd
1 Bob abcd
Above given is history table with records added for any update or delete...so Robert was the record inserted to main table and Record was updated as bob. So we have two records in history table as above with same id and modifieddate to get order
Now here Robert updated his name as Bob....now in result set I need to get a list of employees and their previous name like..
Id NewName OldName
1 Robert Null
2 Peter Null
1 Bob Robert
Can I get this somehow? Any hint or suggestions welcome..
Keep DotNetting!!
GeekFromIndia
modified on Wednesday, September 3, 2008 11:23 AM
|
|
|
|
|
Show here your update command to see how you make that update.
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
