Hi, I'm using SQL 2000. I have 2 tables, Invoice and Products. In 'Invoice' table, there are 2 cols: Invoice/invoicedate In Products table, there are 2 cols: Invoice/Productid At the front end 'Access", these two tables are linked using 'invoice' field. But on SQL backend side, I didn't...more >>

Hi! In Studio 2005, I get nothing for my output parameter except sometimes the error: problem converting char to int (where you have @outputpar = @declarevar, etc.) The parameters are varchar(50). I tried char, nvarchar, etc. where should I look? Thank you! -Bahman ...more >>

Is it possible in SQL Server 2005 Management Studio - to script multiple database objects; each to its own file (and NOT all into one single file)? This was obvious to accomplish in EM, but I didin't see how to do it in SSMS. What did I overlook? Thanks! ...more >>

Hi, I want to use Windows authentication to connect to SQL Server 2005 from Access 2003 projects. I have two users in the company who need to access our SQL Server 2005 from an Access 2003 front end. I'm one of them but because I'm an admin I don't have any problem accessing the server. ...more >>

i have sql2k5 enterprise version installed on the database server. my question is what version of SSMS (client tool) I should install on developer's worksation? our developers mostly deal with .net applications and also report design, so they need to be able to deploy reports to reporting server...more >>

In SQL 2000 Enterprise Manager, when I type this query: SELECT * FROM Users u, Companies c where u.CompanyID = c.CompanyID it always converts to: SELECT * FROM USERS u INNER JOIN COMPANIES c ON u.CompanyID = c.CompanyID Why does i...more >>

Hi All, I have 2 select statement that return 2 sets of data. The tables have one field with like results. What I am trying to do is list the like field on the left and the 2 sets of results beside each other on the same data set ie. select Results 1 Name | Result1 select Result 2...more >>

In SQL Server 2005, is there a way to retrieve only the time portion of a date/time field and ignore the date part? I have a DATETIME field and I do not care what the specific date is, I only want query results if time > 00:00:00 and <= 00:30:00 for any Sunday. Like this: SELECT 1, CO...more >>

Hi All How Can I Run Application Whit Parameter from Sp Example Run Notepad whit file name myfile.txt Thanks ...more >>

Hi All How Can I Run Application Whit Parameter from Sp Example Run Notepad whit file name myfile.txt Thanks ...more >>

How do you guys prefer to write queries, by hand or by using the query builder? I'm used to writing it by hand, but a co-worker loves the query builder(he even hides the sql part). Should I just switch to query builder? Personally, I don't like it (it looks too much like the MS Access utility)...more >>

Hi, Been looking at this a while and can't quite work it out. I want to add the values of columns over two tables. Ideally I'd like to be able to specify multiple pairs of columns, but initially adding a single pair would be a start. The following query works as long as there is data in bot...more >>

fld0 fld1 fld2 fld3 BRKT STRAP 36 3 54 BRKT STRAP 7 4 3 BRKT STRAP 14 27 5 BRKT STRAP 56 29 7 BRKT STRAP 20 31 6 BRKT STRAP 3 2 7 BRKT STRAP ...more >>

I can query who the current user is (sp_who or select current_user) -- is there a a way to determine their default schema? Thank you! ...more >>

Hi All, I keep getting the below messsage, when I sent a test email message from a Database Mail. I have a profile setup and the user is also added to MSDB DatabaseMailUserRole. I did install MS Outlook on the same machine and sent email to the same recipient and it worked.But will not work wh...more >>

The problem with the code below is that it ADDS the value of @year to the current @thedate value. I need to SET @thedate.year to the @year value. Any suggestions? Thanks, Randy declare @month int set @month = 3 declare @year int set @year = 1999 declare @thedate datetime set @the...more >>

I want to script out a table's DDL (create table statement). I can do this through the GUI, but I don't know how to accomplish this using TSQL. Can anyone point me in the right direction? Many Thanks! - Johnny...more >>

Hello, The subject really says it all. Is there a way to perform the same job as Replace when the column is a Text data type? Thanks, Anthony ...more >>

