I am using openrowset to load Excel file. Some column has a very big number as Identify code. I've use IMEX=1 in the connection string. However, it still convert the big ID number to scientific notation and the lower digit number missed. Anyway to avoid it? Thanks...more >>

Hi all, we have 2 tables that will have huge data updations during the bussiness hours I have ran DBCC SHOWCONTIG on 2 tables and the data is shown as below Table1 ------ DBCC SHOWCONTIG ('table1') Table: 'table1' (251147940); index ID: 1, database ID: 7 [SQLSTATE 01000] TABLE l...more >>

Hello Ich was about to use DDL Triggers to extract all DDL-Level Events to a table for later Scripting. But when testing I found out that if a developer just right-clicks a Table and renames it inside Management Studio, that event doesn't get caught. Same when renaming a Column inside a tab...more >>

should be simple yet I don;t know how to write it . create table myTest(ProcessDate datetime , ID int , Total int, CONSTRAINT [PK_myTest] PRIMARY KEY CLUSTERED ( [ProcessDate], [ID] ) ON [PRIMARY] ) insert into myTest values ('1/1/7',1,8) insert into myTest values ('1/1/7',2,7...more >>

Hi, My company is willing to send me to a SQL developer boot camp and I was wondering which ones might be good. I have several years of SQL administration experience and understand Transact SQL. I would say my knowledge of Transact SQL is about 25% compare to an expert. Administration sid...more >>

We are having a problem with the way "for xml" is formatting the below xml. Example: Table 1: Queue ID Name Seq StatusID 1 Tom 1 0 1 Tom 2 0 Table 2: Status StatusID Description 0 Receive...more >>

Hello, Does anyone have any experience with the AccPac SQL Database structure? I'm trying to find some sort of database diagram and / or some SQL I could use to put together a P&L statement. Any help would be appreciated. Thanks! Rick ...more >>

I have a table with 25 fields. The 'valid' data will variably fill 1-n fields and leave NULL in the infilled fields. I need a query that will only retrieve the non-NULL fields. i.e. if Select f(1) if not NULL, f(2) if not NULL, ......from tbl..... I've tried IsNull(a,b) but that returns ...more >>

I am looking at SQL reporting services, Crystal Reports and other Business Objects packages for a reporting tool for software usage metrics collection. I don't necessarily need a highly complex solution but I would like to understand the benefits of each option (and under what circumstances the ...more >>

Hi, any suggestion for removing extra spaces from within a string and replace them with ONE space example 'This is a test!' to this: 'This is a test!' Thanks ...more >>

Hi All, We had stored date in (Unix time * 1000 ) in a column with float data type. I need to convert this number to datetime data type. When I run sql statement below against our table with 100,000 + rows I get an Arithmetic overflow error message: SELECT DATEADD(s, col_name, '19700101'...more >>

I've cut and pasted some modules from one package to another in VS. I've got some user defined variables. I've defined them with scope of the parent level. When I debug the code, it's telling me it can't find one of the variables. What's up? I've included them in the script definition f...more >>

Hi Table A has a_id and a_category Table B has b_id and a_id and b_category I want to update a_category to equal b_category where A.a_id=B.a_id Can you advise? Thanks Soc. ...more >>

Hi all I am looking at a query that someone designed in Access in the form of something like: SELECT A,B,C, E+F*G AS Var1, Var1 + H AS Var2 FROM XYZ Is there an equivalent in (T-)SQL of creating an alias (Var1) and then referring to it in subsequent parts of the SELECT (i.e Var1 + H AS V...more >>

Point one: Newbie Point two: I'm finding that I create SSIS files in Visual Studio, draw all the nice graphical boxes on the screen, then come back and try to open some of them and I get a dark grey screen. Should I be opening .sln files, .proj files, or .dtsx files? Sometimes, when I...more >>

hello When we execute SELECT statement, the following steps are taken ( I realize that each DB system may internally take completely different approach to arrive with the result, but conceptually at least these are the steps performed ): 1) temporary table ( call it T ) is created 2) the ...more >>

In SQL 2000, I need to pass in a variable to sp_makewebtask: set @custno = 9 (int field) EXEC sp_makewebtask 'd:\output\output.html', 'select custname from customer where customerid = '@custno' SELECT CONVERT(varchar,GETDATE()) SELECT orderdetail.Orderid,Productid,Produc...more >>

I am trying to merge data from 2 tables into one TableA and TableB TableA can have the following fields: SSN Field1 Field2 Fields3 TableB can have the following fields: SSN FieldA What I want to do is update TableA with TableB data from FieldA where TableA.SSN = TableB.SSN Her...more >>

SQL Server 2005, SP2 We have a web app that 'pages' through data requested by stored procedures. Currently, the stored procedure ones 2 SQL stmts: one SQL stmt to return the slice of data requested by the UI using ROW_NUMBER() and SELECT TOP(@n), and a second SQL to return the total number...more >>

I noticed that all our 2000 SP4 servers have exactly 137 alerts. I take this is standard?...more >>

We are setting up a new backup schedule. All the databases in this case will be in Full recovery mode. We have SQL Server 2005 and Red Gate SQL Backup. We want to do a full backup every week, a differential every day and a trans log backup 15 minutes. However, if the "script" determine...more >>

When a user loads the order form with a certain OrderID, I want them to be the only user who is able to make modifications to it. If another user loads the same form with OrderID, they will have only read access to it. The storedprocedure belows gets executed when the form loads. It updates t...more >>

Have data that I am retrieving from another server running Linux. I want to return all records associated with a ship date that occurred within the last six months. The ship date data is in YYYYMMDD format. I need some sort of expression that will return only items that have been shipped with...more >>

Hi people! I desperately need your help here. I need to find overlapping dates for each of the members: CREATE TABLE #test( [eligibility_ID] [int] IDENTITY (1, 1) NOT NULL , [last_name] [varchar(70)] NULL , [first_name] [varchar(70)] NULL , [region] [varchar(255)] NOT NULL , [eff_...more >>

Hi. I have a table containing an ID column and a Price column, as illustrated below. I need to create a stored procedure that selects "records having the closest price range to the specified record. ID (int) Price(decimal) ------------ 1 10.5 2 150.6 3 75.33...more >>

Hi All, Right now we get a daily email with the current currency exchange rate in it. Then someone copies that data into a SQL Sever table every day. I am looking to automate this process. The source of the exchange rate does not have to be the email. I would like the server to update the rate...more >>

I am trying to figure out how the Service broker works. I have been reading BOL and Googling, and it seems what I have should work. I'm on SS2005, but I don't have AdventureWorks or Northwind available to me. I'm wondering if I need a special server configuration setting. Can anyone help me? ...more >>

I have created a fulltext index (made up of many columns) against tables, and it works fine when searching for correctly spelt values, but I need to be able to perform “fuzzy” searches over the same index (similar to Soundex but against a fulltext index). Can I do this? E.g. find “Ac...more >>

Hi We are trying to use the sp sp_send_cdosysmail described and coded below on a SQL 2005 Clustered server. http://support.microsoft.com/kb/312839 We have another non-clustered SQL 2005 server running on the same domain as the same user where this does work. If I execute the SQL Code b...more >>

I've always used sp_executesql for various reasons, however, I am reviewing someone's code who is using the Exec approach, and I can't prove my approach is better. The stored procedure is used internally, so SQL injection isn't an issue. Performance seems to be the same for both, both are cach...more >>

Using VB for a list of SQL Server Names, using windows authentication how can you get their SQL Server version number? ...more >>

Can anyone show me T-SQL code that will determine if a primary key exists for a table? Thanks very much ...more >>

Device Table ----------------- deviceId bigint PK deviceName nchar(255) null Stats Table -------------------------------- id bigint PK DeviceID int FK to DeviceTable ScanTime datetime MonoPrintCount int null ColorPrintCount int null Every period (days,minutes,whatever), the system q...more >>

Hi all, Maybe it's a dumb question, but I have never seen an answer for it..yet. We have a table that is currently about 1 GB in size and occasionally a table scan is required for certain queries. Does anyone know how much of that table is loaded into memory for the scan to take place? ...more >>

Hi All, I am trying to form query at the runtime . In the below sample code i am trying to fetch a sector code from table and store it in a variable. This query is a dynamic query. But i am unable to execute this query. can any one help me.. Sample Code: Declare @svSecCode as Varchar...more >>

Is it possible develop a CLR function which apply .NET framework WF? ...more >>