I've got an odd thing going on with a query. I have a table with two bigints, StartNum and EndNum that defines number ranges. I have an index on these two columns. The query takes a given number and returns the range it falls into. The WHERE clause is where @Num >= StartNum and @Num <= EndNum. There's a big difference in the Profiler b...
Is there away to cause a script to prevent running if an if statement is true even with "GO"'s? For example I want to do something similar to the following: insert into table1 (col1, col2) value ('1', '2') GO if exists(select * from table1 where col1 = '1') BEGIN --Cause Script to fail END GO insert into table1 (col1, col2) val...
Been working with SQL Server since it was Sybase (early 90s for the greenies) and I'm a bit stumped on this one. In Oracle and DB2, you can pass a SQL batch or script to a stored procedure to test if it can be parsed, then execute conditional logic based on the result, like this pseudocode example: if (TrySQLParse(LoadSQLFile(filename...
Hi, I've imported a country database from somewhere, with these fields: Abbr varchar(2), FullName varchar(50) An example tuple is: AL, "Albania" As you can see, the data in the second field is surrounded by quotation marks. I'd like to remove them, because they make the rest of my code just a little more annoying (I have to remove...
I need to get a dimension member returned as a calculated measure. Given: Dimensions Customer {ACME, EMCA, Universal Imports, Universal Exports} Salesperson {Bob, Fred, Mary, Joe} Credit Type {Director, Manager} Measures Credited Value Value Relationships The Customer is a dimension of the facts that contain Value The Custome...
I have tables in a local database I've built in Visual Studio 2008 that I want to publish to an existing database on the server that I will be deploying my Asp.NET app. Unfortunately the only two options I have for deploying this database when I select "publish" is -script to file- and -publish to shared hosting provider-. My dedicated...
I have a database table structured like this (irrelevant fields omitted for brevity): rankings ------------------ (PK) indicator_id (PK) alternative_id (PK) analysis_id rank All fields are integers; the first three (labeled "(PK)") are a composite primary key. A given "analysis" has multiple "alternatives", each of which will have a "...
Hey folks, Simple question here, and I know there are lots of kludgy ways to do it :) But I was hoping a SQL server expert had a script to easily script all the data out of a table? Or possibly all the tables in a DB? I'm getting tired of copying and pasting data over RDC! :) ...
Can someone please help me out with a query? In Microsoft SQL Server, I have the following query, which executes fine: SELECT * FROM ControlPoint INNER JOIN Project ON ControlPoint.ProjectID = Project.ProjectID INNER JOIN Site ON Site.SiteID = Project.SiteID WHERE Project.ProjectName LIKE '%Flood%' My problem is, when I try to execute...
Our primary database server is an 8 core box with 8GB of RAM. The CPU is a Xeon E7330 @ 2.4GHz. It runs Windows Server 2003 R2 (x64 edition) and SQL Server 2005 I wanted to do some testing so I set up SQL Server 2005 on another brand-new server which is an 8 core box with 4 GB of RAM. It has a Xeon X5460 @ 3.16GHz and runs Windows Serve...
I'm basically trying to copy data from a table in one database in SQL Server 2005 to another table, with the same structure (but lots of indexes) in another database in the same SQL Server instance. My current approach is the obvious INSERT/SELECT: set identity_insert TargetDBName.dbo.TableName on insert into TargetDBName.dbo.TableNam...
I have table in Database and the primry key is 'ID', Just I want to ask how can I make it AUTOINCREMENT I know that's esay Q, but I dont know how can I do it. thanks ...
This is a simple function that returns TRUE if the user exists in the users table, false otherwise. For some reason it is always return false, I even removed the WHERE clause and I still get false? (a manual check in query analyzer tells me I have lots of rows?) Public Shared Function DoesUserExist(ByVal userID As Integer) As Boolean ...
I have 5 large XML files which I am keen to analyse. All of them are too large to open in a text editor and so I do not know their XML schemas. I have tried to import them into SQL server, however the process has given me an error even though I am pretty sure they are valid, as they were sourced from very reputable programmers. I have ...
I created a report in SQL BI studio, and in the sql server 2005 Configuration tools made all the setup. In IIS the directory security is Anonymous Access ticked. I can access the website, but no one else can. I changed the setting to Integrate Windows Authentication and then the people could connect but they had the username and password...
I have images stored in my database in a FILESTREAM and I am trying to find out what the best solution is to get that image back out into a web browser. If I was managing the files on the file system myself, the quickest way would just be: Response.TransmitFile(pathToFile); This does not load the file into memory before transmitting ...
I get this error "Cannot call methods on int" when I try and execute the following sql statement in MVS datasource querybuilder. SELECT item_k.ItemID, item_k.Name AS Expr1, SUM(item_k.Price) AS TotalPrice FROM item_k INNER JOIN orderitems_k ON item_k.ItemID = orderitems_k.ItemID GROUP BY item_k.Name what seems ...
I would like to get the functionality of CONNECT BY PRIOR of ORACLE in SQL SERVER 2000/2005/2008? Please help me ...
In SQL Server 2005, I have a table say tbl_Info. There I have one column say Info. The values are as under Name1 Age1 Sex1 Name2 Age2 Sex2 ..... ..... ..... And the row values goes on like this. My question is how to make row col transposition. The desired output will be Name Age Sex Name1 Age1 Sex1 Name2 ...
I am running a SQL Server Agent job which zips up my database backups. The application I run is a C# Windows Application which takes in the path of the database as an argument. When I run the application form a command prompt all works well. However when I run it from within a sql server job using the Operating System (CmdExec) as the ty...