All, I am doing a fairly simple ETL accross from a SQL Server to a Linked Server. I am doing the processing with a stored procedure and t-sql. The tables I am loading contain a foreign key which is an identity field of another table. I am using SCOPE_IDENTITY to attain the value inserted a...more >>

Hi, I have a couple ways that I figured out how to do a query but am not sure of the best way. I was wondering if any of you knew which is "better" for T-SQL. Here's the SQL for setting up the tables: CREATE TABLE [Teams] ( [TeamID] [int] IDENTITY (1, 1) NOT NULL , [Name] [nvarchar]...more >>

Is there a general rule when to use char and when to use varchar? I'm talking about short, simple fields, like "last name" etc. I had thought that varchars would be useful, since I won't need to trim them after retrieving their values; but instead I have to trim before storing, so there's n...more >>

I'm new at this so bear with me. I have a view (Cde_Unique) that contains the field Cde_26. This is a unique list of codes from a table. I also have another view (Cde_Master) that has information relative to the codes ie. YR;LocNme;Cde_26;F_Units;NF_Units. What I'm trying to do is create a...more >>

Hi, I have a data file with about 6.5 million records with a single field that is about 450 bytes wide. The table DDL I'm loading into looks like this: CREATE TABLE comments ( id int IDENTITY (1, 1) NOT NULL , comment varchar(512) NULL ) id is a primary key (unique clustered index) ...more >>

Hello, I have a stored procedure being executed from .NET 2003. For some reason this sp was executed twice, the second time 3 minutes after the first time. I checked all my code and there is only one place where I call this stored procedure. I checked every sp I have and it's not being called fro...more >>

I know there is a way to refer to a column in a select clause by its numeric zero-based position in the underlying table / view, but this is obviously wrong: SELECT 0 FROM Table In other words, I want to say SELECT [the zeroeth column] FROM Table -- Peace & happy computing, Mi...more >>

Hi, I have MSDE 2000 installed on a W2k Pro machine. I login to a W2k domain as a member of Domain Admins. I am admin on both the local machine and the W2k domain. I can use sp_grantlogin to grant logins to local groups, but not domain groups. From VBScript I run the T-SQL statement: Exec s...more >>

How do I display an amount as money (with commas and dollar signs) as well as only showing the whole number? For example, I can take an amount which is defined as money and display it with commas like so: SalaryMaxDesc=CONVERT(varchar, CAST(SalaryMax AS money), 1) From this I get 52,...more >>

Hello... I work with a financing, well established company located in Atlanta,GA. We have a big (several hundred programs) multiusers prepriatory busines system running on DBASE III+ which has been developed over the last 20 years. At the moment we are looking for the team of programmers w...more >>

I can use @@, for example @@variable as parametre of a store procedure ?? create sp_insert @@name varchar(19) @@age integer as SELECT * FROM EMPLOOYE WHERE NAME = @@name AND AGE = @@age Or I just must use one @ ??...more >>

