2018-10-22         Audrey

Key DataType for a high volume SQL Server 2008?

I in the process of designing a database for high volume data and I was wondering what datatype to use for the primary keys?There will be table partitioning and the database will ultimatley be clustered and will be hot failover to alternative datacentres.EDITTables - think chat system for multiple time periods and multiple things to chat about with multiple users chatting about the time period and thing. Exponential issues are what I am thinking about - ie something could generate billions of rows in small time period. ie before we could change the database or DBA doing DB...

 sql-server-2008                     4 answers                     27 view
 2018-10-22         Horace

Select specific cells from multiple spreadsheets into SQL using SSIS

I need to loop through a series of spreadsheets (all in the same folder), pulling data from the same cells within the same named range in each, into an existing SQL database, using SSIS (SQL Server 2008 R2).I started by using the information in How to loop through Excel files and load them into a database using SSIS package? as a point of reference.However, because my files don't run in a strict columnar format (i.e. the whole of column C plus the whole of column E, etc.), I am struggling with it.My sheet is as follows:Basically, the area outlined in red (A6:E11) will be th...

 sql-server-2008                     1 answers                     32 view
 2018-10-22         Betty

SQL Server 2008 Pivot Data

If I have four columns called Person1, Person2, Person3 and Value.Is it possible to pivot all three Person columns into just one like below(?):Person Value-------------------Person1 10Person1 50 Person2 60Person3 0I am using SQL Server 2008. The process of transforming multiple columns into multiple rows is called UNPIVOT:select person, valfrom yourtableunpivot( val for person in (Person1, Person2, Person3)) unpiv;This could also be written using a UNION ALL query:select 'person1' as person, person1 as valfrom yourtableunion allselect 'person2' as person, person2 as va...

 sql-server-2008                     1 answers                     34 view
 2018-10-22         Zachary

TSQL optimize multiple Intersect

The problem query uses multiple Intersect. Changing it to an in ('alpha','beta','gamma','delta','epsilon','phi') group by having count() = 6 is not an option as the application supports like with wild cards (e.g. alpha%). But the count() = 6 query runs in less than 1 second. With wild card could use multiple joins and that is how it used to be structured. At 4 or less an Intersect performs better than the multiple joins but unfortunately did not test at 5 or more. Query performs great with any 4 terms - less than 1 second.Literally any 4 - first 4, last 4, or mid...

 sql-server-2008                     2 answers                     56 view
 2018-10-22         Quinn

Select top rows where last entry is March based on todays date

I've written SQL to produce 12 rows based on todays date. Basically it is a list of month numbers, month names and the year starting from the current month using GETDATE() and then adding one month on for each row and a year where appropriate. I now need to limit this data so that only rows up to and including March are displayed. So today I would want to see 4 rows for December 2014, January 2015, February 2015 and March 2015.I've tried adding in an additional column where April is always 1, going up to March which is always 12. I've also tried including another column...

 sql-server-2008                     1 answers                     69 view
 2018-10-22         Boris

How to select the last / latest edited row in Sql Server 2008?

I have a table T1, which contains id and name. If the name is updated from front end, it then creates a new row with updated name, hence creating two row for the same id. I want to get the last or latest updated row from the table. Note:The table is not capturing any date or time column. Below is the example, here I have to get the latest record with name Albert Dazy. I am using Sql Server 2008 Table T1: |---------------------|------------------| | id | name | |---------------------|------------------| | abc_0011 | Al...

 sql-server-2008                     1 answers                     69 view
 2018-10-22         Elvis

How to call stored procedure inside stored procedure?

I am defining one user defined table type in SQL Server 2008.This is the codeCREATE TYPE [dbo].[PayElementTable] AS TABLE( [EffDate] [varchar](30) NULL, [PayEle] [varchar](50) NULL, [ComType] [varchar](50) NULL, [Oper] [varchar](20) NULL, [Amount] [decimal](10, 2) NULL, [Rowno] [varchar](10) NULL)GOThen I create a procedure sptemptableCREATE PROCEDURE sptemptable @T PayElementTable Readonly,--user type as parameter @CTCID int ASBEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET N...

 sql-server-2008                     2 answers                     79 view
 2018-10-22         Janet

SQL Server 2008 NOT IN() seems to fail

I am building a suggestive tagging system (sounds seedy), I pass in a string to the db and if there is a match it sends back that tag. I want to stop duplicate tags, so if one tag is WEB, I only want it to be returned once. So if I had WEB as a tag and then add WEEKLY, typing "WE" doesn't bring up WEB for a second time as a suggestion.But, seem to be not working. I send the current tags and use NOT IN() to remove any duplicates. Below is the SP code:SELECT TOP 1 t.vTagName As vTagNameFROM Tags tWHERE t.vTagName LIKE @vTagName+'%'AND t.nTagPortalId = @n...

 sql-server-2008                     3 answers                     87 view
 2018-10-22         Guy