hi, i created another instance of MS SQL Server on server BESQL01. this new instance is called BESQL01A. i'm trying to connect to it from a web page and it's giving me an error saying the server doesn't exist. any ideas what the missing link is. here is the connection string: oDBCOnn.open "d...more >>

I have a hierarchical structure to contain objects which maps users (belong to roles) to an ACL table for permissioning on these objects. I then need to create hierarchical XML based on the data hierarchy with the ACL access included. My lookup on the data is peachy, but I'm noticing that a...more >>

Good afternoon, all! I have a stored procedure that has an input parameter, @chvComments defined as text. The comments are getting in with the proper length, as shown by printing out len(@chvComments) in the body of the stored procedure. Prior to being inserted into a table, the comments ...more >>

given this dataset: CustomerNum VisitNum DateofVisit 1 1 12-Jan-2005 1 2 23-Jan-2005 1 3 28-Jan-2005 2 1 01-Feb-2005 2 2 08-Feb-2005 3 1 03-Feb-2005 3 2 08-Feb-2005 how do i calculate the # of days elapsed between each visit, to produce a resultset like...more >>

I am trying to run a script through ADO that changes the schema of a table. if not EXISTS ( SELECT * FROM dbo.syscolumns WHERE (name = N'owner') and id = (SELECT id from dbo.sysobjects where name = 'models')) begin -- several steps he...more >>

I am somewhat of a T-SQL beginner, so excuse the simple question. I need to perform the following queries, but I am sure they could be combined into a more efficient single query. How would I combine these into the most optimal query? This segment is from my stored procedure. DECLARE @cla...more >>

Hi Guys, Is there any stored procedure or sql statement to add notification for a job? The senerio is i have to create a job by using sps and i cant find any procedure for enabling/disabling "write to application event log" or autodelete job option which we have in notifications tab. ...more >>

Thank you for taking a look at my issue. Given schema and data below, I am trying to build a function that will = do the following: Given an OrderItem that is oriIsMfg =3D 0, non manufactured item, and = considering recursive relationship of this Bill of Materials, return the = firs...more >>

A workaround to fix an application issue requires to insert a space in an Address field. Using UPDATE tablename SET Address = ' ' in Query Aanalyzer seems do not fix the issue. This Address field is varchar and allows nulls. I'm not sure if I hit the space bar in the Default Value line under the ...more >>

Current sturucture: We have a small web server farm that includes two machines. One machine contains the folder where all uploaded document are stored. The other machine links to the documents vai a mapped drive. Meta data about the files is stored in a group of tables in a SqlServer 2005 data...more >>

I need to create a table with two values - old no and new no the old no should come from an existing table and the new number should begin with a starting value and increment by 1 how do i do this???? From http://www.developmentnow.com/g/113_0_0_0_0_0/sql-server-programming.htm Posted via Deve...more >>

Hi, I want to update a column based on substring of another column (of the same row and same table). How can I do that? Thanx....more >>

I have .NET web application that connects to SQL 2005 using the following connection string: server=(local);database=db1;uid=sa;pwd=pasword;min pool size=0;max pool size=100;connect timeout=10; I assumed that if i use (local) for the server name, and the .net application is on the same m...more >>

Hi everyone: I have two tables: pat01 record patient status1 from date to date. patientID startdate enddate status1 1 2000-01-01 2000-01-25 Good 1 2000-01-26 2000-02-25 not so Good 1 2...more >>

In the database design - how can you force the input and output in a particular field to always be in UPPER CASE? re: Field Name: Net Qualifier nvarchar 255 sample output "oz" required "OZ"...more >>

I need to calculate the Amount in the Main table using the other three tables. Main: Account Desc Amount 544-411000-000000-100-999 Services ??? 544-413000-000000-101-999 Expenses ??? 544-414000-000000-104-999 Bonus ??? 560-411000-000000-050-999 Services ??? 560-413000-000000-060-999 Ex...more >>

We have a one table that has a record with the summary and all the records of that summary. For example, on this same table there may be a record with the summary of the product (i.e. "PC") and five other records with the components of that "computer" ("ROM", "RAM", "CPU", etc...). They all h...more >>

