Foreach in sql with static data
Hi, I have 3 tables: Countries: Code, Name Vacances: id(autoincrement),CountryCode, NameOfCountry, numberOfdays How to foreach country insert Row: countryCode,'Welcome in " + nameofCountry +"!",12 ...
sql-server-2005
Strange Values in SYS.DM_TRAN_LOCKS table RESOURCE_ASSOCIATED_ENTITY_ID column
I’ve been trying to understand some strange values in the RESOURCE_ASSOCIATED_ENTITY_ID column of SYS.DM_TRAN_LOCKS when RESOURCE_TYPE is “OBJECT”. Although these should be object Ids, I cannot determine what object they actually refer to. I’ve tried everything I can think of, including querying all system tables with columns of type IN...
can't Remote debug a SQL Server 2005 Stored proc from within VS 2008 Team System
Hi, Am trying to debug a stored proc in SQL Server 2005, which is located at a remote location, from within my VS 2008 Team System (which is located in my local machine). The SQL Server and my machine are in 2 different domains. I tried to run visual studio using the Runas command for the User with which I am connecting to the remote sq...
SQL data execution plan
Does SELECT TOP 1000 * FROM TABLE return the same data execution plan as SELECT * FROM TABLE? Please also let me know if this should be moved to ServerFault. Thanks. ...
Hibernate Stored Procedure Results
I am forced to manage an auto-increment field from code. I've decided to write a stored procedure to do this for me. The idea is to have a procedure that inserts a new row and returns the auto-incremented value to Java so I can work with it further. The following is what I have so far. I don't know what to change to fill the gaps to ...
Can I use TSQL to operate on normal os files? Such as create a .bat file and write some query result into that file?
Hi guys, Can I use TSQL to operate on normal operating system files? Such as create a .bat file at c:\test and write some query result into that batch file? Thanks. ...
Executing a query on csv data stored in an ntext column
Say that the raw text of CSV exports and an associated timestamps are stored in a database, where one record is equivalent to one export. Does anyone have a way to execute a query on the CSV file stored in that field without creating a second connection to the database or exporting the data to a file and then reopening it using the csv ...
Optimizing ROW_NUMBER() in SQL Server
We have a number of machines which record data into a database at sporadic intervals. For each record, I'd like to obtain the time period between this recording and the previous recording. I can do this using ROW_NUMBER as follows: WITH TempTable AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY Machine_ID ORDER BY Date_Time) AS Orde...
sql server 2005 sp3
Hi there , i have installed sql 2005 express with sp3m but after that when i run an application that connects to a database attached to sql express i get this error message "failed to update,database is read only' is it a bug or what? when i uninstall sql express sp3 and install the older version things work fine any comments? ...
SQL Server 2005, wide indexes, computed columns, and sargable queries
In my database, assume we have a table defined as follows: CREATE TABLE [Chemical]( [ChemicalId] int NOT NULL IDENTITY(1,1) PRIMARY KEY, [Name] nvarchar(max) NOT NULL, [Description] nvarchar(max) NULL ) The value for Name can be very large, so we must use nvarchar(max). Unfortunately, we want to create an index on this col...
when i download file from my website which contains grid and in one of the column of grid there is download button.
Hi, I have page called filedownload.aspx , it contains grid and in grid they are three columns and in one of the column there is button which says Download , when user clicks on that button it picks file id and gives the download box to user with three options Open save and cancel . Now the problem is that when user clicks on the down...
Execute Statements in a Transaction - Sql Server 2005
hi, i need to update a database where some of the table have changed (columns have been added). I want to perform this action in a proper transaction. If the code executes without any problem then i will commit the changes otherwise i will rollback the database back to its original state. I want to do something like this: BEGIN TRANSA...
Declare global variables for a batch of execution statements - sql server 2005
hi, i have an SQL statement wherein i am trying to update the table on the client's machine. the sql statement is as follows: BEGIN TRANSACTION DECLARE @CreatedBy INT SELECT @CreatedBy = [User_Id] FROM Users WHERE UserName = 'Administrator' --////////////////////////////////////////////////////////////...
Recursive only 2 level records using common sql
I have an Employee table, it's a self-referencing table, with managerId refers to primary key empID. I want to find 2 level records by a given empId. For example: if given empId=5, if empId=5 has children records, display them, as well as the children in children records. The database is SQL Server 2005. update: I wanna a full project...
INSERT statement conflicted with the FOREIGN KEY constraint
I am getting the following error. Could you please help me in ? Msg 547, Level 16, State 0, Line 1 The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Sup_Item_Sup_Item_Cat". The conflict occurred in database "dev_bo", table "dbo.Sup_Item_Cat". The statement has been terminated. insert into sup_item (supplier_id,sup_it...
Adding FK Index to existing table in Merge Replication Topology
I have a table that has grown quite large that we are replicating to about 120 subscribers. A FK on that table does not have an index and when I ran an Execution Plan on a query that was causing issues it had this to say --> /* Missing Index Details from CaseNotesTimeoutQuerys.sql - mylocal\sqlexpress.MATRIX (WWCARES\pschaller (54)) T...
Database Connection Problem with MS SQL Database: JDBC-ODBC Driver from JSP
I'm using Netbeans 6.8 to develop application using JSP. I'm able to work with it properly in my project guides system. But i'm unable to get the connection to database from my system. It shows error unable to connect. I have not changed any of the codes. How can I fix this error? ...
if an object has no entry in sys.database_permissions are all of its permissions assumed to be "GRANT" ?
Let's say I have a function that is accessed in many stored procedures. If that function has no entries in sys.database_permissions does any account that can run one of the stored procs automatically run the function? This is the query I'm using: exec sp_depends 'dbo.theFunction' -- for each object in those results, run this quer...
What trace flags are needed for detecting deadlocks
I am trying to get better information on deadlocks that are occurring in my DB. After reading this article, it's still unclear to me whether I should be using trace flag 1204, 1222 or both. ...
What is the cost in bytes for the overhead of a sql_variant column in SQL Server?
I have a table, which contains many columns of float data type with 15 digit precision. Each column consumes 8 bytes of storage. Most of the time the data does not require this amount of precision and could be stored as a real data type. In many cases the value can be 0, in which case I could get away with storing a single byte. My goal...