Hi, I create a user(user1) only have "read" permission in SQL server. and through ODBC(use user1) and Access to create a link table to access the sql server database. At my clinet's computer, I at least saw two situations: 1. one of the computer, Access still can modify the link table but won't ...more >>

I need to import delimited text files: -webhost locked out bulk insert -building insert queries takes too much cpu -admin privileges not available what else is an option?? ...more >>

Hi, I have a need to create T-SQL queries that can be used to return complete and comprehensive schema information for all entities in a given database. In an ideal world this would cover version 7, 2000 and possbily Yukon. Does any one know if such queries exist in the public domain? I h...more >>

Hello, I opened the "simple" example from SQL 2000 in Visual Studio.Net 2003. (The simple example is located in C:\Program Files\Microsoft SQL Server\80\Tools\DevTools\Samples\backup\simple). Of course, VS.Net 2003 upgraded the code, and did a sufficient job at doing so. On 3 PC's, I've b...more >>

The documentation states that a resultset is returned for each select statement in a stored procedure. Is this true? How do multiple result sets get returned? If I do a "Set rs = cmnd.execute" of a stored procedure which does the "rs" get set to? Gene Frassetto ...more >>

Hi everyone, I need to create a procedure that would take the two parameters @FieldName and @FieldValue and replaces it in my SQL statement without using Exec because Exec can take at most 128 characters. CREATE PROCEDURE Get_Person_By_Field @FieldName varchar(20), @FieldValue varchar(100)...more >>

is it possible to do something like this in sql server? select field, recno() from table where recno() is a sequence number created by sql server. For those who knew dbase, you would understand what I meant. -- .~. Might, Courage, Vision. In Linux We Trust. / v \ http://www....more >>

I have two SELECT INTO statements that create a temp table with the same name. They are in an IF ELSE block so only one will get executed. IF @test = 0 SELECT something... INTO #temp FROM... ELSE SELECT somethingelse... INTO #temp FROM... When I try to create the SP (not execute i...more >>

I have found tons of information on internet as to why a column can only contain 1 NULL in it when you have a Unique Constraint on that column but I have not found any alternatives on how to check when you have multiple NULLS. I have a table with a column that can contain a value but which can b...more >>

Hello, Someone knows the way to use % operator, I have this example: Select (26.533325*60) % 6 or Select 1591.999500 % 6 I get this error: Server: Msg 206, Level 16, State 2, Line 1 Operand type clash: int is incompatible with void type Server: Msg 8117, Level 16, State 1, Line 1 Op...more >>

Dim conn As New SqlConnection conn.ConnectionString = "Password=angiep;Persist Security Info=True;User ID=trint;Initial Catalog=tribidz;Data Source=TRINITY" Dim cmd As New SqlCommand With cmd .Connection = conn End With ...more >>

If I am sitting with a dataset in a DALC component can I pass the dataset to a stored procedure or do I have to parse the dataset, populate parameters and do a regular call to the stored procedure. If I have to do this can someone tell be the usefullness of building a dataset and passing it to...more >>

Is there a way to execute an ODBC connection and commands to an AS400 from a store procedure on a SQL Server? ...more >>

--SQL Server 2000 I have multiple file in a folder all with the same file extensions that I want to insert into a table using bulk insert... I do it already with one file, but now i have many. I can write a VB app to merge all the text files into on but I want to try and do it from SQL. ...more >>

Did anyone use a ComObject in a transact sql script? How to make such objects "global" to the server, so they must no be created each time the code is called ? Thank you for your time. Regards Max ...more >>

