Hi all, I would like to have stored procedures that select data from another database without granting the user running the proc access to the other db. It works fine until I have procs which contain dynamic SQL as it breaks the ownership chain. Is there a way to circumvent this without ...more >>

Is it possible to see the result set data returned by a query in SQL Profiler? I can see the TSQL inside by Stored Procs listed in my trace, but I can't find a way to see the result data that is returned. How is this done? Thanks, -- da ...more >>

Just for the heck of it I took a few of the ERD tools out for a test run. I was a methodologist with a company called Knowledgeware back in the 90's. The joke was: What is the difference between a terrorist and a methodolgist? Answer: You can reason with a terrorist. But here is what's interest...more >>

When I write a create table SQL statement I want to add some information about the column heading of each column. How does the exact syntax for the create table look like (which includes this column data look)? How do I add later column headings ? Tom ...more >>

I created a function in C-Sharp to do a connection between Windows CE ..Net 4.1 and one MSDE SQL Server. This function work when I run it on a PC but when I run it on WinCE the connection failed. There is a procedure, files to copy or something to install on WinCE to do a connection with MSDE ...more >>

I possess 2 servers SQLServer with Transaction Replication among them. Some facts that I am in he/she doubts: I -increased 2 Campos new in a Table in server Publisher and it didn't happen Replication of the structure for Subscriber, doesn't altering only the structure of a table alter in the...more >>

I am creating tables with field names that are not very descriptive (I have to ) and I wanted to add a description to the field names when creating the dable. I have looked everywhere and cannot find the sytax to be able t do this. Is this possible and if so how do I do it Regards NE...more >>

How do I update a table using values from another table ? Both tables have 10 columns with the same name and properties. These columns all allow NULLs. These are the columns I want to update. The first table (to be updated) has 150 rows and the second table 100 rows. Both tables share two c...more >>

Hello! Can I raise an error from within an user-defined function? Thank you! ...more >>

I hope this is the right group to ask this kind of questions. I'm designing a database that incorporates also tree structure. I'm determined to implement "nested set" (Modified Preorder Tree Traversal) approach with two additional integers (left, right), to have fast SELECTS, while the tree wo...more >>

Hi! How do I move the database files to another location? I mean.. right now all the databases are stored where the installation thought was a good place to stare databases but I don't want to save them on the same partition as the OS so I want to move them to another disk.. There must be some...more >>

i have a stored below it uses create temp table and drop temp table... when more than one user request the same page it probably returns error.. how can i solve this problem **************************************************************************** ***************************************...more >>

I have some user tables that somehow have a system index on them (not a statistic). When I try to drop the index, I get the error: Server: Msg 3708, Level 16, State 3, Line 1 Cannot drop the index 'RPT_DEF.tRPT_DEF' because it is a system index. where the index name is always "t" + table_name...more >>

Hi, I have been racking my brain on this query I have a filtered table of data that looks like this: -------------------------- ID ¦ Group ¦ Charge -------------------------- 1 ¦ 1 ¦ 1.00 2 ¦ 1 ¦ 2.00 3 ¦ 2 ¦ 5.00 4 ¦ 2 ¦ ...more >>

I have a table that represents a parent (crt_actn_id) / child (rcvbl_id) relationship. There is a status code for the child on the table. What I want to do is to select the parents where all of the children of the parent have the same status 'F' In reference to the data example below, I'd like...more >>

Hi, I have the foll query select * from openquery(server, 'select prod.name,categ.name,price prod,categ where prod.state = 'ny' and prod.num = categ.num') This query works fine, however, if I want to give the columns names and I do select name as ProductName, name as categoryName f...more >>

I need to output results from a stored proc, to a dynamically created .txt file. The file name will be dependent on the date. I am using BCP to do this; however the output does not contain column names. The solution i have devised is to insert the column names into the table, and then orde...more >>

I want to select the rank of a name from a table of first names, but I want to also match common nicknames that aren't in that table (or are misleadingly ranked). So I do an optional mapping from the nickname to the canonical name first. I'm wondering if there's a better way to do this - perhaps...more >>

Hello, One of our databases has a stored procedure which returns a table (i.e. the last line of the SP is probably something like SELECT a, b, c FROM TableX... etc). Now I say "probably" because the SP is encrypted (it's part of a 3rd party application we purchased). Is there a way I can wr...more >>

What is the most efficient way to update a column based on the first occurrence of another column value only. eg I have a table with two columns col1 col2 A A A B B B C C C In this example rows 1,4, and 7 would be updated (when ever col1 changes) col1 ...more >>

hello there I just wanted to see if anybody had an age calculator function ready that takes dob and spit out the age ? thanks...more >>

Is there a proper (read standard) format that we should be passing date/times to SQL Server queries so that regardless of a user's Regional Settings or the version of SQL Server (i.e. domestic MM/DD/YYYY HH:MM:SS or Int'l DD/MM/YYYY HH:MM:SS) the query will be properly executed? Currently, re...more >>

