I have an XML stored procedure in MS SQL 2005 which I use the SqlCommand.ExecuteXmlReader to get an XmlReader, then parse through the data and form an XML document. The problem is that the data in SQL contains some binary characters which are illegal within a UTF-8 XML document, so an exception is thrown. Has anyone else dealt with this...
I was looking at the source of sys.sp_dbcmptlevel in SQL Server 2005. In the source, there is this line I do not understand how it works. EXEC %%DatabaseEx(Name = @dbname).SetCompatibility(Level = @input_cmptlevel) It doesn't appear that DatabaseEx is a stored procedure. -- does not return any result select * from sys.procedures whe...
I have to compute a value involving data from several tables. I was wondering if using a stored procedure with cursors would offer a performance advantage compared to reading the data into a dataset (using simple select stored procedures) and then looping through the records? The dataset is not large, it consists in 6 tables, each with a...
In Mysql there is a compare operator that is a null safe: <=>. I use this in my Java program when creating prepared statements like this: String routerAddress = getSomeValue(); String sql = "SELECT * FROM ROUTERS WHERE ROUTER_ADDRESS <=> ? "; PreparedStatement stmt = connection.prepareStatement(sql); stmt.setString(1, routerAddress); ...
What's the equivalent of an MS-Access crosstab query in TSQL? And Is there a better way? I have a data organised like this: Fish ID Name ---- --------- 1 Jack 2 Trout 3 Bass 4 Cat FishProperty ID FishID Property Value ---- ------ -------- ----- 1 1 Length 10 2 1 Girth 6 3 1 Weight 4 4 ...
Hi, I have just created a report in Report Manager using a Stored Procedure which uses a temporary table. I'm getting the error "Could not generate a list of fields for the query. Invalid object name '#xxxx'" I read this is because when Report Manager tries to PrepareQuery it runs into difficulty because the temporary table doesn't ex...
Hi guys, I'm new with postgresql, and already have my first problem.. Well, I wrote some code to understand how transactions work, following step by step the manual. To make it short, I've created 2 tables, user and movements: in the first one there are the name, email and credit columns, in the second the columns from, to, import. So...
I have a table orders that keeps all order from all our stores. I wrote a query to check the sequence orders for each store. It looks like that. select WebStoreID, min(webordernumber), max(webordernumber), count(webordernumber) from orders where ordertype = 'WEB' group by WebStoreID I can check it all orders are present with this qu...
Does anyone have an example of a stored procedure which makes a connection to a remote server? I have been searching the web and have so far discovered that it might can be done using sp_addlinkedserver and sp_addlinkedsrvlogin but I haven't found a good example and I don't understand the documentation that well. UPDATE: None of the t...
I have a small (200 rows / 400kb) table with 4 columns - nvarchar(MAX), nvarchar(50), and two ints. I'm having a problem with one particular row in which I can select and update the int fields, but when I attempt to select or update the nvarchar fields, the query runs indefinitely (at least 45 minutes before I cancel). I'm also unable ...
Is there an easy way to export and then import users/permissions from one Sql Server 2005 instance to another? ...
I've been asked to create a financial report, which needs to give a total commission rate between two dates for several 'referrers'. That's the easy part. The difficult part is that the commission rate varies depending not only on the referrer but also on the type of referral and also on the number of referrals of that type that have be...
Hi. I'm trying to SELECT from the dba_tab_cols view from within a stored procedure. It's not working and I don't know why. If I execute the following SQL as a query: SELECT t.data_type FROM dba_tab_cols t WHERE t.table_name = 'ACCOUNTTYPE' AND t.column_name = 'ACCESSEDBY'; it works fine. However if I copy it into a stored p...
I can't really think of the best way to phrase this question, so I'll just give an example. Suppose I have a table that is created like this: CREATE VIEW People AS SELECT id, --int name, --varchar(20) birthdate --datetime FROM SomeTable If I wanted to change this from a view to a physical table, is the...
Can't find any answers on the net, has anyone tried this? My stored proc processes deletion of tokens found across multiple tables and it is set to run for a specified number of "cpu service units" by using the ASUTIME LIMIT integer stored proc definition/characteristic. When this limit is met, will the stored proc commit or rollback th...
Rolled back to revision one, then edited somewhat. See revised question. I have an interesting issue with the below SELECT. Its about ORDER BY clause; I am trying to sort using a variable called "p_sortby". Order by can be used by column name or column position, (1, 2, … etc. ). Somehow, if I use position in the PL/SQL, it doesn’t w...
I have two tables in MS-Access, U and R. I have joined them together and want to compare two fields. However, because there are X Tags in both to compare I am getting X lines. As you can see on ID 4 and 2 there are two tags in each table and they are both the same however because of my query I get four lines back because it compares all...
Suppose we have two tables. Post and Comment. Post has many Comments. Pretend they are somewhat filled so that the number of comments per post is varied. I want a query which will grab all posts but only the newest comment per post. I have been directed to joins and sub queries but I can't figure it out. Example Output: Post1: Comment...
I am using a stored procedure to fill a DataSet. What I need to do is force the name of the DataTable that is created when filled. There are multiple tables returned from the Stored Procedure. The last table is the one I need to make sure has a specific name when returned. It is created by returning a value of a variable and not pull...
I am trying to import a CSV file into MySQL 5.0 with the following line: LOAD DATA LOCAL INFILE 'file' INTO TABLE MYTABLE FIELDS TERMINATED BY ';' ENCLOSED BY '"' ESCAPED BY '\\' My table schema is as follows CREATE TABLE AUCTIONS ( ARTICLE_NO VARCHAR(20), ARTICLE_NAME VARCHAR(100), SUBTITLE VARCHAR(20), CURREN...