Dear All, I want my WHERE clause to permit me to compare a casted varchar to an int, without throwing an error on varchars that contain decimal points. For example, in the sql below, I want the last select to simply return nothing; instead, it throws a Syntax error. /***********************...more >>

Hi, The codes below create a temp table (#keys) then populate it with data from another table that vary daily. CREATE TABLE #keys(keyvalue nvarchar(100) NOT NULL PRIMARY KEY) INSERT INTO #keys SELECT DISTINCT CAST(type AS nvarchar (100)) FROM table GROUP BY type Note: Type field is var...more >>

Joining to a table with multiple rows needing only to join it the max key has status = 'O'. The problem I am having is when I use Max(STA_ID) and I can't use where clause for STA_CD = 'O'. I only want to join "IF" the MAX(STA_ID) has Status code of 'O'. Thanks in Advance, Marcel K. Data ...more >>

I am challenged with keeping track of laptop resources as they arrive, as they are checked in and out, and when they reach end of life. I would to create a db that handles storing a resource's information and handles and stores transactions pertaining to the resource. I would like to be flexib...more >>

Hello Friends, I need to get a complicated query ... i think you could help me. I need to retrieve this (show all items while total is fewer then 60) Item1=A Value=10 (Total=10) ... show Item2=B Value=30 (Total=40) ... show Item3=C Value=20 (Total=60) ... show .. .. .. Item4=D Value=40 ...more >>

Hi, I have a view made up of 3 different tables from 3 different Databases Create View MyView as Select * from DB1..Table1 union Select * from DB2..Table2 union Select * from DB3..Table3 Each table has it's own primary key. When I try to query the view based on the PK it is very slow...more >>

How?...more >>

I have 3 tables that I want to join. Two of the tables are going to be joined on 4 fields. Should I index those 4 columns as on clustered index or should I index the columns individually? The other table is only joined on one column which I created an index for it. Why, if ever, would...more >>

Hello, My application is written for US OS, but a version is localized for Japanese OS. My application creates records in MSDE 2000 on Japanese OS. However, the MSDE 2000 that I incorporated in my application's installation program was from US version. Whenever I write new record the the datab...more >>

I'm running into a problem with blocking. When I go into EM and expand 'Locks / Process ID', I see a list of SPID's. One of them will have "Blocking" next to it. If I click on that SPID, on the right hand side of the EM screen, I see a list of tables with various lock types, modes and other i...more >>

I'm using Access 2000 database for my VB 6.0 multiuser application (db is located on file server). Database gets corrupted pretty often. I've read just general statement about SQL Server stability and robustness. Is SQL Server database corruption happening too? If so then how often comparing to ...more >>

Can someone tell me how long SQL Server waits, by default, to acquire a lock before timing out? And, how can this time be changed? Thanks. ...more >>

I have a VB6 application that allows the user to provide criteria that is sent into parameters of a SQL stored procedure. The stored procedure selects records based on the criteria, and returns the data to a disconnected recordset in VB. My issue is that some of the recordsets being return...more >>

How do I get a count of the number of distinct occurences in a select? Doing the following gives me a total count: select count(distinct customerid) total in orders I want somthing like the total number of orders per customer. ...more >>

I use SQL server 2000. I have a problem composing a query for the following : A section of my data will look like this: two of the fields are book and page : book | page .........other fields 1 2 2 1 2 3 2 2 1 1 1 2 ...more >>

Hi, Is there a way to reference values in the DELETED table for a PARENT table from a CHILD table (assuming that referental integrity have been set to CASCADE ON DELETE bewteen the PARENT and CHILD). Reason: ======================== I'm trying to get a value from a parent table, while ...more >>

Hi, Is there any way to transform data before exporting data in to a table using bcp command? I want to transform data(like calling a function to transform data) before exporting data into a table. ...more >>

Hello, So tbl1 contains about 1.2 million records with a unique RowID field. tbl2 contains a list of RowIDs from tbl1 where the data in the respective rows is duplicate data (but the RowIDs are unique). tbl2 contains about 66,000 RowIDs. I need to delete these Rows from tbl1. In hopes...more >>

Hi, guys, I need to implement a query as: -- @Group is actuallypass-in parameter. Here I declare and set it to a dummy value declare @Group int set @Group = '123' if @Group in ( .???. ) begin --show this group full list end else begin --show this group shared list end ...more >>

I'm using a .NET client to query a SQL Server 2000 database. Is there any possible way to screw up a dynamic SQL statement if you escape any single quotes in user input with two single quotes? The only way I can think of that this could fail is if you exceed 8K for the SQL statement by enteri...more >>

CREATE TABLE dbo.t ( a int null, b int null, c int null, d int null) GO INSERT INTO t VALUES(1,7,0,4) INSERT INTO t VALUES(1,6,0,5) INSERT INTO t VALUES(2,0,4,0) INSERT INTO t VALUES(2,5,5,0) INSERT INTO t VALUES(2,3,6,0) --**************************************** Hello everyone,...more >>

How have I get COUNT() to return zero if a query returns no rows? It seems to return an empty string which I can't seem to use either CAST or CONVERT to get a valid number. The problem is that if I attempt to use the result in a calculation, that gets an empty result as well! I need to ge...more >>

Hi, Do you know how to convert the following hex into a datetime? Select datecolumn from tablename The result was this hex from the query: 0x000000F9587D9A04 This is how the application insert the datetime...strange! Thanks for your help. ...more >>

Hi I'm having a mental block... I need this : Declare @EndTime as DateTime, @InitDate as DateTime, @NoOfSlots as int, -- Number of days to create slots for @SlotSize as int -- SlotSize Default=15 mins Select @InitDate = GetDate() -- Default to today Select @NoOfSlots = 34 Se...more >>

I'd like to search multiple databases at one time for instances of a selected view or stored procedure. Can you share a code snippet with me? Thanks, B...more >>

I am wondering if there is a stored procedure or script where you can obtain values from a field in a previous record and have that field populated with the same values in the current record. For example, the Shortcut Key in Access and Access Project would be CTRL + APOSTROPHE(') - to ins...more >>

Hi, I'm having a bit of a mental block ;-( I need this : ...more >>

Hi! Need help in corercting UPDATE logic. Please see the DDL below: create table #p(pool varchar(20), acct varchar(20), amt money ) insert into #p values('dps00', '123-456', 5) insert into #p values('dps00', '444-555', 1) insert into #p values('dps00', '22222', 2) insert in...more >>

I have been using FP for two years and need to learn about databases and some beginning programming in order to make any progress with my company. I know a very little about asp.net and that is the direction I have been told to follow. I am sure many, many people have already done this and so a...more >>

Are there any trace flags I can set to help me deal with blocking issues (not deadlocks)? ...more >>

how can i tell the date that a stored procedure or function was altered?...more >>

Hi folks! I have a table which holds prices in a column of type float. A second column holds a quantity as type int. How can I get a query to give me a total value rounded to 2dp? I have dried "SELECT price*quantity", but if, for example, the price is 9.25 and the quantity is 8, then ...more >>

Dear All, I found a store procedure on here a couple of days ago called sp_msforeachtable. Today I thought I would have a look at the BOL definition of what it does. I do have the latestest version of BOL. Anyway BOL does not have it. This got me thinking, are there other undocumented ...more >>

I have a .NET app that I've written that uses calls to SQL-DMO to simplify common SQL Server tasks we do every day in our business. It's worked out very nicely so far. But recently I've run into the problem that I now have users that need this app and yet I don't want to install the SQL ...more >>

Dim MyConnection As SqlConnection = New SqlConnection("server=TRINITY;Trusted_Connection=yes;database=tribidz") Dim myCommand As SqlCommand = New SqlCommand Dim myTrans As SqlTransaction Try ' Open the connection. MyConnection.Open() ...more >>

Hi If we use wildcard searches using a LIKE clause on an indexed column, does SQL Server use an index ? What if we do an exact search such as "where state like 'NJ'" ? I cannot find information on how sql server decides to use an existing index for wildcard searches. Any pointers ? T...more >>

Hello I ran the following query --START QUERY EXEC sp_configure 'allow updates', '1' RECONFIGURE WITH OVERRIDE GO UPDATE master.dbo.[sysmessages] SET severity = 10 WHERE error = 266 AND severity = 16 GO EXEC sp_configure 'allow updates', '0' RECONFIGURE WITH OVERRIDE GO --END QUERY Th...more >>

Hi! pls. advise, I need to run a select query, but table name should be returned from variable, for example "select id from @var_table_name" -this does not work... thx...more >>

I've been researching to find some code ideas to help with a project, this is a long list but please read it seems to be a problem that alot of people have designing a SQL Server queue type table. I want to build a queue where I have a list of people in a list, I want to ensure that each user ...more >>

Hello, Can someone please give me an example of how I can change the severity of 16 to severity of 10 for Err 266. Thanks...more >>