I'm trying to rename a column with the following syntax: execute master..sp_rename 'PAY_PLAN.pay_type_seqno', 'pay_type', 'COLUMN' I'm getting the error: Server: Msg 15248, Level 11, State 1, Procedure sp_rename, Line 163 Either the parameter @objname is ambiguous or the claimed @objtype (COLU...more >>

CREATE TABLE Foo ( FooKey INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED, FooCode NCHAR(3) NOT NULL, -- other columns ) Data: FooKey FooCode 1 001 2 002 3 003 We have a batch that needs to get stuff from this and other tables and send it a...more >>

Hi all, I can join 2 tables using tableA a inner join tableB b on a.svc = b.svc. How do I add tableC c.acct = a.acct? TIA Joe ...more >>

Hi, i´ve a simple problem. I must add a parameter in the query analyer but it doesn´t work correct. My SQL-Strings looks like this: Select * from testtable where nr = :myparam1 In Delphi I can work with this string without any errors. Does anybody know how to use this parameters in the qu...more >>

Goodmorning, Could I have a SELECT statement that normally returns two rows ,but that instead returns one row appending to the first row the second one of the result ? For example Query: "SELECT username from tab1 where year in (2001,2002)" Result: 1° - "'John'" 2° - "...more >>

Hi all, I have a table with a column called "modifieddate" in which I am storing the date the row was updated. I have a stored proc that updates the row along with the modifieddate column. Now what I dont want to do is update the modifieddate column if none of the columns actually changed i.e....more >>

I can run a DTS that sends out results via sendmail with no problem, but when I call that DTS from a Stored Procedure, I'm getting Error string: Initialization of MAPI failed. Please make sure that you have messaging services installed on this system. Any thoughts on why its failing? I'm...more >>

The subject line says it all... I have had a quick search in BOL but didn't turn up anything relevant (likely to be more down to my searching than BOL). I want to write TSQL code that will grant/deny any or all permissions on any database objects I specify. Can someone point me at, or sho...more >>

I want to be able to always put in only the date in my datetime field. If I use '10/12/04', this is what I get in the field - 10/12/2004. If I use getdate(), I get "10/12/2004 11:18:52 AM". This causes me a problem with my testing for todays date or any other dates where I am dealing with...more >>

Hi, is it possible to create a trigger that can log a query fired to my database? I was trying like this (sqlserver 2000): CREATE TRIGGER T_xyz ON mytable AFTER DELETE,INSERT,UPDATE,SELECT AS BEGIN SET NOCOUNT ON DECLARE @ExecStr varchar(50), @Qry nvarchar(255) DECLARE @CurrApp VarC...more >>

Is there something in T-SQL that is the same as FOR loops in Oracle PL/SQL? if not, what is the closest equivalent? ...more >>

I want a UDF to return the current date trimmed to mm/dd/yyyy. I had tried CONVERT(NCHAR(10), GetDate(), 101), but get error 'Invalid use of 'getdate' within a function'. Is there an equivalent way to do this in a UDF? Thanks. Alan ...more >>

Hi Is it possible to preserve the format of a SQL query in the Enterprise Manager? As every time you run the query it reformats and bunches up the query, this isn't too much of a problem when you have simple queries. However when they get more complex and longer queries you want to seperate ...more >>

We have over 2000 stored procedures. Only one is causing a problem. Its probably one of the more complicated ones we have (BUT NOT THE MOST COMPLICATED!). The problem is that it takes 2 minutes to compile. (At least that is what I think it is doing!). When we execute it for the first time, it ...more >>

Dear All, I need optimize my report application... now, it take at least 5 minutes to generate a report, which according to the date range. for example: one week report, almost 5 minutes one month report, almost 25 minutes... I would like to know how to impove the performance.... The...more >>

Is there a way to create multiple non-clustered indexes on a memory table? Something like this: DECLARE @MemoryTable Table ( ID1 INT, ID2 INT, SomeFields INT KEY (ID1), ? KEY(ID2) ? ) -sandor ...more >>

Hi everybody ! The system freezes when I call a function to compute a total in my stored procedure. This problem only occurs when data become important. My SP Create Sp_.. () As Select dbo.MyFunction (idCustomer), Name, Adress.... From Customers My function CREATE FUNCTION MyFunction (@...more >>

Is there a way to create an index on more than 1 field in a memory table on SQL Server 2000? ex: DECLARE @MemoryTable TABLE ( ID1 INT PRIMARY KEY, ID2 INT PRIMARY KEY, SomeField INT ) Thanks in advance. -sandor ...more >>

I have a Condition in Which I need to update the TableA with a new record if the same Address Does not Exists in the TableA as of TableB If not Exists(Select Distinct B.Address From TABLEB B, Name A where A.ID = ‘24530’ and B.Address <> A.Address and B.County = 'Middlesex') Begi...more >>

A transaction can be named like BEGIN TRANSACTION ShipOrders but does that have any value if I don't use WITH MARK? What I am trying to figure out is why would I spend any time naming the transaction if I don't use WITH MARK because the name seems to be worthless without marking it in the log...more >>

46 tables from an old DB need to be moved into a new DB. The transfer is not always literal. The new tables can contain data from multiple tables from the old system. Some fields require a check by an IF statment before being transformed. I rather do this using SQL as it is intended to be u...more >>

I want to concatenate 2 columns : M_Data and D_Data in DATAS. Like: IF M_DATA=NULL THEN DATAS = D_DATA ELSE DATAS = M_DATA SELECT D_ID, D_M_ID, D_TD_ID, D_DATA, D_DESCRICAO, D_VALOR, M_DATA , D_Data + M_Data as DATAS FROM DESPESA LEFT OUTER JOIN MOVIMENTO ON D_M_ID=M_ID RESULT:...more >>

I have a DTS package which locates a text file and imports it into a table... At the moment, the name and path of the text file are stored as global variables. The VB script in the DTS package reads these variables, and sets the connection's datasource to the file specified.. The idea being I...more >>

I have a DTS package which locates a text file and imports it into a table... At the moment, the name and path of the text file are stored as global variables. The VB script in the DTS package reads these variables, and sets the connection's datasource to the file specified.. The idea being I...more >>

Hi, I was wondering before I proceed much further whether it is possible to pass in the Dictionary object (Scripting.Dictionary) from ASP to a stored procedure in SQL Server. Any sample sites or simple code examples would be really great. Thanks, Shahid ...more >>

Can you access temporary tables in the "query" portion of the xp_sendmail extended stored procedure? I.E. (#TempTable1 is a temporary table) CREATE TABLE #TempTable1 (ID int, YesNo bit) INSERT INTO #TempTable1 (ID int, YesNo bit) SELECT ID, YesNo FROM Table1 EXEC @r = master..xp_sen...more >>

Hi All, How can I list databases on a SQL Server that is on a different domain? I just cant get the connection right. Is there any way that I can connect to the server on the different domain? Any help would be much appreciated. Thanks in advance Suchir...more >>

Can you access temporary tables in the "query" portion of the xp_sendmail extended stored procedure? I.E. (#TempTable1 is a temporary table) CREATE TABLE #TempTable1 (ID int, YesNo bit) INSERT INTO #TempTable1 (ID int, YesNo bit) SELECT ID, YesNo FROM Table1 EXEC @r = master..xp_sendm...more >>

I have to capitalize the first letter of each word in an address field , here is an example original : 4 moorefield circle final : 4 Moorefield Circle Does anyone have any suggestion on how this can be done quickly and effectively? Thanks in advance.... ...more >>

I was experimenting with diagrams and I created a relationship between two tables. Later, I deleted the diagram definition; however, what it appears is that the relationship still exists. I validated this by going to my primary table and trying to change its value to something that wouldnt e...more >>

Hello Experts, The solution might be so easy but my head is going to be explode right now. I have 2 tables, showing below. I want to retrieve Works with only one of the responsibles of each work (don't care who), but without using subquery for the responsible: Select W.*, (Select Top 1 ...more >>

I have a table with at least two columns. One contains a key the other contains a comma sepatated list of varchar like this 'ellen,tom,sue'. There are no spaces between the listitems. My target is to create an new table/view containing per row a. the key value b. every single listitem befo...more >>

Thanks for all the help, but the problem is a bit more complicated than I first thought. Here is the code to create and populate a test table. CREATE TABLE [tblAbsence] ( [strLogonName] [varchar] (30) NOT NULL , [intYear] [int] NOT NULL , [dtmAbsStart] [datetime] NOT NULL , [dtmAbs...more >>

Is it possible to write a T-SQL statement that will: Delete duplicate records where lastname, firstname and address are equal and keep most current records in the following table: tbl_customers CustomerID LastName FirstName MI Addr City ST Zip DateUpdated For me as a beginner, ...more >>

Is there a way I can pass a Parameter(of type char) in to a stored procedure that will bring back all records?...more >>

The following is my query, which i try to execute in query analyzer. declare @listID varchar(100) set @listid = '1, 2, 3' SELECT * FROM MASTTANK WHERE ID in (@LISTID) it reports me an error Server: Msg 245, Level 16, State 1, Line 3 Syntax error converting the varchar value '1, 2, 3' to a ...more >>