CREATE TABLE SampleSourceArchive ( SampleSourceArchiveKey INT NOT NUL IDENTITY (1, 1), --PK ProductID NVARCHAR(255), ProductKey INT NULL, CompanyLocationKey INT NULL, SoftwareProductID NVARCHAR(255) NULL -- other columns ) CREATE TABLE SoftwareProductID ( Softw...more >>

Hello, I seem to have problems with DELETE. I have two tables (same fields) : tblA and tblB, and I want to : - delete from tblA all entries that exist in tblB I used: DELETE FROM tblA WHERE EXISTS ( select * from tblB ) which deleted ALL the entries of tblA! I thoug...more >>

PROBLEM: I am attempting to modify a database structure that has significant spaghetti code built on top of it. It is necessary to break a users table containing columns for shipping address into two related tables with a single user and multiple shipping addresses. So (pseudocode -...more >>

Hi everyone, I'm not sure if this is the best place to be asking this...if not, please tell me what newsgroup would be! What I'd like to know is: is there an equivalent of DBCC CLEANTABLE that will "clean" all the tables in the database? I was unaware of the issue when I initially conv...more >>

Hi, I have, what I believe, a typical problem in datawarehousing scenario relating to managing slowly changing dimensions. It boils down to determining if a row differs between one version of the table to another. Let's say I have a denormalized table called Customers1, which was created ...more >>

Dear all, After to launch this query: sp_indexes 'NSMP-UK67','UAT','tblifpwhsvmf' I obtain the following error: Server 'NSMP-UK67' is not configured for DATA ACCESS. Any help will be welcomed, ...more >>

Hi All, I have a table that has a series of records with varying production dates and quantities. For example: Part # Prod. Date. Qty PARTA 11/30/2005 30 PARTA 12/15/2005 100 PARTA 12/20/2005 50 PARTA 12/26/2005 20 I would like to...more >>

Hi everybody, I'm trying to create an XML structure using XML EXPLICIT, like this one: <tag a> <tag b>value</tag b> <tag c>value</tag c> <tag d> <tag d1>value</tagd1> <tag d2>value</tagd2> </tag d> <tag e>value</tag e> <tag f>value</tag f> </tag a> The p...more >>

I dare say that there's an easy (read: aggregation function ?) way to do this, but I'm trying to do the 'flatten the data in one table to link against another' trick. I.E; From: Table A ID Desc 7 'FOO00005001' 9 'FOO00005007' Table B ID FK_A Desc 3 7 'ASDA0679084' 4 7 'ASDA8887161' 5...more >>

Dear all, I would like to know if the following query retrieves the total amount of pages no clustered in a current database: select SUM(used) as Usados, SUM(reserved) as Reservados from sysindexes, sysobjects where sysindexes.id=sysobjects.id and sysindexes.indid > 1 and sys...more >>

How can I import Lotus data into an SQL table? Thanks ...more >>

Hi I have a function that calculates the number of non working days between two dates. This is working great. However, it now needs extending to take account of public holidays etc. The simplest way to do this is to have a calendar table with a row for each non working day. As the databa...more >>

I have heard "CAST" is ANSI-92 compliant, whereas "CONVERT" is not. I have also heard, "One of the advantages of using ANSI-92 compliant statements when possible is advantageous, since it makes your code more portable between different RDBMS's." Is there a simple list anywhere, of which Mi...more >>

I want to write a script that updates rows on one database and also generates a script that can be run on similar databases to update rows on those. The first database generates unique numbers and these need to be updated on the similar rows in the other databases. The databases are locat...more >>

I have a table to record scores and a column that stores an outcome based on the score (credit/pass/fail etc). When I update a row in the scores table I want the outcome to be calculated automatically. To do this it needs to do some queries on other tables , including checking a grade boundari...more >>

Hey I have problem with getting NULL values from table. I want to have true/false values on the outpout but if it's not possible to do I just want to have NULLs. So the problem is: I have table with a few columns and one of them is ntext ( it can be pretty big or null). So I need to know if ...more >>

Hi All, I have jobs that are scheduled to run nightly. every now and then I notice a job is not executing. I am saying it is not executing beacuse the last run status is a few days ago but the next run date is for the correct time to execute tonight. has anyone ever seen this before? ...more >>

Hi, I've created a sql package and added a job that calls a web service on another machine within our Network. I'm using an ActiveX script to make this call. I have Parsed the code and executed, but it doesn't seem to run. Have not received any errors while executing. Any suggestions? H...more >>

Hello, I have a question about a database design... I have 3 tables, Employee - fields: EmpID (PK, int, identity), EmpName, EmpGroupID (int) Group - fields: GroupID (PK, int, identity), GroupName Job (see below for details) Each employee is a member of a Group. Groups are like Sales, Prod...more >>

Hi folks, I have renamed the drive where I used to have the Transaction Log of a database. I have renemed the drive, now the database has come to Suspect mode. How do I bring the database up. Thanks in advance. ----- *** Sent via Developersdex http://www.developersdex.com ***...more >>

Hi I have written a UDF to return a table into a view. I declare a temporary table (@Tbl) to use to return my UDF's result set to my view. Inside the UDF I build insert statements with a series of loops. The insert statements are like : "insert into @Tbl ...." and are bulit as nvarchars....more >>

I was wondering if anyone would be able to help me with a query to pull out all the urns from a table which end with 2__. Basically anything where the third last digit is 2 and then any other two digits after it. The column is a big int data type. This is probably a simple query but didn't ...more >>

Dear All, I'm trying to right a query for MS Access, that returns the number of rows in each table in the database. This query works fine for SQL server. Is there an MS Access equivalent? - It needs to be a query, NOT VBA code, so I can't figure out how to "get at" tabledefs, or whatever Acce...more >>

Hi All, When I try to Pass a Large Query to SQL Server from my font end Application - Using ADO - I get error message: Error: Internal Query processor Error: The Query processor ran out of stack space during query optimization. What may be the problem for the above error message and How ...more >>

Can anybody help me to find the toplevel ParentAcctCode for the AcctCode '104', with out using the sqlcursors. ( the result will be '101' for the below example) CREATE TABLE ##AccountMaster(AcctCode Varchar(3) Default(''), ParentAcctCode VarChar(3) Default('')) GO INSERT INTO ##AccountMaste...more >>

Table structure is following like that, caStart is Starting of Duty, caEnd is Ending of Duty DutyStatus column's mean when S is Start, C is continue, F is Finish and N is Neutral (single line Duty) In this situation This is my test records explanations; Line 1 is Starts First Duty ...more >>

Hi, I'm hoping someone can help. I need to calculate the difference in days between the current date and a date held in string format in a database. Unfortunately the db is part of an off the shelf product so cannot be altered to hold datetime values instead of strings. I've tried numerous appr...more >>

I need to concatenate the two fields: Address and State SELECT Name, '(' + Address + ' ' + State + ')' AS FullAddress FROM TableEmployee There may be null values in these 2 fields, if both of them are null, I am going to display the NAME only. If one of them is NULL, I will display '('...more >>

I've got a significant programming problem to design and I'm not even sure that I've thought of all of the possibilities from which to select a solution. My company currently has three different systems to schedule employees in three different divisions of the company. Each has their own empl...more >>

Hi people, I got some application errors in event viewer, 18052 : Error: 3624, Severity: 20, State: 1. 17066 : SQL Server Assertion: File: = <R:\sql\ntdbms\storeng\drs\include\record.inl>, line=3D1447=20 Failed Assertion =3D 'm_SizeRec > 0 && m_SizeRec <=3D MAXDATAROW'...more >>

Hi Is there a way to remove the IDENTITY property from a column without dropping the column and re-creating it? Possibly some syntax using ALTER TABLE? Any help will be appreciated Thanks Craig ...more >>

I have to dates: @dateFrom and @dateTo Now, I must create function which returns number of days which are between this 2 dates and weekday is 1,2,3 or 5. What is the best way? example: set DATEFIRST 1 (week starts with monday) set @dateFrom='20050601' set @dateTo='20050615' numb...more >>

-- Thanks ! ...more >>

Hi everybody, I've some questions on creating view while working with it. 1. What's the "group by extension" in the view for? What's the option "with rollup" & "with cube"? 2. I read that "For a standard view, the overhead of dynamically building the result set for each query that reference...more >>

How can I set the value of a variable from a call to exec (code) ? I *have* to use the following syntax to get a value from a linked server and use a variable in the linked server sql string: DECLARE @datemodified DATETIME declare @query varchar(8000) declare @timetablerpath varchar(1000) s...more >>