sql-server

Char count in sql server

Input: AA1 The output will be: Char Count A 2 1 1 ...

Grouping records with or without a foreign key constraint

I have a table containing objects, something like this: PK ObjectId FK ObjectTypeId Description etc The objects need to be grouped. I have been given a number of suggestions, all of which 'work' and some I like more than others. None of them are perfect so I'm struggling to settle on any particular model. 1/ Add a self-referential ...

.Net DbDataAdapter has wrong primary key on filled datatable and i want to correct this

Hi all, .NET/MSSQL Server Question: I use a System.Data.SqlClient.SqlDataAdapter to connect to a database and read (.Fill) a datatable from a view in the database (select * from v_coolview). The problem is that the view consists of multiple tables (of course) and the resulting DataTable has typically a primary key set (Datatable.Primar...

Query/Database Optimization: How to optimize this? (and should I use a materialized view?)

Hi -- I have a question on how to optimize a query. Actually, as I'm going to be running the query frequently, I was thinking of using a materialized or indexed view (is this a good idea here?) or denormalizing. Consider the following four tables (with irrelevant fields omitted): Users (int userId) Groups (int groupId) GroupMembershi...

select top 1 * returns diffrent recordset each time

Hi all, In my application I use SELECT TOP 12 * clause to select top 12 records from database and show it to user. In another case I have to show the same result one by one. So I use SELECT TOP 1 * clause,rest of the query is same. I used Sql row_number() function to select items one by on serially. The problem is SELECT TOP 1 * does...

Interview - What questions asked by Project Managers

Hello everybody, Been for an interview in regards to ETL Developer (Microsoft Toolset / SQL BI suite) - been success through technical interview - reference check done - all good. As the PM was not there during interview period, he wants to have another interview before finalising something. What normally PM interview questions are...

Why is the database diagrams view still showing dropped tables

Using SQLServer 2005 I have dropped some tables (via a T-SQL query) and they are still showing as tables when creating a database diagram! Already tried refreshing, disconnecting and reconnecting. ...

SQL Server doesn't use index in stored procedure

I haven't solved this issue by using the stored procedure, yet we've decided to surpass the SP and just execute the plain ol' SQL Please see the extended table scheme below Edit 2: Updated the index (to not use actieGroep anymore) NB. SQL Server 2005 Enterprise 9.00.4035.00 NB2. Seems related to http://www.sqlservercentral.com/Forums/To...

While Using Distinct still getting duplicate values?

Using SQL Server 2000 How to avoid the duplicate values? Query SELECT DISTINCT Modification.dbo.Reference.Dates AS DailyDate, tmp_Cardevent2.PERSONID, tmp_Cardevent2.empname, tmp_cardevent2.cardno, tmp_Cardevent2.titlecode, tmp_Cardevent2.titlename, tmp_Cardevent2.departname, CASE WHEN tm...

Finding Total Log Size of each DataBase in the SQL Server 2005?

Possible Duplicate: How to find out size of the individual databases in the sql server 2005? How can I get the log size of a database in SQL Server 2005 Due to permission problem ,I am unable to use the command DBCC SQLPERF(logspace),I am unable to view the properties of the database. Please provide me the query or Advise. ...

How to get SQL Server Date in its native format?

I need to get a Date from an SQL Server Database (SQL Server 2000), the date is stored as such: 2009-09-30 00:00:00.000 I want to pass this date into a Classic ASP function to use this date in the Database somewhere else, however when I get the date, it converts in my ASP code to a locale-dependant version, such as: 30/09/2009 I ju...

Concurrent update on the same record

Hello Everybody. I'm facing a strange issue with some TSQL code on SQL2005. The piece we suspect is generating the issue is: INSERT INTO SGVdProcessInfo ([StartTs]) VALUES (GETDATE()) SELECT @IdProcessInfo = SCOPE_IDENTITY() UPDATE TOP(@quantity) [SGVdTLogDetail] WITH (ROWLOCK) SET [IdSGVdProcessInfo] = @IdProcessInfo WHERE ...

DATENAME(MONTH,GETADATE()) is returning numeric value of the month as '09'

Hi, When I run the following query [SELECT DATENAME(MONTH,GETDATE())], ideally speaking it should return value as 'September' but its returning value as '09'. I am running this query on MS SQL Server 2005. Is there anything I need to configure with MS SQL Server 2005? Please find the details of the SQL Server 2005 Component Name ...

How to get a total of time?

Using SQL Server 2000 My table column values are datetime Column1 Column2 1/1/1900 12:20:45 1/1/1900 23:22:25 1/1/1900 09:00:00 1/1/1900 18:10:30 and so on… 12:20:45 means (HH:MM:SS)` Here I want to take a total of column 1 and total of column 2 Expected Output column1 column2 1/1/1900 21:20:45 1...

Web gui for reporting services

Hi, Have a project where were building custom business reports accessible via web browser. As we run SQL server I would like to use reporting services instead of writing custom reports in my application. Is there a way to publish a GUI for generating reports from reporting services on the web, or is there any convenient way to get the ...

Another simple SQL question

Hello everyone, i have this query and count(artikel.artikelid) returns 2 instead of 3. i know why, because i have the artikelid twice (8135) in the 'where in...' part but i would like it to return 3 here is my problem query: SELECT cg.CategorieGroepID, cg.GroepsNaam, cg.BenodigdAantal, cg.ActieCatI...

Would it be a bad idea to develop a desktop application that directly accesses the SQL server?

Hi! I want to install a desktop application (on many stations - about 10-20) should access the SQL Server directly, no Services, and no server-DALs. The application will be installed on a local network on about 10 machines, while one of them is a server. When I will install the program I will set the connection string, and the applicat...

Sql Server Computed Column Formula syntax

I want to use a computed bit column that will be true if another column in the table is not null. What's the correct formula for this? HasLabel = computed column (bit) Label = varchar NULL The following formula does not validate - what am I missing? Formula for HasLabel = Label IS NOT NULL ...

Configuring Remote Database Connection Strings within SQL CLR Assemblies

I'm writing a SQL CLR assembly that will be deployed to a third-party database server, but which needs to execute stored procedures residing in my database server. All the pieces are in place, but I don't know how to make the connection string configurable. Articles such as this: http://msdn.microsoft.com/en-us/library/ms345135%28SQL.90%...

Script all objects in a database into a table

I need to populate a table in SQL server 2005 with some objects in a particular database (stored procedures, tables, views and functions, etc) In the table I would like one line per object and the table columns will be the name of the object , the script of the object, schema and object type. I have tried to use the script below but ...