All columns that contain text in the database used to be nvarchar but we converted them to varchar for performance reasons. I am now left with all my SqlParameters still using SqlDbType.NVarChar and I want to change them to SqlDbType.VarChar but I don't want to waste time doing it if I won't see any benefits from changing them. Is it wor...
Is setting EXEC sp_addrolemember N'db_datareader', N'User' EXEC sp_addrolemember N'db_datawriter', N'User' Is the same as granting SELECT/INSERT/DELETE/UPDATE on all tables to User GRANT SELECT ON [Schema].[Table] TO [User] GRANT INSERT ON [Schema].[Table] TO [User] GRANT DELETE ON [Schema].[Table] TO [User] GRANT UPDATE ON [Sch...
Hi there. I have an Sql Server Table.. it's something like this: Id ...... Column1 ...... Column2 ```````````````````````````````` 1 ........ 1 ............. 34 2 ........ 1 ............. 44 3 ........ 2 ............. 45 4 ........ 2 ............. 36 5 ........ 2 ............. 23 6 ........ 3 ............. 68 7 ..........
In an attempt to close my question on connections remaining open and exceeding the maximum pool, I'm trying tor rewrite the function that is used to connect to our database. The function exists within a homegrown compiled library. using reflector I can see the code looks like this: public SqlProvider([Optional, DefaultParameterValue(""...
Possible Duplicate: How do I Create a Comma-Separated List using a SQL Query? I am working on a web application. I need the SQL query of single column selection like select recordid from device where accountid in (1,2)) I need this result to be formatted comma separated string from SQL. ...
I just upgraded from SQL Server 2008 to SQL Server 2008 R2. I went to create a trace, but now it's asking me to pick which options to show in the trace and it doesn't select any of them by default. I'm trying to get a trace that works like it did in previous versions of SQL Server, but I can't figure out which options to check in the T...
Hi there. Let me explain the context first. There are two databases sitting on two distinct servers on the same network. There is a stored procedure which copies data from a table in one database to a table in the other database (the two databases have same structure, but contain different data). Users do not have permissions to query ...
Hi there, I'm back with another (possibly) silly question. sorry. I have a pretty complicated query which joins 4 tables and computes the sum of a column based on the other two columns in two tables. the result returned is like this: Now, I want the results to be like the right hand side of the image. the number rows per month/year ...
I have the following integer type values in a SQL script: @year, @month, @day. Now I want to convert those into a datetime value. Should be easy, right? Well, I just went through the SQL documentation and was very surprised that I couldn't find any way to do this, other than converting to a string and then to a datetime. declare @dt ...
I have a table with registered users, in which i save year as varchar values simply because i take just an year. I want to create pie chart with ages to show which users are more likely to register. Query below gives me count of user ages which appear more than 5 times in Table to avoid small results. While these small results, below "...
Is there an SQL Server 2008 R2 Developer Edition? I have read there is, but I only see 2008 R1 Developer on Microsoft's website and can't find where to get it. If someone can point me in the right direction I'd appreciate it. I tried Microsoft's sales, but the person I talked to didn't seem to know. If I can't get it now I have to go bac...
I've got two tables that are linked with foreign keys. I need to do a few inserts on one table and then use the identity column values are part of my next batch of insert statements. This all has to be done through SQL. I've been reading about SCOPE_IDENTITY() but all the examples I'm seeing are using ASP/PHP/Whatever to do the substitu...
I've made a post here a link , asking some question about how to make statistics for number of users with from same age group so i can show pie chart. I want to take query in older post to higher level with percentages along with values... Means i to have count(userID) of certain age group / count(userID) of all * 100 to get percentage....
the situation is that there are certain stored procedures and/or ad-hoc SQL that is causing our CPU to spike from 30 to 80% and dropping all our indexes out of memory, I'm wondering if there is a well established way to correlate the performance spikes (CPU, Disk Read, etc.) to particular instances of SP running.. ...
Want to copy data from 1 table to other and they are in different servers. I have set 2 servers as linked servers. In SQL Server Management Studio, Server Objects --> Right-click Linked Servers, and then New linked server. I gave the connecting server as my servername SERVER2 and type as SQLSERVER. Now i executed the query, Insert Into...
I am getting these errors while executing the following SQL please help me ;WITH myCTE AS (Select mcisi.* from coke_packaged_item AS spi JOIN coke_item AS si ON si.coke_id = spi.coke_id AND si.coke_item_id = spi.coke_item_id AND si.shipper_flag = 'n' JOIN merch_cat_import_coke_item AS mcisi ON mcisi.coke_id = si.coke_id AND m...
I have a parameter for stored procedure, which I want to make optional and assign default value from a table, how do I do this? ALTER PROCEDURE [DBO].[Test] @planId int = 1 ( This I can do ) But I would like to do @planId int = ( Select Id from Table1 Where name = ‘ABC’) ‘ Assign default id from a table ...
Hi, The script below was written by a member (OMG Ponies) for the question i posted last night. There were syntax error in the script i could not figure it out. Can someone please give it a try? It used MS SQL Server 2000/2005/2008 select 'Steve' as name, '4/20/1960' as DOB, '12456' as agentID, 'Smith' as agentName into ...
I'm starting out with Linq To SQL, fiddling around with Linqpad and I'm trying to duplicate a SQL script which joins on tables in separate databases on the same server (SQL Server 2008). The TSQL query looks approximately like this: using MainDatabase go insert Event_Type(code, description) select distinct t1.code_id, t2.desc from O...
I execute SQL scripts to change the database schema. It looks something like this: using (var command = connection.CreateCommand()) { command.CommandText = script; command.ExecuteNonQuery(); } Additionally, the commands are executed within a transaction. The scrip looks like this: Alter Table [TableName] ADD [NewColumn] bigi...