i have to insert a fake column at the result of a query which is the return value of a table-value function. this column must be uniqueidentifier type. the best way (i think ...) is to use 'newid()' function. the problem is I can't use the 'newid()' inside this type of function: Invalid use of side-effecting or time-dependent operator i...
I am trying to launch sql profiler 2008 from the command line launch. the syntax I used : C:\Program Files\Microsoft SQL Server\100\Tools\Binn>profiler90 /S server /D dbname /U sa /P password /T "C:\Documents and Settings\template.tdf" actual result: profiler opens, successful login happens but still I am forced to select a template. ...
Hi all i'm looking for a way to pass in a generated id from a sproc to another sproc. i.e. @name varchar(12), @descript varchar(55) @test_date date, @test_result varchar(1) BEGIN EXEC ts_create_item @name, @descript END if (@test_date is not null) or (isnull(@test_result_id,'')!='') BEGIN Exec ts_update_test_results @itemid(gener...
How do you track/manage your stored procedures, views, and functions in SQL Server? I'd like to use Subversion, but it looks like I would have to just save & commit the CREATE/ALTER statements. That might work okay for me, but I suspect I'd end up doing a lot of nagging. Is anyone using versioning with their databases? Is there a bette...
This SQL (called from c#) occasionally results in a deadlock. The server is not under much load so the approach used is to lock as much as possible. -- Lock to prevent race-conditions when multiple instances of an application calls this SQL: BEGIN TRANSACTION -- Check that no one has inserted the rows in T1 before me, and th...
A fairly comprehensive query of the brain has turned up a thousand and one ways to pass variable length parameter lists that involve such methods as: CLR based methods for parsing strings to lists of integers Table valued functions that require the presence of a 'Numbers' table (wtf?) Passing the data as XML Our requirements are to p...
Hi everyone!! I have a lambda expression that has this: Convert.ToDateTime(a.startTime).TimeOfDay >= Convert.ToDateTime(startTime).TimeOfDay But, I have to create a procedure in SQL Server and how should be the statement above to SQL statement? I've tried to use some kinda 'convert(startime, getdate(),8) but it didn't work. And I f...
I have an MDF file and no LDF files for a database created in MS SQL Server 2005. When I try to attach the MDF file to a different SQL Server, I get the following error message. The log cannot be rebuilt because there were open transactions/users when the database was shutdown, no checkpoint occurred to the database, or the database...
I have an Access 2003 app that connects to a SQL Server 2000 box. I have a table in which I need to lock down a record along with all related records in different tables. By "lock down", I mean mark them as read-only so that no clients can edit those records unless an admin unlocks them. Any ideas? ...
Hullo to all! This question is more about a shortcut than anything: Is there a simple, yet efficient way to associate column names to csv data? Problem: I need to associate column names (and bind them) to import the csv file correctly to my SQL Server database. I don't know before I see the csv what column of the csv will contain what...
Linq to SQL and Linq to Entities depend on creating dynamic SQL to do a lot of their work, specially when you have classes represent database tables in some fashion. However if the database(s) does not allow ad hoc SQL queries and everything has to go through stored procedures, I don't see the big value of using L2Q or L2E if the develop...
I've checked BOL and I don't see what I'm looking for. I know that Oracle has a "WITH READ ONLY" option when creating a view. I don't see this option in SQL2000. Do I need to add an INSTEADOF trigger to accomplish this? In short, I don't want users of the view to be able to update the data. Thanks ST ...
I am currently storing normalized versions of strings in my SQL Server database in lower case. For example, in my Users table, I have a UserName and a LoweredUserName field. Depending on the context, I either use T-SQL's LOWER() function or C#'s String.ToLower() method to generate the lower case version of the user name to fill the Low...
HelLo Is there any way to password protect an SQL server database without using commercial third party tools? What can help me achieve this? Please help...Thanks ...
When I go to the 'Add New Item' window in Visual Studio 2005 and try to add a SQL Database it hangs for a bit and then give the following error: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct...
Hi everyone! I'm trying to make this SP run: myDataContext.InsertEvent(codEvent, dateOfEvent, statusOfEvent); codEvent is an int; dateOfEvent is a DateTime; statusOfEvent is a char. The problem is Visual Studio is throwing an error if I pass a statusOfEvent null. And, my SP accpts nullable char just like my table. I've tried to do ...
Is there any information as to how SQL Server compares strings and handles searching in them (like statments)? I am trying to find out if there is a way to determine how efficient it is to store information as a large string and use sql server to do a bunch of comparisons on rows to determine which match. I know this is potentially goi...
The scenario is this. I have a SQL Server database online that I am demoing an application. During development, I have added extra fields, modified field types, changed keys and added some new tables locally. What's the best way for me to update the online database with the new structure and not lose the data? The database is a SQL Serv...
What would be more efficient in storing some temp data (50k rows in one and 50k in another) to perform come calculation. I'll be doing this process once, nightly. How do you check the efficiency when comparing something like this? ...
Consider the following scenario: Tables: Employee (EmpId(PK), Name) TeamMembers(TeamId(PK), EmpId(PK)) Project(ProjId(PK), TeamId) I really want to avoid using composite PK, but the only way I see out of the problem is creating a Team table with only 1 column TeamId(PK) (i do not want to store any info associated with the team othe...