Hi Folks, i need to copy data from a Access-Database to a SQL-Server- Database. Using a C#-Programm (reading data with OleDBDataReader and writing them with OleDbDataAdapter) takes more than 3 hours. I need a quicker solution. If i copy the data into Access by using select * into ne...more >>

Hi Hope someone can point me in the right direction I have a query that produces 2 sums QTY and Donation_amount I am trying to work out the average donation therefore I would like to sum (donation_amount/QTY) How Can I do this in the query when I try adding this in the error message is inval...more >>

I have a table that I want to have a precalulcated length on a character field and group/sum up. Thought I could do this by creating a view with a group by clause that includes the sum function. Unfortunately, the compiler complains with: A clustered index cannot be created on the view 'M...more >>

I'm publishing a book on SQL server tuning and thinking about using credit database as my sample database. Do you know where it is legal or not? Is the credit database still have copyright? ...more >>

How can I find duplicate varchar values in one table via QA? Thanks, Brett ...more >>

Hi , I am running the following script using osql. I get the following syntax error : Msg 156, Level 15, State 1, Line 22 Incorrect syntax near the keyword 'IF'. The script loops through sysdatabases and when it finds a table called executionlog, it loops throught the table and exits. ...more >>

Hi All, I have a table with id, start_date, end_date columns. There are many overlapped records. I just need to get timeframes which are not covered by any records regardsless of id's. How can I consolidate sets of date intervals and still gather the gaps in the date sequence using a set-...more >>

Hi, We are having an issue with the way the delete trigger is working. = Below is an example of what we have created: CREATE TRIGGER [SaveDeletedEvent] ON [dbo].[events]=20 INSTEAD OF DELETE -- Must use INSTEAD OF as the events table has many = Text columns. AS insert into deleve...more >>

Hi, I hope you can help me with this problem. The problem is very simple, but I tried to post all the DDL and some examples to make it even easier. I have a table where I store information about Persons. This table has many fields, some of them are these: HairColor, MaritalStatus,Complexio...more >>

hi, Does anyone know where/which table can I find the content of stored procedures and jobs? I want to look for certain linked servers name that is used in any stored procedures, and I want to look for which job is running that certain stored procedures. many thanks!! JJ...more >>

running across the following error what are the most likely things to look at to fix the problem Error = [Microsoft][ODBC SQL Server Driver]Incorrect host-column number found in BCP format-file ...more >>

I am trying to insert blob data in Sql server 8.0 database using odbc driver 3.5. I am getting following error message Actions.c(438): lrdb_stmt: "SQLExecDirect", return-code=-1, native-error-code=103, SQLState=42000, SQLError=[Microsoft][ODBC SQL Server Driver][SQL Server]The identifier that...more >>

I know the function of GAM and SGAM, but fail to understand when it is actually used. Following is from BOL: "SQL Server uses the IAM pages to find the extents allocated to the object. For each extent, SQL Server searches the PFS pages to see if there is a page with enough free space to hold th...more >>

I have two different columns (ValueDate and ValueTime) in the database with column type & format as Decimal & 9(18,0). It stores values as 131336729 (ValueDate) and 252126464 (ValueTime). Now I have tough time migrating data from old database to new database. I have only one column in my o...more >>

Well this one is stumping me to no end, and it is probably something really simple, or just impossible (I sure hope not, though!) The following example illustrates my problem of executing a string that includes a call to bcp and cmdshell in order to export a query result to a file on the sql s...more >>

Hi, I have a system that handles inventory transactions. It is based on two tables (see below for DTS); one which hold each transaction header, and one which holds all the rows. The system already has some 15,000,000 rows in it. The transaction header holds the information on whether th...more >>

Hi, I am encrypting my passwords to be stored in sql server. When the password field is blank the encryption function generates 2122914021714301784233128915223624866126. In sql server that is converted to 0xD41D8CD98F00B204E9800998ECF8427E00000000. I tried to create my procedure to check t...more >>

