Just to make sure - is this the right way to do a simple insert? Create PROCEDURE dbo.spInsert( @name varchar(50)) AS begin tran set nocount on insert into tbl (name) values(@name) set nocount off declare @id int set @id = @@IDENTITY commit tran return @id ...more >>

I'm using SQL Server 8. My test database is 2.5 gig but my log file is now 30 gig. I'm using all the default settings and I guess it's been just increasing until it nearly filled up my HD. Is there an equivalent of "Truncate on Checkpoint"? I've tried checking "auto shrink" and backing up and re...more >>

I have been given two tables in Excel which I've imported into SQL Server and I need to join them. Each of them has a unique Product but there is a bit of inconsistency in that in Table A, some (but not all) of the Products have been prefixed with the Supplier Name - I don't have a master ...more >>

I am trying to get counts of jobs accum into 4 columns by date. When I try to save the view it gives me the error "Column 'dbo.RepairOrder.JobSize' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause." I don't want to group by Jo...more >>

create table tbl1( fname varchar(10), lname varchar(10), item int ) insert into tbl1 select 'joe', 'smith', 1 union select 'joe', 'smith', 2 union select 'joe', 'smith', 3 union select 'bill', 'jones', 4 union select 'bill', 'jones', 5 union select 'bill', 'jones', 6 union select 'sam'...more >>

Hi All, I have the following situation. I am trying to populate data in a table from other tables. Say table1 has default values in all fields. But the data is coming from joining table2, table3, table4 and so on joined in a single query. Some of the fields of tables' table2, table3 and t...more >>

Hi all I have a SP that beahves strange. Originally it takes about 20 milliseconds to complete, but sometimes it starts going slow and take about 5-7 seconds. When this happens, it keeps going slow until I drop the SP and re-create it. I tried to run the SQL body of the SP in the Query ana...more >>

I am trying to execute an ssis package from the web - classic asp page I am using windows authentication to login to the web site which I assume means i am executing the package under that account That account being administrator I am using the code below to execute Dim objWshShell...more >>

