all groups > sql server programming > december 2004 > threads for thursday december 9
Filter by Day: 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31
Dynamically create column name within stored procedure
Posted by j.jung NO[at]SPAM gmbsupport.com at 12/9/2004 9:12:24 PM
Hi all,
I have a set of tables for different users of my application.
Eg. user name = jj
table for customers = jj_customers
user name = kk
table for customers = kk_customers
How can I pass the prefix of the table name to a stored procedure?
Eg stored procedure = select * <pref... more >>
Random number
Posted by Peter Afonin at 12/9/2004 7:55:02 PM
Hello,
Is there a formula that generates the random number that I could put in the
table column as a default and use it as a primary key and an alternative of
identity column? The uniqueidentifier is too long for my needs, I'd like to
have a five-digit number. I could create an identity column... more >>
Using Bitmasks and FlagFields in SQL
Posted by Wells Caughey at 12/9/2004 7:49:17 PM
I have a situation where it is desirable to use flag fields in my table
definition, but because I want to aggregate across several different flag
fields I am storing them as a binary(1) instead of bit column for each.
Generally when I use something symbolic, like an arbitray ID field, in a
t... more >>
IsStr equivlant function
Posted by S Shulman at 12/9/2004 6:43:13 PM
Hi
I would like to search a database field with a part of a string, is there
any function that returns the true/false if the string in the field contains
that string passed
Thank you,
Shmuel
... more >>
drop table if exists
Posted by shank at 12/9/2004 6:39:23 PM
DROP TABLE IF EXISTS #TempNR;
or
DROP TABLE IF EXISTS #TempNR
Gets me an error "Incorrect syntax near the word 'IF"
What am I doing wrong?
thanks
... more >>
Backup Location
Posted by Leila at 12/9/2004 6:30:28 PM
Hi,
Our web site's SQL Server is hosted by another company in internet. I want
to backup our database but the backupsets will be created on the host. Is it
possible to create a device the way that backupsets be created on my local
SQL Server?
Any help would be greatly appreciated.
Leila
... more >>
Design Issue
Posted by nvishnu at 12/9/2004 5:24:58 PM
I have a design question
Customer A has a contact table coulmns like below
tblContact (for CustomerA)
ContactID
CustomerID (fk from tblCustomer
ContactFirstName
ContactLastName
while customer B may need an extra contact info like Age. Another customer
may have DOB.
tblContact (for Cust... more >>
ORDER BY question
Posted by Mike Harrington at 12/9/2004 5:24:37 PM
Hi everyone,
I have a table, and I'm trying to run a simple query against it with an
ORDER BY clause.... I have:
SELECT id, platform, pkgname, pkgnotes, det_size
FROM prod_packages
WHERE id_rel = @Release
ORDER BY platform, pkgname
the field "platform" is an int, and "pkgname" is define... more >>
Don't see what you're looking for? Search DevelopmentNow.com.
Query all date fields
Posted by Ken Briscoe at 12/9/2004 5:24:23 PM
Hi,
We're having a problem with our accounting system. (read: problem with
end-users using our accounting system) Months ago, someone entered a payment
from a customer with a year of 2023. A bunch of accounting stuff ensued -
Credit Memos, Invoices, Debit Memos...you name it, they tried it. So,... more >>
Looping through dependent sets in a stored proc
Posted by not_a_real_email NO[at]SPAM no_spam.com at 12/9/2004 5:10:30 PM
Hi, I'm new here so if this is not the right place for this question, please
let me know.
I have a set of dependent items ("jobs") that must be processed to determine
the start and finish date for each job. The starting date of a dependent job
is deterimed by the finish date of the job it depe... more >>
Newsgroup name
Posted by nvishnu at 12/9/2004 4:39:25 PM
Does anyone know the newsgroup for SQL Server table design issues?
... more >>
best efficiency for full-text search
Posted by Random at 12/9/2004 4:23:01 PM
I have created and populated a catalog on my table to enable full-text
searching, no problems. Now I want to implement a stored procedure to run
the search based on a delimited string of keywords. I am wondering if there
is a de facto accepted method for parsing the keywords, and running the... more >>
Question on temp table
Posted by SqlJunkies User at 12/9/2004 4:13:06 PM
Does any one know why the first statement works and the second statement does not work?
It gives me an error message
Server: Msg 197, Level 15, State 1, Line 13
EXECUTE cannot be used as a source when inserting into a table variable.
create table #tmp (
col1 int,
col2 varchar(100)
)
... more >>
Question on temp table
Posted by SqlJunkies User at 12/9/2004 4:11:18 PM
Does any one know why the first statement works and the second statement does not work?
It gives me an error message
Server: Msg 197, Level 15, State 1, Line 13
EXECUTE cannot be used as a source when inserting into a table variable.
create table #tmp (
col1 int,
col2 varchar(100)
)... more >>
Why running the same query after the first time is faster ?
Posted by Paul fpvt2 at 12/9/2004 3:25:06 PM
I have SQL Server 2000 database that has about 8.5 million records. The
database has 1 table, and the table has 4 columns.
CREATE TABLE [dbo].[Packet] (
[PACKET_ID] [int] IDENTITY (1, 1) NOT NULL ,
[PACKET_TIME] [datetime] NOT NULL ,
[PACKET_CONTRACT] [varchar] (8) NOT NULL ,
[PACKET_DATA] [t... more >>
Triggerproblem
Posted by Georg Kreutz at 12/9/2004 3:24:17 PM
Guys,
I have developed a trigger, which inserts in an other table. It works fine
without any problems. Now I would like to preselect
the rows from the inserted table first. I have one field which has the value
0 or 1. I would like to have only the records with
the value 1 in the field. But ... more >>
Remote calling a UDF
Posted by Justin Drennan at 12/9/2004 3:09:57 PM
Is there a way to call a UDF accross SQL servers?
When I call:
select * from server1.database1.dbo.my_udf(1)
it results in:
Server: Msg 170, Level 15, State 31, Line 1
Line 2: Incorrect syntax near '('.
However if I do it on the local server the UDF works perfectly.
The reason I need ... more >>
order
Posted by ls_y041 at 12/9/2004 3:04:08 PM
a+1+1
a+10+1
a+2+1
a+1+9
a+12+1
.......
... more >>
XP_SMTP_SendMail limitations
Posted by Christian Perthen at 12/9/2004 2:57:12 PM
Hi,
Are there any known work-arounds the varchar(8000) limit on sent messages in
XP_SMTP_SendMail?
My current dynamic generated html messages are getting cut and I don't want
to send them as attachments!
Thanks in advance.
Christian
... more >>
query question
Posted by Mike Kanski at 12/9/2004 2:50:13 PM
I have the following table:
CREATE TABLE [Test] (
[ID1] [varchar] (50) ,
[Decile] [int],
[CustID] [varchar] (50))
I have the following data in it:
ID1 Decile CustID
1 1 2
2 1 3
3 2 4
2 2 3
... more >>
Help with Check Constraint
Posted by james at 12/9/2004 2:47:21 PM
I have a TimeCard table like so
create table TimeCard
(
pk int not null identity(1,1),
employee in not null references Employee(pk),
punchIn datetime not null,
punchOut datetime null
)
what I do not know how to do is build a constraint that dissallows a punchIn
or punch... more >>
Need help with query to group
Posted by mitra at 12/9/2004 2:35:01 PM
Hello,
My query returns all Inbound
traffic email addresses and
groups them by email address:
Emailaddress EmailCount
fmitra@jp.abc.com 10
bjoe@jp.abc.com 5
kmary@uk.abc.com 3
bsmith@jp.abc.com 7
gchuck@uk.abc.com 5
john.smith@yahoo.com 5
edave... more >>
group by expression
Posted by Vadim Rapp at 12/9/2004 2:24:59 PM
Hello,
the expression
select isnull(c1,'') from table1 group by isnull(c1,'')
is valid; but
select isnull(c1,'') + sum(c2) from table1 group by isnull(c1,'')
returns error.
Is there a reason?
thanks,
Vadim Rapp... more >>
Performance of an INSERT with or without error handling
Posted by Jo Segers at 12/9/2004 2:22:37 PM
Hi,
For all of you who are interested in a performance question: Wich query
is faster if they both generate a primary key violation?
The first query will return a primary key violation tot the application
while the second query handles the violation in the subquery.
Our programmers use ... more >>
temp table
Posted by simon at 12/9/2004 2:03:08 PM
How can I get the result of sp_executeSql statement into temp table?
EXEC sp_executesql @sql, @paramlist,@DMLs,@korak,@mediji
I tried like this:
EXEC sp_executesql @sql, @paramlist,@DMLs,@korak,@mediji INTO #tempTable
but it doesn't work.
Thank you,
Simon
... more >>
Group Top Query
Posted by Taoge at 12/9/2004 1:52:11 PM
Is there a way to query top (or percent) by group?
e.g.
If I have table to hold the data of all students, such as
No, Name, Class,Achievement
1 Bob A 90
2 Mike A 96
3 Joy B 88
4 Doris B 80
5 John C 99
6 Mary C 97
I need the query pro... more >>
Query Kills webserver but SQL server runs fine?
Posted by JP at 12/9/2004 1:45:04 PM
I have a query that looks for addresses in a table and retrieves any of the
addresses that are LIKE the% @parameters% they specified. Since the Addresses
are stored in an Attribute driven table, the Address itself is stored as
Address1 | Address2 | City | State | Zip
all in a varchar(1000... more >>
How to break a result of a query in SP into batches ?
Posted by Paul fpvt2 at 12/9/2004 1:33:07 PM
I am using VB6 with ADO accessing a SQL Server 2000 database that has about
8.5 million records. The database has 1 table, and the table has 4 columns.
CREATE TABLE [dbo].[Packet] (
[PACKET_ID] [int] IDENTITY (1, 1) NOT NULL ,
[PACKET_TIME] [datetime] NOT NULL ,
[PACKET_CONTRACT] [varchar] (8)... more >>
Mail Profile
Posted by MS User at 12/9/2004 1:29:04 PM
SQL 2K
I just developed a DTS and want to add a 'Send Mail Task'.
In 'Send Mail Task' the drop-down for profle name is not displaying the
Profile Name configured under SQL Mail.
I know SQL Mail is confugured under a different account, my question is - As
a developer how can I set this task ... more >>
stripping out alpha characters from a string
Posted by Munch at 12/9/2004 1:19:08 PM
Here is some sample data I am looking at cleaning up :
FI 20
FIN04
FLFIT 03
FLFIT 04
FLFLT 03
FO 01
FO 02
FO 03
I am looking to seperate the Alpha part into one column and the numeric part
in another column.
I wrote this code, but wondering if there is an easie... more >>
select question
Posted by raybouk at 12/9/2004 12:49:01 PM
I would like possible solutions to knowing when a person hasn't received
report and when it has been over 90 days since report C has been sent.
---
DDL
CREATE TABLE [dbo].[reports_tracking1] (
[person_id] [int] NULL ,
[report] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[a... more >>
CREATE VIEW
Posted by Costi Stan at 12/9/2004 12:46:02 PM
What is wrong with this script?
IF @iErr = 0 BEGIN
CREATE VIEW vw_dabForms AS SELECT * FROM dabForms
SET @iErr = @@ERROR END
... more >>
Kick out all users
Posted by Martin at 12/9/2004 12:42:55 PM
Hi,
I have an installation of msde on a production server, I would like to do
some maintenance on this instance, however before doing so I need to kick
all users of the system -- that means all users of all databases.
I have achieved this in the past by just stopping the instance of SQL SE... more >>
Help with PATINDEX
Posted by lee NO[at]SPAM nospamplease.com at 12/9/2004 12:06:09 PM
Hello All,
I am in need of some guidance with regard to pulling out some numbers
from a nvarchar(240) field. The number reference I am looking for starts
with a
6, 7 or 8 and has at least 7 numerics in total, with the longest being 9 in
total. Not all records will have this referenc... more >>
trying to call my function
Posted by meg at 12/9/2004 12:01:52 PM
I created the following function w/your help ;)
CREATE FUNCTION fn_GetBusinessMinutesBetweenDates
(@startdate datetime, @enddate datetime)
RETURNS int
AS
BEGIN
RETURN (SELECT datediff(mi,@startdate,@enddate) - (1440 *
(SELECT count(*) from calendar
WHERE convert(char(8... more >>
set ansi null off
Posted by Andres Felipe Arguello at 12/9/2004 11:52:23 AM
Hi,
I need to use the ansi nulls in off to compare some register with null using
'=', this is my enviroment:
I call the SP from .net 1.0 object using sqlclient
the SP has in the header the,
SET ANSI_NULLS OFF
but when I compare using the = there is no response,
actually the SP doesn't change... more >>
Brackets and special characters -- Brain Teaser
Posted by Zeeshan at 12/9/2004 11:49:07 AM
Hello all first post,
Here's a new problem we just discovered:
Users login to our website with special characters in their user ids, and we
create tables appending their user ids. Unfortunately when we create, or
query these tables in stored procedures it gives us an error, because we don... more >>
Indirect Inner Join
Posted by Dr Tarheel at 12/9/2004 11:33:06 AM
I have a table which contains a tablename, column name and an identity
column, which also exists in the sam table that is named in the first table,
and want to create a view that displays elements from the first table and the
data within the column name set in the first table.
ie.
Table1
... more >>
can't drop database with DTS package
Posted by Chris at 12/9/2004 11:18:49 AM
I have a dts package that drops a database.
I run this dts package from a vb.net application.
I do perform one query on this database before I drop it but I believe I
have sufficiently destroyed all connections to the database.
When I run the package I get this error from the application... more >>
can't drop table because in use.
Posted by Chris at 12/9/2004 11:17:27 AM
I have DTS package that has a SQL query task, it drops a database on the
server.
I execute this DTS package from a vb.net application.
Before I execute the DTS package, earlier in the program I connect to the
database
to indicate if it actually does exist, then I query it once for some
in... more >>
can't drop database.
Posted by Chris at 12/9/2004 11:17:17 AM
I have a dts package that drops a database.
I run this dts package from a vb.net application.
I do perform one query on this database before I drop it but I believe I
have sufficiently destroyed all connections to the database.
When I run the package I get this error from the application... more >>
Generating sequence number
Posted by G B at 12/9/2004 11:04:43 AM
Hi Guys
I wonder whether you can help.
PROBLEM
'///
When a record is added or amended in the above table
the[FiySequence]field
are regenerated to place the records in date order.
Basically Depending on the date Order i need to generete a Sequence
Number.
I also need to check that d... more >>
Small problem with NULL
Posted by Mij at 12/9/2004 11:04:38 AM
Hello All,
I have the following few lines of code in my stored procedure:
declare @specnum int, @folup tinyint
SELECT @specnum = 1043, @folup = NULL
SELECT * FROM dbo.tblBilling WHERE Inspec_ID = @specnum AND FolUp_ID =
@folup
Obviously, I am trying to use a NULL for FolUp_ID and it ... more >>
Import text file on client into sql server table
Posted by Eric at 12/9/2004 10:38:39 AM
Is there a way to import a client-side tab-delimited text file into a SQL
Server table?
I'm not allowed to create a share (sensitive data), so I am looking to have
the SQL client perform the transfer.
I am using VB scripting. The last resort is to open two ADODB.recordsets
and do manual... more >>
Forecasting
Posted by Fredrick A. Zilz at 12/9/2004 10:31:48 AM
I need some assistance in creating a query to predict next years sales based
on the average of sales over the last two years, by Territory, Customer,
Item. I am basically working with two Tables OEINVH (master) and
OEINVD(detail). If All customers ordered the same product each month for
bo... more >>
emailing record counts of multiple tables
Posted by mamun at 12/9/2004 10:27:00 AM
Hi All,
I want to count records of some tables every morning and want the
results to be mailed in attachment or in the body of the email (I
prefer in the body to attachment).
I was searching the newsgroup and found the following (Thanks to all),
which works. But I need to do this for mult... more >>
Selecting rows from a distinct selection of one field
Posted by Adam Smith at 12/9/2004 10:07:58 AM
I'm not that new to SQL, but still I can't figure this out.
I'm writing some routines to check through IIS logs.
After batch importing the logs I end up with 16 fields. Field 2 is the IP
addresses of our users. I can distinct this field and get all IPs that
access our website.
What I then... more >>
Composing sql statement
Posted by simon at 12/9/2004 10:01:11 AM
What is better?
Compose SQL statement and use exec(sql) or write for each combination
different sql?
Simple example:
sql='SELECT * FROM products p"
if @mediaID is not null
sql=sql+' INNER JOIN media m on p.med_id=m.med_id WHERE
m.med_id='+@mediaID
exec(sql)
OR second option... more >>
Revisit: DISTINCT Query Returning Duplicate Results
Posted by Brad M. at 12/9/2004 9:47:04 AM
Hello everyone, apologies for the repost, just wasn't sure if people would
find the old one - it was almost two weeks ago.
Query and DDL - Problem Below:
Query:
SELECT DISTINCT MH.agent_id, MA.subscriber_name, MH.publisher_insertcount,
MH.publisher_updatecount, MH.publisher_deletecount,... more >>
Checking Execute Permissions
Posted by Andre at 12/9/2004 9:43:05 AM
I need to be able to check if a user has execute permissions on stored procs
in a database. I dont want to go through like 1000's of procs to check. Is
there some script I can run to do the checking for me? Thanks in advance.... more >>
"between" and < & >
Posted by meg at 12/9/2004 9:21:46 AM
Hi all -
I noticed that most times I get help on a query here most people use > and < instead of between... I was just
wondering if there was best practice to use < and > or if it didn't really matter.
Thanks!
... more >>
Copy ntext field to another table
Posted by matt at 12/9/2004 9:01:33 AM
In SQL Server 2000, I am trying to program a trigger that copies an ntext
field from table 'request' into table 'survey' when a new record is inserted
into table 'survey'. I've tried using WRITETEXT like the following, but it
doesn't work because it copies ptrval2's value (memory location), inst... more >>
select top 1000 per category
Posted by AstronusX at 12/9/2004 8:49:13 AM
How can I select each category top 100 items?
... more >>
sp_detach_db from VB6 code failure
Posted by E Sullivan at 12/9/2004 8:47:53 AM
Hi,
I am trying to detach a database. Sometimes it works, usually after a cold
boot but then it suddenly stops working. I get an error -2147217900 which is
very general. I'm starting a new connection, a new command object and then
calling a stored proc. I have also tried this with using a comm... more >>
T-SQL for multiple servers
Posted by Tod at 12/9/2004 8:45:02 AM
I'm coming from building queries in Access to writing
SQL, so pardon my newbieness.
In Access I can link to the tables of databases on
several servers, then use them in one query. Is it
possible to do this with T-SQL? I don't think I can do it
in Query Analyzer.
So if I had this:
S... more >>
Running SQL 64-bit on 32-bit servers
Posted by Andre at 12/9/2004 8:17:02 AM
Hi all,
Can anyone tell me if I can run SQL Server 64-bit on 32-bit Windows servers?
If possible? Drawbacks? Advantages? Maybe you can point me to whitepapers
on this question.
Thanks in advance.... more >>
Consecutive records then Mark the second record
Posted by PVR at 12/9/2004 7:59:48 AM
Hi Sql Gurus,
SeqNum Codes Comments
10 5F
20 5B
30 5F
40 5C
If there are consecutive records as 5F,5B or 2F ,2B then
the 5B Record to be marked as to be deleted in the
comments.
can any of them help with the following query.
Thanks
PVR
... more >>
Trim off charecters ???
Posted by jpferr119 at 12/9/2004 7:41:06 AM
I have a colum where all numeric fields are 10 charectors in length.
Example:
3730084712
3730084713
3730084714
3730000345
3730000346
3730000347
I would like to trim the entire colum to eliminate the '37300' in front of
all numbers. Once complete, I would like to trim off ALL leading... more >>
Delete link against another sql server
Posted by Enric at 12/9/2004 7:09:04 AM
Hi all of us,
I've done a link using sp_addlinkedserver but right now I want to delete
that from Query Analyzer.
Thanks in advance... more >>
Help for CASE
Posted by niitmalad at 12/9/2004 7:02:09 AM
Dear Freinds
I have wrote the follwoing query
ALTER PROCEDURE dbo.StockLoadingMonthlyReport
(@StartDate datetime,
@EndDate datetime)
AS
SELECT
(
CASE (dbo.STOCKSALES.FIELD1)
WHEN 'SDAY' THEN 145
ELSE 84 END
) AS COST,
Field1 AS LOADSTATUS, SLS_SALDATE, SLS_WHREF,
SLS_NoOfP... more >>
Speed question
Posted by Paul fpvt2 at 12/9/2004 6:57:04 AM
My SQL Server 2000 database has about 7.5 million records. The database has 1
table, and the table has 4 columns.
CREATE TABLE [dbo].[Packet] (
[PACKET_ID] [int] IDENTITY (1, 1) NOT NULL ,
[PACKET_TIME] [datetime] NOT NULL ,
[PACKET_CONTRACT] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_... more >>
Advanced insert problem???
Posted by Tim::.. at 12/9/2004 6:49:11 AM
OK
I'm having some problems with an insert statement! Basically I have a
datagrid that users can enter data into and a Stored Procedure that inserts
the info. into the appropriate tables...
This is the problem...
How do I Insert all the page information into tblPage and also get the
... more >>
CHAR(163) and CHARINDEX
Posted by phancey NO[at]SPAM 2bytes.co.uk at 12/9/2004 6:45:54 AM
I am looking for rows in my database that have a CHAR(163) in a certain
field.
When I do a select ... WHERE CHARINDEX(CHAR(163),field) > 0 I get lots
of rows returned. However it appears that all these rows in fact just
have a lower case u. For each row, I return the position of the first
ins... more >>
Median calculation
Posted by DrLostinExcel at 12/9/2004 6:35:02 AM
I'm trying to find a median (not average) for a column in a table. I thought
there was a median function but it only exists in Data Analysis. Is there
another function I can use and if not how do I get at the Data Analysis
function from a query. I DON'T want to have to leave my application and... more >>
Convert Date
Posted by Coz at 12/9/2004 5:22:18 AM
Hi, part of a report I need to layout out is a date. To simplify the whole
question I have changed it to the follow:
SELECT CONVERT(VARCHAR,GETDATE(),110)
How would I get these results of the above into, for example, 9 December 2004.
I have looked through this site and couldn't find an e... more >>
Modify Local IP in VB6
Posted by Roberto Lo Baido at 12/9/2004 4:39:57 AM
From Control Panel, LAN, TCP/IP, properties, IP Address,
Gataway
How do i change this properties with VB6 code without
doing it manually?
Thanks... more >>
calculating time differences-->more help needed!
Posted by Joshua at 12/9/2004 2:26:11 AM
Hi, I had a question couple of weeks back and got answer
from JAG and I thank for that, but one problem still occurs
(script below): I have next kind of script what calculates
seconds between changes in production line. It works fine
but I need to have the result in other line than now.
Tha... more >>
Incorrect syntax near the keyword 'AND'. i MS SQL 7.0 not in 2000
Posted by mortency at 12/9/2004 2:23:02 AM
I have problem with a query in version 7.0 but not in 2000. Dosent 7.0
support this?
SP looks like this:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER PROCEDURE sp_wpDotNo_DocList(@sp_LangID int,@sp_Instance
varchar(50))AS EXEC ('SELECT
DocID,DocActive,DocPriority,DocSu... more >>
Legal implications using Microsoft enhanced CSP
Posted by Rajendra at 12/9/2004 2:03:05 AM
Hi,
What are the legal implications of using Microsoft enahnced CSP?As I know
Microsoft Base CSP can be used worldwide without exprot license.Can I use
Microsoft enhanced CSP assuming that it is available without export license
worldwide.Suppose I have to generate a key pair using 2048 bit ... more >>
How to insert 'Simplified chinese' into database?
Posted by wangyang at 12/9/2004 1:25:03 AM
Dear all:
I create a database with collation name='Chinese_Taiwan_Stroke_CI_AS'. and
then I create a table with collation name='Chinese_PRC_Stroke_CI_AI ' by
using the following code.
CREATE TABLE [dbo].[article_choice_sc] (
[article_id] [int] NOT NULL ,
[issue_date] [smalldate... more >>
|