I have: public class Triggers { [SqlTrigger (Name=3D"TrrPerson.ContactType", = Target=3D"Person.ContactType", Event=3D"FOR INSERT")] public static void MyTrigger() { SqlTriggerContext oTriggerContext =3D = SqlContext.GetTriggerContext(); ...more >>

as Title Thanks ...more >>

I have written some code that calls into SQLDMO to execute a job. When the job executes through my code an exception the following exception is thrown: "[Microsoft][ODBC SQL Server Driver][SQL Server]EXECUTE permission denied on object 'sp_sqlagent_notify', database 'msdb', owner 'dbo'." } ...more >>

Hello, I was trying to delete records...the second table has a constraint that prevents me from doing so. Is there anyway to bypass the constraint like no check constraints? delete table1 from table2 inner join table1 on table1.t1id = table2.t2id where table1.inserttime > '2004-10-01' ...more >>

hi guys: Question like title,how to resolve this ,thank you very much. jiangyh...more >>

Hi! I'm trying to remove or replace any non alphabetical character from fields in existing tables. I have written the function below to replace those characters from a single nvarchar string. Though it slows down the execution time significantly. Does anybody know any easier and faster way to do...more >>

I have two identical tables, each with identical columns. How can I create a VIEW that shows them as one large table? COL1 COL2 James Brown Ted Grier Nancy Pink Moniqu Blue ...more >>

I used a trigger to add a record to a log file refrencing the date a record was added. It works fine if I use @@Identity but returns nothing if I use SCOPE_IDENTITY(). Any suggestions? Below is the trigger code: CREATE TRIGGER [dbo].[trg_addRecord] ON [dbo].[tblSchedule] FOR INSERT AS ...more >>

Hi freinds, I am using execute sp_msforeachdb N'use ? select db_name()' but getting error because couple of databases has an space in the name <my datdabse name> What is work around for this ? Thanks in advance, Pat ...more >>

Hi I have a 1.5 million row table with a text field containing a resume. Can anybody think of a way to remove any duplicate resumes in my table (and a way which will not take days to run!) Thanks ...more >>

Hello All, I am looking for suggestions on the design and implementation of a mechanism that notifies clients of specific changes in our database. Our current implementation, while resolves the problem, creates an explicit bottleneck, which has caused contention problems under stress con...more >>

have Stored procedure spAdmin_Do And I try to execute it from application (I can execute it from Query Analyzer). The procedure simply stop at the first EXEC 'delete Do.dbo.Admin_Do' and don't continue. If I delete that line procedure again stop at the first EXEC. Is it problem if I have m...more >>

I have smalldatetime type field in one of SQL Server 2000 tables. When I add a record into that table manually in Enterprise Manager and leave that field blank then it contains NULL in it. I also can clear (insert Null) in the field by pressing Ctrl+0. How do I write Insert or Update SQL stateme...more >>

Hi there.. I got a weird symtoms. "Reads" column in Profiler does not display valid reads count, It dislpay '0' all. Any idea will be helpful.. Thanks. ...more >>

Hello I have an ntext field in a table and when I open the table to see the rows I only see <Long Text> in the field. If I open from the SQL Query Analyzer, it's truncating the field. How do I get to see the whole string without having to export the table and open the file from a notepad? Than...more >>

Hey.. Thank you for looking on this :) Problem: One physical SQL server and one physical OLAP server.. I want to execute OLAP Cubes from my DTS package on my SQL server.. Do anyone know how to do that? And what i need? Regards /Jacob ...more >>

I'm trying to perform an IF statement on a couple of input variables within a UDF. Is this possible? I have the following syntaxx, and receive an error: CREATE FUNCTION dbo.MyNewFunction(@BegDate datetime, @EndDate datetime) RETURNS TABLE AS --This is the beginning, if tells me a syntax...more >>

Hi Freinds, I am running this : declare @rc int declare @TraceID int declare @maxfilesize bigint set @maxfilesize = 5 exec @rc = sp_trace_create @TraceID output, 0, N'c:\sql02.trc', @maxfilesize, NULL Error 12 ( File cannot be created ) is popoing up What am I doing wrong? Thank...more >>

I have transactional replication enabled where subsribers are using data in read only mode. (Subscribers are not updating data.) I wanted to add a new table in the publication without dropping publication so I ran a procedure sp_addarticle on publicaiton database. Here is the SP call. EXEC sp_...more >>

Is it possible to retrieve the result of a sp as a temp table into a new table? Like "SELECT * FROM (EXEC sp_tables) INTO <table>" Thanks ...more >>

page split The process of moving half the rows or entries in a full data or index page to two new pages to make room for a new row or index entry. Above is the definition of page splitting in BOL. What i don't understand is why does it move half the rows, for a single row inserted, instead of ...more >>

I mistakenly deleted a very important portal site from SPS with its databases.I am gonna be in a real trouble and we don't have backup .IS there a way to restore databasees(content-profile and services) and then restore portal based on them.config database is still there. ...more >>

