I want to write a stored proc which will use a parameter, which will be the table name. E.g: @tablename << Parameter SELECT * FROM @tablename How is this possible? I wrote this: set ANSI_NULLS ON set QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[GetAllInterviewQuestions] @Alias varchar = null AS BEGIN Exec('Select * FROM Table a...
Hi there, i was wondering if there is a way to automatically append to a script file all the changes i am making to my columns, tables, relationships etc... The thing is i am doing a lot of different changes on a TEST db and the idea will be to apply this change script when i move the test db to production .. hence keeping production d...
Is is possible to increase the Rank of the ContainsTable results if its an Exact match. I have a fulltext query like this select iname,rank from ItemSearch_View sv INNER JOIN CONTAINSTABLE(ItemSearch_View,searchstring, '(FORMSOF(INFLECTIONAL, "google") )',500) AS itable ON sv.itemid = itable.[KEY] order by rank desc which provides ...
I created two database tables. SQL Server will not allow me to insert items into my second table, the one with the foreign key. The error I keep getting is Msg 207, Level 16, State 1, Line 53 Invalid column name 'ExemNonExemStat'. Following is my code. Please help, this is driving me crazy. ----Create Job Title Table CREATE TAB...
I'm having a C++ application connecting to the MS SQL Server 2005 using CDynamicAccessor. When my column is text or ntext, the CDynamicAccessor::GetColumnType always return DBTYPE_IUNKNOWN. I can bind the data as ISequentialStream and read the byte stream out. However, how can I tell if the column that I'm reading is text or ntext, and h...
Without stored procedures, how do you page result sets retrieved from SQL Server in ASP.NET? ...
During an ordeal yesterday, I learned that you can't pass this query to EXEC(): @SQL = @SQL + 'WHERE ID = ' + @SomeID EXCEC(@SQL) Where @SomeID is an INT and @SQL is NVARCHAR. This will complain about not being able to convert NVARCHAR back to INT during execution. I realized you have to do it like @SQL = @SQL + 'WHERE ID = ' + CONV...
I have 2 temp tables #temp1 and #temp. Both have a key and date columns. Both have around 25k rows. And I'm left joining them on the basis of the key and date which are unique on all rows. It's taking around 4 minutes for this join to complete. Is there any way to speed it up or any alternative methods? ...
We're extracting booking information for our tennis courts from our SQL database into a simple results table to help us build a picture of court usage. It's pretty straight-forward except when it comes to bookings that last more than an hour. At present, each booking results in a row in our results table. Each row contains a start-time,...
I am trying to sync up a SQL Server table with a Lotus Notes database. I have set up the NotesSQL ODBC driver and have been able to insert, update and select from the notes database form using the ActiveX Script Task in DTS. Everything works well until I try to insert Chinese characters into Text field in the notes database. After insert...
Hello! (Please note that I have seen a similar question on StackOverflow recently, however I can not find it anywhere - if anyone can find it please post the link) I have a table that has a datetime field in it. For example: Table data DateTime date; int number; I need to find the difference between date in a row and date in the ne...
I am working on a feather that export some tables(~50) to a disk file and import the file back to database. Export is quite easy, serialize dataset to a file stream. But when importing: table structure need to be determined dynamically.What I am doing now : foreach table in dataset (compare table schemas that in db and imported datas...
I have a database (Sql Server 2005) where there are dozens of tables, each of which has a number of columns (on average 10-20) with datatype set to nvarchar(max). This is absolutely killing performance (some of these columns are being used for joins and some of the tables have 100K+ rows). I would like to change all of these columns to b...
I need to extract some management information (MI) from data which is updated in overnight batches. I will be using aggregate functions to generate the MI from tables with hundreds of thousands and potentially millions of rows. The information will be displayed on a web page. The critical factor here is the efficiency of SQL Server's han...
Hello, We are not hosting our databases. Right now, One person is manually creating a .bak file from the production server. The .bak then copied to each developer's pc. Is there a better apporach that would make this process easier? I am working on build project right now for our team, I am thinking about adding the .bak file into SVN so...
Hi, i'm having doubts on how to create a table for temporary storing error messages. A lot of inserts and deletes A GUID as foreign key, likely to be left outer joined A simple nvarchar field for the ErrorMessage Which primary key, if any, and indexes should I use for storing a lot of data for a short period of time? Thanks ...
sys.dm_exec_query_stats seems to be a very useful function to gather statistics from your database which you can use as a starting point to find queries which need to be optimized. selecting * gives somewhat cryptic results, how do you make the results readable? What type of queries do you get from it? Are there other functions or querie...
At work we are having a bit of trouble with spatial support of SQL Server 2008. In SQL Server 2008 we have a big system in production going on, managing a bunch of important stuff. In some of the tables, I have pairs of coordinates and need to display them to ArcGIS and other GIS software. My question here really is: Is it possible to ...
I need to return newly created GUID from Stored procedure for each time i make a call. How can i achieve this? ...
I have a stored procedure that queries some data and emails it, I'd like to change the procedure so that any numbers in the thousands get displayed with commas like 1,000. I guess the best way of doing this in SQL is to convert it to a string and add the comma? Thanks ...