Hi I need to calculate the date of the next weekend (so either Saturday and/or Sunday's date) from getdate() inclusive of if getdate() is a weekend-day. I need to filter some realtime travel related data for "this weekend" via SQL. Anyone got any neat ways of doing this? Cheers Matt...more >>

Using SQLServer 2005 I create a new database with code from my Windows application. Then I launch osql to create tables from a script. This works fine. Next I try to insert a row into one of the tables and I get: Login Failed. Error: 18456, Severity: 14, State: 16. If I pause for 5-1...more >>

I've got a user, "joe", logging into a SqlServer 2005 database using sqlserver authentication. Joe can create tables, insert, select, drop -- whatever -- but when he tries to do a bulk insert, he gets: "The current user is not the database or object owner of table 'myTable'. Cannot perform...more >>

What exactly do I need to configure on my SQL Server 2005 to use more than 2GB of RAM? Currently the Server Properties is only showing ~2GB and I have 4GB installed. There seems to be a lot of conflicting information (from Google). I have: Windows 2003 Server Standard. Using 4GB of RA...more >>

With Access, I could link to tables in a foreign db, even a csv file, and query them as if they were local tables. How do I do that in SQL Server? ...more >>

I have a SQL table with a 2,000 + records. I need to pull the top 5 problems from each office. The fields in the tables are Office, Problem (TypeItem below), User. My queries right now only pulls the top 5 from the table (see below). I would like to pull the top 5 from each office. Th...more >>

I have a Payments table with a trigger that fires on Insert and Update. It has been working fine for the past few months. Now, it disconnects the user from the db whenever it fires. However, if I, as a sysadmin, modify data in the table, the trigger fires OK and does what is supposed to do. I ...more >>

In Query Analyzer connected to my local server using Windows authenticatio I tried to run this select statement against my remote server: select * from [11.22.333.444].remoteDB.dbo.tbl1 I got this error: "Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server con...more >>

I have a UDF, dbo.ContractSummary which takes one parameter ContractID and returns a table. I want to use this UDF in a SQL select statement like this: SELECT cs.* FROM Contract c JOIN dbo.ContractSummary(Contract.ContractID) cs ON c.ContractID = cs.ContractID But I got error message c...more >>

Hello all, I have two tables, I'll say Table A and Table B for the example, where Table A has all my data and Table B is blank but has the same layout as Table A except it has an additional Identity field setup to create an ID field. I want to Import all the data from Table A to Table B, bu...more >>

I have these 2 tables: CREATE TABLE tbl_ft_testsegments ( SegmentID int(10) AUTO_INCREMENT NOT NULL, TestID smallint(6) NOT NULL DEFAULT '0', CarrierFreq smallint(6) NOT NULL DEFAULT '0', BeatFreq smallint(6) NOT NULL DEFAULT '0', BeatFreqDelta ...more >>

Hello, I have a query that needs to run daily and only collect records for that day's activity. The table has a date/time field called TranDateSold. Could someone please advise the proper syntax for the following logic: SELECT * FROM tablename WHERE TranDateSold (is today only) I experiment...more >>

Hi , Here is my stored proce and I am not sure why its complaining about the below error SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[USP_InsertUserInfo] @aspnet_userid uniqueidentifier(50) ,@DealershipID int ,@LastName va...more >>

On one to one relationships data will only be inserted into the second table if that information is provided. That is it doesn't place a bunch of null values just because you added and item to the first table but do not define any values to the second table, there by the first table can have tho...more >>

I have a stored procedure that selected records from one table and inserts them into a second table based on some criteria. What I want to add is filtering by "Today -1". The problem I am having is that the date field being looked at is formated as yymmdd. Can anyone help me out in trying to fig...more >>

Hi, I am trying to execute a simple statemnt that I got right out of books online and I keep getting this error and I cant figure out what Im doing wrong: ALTER DATABASE [00057088] SET SINGLE_USER WITH ROLLBACK IMMEDIATE Msg 226, Level 16, State 6, Line 4 ALTER DATA...more >>

I have a table in SQL Select Count(*) AS CountAll from Data Select Count(*) AS CountSome from Data where Individual = Name I would like to combine this into one statement and give me both the CountAll and CountSom ...more >>

I have an update query that I'm trying to resolve, but I'm at a lost as to how to accomplish this. I have two tables(Orders and Mailings). I need to update an Orders field with a field from Mailings. There are some that will have a one-to-one relationship, and others will have many-to-many,...more >>

Hello All, I used DTS to copy one database to a blank database for a backup of the database. A duplicate database. How do I copy over the user logins to the new database. This did not happen with DTS. The databases are on the same instance of SQL. Any help appreciated. Thanks, Terry...more >>

I am preparing the write a sp that will accept about 20 optional parameters that will filter my resultset. Since these are optional I was thinking that I needed to dynamically build my query string based on if a parameter had a value. This seems inefficient so I was wondering if there is a b...more >>

I have been told that using BLOBS and Stored Procedures is a bad thing. Running the SQL in the page is the only correct way. We are using SQL Server 2000 - soon to go to 2005. Could someone direct me to documentation that addresses this situation? -- Tony...more >>

I am trying to compile this procedure and run it. It compiles fine, however when I run it I get the following error: (1 row(s) affected) (1 row(s) affected) Msg 203, Level 16, State 2, Procedure ETL_CC_TBL_CUSTOMER, Line 96 The name 'CREATE TABLE CUSTOMER_DELTA_A ( ...more >>

I need to create a cross-database view (same server) in a master database. The databases I'm joining in the view are listed in a table in that master. I'd like for the view to automatically include new databases whose names are added to that table in the master. How would I go about doing t...more >>

Hi, I'm developping a asp application that somestimes needs some heavy synchronisation done. I wrote a stored procedure for that purpose witch takes approximatly 15 minutes. Right now I run this directly on the server (using MS SQL Server management studio). But I would love to be able to cal...more >>

I explain: I have created a stored procedure that indicate the status of an ETL... I call it like this: 0- BEGIN TRANSACTION 1- --Updating LOG Status 2- exec update_etl_status 'Main Table','Transforming' 3- update Stg.[Main table] ... blablabla 4- 5- -- Updating LOG Status 6- exec updat...more >>

I'm a newbie, and I need to convert a time interval of seven position numeric field from a flat text file in format MMMMSST and store in a database with the tenths of second in first position after the decimal. The current stored proc uses the following float method and I was thinking it would ...more >>

I've recently been tasked with duplicating the permissions from one account to another. We have a development, system test, and production SQL Server, and approximately 35 databases in each. We use a fine level of control on this particular account because it's what the applications use to log...more >>

Hi my first post!!! i have an organisation table and i want to do something like this select count(orgname) as Count1 where orgname like 'A%' select count(orgname) as Count2 where orgname like 'B%' but i want it to list every organisation, so one on each row, then the 2nd column show t...more >>

I have four lookup tables that have identical structure. I have to write a query to check if a particlaur string (code) exists in any of the four lookups. What is the best way of dealing with this please? 1. Write one query with four corelated subqueries (one for each lookup). 2. Write 4 ...more >>

Hi I need to set security for row level but not based on Database user's login. It should be based on the user table login. For the particular user I need to allow only the particular records to access insert, update delete and select. Let me explain clearly For example think we are usi...more >>

According to msdn the views 1.sys.dm_broker _forwarded_messages returns a row for each Servive Broker message that an instance of SQL server is in the process of forwarding, and 2. sys.dm_broker_connections returns one row for each Service Broker network connection. But when I do "Select" ...more >>

When I run the following code on SQL Server2005 I get an exception every time. SqlInt32 maxct = (SqlInt32)sqlCommand.ExecuteScalar(); // Throws Specified cast is not valid. Why doesn't this work, or what do I not understand here? Since I could not make previous code work, I am forced to...more >>

Hi guys, need your help! (sorry this is quite long) I've got a table of Projects which I'm using with an asp:Repeater to display a list of the projects. Here's the sql... SELECT ProjectID, ProjectName, ProjectClient, DartsContact, LeadArtist, Projects.AreaOfWork, AreaOfDoncaster, StartDate...more >>

Thanks in Advance, I would like to parse a column of ntext that looks something like this: 'I was born on Sept. 14, 1960. I graduated college on August 2, 1982, and went to France on Jul 17, 1985. I died on December 30, 2001.' I would like to break it into units that each end with the da...more >>

Hi, How can I run EXEC conditionally? The EXEC syntax woks on its own but not when it's in a query. TIA Mike select 'name','surname', case when datediff (dd, getdate(), getdate()+2) between 0 and 2 then EXEC master..xp_sendmail 'mike', 'The master database is full.' else '' end as test ...more >>

Howdy I've got the following example of a SP of mine (except in real life the @XML is ntext datatype) ----------------------------------------------------------- DECLARE @XML VARCHAR(4000) SET @XML = ' <?xml version="1.0"?> <roadrunner updatetime="2006-04-20T1752"> <rr_event> <webid>9...more >>

Hi, I have a table with the DATETIME columns min_date_time and max_date_time, these columns store a min and max time for some date. Now I want to extract all the rows that limit curr_time (independently of the date). Something like this: ---------------------------------- SELECT * FROM M...more >>