I have a bunch of (50+) XML files in a directory that I would like to insert into a SQL server 2008 table. How can I create a SQL script from the command prompt or Powershell that will let me insert the files into a simple table with the following schema: XMLDataFiles ( xmlFileName varchar(255) , content xml ) All I need is for som...
I need to convert a number which gives decimals in factored format in to decimals, i.e. 11.16 is 11.5 in decimal. This is because 16 is in base of 32 and 11.16 should be read as 11+16/32 = 11.5 I get 11.16 as a string and I need to change it to 11.5 as numeric in a SQL Server 2005 database. Any shorter way of doing this rather than sp...
i have the following query to list the employees of two table. i need to update the a.staffdiscountstartdate to '20100428' how to rewrite the following query for this? select a.employeeid, b.employeeid from tblEmployees a left join tblCards b on a.employeeid=b.employeeid where Group...
goal: I have the string "1234432144" I want to only replace the first 2 4's with '10' so I would get '1231032144' Is there a way to do this in tsql? so far I have come up with the tsql substring() function substring('1234432144', 4, 2) which draws the 44 .. however how do i replace it within the existing string? If i wrap a r...
I have table to test score data that I need to pivot and I am stuck on how to do it. I have the data as this: gradelistening speaking reading writing 0 0.0 0.0 0.0 0.0 1 399.4 423.8 0.0 0.0 2 461.6 508.4 424.2 431.5 3 501.0 525.9 492.8 491.3 4 521.9 517.4 488.7 486.7 5 555.1 581.1 547.2 538.2 6 562.7 545.5 498.2 530.2 7 560...
One of my web pages allows the user to create a new record, and, at the same time, associate any number of images with that record. The way I am thinking of doing this is to upload each image to the server when the user click the 'upload' button in conjunction with a fileupload control. Then, when the user clicks the 'update' button fo...
I have a table full of Dealers along with their latitude and longitude. I am trying to determine the top n closest dealers to any given lat and lon. I already have the function to calculate distance between locations, but I want to do as few calculations as possible (my table can contain many thousands of entries). Currently I have to...
I'm deploying an example SQL CLR stored procedure which has a SQL CLR type as parameter using Visual Studio 2008 and menu Project -> Deploy. public partial class StoredProcedures { [Microsoft.SqlServer.Server.SqlProcedure] public static void TakeTariff(TariffInfo tariffInfo) { } } public class TariffInfo { public SqlDecimal...
So I have some data that looks like this. `USERID1 USERID2` 1 10 2 20 2 30 3 40 3 50 1 10 2 20 2 30 3 50 I want a query that produces the following `USERID1 COUNT` 2 2 3 2 It's a group by query that shows me ...
On my SQL Server 2008 I have a stored procedure with a large number of parameters. The first part of them is used in every call and parameters from the second part are used rarely. And I can't move the logic to two different stored procedures. Is there a way to encapsulate all this parameters to a class or struct and pass it as a store...
Hi, I'm trying to connect to a remote SQL Server Express machine using Windows authentication. I´m logged on with a user with username Administrator, but when logging in, I get the error message: Login failed for user guest ... Why is not using the Administrator account? Thanks in advance. ...
I have this really strange problem where my entity framework query isn't enumerating correctly. The SQL Server table I'm using has a table with a Sku field, and the column is "distinct". It isn't a key, but it doesn't contain any duplicate values. Using actual SQL with where, distinct and group by cluases I have confirmed this. Howev...
I have a user-defined table type. I want to check it's existence before editing in a patch using OBJECT_ID(name, type) function. What type from the enumeration should be passed for user-defined table types? N'U' like for user defined table doesn't work, i.e. IF OBJECT_ID(N'MyType', N'U') IS NOT NULL ...
Does SQL Server 2005 maintain built-in, queryable, row-level last-modified timestamp metadata? I'm doing some analysis on a database that did not include any sort of column to track a revision date/time stamp. I usually create columns for this purpose, but the designer of this database didn't, so I'm stuck with reconstructing history on...
I have a table which has essentially boolean values in a legacy database. The column names are stored as string values in another table so I need to match the column names of one table to a string value in another table. I know there has to be a way to do this directly with SQL in SQL Server but it is beyond me. My initial thought was t...
Hello everybody, I'm starting on a new iPhone project, and this application mostly relies on mySQL. I have a mysql database running on my computer, and I need this application to send queries to the server to gain information. One example is creating and logging into an account. I have successfully done this on my windows vb.net applica...
I have an issue with a deadlock in SQL Server that I haven't been able to resolve. Basically I have a large number of concurrent connections (from many machines) that are executing transactions where they first delete a range of entries and then re-insert entries within the same range with a bulk insert. Essentially, the transaction lo...
The query says it all, I can't find out the right syntax without without using a WHILE loop UPDATE Webtree SET Webtree.Sorting=w2.Sorting FROM ( SELECT BranchID, CASE WHEN @Index>=ROW_NUMBER() OVER(ORDER BY Sorting ASC) THEN ROW_NUMBER() OVER(ORDER BY Sorting ASC) ELSE ROW_NUMBER() OVER(ORDER BY Sorting ASC)+1 ...
My web service app on my Windows XP box is trying to log in to my sql server 2005 database on the same box. The machine is part of a domain. I am logged in in the domain and I am an admin on my machine. I am using Windows Authentication in my connection string as in "Server=myServerAddress;Database=myDataBase;Trusted_Connection=True". ...
The error is cannot convert object to sql parameter I'm working with a bol->dal, building an array of parameters to pass to the dal: bol: SqlParameter[] sqlParams = new SqlParameter[] { new SqlParameter("@p1", SqlDbType.VarChar, 30).Value = "val1", new SqlParameter("@p2", SqlDbType.VarChar, 30).Value = "val2" }; ...