When I open a crystal report in a .net app, it opens a connection to the sql server, but when I close the report, the connection remains open. Any ideas on how I can close this connection, either through identifying it in sql server (2000) or in vb .net itself? Thanks for any help. Bern...more >>

I'm new to this site so thanks in advance to everyone who replies= to this post. I have a fairly complex query in a Stored Proc (M$ SQL Server= 2000) that outputs results from 3 tables by using the UNION= Join. I now need to order this output in several dirrently ways= but I cannot seem t...more >>

Hello all, I have a Stored Procedure that to do a simple processing (a insert in two tables), and after call a other stored procedure that to do a update in this two tables, but before to do a medium processing. The question is, the stored procedure is very slow. This stored procedure go...more >>

Hi all, I am trying to use the IF function in a stored procedures, I want to execute different queries depenant upon one of the input parameters. When I execute the SP I get the following error messages: Server: Msg 213, Level 16, State 4, Procedure spInventCheck, Line 25 Insert Error: Colu...more >>

I want to be able to see all of my databases and their recovery models. Can someone provide the code to show the database name and the recovery model? I would greatly appreciate it. Thanks all....more >>

MSSQL 2000 I have two Stored Procedures, one calls another to do some trigger-style updating of a history/log table. I would like SP #1 to async call SP #2 asynchronously so SP #1 is not blocked and makes the caller wait on SP #2. I am not worried that SP #2 might fail, I just want SP #1 t...more >>

I am trying to total several fields from the same table. The problem I am having is if any of the following subqueries results in a null, then the whole row of the main select is omitted. Here is what I have so far. select a.lastname, a.firstname, (p.patienttime + (s.patienttime/2) + (d.patien...more >>

We are using long SQL strings (up to 24,000 characters) for some of our operations. We need to insert these long strings into a text field in a table for logging purposes. Using @string1, @string2, and @string3, all of which are up to 8000 characters, we cannot succeed in using: INSERT ...more >>

I have a program that reads xml and updates database table. To insert value I need to convert it to the appropriate datatype. Code like this ALTER proc spInsertApplfromXML (@fieldn varchar , @value varchar) as declare @datatype char(15), @datalength smallint -- get correct datatype...more >>

I have the following procedure which my predicessor wrote: CREATE Procedure sp_GetPermissions @Pos_lc tinyint OUTPUT, @Pos_do tinyint OUTPUT, @CurrentAppName VARCHAR(30) AS SELECT @Pos_lc = pos_lc, @Pos_do = pos_do FROM tbl_app_applications a, tbl_app_permissions p WHERE app...more >>

Dear fellows, I wish obtain the name of the day through a transact-sql's function. Anyone knows how and when? Thanks in advance and kind regards,...more >>

I am using a function to split the delimited separated codes, CREATE FUNCTION dbo.udfSelSplittedCodes1 ( @CdString VARCHAR(8000) , @RowDelimiter CHAR(1) = ',' , @ColDelimiter CHAR(1) = ':' ) RETURNS @tblCodes TABLE (Cd INT) AS BEGIN DECLARE @RetVal int ,@ErrMsg var...more >>

I receive numerous deadlocks while processing transactions with more than 2 users in SQLServer 2000 SP3. While these does not happen often, it does happen often enough where it has become an annoyance. The error that the users are getting are very similar or identical to the one below: Micro...more >>

Greetings, I have what is probably a simple SQL question. First let me show you an example of the data: ID Color User Created 1 Blue Jim 10/1/04 2 Red Bob 10/2/04 3 Blue Gus 10/2/04 4 Yellow Bob 10/2/04 5 Red Jim 10/3/04 I'm summarizi...more >>

how can i get the ist and last dates of the previous month...more >>

Hi Folks I have written a generic import script, which crawls directories using pattern matching on files/file types. Recently, I've experienced an odd problem with either bcp -or- a real information_schema bug? My best guess is that this is an information schema bug (possibly some type...more >>

Hi All, I have an INSTEAD OF UPDATE trigger on one of my tables that will do some calculations and then eventually update the modified record with the new calculated values. The problem is: When I update one by one record, the triggers works fine - When I run an update to update multipl...more >>

Hi, I was wondering if anyone could please help with the following query: I have a table containing a list of attributes associated with documents. (DocAttrib) Another table contains predefined sets of attributes. (SetAttrib) Another table contains a list of actions associated with each set...more >>