Table-Valued Parameters to CLR Procedures in SQL Server 2008 - possible?

This page from SQL Server 2008 BOL, talks about CLR Stored Procedures and has a section labelled, "Table-Valued Parameters", which talks about how they can be advantageous. That's great - I'd love to use TVPs in my CLR procs, but unfortunately this seems to be the only reference in the universe to such a possibility, and the section doesn't describe what the syntax would be (nor does the further information linked at the end of the paragraph)Sure, I can easily find descriptions of how to use TVPs from T-SQL procs, or how to do CLR procs in general. But writing a CLR proc th...

 sql-server-2008                     5 answers                     88 view
 2018-10-22         Jim

SSIS lost stored procedure metadata

We have an SSIS pkg that runs a stored procedure in an OLEDB source and writes the output to a file. The stored procedure has a condition in that it won't do anything if it's a certain day of the week. When it does produce data, it steps through several temp tables to generate the final data (which comes entirely from temp tables); the stored proc has SET FMTONLY OFF enabled at the start so it can send metadata to SSIS. (The two servers involved are SQL Server 2008 on Windows 2008 Server boxes.)Last night (the days it should not generate data are Sunday/Monday, and this ...

 sql-server-2008                     1 answers                     8 view
 2018-10-22         Steward

SSIS 2008 Check for flat file and take action if found

I have a package that needs to check if a file exists in a folder and if the file does exist then take a branch that will import the file to SQL Server and execute some stored procedures to process it. If the file does not exist then just end the current run of the package without error. I have all parts working just fine except for the file detection and branching depending on the results. (In other words currently it just runs as if the file is there and does the rest). I know how to use a script task to detect for the file and return an error if not found - I need to kno...

 sql-server-2008                     1 answers                     9 view
 2018-10-22         Adam

What permissions needed to connect to SQL Server Integration Services

I need to allow a consultant to connect to SSIS on a SQL Server 2008 box without making him a local administrator. If I add him to the local administrators group, he can connect to SSIS just fine, but it seems that I can't grant him enough permissions through SQL Server to give him these rights without being a local admin.I've added him to every role on the server, every database role in MSDB shy of DBO, and he's still not able to connect. I don't see any SSIS-related Windows groups on the server - Is membership in the Local Administrators group really required to connect ...

 sql-server-2008                     4 answers                     16 view
 2018-10-22         Valentine

SSRS returning different results than the stored procedure

I have a stored procedure that I can call from Management Studio and it returns 56 rows consistently. However, when I execute the query under from inside of ssrs 2008, I get back 61 rows. The stored procedure uses cursors and has some print statement inside of it. Thanks, I had a similar problem when I had an a stored procedure return warnings messages. It caused problems returning inconsistent results just in my SSRS dataset/report. I set ansi-warnings to off and my problem went away. You may want to comment out your print statements and try that.Thanks, Stev...

 sql-server-2008                     3 answers                     18 view
 2018-10-22         Colbert

How to backup a [SQL Server] database if privileges are not present?

I have complete readonly access to a SQL Server Database. If I try to do a backup, I get the following message.The EXECUTE permission was denied on the object 'xp_get_tape_devices', database 'mssqlsystemresource', schema 'sys'.How can I take a backup and load this data into my local SQLServer Express Edition? You can't. That's the whole purpose of the SQL Server security model. Otherwise, any valid database user with read access would be able to backup a DB and restore it somewhere else in order to view sensitive data. The last thing anyone wants is users messing with th...

 sql-server-2008                     1 answers                     19 view
 2018-10-22         Beacher

How can I insert data from SQL Server to SQL Azure?

I would like to write a query with data based on a SQL Server table and insert the results into a SQL Azure table. Is this possible in SQL Server Management Studio?For example: SELECT stuffINTO AzureTableFROM SqlServerTable I suppose it's possible to do something similar to that with a linked server to your SQL Azure host, but using SELECT INTO explicitly will not work - you'd need the destination table to exist first (see this MSDN blog post where they describe why SELECT INTO is not supported in SQL Azure). If the destination tables don't already exist, you can script...

 sql-server-2008                     1 answers                     81 view
 2018-10-22         Burgess

Display row of another table as column of current table

Consider there are two tables:Table1: **Result Total**Pass 102Fail 3Undetermined 1Table 2:**Pass% Fail% Undetermined%** 96.23 2.83 0.94 Result Needed:**Result Total Percentage**Pass 102 96.23Fail 3 2.83Undetermined 1 0.94How to convert the table 2 rows as column in table 1 to obtain the result ? first, You can try to do unpivot on Table2, then JOIN with Table1.Your sql-server version is 2008, you can use unpivot by UNION ALL.CREATE TABLE T1( Result VARCHAR(50), Total int...

 sql-server-2008                     1 answers                     36 view

Page 1 of 72  |  Show More Pages:  Top Prev Next Last