A customer just asked me to remove all spaces from the FirstName and LastName columns because they want for searches for [for example] "De Leon" to return anyone with the name spelled "De Leon" or "Deleon". I'd rather not remove spaces. And yes, I'm aware of the wildcard characters (% etc)...more >>

Hi I am doing log shipping on sql 2000 and want to kick all users out of the database before applying the backup or the log. What I am doing is writing a cursor which will read sysprocesses to see which users are in the db. I filter on db_id =db_id('pubs') Then I kill all the spids. I...more >>

Hi all, I have a SQL statement that is longer then 8000 characters. So I have split it up... store it in two variables.... after Execute (@var1+@var2).... i get a select statement with parameters in it and is run by SSIS dynamic SQL task. ...which should store it in a string variable.... this p...more >>

Is there any setting in SQLMCMD and hopefully in ISQL/OSQL as well, that when I type a query and hit enter it will run/submit the query without having to hit enter, then type go and hit enter again? I was hoping a ';' would work, but I can't seem to figure out how to get it to work. Please h...more >>

Hi, Is there a equivalent to "FOR UPDATE" in sql server Select field1, field2 fom table1 for update Will lock the record being accessed by other transactions and release the lock when the transaction using it commits Thanks JP ...more >>

Hi, This may be a little OT, but I don't know exactly where else to post it. I am writing a little parser that generates valid SQL using "English like" text. I want to use this to allow non-technical users to be able to quickly write their own queries, to search through a database that stores...more >>

Hi, I have the following situation: a partitioned view as the target of a "insert into", with the source being an "select from" statement. the problem: the partitioned view requires a primary key, the source table however does not have either a primary nor a unique key and does have seve...more >>

i have a fact table that a stored procedure use but have difficulting finding what populates the fact table... when i go to All Tasks > show dependencies on the fact table i get nothing? what's a quick way for me to find how this table is populated? ...more >>

I am selecting a column from table B that is a varchar and want to insert directly into a smallint column in table A. The value will always be an integer in display format e.g. 1, 2, 3 etc. So the varchar will simply have one number left justified. Thus the basic format is: Insert into A S...more >>

Hello! I need to use dynamic sql in UDF. There are a lot of function in the database like: ALTER FUNCTION ...... (@Date1 int, @Date2 int) RETURNS @Result TABLE (a int, b int) AS BEGIN INSERT INTO @Result SELECT columnX, columnY FROM TableZ RETURN END There are more details ...more >>

HI. As a newbie I am trying to write a TSQL statement that can retrieve multiple rows. The trick for me is that I have a version field, smallint, that can indicate if a row exists in various versions. What I am trying to do is to retrieve only the rows with the highest version number, I ...more >>

Hi, I have a problema of query in an asp page, if I run the query in sql query analizer is time running it's about 3-4 seconds, if I made the same query in an asp page it create the error of timeout for the browser. The result of the query output 2000 of records, all in one page. The query i...more >>

Hi, I want to pass a multidimensional array from VB6 to a stored procedure. This stored procedure will the add multiple records to a table. Today: --------------- ALTER PROCEDURE [dbo].[AddGroupUser] ( @GroupID int, @UserName varchar(50)) AS BEGIN SET NOCOUNT ON; INSER...more >>

I set up a new backup for the database and I can't change the settings of this back up I simply can't find it in the Enterprise Manager Thank you, Samuel ...more >>

Hi y'all Does anyone out there have a solution to the problem of bit manipulation when you need to operate on something bigger than a BIGINT? I have a requirement to manipulate up to 128 bits... Anyone tried bit-shifting and masking with a VARBINARY? (Joke) I'm sure someone has cracked...more >>

Hi All, On my production server, I see a peculiar behavior starting today morning. The issue is : I run select * from a table which has 20165, but it keeps on returning 9000, 32000, 3000, 10000 etc.(never got the exact records) Not sure what is wrong. What would be the reason for this and ho...more >>

Installing a database Hi, this I probably no problem for the right person. I am about to install a database on my SQL Server. Due to the summer holidays, I can't get hold of the support people for the database, so I was wondering if anyone out there could help me. I have a file w...more >>